SimpleDriverDataSource dataSource = new SimpleDriverDataSource(); // code to set driver class name, database URL, username and password JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);Or setting the DataSource reference via setter:
JdbcTemplate jdbcTemplate = new JdbcTemplate(); jdbcTemplate.setDataSource(dataSource);Then use variants of the JdbcTemplate’supdate() and query() methods for execute updates and queries. For example:
jdbcTemplate.update("INSERT INTO..."); List<T> results = jdbcTemplate.query("SELECT * FROM...", new RowMapper<T>() {...});For querying for results, an implementation (typically an anonymous class) of the RowMapper interface must be provided in order to map a row from the result set to an object in the returned list. Let’s see the details by coding a sample project.
CREATE DATABASE contactdb; CREATE TABLE `contact` ( `contact_id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(45) NOT NULL, `email` varchar(45) NOT NULL, `address` varchar(45) NOT NULL, `telephone` varchar(45) NOT NULL, PRIMARY KEY (`contact_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8
<dependency> <groupId>org.springframework</groupId> <artifactId>spring-jdbc</artifactId> <version>4.0.3.RELEASE</version> </dependency>
<dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.30</version> </dependency>
package net.codejava.spring; /** * * @author www.codejava.net * */ public class Contact { private String name; private String email; private String address; private String phone; // getters and setters are removed for brevity. public String toString() { return String.format("[%s - %s - %s - %s]", name, email, address, phone); } }This class simply maps a row in the contact table to a Java object.
package net.codejava.spring; import java.sql.ResultSet; import java.sql.SQLException; import java.util.List; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.core.RowMapper; import org.springframework.jdbc.datasource.SimpleDriverDataSource; /** * This console application demonstrates how to do CRUD operations using JDBC * with Spring framework. * @author www.codejava.net * */ public class SpringJDBCTemplateExample { public static void main(String[] args) throws SQLException { SimpleDriverDataSource dataSource = new SimpleDriverDataSource(); dataSource.setDriver(new com.mysql.jdbc.Driver()); dataSource.setUrl("jdbc:mysql://localhost/contactdb"); dataSource.setUsername("root"); dataSource.setPassword("P@ssw0rd"); JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource); String sqlInsert = "INSERT INTO contact (name, email, address, telephone)" + " VALUES (?, ?, ?, ?)"; jdbcTemplate.update(sqlInsert, "Tom", "tomea@mail.com", "USA", "12345"); String sqlUpdate = "UPDATE contact set email=? where name=?"; jdbcTemplate.update(sqlUpdate, "tomee@mail.com", "Tom"); String sqlSelect = "SELECT * FROM contact"; List<Contact> listContact = jdbcTemplate.query(sqlSelect, new RowMapper<Contact>() { public Contact mapRow(ResultSet result, int rowNum) throws SQLException { Contact contact = new Contact(); contact.setName(result.getString("name")); contact.setEmail(result.getString("email")); contact.setAddress(result.getString("address")); contact.setPhone(result.getString("telephone")); return contact; } }); for (Contact aContact : listContact) { System.out.println(aContact); } String sqlDelete = "DELETE FROM contact1 where name=?"; jdbcTemplate.update(sqlDelete, "Tom"); } }For executing a query that select all rows from the tabble and returns a list of domain model class objects, you can use the BeanPropertyRowMapper class greatly simplifies the code, for example:
String sql = "SELECT * FROM Contact"; List<Contact> listContacts = jdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(Contact.class));As long as the Contact class has the fields exactly match the columns in the database table.You can also use the BeanPropertyRowMapper class for a query that returns a single object, for example:
String sql = "SELECT * FROM Contact WHERE id=" + id; Contact contact = jdbcTemplate.queryForObject(sql, BeanPropertyRowMapper.newInstance(Contact.class));NOTES:
try { String sqlDelete = "DELETE FROM contact where name=?"; jdbcTemplate.update(sqlDelete, "Tom"); } catch (DataAccessException ex) { ex.printStackTrace(); }