- 项目介绍
Spring中关于JDBC的一个辅助类(JDBC Template),它封装了JDBC的操作,使用它来构建项目
- 项目结构
创建maven项目,结构如上图
- maven依赖jar包汇总
<dependencies>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>3.8.1</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>javax.servlet-api</artifactId>
<version>3.1.0</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>4.2.4.RELEASE</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.35</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>4.2.4.RELEASE</version>
</dependency>
</dependencies>
- 新建测试类,创建数据表
package com.curd.spring.test;
import org.junit.Test;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
public class JDBCTemplate {
@Test
public void demo(){
DriverManagerDataSource dataSource=new DriverManagerDataSource();
dataSource.setDriverClassName("com.mysql.jdbc.Driver");
dataSource.setUrl("jdbc:mysql:///test");
dataSource.setUsername("root");
dataSource.setPassword("");
JdbcTemplate jdbcTemplate=new JdbcTemplate(dataSource);
jdbcTemplate.execute("CREATE TABLE temp(`id` int(11) NOT NULL,`name` varchar(32) DEFAULT NULL,`password` varchar(32) DEFAULT NULL,PRIMARY KEY (`id`))");
}
}
创建完的表结构如下
- appliactionContext.xml配置文件
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:p="http://www.springframework.org/schema/p"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd">
<!--数据源的配置 -->
<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
<property name="url" value="jdbc:mysql:///test"></property>
<property name="username" value="root"></property>
<property name="password" value=""></property>
</bean>
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"></property>
</bean>
<bean id="userDao" class="com.curd.spring.dao.impl.UserDAOImpl">
<property name="jdbcTemplate" ref="jdbcTemplate"></property>
</bean>
</beans>
注意修改数据库配置为你本地的配置
- 模型类User.java
package com.curd.spring.model;
public class User {
private int id;
private String username;
private String password;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
@Override
public String toString() {
return "User [id=" + id + ", username=" + username + ", password="
+ password + "]";
}
}
- UserDAO 接口IUserDAO.java
package com.curd.spring.dao;
import java.util.List;
import com.curd.spring.model.User;
public interface IUserDAO {
public void addUser(User user);
public void deleteUser(int id);
public void updateUser(User user);
public String searchUserName(int id);
public User searchUser(int id);
public List<User> findAll();
}
- 接口实现类 UserDAOImpl.java
按照以往Spring的依赖注入,我们需要在接口实现类中利用构造器去获取JdbcTemplate ,Spring早就帮我们想到了这点, 它为我们提供了JdbcDaoSupport支持类,所有
DAO继承这个类,就会自动获得JdbcTemplate(前提是注入DataSource)
在我们的实现类中直接利用getJdbcTemplate就可以获取操作对象了。
JdbcTemplate主要提供下列方法:
1、execute方法:可以用于执行任何SQL语句,一般用于执行DDL语句;
2、update方法及batchUpdate方法:update方法用于执行新增、修改、删除等语句;batchUpdate方法用于执行批处理相关语句;
3、query方法及queryForXXX方法:用于执行查询相关语句;
4、call方法:用于执行存储过程、函数相关语句。
JdbcTemplate主要提供下列方法:
1、execute方法:可以用于执行任何SQL语句,一般用于执行DDL语句;
2、update方法及batchUpdate方法:update方法用于执行新增、修改、删除等语句;batchUpdate方法用于执行批处理相关语句;
3、query方法及queryForXXX方法:用于执行查询相关语句;
4、call方法:用于执行存储过程、函数相关语句。
package com.curd.spring.dao.impl;
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;
import com.curd.spring.dao.IUserDAO;
import com.curd.spring.model.User;
public class UserDAOImpl extends JdbcDaoSupport implements IUserDAO {
@Override
public void addUser(User user) {
// TODO Auto-generated method stub
String sql = "insert into temp values(?,?,?)";
this.getJdbcTemplate().update(sql, null,user.getUsername(),user.getPassword());
}
@Override
public void deleteUser(int id) {
// TODO Auto-generated method stub
String sql = "delete from temp where id = ?";
this.getJdbcTemplate().update(sql, id);
}
@Override
public void updateUser(User user) {
// TODO Auto-generated method stub
String sql = "update temp set username =?,password=? where id = ?";
this.getJdbcTemplate().update(sql, user.getUsername(),user.getPassword(),user.getId());
}
@Override
public String searchUserName(int id) {
// TODO Auto-generated method stub
String sql = "select username from temp where id = ?";
return this.getJdbcTemplate().queryForObject(sql, String.class, id);
}
@Override
public User searchUser(int id) {
// TODO Auto-generated method stub
String sql="select * from temp where id=?";
return this.getJdbcTemplate().queryForObject(sql, new UserRowMapper(), id);
}
@Override
public List<User> findAll() {
// TODO Auto-generated method stub
String sql = "select * from temp";
return this.getJdbcTemplate().query(sql, new UserRowMapper());
}
class UserRowMapper implements RowMapper<User> {
//rs为返回结果集,以每行为单位封装着
@Override
public User mapRow(ResultSet rs, int rowNum) throws SQLException {
// TODO Auto-generated method stub
User user = new User();
user.setId(rs.getInt("id"));
user.setUsername(rs.getString("username"));
user.setPassword(rs.getString("password"));
return user;
}
}
}
- 测试类UserTest.java
package com.curd.spring.test;
import java.util.List;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import com.curd.spring.dao.IUserDAO;
import com.curd.spring.model.User;
public class UserTest {
@Test
public void testaddUser(){
User user = new User();
user.setUsername("liu");
user.setPassword("6");
ApplicationContext ctx = new ClassPathXmlApplicationContext("applicationContext.xml");
IUserDAO dao = (IUserDAO) ctx.getBean("userDao");
dao.addUser(user);
}
@Test
public void testdeleteUser(){
ApplicationContext ctx = new ClassPathXmlApplicationContext("applicationContext.xml");
IUserDAO dao = (IUserDAO) ctx.getBean("userDao");
dao.deleteUser(1);
};
@Test
public void testupdateUser(){
User user = new User();
user.setId(1);
user.setUsername("zhi");
user.setPassword("1234");
ApplicationContext ctx = new ClassPathXmlApplicationContext("applicationContext.xml");
IUserDAO dao = (IUserDAO) ctx.getBean("userDao");
dao.updateUser(user);
};
@Test
public void testsearchUserName(){
ApplicationContext ctx = new ClassPathXmlApplicationContext("applicationContext.xml");
IUserDAO dao = (IUserDAO) ctx.getBean("userDao");
String username = dao.searchUserName(2);
System.out.println(username);
};
@Test
public void testsearchUser(){
ApplicationContext ctx = new ClassPathXmlApplicationContext("applicationContext.xml");
IUserDAO dao = (IUserDAO) ctx.getBean("userDao");
User u = dao.searchUser(2);
System.out.println(u.toString());
};
@Test
public void testfindAll(){
ApplicationContext ctx = new ClassPathXmlApplicationContext("applicationContext.xml");
IUserDAO dao = (IUserDAO) ctx.getBean("userDao");
List<User> u = dao.findAll();
for (User user : u) {
System.out.println(user.toString());
}
};
}
赶紧测试一下吧!
- 知识点汇总
1、Spring 为每种持久化技术 提供一个支持类,在DAO 中注入 模板工具类
(1)JDBC : org.springframework.jdbc.core.support.JdbcDaoSupport
(2)Hibernate 3.0 :org.springframework.orm.hibernate3.support.HibernateDaoSupport
(3)iBatis :org.springframework.orm.ibatis.support.SqlMapClientDaoSupport
用户自己编写DAO 只需要继承 JdbcDaoSupport, 就可以注入 JdbcTemplate
2、 通过jdbcTemplate 提供 int update(String sql, Object... args) 实现增加 、修改 、删除
3、简单查询,返回原始数据类型, String类型
String sql = "select count(*) from user";
// int queryForInt(String sql)
String sql = "select name from user where id = ? ";
// <T> T queryForObject(String sql, Class<T> requiredType, Object... args)
4、 复杂查询 JdbcTemplate 没有handler, 手动完成对象封装
编写实体类 RowMapper
class UserRowMapper implements RowMapper<User> {
@Override
public User mapRow(ResultSet rs, int rowNum) throws SQLException {
// rs 已经指向每一条数据,不需要自己调用 next,将rs指向数据 转换 User对象
User user = new User();
user.setId(rs.getInt("id"));
user.setName(rs.getString("name"));
return user;
}
}
查询单个对象 <T> T
queryForObject(String sql, RowMapper<T> rowMapper, Object... args)
return this.getJdbcTemplate().queryForObject(sql, new UserRowMapper(),id);
查询所有对象List集合 <T> List<T>
query(String sql, RowMapper<T> rowMapper, Object... args)
return this.getJdbcTemplate().query(sql, new UserRowMapper());
class UserRowMapper implements RowMapper<User> {
@Override
public User mapRow(ResultSet rs, int rowNum) throws SQLException {
// rs 已经指向每一条数据,不需要自己调用 next,将rs指向数据 转换 User对象
User user = new User();
user.setId(rs.getInt("id"));
user.setName(rs.getString("name"));
return user;
}
}
查询单个对象 <T> T
queryForObject(String sql, RowMapper<T> rowMapper, Object... args)
return this.getJdbcTemplate().queryForObject(sql, new UserRowMapper(),id);
查询所有对象List集合 <T> List<T>
query(String sql, RowMapper<T> rowMapper, Object... args)
return this.getJdbcTemplate().query(sql, new UserRowMapper());
- 参考资料
http://www.tuicool.com/articles/nYjEjy
- 源码下载
http://download.csdn.net/detail/sunxiaoyu94/9402507