In the Java world, there are several common solutions for data persistence, including Spring’s JdbcTemplate, MyBatis, and JPA. The simplest of these solutions is Spring’s JdbcTemplate. JdbcTemplate is not as convenient as MyBatis, but it is much stronger than Jdbc. It is not as powerful as MyBatis, which means it is relatively simple to use. In fact, JdbcTemplate is the simplest data persistence solution. This article will tell you how to use this thing.
1. Basic configuration
The basic usage of JdbcTemplate is actually quite simple. When creating a SpringBoot project, the developer should select the Jdbc and database driver dependencies in addition to the basic Web dependencies, as follows:
Add the Druid database connection pool dependency once the project is successfully created (note that you can add druid-spring-boot-starter for Spring Boot instead of Druid for SSM).
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.10</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.27</version>
<scope>runtime</scope>
</dependency>
Copy the codeOnce the project is created, you only need to provide the basic configuration of the data in application.properties, as follows:
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource spring.datasource.username=root spring.datasource.password=123 spring.datasource.url=jdbc:mysql:///test01? useUnicode=true&characterEncoding=UTF-8Copy the codeNow that all the configuration is complete, can we use the JdbcTemplate directly? How so convenient? In fact, this is the automatic configuration of SpringBoot benefits, we will first talk about the usage, principle.
2. Basic usage
First let’s create a User Bean as follows:
public class User {
private Long id;
private String username;
private String address;
/ / omit getter/setter
}
Copy the codeThen create a UserService class and inject JdbcTemplate into the UserService class as follows:
@Service
public class UserService {
@Autowired
JdbcTemplate jdbcTemplate;
}
Copy the codeWell, after that, the preparation is complete.
2.1 to add
JdbcTemplate, in addition to the query has several apis, add, delete and change the same operation using update, you can pass in SQL. For example, you can add data as follows:
public int addUser(User user) {
return jdbcTemplate.update("insert into user (username,address) values (? ,?) ;", user.getUsername(), user.getAddress());
}
Copy the codeThe return value of the update method is the number of rows affected by the SQL execution.
If you have more complex requirements, such as primary key backfill during data insertion, you can use PreparedStatementCreator, as follows:
public int addUser2(User user) {
KeyHolder keyHolder = new GeneratedKeyHolder();
int update = jdbcTemplate.update(new PreparedStatementCreator() {
@Override
public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
PreparedStatement ps = connection.prepareStatement("insert into user (username,address) values (? ,?) ;", Statement.RETURN_GENERATED_KEYS);
ps.setString(1, user.getUsername());
ps.setString(2, user.getAddress());
return ps;
}
}, keyHolder);
user.setId(keyHolder.getKey().longValue());
System.out.println(user);
return update;
}
Copy the codeIn effect, we are using the JDBC solution entirely, first passing Statement.return_generated_keys when building a PreparedStatement, then KeyHolder, Finally, the id of the newly inserted data is retrieved from the KeyHolder and stored in the ID property of the User object.
The JdbcTemplate provided by Spring is not as convenient as MyBatis, but it is much more convenient than JDBC.
2.2 delete
Use the UPDATE API to pass in your SQL:
public int deleteUserById(Long id) {
return jdbcTemplate.update("delete from user where id=?", id);
}
Copy the codeYou can also use PreparedStatementCreator.
2.3 change
public int updateUserById(User user) {
return jdbcTemplate.update("update user set username=? ,address=? where id=?", user.getUsername(), user.getAddress(),user.getId());
}
Copy the codeYou can also use PreparedStatementCreator.
2.4 check
SQL > select * from user where user = ‘user’;
public List<User> getAllUsers(a) {
return jdbcTemplate.query("select * from user".new RowMapper<User>() {
@Override
public User mapRow(ResultSet resultSet, int i) throws SQLException {
String username = resultSet.getString("username");
String address = resultSet.getString("address");
long id = resultSet.getLong("id");
User user = new User();
user.setAddress(address);
user.setUsername(username);
user.setId(id);
returnuser; }}); }Copy the codeRowMapper = RowMapper = RowMapper = RowMapper = RowMapper = RowMapper = RowMapper Well, this may seem like a bit of a hassle, but in fact, if the names of the fields in the database and the properties of the objects are exactly the same, there is another simple solution:
public List<User> getAllUsers2(a) {
return jdbcTemplate.query("select * from user".new BeanPropertyRowMapper<>(User.class));
}
Copy the codeAs for the query, the parameter is also used as a placeholder, which is consistent with the previous article and will not be repeated here.
2.5 other
In addition to these basic uses, JdbcTemplate also supports other uses, such as calling stored procedures, that are relatively easy and similar to Jdbc itself, so I won’t cover them here.
3. Principle analysis
So in SpringBoot, after configuring the basic database information, we have a JdbcTemplate. Where does this thing come from? Source in the org. Springframework. Boot. Autoconfigure. JDBC. JdbcTemplateAutoConfiguration class, the class source code is as follows:
@Configuration
@ConditionalOnClass({ DataSource.class, JdbcTemplate.class })
@ConditionalOnSingleCandidate(DataSource.class)
@AutoConfigureAfter(DataSourceAutoConfiguration.class)
@EnableConfigurationProperties(JdbcProperties.class)
public class JdbcTemplateAutoConfiguration {
@Configuration
static class JdbcTemplateConfiguration {
private final DataSource dataSource;
private final JdbcProperties properties;
JdbcTemplateConfiguration(DataSource dataSource, JdbcProperties properties) {
this.dataSource = dataSource;
this.properties = properties;
}
@Bean
@Primary
@ConditionalOnMissingBean(JdbcOperations.class)
public JdbcTemplate jdbcTemplate(a) {
JdbcTemplate jdbcTemplate = new JdbcTemplate(this.dataSource);
JdbcProperties.Template template = this.properties.getTemplate();
jdbcTemplate.setFetchSize(template.getFetchSize());
jdbcTemplate.setMaxRows(template.getMaxRows());
if(template.getQueryTimeout() ! =null) {
jdbcTemplate
.setQueryTimeout((int) template.getQueryTimeout().getSeconds());
}
returnjdbcTemplate; }}@Configuration
@Import(JdbcTemplateConfiguration.class)
static class NamedParameterJdbcTemplateConfiguration {
@Bean
@Primary
@ConditionalOnSingleCandidate(JdbcTemplate.class)
@ConditionalOnMissingBean(NamedParameterJdbcOperations.class)
public NamedParameterJdbcTemplate namedParameterJdbcTemplate( JdbcTemplate jdbcTemplate) {
return newNamedParameterJdbcTemplate(jdbcTemplate); }}}Copy the codeDataSource = JdbcTemplate = JdbcTemplate = JdbcTemplate = JdbcTemplate = JdbcOperations = JdbcOperations A JdbcTemplate Bean is automatically configured (JdbcTemplate is an implementation of the JdbcOperations interface). Ok, I wonder if you guys have gained anything?
Pay attention to the public account [Jiangnan little Rain], focus on Spring Boot+ micro service and front and back end separation and other full stack technology, regular video tutorial sharing, after attention to reply to Java, get Songko for you carefully prepared Java dry goods!
