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

By Wayan in Spring JDBC 2 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>
    <!-- https://search.maven.org/remotecontent?filepath=org/springframework/spring-jdbc/5.3.21/spring-jdbc-5.3.21.jar -->
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-jdbc</artifactId>
        <version>5.3.21</version>
    </dependency>
    <!-- https://search.maven.org/remotecontent?filepath=mysql/mysql-connector-java/8.0.29/mysql-connector-java-8.0.29.jar -->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.29</version>
    </dependency>
</dependencies>

Maven Central Maven Central

Latest posts by Wayan (see all)
Views: 20,335

2 Comments

    • Hi Anant,

      Using Spring JDBCTemplate you can do it in the following way to get the generated key.

      KeyHolder keyHolder = new GeneratedKeyHolder();
template.update(connection -> {
    PreparedStatement ps = connection.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
    ps.setString(1, title);
    ps.setDate(2, new java.sql.Date(releaseDate.getTime()));
    ps.setInt(3, artistId);
    ps.setInt(4, labelId);
    ps.setDate(5, new java.sql.Date(new Date().getTime()));
    return ps;
}, keyHolder);

System.out.println("Key: " + Objects.requireNonNull(keyHolder.getKey()).longValue());
      Reply

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.