Detailed explanation of examples of using JdbcTemplate

JdbcTemplate provides a more abstract encapsulation based on the JDBC API and provides transaction management capabilities based on method annotations. By using the SpringBoot automatic configuration function and replacing our automatic configuration of beans, let me introduce to you the relevant knowledge about using JdbcTemplate in spring boot. Let's take a look.

This article will introduce how to use spring boot and JdbcTemplate to work together.

Spring's operations on the database are deeply encapsulated in jdbc. Using spring's injection function, you can register DataSource into JdbcTemplate. JdbcTemplate provides a more abstract encapsulation based on the JDBC API and provides transaction management capabilities based on method annotations. By using the SpringBoot automatic configuration function and automatically configuring beans instead of us.

Data source configuration

In maven, we need to add spring-boot -starter-jdbc module

<dependency>
 <groupId>org.springframework.boot</groupId>
 <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>

Through this module, we have done the following things for us

Tomcat-jdbc-{version}.jar automatically configures DataSource for us.

If you do not define any DataSource, SpringBoot will automatically configure an in-memory database resource setting
If no beans are set, SpringBoot will automatically register it
Initialize the database
If we define the schema in the classpath. sql and data.sql files, springBoot will use these files to automatically initialize the database (but you must choose to build the library)
In addition to loading schema.sql and data.sql, SpringBoot will also load schema-${platform} .sql and data-${platform}.sql, if they exist in your classpath.

  spring.datasource.schema=xxxx-db.sql 可以定义你的建库文件
  spring.datasource.data=xxxx-data.sql 可以定义你的数据文件
  spring.datasource.initialize＝true|false 可以决定是不是要初始化这些数据库文件
  spring.datasource.continueOnError＝true|false 有了错误是否继续运行

Embedded database support

Embedded databases are typically used in development and testing environments and are not recommended for production environments. Spring Boot provides automatically configured embedded databases such as H2, HSQL, and Derby. You do not need to provide any connection configuration to use them.

For example, we can introduce the following configuration in pom.xml to use HSQL

<dependency>
 <groupId>org.hsqldb</groupId>
 <artifactId>hsqldb</artifactId>
 <scope>runtime</scope>
</dependency>

to connect to the production data source configuration

Taking MySQL database as an example, first introduce the dependency package of MySQL connection and add in pom.xml:

<dependency>
 <groupId>mysql</groupId>
 <artifactId>mysql-connector-java</artifactId>
 <version>5.1.21</version>
</dependency>

Configure data source information in src/main/resources/application.properties

spring.datasource.url=jdbc:mysql://localhost:3306/test
spring.datasource.username=dbuser
spring.datasource.password=dbpass
spring.datasource.driver-class-name=com.mysql.jdbc.Driver

Connect JNDI data source configuration

When you deploy the application on the application server and want the data source to be managed by the application server, then You can use the following configuration method to introduce the JNDI data source.

spring.datasource.jndi-name=java:jboss/datasources/customers

Custom data source configuration

If you don’t want to use the default configuration data source, for example, you want to use Alibaba’s data pool management Data source, you can also configure it yourself

First exclude the default configuration of tomcat-jdbc dataSource

<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>

Define your own data resources Alibaba's data pool management is used here, you can also use BasicDataSource

<dependency>
 <groupId>com.alibaba</groupId>
 <artifactId>druid</artifactId>
 <version>1.0.19</version>
</dependency>

package com.example;

import javax.sql.DataSource;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.web.servlet.FilterRegistrationBean;
import org.springframework.boot.web.servlet.ServletComponentScan;
import org.springframework.boot.web.servlet.ServletListenerRegistrationBean;
import org.springframework.boot.web.servlet.ServletRegistrationBean;
import org.springframework.context.annotation.Bean;
import org.springframework.core.env.Environment;
import org.springframework.web.servlet.DispatcherServlet;
import com.alibaba.druid.pool.DruidDataSource;
import com.example.Listener.IndexListener;
import com.example.filter.IndexFilter;
import com.example.servlet.MyServlet;
@SpringBootApplication
public class SpringBootSimpleApplication {
 @Autowired
 private Environment env;
 @Bean
 public DataSource dataSource() {
  DruidDataSource dataSource = new DruidDataSource();
  dataSource.setUrl(env.getProperty("spring.datasource.url"));
  dataSource.setUsername(env.getProperty("spring.datasource.username"));//用户名
  dataSource.setPassword(env.getProperty("spring.datasource.password"));//密码
  dataSource.setDriverClassName(env.getProperty("spring.datasource.driver-class-name"));
  dataSource.setInitialSize(2);
  dataSource.setMaxActive(20);
  dataSource.setMinIdle(0);
  dataSource.setMaxWait(60000);
  dataSource.setValidationQuery("SELECT 1");
  dataSource.setTestOnBorrow(false);
  dataSource.setTestWhileIdle(true);
  dataSource.setPoolPreparedStatements(false);
  return dataSource;
 }
 public static void main(String[] args) {
  SpringApplication.run(SpringBootSimpleApplication.class, args);
 }
}

