This Spring JDBC tutorial helps you understand how to use the SimpleJdbcInsert class with some code examples. The SimpleJdbcInsert class simplifies writing code to execute SQL INSERT statement, i.e. you don’t have to write lengthy and tedious SQL Insert statement anymore – just specify the table name, column names and parameter values.

 

1. Basic SimpleJdbcInsert Example

If using the JdbcTemplate class, you have to write an SQL Insert statement like this:

JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
String sqlInsert = "INSERT INTO contact (name, email, address) VALUES (?, ?, ?)";
jdbcTemplate.update(sqlInsert, "Tom", "tomea@mail.com", "USA");

Now, let’s see how to use the SimpleJdbcInsert class. First, create a new instance:

SimpleJdbcInsert insertActor = new SimpleJdbcInsert(dataSource);

Next, specify the table name to insert data:

insertActor.withTableName("contact");

Next, create a Map of parameters:

Map<String, Object> params = new HashMap<>();
params.put("name", "Bill Gates");
params.put("email", "bill@microsoft.com");
params.put("address", "Seattle, USA");

Note that the key for each entry is the name of a column in the table. Then execute the statement like this:

int result = insertActor.execute(params);

The returned value is the number of rows affected by the query. The whole code snippet would be as follows:

SimpleJdbcInsert insertActor = new SimpleJdbcInsert(dataSource);
insertActor.withTableName("contact");

Map<String, Object> params = new HashMap<>();
params.put("name", "Bill Gates");
params.put("email", "bill@microsoft.com");
params.put("address", "Seattle, USA");

int result = insertActor.execute(params);

if (result > 0) {
	System.out.println("Insert Successfully!");
}

You see, you don’t have to write SQL Insert statement. And using named parameters same as the column names make the code more readable.

 

2. SimpleJdbcInsert Example that returns value of the auto-generated primary key

You can use the SimpleJdbcInsert class to execute SQL Insert statement and return value of the primary column which is auto-generated. Specify the name of the primary column as follows:

SimpleJdbcInsert insertActor = new SimpleJdbcInsert(dataSource);
insertActor.withTableName("contact").usingGeneratedKeyColumns("contact_id");

And then specify the parameter values and execute:

MapSqlParameterSource params = new MapSqlParameterSource();

params.addValue("name", "Steve Jobs")
	.addValue("email", "steve@apple.com")
	.addValue("address", "USA");

Number newId = insertActor.executeAndReturnKey(params);

if (newId != null) {
	System.out.println("Insert Successfully. New Id = " + newId.intValue());
}

You see, the executeAndReturnKey() method returns a Number object holding value of the auto-generated key of the primary column.

Also in this example, we use MapSqlParameterSource instead of HashMap to specify parameter values because it allows us to chain multiple addValue() methods together. Learn more about using named parameters with Spring JDBC in this tutorial.

 

3. SimpleJdbcInsert Example with BeanPropertySqlParameterSource

If you have a domain model class that follows JavaBean convention, you can use the SimpleJdbcInsert class with a BeanPropertySqlParameterSource class. Consider the following example:

SimpleJdbcInsert insertActor = new SimpleJdbcInsert(dataSource);
insertActor.withTableName("contact");

BeanPropertySqlParameterSource paramSource = new BeanPropertySqlParameterSource(contact);

int result = insertActor.execute(paramSource);

if (result > 0) {
	System.out.println("Insert Successfully!");
}

As you can see, you don’t even have to specify parameter names and values, as long as you provide an object that has attributes same as the column names in the database table. In the above code, the contact variable is an object of the Contact class as follows:

public class Contact {
	private Integer id;
	private String name;
	private String email;
	private String address;

	// getters 
	// setters
	
}

That’s how to use the SimpleJdbcInsert class in Spring JDBC. To understand how to configure your project to use Spring JDBC, please refer to the following tutorials:

• Spring JdbcTemplate Simple Example
• Java Spring MVC with JdbcTemplate Tutorial

 

Related Spring and Database Tutorials:

• How to configure Spring MVC JdbcTemplate with JNDI Data Source in Tomcat
• Spring and Hibernate Integration Tutorial (XML Configuration)
• Understand Spring Data JPA with Simple Example
  
• Spring MVC + Spring Data JPA + Hibernate - CRUD Example

 

Other Spring Tutorials:

• Understand the core of Spring framework
• Understand Spring MVC
• Understand Spring AOP
• Spring MVC beginner tutorial with Spring Tool Suite IDE
• Spring MVC Form Handling Tutorial
• Spring MVC Form Validation Tutorial
• 14 Tips for Writing Spring MVC Controller
• Spring Web MVC Security Basic Example (XML Configuration)