How to establish connection to database with Properties?

In the following code snippet you’ll see how to pass some connection arguments when connecting to a database. To do this we can use the java.util.Properties class. We can put some key value pairs as a connection arguments to the Properties object before we pass this information into the DriverManager class.

Let’s see the example below:

package org.kodejava.example.sql;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

public class GetConnectionWithProperties {
    private String url = "jdbc:mysql://localhost/testdb";

    public static void main(String[] args) {
        GetConnectionWithProperties demo = new GetConnectionWithProperties();
        try {
            Connection connection = demo.getConnection();

            // do something with the connection.
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private Connection getConnection() throws SQLException {
        Properties connectionProps = new Properties();
        connectionProps.put("user", "root");
        connectionProps.put("password", "root");

        Connection connection = DriverManager.getConnection(url, connectionProps);
        System.out.println("Connected to database.");
        return connection;
    }
}

How to automatically close resources in JDBC?

One thing that we need to do manually when programming using JDBC is to make sure to close all the resources that we use. All resources including the ResultSet, Statement and Connection must be closed. This usually will produce a lot of boiler plate code in our program.

Starting from JDBC 4.1 which is a part of Java 7 we can use the try-with-resources statement to automatically manage the resources that we use. This try statement closes the resources used when the block finishes its execution either normally or abruptly.

Here is an example that show us how to use the try-with-resources statement.

package org.kodejava.example.sql;

import java.sql.*;

public class TryWithResourceJdbc {
    public static final String URL = "jdbc:mysql://localhost/sampledb";

    public static void main(String[] args) {
        try {
            Class.forName("com.mysql.jdbc.Driver");

            TryWithResourceJdbc demo = new TryWithResourceJdbc();
            demo.selectData();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * Select data from m_users table.
     * @throws SQLException when an exception happens.
     */
    private void selectData() throws SQLException {
        try (Connection conn = DriverManager.getConnection(URL, "root", "");
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery("SELECT * FROM m_users")) {

            while (rs.next()) {
                String username = rs.getString("username");
                String password = rs.getString("password");

                System.out.println("username: " + username + "; password: " + password);
            }
        }
    }
}

How do I disable auto commit mode in JDBC?

The code fragment below show you how we disable auto commit operation when executing a JDBC command.

package org.kodejava.example.jdbc;

import java.sql.*;

public class AutoCommitSettingExample {
    private static final String URL = "jdbc:mysql://localhost/kodejava";
    private static final String USERNAME = "root";
    private static final String PASSWORD = "";

    public static void main(String[] args) {
        // DO: Get a connection to database, we need to obtain the
        // database connection prior to executing any JDBC commands
        try (Connection connection =
                 DriverManager.getConnection(URL, USERNAME, PASSWORD)) {

            // Disable the auto-commit operation. By default every statement
            // executed against database in JDBC is in auto-commit mode. To
            // disable auto-commit set it to false
            connection.setAutoCommit(false);

            // DO: Execute some other database operation here
            String sql = "DELETE FROM books WHERE isbn = ?";
            try (PreparedStatement statement = connection.prepareStatement(sql)) {
                statement.setString(1, "9781617294945");
                statement.executeUpdate();

                // Finally we must call the commit method explicitly to finish
                // all database manipulation operation
                connection.commit();
            } catch (SQLException e) {
                // When some exception occurs rollback the transaction.
                connection.rollback();
                throw e;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Maven dependencies

<!-- https://search.maven.org/remotecontent?filepath=mysql/mysql-connector-java/5.1.47/mysql-connector-java-5.1.47.jar -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.47</version>
</dependency>

How do I use JNDI to get database connection or data source?

package org.kodejava.example.jndi;

import javax.naming.InitialContext;
import javax.servlet.Servlet;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;

public class JNDITestServlet extends HttpServlet implements Servlet {

    protected void doGet(HttpServletRequest req, HttpServletResponse res)
            throws ServletException, IOException {

        // This implementation of doGet method show us an example to use
        // conn obtained in the getConnection() method.
        DateFormat format = new SimpleDateFormat("dd/MM/yyyy");

        res.setContentType("text/html");
        PrintWriter writer = res.getWriter();

        Connection conn = getConnection();
        if (conn != null) {
            try {
                // A query to get current date time from Oracle database
                String sql = "SELECT SYSDATE FROM DUAL";
                PreparedStatement statement = conn.prepareStatement(sql);
                ResultSet rs = statement.executeQuery();

                while (rs.next()) {
                    Date date = rs.getDate("SYSDATE");
                    writer.println("The current date is " + format.format(date));
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                try {
                    if (!conn.isClosed()) {
                        conn.close();
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * Get a database connection from the registered data source in the 
     * servlet container. To registered the JNDI data source you should 
     * refer to your servlet container documentation.
     *
     * @return a database connection
     */
    private Connection getConnection() {
        Connection connection = null;
        try {
            InitialContext context = new InitialContext();
            DataSource dataSource = (DataSource) context
                    .lookup("jdbc/DataSource");
            connection = dataSource.getConnection();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return connection;
    }
}

How do I query records from table?

package org.kodejava.example.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class JdbcQueryExample {
    // Database connection information
    private static final String URL = "jdbc:mysql://localhost/kodejava";
    private static final String USERNAME = "root";
    private static final String PASSWORD = "";

    public static void main(String[] args) throws Exception {
        // Get a connection to database.
        try (Connection connection =
                 DriverManager.getConnection(URL, USERNAME, PASSWORD)) {
            // Create a statement object.
            Statement statement = connection.createStatement();

            // Executes a query command to select isbn and the book title
            // from books table. The execute query returns a ResultSet
            // object which is the result of our query execution.
            String query = "SELECT isbn, title, published_year FROM books";
            ResultSet books = statement.executeQuery(query);

            // To get the value returned by the statement.executeQuery we
            // need to iterate the books object until the last items.
            while (books.next()) {
                // To get the value from the ResultSet object we can call
                // a method that correspond to the data type of the column
                // in database table. In the example below we call
                // books.getString("isbn") to get the book's ISBN 
                // information.
                System.out.println(books.getString("isbn") + ", " +
                    books.getString("title") + ", " +
                    books.getInt("published_year"));
            }
        }
    }
}

Maven dependencies

<!-- https://search.maven.org/remotecontent?filepath=mysql/mysql-connector-java/5.1.47/mysql-connector-java-5.1.47.jar -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.47</version>
</dependency>

How do I create a connection to database?

This post is about an example for obtaining a connection to MySQL database. For connecting to other database all you have to do is change the url to match to url format for a particular database and of course you have to register a correct JDBC driver of the database you are using.

Here are the steps:

  • Define the JDBC url of your database. Below is the format of JDBC url for MySQL database. localhost is your database address and kodejava is the database name.
private static final String URL = "jdbc:mysql://localhost/kodejava";
  • Define the username and password for the connection.
private static final String USERNAME = "root";
private static final String PASSWORD = "";
  • Register the database JDBC driver to be used by our program. Below is the driver for MySQL database.
Class.forName("com.mysql.jdbc.Driver");

But the driver registration step above is not required anymore for modern JDBC drivers (JDBC 4.0 / since JDK 6). The JDBC driver class will be located using the service provider mechanism. So you can remove the Class.forName() statement above and all you need to do is place the JDBC driver in your classpath and the driver will be loaded automatically.

  • We can open a connection to the database.
Connection connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
  • Do any database operation such as select, insert, update and delete.
  • Finally don’t forget to close the Connection object. We usually do this in the finally block of the try-catch block`
if (connection != null) {
    connection.close();
}

But in the code snippet below instead of manually close the connection object we use the try-with-resource statement, this statement will automatically close the connection for us.

Here is the complete code snippet.

package org.kodejava.example.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class ConnectionSample {
    private static final String URL = "jdbc:mysql://localhost/kodejava";
    private static final String USERNAME = "root";
    private static final String PASSWORD = "";

    public static void main(String[] args) throws Exception {
        try (Connection connection =
                 DriverManager.getConnection(URL, USERNAME, PASSWORD)) {
            System.out.println("connection = " + connection);

            String sql = "SELECT isbn, title, published_year FROM books";
            PreparedStatement stmt = connection.prepareStatement(sql);

            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                System.out.println(rs.getLong("isbn") + ", " +
                    rs.getString("title") + ", " +
                    rs.getInt("published_year"));
            }
        }
    }
}

Maven dependencies

<!-- https://search.maven.org/remotecontent?filepath=mysql/mysql-connector-java/5.1.47/mysql-connector-java-5.1.47.jar -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.47</version>
</dependency>