Spring SimpleJdbcInsert example

In this tutorial is about inserting data into database table using Spring SimpleJdbcInsert. Let’s start by looking at the Spring SimpleJdbcInsert class with the minimal amount of configuration options. You should instantiate the SimpleJdbcInsert in the data access layer’s initialization method.

Popular Tutorials

This approach minimize the database metadata. This optimization is to bound the amount of necessary configuration. In this approach you need to provide name of table and a map of parameters matching the column name. This works only if database provide sufficient metadata. In case it is not provided adequately, then you have to provide explicit configuration of the parameters.

Using database metadata, SimpleJdbcInsert provide a easy configuration. The database metadata can be fetched via JDBC driver. It means SimpleJdbcInsert provide minimum configuration options. The SimpleJdbcInsert must be initialize inside setDataSource method and table name can be set using “withTableName” method.

1. Inserting data using Spring SimpleJdbcInsert:-

Let’s start by looking at the SimpleJdbcInsert class with the minimal amount of configuration options. You should instantiate the SimpleJdbcInsert in the data access layer’s initialization method. For this example, the initializing method is the setDataSource method. You do not need to subclass the SimpleJdbcInsert class; simply create a new instance and set the table name using the withTableName method. Configuration methods for this class follow the “fluid” style that returns the instance of the SimpleJdbcInsert, which allows you to chain all configuration methods. This example uses only one configuration method; you will see examples of multiple ones later.

public class EmployeeDaoImpl implements EmpDao {
    private SimpleJdbcTemplate simpleJdbcTemplate;
    private SimpleJdbcInsert insertEmp;

    public void setDataSource(DataSource dataSource) {
        this.simpleJdbcTemplate = new SimpleJdbcTemplate(dataSource);
        this.insertEmp = 
                new SimpleJdbcInsert(dataSource).withTableName("employee");
    }

    public void create(String name, Integer age, Long salary) {
        Map parameters = new HashMap();
        parameters.put("name", name);
        parameters.put("age", age);
        parameters.put("salary", salary);
        insertEmp.execute(parameters);
    }

    //  ... additional methods
}

The execute method used here takes a plain java.utils.Map as its only parameter. The important thing to note here is that the keys used for the Map must match the column names of the table as defined in the database. This is because we read the metadata in order to construct the actual insert statement.

2. Retrieving auto-generated keys using Spring SimpleJdbcInsert:-

When you create the SimpleJdbcInsert, in addition to specifying the table name, you specify the name of the generated key column with the usingGeneratedKeyColumns method.

public class EmployeeDaoImpl implements EmpDao {
    private SimpleJdbcTemplate simpleJdbcTemplate;
    private SimpleJdbcInsert insertEmp;

    public void setDataSource(DataSource dataSource) {
        this.simpleJdbcTemplate = new SimpleJdbcTemplate(dataSource);
        this.insertEmp = 
                new SimpleJdbcInsert(dataSource)
                 .withTableName("employee");
                 .usingGeneratedKeyColumns("empid");
    }

    public void create(String name, Integer age, Long salary) {
        Map parameters = new HashMap();
        parameters.put("name", name);
        parameters.put("age", age);
        parameters.put("salary", salary);
        Number empid = insertEmp.executeAndReturnKey(parameters);
    }

    //  ... additional methods
}

3. Specifying columns for a SimpleJdbcInsert:-

You can limit the columns for an insert by specifying a list of column names with the usingColumns method:

public class EmployeeDaoImpl implements EmpDao {
    private SimpleJdbcTemplate simpleJdbcTemplate;
    private SimpleJdbcInsert insertEmp;

    public void setDataSource(DataSource dataSource) {
        this.simpleJdbcTemplate = new SimpleJdbcTemplate(dataSource);
        this.insertEmp = 
                new SimpleJdbcInsert(dataSource)
                 .withTableName("employee");
                 .usingColumns("name", "age", "salary")
                 .usingGeneratedKeyColumns("empid");
    }

    public void create(String name, Integer age, Long salary) {
        Map parameters = new HashMap();
        parameters.put("name", name);
        parameters.put("age", age);
        parameters.put("salary", salary);
        Number empid = insertEmp.executeAndReturnKey(parameters);
    }

    //  ... additional methods
}

Spring JDBC Framework Tutorial