Creating & Executing Statements - Java Database Connectivity

Home	»	Java	»	Advance Java	»	Java Database	»	Creating & Executing Statements

An object of the Statement interface is used to send SQL queries to the DBMS. This interface is available in the java.sql package of Java. Table 22.2 describes some of the methods of the Statement interface.

Method Name	Description
cancel()	Cancel a Statement object if the driver and DBMS support termination of the SQL statement. The Statement object being processed by one thread can be cancelled by another thread.
close()	Closes a Statement object and releases all the resources associated with the Statment object.
execute()	Executes the SQL Query associated with a Statement object that has a Boolean return type.
executeQuery()	Executes a SQL query associated with a Statement object that returns the database records in a ResultSet object.
executeUpdate()	Executes a SQL query associated with a Statement object that has an interger return type.
getConnection()	Returns the name of the Connection object that created the Statement object.
getMaxRows()	Returns the Maximum number of rows that the ResultSet object associated with the Statement object can contain.
getResultSet()	Returns the results of the SQL query associated with the Statement object in the ResultSet object.
setMaxRows()	Set the maximum number of rows that a ResultSet object can contain.
getWarnings()	Retrieves the first warning that is reported when the Statement object is called.
setQueryTimeOut()	Sets the time for which the driver waits for a Statement object to execute.

Table 22.2 Methods of the Statement Interface

You need to use the createStatement() method of the Statement interface to create a Statement object. This object enables you to send SQL queries to the database. The code to create a Statement object is:

Statement stmt=con.createStatement();

In the above code, con is an object of the Connection interface, which is used to establish a connection with the database by using the getConnection() method of the DriverManager class. The code returns the Statement objects.

After creating the Statement object, the SQL statement are executed to access and retrieve the rows from the database tables. You can use any of these methods, execute(), executeQuery() and executeUpdate() to execute SQL statements. The Syntax to use the execute() method is:

stmt.execute(<SQL_stmt>);

There is another method, executeQuery(), in the Statement interface that is used to execute a SQL statement that retrieves records from a database on the basis of the criteria specified in the statement. The Syntax to use the executeQuery() method is:

stmt.executeQuery(<SQL_stmt>);

In the above syntax, stmt is a Statment object and the SQL statement to be executed is passed as an argument to the executeQuery() method.

The executeUpdate() method is used to execute SQL Statements such as INSERT, UPDATE and DELETE. You can pass SQL queries as arguments to the executeUpdate() method. The syntax to use the executeUpdate() method to perform the insertion, update of deletion operation in a database is:

stmt.executeUpdate();

The information retrieved from a database when you execute a SQL Statement is called the ResultSet. The java.sql package contains the ResultSet interface that provides access to the retrieved data. A ResultSet uses a cursor that points to the currrent row in the retrieved data. Initially, this cursor points before the first row in the result set. The next() method moves the cursor to the next row in the result set. Table 22.3 describes the various methods of the ResultSet interface.

