Spring的JdbcTemplate
JdbcTemplate模板与DbUtils工具类比较类似.
1 Spring对持久层技术支持:
JDBC:org.springframework.jdbc.core.JdbcTemplate
Hibernate3.0:org.springframework.orm.hibernate3.HibernateTemplate
IBatis(MyBatis):org.springframework.orm.ibatis.SqlMapClientTemplate
JPA:org.springframe work.orm.jpa.JpaTemplate
2 开发JDBCTemplate入门:
第一步:引入相应jar包:
spring-tx-3.2.0.RELEASE.jar
spring-jdbc-3.2.0.RELEASE.jar
mysql驱动.
第二步:创建applicationContext.xml
JdbcTemplate
第三步:编写一个测试类:
package com.sihai.spring3.demo1;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
@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="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql:///spring3_day02"/>
<property name="username" value="root"/>
<property name="password" value="123"/>
</bean>
DBCP连接池:
导入jar包:
com.springsource.org.apache.commons.dbcp-1.2.2.osgi.jar
com.springsource.org.apache.commons.pool-1.5.3.jar
C3P0连接池:
导入jar包:
com.springsource.com.mchange.v2.c3p0-0.9.1.2.jar
2.2 参数设置到属性文件中:
在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="classpath:jdbc.properties"></property>
</bean>
第二种写法:
<context:property-placeholder location="classpath:jdbc.properties"/>
2.3 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
2.3.1 编写DAO的时候:
Public class UserDao extends JdbcDaoSupport{
}
进行CRUD的操作;
保存:update(String sql,Object… args)
修改:update(String sql,Object… args)
删除:update(String sql,Object… args)
2.3.2 查询:
简单查询:
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 rowMapper,Object… args);
select * from user;— query(String sql,RowMapper rowMapper,Object… args);
package com.sihai.spring3.demo2;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;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; } }
}