Writing JDBC applications with MySQL

Document directory

• Issuing queries that return no result set
• Issuing queries that return a result set
• Error Handling

 

Paul Dubois
Paul@kitebird.com

Document revision: 1.01
Last Update: 2003-01-24

Table of contents

• Preliminary requirements
• Connecting to the MySQL server
• Issuing queries
  • Issuing queries that return no result set
  • Issuing queries that return a result set
    • Using placeholders
  • Error Handling
• Resources
• Acknowledgment

You can write MySQL applications in a variety of ages. the versions that most people use with MySQL are PHP and Perl, but a sometimes overlooked option is the MySQL connector/J driver, which allows you to develop Java applications that interact with your MySQL server.

MySQL connector/J works within the framework of the Java JDBC interface, an API that allows Java programs to use database servers in a Portway. JDBC is based on an approach similar to that used in the design of Perl and Ruby DBI modules, Python's DB-API module, and PHP's pear: DB class. this approach uses a two-tier architecture:

• The top level is visible to application programs and presents an abstract interface for connecting to and using database engines. The application interface does not depend on details specific to particle engine.
• The lower level consists of drivers for individual database engines. Each driver handles the details necessary to map the abstract application interface onto operations that a specific engine will understand.

The JDBC interface allows developers to write applications that can be used with different databases with a minimum of porting effort. once a driver for a given server engine is installed, JDBC applications can communicate with any server of that type. by using MySQL connector/J, your Java programs can access MySQL databases.

Note:MySQL connector/J is the successor to the MM. mySQL driver. if you have JDBC programs written for mm. mySQL, They shocould work with MySQL connector/J as well, although you may be want to update the driver class name used in your programs. just replace instancesOrg. gjt. Mm. MySQLIn your Java source filesCom. MySQL. JDBCAnd recompile.

Preliminary requirements

To use Java applications with MySQL, you may need to install some additional software:

• If you want to compile and run Java programs, you'll need a Java compiler (suchJavacOrJikes) And a runtime environment. If these are not already installed on your system, you can get them by obtaining a Java software development kit (SDK) fromJava.sun.com.
• If you want only to run precompiled applications, no compiler is necessary, but you'll still need a Java Runtime Environment (JRE). This too may be obtained fromJava.sun.com.

This article assumes that you'll write and compile your own programs, and thus that you have a Java SDK installed. once you compile a Java program, however, you can deploy it to other machines, even ones that have only a runtime environment. this works even in heterogenous installations, Because Java is platform-independent. applications compiled on one platform can be expected to work on other platforms. for example, you can develop on a Linux box and deploy on Windows.

Connecting to the MySQL server

To connect to the MySQL server, register the JDBC driver you plan to use, then invoke itsGetconnection ()Method. The following short program,Connect. Java, Shows how to connect to and disconnect from a server running on the local host. It accesses a database namedTest, Using a MySQL account with a user name and passwordTestuserAndTestpass:

   import java.sql.*;public class Connect{public static void main (String[] args){Connection conn = null;try{String userName = "testuser";String password = "testpass";String url = "jdbc:mysql://localhost/test";Class.forName ("com.mysql.jdbc.Driver").newInstance ();conn = DriverManager.getConnection (url, userName, password);System.out.println ("Database connection established");}catch (Exception e){System.err.println ("Cannot connect to database server");}finally{if (conn != null){try{conn.close ();System.out.println ("Database connection terminated");}catch (Exception e) { /* ignore close errors */ }}}}}

CompileConnect. JavaTo produce a class fileConnect. ClassThat contains executable Java code:

   % javac Connect.java

Then invoke the class file as follows and it shoshould connect to and disconnect from your MySQL Server:

   % java ConnectDatabase connection establishedDatabase connection terminated

If you have trouble compilingConnect. Java, Double check that you have a Java software development kit installed and make sure that the MySQL ctor/J driver is listed in yourClasspathEnvironment variable.

The argumentsGetconnection ()Are the connection URL and the user name and password of a MySQL account. As your stratedConnect. Java, JDBC URLs for MySQL consistJDBC: mysql ://Followed by the name of the MySQL server host and the database name. An alternate syntax for specifying the user and password is to add them as parameters to the end of the connection URL:

   jdbc:mysql://localhost/test?user=testuser&password=testpass

When you specify a URL using this second format,Getconnection ()Requires only one argument. For example, the Code for connecting to the MySQL server inConnect. JavaCocould have been written like this:

   String userName = "testuser";String password = "testpass";String url = "jdbc:mysql://localhost/test?user="+ userName+ "&password="+ password;Class.forName ("com.mysql.jdbc.Driver").newInstance ();conn = DriverManager.getConnection (url);

Getconnect ()ReturnsConnectionObject that may be used to interact with MySQL by issuing queries and retrieving their results. (The next section describes how to do this.) When you're done with the connection, invoke itsClose ()Method to disconnect from the MySQL server.

To increase the portability of your applications, you can store the connection parameters (host, database, user name, and password) in a Java properties file and read the properties at runtime. then they need not be listed in the program itself. this allows you to change the server to which the program connects by editing the properties file, rather than by having to recompile the program.

Issuing queries

To process SQL statements in a JDBC-based application, createStatementObject from yourConnectionObject.StatementObjects supportExecuteupdate ()Method for issuing queries that modify the database and return no result set, andExecutequery ()Method for queries that do return a result set. The query-processing examples in this article use the following table,Animal, Which contains an integerIDColumn and two string columns,NameAndCategory:

   CREATE TABLE animal(id          INT UNSIGNED NOT NULL AUTO_INCREMENT,PRIMARY KEY (id),name        CHAR(40),category    CHAR(40))

