How do I get the auto-generated key in JdbcTemplate?

By Wayan in Spring JDBC 0 Comment

Spring JDBC simplifies database operations by providing an abstraction layer over traditional JDBC. In this blog post, we’ll walk through a practical example of using Spring JDBC to insert a record into a database and obtain the generated key for the newly inserted record.

First, let’s look at the complete code snippet:

package org.kodejava.spring.jdbc;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import java.math.BigInteger;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.Timestamp;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.Month;

public class InsertDemoObtainsKey {
    public static void main(String[] args) {
        String insert = "INSERT INTO record (title, release_date, artist_id, label_id, created) VALUES (?, ?, ?, ?, ?)";

        JdbcTemplate template = new JdbcTemplate(new DriverManagerDataSource("jdbc:mysql://localhost/musicdb", "root", ""));
        KeyHolder keyHolder = new GeneratedKeyHolder();

        template.update(con -> {
            PreparedStatement ps = con.prepareStatement(insert, PreparedStatement.RETURN_GENERATED_KEYS);
            ps.setString(1, "Let It Be");
            ps.setDate(2, Date.valueOf(LocalDate.of(1970, Month.MAY, 8)));
            ps.setLong(3, 1L);
            ps.setLong(4, 1L);
            ps.setTimestamp(5, Timestamp.valueOf(LocalDateTime.now()));
            return ps;
        }, keyHolder);

        BigInteger key = (BigInteger) keyHolder.getKeys();
        System.out.println("key = " + key);
    }
}

This code demonstrates how to insert a record into a database table and retrieve the generated key using Spring’s JdbcTemplate.

DataSource Configuration

JdbcTemplate template = new JdbcTemplate(new DriverManagerDataSource("jdbc:mysql://localhost/musicdb", "root", ""));

Here, we create a JdbcTemplate instance, providing it with a DriverManagerDataSource. This data source points to our MySQL database named musicdb, with the username root and an empty password.

SQL Insert Statement

String insert = "INSERT INTO record (title, release_date, artist_id, label_id, created) VALUES (?, ?, ?, ?, ?)";

We define our SQL insert statement with placeholders for the values to be inserted. The table record is assumed to have columns title, release_date, artist_id, label_id, and created.

KeyHolder Initialization

KeyHolder keyHolder = new GeneratedKeyHolder();

A KeyHolder is used to hold the generated keys. In this case, we’re using a GeneratedKeyHolder.

Executing the Update

template.update(con -> {
PreparedStatement ps = con.prepareStatement(insert, PreparedStatement.RETURN_GENERATED_KEYS);
    ps.setString(1, "Let It Be");
    ps.setDate(2, Date.valueOf(LocalDate.of(1970, Month.MAY, 8)));
    ps.setLong(3, 1L);
    ps.setLong(4, 1L);
    ps.setTimestamp(5, Timestamp.valueOf(LocalDateTime.now()));
    return ps;
}, keyHolder);

We use the JdbcTemplate‘s update method, which takes two parameters: a PreparedStatementCreator and a KeyHolder. The PreparedStatementCreator is implemented using a lambda expression that sets the values for the placeholders in our SQL insert statement.

Retrieving the Generated Key

BigInteger key = (BigInteger) keyHolder.getKeys();
System.out.println("key = " + key);

After the update operation, we retrieve the generated key from the KeyHolder. The key is then printed to the console.

Summary

Using Spring JDBC simplifies the process of interacting with the database. In this example, we demonstrated how to insert a record into a MySQL database and retrieve the generated key using JdbcTemplate. This approach ensures clean and manageable code while leveraging the power of Spring’s abstraction over traditional JDBC.

Feel free to experiment with this code, adjusting the database connection details and SQL statements to fit your requirements. Happy coding!

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 select records from database using JdbcTemplate?

By Wayan 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 Wayan 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 Wayan 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 Wayan 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