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; } }
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]
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!