The Connection interface defines the following methods to obtain statement objects.
Statement createStatement()
Statement createStatement(int resultSetType,
int resultSetConcurrency)
Statement createStatement(int resultSetType,
int resultSetConcurrency, int resultSetHoldability)
PreparedStatement prepareStatement(java.lang.String)
PreparedStatement prepareStatement(String sql,
int resultSetType,
int resultSetConcurrency)
PreparedStatement prepareStatement(String sql,
int resultSetType,
int resultSetConcurrency,
int resultSetHoldability)
CallableStatement prepareCall(java.lang.String)
CallableStatement prepareCall(String sql,
int resultSetType,
int resultSetConcurrency)
CallableStatement prepareCall(String sql,
int resultSetType,
int resultSetConcurrency,
int resultSetHoldability)
The JDBC Statement, CallableStatement, and PreparedStatement interfaces define the methods and properties that enable you to send SQL or PL/SQL commands and receive data from your database.
1. Simple Statement
The Statement interface is used to execute static SQL statements A Statement object is instantiated using the createStatement() method on the Connection object as follows:
Statement stmt = con.createStatement();
This Statement object defines the following methods to fire different types of SQL commands on the database.
executeUpdate()
This method is used to execute DDL (CREATE, ALTER, and DROP), DML (INSERT, DELETE, UPDATE, etc.) and DCL statements. In general, if an SQL command changes the database, the executeUpdate() method is used. The return value of this method is the number of rows affected.
Assume that stmt is a Statement object. The following code segment first creates a table named accounts.
Once the table is created, data can be inserted into it using the following code segment.
String Insert = “INSERT INTO accounts VALUES(1,’Uttam K. Roy’, 10000)”;
stmt.executeUpdate(insert);
insert = “INSERT INTO accounts VALUES(2,’Bibhas Ch. Dhara’, 20000)”;
stmt.executeUpdate(insert);
The following JSP page (DDL.jsp) creates a table accounts and insert two rows in the table. Make sure that the MySQL JDBC driver is in the / lib directory under the Tomcat installation directory.
<!–DDL.jsp–>
<%@page import=”java.sql.*”%>
<%
try {
new com.mysql.jdbc.Driver();
String url = ”jdbc:mysql://uroy:3306/test?user=root&password=nbuser”;
Connection conn = DriverManager.getConnection(url);
Statement stmt = conn.createStatement();
String create = “CREATE TABLE accounts ( ” +
” accNum integer primary key, ” +
” holderName varchar(20), ” +
” balance integer ” +
“)”;
stmt.executeUpdate(create);
String insert = “INSERT INTO accounts VALUES(1,’Uttam K. Roy’, 10000)”;
stmt.executeUpdate(insert);
insert = “INSERT INTO accounts VALUES(2,’Bibhas Ch. Dhara’, 20000)”;
stmt.executeUpdate(insert);
stmt.close();
conn.close();
out.println(“Created table accounts”);
} catch (Exception e) { out.println(e);
}
%>
executeQuery()
This is used for DQL statements such as SELECT. Remember, DQL statements only read data from database tables; it cannot change database tables. So, the return value of this method is a set of rows that is represented as a ResuitSet object.
The result of the executeQuery method is stored in an object of type ResuitSet. This result set object looks very much similar to a table and hence has a number of rows. A particular row is selected by setting a cursor associated with this result set. A cursor is something like a pointer to the rows. Once the cursor is set to a particular row, individual columns are retrieved using the methods provided by the ResuitSet interface. The cursor is placed before the first row of result set when it is created first. JDBC 1.0 allows us to move the cursor only in the forward direction using the method next(). JDBC 2.0 allows us to move the cursor both forward and backward as well as to move to a specified row relative to the current row. These types of result sets are called scrollable result sets, which will be discussed in Section 22.12.
Since the cursor does not point to any row (it points to a position before the first row), users have the responsibility to set the cursor to a valid row to retrieve data from it. To retrieve data from a column, methods of the form getX() are used, where X is the data type of the column. The following is an example that retrieves information from the accounts table created earlier.
String query = “SELECT * FROM accounts”;
ResuitSet rs = stmt.executeQuery(query);
while(rs.next()) {
out.println(rs.getString(“accNum”));
out.println(rs.getString(“holderName”));
out.println(rs.getString(“balance”));
}
execute()
Sometimes, users want to execute SQL statements whose type (DDL, DML, DCL, or DQL) is not known in advance. This may happen particularly when statements are obtained from another program. In that case, users cannot decide which method they should use. In such cases, the executes method is used. It can be used to execute any SQL commands. Since it allows us to execute any SQL commands, the result can either be a ResuitSet object or an integer. However, how does a user know it? Fortunately, this method returns a Boolean value, which indicates the return type. The return value true indicates that the result is a ResuitSet object, which can be obtained by calling its getResuitSet() method. On the other hand, if the return value is faise, the result is an update count, which can be obtained by calling the getUpdateCount() method.
The following JSP page (execute.jsp) takes an arbitrary SQL statement as a parameter and fires this SQL statement on the database.
<!–execute.jsp–>
<%@page import=”java.sql.*”%>
<%
response.setHeader(“Pragma”, “no-cache”);
response.setHeader(“Cache-Control”, “no-cache”);
response.setDateHeader(“Expires”, -1);
try {
String query = request.getParameter(“sql”);
if (query != null) {
new com.mysql.jdbc.Driver();
String url = “jdbc:mysql://uroy:3306/test”;
Connection con = DriverManager.getConnection(url, “root”, “nbuser”);
Statement stmt = con.createStatement();
if (stmt.execute(query) == false) {
out.println(stmt.getUpdateCount() + ” rows affected”);
}
else {
ResultSet rs = stmt.getResultSet();
ResultSetMetaData md = rs.getMetaData(); out.println(“<table border=\”1\”><tr>”);
for (int i = 1; i <= md.getColumnCount(); i++) {
out.print(“<th>” + md.getColumnName(i) + “</th>”);
}
out.println(“</tr>”); while (rs.next()) {
out.println(“<tr>”);
for (int i = 1; i <= md.getColumnCount(); i++) {
out.print(“<td>” + rs.getString(i) + “</td>”);
}
out.println(“</tr>”);
}
out.println(“</table>”);
rs.close();
}
stmt.close();
con.close();
}
}
catch (Exception e) {
out.println(e);
}
%>
<form name=”sqlForm” method=”post”>
SQL statement:<br><input type=”text” name=”sql” size=”50″><br />
<input type=”reset”><input type=”submit” value=”Execute”>
</form>
Note that this JSP page allows you to fire any valid SQL query, including DML. So, the JSP page must perform user verification before providing this interface.
2. Atomic Transaction
The database transaction made by the executeUpdate() method is committed automatically. This may lead to data inconsistency if a series of related statements are executed. Consider the following simple table for a banking application.
accounts(accNum, holderName, balance)
The bank manager wants to write a Java program to transfer some amount of money amount from the source account src to the destination account dst. The basic task of this program will be to subtract amount from the source account balance and add amount to the destination account balance. A sample JSP page looks like this:
<!–manager.jsp–>
<%@page import=”java.sql.*”%>
<% !
Connection con;
Statement stmt;
String query; public void jspInit() {
try {
new com.mysql.jdbc.Driver();
String url = “jdbc:mysql://uroy:3306/test”;
con = DriverManager.getConnection(url, “root”, “nbuser”);
stmt = con.createStatement();
}catch(Exception e) {}
}
public boolean transfer(int src, int dst, int amount) {
try {
query = “SELECT balance FROM accounts WHERE accNum=” + src;
ResultSet rs = stmt.executeQuery(query); rs.next();
int srcBal = rs.getInt(“balance”) – amount;
query = “SELECT balance FROM accounts WHERE accNum=” + dst;
rs = stmt.executeQuery(query);
rs.next();
int dstBal = rs.getInt(“balance”) + amount;
return doTransfer(src, dst, srcBal, dstBal);
} catch (SQLException e) { return false;
}
}
public boolean doTransfer(int src, int dst, int srcBal, int dstBal) {
try {
query = “UPDATE accounts SET balance=” + srcBal + ” WHERE accNum=” + src;
stmt.executeUpdate(query);
query = “UPDATE accounts SET balance=” + dstBal + ” WHERE accNum=” + dst;
//If anything goes wrong here, destination account will contain wrong //result.
stmt.executeUpdate(query); return true;
} catch (SQLException e) { return false;
}
}
%>
<%
try {
int src = Integer.parseInt(request.getParameter(“src”)); int dst =
Integer.parseInt(request.getParameter(“dst”));
int amount = Integer.parseInt(request.getParameter(”amount”));
transfer(src, dst, amount);
}catch(Exception e) {out.println(e);}
%>
Note that source and destination accounts must be updated atomically. However, if anything goes wrong after updating the source account and before updating the destination account in the doTransfer() method, the destination account will hold an incorrect balance.
This problem can be solved using the autoCommit() method available on the Connection object. First the autoCommit flag of the Connection object is set to false. At the end of execution of all related statements, the transaction is committed. If anything goes wrong during the execution of those statements, it can be caught and the transaction gets rolled back accordingly. This way a set of related operations can be performed atomically.
The correct doTransfer() method looks like this:
public boolean doTransfer(int src, int dst, int srcBal, int dstBal) {
try {
con.setAutoCommit(false);
query = “UPDATE accounts SET balance=” + srcBal + ” WHERE accNum=” + src;
stmt.executeUpdate(query);
query = “UPDATE accounts SET balance=” + dstBal + ” WHERE accNum=” + dst;
stmt.executeUpdate(query);
con.commit();
return true;
} catch (SQLException e) {
try {
con.rollback();
} catch (SQLException e1) {
}
return false;
}
}
executeBatch()
This method allows us to execute a set of related commands as a whole. Commands to be fired on the database are added to the Statement object one by one using the method addBatch(). It is always safe to clear the Statement object using the method ciearBatch() before adding any command to it. Once all commands are added, executeBatch() is called to send them as a unit to the database. The DBMS executes the commands in the order in which they were added. Finally, if all commands are successful, it returns an array of update counts. To allow correct error handling, we should always set auto-commit mode to false before beginning a batch command.
The following is the method doTransfer, rewritten using this mechanism.
public boolean doBatchTransfer(int src, int dst, int srcBal, int dstBal) {
try {
String query;
con.setAutoCommit(false);
stmt.clearBatch();
query = “UPDATE accounts SET balance=” + srcBal + ” WHERE accNum=” + src;
stmt.addBatch(query);
query = “UPDATE accounts SET balance=” + dstBal + ” WHERE accNum=” + dst;
stmt.addBatch(query); stmt.executeBatch(); con.commit();
return true;
} catch (SQLException e) {
try {
con.rollback();
} catch (SQLException el) {
}
return false;
}
}
Since all the commands are sent as a unit to the database for execution, it improves the performance significantly.
3. Pre-compiled Statement
When an SQL statement is fired to the database for execution using the Statement object the following steps get executed:
- DBMS checks the syntax of the statement being submitted.
- If the syntax is correct, it executes the statement.
DBMS compiles every statement unnecessarily, even if users want to execute the same SQL statement repeatedly with different data items. This creates significant overhead, which can be avoided using the PreparedStatement object.
A PreparedStatement object is created using the prepareStatement() method of the Connection object. An SQL statement with placeholders (?) is supplied to the method Connection. prepareStatement() when a PreparedStatement object is created. This SQL statement, together with the placeholders is sent to the DBMS. DMBS, in turn, compiles the statement and if everything is correct, a PreparedStatement object is created. This means that a PreparedStatement object contains an SQL statement whose syntax has already been checked and hence is called pre-compiled statement. This SQL statement is then fired repeatedly, with placeholders substituted by different data items. Note that PreparedStatement is useful only if the same SQL statement is executed repeatedly with different parameters. Otherwise, it behaves exactly like Statement and no benefit can be obtained.
The following example creates a PreparedStatement object:
PreparedStatement ps = con.prepareStatement(”INSERT INTO user values(?,?)”);
The SQL statement has two placeholders, whose values will be supplied whenever this statement is sent for execution. Placeholders are substituted using methods of the form setX(), where X is the data type of the value used to substitute. These methods take two parameters. The first parameter indicates the index of the placeholder to be substituted and the second one indicates the value to be used for substitution. The following example substitutes the first placeholder with “user1”.
ps.setString(1, ”user1”);
Consider a file, question.txt, which contains questions of the form question_no:question_ string as follows:
1:What is the full form of JDBC?
2:How is a PreparedStatement created?
…
The following example inserts questions and their numbers stored in this file in the table questions.
PreparedStatement ps = con.prepareStatement(“INSERT INTO questions values(?,?)”);
BufferedReader br = new BufferedReader(new InputStreamReader(new
FileInputStream(”question.txt”)));
String line = br.readLine(); while (line != null) {
StringTokenizer st = new StringTokenizer(line, ”:”);
String qno = st.nextToken();
String question = st.nextToken();
ps.setString(1, qno);
ps.setString(2, question);
ps.executeUpdate();
line = br.readLine();
}
PreparedStatement has another important role in executing parameterized SQL statements. Consider this solution using the Statement object.
Statement stmt = con.createStatement();
BufferedReader br = new BufferedReader(new InputStreamReader(new
FileInputStream(application.getRealPath(“/”)+”question.txt”)));
String line = br.readLine(); while (line != null) {
StringTokenizer st = new StringTokenizer(line, “:”);
String qno = st.nextToken();
String question = st.nextToken();
String query = “INSERT INTO questions values(“+qno+”,'”+question+”‘)”;
stmt.executeUpdate(query);
line = br.readLine();
}
This code segment will work fine, provided the question does not contain characters such as “’”. For example, if the file question.txt contains a line “3:What’s JDBC?”, the value of the query will be
INSERT INTO questions values(3,’What’s JDBC?’)
This is an invalid query due the “’” character in the word “What’s”. If you try, you will get an error message like this:
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax;
check the manual that corresponds to your MySQL server version for the right syntax to use near ‘s JDBC?’)’ at line 1
PreparedStatement can handle this situation very easily, as it treats the entire parameter as input. So, the example given using the PreparedStatement will work correctly in this case.
4. SQL Statements to Call Stored Procedures
JDBC also allows the calling of stored procedures that are stored in the database server. This is done using the CallableStatement object. A CallableStatement object is created using the
prepareCall() method on a Connection object.
CallableStatement prepareCall(String)
The method prepareCall() takes a primary string parameter, which represents the procedure to be called, and returns a CallableStatement object, which is used to invoke stored procedures if the underlying database supports them. Here is an example:
String proCall = “{call changePassword(?, ?, ?)}”;
CallableStatement cstmt = con.prepareCall(proCall);
The variable cstmt can now be used to call the stored procedure changePassword, which has three input parameters and no result parameter. The type of ? placeholders (in, out, or inout) depends on the definition of the stored procedure changePassword.
JDBC API allows the following syntax to call stored procedures:
{call procedure-name [(?, ?, …)]}
For example, to call a stored procedure with no parameter and no return type, the following syntax is used:
{call procedure-name}
The following syntax is used to call a procedure that takes a single parameter:
{call procedure-name(?)}
If a procedure returns a value, the following syntax is used:
{? = call procedure-name (?, ?)}
MySQL procedures are not allowed to return values. So, the last format is not allowed in MySQL. The web developer must know what stored procedures are available in the underlying database. Before using any stored procedure, one can use the supportsStoredProcedures() method on the DatabaseMetaData object to verify if the underlying database supports the stored procedure. If it supports, the description of the stored procedures can be obtained using getProcedures() on the DatabaseMetaData object. Consider the following procedure created in MySQL.
DELIMITER //
CREATE PROCEDURE changePassword(IN loginName varchar(10), IN oldPassword
varchar(10), IN newPassword varchar(10))
BEGIN
DECLARE old varchar(10);
SELECT password INTO @old FROM users WHERE login=loginName;
IF @old = oldPassword THEN
UPDATE users SET password=newPassword WHERE login=loginName;
END IF;
END //
DELIMITER ;
This procedure changes the password of a specified user in the table users. It takes three parameters: the login id of the user whose password has to be changed, the old password, and a new password.
If you are using MySQL command prompt to create a procedure, you may face a problem. Note that the stored procedures use “;” as the delimiter. The default MySQL statement delimiter is also “;”. This would make the SQL in the stored procedure syntactically invalid. The solution is to temporarily change the command-line utility delimiter using the following command;
DELIMITER //
This command delimiter // is not related to the stored procedure. The delimiter statement is used to change the standard delimiter (semicolon) to another. In this case, the delimiter is changed to //, so that you can have multiple SQL statements separated by the semicolon inside stored procedure. After the end keyword, we use delimiter // to show the end of the stored procedure. The last command delimiter ; changes the delimiter back to the standard one (semicolon).
The in parameters are passed to a CallableStatement object using methods of the form setXxx(). For example, setFloat() and setBoolean() methods are used to pass float and boolean values, respectively.
The following code segment illustrates how to call this procedure.
<!–CallableStatement.jsp–>
<%@page import=”java.sql.*”%>
<%
try {
new com.mysql.jdbc.Driver();
String url = “jdbc:mysql://uroy:3306/test”;
Connection con = DriverManager.getConnection(url, “root”, “nbuser”);
String proCall = “{call changePassword(?, ?, ?)}”;
CallableStatement cstmt = con.prepareCall(proCall);
String login = request.getParameter(“login”);
String oldPassword = request.getParameter(“oldPassword”);
String newPassword = request.getParameter(“newPassword”);
cstmt.setString(1, login);
cstmt.setString(2, oldPassword);
cstmt.setString(3, newPassword);
if (cstmt.executeUpdate() > 0) {
out.println(”Password changed successfully”);
} else {
out.println(”Couldn’t change password”);
}
cstmt.close();
conn.close();
} catch (Exception e) { out.println(e);
}
%>
The following URL may be used to change the password for the user from passl to newPassl:
http://127.0.0.1:8080/net/jdbc/CallableStatement.jsp?login=user1&oldPassword=pa
ss1&newPassword=newPass1
Make sure that the table was created and populated with values as shown below:
String create = “CREATE TABLE users ( “+
” login varchar(20) primary key, “+
” password varchar(20)”+
“)”;
stmt.executeUpdate(create);
String insert = “INSERT INTO users VALUES(‘user1′,’pass1’)”;
stmt.executeUpdate(insert);
insert = “INSERT INTO users VALUES(‘user2′,’pass2’) “;
stmt.executeUpdate(insert);
The CallableStatement object also allows batch update exactly like PreparedStatement.
The following is an example:
String proCall = “{call changePassword(?, ?, ?)}”;
CallableStatement cstmt = con.prepareCall(proCall);
cstmt.setString(1, ”user1”);
cstmt.setString(2, ”pass1”);
cstmt.setString(3, ”newPass1”);
cstmt.addBatch();
cstmt.setString(1, ”user2”);
cstmt.setString(2, ”pass2”);
cstmt.setString(3, ”newPass2”);
cstmt.addBatch();
int [] updateCounts = cstmt.executeBatch();
This example illustrates how to use batch update facility to associate two sets of parameters with a CallableStatement object.
Source: Uttam Kumar Roy (2015), Advanced Java programming, Oxford University Press.