首页 > 代码库 > Spring的JdbcTemplate
Spring的JdbcTemplate
#1 开发JDBCTemplate入门:
## 第一步:引入相应jar包:
* spring-tx-3.2.0.RELEASE.jar
* spring-jdbc-3.2.0.RELEASE.jar
* mysql驱动.
## 第二步:创建applicationContext.xml
## 第三步:编写一个测试类:
```
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("classpath:applicationContext.xml")
public class SpringTest1 {
@Autowired
@Qualifier("jdbcTemplate")
private JdbcTemplate jdbcTemplate;
@Test
public void demo2(){
jdbcTemplate.execute("create table user (id int primary key auto_increment,name varchar(20))");
}
@Test
public void demo1(){
// 创建连接池:
DriverManagerDataSource dataSource = new DriverManagerDataSource();
// 设置参数:
dataSource.setDriverClassName("com.mysql.jdbc.Driver");
dataSource.setUrl("jdbc:mysql:///spring3_day02");
dataSource.setUsername("root");
dataSource.setPassword("123");
// 使用JDBC的模板:
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
jdbcTemplate.execute("create table user (id int primary key auto_increment,name varchar(20))");
}
}
```
#2 配置连接池:
##(1) Spring默认的连接池:
<!-- 配置Spring默认的连接池 -->
<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="http://www.mamicode.com/com.mysql.jdbc.Driver"/>
<property name="url" value="http://www.mamicode.com/jdbc:mysql:///spring3_day02"/>
<property name="username" value="http://www.mamicode.com/root"/>
<property name="password" value="http://www.mamicode.com/123"/>
</bean>
```
##(2) DBCP连接池:
* 导入jar包:
* com.springsource.org.apache.commons.dbcp-1.2.2.osgi.jar
* com.springsource.org.apache.commons.pool-1.5.3.jar
<!-- 配置DBCP连接池 -->
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
<property name="driverClassName" value="http://www.mamicode.com/com.mysql.jdbc.Driver"/>
<property name="url" value="http://www.mamicode.com/jdbc:mysql:///spring3_day02"/>
<property name="username" value="http://www.mamicode.com/root"/>
<property name="password" value="http://www.mamicode.com/123"/>
</bean>
```
##(3) C3P0连接池:
* 导入jar包:
* com.springsource.com.mchange.v2.c3p0-0.9.1.2.jar
```
<!-- 配置c3p0连接池 -->
<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
<property name="driverClass" value="http://www.mamicode.com/com.mysql.jdbc.Driver"/>
<property name="jdbcUrl" value="http://www.mamicode.com/jdbc:mysql:///spring3_day02"/>
<property name="user" value="http://www.mamicode.com/root"/>
<property name="password" value="http://www.mamicode.com/123"/>
</bean>
```
##(4) 参数设置到属性文件中:
* 在src下创建jdbc.properties
```
jdbc.driver = com.mysql.jdbc.Driver
jdbc.url = jdbc:mysql:///spring3_day02
jdbc.user = root
jdbc.password = 123
```
* 需要在applicationContext.xml 中使用属性文件配置的内容.
* 第一种写法:
```
<bean class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
<property name="location" value="http://www.mamicode.com/classpath:jdbc.properties"></property>
</bean>
```
* 第二种写法:(常用方式，来加载jdbc的配置文件)
```
<context:property-placeholder location="classpath:jdbc.properties"/>
```
```
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/test?characterEncoding=UTF-8
jdbc.username=root
jdbc.password=123
```
* 定义配置文件properties
- 利用标签加载jdbc.properties
- 利用spring提供的ComboPooledDataSource类来产生dataSource这个类里面主要是传入jdbc连接数据库的四个参数
- 利用spring提供的JdbcTemplate类，这个类里面提供了很多直接操纵数据库的很多方法
```
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:context="http://www.springframework.org/schema/context"
xsi:schemaLocation="
http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd">
<context:property-placeholder location="classpath:jdbc.properties"/>
<!--配置c3p0-->
<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
<property name="driverClass" value="${jdbc.driver}"/>
<property name="jdbcUrl" value="${jdbc.url}"/>
<property name="user" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
</bean>
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"/>
</bean>
</beans>
```
* 测试，直接注入jdbcTemplate来直接操纵数据库
```
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("classpath:applicationContext.xml")
public class SpringTest1 {
@Autowired
@Qualifier("jdbcTemplate")
private JdbcTemplate jdbcTemplate;
@Test
public void demo() {
jdbcTemplate.execute("create table user (id int primary key auto_increment,name varchar(20))");
}
}
```
#4 JdbcTemplate的CRUD的操作:
* Spring框架中提供了对持久层技术支持的类:
- JDBC : org.springframework.jdbc.core.support.JdbcDaoSupport
- Hibernate 3.0 : org.springframework.orm.hibernate3.support.HibernateDaoSupport
- iBatis : org.springframework.orm.ibatis.support.SqlMapClientDaoSupport
public class User {
private Integer id;
private String name;
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;
}
@Override
public String toString() {
return "User [id=" + id + ", name=" + name + "]";
}
}
<!-- 配置c3p0连接池 -->
<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
<property name="driverClass" value="http://www.mamicode.com/${jdbc.driver}"/>
<property name="jdbcUrl" value="http://www.mamicode.com/${jdbc.url}"/>
<property name="user" value="http://www.mamicode.com/${jdbc.user}"/>
<property name="password" value="http://www.mamicode.com/${jdbc.password}"/>
</bean>
<!-- 定义jdbctemplate -->
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"/>
</bean>
<bean id="userDao" class="cn.itcast.spring3.demo2.UserDao">
<property name="jdbcTemplate" ref="jdbcTemplate"/>
</bean>
* ## 编写DAO的时候:
Public class UserDao extends JdbcDaoSupport
- 原因是因为我们操纵数据库的时候都是通过JdbcTemplemate来直接操纵的，所以如果你不继承他，那么你需要在userDao中还要定义JdbcTemplemate，然后在配置文件中再把这个注入进userDao中，但是直接继承他之后，它里面是有JdbcTemplemate
```
public class UserDao extends JdbcDaoSupport{
public void add(User user){
String sql = "insert into user values (null,?)";
this.getJdbcTemplate().update(sql, user.getName());
}
public void update(User user){
String sql = "update user set name = ? where id = ?";
this.getJdbcTemplate().update(sql, user.getName(),user.getId());
}
public void delete(User user){
String sql = "delete from user where id = ?";
this.getJdbcTemplate().update(sql, user.getId());
}
public int findCount(){
String sql = "select count(*) from user";
return this.getJdbcTemplate().queryForInt(sql);
}
public String findNameById(int id){
String sql = "select name from user where id = ?";
return this.getJdbcTemplate().queryForObject(sql, String.class, id);
}
public User findById(int id){
String sql = "select * from user where id = ?";
User user = this.getJdbcTemplate().queryForObject(sql, new UserRowMapper(), id);
return user;
}
public List<User> findAll(){
String sql = "select * from user";
return this.getJdbcTemplate().query(sql, new UserRowMapper());
}
class UserRowMapper implements RowMapper<User>{
/**
* rs:结果集.
* rowNum:行号
*/
public User mapRow(ResultSet rs, int rowNum) throws SQLException {
User user = new User();
user.setId(rs.getInt("id"));
user.setName(rs.getString("name"));
return user;
}
}
}
```
* 进行CRUD的操作;
- 保存:update(String sql,Object... args)
- 修改:update(String sql,Object... args)
- 删除:update(String sql,Object... args)
* 查询:
- ### 简单查询:
* select count(*) from user; --- queryForInt(String sql);
* select name from user where id = ?; --- queryForObject(String sql,Class clazz,Object... args);
- ### 复杂查询:(返回对象,和对象集合)
* select * from user where id = ? --- queryForObjectString sql,RowMapper<T> rowMapper,Object... args);
* select * from user; --- query(String sql,RowMapper<T> rowMapper,Object... args);
Spring的JdbcTemplate