This is an example of how to insert a record to the database using the JdbcTemplate class provided by the Spring Framework. The JdbcTemplate class is the central class in the JDBC core package. It simplifies the use of JDBC and helps to avoid common errors. It executes core JDBC workflow, leaving application code to provide SQL and extract results. This class executes SQL queries or updates, initiating iteration over ResultSets and catching JDBC exceptions. Inserting a record to the database with JdbcTemplate class implies that you should:
- Use the
DataSourceclass, a utility class that provides connection to the database. It is part of the JDBC specification and allows a container or a framework to hide connection pooling and transaction management issues from the application code. We implement it using theorg.springframework.jdbc.datasource.DriverManagerDataSource. Set the credentials needed to the datasource, using the inherited methodssetPassword(String password),setUrl(String url)andsetUsername(String username)API methods ofAbstractDriverBasedDataSourceclass, as also thesetDriverClassName(String driverClassName)API method ofDriverManagerDataSource. Create a newDatasourceobject having the above configuration. Here ingetDatasource()method we create a newDatasourceand configure it. - Create a new
JdbcTemplateobject, with the given datasource to obtain connections from. - Use the
update(String sql, Object args, int[] argTypes)API method ofJdbcTemplate, to issue the SQL insert operation via a prepared statement, binding the given arguments. The given parameters are the String containing the sql query, the arguments to bind the query, and the types of the arguments. It returns the number of rows processed by the executed query.
Let’s take a look at the code snippet that follows:
package com.javacodegeeks.snippets.enterprise;
import java.sql.Types;
import java.util.Date;
import javax.sql.DataSource;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
public class InsertRecordInDatabaseWithJdbcTemplate {
private static final String driverClassName = "com.mysql.jdbc.Driver";
private static final String url = "jdbc:mysql://localhost/companydb";
private static final String dbUsername = "jcg";
private static final String dbPassword = "jcg";
private static final String insertSql =
"INSERT INTO employee (" +
" name, " +
" surname, " +
" title, " +
" created) " +
"VALUES (?, ?, ?, ?)";
private static DataSource dataSource;
public static void main(String[] args) throws Exception {
dataSource = getDataSource();
saveRecord("John", "Black", "Software developer", new Date());
saveRecord("Tom", "Green", "Project Manager", new Date());
}
public static void saveRecord(String name, String surname, String title, Date created) {
JdbcTemplate template = new JdbcTemplate(dataSource);
// define query arguments
Object[] params = new Object[] { name, surname, title, new Date() };
// define SQL types of the arguments
int[] types = new int[] { Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.TIMESTAMP };
// execute insert query to insert the data
// return number of row / rows processed by the executed query
int row = template.update(insertSql, params, types);
System.out.println(row + " row inserted.");
}
public static DriverManagerDataSource getDataSource() {
DriverManagerDataSource dataSource = new DriverManagerDataSource();
dataSource.setDriverClassName(driverClassName);
dataSource.setUrl(url);
dataSource.setUsername(dbUsername);
dataSource.setPassword(dbPassword);
return dataSource;
}
}
CREATE TABLE `companydb`.`employee` ( `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, `name` VARCHAR(45) NOT NULL, `surname` VARCHAR(45) NOT NULL, `title` VARCHAR(45) NOT NULL, `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`) );
Output:
1 row inserted.
1 row inserted.
This was an example of how to insert a record to the database using the JdbcTemplate class provided by the Spring Framework in Java.
