- JdbcTemplate模板与DbUtils工具类比较类似.
Spring对持久层技术支持
- JDBC : org.springframework.jdbc.core.JdbcTemplate
- Hibernate3.0 : org.springframework.orm.hibernate3.HibernateTemplate
- IBatis(MyBatis) : org.springframework.orm.ibatis.SqlMapClientTemplate
- JPA : org.springframework.orm.jpa.JpaTemplate
开发JDBCTemplate入门:
-
第一步:引入相应jar包:
- spring-tx-3.2.0.RELEASE.jar
- spring-jdbc-3.2.0.RELEASE.jar
- mysql驱动.
- 传统JDBC连接demo
package cn.spring3.demo1;
import org.junit.Test;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
public class SpringTest1 {
@Test
public void demo1(){
//创建连接池
DriverManagerDataSource dateSource = new DriverManagerDataSource();
//设置参数
dateSource.setDriverClassName("com.mysql.jdbc.Driver");
//dateSource.setUrl("jdbc:mysql://172.16.30.189:3306/spring3_day02");
dateSource.setUrl("jdbc:mysql://192.168.0.120:3306/spring3_day02");
dateSource.setUsername("root");
dateSource.setPassword("123");
//使用jdbc的模板
JdbcTemplate jdbcTemplate = new JdbcTemplate(dateSource);
//这个是第二种方式:jdbcTemplate.setDataSource(dateSource);
String sql="create table user (id int primary key auto_increment,name varchar(20))";
jdbcTemplate.execute(sql);
}
}
- 第一种方式:创建applicationContext.xml(配置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://172.16.30.189:3306/spring3_day02"/>
<property name="username" value="root"/>
<property name="password" value="123"/>
</bean>
<!-- 定义jdbctmplate -->
<bean id="jdbcTmplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"></property>
</bean>
- 编写一个测试类:
package cn.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.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4Cla***unner;
@RunWith(SpringJUnit4Cla***unner.class)
@ContextConfiguration(locations="classpath:applicationContext.xml")
public class SpringTest2 {
//配置Spring默认连接池
@Autowired
@Qualifier("jdbcTmplate")
private JdbcTemplate jdbcTmplate;
@Test
public void demo1(){
jdbcTmplate.execute("create table user (id int primary key auto_increment,name varchar(20))");
}
}
- 第二种方式:创建applicationContext.xml(配置DBCP连接池)
<!-- 配置DBCP连接池 -->
<bean id="dateSourceDbcp" class="org.apache.commons.dbcp.BasicDataSource">
<property name="driverClassName" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://172.16.30.189:3306/spring3_day02"/>
<property name="username" value="root"/>
<property name="password" value="123"/>
</bean>
<!-- 定义jdbctmplate DBCP -->
<bean id="jdbcTmplateDbcp" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dateSourceDbcp"></property>
</bean>
- 编写测试类:
package cn.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.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4Cla***unner;
@RunWith(SpringJUnit4Cla***unner.class)
@ContextConfiguration(locations="classpath:applicationContext.xml")
public class SpringTest2 {
//配置DBCP连接池
@Autowired
@Qualifier("jdbcTmplateDbcp")
private JdbcTemplate jdbcTmplateDbcp;
@Test
public void demo2(){
jdbcTmplateDbcp.execute("create table user (id int primary key auto_increment,name varchar(20))");
}
}
- 第三种方式:创建applicationContext.xml(配置C3p0连接池)
<!-- 配置C3P0连接池 -->
<bean id="dateSourceC3p0" class="com.mchange.v2.c3p0.ComboPooledDataSource">
<property name="driverClass" value="com.mysql.jdbc.Driver"/>
<property name="jdbcUrl" value="jdbc:mysql://172.16.30.189:3306/spring3_day02"/>
<property name="user" value="root"/>
<property name="password" value="123"/>
</bean>
<!-- 定义jdbctmplate DBCP -->
<bean id="jdbcTmplateC3p0" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dateSourceC3p0"></property>
</bean>
- 编写测试类:
package cn.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.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4Cla***unner;
@RunWith(SpringJUnit4Cla***unner.class)
@ContextConfiguration(locations="classpath:applicationContext.xml")
public class SpringTest2 {
//配置C3p0连接池
@Autowired
@Qualifier("jdbcTmplateC3p0")
private JdbcTemplate jdbcTmplateC3p0;
@Test
public void demo3(){
jdbcTmplateC3p0.execute("create table user (id int primary key auto_increment,name varchar(20))");
}
}
引入配置文件方式,配置连接池(C3P0举例说明)
1.第一步:新建JDBC.properties(名字随便)
jdbc.driver = com.mysql.jdbc.Driver
jdbc.url = jdbc:mysql://172.16.30.189:3306/spring3_day02
jdbc.user = root
jdbc.password =123
2.第二步:配置applicationContext.xml(两种方式引入)
2.1 第一种配置方式:
<!-- 配置C3P0连接池 引入配置文件的方式 方法一-->
<bean class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
<property name="location" value="classpath:JDBC.properties"></property>
</bean>
<bean id="dateSourceC3p0ref" class="com.mchange.v2.c3p0.ComboPooledDataSource">
<property name="driverClass" value="${jdbc.driver}"/>
<property name="jdbcUrl" value="${jdbc.url}"/>
<property name="user" value="${jdbc.user}"/>
<property name="password" value="${jdbc.password}"/>
</bean>
<!-- 定义jdbctmplate DBCP -->
<bean id="jdbcTmplateC3p0ref" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dateSourceC3p0ref"></property>
</bean>
编写测试类:
package cn.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.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4Cla***unner;
@RunWith(SpringJUnit4Cla***unner.class)
@ContextConfiguration(locations="classpath:applicationContext.xml")
public class SpringTest2 {//配置C3p0连接池 引入配置文件方式 方法一 第二种方法请见springtest3.java
@Autowired
@Qualifier("jdbcTmplateC3p0ref")
private JdbcTemplate jdbcTmplateC3p0ref;
@Test
public void demo4(){
jdbcTmplateC3p0ref.execute("create table user (id int primary key auto_increment,name varchar(20))");
}
}
2.2 第二种配置方式:
xmlns:context="http://www.springframework.org/schema/context"
<!-- 配置C3P0连接池 引入配置文件的方式 方法二-->
<context:property-placeholder location="classpath:JDBC.properties"/>
<bean id="dateSourceC3p0ref" class="com.mchange.v2.c3p0.ComboPooledDataSource">
<property name="driverClass" value="${jdbc.driver}"/>
<property name="jdbcUrl" value="${jdbc.url}"/>
<property name="user" value="${jdbc.user}"/>
<property name="password" value="${jdbc.password}"/>
</bean>
<!-- 定义jdbctmplate DBCP -->
<bean id="jdbcTmplateC3p0ref" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dateSourceC3p0ref"></property>
</bean>
编写测试类:
package cn.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.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4Cla***unner;
@RunWith(SpringJUnit4Cla***unner.class)
@ContextConfiguration(locations="classpath:applicationContext1.xml")
public class SpringTest3 {
//配置C3p0连接池 引入配置文件方式 方法二 方法一见springtest2.java
@Autowired
@Qualifier("jdbcTmplateC3p0ref")
private JdbcTemplate jdbcTmplateC3p0ref;
@Test
public void demo1(){
jdbcTmplateC3p0ref.execute("create table user (id int primary key auto_increment,name varchar(20))");
}
}
* 总结以上:
在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"/>
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
编写DAO的时候:
Public class UserDao extends JdbcDaoSupport{
}
进行CRUD的操作;
* 保存:update(String sql,Object... args)
* 修改:update(String sql,Object... args)
* 删除:update(String sql,Object... args)
- 例子:
User类
package cn.spring3.demo2;
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;
}
}
UserDao类
package cn.spring3.demo2;
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());
}
}
测试类:
package cn.spring3.demo2;
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.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4Cla***unner;
@RunWith(SpringJUnit4Cla***unner.class)
@ContextConfiguration(locations="classpath:applicationContext1.xml")
public class StringTest1 {
@Autowired
@Qualifier("userDao")
private UserDao userDao;
//增
@Test
public void demo1(){
User user = new User();
user.setName("one");
userDao.add(user);
}
//修改
@Test
public void demo2(){
User user = new User();
user.setId(2);
user.setName("two");
userDao.update(user);
}
//删除
@Test
public void demo3(){
User user = new User();
user.setId(3);
userDao.delete(user);
}
}
查询:
-
简单查询(单一类型查询):
- 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);
UserDao类添加方法
public int findCount(){
String sql = "select count(*) from user";
return this.getJdbcTemplate().queryForInt(sql);
}
public String findNameById(User user){
String sql = "select name from user where id= ?";
return this.getJdbcTemplate().queryForObject(sql, String.class, user.getId());
}
测试类编写:
//简单查询1
@Test
public void demo4(){
System.out.println(userDao.findCount());
}
//简单查询2
@Test
public void demo5(){
User user = new User();
user.setId(2);
System.out.println(userDao.findNameById(user));
}
复杂查询
UserDao类添加方法
class UserRowMapper implements RowMapper<User>{
/*
* rc:结果集
* 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;
}
}
public User findById(int id){
String sql = "select * from user where id= ?";
User user = this.getJdbcTemplate().queryForObject(sql,new UserRowMapper(), id);
return user;
}
或者用内部类的方式:
public User findById(int id){
String sql = "select * from user where id = ?";
return this.getJdbcTemplate().queryForObject(sql, new RowMapper<User>(){
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;
}
}, id);
}
public List<User> find(){
String sql = "select * from user ";
return this.getJdbcTemplate().query(sql, new RowMapper<User>(){
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;
}
});
}
编写测试类:
//复杂查询
@Test
public void demo6(){
User user = userDao.findById(2);
System.out.println(user);
}
多条查询
UserDao
public List<User> findAll(){
String sql = "select * from user";
return this.getJdbcTemplate().query(sql,new UserRowMapper());
}
编写测试类:
// 复杂查询2
@Test
public void demo7() {
List<User> list = userDao.findAll();
for (User user : list) {
System.out.println(user);
}
}
转载于:https://blog.51cto.com/4534309/2112489