How do I use JDBC to query a MySQL database?
Author: Deron Eriksson
Description: This Java tutorial describes using JDBC to query a MySQL database.
Tutorial created using:
Windows XP || JDK 1.5.0_09 || Eclipse Web Tools Platform 1.5.1 || MySQL 5.0.27
To connect to a MySQLW databaseW from JavaSW via JDBCW, you need a MySQL JDBC driver and accompanying classes. The latest MySQL Connector/J driver library can be downloaded from http://www.mysql.com/products/connector/j/. After downloading the Connector/J library, you should add it to the classpathW of your project. In the project below, the mysql-connector-java-5.0.4-bin.jar file has been added to the lib/ directory of the project, and this jarW file was added to the project classpath. The MySqlJdbcTest class is shown below. It first creates a com.mysql.jdbc.Driver object via the call to Class.forName("com.mysql.jdbc.Driver").newInstance(). I'm a little confused as to why 'Class.forName("com.mysql.jdbc.Driver").newInstance()' is typically used in JDBC examples to instantiate the Driver object rather than just 'new com.mysql.jdbc.Driver()'. Both work as far as I can tell. The second version is shown, but commented out. It's necessary to get a connection via DriverManager.getConnection. It's possible to specify the database user and password on the URL string passed to the method, and this is shown commented out in the example. The user and password can also be passed as separate parameters, and this is shown in the example (the connectionUser and connectionPassword variables). A statement is created and the "SELECT * FROM employees" query is executed, and the results are returned as a result set. The result set it iterated over, and the results of each row are displayed. Note that the 'employees' table was created in another tutorial. Notice that in the finally block, the result set, statement, and connection are all explicitly closed. This is a VERY good practice to follow so that database connections do not get leaked when you write JDBC code. MySqlJdbcTest.javapackage test; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; //import com.mysql.jdbc.Driver; public class MySqlJdbcTest { public static void main(String[] args) { Connection conn = null; Statement stmt = null; ResultSet rs = null; try { // new com.mysql.jdbc.Driver(); Class.forName("com.mysql.jdbc.Driver").newInstance(); // conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/testdatabase?user=testuser&password=testpassword"); String connectionUrl = "jdbc:mysql://localhost:3306/testdatabase"; String connectionUser = "testuser"; String connectionPassword = "testpassword"; conn = DriverManager.getConnection(connectionUrl, connectionUser, connectionPassword); stmt = conn.createStatement(); rs = stmt.executeQuery("SELECT * FROM employees"); while (rs.next()) { String id = rs.getString("id"); String firstName = rs.getString("first_name"); String lastName = rs.getString("last_name"); System.out.println("ID: " + id + ", First Name: " + firstName + ", Last Name: " + lastName); } } catch (Exception e) { e.printStackTrace(); } finally { try { if (rs != null) rs.close(); } catch (SQLException e) { e.printStackTrace(); } try { if (stmt != null) stmt.close(); } catch (SQLException e) { e.printStackTrace(); } try { if (conn != null) conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } } (Continued on page 2) |