Communicating with DataBase Sever

Up to now we have seen how to register the driver and how to establish the connection with DB server.  Now let us see how to communicate with the database using java application. We can send the query to the database using the Statement object. Let us see how to create this object. Follow the below steps.

Goto JSE API - java.sql package - Connection Interface - Method Summary - createStatement(). The following figure shows the method. 

Fig 1: createStatement() method in JSE API

In the above figure, createStatement() method returns a Statement class object. The syntax for the method is as follows:

• Statement stmt=con.createStatement();   

By using the above statement we can just create a Statement object. After creating the statement object we should send the query to the database. This can be done by the following two methods, which are present in Statement Interface. The following figure shows the two methods in JSE API.

Fig 2: Methods to send queries to Database

Generally java have divided the SQL statements into two types, they are:

1. select statements (SELECT * from Tab_name;)
2. non- select statements (other than SELECT like INSERT, UPDATE, CREATE etc,,,)

To send the non-select queries to the database we use executeUpdate(query) method. Since these methods are present in the Statement Interface, they are called by using Statement class object. The syntax for this method is as follows:

•  stmt . executeUpdate(SQL query);

Let us discuss executeQuery() method later. The following figure shows the java code for creating Statement object to send SQL queries to the database. 

Fig 3: program to Insert records into database.

Just observe the  executeUpdate() method in Fig 2. The return type of this method is int. That means this method returns the no.of records inserted or deleted or created etc..., as an integer. 

 Now let us discuss about executeQuery() method. This method is used to send the select queries to the database. The following is the syntax of the executeQuery();

• stmt . executeQuery(SQL query);

 This method is used to retrieve the data from the database. This method returns the ResultSet object. The retrieved data is stored in the ResultSet object. To retrieve the data from the database we uses SELECT query. To send the select query to the database, first the Java Application is sends the query to the JDBC driver, it is the responsibility of the JDBC driver to send the query to the database server.Now, DBServer returns the ResultSet object to the JDBC driver, This ResultSet object is sent to the Java application by the driver.The following figure shows the process of retrieving the data from the database.

Fig 4: Process of retrieving data form database.

In the above figure we are frequently exposed to a word ResultSet Object. This is the object that contains the retrieved data, but when we print this ResultSet object, we cannot see the retrieved data. The following program shows this.

Fig 5: Program to retrieve records from DBserver.

 The result of the above program is as follows.

Fig 6: ResultSet object as Output.

We got the ResultSet object, but we want the records presented in that. This ResultSet object is associated with a ResultSet pointer which initially points to the zeroth record. The following figure shows the ResultSet pointer.

Fig 7: shows ResultSet pointer, pointing to zeroth record.

To move this pointer to the next record we should use a method next(). This method returns a boolean value. The following is the syntax.

Fig 8: next() method syntax.

The following figure shows the program to extract the data from ResultSet object. 

Fig 9: program to extract data from rs object

Now let us try to execute this non-select query using executeQuery() method. The following figure shows the program:

Fig 10: executing Non-Select query using executeQuery().

The above program executes with no error, and even it inserts the records.