How to establish connection to a database using Properties object?

By Wayan in JDBC 0 Comment

In the following code snippet, you will 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.jdbc;

import java.sql.*;
import java.util.Properties;

public class GetConnectionWithProperties {
    private static final String URL = "jdbc:mysql://localhost/kodejava";
    private static final String USERNAME = "kodejava";
    private static final String PASSWORD = "s3cr*t";

    public static void main(String[] args) {
        GetConnectionWithProperties demo = new GetConnectionWithProperties();
        try (Connection connection = demo.getConnection()) {
            // do something with the connection.
            Statement stmt = connection.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT * FROM product");
            while (rs.next()) {
                System.out.println("Code = " + rs.getString("code"));
                System.out.println("Name = " + rs.getString("name"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

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

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

Maven Dependencies

<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <version>8.1.0</version>
</dependency>

Maven Central

How to automatically close resources in JDBC?

By Wayan in JDBC 0 Comment

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 will usually produce a lot of boilerplate 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 shows us how to use the try-with-resources statement.

package org.kodejava.jdbc;

import java.sql.*;

public class TryWithResourceJdbc {
    private static final String URL = "jdbc:mysql://localhost/kodejava";
    private static final String USERNAME = "kodejava";
    private static final String PASSWORD = "s3cr*t";

    public static void main(String[] args) {
        try (Connection conn =
                     DriverManager.getConnection(URL, USERNAME, PASSWORD);
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery("SELECT * FROM product")) {

            while (rs.next()) {
                String code = rs.getString("code");
                String name = rs.getString("name");

                System.out.println("Code: " + code + "; Name: " + name);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Maven Dependencies

<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <version>8.1.0</version>
</dependency>

Maven Central

How do I get the max concurrent connection to a database?

By Wayan in JDBC 0 Comment 
package org.kodejava.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.DatabaseMetaData;
import java.sql.SQLException;

public class MaxConcurrentConnection {
    private static final String URL = "jdbc:mysql://localhost/kodejava";
    private static final String USERNAME = "kodejava";
    private static final String PASSWORD = "s3cr*t";

    public static void main(String[] args) {
        try (Connection connection =
                     DriverManager.getConnection(URL, USERNAME, PASSWORD)) {
            DatabaseMetaData metadata = connection.getMetaData();

            // Get the information of maximum concurrent connection to the
            // database. The maximum number of active connections possible
            // at one time; a result of zero means that there is no limit
            // or the limit is not known.
            int maxConnection = metadata.getMaxConnections();
            System.out.println("Maximum Connection = " + maxConnection);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

The result of the code snippet above:

Maximum Connection = 0

Maven Dependencies

<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <version>8.1.0</version>
</dependency>

Maven Central

How do I disable auto-commit mode in JDBC?

By Wayan in JDBC 0 Comment

The code snippet below shows you how to disable auto-commit operation when executing JDBC commands or queries.

package org.kodejava.jdbc;

import java.sql.*;

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

    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 book WHERE id = ?";
            try (PreparedStatement statement = connection.prepareStatement(sql)) {
                statement.setLong(1, 1L);
                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

<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <version>8.1.0</version>
</dependency>

Maven Central

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

By Wayan in Core API, JNDI 0 Comment 
package org.kodejava.jndi;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.servlet.Servlet;
import javax.servlet.annotation.WebServlet;
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;

@WebServlet(name = "JNDITestServlet", urlPatterns = "/jndi-datasource-test")
public class JNDITestServlet extends HttpServlet implements Servlet {

    protected void doGet(HttpServletRequest request,
                         HttpServletResponse response) throws 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 HH:mm:ss");

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

        try (Connection connection = getConnection()) {
            // A query to get current date time from Oracle database
            String sql = "select current_timestamp() as SYSDATE";
            PreparedStatement statement = connection.prepareStatement(sql);
            ResultSet rs = statement.executeQuery();
            while (rs.next()) {
                Date date = rs.getTimestamp("SYSDATE");
                writer.println("The current date is " + format.format(date));
            }
        } 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 initialContext = new InitialContext();
            Context context = (Context) initialContext.lookup("java:/comp/env");
            DataSource dataSource = (DataSource) context.lookup("jdbc/DataSource");
            connection = dataSource.getConnection();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return connection;
    }
}

Configure the JNDI DataSource in Tomcat conf/context.xml configuration file. And don’t forget to copy the JDBC driver library to Tomcat’s lib directory.

<?xml version="1.0" encoding="UTF-8"?>
<Context>
    ...
    <Resource name="jdbc/DataSource" auth="Container" type="javax.sql.DataSource"
               maxTotal="100" maxIdle="30" maxWaitMillis="10000"
               username="root" password="" driverClassName="com.mysql.cj.jdbc.Driver"
               url="jdbc:mysql://localhost:3306/kodejava"/>
    ...
</Context>

Maven dependencies

<dependencies>
    <dependency>
        <groupId>javax.servlet</groupId>
        <artifactId>javax.servlet-api</artifactId>
        <version>4.0.1</version>
    </dependency>
</dependencies>

Maven Central