Statement - Simple JDBC

JDBC: Java provides and API called JDBC to interact with Database. It provides methods for querying and updating data in a database. JDBC is oriented towards relational databases. JDBC provide JDBC-ODBC bridge to connect to any ODBC database.

Well, Database programming only sounds tough but it's pretty simple. There are always four steps to access the database.

1. Load the JDBC drivers in memory
2. Create a database connection
3. Create Statement and execute the sql query
4. process the result, if any

You will be following these steps only no matters what type to database you are accessing. In java, We have three way to access database.

• Statement
• Prepared Statement
• Callable Statement

We will discuss them one by one.

Statement:

Statement is an Interface provided by JDBC. Statement is used in simplest type of database programming. Practically this is not used much in real projects but this provides a basic understanding of database programming.

Suppose we have a table called tblEmployee with following column,

1. empName
2. empCode
3. empSalary
4. empAddress

Case 1: Retrieving data from database

We will fetch all the employee

*****************************************************************

class JDBCConnection {

public static void main (String args[]) throws SQLException

{

Connection conn = null;

Statement stmt = null;

try {

// Step:1

Class.forName ("oracle.jdbc.driver.OracleDriver");

//Step:2

conn = DriverManager.getConnection

("jdbc:oracle:thin:@localhost:1521:xe", "scott", "tiger");

// host:port:SID, username, password

//Step:3

stmt = conn.createStatement();

ResultSet rset = stmt.executeQuery("select * from tblEmployee");

//Step:4

while (rset.next())

{

System.out.println (rset.getString(1)); // empName

System.out.println (rset.getInt(2)); // empCode

System.out.println (rset.getInt(3)); // empSalary

System.out.println (rset.getString(4)); // empAddress

}

}

catch (ClassNotFoundException e) {

e.printStackTrace();

}

catch(SQLException ex) {

System.err.println("SQLException: " + ex.getMessage());

}

finally {

conn.close();

stmt.close();

}

}

}

*******************************************************************************

Code Explanation:

Step 1: Here we are loading the driver in memory. this is an essential step so use it as such.

Step 2: We are creating connection to dabase using connection string. In connection string, we need to specify drivers, databse host & port, service name and db username and password. These details must be provided by DBA or someone who actually configured the database.

Step 3: Here we are creating statement. Statment provides necessary methods to interact database. There are common methods for inserting, updating and retrieving the data from database. Here we are retrieving the data from database so we used general purpose method executeQuery. you can use executeUpdate() to update data in database.

Step 4: executeQuery method return a ResultSet onject which contains query result. We can iterate over result set to get data fetched from DB. Depending on number of column returned by your query, use getXXX(colNum) method of result set. colNum starts with "1". XXX in setXXX() can be repalced by various datatypes as Int, String, Float, Object etc. 
Always close the connection in finally block. it should not be left open to improve network usage.

You have learned how to fetch data from DB. Lets move to data updating in DB.

Case 2: UPDATE DB

Now we need to update a employee's salary to 25000 whose code is 101, so we will write a program for that,

******************************************************************************

import java.sql.*;

class JDBCConnection {

public static void main (String args[]) throws SQLException

{

Connection conn = null;
Statement stmt = null;
try {
Class.forName ("oracle.jdbc.driver.OracleDriver");

String conStr = "jdbc:oracle:thin:@localhost:1521:xe";
conn = DriverManager.getConnection(conStr,"scott","tiger");

String queryStr = "update tblEmployee set empSalary = 250000 where empCode = 101";
stmt = conn.createStatement();
stmt.executeUpdate(queryStr);
}

catch (ClassNotFoundException e) {

e.printStackTrace();

}

catch(SQLException ex) {

System.err.println("SQLException: " + ex.getMessage());

}

finally {

conn.close();

stmt.close();

}

}

}

********************************************************************************

Code Explanation:

Here you can see lots of difference from last example. As we are updating the record, not fetching them, so there will be no result set. we are using executeUpdate for updaing the data in database.

Case 3: Adding New Record in DB,

Suppose new employee named Rajesh joined the company with salary 30000, employee code 10002 and address 'Noida'.

We will add him in DB using program blow,

********************************************************************************

import java.sql.*;

class JDBCConnection {

public static void main (String args[]) throws SQLException

{

Connection conn = null; Statement stmt = null;

try {

Class.forName ("oracle.jdbc.driver.OracleDriver");
String conStr = "jdbc:oracle:thin:@localhost:1521:xe";

conn = DriverManager.getConnection(conStr,"scott", "tiger");

stmt = conn.createStatement();

String queryStr = "INSERT INTO tblEmployee (empCode, empName, empSalary, empAddress)

VALUES(10002, 'Rajesh Jha', 30000, 'Noida') ";

stmt.executeUpdate(queryStr);

}

catch (ClassNotFoundException e) {

e.printStackTrace();

}

catch(SQLException ex) {

System.err.println("SQLException: " + ex.getMessage());

}

finally {

conn.close();

stmt.close();

}

}

}

*************************************************************************

Case 4: Deleting a Record

An employee with code 10001 left the company so we need to delete his record from employee table. Lets do it,

*************************************************************************

import java.sql.*;

class JDBCConnection {

public static void main (String args[]) throws SQLException

{

Connection conn = null; Statement stmt = null;

try {

Class.forName ("oracle.jdbc.driver.OracleDriver");

String conStr = "jdbc:oracle:thin:@localhost:1521:xe";

conn = DriverManager.getConnection(conStr, "scott", "tiger");

stmt = conn.createStatement();

String conStr = "DELETE FROM tblEmployee where empCode = 10001";

stmt.executeUpdate(queryStr);

}

catch (ClassNotFoundException e) {

e.printStackTrace();

}

catch(SQLException ex) {

System.err.println("SQLException: " + ex.getMessage());

}

finally {

conn.close();

stmt.close();

}

}

}

**************************************************************************

Code Explanation:

You must have noticed there is not much difference in UPDATING, DELETING and INSERTING data in database.

There is same code for all these operation, only thing that change is Query String.

Just change your query string and db details in codes above and use them in your project and enjoy smart working...

Hope you have got a brief and cocrete idea about JDBC statements. There are books alone on JDBC but the extract of 200 pages is in this page only. For better understanding, just try out some hands on examples and be master in JDBC. For any clarificatin, Please feel free to contact me at mohit.amour@gmail.com

Thanks,

The Interpreter