Spring JdbcTemplate example to get a large ResultSet and process it. P.S Tested with Java 8 and Spring JDBC 5.1.4.RELEASE 1. Get large ResultSet 1.1 Below is a classic findAll to get all data from a table. BookRepository.java public List<Book> findAll() { return jdbcTemplate.query( "select * from books", (rs, rowNum) -> new Book( rs.getLong("id"), rs.getString("name"), […]
Below example, the jdbcTemplate.queryForList returns an object of Integer and we try to convert it into a Long directly: public List<Customer> findAll() { String sql = "SELECT * FROM CUSTOMER"; List<Customer> customers = new ArrayList<>(); List<Map<String, Object>> rows = jdbcTemplate.queryForList(sql); for (Map row : rows) { Customer obj = new Customer(); obj.setID(((Long) row.get("ID"))); // the […]
In this tutorial, we will show you how to use Spring Boot JDBC SimpleJdbcCall to call a stored procedure and stored function from a Oracle database. Technologies used : Spring Boot 2.1.2.RELEASE Spring JDBC 5.1.4.RELEASE Oracle database 19c HikariCP 3.2.0 Maven 3 Java 8 Unlike JdbcTemplate, Spring Boot didn’t create any SimpleJdbcCall automatically, we have […]
In this article, we will show you how to create a Spring Boot JDBC application + Oracle database + Commons DBCP2 connection pool. Tools used in this article : Spring Boot 1.5.1.RELEASE Oracle database 11g express Oracle JDBC driver ojdbc7.jar Commons DBCP2 2.1.1 Maven Java 8 Note Related – Spring Boot JDBC + MySQL + […]
In this article, we will show you how to create a Spring Boot JDBC application + MySQL and HikariCP. Tools used in this article : Spring Boot 1.5.1.RELEASE MySQL 5.7.x HikariCP 2.6 Maven Java 8 Note Related – Spring Boot JDBC + Oracle database + Commons DBCP2 example 1. Project Structure A standard Maven project […]
A Spring @Configuration example to start an HSQLDB embedded database or in-memory database. DataSourceConfig.java package com.mkyong.config.db; import javax.sql.DataSource; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration; import org.springframework.jdbc.datasource.embedded.EmbeddedDatabase; import org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseBuilder; import org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseType; @Configuration public class DataSourceConfig { @Bean public DataSource dataSource(){ //jdbc:hsqldb:mem:testdb EmbeddedDatabaseBuilder builder = new EmbeddedDatabaseBuilder(); EmbeddedDatabase db = builder.setType(EmbeddedDatabaseType.HSQL) .addScript("db/hsqldb/db.sql") .build(); return db; } } Review the […]
A Simple DAO class extends JdbcDaoSupport, but, unable to inject or @autowired a “dataSource”, the method setDataSource is final, can’t override. UserDetailsDaoImpl.java package com.mkyong.users.dao; import javax.sql.DataSource; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.jdbc.core.support.JdbcDaoSupport; import org.springframework.stereotype.Repository; @Repository public class UserDetailsDaoImpl extends JdbcDaoSupport implements UserDetailsDao { //Error, cannot override the final method from JdbcDaoSupport @Autowired public void setDataSource(DataSource dataSource) { […]
Upgrading Spring version and noticed that queryForInt() is deprecated, what should be replaced by? private boolean isUserExists(String username) { String sql = "SELECT count(*) FROM USERS WHERE username = ?"; boolean result = false; //The method queryForInt(String, Object…) from the type JdbcTemplate is deprecated int count = getJdbcTemplate().queryForInt(sql, new Object[] { username }); if (count […]
Reviewing a legacy project, and found this Spring JDBC code snippets : public User getUser(String username) { String sql = "SELECT * FROM USER WHERE username = ?"; return getJdbcTemplate().queryForObject( sql, new Object[] { username }, new RowMapper<UserAttempts>() { public UserAttempts mapRow(ResultSet rs, int rowNum) throws SQLException { User user = new User(); user.setId(rs.getInt("id")); user.setUsername(rs.getString("username")); […]
Here are a few examples to show you how to use Spring JdbcTemplate to query or extract data from database. Technologies used : Spring Boot 2.1.2.RELEASE Spring JDBC 5.1.4.RELEASE Maven 3 Java 8 In Short: jdbcTemplate.queryForObject for single row or value jdbcTemplate.query for multiple rows or list Note The article is updated from Spring core […]
Spring JdbcTemplate batch insert, batch update and also @Transactional examples. Technologies used : Spring Boot 2.1.2.RELEASE Spring JDBC 5.1.4.RELEASE Maven 3 Java 8 1. Batch Insert 1.1 Insert a batch of SQL Inserts together. BookRepository.java import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.core.BatchPreparedStatementSetter; public int[] batchInsert(List<Book> books) { return this.jdbcTemplate.batchUpdate( "insert into books (name, price) values(?,?)", new BatchPreparedStatementSetter() { […]