Method Name	Description
afterLast()	Moves the cursor to the last value in the ResultSet object.
beforeLast()	Moves the cursor to the first value in the ResultSet object.
cancelRowUpdates()	Cancels all the updates made to the current row of the ResultSet object.
close()	Closes the ResultSet object and releases all the resources associated with the ResultSet object.
deleteRow()	Deletes the records in the ResultSet objects from the ResultSet object and the database.
first()	Transfers the cursor to the first row of the ResultSet object.
getArray()	Retrieves the value of the database column in the current row of the ResultSet object in array.
GetBigDecimal()	Retrieves the value of the database column in the current row of the ResultSet object as a java.math.BigDecimal data type.
getBoolean()	Retrieves the value of the database column in the current row of the ResultSet object as a Boolean data type.
getByte()	Retrieves the value of the database column in the current row of the ResultSet object as a byte array.
getStatement()	Retrieves the Statement object that produced the ResultSet object.
isFirst()	Returns a Boolean value speciying whether the cursor is on the first row of the ResultSet object or not.
isLast()	Returns a Boolean value specifying whether the cursor is on the last row of the ResultSet object or not.
next()	Moves the cursor to the next row in the ResultSet object.
previous()	Moves the cursor to the previous row in the ResultSet object.
getBiob()	Retrieves the value of the database column in the current row of the ResultSet object as a BLOB type in Java.
getClob()	Retrieves the value of the database column in the current row of the ResultSet object as a CLOB type in Java.
getDate()	Retrieves the value of the database column in the current row of the ResultSet object as a date type in Java.
getDouble()	Retrieves the value of the database column in the current row of the ResultSet object as a double type in Java.
getFloat()	Retrieves the value of the database column in the current row of the ResultSet object as a float type in Java.
getInt()	Retrieves the value of the database column in the current row of the ResultSet object as a int type in Java.
getLong()	Retrieves the value of the database column in the current row of the ResultSet object as a long type in Java.
getShort()	Retrieves the value of the database column in the current row of the ResultSet object as a short type in Java.
GetString()	Retrieves the value of the database column in the current row of the ResultSet object as a string type in Java.
getTime()	Retrieves the value of the database column in the current row of the ResultSet object as a time type in Java.
getURL()	Retrieves the value of the database column in the current row of the ResultSet object as a URL object type in Java.
getWarnings()	Retrieves the first warning reported when the first call to this ResultSet object is made.
InsertRow()	Inserts a new row with the specified values in the ResultSet object and in the database.
isAfterLast()	Returns a Boolean value, which specifies that the cursor is after the last row in the ResultSet object or not.
isBeforeFirst()	Returns a Boolean value, which specifies that the cursor is after the first row in the ResultSet object or not.
last()	Moves the cursor to the last row in the ResultSet object.
rowInserted()	Returns a Boolean value that specifies whether the current row had an insertion operation or not.
rowDeleted()	Returns a Boolean value that specifies whether the rows have been deleted or not.
rowUpdated()	Returns a Boolean value that specifies whether the rows have been updated or not.
updateArray()	Updates the specified database column with an array value
updateBigDecimal()	Updates the specified database column with a BigDecimal value
updateBlob()	Updates the specified database column with a BLOB value
updateBoolean()	Updates the specified database column with a Boolean value
updateByte()	Updates the specified database column with a byte value
updateClob()	Updates the specified database column with a CLOB value
updateDate()	Updates the specified database column with a double value
updateFloat()	Updates the specified database column with a float value
updateInt()	Updates the specified database column with a integer value
updateLong()	Updates the specified database column with a long value
updateNull()	Updates the specified database column with a null value
updateObject()	Updates the specified database column with an object value
updateShort()	Updates the specified database column with a short value
updateString()	Updates the specified database column with a string value
updateTime()	Updates the specified database column with a time value
wasNull()	Returns whether the last column read was null or not.

Table 22.3 Methods of the ResultSet Interface

You need to create a ResultSet object to store the retrieved database records. The execute() method of the Statement interface executes the SQL query and returns the Boolean results in the ResultSet object. The executeQuery() method returns the database records in the ResultSet and the executeUpdate() method returns and integer value.

The following program code shows how to retrieve database records using SQL statements:

Retrieving Database Records:

/* =========================================================== File Name : MyDbTest.java WebSite : http://javaproglang.blogspot.in/ Facebook : https://www.facebook.com/AdvanceJavaProgramming Twitter ID : https://twitter.com/AdvanceJava Created By : Bintu Chaudhary =========================================================== */ import java.lang.*; import java.sql.*; class MyDbTest { public static void main(String args[]) throws Exception { String stu_rollno,stu_name,stu_class; //Creating Connection Object Connection con=null; //Creating Statement Object for executing SQL Statement Statement stmt; //Creating ResultSet Object for retrieving information from a database ResultSet rs; //Creating try block for exception handling try { //Initialistaion and loading of jdbc-odbc driver Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); //For establishing connection with a database con=DriverManager.getConnection("jdbc:odbc:MyDsn"); stmt=con.createStatement(); //Sending SQL Statement to the RDBMS for execution rs=stmt.executeQuery("Select * From StudentTable"); //Display Result System.out.println("===================================="); System.out.println("Records of Student Table"); System.out.println("===================================="); System.out.println("RollNo"+" "+"Name"+" "+"Clas"); System.out.println("-------------------------------------"); while(rs.next()) { stu_rollno = rs.getString("Stu_RollNO"); stu_name = rs.getString("Stu_Name"); stu_class = rs.getString("Stu_Class"); System.out.println(stu_rollno+" "+stu_name+" "+stu_class+"\n"); } stmt.close(); con.close(); } catch(Exception e) { System.out.println(e.getMessage()); } } }
The above code shows how to retrieve and display the records in the StudentTable table. The records are retrieved from the StudentTable table by using the executeQuery() method of the Statement interface. The retrieved records are stored in the ResultSet object, rs. The while loop moves cursor to the next record in the ResultSet and displays the record.

Figure 22.15 shows the employee information retrieved from the StudentTable table.

Diagram 22.15 Display Student Information

	Create the Database.
	Creating a DSN.
	Connecting to a Database.
	Creating & Executing Statements.
	Closing a database connection.

JDBC - Programming:

	View records from the Access/SQL table.
	Inserts records into the Access/SQL table.
	Updates records into the Access/SQL table.
	Deletes records from the Access/SQL table.
	Drops the Access/SQL table.

JDBC - Working With User Interfaces - Using Swing Compoents

	Inserts records into the Access/SQL table using Swing Components
	View records from the Access table/SQL using Swing JTable
	Updates records into the Access/SQL table using Swing Components
	Deletes records from the Access/SQL table using Swing Components