spring对于数据访问层提供了多种的模板技术。如果直接使用JDBC,那么可以选择JdbcTemplate、如果使用的是对象关系映射框架,使用hibernate应该使用HibernateTemplate模板,使用JPA则应该使用JpaTemplate。
除此之外,Spring框架为每一项的持久层技术都提供了相应的帮助类来简化操作。对于Jdbc提供了JdbcDaoSupport类、对于Hibernate技术提供了HibernateDaoSupport类、对于MyBatis提供了SqlMapClientDaoSupport类。
本篇主要介绍Spring如何使用JdbcTemplate来访问关系型数据库。
1.首先引入使用Spring的jdbc模块时的jar文件(maven项目可引入对应的依赖)。
- 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.jar
对应的数据库驱动(这里采用mysql)
2.在src下引入两个文件:applicationContext.xml和log4j.xml
3.下面以连接两种数据库连接池的技术来介绍Spring关于JdbcTemplate的使用:
使用Spring内置的数据库连接池:
1
2
3
4
5
6
7
8
9
|
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))" ); |
或者:
1
2
3
4
5
6
7
8
9
10
|
<!-- XML配置Spring默认的连接池 --> < 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代码使用:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
/** * @author BeautifulSoup * 首先使用Spring内置的连接池 */ @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))" ); } } |
使用世界上性能最好的Druid连接池:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
|
<!-- 配置Druid的连接池 --> < 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....." /> <!-- 设置初始的连接数目,最小的连接数,最大的连接数 --> < property name = "initialSize" value = "1" /> < property name = "minIdle" value = "1" /> < property name = "maxActive" value = "8" /> <!-- 配置获取连接等待超时的时间 --> < property name = "maxWait" value = "10000" /> <!-- 配置间隔多久才进行一次检测需要关闭的空闲连接 --> < property name = "timeBetweenEvictionRunsMillis" value = "60000" /> <!-- 配置一个连接在池中最小的生存时间 --> < property name = "minEvictableIdleTimeMillis" value = "300000" /> < property name = "testWhileIdle" value = "true" /> <!-- 这里建议配置为TRUE,防止取到的连接不可用 --> < property name = "testOnBorrow" value = "true" /> < property name = "testOnReturn" value = "false" /> <!-- 打开PSCache,并且指定每个连接上PSCache的大小 --> < property name = "poolPreparedStatements" value = "true" /> < property name = "maxPoolPreparedStatementPerConnectionSize" value = "20" /> <!-- 这里配置提交方式,默认就是TRUE,可以不用配置 --> < property name = "defaultAutoCommit" value = "true" /> <!-- 验证连接有效与否的SQL,不同的数据配置不同 --> < 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 > |
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
/** * @author BeautifulSoup * 首先使用Spring内置的连接池 */ @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.使用得到的JdbcTemplate进行基本的增删改查:
首先创建实体类对象,
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
|
/** * @author BeautifulSoup * 创建实体类对象 */ 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 + "]" ; } } |
在配置文件中配置bean:
1
2
3
|
< bean class = "com.fuyunwang.springjdbc.dao.BookDao" > < property name = "jdbcTemplate" ref = "jdbcTemplate" /> </ bean > |
Dao层进行持久层的开发:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
|
/** * @author BeautifulSoup 完成基本的增删改查 */ 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; } } } |
单元测试,
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
/** * @author BeautifulSoup * 首先使用Spring内置的连接池 */ @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实战" ); book.setAuthor( "Craig Walls" ); bookDao.add(book); } } |
以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持服务器之家。
原文链接:http://blog.csdn.net/James_shu/article/details/54647023?locationNum=3&fps=1