[+/-]
This section provides some general JDBC background.
When you are using JDBC outside of an application server, the
DriverManager
class manages the
establishment of Connections.
The DriverManager
needs to be told which
JDBC drivers it should try to make Connections with. The
easiest way to do this is to use
Class.forName()
on the class that
implements the java.sql.Driver
interface.
With MySQL Connector/J, the name of this class is
com.mysql.jdbc.Driver
. With this method,
you could use an external configuration file to supply the
driver class name and driver parameters to use when connecting
to a database.
The following section of Java code shows how you might
register MySQL Connector/J from the
main()
method of your application. If
testing this code please ensure you read the installation
section first at Section 20.3.2, “Connector/J Installation”, to
make sure you have connector installed correctly and the
CLASSPATH
set up. Also, ensure that MySQL
is configured to accept external TCP/IP connections.
import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; // Notice, do not import com.mysql.jdbc.* // or you will have problems! public class LoadDriver { public static void main(String[] args) { try { // The newInstance() call is a work around for some // broken Java implementations Class.forName("com.mysql.jdbc.Driver").newInstance(); } catch (Exception ex) { // handle the error } } }
After the driver has been registered with the
DriverManager
, you can obtain a
Connection
instance that is connected to a
particular database by calling
DriverManager.getConnection()
:
Example 20.1. Connector/J: Obtaining a connection from the
DriverManager
If you have not already done so, please review the section
Section 20.3.5.1.1, “Connecting to MySQL Using the DriverManager
Interface”
before working with these examples.
This example shows how you can obtain a
Connection
instance from the
DriverManager
. There are a few different
signatures for the getConnection()
method. You should see the API documentation that comes with
your JDK for more specific information on how to use them.
import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; Connection conn = null; ... try { conn = DriverManager.getConnection("jdbc:mysql://localhost/test?" + "user=monty&password=greatsqldb"); // Do something with the Connection ... } catch (SQLException ex) { // handle any errors System.out.println("SQLException: " + ex.getMessage()); System.out.println("SQLState: " + ex.getSQLState()); System.out.println("VendorError: " + ex.getErrorCode()); }
Once a Connection
is established, it
can be used to create Statement
and
PreparedStatement
objects, as well as
retrieve metadata about the database. This is explained in
the following sections.
Statement
objects allow you to execute
basic SQL queries and retrieve the results through the
ResultSet
class which is described later.
To create a Statement
instance, you
call the createStatement()
method on the
Connection
object you have retrieved via
one of the DriverManager.getConnection()
or
DataSource.getConnection()
methods
described earlier.
Once you have a Statement
instance, you
can execute a SELECT
query by
calling the executeQuery(String)
method
with the SQL you want to use.
To update data in the database, use the
executeUpdate(String SQL)
method. This
method returns the number of rows affected by the update
statement.
If you do not know ahead of time whether the SQL statement
will be a SELECT
or an
UPDATE
/INSERT
,
then you can use the execute(String SQL)
method. This method will return true if the SQL query was a
SELECT
, or false if it was an
UPDATE
,
INSERT
, or
DELETE
statement. If the
statement was a SELECT
query,
you can retrieve the results by calling the
getResultSet()
method. If the statement
was an UPDATE
,
INSERT
, or
DELETE
statement, you can
retrieve the affected rows count by calling
getUpdateCount()
on the
Statement
instance.
Example 20.2. Connector/J: Using java.sql.Statement to execute a
SELECT
query
import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.Statement; import java.sql.ResultSet; // assume that conn is an already created JDBC connection (see previous examples) Statement stmt = null; ResultSet rs = null; try { stmt = conn.createStatement(); rs = stmt.executeQuery("SELECT foo FROM bar"); // or alternatively, if you don't know ahead of time that // the query will be a SELECT... if (stmt.execute("SELECT foo FROM bar")) { rs = stmt.getResultSet(); } // Now do something with the ResultSet .... } catch (SQLException ex){ // handle any errors System.out.println("SQLException: " + ex.getMessage()); System.out.println("SQLState: " + ex.getSQLState()); System.out.println("VendorError: " + ex.getErrorCode()); } finally { // it is a good idea to release // resources in a finally{} block // in reverse-order of their creation // if they are no-longer needed if (rs != null) { try { rs.close(); } catch (SQLException sqlEx) { } // ignore rs = null; } if (stmt != null) { try { stmt.close(); } catch (SQLException sqlEx) { } // ignore stmt = null; } }
Starting with MySQL server version 5.0 when used with
Connector/J 3.1.1 or newer, the
java.sql.CallableStatement
interface is
fully implemented with the exception of the
getParameterMetaData()
method.
For more information on MySQL stored procedures, please refer to http://dev.mysql.com/doc/mysql/en/stored-routines.html.
Connector/J exposes stored procedure functionality through
JDBC's CallableStatement
interface.
Current versions of MySQL server do not return enough
information for the JDBC driver to provide result set
metadata for callable statements. This means that when using
CallableStatement
,
ResultSetMetaData
may return
NULL
.
The following example shows a stored procedure that returns
the value of inOutParam
incremented by 1,
and the string passed in via inputParam
as
a ResultSet
:
Example 20.3. Connector/J: Calling Stored Procedures
CREATE PROCEDURE demoSp(IN inputParam VARCHAR(255), \ INOUT inOutParam INT) BEGIN DECLARE z INT; SET z = inOutParam + 1; SET inOutParam = z; SELECT inputParam; SELECT CONCAT('zyxw', inputParam); END
To use the demoSp
procedure with
Connector/J, follow these steps:
Prepare the callable statement by using
Connection.prepareCall()
.
Notice that you have to use JDBC escape syntax, and that the parentheses surrounding the parameter placeholders are not optional:
Example 20.4. Connector/J: Using Connection.prepareCall()
import java.sql.CallableStatement; ... // // Prepare a call to the stored procedure 'demoSp' // with two parameters // // Notice the use of JDBC-escape syntax ({call ...}) // CallableStatement cStmt = conn.prepareCall("{call demoSp(?, ?)}"); cStmt.setString(1, "abcdefg");
Connection.prepareCall()
is an
expensive method, due to the metadata retrieval that the
driver performs to support output parameters. For
performance reasons, you should try to minimize
unnecessary calls to
Connection.prepareCall()
by reusing
CallableStatement
instances in
your code.
Register the output parameters (if any exist)
To retrieve the values of output parameters (parameters
specified as OUT
or
INOUT
when you created the stored
procedure), JDBC requires that they be specified before
statement execution using the various
registerOutputParameter()
methods in
the CallableStatement
interface:
Example 20.5. Connector/J: Registering output parameters
import java.sql.Types; ... // // Connector/J supports both named and indexed // output parameters. You can register output // parameters using either method, as well // as retrieve output parameters using either // method, regardless of what method was // used to register them. // // The following examples show how to use // the various methods of registering // output parameters (you should of course // use only one registration per parameter). // // // Registers the second parameter as output, and // uses the type 'INTEGER' for values returned from // getObject() // cStmt.registerOutParameter(2, Types.INTEGER); // // Registers the named parameter 'inOutParam', and // uses the type 'INTEGER' for values returned from // getObject() // cStmt.registerOutParameter("inOutParam", Types.INTEGER); ...
Set the input parameters (if any exist)
Input and in/out parameters are set as for
PreparedStatement
objects. However,
CallableStatement
also supports
setting parameters by name:
Example 20.6. Connector/J: Setting CallableStatement
input
parameters
... // // Set a parameter by index // cStmt.setString(1, "abcdefg"); // // Alternatively, set a parameter using // the parameter name // cStmt.setString("inputParameter", "abcdefg"); // // Set the 'in/out' parameter using an index // cStmt.setInt(2, 1); // // Alternatively, set the 'in/out' parameter // by name // cStmt.setInt("inOutParam", 1); ...
Execute the CallableStatement
, and
retrieve any result sets or output parameters.
Although CallableStatement
supports
calling any of the Statement
execute methods (executeUpdate()
,
executeQuery()
or
execute()
), the most flexible method
to call is execute()
, as you do not
need to know ahead of time if the stored procedure returns
result sets:
Example 20.7. Connector/J: Retrieving results and output parameter values
... boolean hadResults = cStmt.execute(); // // Process all returned result sets // while (hadResults) { ResultSet rs = cStmt.getResultSet(); // process result set ... hadResults = cStmt.getMoreResults(); } // // Retrieve output parameters // // Connector/J supports both index-based and // name-based retrieval // int outputValue = cStmt.getInt(2); // index-based outputValue = cStmt.getInt("inOutParam"); // name-based ...
Before version 3.0 of the JDBC API, there was no standard way
of retrieving key values from databases that supported auto
increment or identity columns. With older JDBC drivers for
MySQL, you could always use a MySQL-specific method on the
Statement
interface, or issue the query
SELECT LAST_INSERT_ID()
after issuing an
INSERT
to a table that had an
AUTO_INCREMENT
key. Using the
MySQL-specific method call isn't portable, and issuing a
SELECT
to get the
AUTO_INCREMENT
key's value requires another
round-trip to the database, which isn't as efficient as
possible. The following code snippets demonstrate the three
different ways to retrieve AUTO_INCREMENT
values. First, we demonstrate the use of the new JDBC-3.0
method getGeneratedKeys()
which is now
the preferred method to use if you need to retrieve
AUTO_INCREMENT
keys and have access to
JDBC-3.0. The second example shows how you can retrieve the
same value using a standard SELECT
LAST_INSERT_ID()
query. The final example shows how
updatable result sets can retrieve the
AUTO_INCREMENT
value when using the
insertRow()
method.
Example 20.8. Connector/J: Retrieving AUTO_INCREMENT
column values
using Statement.getGeneratedKeys()
Statement stmt = null; ResultSet rs = null; try { // // Create a Statement instance that we can use for // 'normal' result sets assuming you have a // Connection 'conn' to a MySQL database already // available stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_UPDATABLE); // // Issue the DDL queries for the table for this example // stmt.executeUpdate("DROP TABLE IF EXISTS autoIncTutorial"); stmt.executeUpdate( "CREATE TABLE autoIncTutorial (" + "priKey INT NOT NULL AUTO_INCREMENT, " + "dataField VARCHAR(64), PRIMARY KEY (priKey))"); // // Insert one row that will generate an AUTO INCREMENT // key in the 'priKey' field // stmt.executeUpdate( "INSERT INTO autoIncTutorial (dataField) " + "values ('Can I Get the Auto Increment Field?')", Statement.RETURN_GENERATED_KEYS); // // Example of using Statement.getGeneratedKeys() // to retrieve the value of an auto-increment // value // int autoIncKeyFromApi = -1; rs = stmt.getGeneratedKeys(); if (rs.next()) { autoIncKeyFromApi = rs.getInt(1); } else { // throw an exception from here } rs.close(); rs = null; System.out.println("Key returned from getGeneratedKeys():" + autoIncKeyFromApi); } finally { if (rs != null) { try { rs.close(); } catch (SQLException ex) { // ignore } } if (stmt != null) { try { stmt.close(); } catch (SQLException ex) { // ignore } } }
Example 20.9. Connector/J: Retrieving AUTO_INCREMENT
column values
using SELECT LAST_INSERT_ID()
Statement stmt = null; ResultSet rs = null; try { // // Create a Statement instance that we can use for // 'normal' result sets. stmt = conn.createStatement(); // // Issue the DDL queries for the table for this example // stmt.executeUpdate("DROP TABLE IF EXISTS autoIncTutorial"); stmt.executeUpdate( "CREATE TABLE autoIncTutorial (" + "priKey INT NOT NULL AUTO_INCREMENT, " + "dataField VARCHAR(64), PRIMARY KEY (priKey))"); // // Insert one row that will generate an AUTO INCREMENT // key in the 'priKey' field // stmt.executeUpdate( "INSERT INTO autoIncTutorial (dataField) " + "values ('Can I Get the Auto Increment Field?')"); // // Use the MySQL LAST_INSERT_ID() // function to do the same thing as getGeneratedKeys() // int autoIncKeyFromFunc = -1; rs = stmt.executeQuery("SELECT LAST_INSERT_ID()"); if (rs.next()) { autoIncKeyFromFunc = rs.getInt(1); } else { // throw an exception from here } rs.close(); System.out.println("Key returned from " + "'SELECT LAST_INSERT_ID()': " + autoIncKeyFromFunc); } finally { if (rs != null) { try { rs.close(); } catch (SQLException ex) { // ignore } } if (stmt != null) { try { stmt.close(); } catch (SQLException ex) { // ignore } } }
Example 20.10. Connector/J: Retrieving AUTO_INCREMENT
column values
in Updatable ResultSets
Statement stmt = null; ResultSet rs = null; try { // // Create a Statement instance that we can use for // 'normal' result sets as well as an 'updatable' // one, assuming you have a Connection 'conn' to // a MySQL database already available // stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_UPDATABLE); // // Issue the DDL queries for the table for this example // stmt.executeUpdate("DROP TABLE IF EXISTS autoIncTutorial"); stmt.executeUpdate( "CREATE TABLE autoIncTutorial (" + "priKey INT NOT NULL AUTO_INCREMENT, " + "dataField VARCHAR(64), PRIMARY KEY (priKey))"); // // Example of retrieving an AUTO INCREMENT key // from an updatable result set // rs = stmt.executeQuery("SELECT priKey, dataField " + "FROM autoIncTutorial"); rs.moveToInsertRow(); rs.updateString("dataField", "AUTO INCREMENT here?"); rs.insertRow(); // // the driver adds rows at the end // rs.last(); // // We should now be on the row we just inserted // int autoIncKeyFromRS = rs.getInt("priKey"); rs.close(); rs = null; System.out.println("Key returned for inserted row: " + autoIncKeyFromRS); } finally { if (rs != null) { try { rs.close(); } catch (SQLException ex) { // ignore } } if (stmt != null) { try { stmt.close(); } catch (SQLException ex) { // ignore } } }
When you run the preceding example code, you should get the
following output: Key returned from
getGeneratedKeys()
: 1 Key returned from
SELECT LAST_INSERT_ID()
: 1 Key returned for
inserted row: 2 You should be aware, that at times, it can be
tricky to use the SELECT LAST_INSERT_ID()
query, as that function's value is scoped to a connection. So,
if some other query happens on the same connection, the value
will be overwritten. On the other hand, the
getGeneratedKeys()
method is scoped by
the Statement
instance, so it can be
used even if other queries happen on the same connection, but
not on the same Statement
instance.
User Comments
I think that the usage of parameter dontTrackOpenResources=true should be emphasized with an example (or default set to "true").
The novice user will most likely overlook the description of this parameter in the long parameter list provided, and end-up with an annoying and "hard-to-explain" memory-leak behaviour of his/her application.
It should be clearly pointed out that the connector's close() method for ResultSet and Statement does not fully comply with the Interface specification when dontTrackOpenResources=false (as it does NOT release immediately all JDBC resources).
There is one important thing however you need to remember before invoking the "prepareCall()" method, the mysql "user" you are using should have at least "SELECT" privilege on the `mysql`.`proc` table, else you will get the following exception :-
"java.sql.SQLException:User does not have access to metadata required to determine stored procedure parameter types. If rights can not be granted, configure connection with "noAccessToProcedureBodies=true" to have driver generate parameters that represent INOUT strings irregardless of actual parameter types."
Or Alternatively you Could listen to the above exception and configure your connection URL likewise.
Add your own comment.