Spring整合JDBC实现简单的增删改：
1.导入Spring的包和数据库的驱动包；
2.选择一个数据源（dbcp和C3P0）
3.导入数据源的包（这里我们使用dbcp）
<span style="font-family:FangSong_GB2312;font-size:14px;">commons-pool-1.5.6.jar 、commons-dbcp-1.4.jar（jdk6+）</span>
4.在beans.xml中创建dataSource数据源
5.创建一个jdbc.properties文件存储数据库连接信息
6.在beans.xml中导入对应的jdbc.properties文件
<span style="font-family:FangSong_GB2312;font-size:14px;"><context:property-placeholder location="jdbc.properties" /> </span>
7.写对应的DAO，而且为这个DAO创建一个JdbcTemplate对象，通过JdbcTemplate对象能够方便的完毕对数据库的操作
8.为DAO注入对应的DateSource而且创建JdbcTemplate
/**
* Set the JDBC DataSource to obtain connections from.
*/
@Resource
public void setDataSource(DataSource dataSource) {
JdbcTemplate=new JdbcTemplate(dataSource);
}9.完毕数据对象的加入
详细实现过程：
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:context="http://www.springframework.org/schema/context" xmlns:aop="http://www.springframework.org/schema/aop" xsi:schemaLocation="http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-3.2.xsd 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-3.2.xsd"> <!-- 打开Spring的Annotation的支持 --> <context:annotation-config /> <!-- 设定Spring去哪些包中找Annotation --> <context:component-scan base-package="org.oms.spring" /> <!-- 创建数据源 --> <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>
jdbc.properties
jdbc.driverClassName=com.mysql.jdbc.Driver jdbc.url=jdbc:mysql://localhost:3306/dbname jdbc.username=root jdbc.password=root
Mode类，User
package org.oms.spring.model;
import java.util.Date;
public class User {
private int id;
private String username;
private String password;
private Group group;
private int age;
private Date birthDay;
private Date createDate;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public Date getBirthDay() {
return birthDay;
}
public void setBirthDay(Date birthDay) {
this.birthDay = birthDay;
}
public Date getCreateDate() {
return createDate;
}
public void setCreateDate(Date createDate) {
this.createDate = createDate;
}
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 Group getGroup() {
return group;
}
public void setGroup(Group group) {
this.group = group;
}
public User() {
super();
}
public User(String username, String password, int age,
Date birthDay, Date createDate) {
super();
this.username = username;
this.password = password;
this.age = age;
this.birthDay = birthDay;
this.createDate = createDate;
}
@Override
public String toString() {
return "User [id=" + id + ", username=" + username + ", password="
+ password + ", group=" + group + ", age=" + age
+ ", birthDay=" + birthDay + ", createDate=" + createDate + "]";
}
}
Group类
package org.oms.spring.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;
}
public Group(String name) {
super();
this.name = name;
}
public Group() {
super();
}
@Override
public String toString() {
return "Group [id=" + id + ", name=" + name + "]";
}
}
dao类包含接口：
package org.oms.spring.dao;
import java.util.List;
import org.oms.spring.model.User;
public interface IUserDao {
public void add(User user,int gid);
public void update(User user,int gid);
public void delete(int id);
public User load(int id);
public List<User> list(String sql);
}
package org.oms.spring.dao;
import java.util.List;
import javax.annotation.Resource;
import javax.sql.DataSource;
import org.oms.spring.model.User;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
@Repository("userJdbcDao")
public class UserDao implements IUserDao {
private JdbcTemplate JdbcTemplate;
/**
* Set the JDBC DataSource to obtain connections from.
*/
@Resource
public void setDataSource(DataSource dataSource) {
JdbcTemplate=new JdbcTemplate(dataSource);
}
@Override
public void add(User user,int gid) {
JdbcTemplate.update("insert into user(username,password,gid,age,birthday,createdate) value (?,?,?,?,?,?)",
user.getUsername(),user.getPassword(),gid,user.getAge(),user.getBirthDay(),user.getCreateDate());
}
@Override
public void update(User user,int gid) {
JdbcTemplate.update("update user set username=?,password=?,gid=?,age=?,birthday=?,createdate=? where id=?",
user.getUsername(),user.getPassword(),gid,user.getAge(),user.getBirthDay(),user.getCreateDate(),user.getId());
}
@Override
public void delete(int id) {
JdbcTemplate.update("delete from user where id =?
",id);
}
@Override
public User load(int id) {
return null;
}
@Override
public List<User> list(String sql) {
return null;
}
}
GroupDao类
package org.oms.spring.dao;
import org.oms.spring.model.Group;
public interface IGroupDao {
public void add(Group group);
}
package org.oms.spring.dao;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import javax.annotation.Resource;
import javax.sql.DataSource;
import org.oms.spring.model.Group;
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 com.mysql.jdbc.Connection;
@Repository("groupJdbcDao")
public class GroupDao implements IGroupDao {
private JdbcTemplate JdbcTemplate;
/**
* Set the JDBC DataSource to obtain connections from.
*/
@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(
java.sql.Connection con) throws SQLException {
String INSERT_SQL = "insert into `group`(name) value(?)";
PreparedStatement ps = con.prepareStatement(INSERT_SQL, new String[] {"id"});
ps.setString(1, group.getName());
return ps;
}
}, keyHolder);
group.setId(keyHolder.getKey().intValue());
}
}
測试方法类：
package org.oms.spring.test;
import static org.junit.Assert.*;
import java.util.Date;
import javax.annotation.Resource;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.oms.spring.dao.IGroupDao;
import org.oms.spring.dao.IUserDao;
import org.oms.spring.model.Group;
import org.oms.spring.model.User;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
/**
* 当使用了下面凝视之后。就能够直接在Test中进行依赖注入
*
* @author sunlight
*
*/
// 让Junit执行在Spring的測试环境中
@RunWith(SpringJUnit4ClassRunner.class)
// 载入beans.xml文件
@ContextConfiguration("/beans.xml")
public class TestJdbc {
@Resource(name = "userJdbcDao")
private IUserDao userJdbcDao;
@Resource(name="groupJdbcDao")
private IGroupDao groupJdbcDao;
@Test
public void testAdd() {
Group group=new Group("test1");
groupJdbcDao.add(group);
System.out.println(group.getId());
User user = new User("我是Spring","123", 20, new Date(), new Date());
userJdbcDao.add(user,group.getId());
}
@Test
public void testUpdate() {
User user = new User("Spring","123", 20, new Date(), new Date());
user.setId(2);
userJdbcDao.update(user,1);
}
@Test
public void testDel() {
userJdbcDao.delete(2);
}
}
測试结果图：
数据库表：
CREATE TABLE `User` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Id主键', `username` varchar(100) NOT NULL DEFAULT '' COMMENT '姓名', `password` varchar(100) DEFAULT NULL COMMENT '密码', `gid` int(11) DEFAULT NULL COMMENT '组ID', `age` int(11) DEFAULT '18' COMMENT '年龄', `birthday` date DEFAULT NULL COMMENT '生日', `createdate` datetime DEFAULT NULL COMMENT '创建时间', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
CREATE TABLE `Group` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(100) NOT NULL DEFAULT '' COMMENT '组名称', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
数据表中中文乱码，瞌睡了2014年09月18日00:36:54 下次说明。
