JAVA: More on JDBC

<< Java Database Connectivity

JAVA: Result Set >>

Web Design & Development CS506

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

Web Design & Development CS506

}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.):

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

Web Design & Development CS506

setMaxRows(int), the number of rows can be specified.

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

Web Design & Development CS506

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

Table of Contents: