Pr.Pg Next Pg

Database access using JDBC tutorials

Using JDBC to access databases can be described in six steps

  1. The first step is to load the JDBC driver for the DBMS you need to interact with.

  2. The second step is to connect to the database.

  3. The third step is to create a statement.

  4. The fourth step is to query the database using SQL Select statements

  5. The fifth step is to insert/delete/update data in the database. The fourth and fifth steps can be repeated as many times as needed by the application.

  6. When the application does not need to interact with the database, it should close all the resources, including ResultSet, Statement, and Connection, which should be the last step of using JDBC.

 

Load the JDBC drivers

  • A JDBC driver is an implementation of JDBC API for a specific DBMS. A Java program can load several JDBC drivers at time.

  • This allows the program to interact with more one database running under different DBMSs.

  • The following line of code loads the JDBC driver for apache derby database.

Class.forName("org.apache.derby.jdbc.ClientDriver");

 

Connect to Database

A connection to a database can be established via the getConnection method of the DriverManager class. The getConnection method accepts three parameters the database, user name, and password. As below:

Connection con = DriverManager.getConnection("jdbc:derby://localhost:1527/Demo","user","password");

 

Create a Statement

A Statement object has the ability to parse SQL statements, send the SQL statements to the DBMS, and accept the results returned from the DBMS.

Statement st = con.createStatement(); 

 

Interacting with Database

Normally, two opertions of Statement are needed to interact with a database.

One is executeQuery(sql_select), which takes a SQL Select statement as its argument, sends the Select to the DBMS, and returns the results as an object of ResultSet.

The other operation is executeUpdate(sql_insert_delete_update), which takes a SQL statement (Insert, Delete, or Update), sends it to the DBMS. Both operations throw a SQLException if the statement cannot be executed by the DBMS successfully.

Statement st=con.createStatement();

 

Disconnect from the Database

  • When the application completes or no further database interaction is needed, you should return JDBC resources back to the system, which include objects of Statement, ResultSet, and Connection.

  • You can disconnect from the Database by using close() method. As below:

rs.close();

st.close();

 

What is derby DB?

  • Apache Derby, an Apache DB subproject, is an open source relational database implemented entirely in Java and available under the Apache License, Version 2.0. Some key advantages include:

  • Derby has a small footprint about 2.6 megabytes for the base engine and embedded JDBC driver.

  • Derby is based on the Java, JDBC, and SQL standards.

  • Derby provides an embedded JDBC driver that lets you embed Derby in any Java-based solution.

  • Derby also supports the more familiar client/server mode with the Derby Network Client JDBC driver and Derby Network Server.

  • Derby is easy to install, deploy, and use.

  • If you have the Apache Tomcat Server or GlassFish Server registered in your NetBeans IDE installation, Java DB will already be registered for you.

 

Starting the Server and Creating the Database

  • The Java DB Database menu options are displayed when you right-click the Java DB node in the Services window. This contextual menu items allow you to start and stop the database server, create a new database instance

  • To start the database server below is the step:

  1. In the Services window, right-click the Java DB node and choose Start Server.

Derby

Note the following output in the Output window, indicating that the server has started.

derby

  1. Right-click the Java DB node and choose Create Database to open the Create Java DB Database dialog.

creating durby db

  1. Type DBDemo for the Database Name.

  2. Type User Name and Password. Click OK.

  3. After you create the database, if you expand the Databases node in the Services window you can see that the IDE created a database was added to the list under the Databases node.

Creating derby db

  1. Now Right click on DBDemo and click on Connect.. as below:

  1. Then next again Right click on DBDemo and click on Execute Command as below:

Derby execution

  1. Now IDE will Open a SQL Command 1 Window where you can write all SQL query for Database manipulation.

  2. Now Creata a table inside SQL Command 1 for our Demo we will create Employee table and also insert some values, query for this table is given below:

Create table Employee

(

EmpID varchar(5),

First_Name varchar(20),

Last_Name varchar(20),

Designation varchar(30),

Salary numeric(7,2),

Contact_No numeric(12),

EmailID varchar(35)

)

 

  1. Query for data insert in Employee table is given below:

Insert into Employee values ('E0001','Jack','Diaz','Manager', 30000, 9635245965,'jackdiaz85@gmail.com')