IDIsAuto_incrementColumn, so MySQL automatically assigns successive values 1, 2, 3,... as records are added to the table.

Issuing queries that return no result set

The following example obtainsStatementObject fromConnectionObject, then uses it to create and populateAnimalTable.Drop Table,Create Table, AndInsertAll are statements that modify the database, soExecuteupdate ()Is the appropriate method for issuing them:

   Statement s = conn.createStatement ();int count;s.executeUpdate ("DROP TABLE IF EXISTS animal");s.executeUpdate ("CREATE TABLE animal ("+ "id INT UNSIGNED NOT NULL AUTO_INCREMENT,"+ "PRIMARY KEY (id),"+ "name CHAR(40), category CHAR(40))");count = s.executeUpdate ("INSERT INTO animal (name, category)"+ " VALUES"+ "('snake', 'reptile'),"+ "('frog', 'amphibian'),"+ "('tuna', 'fish'),"+ "('racoon', 'mammal')");s.close ();System.out.println (count + " rows were inserted");

TheExecuteupdate ()Method returns the number of rows affected by a query. As shown above, the count is used to report how many rowsInsertStatement added toAnimalTable.

AStatementObject may be used to issue several queries. When you're done with it, invoke itsClose ()Method to dispose of the object and free any resources associated with it.

Issuing queries that return a result set

For statements suchSelectQueries that retrieve information from the database, useExecutequery (). After calling this method, createResultsetObject and use it to iterate through the rows returned by your query. The following example shows one way to retrieve the contents ofAnimalTable:

   Statement s = conn.createStatement ();s.executeQuery ("SELECT id, name, category FROM animal");ResultSet rs = s.getResultSet ();int count = 0;while (rs.next ()){int idVal = rs.getInt ("id");String nameVal = rs.getString ("name");String catVal = rs.getString ("category");System.out.println ("id = " + idVal+ ", name = " + nameVal+ ", category = " + catVal);++count;}rs.close ();s.close ();System.out.println (count + " rows were retrieved");

Executequery ()Does not return a row count, so if you want to know how many rows a result set contains, you shocould count them yourself as you fetch them.

To obtain the column values from each row, invokeGetXxx()Methods that match the column data types.Getint ()AndGetstring ()Methods Used in the preceding example return integer and string values. as the example shows, these methods may be called using the name of a result set column. you can also fetch values by position. for the result set retrieved bySelectQuery In the example,ID,Name, AndCategoryAre at column positions 1, 2 and 3 and thus cocould have been obtained like this:

   int idVal = rs.getInt (1);String nameVal = rs.getString (2);String catVal = rs.getString (3);

ResultsetObjects, likeStatementObjects, shoshould be closed when you're done with them.

To check whether or not a column value isNull, Invoke the result set object'sWasnull ()Method After fetching the value. For example, you cocould check forNullValue inNameColumn like this:

   String nameVal = rs.getString ("name");if (rs.wasNull ())nameVal = "(no name available)";

Using placeholders

Sometimes it's necessary to construct queries from values containing characters that require special treatment. for example, in queries, string values are written enclosed within quotes, but any quote characters in the string itself shocould be doubled or escaped with a backslash to avoid creating malformed SQL. in this case, it's much easier to let JDBC handle the escaping for you, rather than fooling around trying to do so yourself. to use this approach, create a different kind of statement (Preparedstatement), And refer to the data values in the query string by means of placeholder characters. Then tell JDBC to bind the data values to the placeholders and it will handle any special characters automatically.

Suppose you have two variablesNamevalAndCatvalFrom which you want to create a new record inAnimalTable. To do so without regard to whether or not the values contain special characters, issue the query like this:

   PreparedStatement s;s = conn.prepareStatement ("INSERT INTO animal (name, category) VALUES(?,?)");s.setString (1, nameVal);s.setString (2, catVal);int count = s.executeUpdate ();s.close ();System.out.println (count + " rows were inserted");

The'?'Characters in the query string act as placeholders -- special markers indicating where data values shoshould be placed.Setstring ()Method takes a placeholder position and a string value and binds the value to the appropriate placeholder, specify Ming any special-character escaping that may be necessary. the method you use to bind a value depends on the data type. for example,Setstring ()Binds string values andSetint ()Binds integer values.

Error Handling

If you want to trap errors, execute your JDBC operations withinTryBlock and use an exception handler to display information about the cause of any problems that occur. JDBC providesGetmessage ()AndGeterrorcode ()Methods that may be invoked when an exception occurs to obtain the error message and the numeric error code. The following example deliberately issues a malformed query. When it runs,Executequery ()Method fails and raises an exception that is handled inCatchBlock:

   try{Statement s = conn.createStatement ();s.executeQuery ("XYZ"); // issue invalid querys.close ();}catch (SQLException e){System.err.println ("Error message: " + e.getMessage ());System.err.println ("Error number: " + e.getErrorCode ());}

Resources

The following sites provide information about the tools discussed in this article:

• Sun's Java site is a clearinghouse for all kinds of Java-related information:

     http://java.sun.com/        

  You can obtain the Java software development kit or Java Runtime Environment here. The specification for the jdbc api is also available on this site, shocould you wish to read more about it.

• The MySQL connector/J driver is supported by MySQL AB. connector/J author Mark donews maintains the driver, which is available:

     http://www.mysql.com/        

• Introductory articles describing other MySQL APIs based on an architecture similar to that used for JDBC may be found:

     http://www.kitebird.com/articles/        

  APIS discussed in the articles include Perl DBI, php pear: DB, Python DB-API, and Ruby DBI.

Acknowledgment

The original version of this article was written for nusphere Corporation. The current version is an updated revision of the original.