A brief analysis of Spring's JdbcTemplate method
- 2020-06-01 09:57:27
- OfStack
spring provides a variety of templating techniques for the data access layer. If you use JDBC directly, you can choose JdbcTemplate, if you use an object-relational mapping framework, you should use HibernateTemplate templates if you use hibernate, and JpaTemplate if you use JPA.
In addition, the Spring framework provides a helper class for each of the persistence layer techniques to simplify operations. The JdbcDaoSupport class is provided for Jdbc, the HibernateDaoSupport class is provided for Hibernate technology, and the SqlMapClientDaoSupport class is provided for MyBatis.
This article focuses on how Spring USES JdbcTemplate to access relational databases.
1. First introduce the jar file when using Spring's jdbc module (the maven project can introduce the corresponding dependencies).
spring-beans-3.2.0.RELEASE.jar spring-context-3.2.0.RELEASE.jar spring-core-3.2.0.RELEASE.jar spring-expression-3.2.0.RELEASE.jar commons-logging-1.2.jar spring-jdbc-3.2.0.RELEASE.jar spring-tx-3.2.0.RELEASE.jarThe corresponding database driver (mysql)
2. Two files are introduced under src: applicationContext.xml and log4j.xml
3. Spring's use of JdbcTemplate is described below with the technique of connecting the two database connection pools:
Use Spring's built-in database connection pool:
DriverManagerDataSource dataSource=new DriverManagerDataSource();
dataSource.setDriverClassName("com.mysql.jdbc.Driver");
dataSource.setUrl("jdbc:mysql:///springjdbc");
dataSource.setUsername("root");
dataSource.setPassword("1997WFY.....");
JdbcTemplate template=new JdbcTemplate();
template.setDataSource(dataSource);
template.execute("create table book(id int primary key auto_increment,name varchar(20) not null,author varchar(25))");
Or:
<!-- XML configuration Spring Default connection pooling -->
<bean id="driverManagerDataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql:///springjdbc"/>
<property name="username" value="root"/>
<property name="password" value="1997WFY....."/>
</bean>
<bean class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="driverManagerDataSource"/>
</bean>
Java code USES:
/**
* @author BeautifulSoup
* The first to use Spring Built-in connection pooling
*/
@ContextConfiguration("classpath:applicationContext.xml")
@RunWith(SpringJUnit4ClassRunner.class)
public class SpringJdbcTest {
@Autowired
private JdbcTemplate template;
@Test
public void testDriverManagerDataSource() {
template.execute("create table book(id int primary key auto_increment,name varchar(20) not null,author varchar(25))");
}
}
Use the world's best performance Druid connection pool:
<!-- configuration Druid The connection pool -->
<bean id="druidDataSource" class="com.alibaba.druid.pool.DruidDataSource">
<property name="driverClassName" value="com.mysql.jdbc.Driver" />
<property name="url" value="jdbc:mysql:///springjdbc" />
<property name="username" value="root" />
<property name="password" value="1997WFY....." />
<!-- Sets the initial number of connections , Minimum number of connections, maximum number of connections -->
<property name="initialSize" value="1" />
<property name="minIdle" value="1" />
<property name="maxActive" value="8" />
<!-- Configure to get the time for the connection wait timeout -->
<property name="maxWait" value="10000" />
<!-- How long is the configuration interval 1 Secondary detection of idle connections that need to be closed -->
<property name="timeBetweenEvictionRunsMillis" value="60000" />
<!-- configuration 1 The minimum lifetime of a connection in the pool -->
<property name="minEvictableIdleTimeMillis" value="300000" />
<property name="testWhileIdle" value="true" />
<!-- It is recommended to configure as TRUE , to prevent the fetched connection from becoming unavailable -->
<property name="testOnBorrow" value="true" />
<property name="testOnReturn" value="false" />
<!-- Open the PSCache , and specify each connection PSCache The size of the -->
<property name="poolPreparedStatements" value="true" />
<property name="maxPoolPreparedStatementPerConnectionSize"
value="20" />
<!-- This is where you configure the submission, and the default is TRUE , you don't have to configure it -->
<property name="defaultAutoCommit" value="true" />
<!-- Verify that the connection is valid or not SQL , different data configuration is different -->
<property name="validationQuery" value="select 1 " />
<property name="filters" value="stat" />
</bean>
<bean class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="druidDataSource" />
</bean>
/**
* @author BeautifulSoup
* The first to use Spring Built-in connection pooling
*/
@ContextConfiguration("classpath:applicationContext.xml")
@RunWith(SpringJUnit4ClassRunner.class)
public class SpringJdbcTest {
@Autowired
private JdbcTemplate template;
@Test
public void testSpringJdbc() {
template.execute("create table book(id int primary key auto_increment,name varchar(20) not null,author varchar(25))");
}
}
4. Use the obtained JdbcTemplate for basic addition, deletion and modification:
First create the entity class object,
/**
* @author BeautifulSoup
* Create entity class objects
*/
public class Book {
private Integer id;
private String name;
private String author;
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 getAuthor() {
return author;
}
public void setAuthor(String author) {
this.author = author;
}
@Override
public String toString() {
return "Book [id=" + id + ", name=" + name + ", author=" + author + "]";
}
}
Configure bean in the configuration file:
<bean class="com.fuyunwang.springjdbc.dao.BookDao">
<property name="jdbcTemplate" ref="jdbcTemplate"/>
</bean>
The Dao layer develops the persistence layer:
/**
* @author BeautifulSoup Complete the basic addition, deletion, revision and inspection
*/
public class BookDao extends JdbcDaoSupport {
public void add(Book book) {
String sql = "insert into book values(?,?,?)";
getJdbcTemplate().update(sql, book.getId(), book.getName(),
book.getAuthor());
}
public void update(Book book) {
String sql = "update book set name = ? , author = ? where id =?";
getJdbcTemplate().update(sql, book.getName(), book.getAuthor(),
book.getId());
}
public void delete(Book book) {
String sql = "delete from book where id =?";
getJdbcTemplate().update(sql, book.getId());
}
public int findCount() {
String sql = "select count(*) from book";
return getJdbcTemplate().queryForInt(sql);
}
public String findNameById(int id) {
String sql = "select name from book where id = ?";
return getJdbcTemplate().queryForObject(sql, String.class, id);
}
public Book findById(int id) {
String sql = "select * from book where id = ?";
return getJdbcTemplate().queryForObject(sql, new BookMapper(), id);
}
public List<Book> findAll(){
String sql="select * from book";
return getJdbcTemplate().query(sql, new BookMapper());
}
class BookMapper implements RowMapper<Book> {
public Book mapRow(ResultSet rs, int rowNum) throws SQLException {
Book book = new Book();
book.setId(rs.getInt("id"));
book.setName(rs.getString("name"));
book.setAuthor(rs.getString("author"));
return book;
}
}
}
Unit tests,
/**
* @author BeautifulSoup
* The first to use Spring Built-in connection pooling
*/
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("classpath:applicationContext.xml")
public class SpringJdbcTest {
@Autowired
private BookDao bookDao;
@Test
public void jdbcTemplateAdd(){
Book book=new Book();
book.setId(1);
book.setName("SpringBoot In actual combat ");
book.setAuthor("Craig Walls");
bookDao.add(book);
}
}