Spring整合JDBC实现简单的增删改

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 下次说明！