How do I use DataSource instead of DriverManager?

By I Wayan Saryada in JNDI 0 Comment

Switching from DriverManager to DataSource is a best practice in modern Java applications because it supports connection pooling, is more configurable, and decouples your code from the specific database driver implementation.

While DriverManager creates a physical connection every time you call getConnection(), a DataSource (specifically a pooling one) maintains a set of open connections that can be reused, significantly improving performance.

1. The DriverManager Approach (Old way)

You are likely used to this pattern:

// Hardcoded driver details and physical connection creation
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/db", "user", "pass");

2. The DataSource Approach (Modern way)

With DataSource, you configure the object once and then use it to get connections throughout your application.

Using Apache Commons DBCP (Connection Pooling)

To use a DataSource with pooling, you can use a library like Apache Commons DBCP’s BasicDataSource.

package org.kodejava.jdbc;

import org.apache.commons.dbcp2.BasicDataSource;
import javax.sql.DataSource;

public class DatabaseConfig {
    private static final BasicDataSource dataSource;

    static {
        dataSource = new BasicDataSource();
        dataSource.setUrl("jdbc:mysql://localhost/musicdb");
        dataSource.setUsername("music");
        dataSource.setPassword("s3cr*t");

        // Optional: Configure pooling parameters
        dataSource.setInitialSize(5);
        dataSource.setMaxTotal(10);
    }

    public static DataSource getDataSource() {
        return dataSource;
    }
}

3. Using the Connection in your Code

Once you have the DataSource instance, getting a connection is consistent regardless of the underlying implementation:

