jdbc Creating a database connection


Syntax

  • DB_URL = "jdbc:DBMS://DB_HOST:DB_PORT/DB_NAME"

  • DBMS: Data Base Driver Manager, this can be any DBMS (mysql, oracle, postgresql, sqlite, ...), exemple of mysql: "com.mysql.jdbc.Driver"

  • DB_HOST: your database base host, the IP adress of your database exemple : 10.6.0.1, the default is localhost or 127.0.0.1

  • DB_PORT: Database port, every DBMS has a defeaut port exemple mysql=3306, postegesql=5432

  • DB_NAME: the name of your Database

  • To connect you should to obtains a reference to the class object,

  • Class.forName(DRIVER);

  • And to connect to database, you need to create a connection

  • java.sql.Connection con = DriverManager.getConnection(DB_URL, DB_USER_NAME, DB_PASSWORD);

  • DB_USER_NAME : the username of your databse

  • DB_PASSWORD : the password of your database

Introduction (SQL)

Since Java 6, the recommended way to access an SQL-based database in Java is via the JDBC(Java DataBase Connectivity) API.

This API comes in two packagages: java.sql and javax.sql.

JDBC defines database interactions in terms of Connections and Drivers.

A Driver interacts with the database, and provides a simplified interface for opening and managing connections. Most database server varieties (PostgreSQL, MySQl, etc.) have their own Drivers, which handle setup, teardown, and translation specific to that server. Drivers are usually not accessed directly; rather, the interface provided by the DriverManager object is used instead.

The DriverManager object is essentially the core of JDBC. It provides a (mostly) database-agnostic interface to create Connections. For older versions of the JDBC API, database-specific Drivers had to be loaded before DeviceManager could create a connection to that database type.

A Connection is, as the name implies, a representation of an open connection to the database. Connections are database-agnostic, and are created and provided by the DriverManager. They provide a number of 'shortcut' methods for common query types, as well as a raw SQL interface.

Using the Connection (And Statements)

Once we've gotten the Connection, we will mostly use it to create Statement objects. Statements represent a single SQL transaction; they are used to execute a query, and retrieve the results (if any). Let's look at some examples:

public void useConnection() throws SQLException{


    Connection conn = getConnection();

    //We can use our Connection to create Statements
    Statement state = conn.getStatement();

    //Statements are most useful for static, "one-off" queries
    
    String query = "SELECT * FROM mainTable";
    boolean sucess = state.execute(query);
    
    //The execute method does exactly that; it executes the provided SQL statement, and returns true if the execution provided results (i.e. was a SELECT) and false otherwise.

    ResultSet results = state.getResultSet();
    
    //The ResultSet object represents the results, if any, of an SQL statement.
    //In this case, the ResultSet contains the return value from our query statement.
    //A later example will examine ResultSets in more detail.

    ResultSet newResults = state.executeQuery(query)
    
    //The executeQuery method is a 'shortcut' method. It combines the execute and getResultSet methods into a single step.
    //Note that the provided SQL query must be able to return results; typically, it is a single static SELECT statement.
    //There are a number of similar 'shortcut' methods provided by the Statement interface, including executeUpdate and executeBatch

    //Statements, while useful, are not always the best choice. 
    
    String newQuery = "SELECT * FROM mainTable WHERE id=?";
    PreparedStatement prepStatement = conn.prepareStatement(newQuery);

    //PreparedStatements are the prefed alternative for variable statements, especially ones that are going to be executed multiple times

    for(int id:this.ids){

        prepStatement.setInt(1,id);
        //PreparedStatements allow you to set bind variables with a wide variety of set methods.
        //The first argument to any of the various set methods is the index of the bind variable you want to set. Note that this starts from 1, not 0. 

        ResultSet tempResults = prepStatement.executeQuery()
        //Just like Statements, PreparedStatements have a couple of shortcut methods. 
        //Unlike Statements, PreparedStatements do not not take a query string as an argument to any of their execute methods.
        //The statement that is executed is always the one passed to the Connector.prepareStatement call that created the PreparedStatement
    }

}

Creating a connection using java.sql.DriverManager

To connect using java.sql.DriverManager you need a JDBC url to connect to your database. JDBC urls are database specific, but they are all of the form

jdbc:<subprotocol>:<subname>

Where <subprotocol> identifies the driver or database (for example postgresql, mysql, firebirdsql, etc), and <subname> is subprotocol-specific.

You need to check the documentation of your database and JDBC driver for the specific url subprotocol and format for your driver.

A simple example to create a connection to a database with the url jdbc:somedb://localhost/foobar:

try (Connection connection = DriverManager.getConnection(
        "jdbc:somedb://localhost/foobar", "anna", "supersecretpassword")) {
    // do something with connection
}

