This example shows how to retrieve auto generated primary key by the database (via an insert statement). Following method of JdbcTemplate takes KeyHolder argument which will contain the generated key on the successful insert execution.
public int update(final PreparedStatementCreator psc, final KeyHolder generatedKeyHolder)
throws DataAccessException
Example using KeyHolder
@Component
public class CustomerBean {
@Autowired
private JdbcTemplate jdbcTemplate;
public void createCustomers() {
String sql = "insert into CUSTOMER (NAME, PHONE, ADDRESS) values (?, ?, ?)";
KeyHolder keyHolder = new GeneratedKeyHolder();
jdbcTemplate.update(
connection -> {
PreparedStatement ps = connection.prepareStatement(sql, new String[]{"ID"});
ps.setString(1, "Jake");
ps.setString(2, "234-333-627");
ps.setString(3, "345 Move Dr, Shine Hill");
return ps;
}, keyHolder);
Number key = keyHolder.getKey();
System.out.println("Newly persisted customer generated id: " + key.longValue());
System.out.println("-- loading customer by id --");
System.out.println(loadCustomerById(key.longValue()));
}
public Customer loadCustomerById(long id) {
List<Customer> customers = jdbcTemplate.query("select * from CUSTOMER where id =?",
new Object[]{id}, (resultSet, i) -> {
return Customer.create(
resultSet.getLong("ID"),
resultSet.getString("NAME"),
resultSet.getString("PHONE"),
resultSet.getString("ADDRESS"));
});
if (customers.size() == 1) {
return customers.get(0);
}
return null;
}
}
public class Customer {
private long id;
private String name;
private String address;
private String phone;
.............
}
src/main/resources/createCustomerTable.sqlCREATE TABLE CUSTOMER(
ID BIGINT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(255),
PHONE VARCHAR(15),
ADDRESS VARCHAR(255)
);
Configuration and main class
@Configuration
@ComponentScan
public class AppConfig {
@Bean
public DataSource h2DataSource() {
return new EmbeddedDatabaseBuilder()
.setType(EmbeddedDatabaseType.H2)
.addScript("createCustomerTable.sql")
.build();
}
@Bean
public JdbcTemplate jdbcTemplate() {
return new JdbcTemplate(h2DataSource());
}
public static void main(String[] args) {
AnnotationConfigApplicationContext context =
new AnnotationConfigApplicationContext(AppConfig.class);
CustomerBean customerBean = context.getBean(CustomerBean.class);
customerBean.createCustomers();
}
} OutputNewly persisted customer generated id: 1 -- loading customer by id -- Customer{id=1, name='Jake', address='234-333-627', phone='345 Move Dr, Shine Hill'}
Example ProjectDependencies and Technologies Used: - spring-context 4.3.6.RELEASE: Spring Context.
- spring-jdbc 4.3.6.RELEASE: Spring JDBC.
- h2 1.4.196: H2 Database Engine.
- JDK 1.8
- Maven 3.3.9
|