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.

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
    }

}

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.

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

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

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
}

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:

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
}

// 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();
}

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

Driver:

• 12c R1
• 11g R2

(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");