spring 集成 jdbc
1、导入Spring的包（spring的dist中的jar包和commons-logging包）和数据库的驱动包
2、选择一个数据源（DBCP和C3p0）
3、导入数据源的包:DBCP （commons-dbcp-jar,commons-pool.jar）
4、在beans.xml中创建dataSource数据源
<bean id="dataSource"
class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
<property name="driverClassName" value="${jdbc.driverClassName}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
</bean>
附件：此时完整的bean.xml为：
<?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:aop="http://www.springframework.org/schema/aop"
xmlns:context="http://www.springframework.org/schema/context"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context-3.0.xsd
http://www.springframework.org/schema/aop
http://www.springframework.org/schema/aop/spring-aop-3.0.xsd">
<!-- 打开Spring的Annotation支持 -->
<context:annotation-config/>
<!-- 设定Spring 去哪些包中找Annotation -->
<context:component-scan base-package="org.zttc.itat"/>
<bean id="dataSource"
class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
<property name="driverClassName" value="${jdbc.driverClassName}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
</bean>
<!-- 导入Src目录下的jdbc.properties文件 -->
<context:property-placeholder location="jdbc.properties"/>
</beans>
5、创建一个jdbc.properties文件来设置数据库的连接信息
jdbc.driverClassName=com.mysql.jdbc.Driver
jdbc.url = jdbc:mysql://localhost:3306/spring_teach
jdbc.username = root
jdbc.password = root
6、在beans.xml中导入相应的properties文件
<context:property-placeholder location="jdbc.properties"/>
附件：
新建User.java，Group.java（它们是多对一的关系）
package org.zttc.itat.model;
public class Group {
private int id;
private String name;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
@Override
public String toString() {
return "Group [id=" + id + ", name=" + name + "]";
}
package org.zttc.itat.model;
public class User {
private int id;
private String username;
private String password;
private String nickname;
private Group group;
public Group getGroup() {
return group;
}
public void setGroup(Group group) {
this.group = group;
}
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;
}
public String getNickname() {
return nickname;
}
public void setNickname(String nickname) {
this.nickname = nickname;
}
public User() {
}
public User(String username, String password, String nickname) {
this.username = username;
this.password = password;
this.nickname = nickname;
}
@Override
public String toString() {
return "User [id=" + id + ", username=" + username + ", password="
+ password + ", nickname=" + nickname + ", group=" + group
+ "]";
}
}
}
新建一个spring_teach数据库，新建t_user表（其中的gid字段是外键）。t_group表
7、写相应的DAO，并且为这个DAO创建一个JDBCTemplate对象，通过JdbcTemplate对象可以方便的完成对数据库的操作
8、为DAO注入相应的DataSource并且创建JdbcTemplate
@Resource
public void setDataSource(DataSource dataSource) {
jdbcTemplate = new JdbcTemplate(dataSource);
}
//抽象类：
package org.zttc.itat.dao;
import java.util.List;
import org.zttc.itat.model.User;
public interface IUserDao {
public void add(User user,int gid);
public void update(User user);
public void delete(int id);
public User load(int id);
public List<User> list(String sql,Object[] args);
}
************************无聊的分割线*************************
package org.zttc.itat.dao;
import org.zttc.itat.model.Group;
public interface IGroupDao {
public void add(Group group);
}
//实现类
package org.zttc.itat.dao;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import javax.annotation.Resource;
import javax.sql.DataSource;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
import org.zttc.itat.model.Group;
import org.zttc.itat.model.User;
@Repository("userJdbcDao")
public class UserDao implements IUserDao {
private JdbcTemplate jdbcTemplate;
@Resource
public void setDataSource(DataSource dataSource) {
jdbcTemplate = new JdbcTemplate(dataSource);
}
@Override
public void add(User user,int gid) {
jdbcTemplate.update("insert into t_user(username,password,nickname,gid) value (?,?,?,?)",
user.getUsername(),user.getPassword(),user.getNickname(),gid);
}
@Override
public void update(User user) {
jdbcTemplate.update("update t_user set username=?,password=?,nickname=? where id=?",
user.getUsername(),user.getPassword(),user.getNickname(),user.getId());
}
@Override
public void delete(int id) {
jdbcTemplate.update("delete from t_user where id=?",id);
}
@Override
public User load(int id) {
String sql = "select t1.id uid,t1.*,t2.* from t_user t1 left join t_group t2 on(t1.gid=t2.id) where t1.id=?";
/*
* 第一个参数是SQL语句
* 第二个参数是SQL语句中的参数值，需要传入一个对象数组
* 第三个参数是一个RowMapper,这个rowMapper可以完成一个对象和数据库字段的对应，实现这个RowMapper需要
* 实现mapRow方法，在mapRow方法中有rs这个参数，通过rs可以有效的获取数据库的字段
* 如果这个方法在该DAO中会被重复使用，建议通过内部类来解决，而不要使用匿名的内部类
*/
User u = (User)jdbcTemplate.queryForObject(sql, new Object[]{id},new UserMapper());
return u;
}
@Override
public List<User> list(String sql,Object[] args) {
String sqlCount = "select count(*) from t_user";
//获取整数值
int count = jdbcTemplate.queryForInt(sqlCount);
System.out.println(count);
String nCount = "select nickname from t_user";
//获取String类型的列表
List<String> ns = jdbcTemplate.queryForList(nCount,String.class);
for(String n:ns) {
System.out.println("--->"+n);
}
String tSql = "select username,nickname from t_user";
//无法取出user
/*List<User> us = jdbcTemplate.queryForList(tSql, User.class);
for(User u:us) {
System.out.println(u);
}*/
//对象数组也无法返回
/*List<Object[]> os = jdbcTemplate.queryForList(tSql, Object[].class);
for(Object[] oo:os) {
System.out.println(oo[0]+","+oo[1]);
}*/
List<User> us = jdbcTemplate.query(tSql,new RowMapper<User>(){
@Override
public User mapRow(ResultSet rs, int rowNum) throws SQLException {
User u = new User();
u.setNickname(rs.getString("nickname"));
u.setUsername(rs.getString("username"));
return u;
}
});
for(User u:us) {
System.out.println(u);
}
return jdbcTemplate.query(sql, args, new UserMapper());
}
//这里特别注意 RowMapper抽象类;注意他的抽象方法 mapRow(ResultSet rs, int rowNum)；看到这里应该就知道这个方法的意 //思啦,除非你从来没有用纯java连接过数据库
private class UserMapper implements RowMapper<User> {
@Override
public User mapRow(ResultSet rs, int rowNum) throws SQLException {
Group g = new Group();
g.setName(rs.getString("name"));
g.setId(rs.getInt("gid"));
User u = new User();
u.setGroup(g);
u.setId(rs.getInt("uid"));
u.setNickname(rs.getString("nickname"));
u.setPassword(rs.getString("password"));
u.setUsername(rs.getString("username"));
return u;
}
}
}
************************无聊的分割线*************************
package org.zttc.itat.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import javax.annotation.Resource;
import javax.sql.DataSource;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import org.zttc.itat.model.Group;
@Repository("groupJdbcDao")
public class GroupJdbcDao implements IGroupDao {
private JdbcTemplate jdbcTemplate;
@Resource
public void setDataSource(DataSource dataSource) {
jdbcTemplate = new JdbcTemplate(dataSource);
}
@Override
public void add(final Group group) {
/**
* 通过以下方法可以添加一个对象，并且获取这个对象自动递增的id
*/
KeyHolder keyHolder = new GeneratedKeyHolder();
jdbcTemplate.update(new PreparedStatementCreator() {
@Override
public PreparedStatement createPreparedStatement(Connection con)
throws SQLException {
String sql = "insert into t_group (name) value(?)";
PreparedStatement ps = con.prepareStatement(sql,new String[]{"id"});
ps.setString(1, group.getName());
return ps;
}
},keyHolder);
group.setId(keyHolder.getKey().intValue());
}
}
test:
package org.zttc.itat.test;
import java.util.List;
import javax.annotation.Resource;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import org.zttc.itat.dao.IGroupDao;
import org.zttc.itat.dao.IUserDao;
import org.zttc.itat.model.Group;
import org.zttc.itat.model.User;
/**
* 当使用了以下注释之后，就可以直接在Test中进行依赖注入
*/
//让Junit运行在Spring的测试环境中
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("/beans.xml")//加载beans.xml文件
public class TestJdbc {
@Resource(name="userJdbcDao")
private IUserDao userJdbcDao;
@Resource(name="groupJdbcDao")
private IGroupDao groupJdbcDao;
@Test
public void testAdd() {
Group g = new Group();
g.setName("文章审核人员");
groupJdbcDao.add(g);
System.out.println(g.getId());
User u = new User("tangsheng","123","唐僧");
userJdbcDao.add(u, 1);
}
@Test
public void testUpdate() {
User u = new User("zhangfeng","123","张峰");
u.setId(1);
userJdbcDao.update(u);
}
@Test
public void testDelete() {
userJdbcDao.delete(1);
}
@Test
public void testLoad() {
User u = userJdbcDao.load(2);
System.out.println(u.getNickname()+","+u.getGroup().getName());
}
@Test
public void testList() {
List<User> us = userJdbcDao.list("select t1.id uid,t1.*,t2.* from t_user t1 left join t_group t2 on(t1.gid=t2.id)", null);
for(User u:us) {
System.out.println(u);
}
}
}
9、完成数据对象的添加
