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.sql;

import java.sql.Connection;
import java.sql.SQLException;

public class CommitSettingExample {
    public static void main(String[] args) {
        Connection connection = null;
        try {
            // DO: Get a connection to database, we need to obtain the 
            // database connection prior to executing any JDBC commands/

            // 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

            // Finally we must call the commit method explicitly to finish
            // all database manipulation operation
            connection.commit();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

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 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.
public static final String URL = "jdbc:mysql://localhost/kodejava";
  • Define the username dan password for the connection.
public static final String USERNAME = "kodejava";
public static final String PASSWORD = "kodejava123";
  • Register the database JDBC driver to be used by our program. Below is the driver for MySQL database.
Class.forName("com.mysql.jdbc.Driver");
  • 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();
}

Here is the complete code snippet.

package org.kodejava.example.sql;

import java.sql.Connection;
import java.sql.DriverManager;

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

    public static void main(String[] args) throws Exception {
        Connection connection = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection(URL,
                    USERNAME, PASSWORD);
        } finally {
            if (connection != null) {
                connection.close();
            }
        }
    }
}