Spring使用JdbcTemplate实现数据库的增删改查（使用注解的形式）

直接上代码，首先是工程目录

这里我用了MyEclipse自带的spring包。并从外部导入了mysql驱动包mysql-connector-java-5.1.46.jar和数据库连接池所用的jar包mchange-commons-java-0.2.3.4.jar
创建数据库并插入数据（用navcat导出的sql文件，可以直接使用）

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `sex` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `age` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES (1, '张三', '男', 18);
INSERT INTO `user` VALUES (2, '李四', '男', 20);
INSERT INTO `user` VALUES (3, '王芳', '女', 19);

实体类User.java

package com.wang.entity;

import java.io.Serializable;

import org.springframework.stereotype.Component;
@Component("user")
public class User implements Serializable{
	private int id;
	private String name;
	private String sex;
	private int age;

	public User() {

	}

	public User(String name, String sex, int age) {
		super();
		this.name = name;
		this.sex = sex;
		this.age = age;
	}

	//重写toString方法
	public String toString() {
		return "User [id=" + id + ", name=" + name + ", sex=" + sex + ", age=" + age + "]";
	}

	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 String getSex() {
		return sex;
	}

	public void setSex(String sex) {
		this.sex = sex;
	}

	public int getAge() {
		return age;
	}

	public void setAge(int age) {
		this.age = age;
	}

}

从数据库查找user类所用的结果集合类

package com.wang.entity;

import java.sql.ResultSet;
import java.sql.SQLException;

import org.springframework.jdbc.core.RowMapper;

public class UserRow implements RowMapper<User> {
	@Override
	public User mapRow(ResultSet arg0, int arg1) throws SQLException {
		User user = new User();
		user.setName(arg0.getString("name"));
		user.setSex(arg0.getString("sex"));
		user.setAge(arg0.getInt("age"));
		return user;
	}

}

spring配置文件application.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:p="http://www.springframework.org/schema/p"
	xmlns:context="http://www.springframework.org/schema/context"
	xmlns:aop="http://www.springframework.org/schema/aop" xmlns:tx="http://www.springframework.org/schema/tx"
	xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.1.xsd
	http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd
	 http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd
        http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop.xsd">
	<!-- 开启注解扫描 -->
	<context:component-scan base-package="com" />
	<!-- 数据库c3p0连接池 -->
	<bean id="dateSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
		<property name="driverClass">
			<value>com.mysql.jdbc.Driver</value>
		</property>
		<property name="jdbcUrl" value="jdbc:mysql://localhost:3306/springtest"></property>
		<property name="user" value="root" />
		<property name="password" value="root" />
	</bean>
	<!-- 配置jdbc模板 -->
	<bean id="template" class="org.springframework.jdbc.core.JdbcTemplate">
		<property name="dataSource" ref="dateSource"></property>
	</bean>

</beans>

dao层代码UserDao.java

package com.dao;

import java.util.Iterator;
import java.util.List;

import javax.annotation.Resource;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import com.wang.entity.User;
import com.wang.entity.UserRow;

@Repository("userDao")
public class UserDao {
	@Resource(name = "template")
	JdbcTemplate jdbcTemplate;

	// 添加的方法
	public void add(User user) {
		jdbcTemplate.update("insert into user (name,sex,age) values(?,?,?)", user.getName(), user.getSex(),
				user.getAge());

	}

	// 删除的方法（根据id删除）
	public void delete(int id) {
		jdbcTemplate.update("delete from user where id=?", id);
	}

	// 修改
	public void update(User user) {
		jdbcTemplate.update("update user set name = ?,sex = ?,age = ? where id = ?", user.getName(), user.getSex(), user.getAge(),user.getId());

	}

	// 查找所有数据（需要用一个RowMap来接收user类）
	public void findAll() {
		List<User> list = jdbcTemplate.query("select * from user", new UserRow());
		Iterator<User> it = list.iterator();
		while (it.hasNext()) {
			User user = (User) it.next();
			System.out.println(user);
		}

	}
}

UserService.java

package com.service;

import javax.annotation.Resource;

import org.springframework.stereotype.Service;

import com.dao.UserDao;
import com.wang.entity.User;

@Service("userService")
public class UserService {
	@Resource(name = "userDao")
	UserDao userDao;

	public void add(User user) {
		userDao.add(user);
		System.out.println("add.....");
	}

	public void delete(int id) {
		userDao.delete(id);
		System.out.println("delete.....");
	}

	public void update(User user) {
		userDao.update(user);
		System.out.println("update.....");
	}

	public void findAll() {
		userDao.findAll();
	}
}

测试类

package Test;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import com.service.UserService;
import com.wang.entity.User;

public class Test01 {

	public static void main(String[] args) {
		ApplicationContext ac = new ClassPathXmlApplicationContext("applicationContext.xml");//读取配置文件
		UserService userService = (UserService) ac.getBean("userService");
		//添加
		User user = new User("邓五","女", 18);
//		userService.add(user);
		//修改
//		User user1 = new User("邓四","女", 18);
//		user.setId(4);//因为是根据id来修改数据所以要保存所需要修改数据的id
//		userService.update(user1);
		//删除
//		userService.delete(4);
		//查找
		userService.findAll();
	}

}

工程已上传，可自行下载https://github.com/1419908068/java/tree/852fdbc7449a516f406f82cb03fc5907d8d37ab0