The JDBC API Query Processing


JDBC helps you to run the query on the DBMS using the java, you prepare the query and execute it. You get back the resultset and process it for Application.

In the previous part of our article you learn about The Basics of the JDBC API. If you are not yet familiar with the basics, we suggest to read the basics first. But if you have already read the basics you can now proceed to next part which will first introduce you with the two type of queries in SQL, these are called DML queries and DDL queries.

DML(Data Manipulation Language)

DML dines the type of queries which are used to make changes to the data this includes the CURD commands.
C-Create : INSERT : the Insert command is used to insert data into the table.
U-Update : UPDATE : Update query is used to update the data in the table.
R-Read : SELECT : The select query is used to read the data from table or tables.
D-Delete : DELETE : Delete the data from the table.

DDL(Data Definition Language)

DDL queries are used to create the database and tables or to make changes to them instead of the set of rows or data. These are,
CREATE: create the database or table
DROP: Drop or say delete the whole table or database
ALTER: make changes to the table as whole.

So these are the main type of SQL commands we may face regularly. So let us look now how we can use these queries in JDBC.

In the last post you saw how do we create the Connection using the type 4 driver now let us use that connection to insert the value in to the database.


package jdbc;
import java.sql.*;
public class TestJDBC {

    public static void main(String[] args) {
        try {
            //call driver and create connection
            Class.forName("com.mysql.jdbc.Driver");
            Connection con = DriverManager.getConnection("jdbc:mysql://localhost/student", "root", "");

            //prepare query to insert value
            PreparedStatement ps = null;
            ps = con.prepareStatement("insert into student_info(name,class) values(?,?)");
            ps.setString(1, "Kenny");
            ps.setString(2, "X");
            int r = ps.executeUpdate();
            System.out.println(r);
        }
        catch(ClassNotFoundException cnfe){
                System.out.println(cnfe);
        }
        catch(SQLException se){
            System.out.println(se);
        }
	catch(Exception e){
		System.out.println(e);
	}
    }
}

In this program the output will be

1

This 1 means that one row is effected. In the program we have used the PreparedStatement interface which is used to create the sql statement using the prepareStatement("write query here"); method. you need to remember that when you are working with INSERT, UPDATE or DELETE queries you need to use executeUpdate() method and other wise executeQuery(); while the executeUpdate() returns the integer value which specifies the number of rows effecting the executeQuery() returns the resultSet object.

As you must have noticed that we have placed '?' on for the values and we have not even put the roll number value this is because the roll number is auto increment field and will increment itself with every new insert. But the question mark must be given a value. So we use the setString() method, which sets the value we pass as argument as the value of question mark, the first argument is an integer which specifies the question mark which is to be replaced.

Now See the program to retrieve the values from database and display on the console.


package jdbc;
import java.sql.*;
public class TestJDBC {
    public static void main(String[] args) {
        try {
            //call driver and create connection
            Class.forName("com.mysql.jdbc.Driver");
            Connection con = DriverManager.getConnection("jdbc:mysql://localhost/student", "root", "");

            //fetch values from table and display
            PreparedStatement ps = null;
            ps = con.prepareStatement("select * from student_info");
            ResultSet rs = ps.executeQuery();
            ResultSetMetaData rsmd = rs.getMetaData();
            for(int i=0; i < rsmd.getColumnCount();i++){
                System.out.print("| "+rsmd.getColumnName(i+1)+" |");
            }
            while (rs.next()) {
                System.out.println();
                System.out.print("| "+rs.getInt("rollnum")+" | "+rs.getString("name")+" | "+rs.getString("class")+" |");
                System.out.println();
            }
        }
        catch(ClassNotFoundException cnfe){
                System.out.println(cnfe);
        }
	catch(SQLException se){
		System.out.println(se);
	}
        catch(Exception e){
            System.out.println(e);
        }
    }
}

output of the program is as follows:

| rollnum || Name || Class |
| 1 | James | X |

| 2 | Kenny | X |

As there are two rows in database. So let us see what is in the code. First you see the process is nearly same up to preparing the statement. After that We execute the select statement which returns the result set. We have tried to cover up the the result set as well as result set meta data.

In the program when we execute the query we get the result set which contains the data. Before we result out the data we want to know the name of columns we have in database. The name of columns and the count of them are the information about the data we store so these comes in the category of meta data. To interpret the meta data we can use ResultSetMetadata interface.

As you see in the program, we have used the reference of ResultSetMetadata to get the number of counts and in for loop we also get the column name using method getColumnName(int) number of column. But in case of the fetching result we can use simple ResultSet. it provides the method next(), this advances the pointer every time it is called. And the reference of result set gives us access to overloaded getXXX methods which are used to retrieve the value of rows either using the column name or column number as we have used the column name in our program.

We have used catch chaining which will take care of any type of exception occurring in the program.