Spring > Databases

Guides

POM

pom.xml
<?xml version="1.0" encoding="UTF-8"?>
<project ...>
 
    <!-- ... -->
 
    <dependencies>
        <!-- ... -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>com.h2database</groupId>
            <artifactId>h2</artifactId>
        </dependency>
    </dependencies>
 
    <!-- ... -->
 
</project>

Store and retrieve data

Application.java
package hello;
 
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.List;
 
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
 
@SpringBootApplication
public class Application implements CommandLineRunner {
 
    private static final Logger log = LoggerFactory.getLogger(Application.class);
 
    public static void main(String args[]) {
        SpringApplication.run(Application.class, args);
    }
 
    @Autowired
    JdbcTemplate jdbcTemplate;
 
    @Override
    public void run(String...strings) throws Exception {
 
    	log.info("Creating tables");
 
    	jdbcTemplate.execute("DROP TABLE customers IF EXISTS");
    	jdbcTemplate.execute("CREATE TABLE customers(id SERIAL, first_name VARCHAR(255), last_name VARCHAR(255))");
 
	List<Object[]> splitUpNames = Arrays.asList(new String[] { "John", "Woo" },   new String[] { "Jeff", "Dean" },
				                    new String[] { "Josh", "Bloch" }, new String[] { "Josh", "Long" });
 
	for (Object[] name : splitUpNames) {
		log.info(String.format("Inserting customer record for %s %s", name[0], name[1]));
	}
 
	// Uses JdbcTemplate's batchUpdate operation to bulk load data
        jdbcTemplate.batchUpdate("INSERT INTO customers(first_name, last_name) VALUES (?,?)", splitUpNames);
 
        log.info("Querying for customer records where first_name = 'Josh':");
        RowMapper<Customer> rm = new RowMapper<Customer>() {
        	public Customer mapRow(ResultSet rs, int rowNum) throws SQLException {
        		return new Customer(rs.getLong("id"), rs.getString("first_name"), rs.getString("last_name"));
        	}
        };
        List<Customer> customers = jdbcTemplate.query("SELECT id, first_name, last_name FROM customers WHERE first_name = ?", new Object[] { "Josh" }, rm);
        for (Customer customer : customers) {
        	log.info(customer.toString());
        }
    }
 
}

Or if you use Java 1.8 :

Application.java
// ...
        // Split up the array of whole names into an array of first/last names
        List<Object[]> splitUpNames = Arrays.asList("John Woo", "Jeff Dean", "Josh Bloch", "Josh Long").stream()
                .map(name -> name.split(" "))
                .collect(Collectors.toList());
 
        // Use a Java 8 stream to print out each tuple of the list
        splitUpNames.forEach(name -> log.info(String.format("Inserting customer record for %s %s", name[0], name[1])));
 
        // Uses JdbcTemplate's batchUpdate operation to bulk load data
        jdbcTemplate.batchUpdate("INSERT INTO customers(first_name, last_name) VALUES (?,?)", splitUpNames);
 
        log.info("Querying for customer records where first_name = 'Josh':");
        jdbcTemplate.query(
                "SELECT id, first_name, last_name FROM customers WHERE first_name = ?", new Object[] { "Josh" },
                (rs, rowNum) -> new Customer(rs.getLong("id"), rs.getString("first_name"), rs.getString("last_name"))
        ).forEach(customer -> log.info(customer.toString()));
// ...
  • The JdbcTemplate (“template class”) makes it easy to work with SQL relational databases and JDBC : Resource acquisition, connection management, exception handling, general error checking…
  • Because we are using spring-jdbc, Spring Boot automatically creates a JdbcTemplate. The @Autowired JdbcTemplate field automatically loads it and makes it available.
  • Spring Boot spots H2, an in-memory relational database engine, and automatically creates a connection.
  • For single insert statements, JdbcTemplate’s insert method is good. But for multiple inserts, it’s better to use batchUpdate.
  • Customer is a POJO with properties id (long), firstName (String), lastName (String)
Print/export