You can also use other:

<dependency>
 <groupId>commons-dbcp</groupId>
 <artifactId>commons-dbcp</artifactId>
 <version>1.4</version>
</dependency>
@Bean
public DataSource dataSource() {
 BasicDataSource dataSource = new BasicDataSource();
 dataSource.setDriverClassName(env.getProperty("spring.datasource.driver-class-name"));
 dataSource.setUrl(env.getProperty("spring.datasource.url"));
 dataSource.setUsername(env.getProperty("spring.datasource.username"));
 dataSource.setPassword(env.getProperty("spring.datasource.password"));
 return dataSource;
}

Code example

Create entity object

/src/main/java/com/example/domain/User.java

package com.example.domain;
public class User
{
 private Integer id;
 private String name;
 private String email;
 public User()
 {
 }
 public User(Integer id, String name, String email)
 {
  this.id = id;
  this.name = name;
  this.email = email;
 }
 public Integer getId()
 {
  return id;
 }
 public void setId(Integer id)
 {
  this.id = id;
 }
 public String getName()
 {
  return name;
 }
 public void setName(String name)
 {
  this.name = name;
 }
 public String getEmail()
 {
  return email;
 }
 public void setEmail(String email)
 {
  this.email = email;
 }
 @Override
 public String toString() {
  return "User{" +
    "id=" + id +
    ", name='" + name + '\'' +
    ", email='" + email + '\'' +
    '}';
 }
}

Create persistence layer

With the above data source configuration, we can inject JdbcTemplate into the data access component and interact with the database.

/src/main/java/com/example/repositories/UserRepository.java

package com.example.repositories;
import com.example.domain.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import java.sql.*;
import java.util.List;
@Repository
public class UserRepository {
 @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 id=?", new Object[]{id}, new UserRowMapper());
 }
 public User create(final User user) {
  final String sql = "insert into users(name,email) 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.setString(1, user.getName());
    ps.setString(2, user.getEmail());
    return ps;
   }
  }, holder);
  int newUserId = holder.getKey().intValue();
  user.setId(newUserId);
  return user;
 }
 public void delete(final Integer id) {
  final String sql = "delete from users where id=?";
  jdbcTemplate.update(sql,
    new Object[]{id},
    new int[]{java.sql.Types.INTEGER});
 }
 public void update(final User user) {
  jdbcTemplate.update(
    "update users set name=?,email=? where id=?",
    new Object[]{user.getName(), user.getEmail(), user.getId()});
 }
}
class UserRowMapper implements RowMapper<User> {
 @Override
 public User mapRow(ResultSet rs, int rowNum) throws SQLException {
  User user = new User();
  user.setId(rs.getInt("id"));
  user.setName(rs.getString("name"));
  user.setEmail(rs.getString("email"));
  return user;
 }
}

Unit Test

You may have noticed that most of the time, we do these configuration things in the application.

Create unit tests to test our persistence layer methods

/src/test/java/SpringBootJdbcDemoApplicationTests.java

import com.example.SpringBootJdbcDemoApplication;
import com.example.domain.User;
import com.example.repositories.UserRepository;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.SpringApplicationConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import java.util.List;
import static org.junit.Assert.*;
import static org.junit.Assert.assertEquals;
@RunWith(SpringJUnit4ClassRunner.class)
@SpringApplicationConfiguration(SpringBootJdbcDemoApplication.class)
public class SpringBootJdbcDemoApplicationTests
{
  Logger logger= LoggerFactory.getLogger(SpringBootJdbcDemoApplicationTests.class);
 @Autowired
 private UserRepository userRepository;
 @Test public void testAll(){
  findAllUsers();
  findUserById();
  createUser();
 }
 @Test
 public void findAllUsers() {
  List<User> users = userRepository.findAll();
  assertNotNull(users);
  assertTrue(!users.isEmpty());
 }
 @Test
 public void findUserById() {
  User user = userRepository.findUserById(1);
  assertNotNull(user);
 }
 private void updateById(Integer id) {
  User newUser = new User(id, "JackChen", "JackChen@qq.com");
  userRepository.update(newUser);
  User newUser2 = userRepository.findUserById(newUser.getId());
  assertEquals(newUser.getName(), newUser2.getName());
  assertEquals(newUser.getEmail(), newUser2.getEmail());
 }
 @Test
 public void createUser() {
  User user = new User(0, "tom", "tom@gmail.com");
  User savedUser = userRepository.create(user);
  logger.debug("{}",savedUser);
  User newUser = userRepository.findUserById(savedUser.getId());
  assertEquals("tom", newUser.getName());
  assertEquals("tom@gmail.com", newUser.getEmail());
  updateById(newUser.getId());
  userRepository.delete(newUser.getId());
 }
}

[Related recommendations]

1. Java free video tutorial

2. Comprehensive analysis of Java annotations

3. Alibaba Java Development Manual

The above is the detailed content of Detailed explanation of examples of using JdbcTemplate. For more information, please follow other related articles on the PHP Chinese website!