ZeePedia

JAVA: More on JDBC

<< Java Database Connectivity
JAVA: Result Set >>
img
Web Design & Development ­ CS506
VU
Lesson 15
More on JDBC
In the previous handout, we have discussed how to execute SQL statements. In this handout, we'll learn
how to execute DML (insert, update, delete) statements as well some useful methods provided by the JDBC
API.
Before jumping on to example, lets take a brief overview of executeUpdate()method that is used for
executing DML statements.
Useful Statement Methods:
o executeUpdate( )
. Used to execute for INSERT, UPDATE, or DELETE SQL statements.
. This method returns the number of rows that were affected in the database.
Also supports DDL (Data Definition Language) statements CREATE TABLE, DROP
..
TABLE, and ALERT TABLE etc. For example,
int num = stmt.executeUpdate("DELETE from Person WHERE id = 2" );
Example Code 15.1: Executing SQL DML Statements
This program will take two command line arguments that are used to update records in the database.
executeUpdate( ) method will be used to achieve the purpose stated above.
// File JdbcDmlEx.java
//step 1: import packageimport java.sql.*; public class
JdbcDmlEx {public static void main (String args[ ]) { try {
//Step 2: load driverClass.forName("sun.jdbc.odbc.JdbcOdbcDriver");
//Step 3: define the connection URL
String url = "jdbc:odbc:personDSN";
//Step 4: establish the connection
Connection con = DriverManager.getConnection(url);
//Step 5: create Statement
Statement st = con.createStatement();
// assigning first command line argument value
String addVar = args[0];
// assigning second command line argument value
String nameVar = args[1];
// preparing query ­ nameVar & addVar strings are embedded
// into query within `" + string + "'
String sql = "UPDATE Person SET address = `"+addVar+"'" +
" WHERE name = `"+nameVar+"' ";
// executing query
int num = st.executeUpdate(sql);
// Step 7: process the results of the query
// printing number of records affected
System.out.println(num + " records updated");
//Step 8: close the connection
con.close();
120
img
Web Design & Development ­ CS506
VU
}catch(Exception sqlEx){
System.out.println(sqlEx);
}
} // end main} // end class
Compile & Execute
The Person table is shown in the following diagram before execution of the program. We want to update
first row i.e address of the person ali.
The next diagram shows how we have executed our program. We passed it two arguments. The first one is
the address (defence) and later one is the name (ali) of the person against whom we want to update the
address value.
The Person table is shown in the following diagram after the execution of the program. Notice that address
of the ali is now changed to defence.
Note
When we execute DML statements (insert, update, delete) we have to commit it in the database explicitly to
make the changes permanent or otherwise we can rollback the previously executed statements.
But in the above code, you have never seen such a statement. This is due to the fact that java will implicitly
commit the changes. However, we can change this java behavior to manual commit. We will cover these in
some later handout.
Useful Statement Methods (cont.):
o
getMaxRows / setMaxRows(int)
.
Used for determines the number of rows a ResultSet may contain
.
By default, the number of rows are unlimited (return value is 0), or by using
121
img
Web Design & Development ­ CS506
VU
setMaxRows(int), the number of rows can be specified.
o
getQueryTimeOut / setQueryTimeOut (int)
.
Retrieves the number of seconds the driver will wait for a Statement object to execute.
.
The current query time out limit in seconds, zero means there is no limit
.
If the limit is exceeded, a SQLException is thrown
Different Types of Statements
.
As we have discussed in the previous handout that through Statement objects, SQL queries
are sent to the databases.
.
Three types of Statement objects are available. These are;
1. Statement
-The Statement objects are used for executing simple SQL statements. -We have already
seen its usage in the code examples.
2. PreparedStatement
-The PrepaeredStatement are used for executing precompiled SQL statements and passing in
different parameters to it.
-  We will talk about it in detail shortly.
3. CallableStatement
-  Theses are used for executing stored procedures.
-We are not covering this topic; See the Java tutorial on it if you are interested in learning it.
Prepared Statements
What if we want to execute same query multiple times by only changing parameters.
PreparedStatement object differs from Statement object as that it is used to create a statement in
standard form that is sent to database for compilation, before actually being used.
Each time you use it, you simply replace some of the marked parameters (?) using some setter
methods.
We can create PreparedStatement object by using prepareStatementmethod of the connection class.
The SQL query is passed to this method as an argument as shown below.
PreparedStatement pStmt = con.prepareStatement ("UPDATE tableName SET columnName = ? " +
"WHERE columnName = ? " );
Notices that we used marked parameters (?) in query. We will replace them later on by using
various setter methods.
If we want to replace first ? with String value, we use setString method and to replace second ?
with int value, we use setInt method. This is shown in the following code snippet.
pStmt.setString (1 , stringValue);
pStmt.setInt (2 , intValue)
Note: The first market parameter has index 1.
.
Next, we can call executeUpdate (for INSERT, UPDATE or DELETE queries) or executeQuery (for
simple SELECT query) method.
pStmt.executeUpdate();
Modify Example Code 15.1: Executing SQL DML using Prepared Statements
This example code is modification to the last example code (JdbcDmlEx.java).The modifications are
highlighted as bold face.
// File JdbcDmlEx.java
//step 1: import packageimport java.sql.*;
public class JdbcDmlEx {public static void main (String
args[ ]) { try {
//Step 2: load driverClass.forName("sun.jdbc.odbc.JdbcOdbcDriver");
//Step 3: define the connection URL
122
img
Web Design & Development ­ CS506
VU
String url = "jdbc:odbc:personDSN";
//Step 4: establish the connection
Connection con = DriverManager.getConnection(url, "", "");
// make query and place ? where values are to
//be inserted later
String sql =
"UPDATE Person SET address = ? " + " WHERE name = ?
";
// creating statement using Connection object and passing // sql statement as parameter
PreparedStatement pStmt = con.prepareStatement(sql);
// assigning first command line argument valueString addVar = args[0];
// assigning second command line argument valueString nameVar = args[1]; //
setting first marked parameter (?) by using setString()// method to address.
pStmt.setString(1 , addVar);
// setting second marked parameter(?) by using setString()// method to name
pStmt.setString(2 , nameVar);
// suppose address is "defence" & name is "ali"
// by setting both marked parameters, the query will look
// like:
// sql = "UPDATE Person SET address = "defence"
// WHERE name = "ali" "
// executing update statemnt
int num = pStmt.executeUpdate();
// Step 7: process the results of the query// printing number of records
affectedSystem.out.println(num + " records updated");
//Step 8: close the connection
con.close();
}catch(Exception sqlEx){
System.out.println(sqlEx);
}
} // end main} // end class
Compile & Execute
Execute this code in a similar way as we showed you in execution of the last program. Don't forget to pass
the address & name values as the command line arguments.
References:
Entire material for this handout is taken from the book JAVA A Lab Course by Umair Javed. This
material is available just for the use of VU students of the course Web Design and Development and not for
any other commercial purpose without the consent of author.
123