public void fetchData() {
    DataSource ds = DatabaseConfig.getDataSource();

    // The try-with-resources ensures the connection is "closed" 
    // (returned to the pool) automatically.
    try (Connection conn = ds.getConnection()) {
        // Use the connection as usual
        var stmt = conn.prepareStatement("SELECT * FROM record");
        var rs = stmt.executeQuery();
        // ... process results
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

Why use DataSource?

  • Connection Pooling: Reusing connections is much faster than opening/closing them for every request.
  • Decoupling: Your business logic only knows about the javax.sql.DataSource interface. You can switch from BasicDataSource to HikariCP (another popular pool) without changing your data-access code.
  • JNDI Support: In Jakarta EE environments, you can look up a DataSource configured in the application server via JNDI, keeping credentials out of your source code.
  • Spring Integration: If you use Spring Framework, JdbcTemplate is designed to work directly with a DataSource.

Maven Dependency

<dependency>
    <groupId>org.apache.commons</groupId>
    <artifactId>commons-dbcp2</artifactId>
    <version>2.13.0</version>
</dependency>

How do I select records from database using JdbcTemplate?

By I Wayan Saryada in Spring JDBC 0 Comment

In this example you will learn how to select records from the database using JdbcTemplate.queryForList() method. This method returns a List object which stores information selected from the table in a HashMap object. The key of the map is the table’s field names while the value of the map contains the corresponding table’s field value.

package org.kodejava.spring.jdbc;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

import java.util.List;
import java.util.Map;

public class SelectDemo {
    public static void main(String[] args) {
        // Creates a DataSource object.
        DriverManagerDataSource ds = new DriverManagerDataSource();
        ds.setDriverClassName("com.mysql.cj.jdbc.Driver");
        ds.setUrl("jdbc:mysql://localhost/musicdb");
        ds.setUsername("root");
        ds.setPassword("");

        // Creates an instance of JdbcTemplate.
        JdbcTemplate template = new JdbcTemplate(ds);

        // Executes a select query using queryForList() method. This
        // method returns a List containing HashMap object. The key
        // of the map is the table's field name and the value is
        // the table's field value.
        String query = "SELECT * FROM record";
        List<Map<String, Object>> results = template.queryForList(query);
        for (Map<String, Object> result : results) {
            for (String key : result.keySet()) {
                System.out.print(key + " = " + result.get(key) + "; ");
            }
            System.out.println();
        }

    }
}

Maven Dependencies

<dependencies>
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-jdbc</artifactId>
        <version>6.1.10</version>
    </dependency>
    <dependency>
        <groupId>com.mysql</groupId>
        <artifactId>mysql-connector-j</artifactId>
        <version>8.4.0</version>
    </dependency>
</dependencies>

Maven Central Maven Central

How do I delete records from database using JdbcTemplate?

By I Wayan Saryada in Spring JDBC 0 Comment

The code below demonstrates on how to delete some records from database using the JdbcTemplate.

package org.kodejava.spring.jdbc;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

import javax.sql.DataSource;

public class DeleteDemo {
    public static final String DRIVER = "com.mysql.cj.jdbc.Driver";
    public static final String URL = "jdbc:mysql://localhost/musicdb";
    public static final String USERNAME = "root";
    public static final String PASSWORD = "";

    public static final String QUERY = "DELETE FROM record WHERE id = ?";

    private final DataSource dataSource;

    public DeleteDemo(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    /**
     * Returns a DataSource object.
     *
     * @return a DataSource.
     */
    public static DataSource getDataSource() {
        DriverManagerDataSource ds = new DriverManagerDataSource();
        ds.setDriverClassName(DeleteDemo.DRIVER);
        ds.setUrl(DeleteDemo.URL);
        ds.setUsername(DeleteDemo.USERNAME);
        ds.setPassword(DeleteDemo.PASSWORD);
        return ds;
    }

    public static void main(String[] args) {
        DataSource ds = getDataSource();
        DeleteDemo demo = new DeleteDemo(ds);

        Long id = 1L;
        demo.deleteRecord(id);
    }

    public void deleteRecord(Long id) {
        // Creates an instance of JdbcTemplate and supply a data
        // source object.
        JdbcTemplate template = new JdbcTemplate(this.dataSource);

        // Delete a record from database where the record
        // id matches with the specified parameter.
        Object[] params = {id};
        int rows = template.update(DeleteDemo.QUERY, params);
        System.out.println(rows + " row(s) deleted.");
    }
}

Maven Dependencies

<dependencies>
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-jdbc</artifactId>
        <version>6.1.10</version>
    </dependency>
    <dependency>
        <groupId>com.mysql</groupId>
        <artifactId>mysql-connector-j</artifactId>
        <version>8.4.0</version>
    </dependency>
</dependencies>

Maven Central Maven Central

How do I update records in the database using JdbcTemplate?

By I Wayan Saryada in Spring JDBC 0 Comment

The example demonstrated below will show you how to use the JdbcTemplate.update() method for updating records in database. This method returns an integer value indicating number of records updated in the database when the query is executed.

package org.kodejava.spring.jdbc;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

import javax.sql.DataSource;
import java.sql.Types;
import java.util.Date;

public class UpdateDemo {
    public static final String DRIVER = "com.mysql.cj.jdbc.Driver";
    public static final String URL = "jdbc:mysql://localhost/musicdb";
    public static final String USERNAME = "root";
    public static final String PASSWORD = "";

    public static final String QUERY =
            "UPDATE record SET title = ?, release_date = ? WHERE id = ?";

    private final DataSource dataSource;

    public UpdateDemo(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    public static void main(String[] args) {
        DataSource ds = getDataSource();
        UpdateDemo demo = new UpdateDemo(ds);

        Long id = 2L;
        String title = "The Beatles 1967 - 1970";
        Date releaseDate = new Date();
        demo.updateRecord(id, title, releaseDate);
    }

    /**
     * Returns a data source object.
     *
     * @return a DataSource.
     */
    public static DataSource getDataSource() {
        DriverManagerDataSource ds = new DriverManagerDataSource();
        ds.setDriverClassName(UpdateDemo.DRIVER);
        ds.setUrl(UpdateDemo.URL);
        ds.setUsername(UpdateDemo.USERNAME);
        ds.setPassword(UpdateDemo.PASSWORD);
        return ds;
    }

    public void updateRecord(Long id, String title, Date releaseDate) {
        // Creates an instance of JdbcTemplate and set the DataSource.
        // We can use the template update() method to update records
        // in the database. Below we use an update() method that accepts
        // three parameters: the sql query, the parameter values and
        // the parameter data types.
        JdbcTemplate template = new JdbcTemplate(this.dataSource);

        Object[] params = {title, releaseDate, id};
        int[] types = {Types.VARCHAR, Types.DATE, Types.BIGINT};

        int rows = template.update(UpdateDemo.QUERY, params, types);
        System.out.println(rows + " row(s) updated.");
    }
}

Maven Dependencies

<dependencies>
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-jdbc</artifactId>
        <version>6.1.10</version>
    </dependency>
    <dependency>
        <groupId>com.mysql</groupId>
        <artifactId>mysql-connector-j</artifactId>
        <version>8.4.0</version>
    </dependency>
</dependencies>

Maven Central Maven Central

How do I insert a new record into a database using JdbcTemplate?

By I Wayan Saryada in Spring JDBC 3 Comments

The following example show you how to use the Spring’s JdbcTemplate class to insert a record into database.

package org.kodejava.spring.jdbc;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

import javax.sql.DataSource;
import java.sql.Types;
import java.util.Date;

public class InsertDemo {
    private static final String INSERT_QUERY = """
            INSERT INTO record (title, release_date, artist_id, label_id, created)
            VALUES (?, ?, ?, ?, ?)
            """;

    private final DataSource dataSource;

    public InsertDemo(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    public static DriverManagerDataSource getDataSource() {
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setUrl("jdbc:mysql://localhost/musicdb");
        dataSource.setUsername("root");
        dataSource.setPassword("");
        return dataSource;
    }

    public static void main(String[] args) {
        InsertDemo demo = new InsertDemo(getDataSource());
        demo.saveRecord("Rock Beatles", new Date(), 1, 1);
    }

    public void saveRecord(String title, Date releaseDate, Integer artistId, Integer labelId) {
        // Creates an instance of JdbcTemplate and passes a connection
        // to the database.
        JdbcTemplate template = new JdbcTemplate(this.dataSource);

        // Defines the query arguments and the corresponding SQL types
        // of the arguments.
        Object[] params = new Object[]{
                title, releaseDate, artistId, labelId, new Date()
        };
        int[] types = new int[]{
                Types.VARCHAR,
                Types.DATE,
                Types.INTEGER,
                Types.INTEGER,
                Types.DATE
        };

        // Calls JdbcTemplate.update() methods to create a new data
        // in the records table. The update method in general will
        // return number of row / rows processed by the executed query
        int row = template.update(INSERT_QUERY, params, types);
        System.out.println(row + " row inserted.");
    }
}

Maven Dependencies

<dependencies>
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-jdbc</artifactId>
        <version>6.1.10</version>
    </dependency>
    <dependency>
        <groupId>com.mysql</groupId>
        <artifactId>mysql-connector-j</artifactId>
        <version>8.4.0</version>
    </dependency>
</dependencies>

Maven Central Maven Central