Home  Connecting  Statement  ResultSets  JDBC data types  Prepared statements

Connecting to a SQL database with JDBC

As mentioned on the previous page, the first step to using JDBC in order to access a database in Java is to locate the JDBC driver for that database:

  • The JDBC driver generally comes in the form of a jar that must be included in your project and/or classpath when running your program;
  • The JDBC driver's documentation should also specify the format of a URL used to connect to the database.

For this example, we'll use MySQL. In case you don't know it, MySQL is an open-source database system that is particularly popular among web developers1. The MySQL JDBC driver is called ConnectorJ. At the time of writing, the latest version (5.1) is available from:

http://dev.mysql.com/downloads/connector/j/5.1.html

With this jar downloaded and added to your classpath, the following code will open a connection to a database:

import java.sql.*;

public Connection openConnection() {
  Properties properties = new Properties();
  properties.put("user", ...);
  properties.put("password", ...);
  properties.put("characterEncoding", "ISO-8859-1");
  properties.put("useUnicode", "true");
  String url = "jdbc:mysql://hostname/database";

  Class.forName("com.mysql.jdbc.Driver").newInstance();
  Connection c = DriverManager.getConnection(url, properties);
  return c;
}

The lines that really do the work are the last two lines in bold. First, we must load the JDBC driver, which is done by creating a new instance of the driver class2. The name of the driver class should be specified in the documentation for the JDBC driver; in this case, the driver class for the MySQL JDBC driver is com.mysql.jdbc.Driver. Then, we call DriverManager.getConnection(), passing in a special URL, plus some properties about the connection.

The format of the URL depends on the specific JDBC driver: again, in this case, we show the format appropriate for MySQL. The hostname could of course be localhost if the MySQL database is installed on the same machine as the application. The properties specify, among other things, the user name and password to access the database. In practice, we usually want to specify the character encoding as above to make sure that accented and non-ASCII characters are read correctly from the database.

The DriverManager.getConnection() method returns us a Connection object, which represents our connection to the database. Via this object, we can execute SQL statements and perform other functions on the database.


1. If you are using a database for the first time, and have no reason to use a different system, I would strongly recommend using MySQL. It has the advantage of a large community of uesrs and because it is open source, is less of a "black box" than proprietary systems such as Microsoft SQL Server. Some large, high-performance applications such as Second Life are based on MySQL. Many hosting companies provide out-of-the-box MySQL installations with support for Java servlets and other web programming languages such as PHP.
2. As of Java 6, this step is not necessary.

comments powered by Disqus

Written by Neil Coffey. Copyright © Javamex UK 2012. All rights reserved.