We use a try-with-resources here so the connection is automatically closed when we are done with it, even if exceptions occur.

4.0

On Java 6 (JDBC 4.0) and earlier, try-with-resources is not available. In those versions you need to use a finally-block to explicitly close a connection:

Connection connection = DriverManager.getConnection(
        "jdbc:somedb://localhost/foobar", "anna", "supersecretpassword");
try {
    // do something with connection
} finally {
    // explicitly close connection
    connection.close();
}
4.0

JDBC 4.0 (Java 6) introduced the concept of automatic driver loading. If you use Java 5 or earlier, or an older JDBC driver that does not implement JDBC 4 support, you will need to explicitly load the driver(s):

Class.forName("org.example.somedb.jdbc.Driver");

This line needs to occur (at least) once in your program, before any connection is made.

Even in Java 6 and higher with a JDBC 4.0 it may be necessary to explicitly load a driver: for example in web applications when the driver is not loaded in the container, but as part of the web application.

Alternatively you can also provide a Properties object to connect:

Properties props = new Properties();
props.setProperty("user", "anna");
props.setProperty("password", "supersecretpassword");
// other, database specific, properties
try (Connection connection = DriverManager.getConnection(
        "jdbc:somedb://localhost/foobar", props)) {
    // do something with connection
}

Or even without properties, for example if the database doesn't need username and password:

try (Connection connection = DriverManager.getConnection(
        "jdbc:somedb://localhost/foobar")) {
    // do something with connection
}

Creating a connection to MySQL

To connect to MySQL you need to use the MySQL Connector/J driver. You can download it from http://dev.mysql.com/downloads/connector/j/ or you can use Maven:

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.39</version>
</dependency>

The basic JDBC URL for MySQL is:

jdbc:mysql://<hostname>[:<port>]/<database>[?<propertyName>=<propertyValue>[&<propertyName>=<propertyValue>]...]

Where:

KeyDescriptionExample
<hostname>Host name of the MySQL serverlocalhost
<port>Port of the MySQL server (optional, default: 3306)3306
<database>Name of the databasefoobar
<propertyName>Name of a connection propertyuseCompression
<propertyValue>Value of a connection propertytrue

The supported URL is more complex than shown above, but this suffices for most 'normal' needs.

To connect use:

try (Connection connection = DriverManager.getConnection(
        "jdbc:mysql://localhost/foobardb", "peter", "nicepassword")) {
    // do something with connection
}
4.0

For older Java/JDBC versions:

4.0
// Load the MySQL Connector/J driver
Class.forName("com.mysql.jdbc.Driver");
Connection connection = DriverManager.getConnection(
        "jdbc:mysql://localhost/foobardb", "peter", "nicepassword");
try {
    // do something with connection
} finally {
  // explicitly close connection
  connection.close();
}

Connection to a Microsoft Access database with UCanAccess

UCanAccess is a pure Java JDBC driver that allows us to read from and write to Access databases without using ODBC. It uses two other packages, Jackcess and HSQLDB, to perform these tasks.

Once it has been set up*, we can work with data in .accdb and .mdb files using code like this:

import java.sql.*;

Connection conn=DriverManager.getConnection("jdbc:ucanaccess://C:/__tmp/test/zzz.accdb");
Statement s = conn.createStatement();
ResultSet rs = s.executeQuery("SELECT [LastName] FROM [Clients]");
while (rs.next()) {
    System.out.println(rs.getString(1));
}

*For more details see the following question:

Manipulating an Access database from Java without ODBC

Oracle JDBC connection

Driver:

(Note: the driver is not included in Maven Central!)

Driver class initialization:

    Class.forName("oracle.jdbc.driver.OracleDriver");

Connection URL

Older format, with SID

"jdbc:oracle:thin:@<hostname>:<port>:<SID>"

Newer format, with Service Name

"jdbc:oracle:thin:@//<hostname>:<port>/<servicename>"

Tnsnames like entry

"jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(HOST=<hostname>)(PORT=<port>))"
    +"(CONNECT_DATA=(SERVICE_NAME=<servicename>)))"

RAC cluster connection string for failover

"jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(LOAD_BALANCE=OFF)(FAILOVER=ON)"
    +"(ADDRESS=(PROTOCOL=TCP)(HOST=<hostname1>)(PORT=<port1>))"
    +"(ADDRESS=(PROTOCOL=TCP)(HOST=<hostname2>)(PORT=<port2>)))"
    +"(CONNECT_DATA=SERVICE_NAME=<servicename>)(SERVER=DEDICATED)))"

Example

connection = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "HR", "HRPASS");