Insert into Employee values ('E0002','Mark','Pintocher','TeamLeader', 25000, 9658245693,

'mark619@gmail.com')

Insert into Employee values ('E0003','Peter','Parker','Researcher', 23000, 9854723025,

'parkerpeter95@gmail.com')

 

  1. To execute the query in SQL Command 1 Select the portion of query and right click and select Run Selection as below

Derby sql

  1. Now it will produce an output result set as below:

DB table


 

Example: Jdbc program for displaying the result of table on a web page

Sam Sir

//Program name DataDemos.java

// Jdbc program for displaying the result of table on a web page

import java.io.IOException;

import java.io.PrintWriter;

import javax.servlet.ServletException;

import javax.servlet.http.HttpServlet;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

import java.sql.*;

public class DataDemos extends HttpServlet {

protected void doGet(HttpServletRequest request, HttpServletResponse response)

throws ServletException, IOException {

response.setContentType("text/html;charset=UTF-8");

PrintWriter out = response.getWriter();

try

{

Connection con;

Statement stm;

Class.forName("org.apache.derby.jdbc.ClientDriver");

con=DriverManager.getConnection("jdbc:derby://localhost:1527/Demo","samstaff","sam@123";

stm=con.createStatement();

ResultSet rs;

rs=stm.executeQuery("Select * from Employee");

out.println("<Center><H1>Database Result</H1></Center>");

out.println("<table border='1'>");

out.println("<tr><th>EMPID</th><th>First_Name</th><th>Last_Name</th><th>Designation</th><th>Salary</th>" +

"<th>Contact_No</th><th>EmailID</th></tr>");

while(rs.next())

{

out.println("<tr><td>"+rs.getString("EmpID")+"</td><td>"+rs.getString("First_Name")+"</td>" +

"<td>"+rs.getString("Last_Name")+"</td><td>"+rs.getString("Designation")+"</td><td>"+rs.getString("Salary")+"</td>" +

"<td>"+rs.getString("Contact_No")+"</td><td>"+rs.getString("EmailID")+"</td></tr>");

}

out.println("</table>");

}

catch(Exception ex)

{

out.println(ex);

}

}

}

Output

Data Display using JDBC

 


 

Example: Servlet program for insert, delete, view,search, update operation using single servlet

Sam Sir

//Program name ProcessServlet.java

// Servlet program for insert, delete, view,search, update operation using single servlet

import java.io.IOException;

import java.io.PrintWriter;

import javax.servlet.ServletException;

import javax.servlet.http.HttpServlet;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

import java.sql.*;

public class ProcessServlet extends HttpServlet

{

protected void doPost(HttpServletRequest request, HttpServletResponse response)

throws ServletException, IOException

{

response.setContentType("text/html;charset=UTF-8");

PrintWriter out = response.getWriter();

String connectionURL = "jdbc:derby://localhost:1527/DBDemo";

Connection connection=null;

ResultSet rs;

Statement stm;

//get the variables entered in the form

String empid = request.getParameter("Empid");

String fname = request.getParameter("fname");

String lname = request.getParameter("lname");

String designation = request.getParameter("designation");

String salary = request.getParameter("salary");

String contact = request.getParameter("contact");

String email = request.getParameter("email");

String Action=request.getParameter("Action");

try {

// Load the database driver

Class.forName("org.apache.derby.jdbc.ClientDriver");

// Get a Connection to the database

connection = DriverManager.getConnection(connectionURL, "user", "password");

stm=connection.createStatement();

if(Action.equals("Insert"))

{

//Add the data into the database

String sql = "insert into Employee values (?,?,?,?,?,?,?)";

PreparedStatement pst =

connection.prepareStatement(sql);

pst.setString(1, empid);

pst.setString(2, fname);

pst.setString(3, lname);

pst.setString(4, designation);

pst.setString(5, salary);

pst.setString(6, contact);

pst.setString(7, email);

int numRowsChanged = pst.executeUpdate();

// show that the new account has been created

out.println(" Inserted Sucessfully..");

pst.close();

}

else if(Action.equals("Delete"))

{

int j= stm.executeUpdate("Delete from Employee where EmpID='"+empid+"'");

out.println("Deleted Successfully..");

}

else if(Action.equals("View"))

{

rs=stm.executeQuery("Select * from Employee");

out.println("<Center><H1>Database Result</H1></Center>");

out.println("<table border='1'>");

out.println("<tr><th>EMPID</th><th>First_Name</th><th>Last_Name</th><th>Designation</th><th>Salary</th>" +

"<th>Contact_No</th><th>EmailID</th></tr>");

while(rs.next())

{

out.println("<tr><td>"+rs.getString("EmpID")+"</td><td>"+rs.getString("First_Name")+"</td>" +

"<td>"+rs.getString("Last_Name")+"</td><td>"+rs.getString("Designation")+"</td><td>"+rs.getString("Salary")+"</td>" +

"<td>"+rs.getString("Contact_No")+"</td><td>"+rs.getString("EmailID")+"</td></tr>");

}

out.println("</table>");

}

else if(Action.equals("Search"))

{

rs=stm.executeQuery("Select * from Employee where EmpID='"+empid+"'");

out.println("<Center><H1>Database Search Result</H1></Center>");

out.println("<table border='1'>");

out.println("<tr><th>EMPID</th><th>First_Name</th><th>Last_Name</th><th>Designation</th><th>Salary</th>" +

"<th>Contact_No</th><th>EmailID</th></tr>");

while(rs.next())

{

out.println("<tr><td>"+rs.getString("EmpID")+"</td><td>"+rs.getString("First_Name")+"</td>" +

"<td>"+rs.getString("Last_Name")+"</td><td>"+rs.getString("Designation")+"</td><td>"+rs.getString("Salary")+"</td>" +

"<td>"+rs.getString("Contact_No")+"</td><td>"+rs.getString("EmailID")+"</td></tr>");

}

out.println("</table>");

}

else

{

PreparedStatement ps = null;

ps=connection.prepareStatement("update Employee set EmpID=?, First_Name=?, Last_Name=?," +

"Designation=?, Salary=?, Contact_No=?, EmailID=? where EmpID=?");

ps.setString(8,empid);

ps.setString(1, empid);

ps.setString(2, fname);

ps.setString(3, lname);

ps.setString(4, designation);

ps.setString(5, salary);

ps.setString(6, contact);

ps.setString(7, email);

int rowupdate = ps.executeUpdate();

out.println("Updated successfully..");

ps.close();

}

}

catch(ClassNotFoundException e)

{

out.println("Couldn't load database driver: "

+ e.getMessage());

}

catch(SQLException e){

out.println("SQLException caught: "+ e.getMessage());

}

catch (Exception e){

out.println(e);

}

finally {

// Always close the database connection.

try {

if (connection != null) connection.close();

}

catch (SQLException ignored){

out.println(ignored);

}

}

}

}

 

 

 


 

HTML form for data

Sam Sir

<!—Insert Form -- >

<html>

<head>

<title>Insert Form</title>

</head>

<body>

<center><h2>Insert Records</h2></center>

<form action="ProcessServlet" method="post">

<table border="0">

<tr><td>EmpID:</td><td><input type="text" name="Empid"></td></tr>

<tr><td>First Name</td><td><input type="text" name="fname"> </td></tr>

<tr><td>Last Name</td><td><input type="text" name="lname"> </td></tr>

<tr><td>Designation</td><td><input type="text" name="designation"> </td> </tr>

<tr><td>Salary</td><td><input type="text" name="salary"> </td> </tr>

<tr><td>Contact No</td><td><input type="text" name="contact"></td></tr>

<tr><td>EmailID</td><td><input type="text" name="email"></td> </tr>

<tr><td><input type="reset"></td><td><input type="submit" value="Insert"

name="Action"></td></tr>

<tr><td><input type="submit" value="Delete" name="Action"></td>

<td><input type="submit" value="View" name="Action"> </td>

</tr>

<tr><td><input type="submit" value="Search" name="Action"></td>

<td><input type="submit" value="Update" name="Action"> </td>

</tr>

</table>

</form>

</body>

</html>

 

Output

Data Manipulation in JDBC

 

JDBC

 

JDBC

 

JDBC

 

JDBC

 

Pr.Pg border                                              Next Pg