Configuring a DataSource
- Configure Embedded Database or In Memory Database
- Configure Production Database
- Configure Database by using JNDI
1. Configure Embedded Database or In Memory Database
<dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <dependency> <groupId>org.hsqldb</groupId> <artifactId>hsqldb</artifactId> <scope>runtime</scope> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-jdbc</artifactId> </dependency> </dependencies>
- The spring-boot-starter-jdbc module transitively pulls tomcat-jdbc-{version}.jar which is used to configure the DataSource bean. In the above dependencies we have included the JDBC dependency – this gives us JdbcTemplate and other JDBC libraries, the org.hsqldb dependency adds embedded hsqldb.
- If you have not defined any DataSource bean explicitly and if you have any embedded database driver in classpath such as H2, HSQL or Derby then SpringBoot will automatically registers DataSource bean using in-memory database settings.
- These embedded DBs are in-memory and each time the application shuts down the schema and data gets erased. One way to keep schema and data in the in-memory is to populate it during application startup. This is taken care by Spring Boot.
- We can have schema.sql and data.sql files in root classpath which SpringBoot will automatically use to initialize database. Spring JDBC uses these sql files to create schema and populate data into the schema.
CREATE TABLE users(userId INTEGER NOT NULL,userName VARCHAR(100) NOT NULL,userEmail VARCHAR(100) DEFAULT NULL,address VARCHAR(100) DEFAULT NULL,PRIMARY KEY (userId));
insert into users(userId, userName, userEmail, address) values (1000, 'Dinesh', 'dinesh@gmail.com', 'Delhi'); insert into users(userId, userName, userEmail, address) values (1001, 'Kumar', 'kumar@gmail.com', 'Greater Noida'); insert into users(userId, userName, userEmail, address) values (1002, 'Rajput', 'rajput@gmail.com', 'Noida');
/** * */ package com.dineshonjava.model; /** * @author Dinesh.Rajput * */ public class User { private Integer userId; private String userName; private String userEmail; private String address; public Integer getUserId() { return userId; } public void setUserId(Integer userId) { this.userId = userId; } public String getUserName() { return userName; } public void setUserName(String userName) { this.userName = userName; } public String getUserEmail() { return userEmail; } public void setUserEmail(String userEmail) { this.userEmail = userEmail; } public String getAddress() { return address; } public void setAddress(String address) { this.address = address; } @Override public String toString() { return "User [userId=" + userId + ", userName=" + userName + ", userEmail=" + userEmail + ", address=" + address + "]"; } }
/** * */ package com.dineshonjava.service; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.SQLException; import java.sql.Statement; import java.util.List; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.core.PreparedStatementCreator; import org.springframework.jdbc.support.GeneratedKeyHolder; import org.springframework.jdbc.support.KeyHolder; import org.springframework.stereotype.Service; import org.springframework.transaction.annotation.Transactional; import com.dineshonjava.model.User; import com.dineshonjava.utils.UserRowMapper; /** * @author Dinesh.Rajput * */ @Service public class UserService { @Autowired private JdbcTemplate jdbcTemplate; @Transactional(readOnly=true) public List<User> findAll() { return jdbcTemplate.query("select * from users", new UserRowMapper()); } @Transactional(readOnly=true) public User findUserById(int id) { return jdbcTemplate.queryForObject( "select * from users where userId=?", new Object[]{id}, new UserRowMapper()); } public User create(final User user) { final String sql = "insert into users(userId,userName,userEmail,address) values(?,?,?,?)"; KeyHolder holder = new GeneratedKeyHolder(); jdbcTemplate.update(new PreparedStatementCreator() { @Override public PreparedStatement createPreparedStatement(Connection connection) throws SQLException { PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS); ps.setInt(1, user.getUserId()); ps.setString(2, user.getUserName()); ps.setString(3, user.getUserEmail()); ps.setString(4, user.getAddress()); return ps; } }, holder); int newUserId = holder.getKey().intValue(); user.setUserId(newUserId); return user; } } /** * */ package com.dineshonjava.utils; import java.sql.ResultSet; import java.sql.SQLException; import org.springframework.jdbc.core.RowMapper; import com.dineshonjava.model.User; /** * @author Dinesh.Rajput * */ public class UserRowMapper implements RowMapper<User>{ @Override public User mapRow(ResultSet rs, int rowNum) throws SQLException { User user = new User(); user.setUserId(rs.getInt("userId")); user.setUserName(rs.getString("userName")); user.setUserEmail(rs.getString("userEmail")); user.setAddress(rs.getString("address")); return user; } }
/** * */ package com.dineshonjava.controller; import java.util.List; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RestController; import com.dineshonjava.model.User; import com.dineshonjava.service.UserService; /** * @author Dinesh.Rajput * */ @RestController public class UserController { @Autowired UserService userService; @RequestMapping("/") User home(User user) { user = userService.create(user); return user; } @RequestMapping("/users") List<User> findAllUsers() { List<User> users = userService.findAll(); return users; } }
package com.dineshonjava; import org.springframework.boot.SpringApplication; import org.springframework.boot.autoconfigure.SpringBootApplication; @SpringBootApplication public class SpringBootDataBaseApplication { public static void main(String[] args) { SpringApplication.run(SpringBootDataBaseApplication.class, args); } }
2. Configure Production Database
- First we prefer the Tomcat pooling DataSource for its performance and concurrency, so if that is available we always choose it.
- Otherwise, if HikariCP is available we will use it.
- If Tomcat pooling datasource and HikariCP are not available then we can choose Commons DBCP, but we don’t recommend it in production.
- Lastly, if Commons DBCP2 is available we will use it.
spring.datasource.driver-class-name=com.mysql.jdbc.Driver spring.datasource.url=jdbc:mysql://localhost:3306/dojdb spring.datasource.username=root spring.datasource.password=root
# Number of ms to wait before throwing an exception if no connection is available. spring.datasource.tomcat.max-wait=10000 # Maximum number of active connections that can be allocated from this pool at the same time. spring.datasource.tomcat.max-active=50 # Validate the connection before borrowing it from the pool. spring.datasource.tomcat.test-on-borrow=true
Using another Connection Pooling library
- org.apache.tomcat.jdbc.pool.DataSource
- com.zaxxer.hikari.HikariDataSource
- org.apache.commons.dbcp.BasicDataSource
- org.apache.commons.dbcp2.BasicDataSource
<dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-jdbc</artifactId> <exclusions> <exclusion> <groupId>org.apache.tomcat</groupId> <artifactId>tomcat-jdbc</artifactId> </exclusion> </exclusions> </dependency> <dependency> <groupId>com.zaxxer</groupId> <artifactId>HikariCP</artifactId> </dependency>
3. Connection to a JNDI DataSource
spring.datasource.jndi-name=java:tomcat/datasources/users
JPA & Spring Data with Spring Boot
- Hibernate — One of the most popular JPA implementations.
- Spring Data JPA — Makes it easy to implement JPA-based repositories.
- Spring ORMs — Core ORM support from the Spring Framework.
<dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-data-jpa</artifactId> </dependency>
@Entity public class User implements Serializable{ @Id private Integer userId; private String userName; private String userEmail; private String address // setters & getters}
Spring Data JPA Repositories
package com.dineshonjava.domain; import org.springframework.data.domain.*; import org.springframework.data.repository.*; public interface UserRepository extends CrudRepository { }
@Service public class UserService { @Autowired private UserRepository userRepository; @Transactional(readOnly=true) public List<User> findAll() { return userRepository.findAll(); } }
Summary
Spring Boot Related Topics
- Introduction to Spring Boot
- Essentials and Key Components of Spring Boot
- Spring Boot CLI Installation and Hello World Example
- Spring Boot Initializr Web Interface
- Spring Boot Initializr With IDEs
- Spring Boot Initializr With Spring Boot CLI
- Installing Spring Boot
- Developing your first Spring Boot application
- External Configurations for Spring Boot Applications
- Logging Configuration in Spring Boot
- Spring Boot and Spring MVC
- Working with SQL Databases and Spring Boot
- MySQL Configurations
- Spring Data JPA using Spring Boot Application
- Spring Boot with NoSQL technologies
- Spring Cache Tutorial
- Spring Security Tutorial with Spring Boot
- Spring Boot and MongoDB in REST Application
- Complete Guide for Spring Boot Actuator
- Microservices with Spring Boot
How can we use the sql files using ClassPath instead of hardcoded sql query using springboot + JDBCTemplate??