使用Spring IOC/DI完成对user_info表的CURD

一、数据库部分

1、创建数据库

• 创建会员数据库

create database if not exists user_db;

2、创建表

• 创建会员表

create table if not exists user_info(
	u_id int auto_increment comment '会员编号',
	u_name varchar(32) not null comment '会员姓名',
	u_regdate date not null comment '入会日期',
	u_money double default 1.0 comment '入会金额',
	constraint pk_userinfo_uid primary key(u_id)
)engine=innodb default charset='utf8';

二、Java部分

1、实体类

package com.gaj.entity;

import java.util.Date;

/**
  * UserInfo实体类
  * JavaBean
  * 对应user_db数据库的user_info表
  * 对应字段 u_id,u_name,u_regdate,u_money
 * @author Jan
 *
 */
public class UserInfo { 
	
	private Integer uid;	// 会员编号
	private String uname;	// 会员姓名
	private Date regDate;	// 入会日期
	private Double money;	// 入会金额
	
	public Integer getUid() {
		return uid;
	}
	public void setUid(Integer uid) {
		this.uid = uid;
	}
	public String getUname() {
		return uname;
	}
	public void setUname(String uname) {
		this.uname = uname;
	}
	public Date getRegDate() {
		return regDate;
	}
	public void setRegDate(Date regDate) {
		this.regDate = regDate;
	}
	public Double getMoney() {
		return money;
	}
	public void setMoney(Double money) {
		this.money = money;
	}

	@Override
	public String toString() {
		return "UserInfo [uid=" + uid + ", uname=" + uname + ", regDate=" + regDate + ", money=" + money + "]";
	}
}

2、DAO层接口

package com.gaj.dao;

import java.sql.SQLException;
import java.util.List;

import com.gaj.entity.UserInfo;

/**
 * 数据访问层接口
 * @author Jan
 *
 */
public interface UserDAO {

	public int insertUser(UserInfo user) throws SQLException;
	
	public int updateUser(UserInfo user) throws SQLException;
	
	public int deleteUser(UserInfo user) throws SQLException;
	
	public UserInfo findUserById(Integer uid) throws SQLException;
	
	public List<UserInfo> findUsersByPage(Integer start, Integer size) throws Exception;
	
	public int usersCount() throws SQLException;
	
}

3、DAO层实现类

package com.gaj.dao.impl;

import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import com.gaj.dao.UserDAO;
import com.gaj.entity.UserInfo;

public class UserDaoImpl implements UserDAO {
	
	private QueryRunner qr;
	// spring用set方法注入该属性
	public void setQr(QueryRunner qr) {
		this.qr = qr;
	}

	@Override
	public int insertUser(UserInfo user) throws SQLException {
		// 定义返回值
		int count = 0;
		// 编写sql语句
		String sql = "insert into user_info values(null,?,?,?)";
		// 占位符赋值
		Object [] params = {user.getUname(), user.getRegDate(), user.getMoney()};
		// 执行sql
		count = qr.update(sql, params);
		//返回
		return count;
	}

	@Override
	public int updateUser(UserInfo user) throws SQLException {
		// 定义返回值
		int count = 0;
		// 编写sql语句
		String sql = "update user_info set u_name=?,u_regdate=?,u_money=? where u_id=?";
		// 占位符赋值
		Object[] params = {user.getUname(), user.getRegDate(), user.getMoney(), user.getUid()};
		// 执行sql
		count = qr.update(sql, params);
		// 返回
		return count;
	}

	@Override
	public int deleteUser(UserInfo user) throws SQLException {
		// 定义返回值
		int count = 0;
		// 编写sql语句
		String sql = "delete from user_info where u_id=?";
		// 占位符赋值
		Object param = user.getUid();
		// 执行sql
		count = qr.update(sql, param);
		// 返回
		return count;
	}

	@Override
	public UserInfo findUserById(Integer uid) throws SQLException {
		// 定义返回值
		UserInfo user = null;
		// 编写sql语句
		String sql = "select u_id uid, u_name uname, u_regdate regDate, u_money money from user_info where u_id=?";
		// 占位符赋值
		Object param = uid;
		// 执行sql
		user = qr.query(sql, new BeanHandler<UserInfo>(UserInfo.class), param);
		// 返回
		return user;
	}

	@Override
	public List<UserInfo> findUsersByPage(Integer start, Integer size) throws SQLException {
		// 定义返回值
		List<UserInfo> users = null;
		// 编写sql语句
		String sql = "select u_id uid, u_name uname, u_regdate regDate, u_money money from user_info limit ?,?";
		// 占位符赋值
		Object[] params = {start, size};
		// 执行sql
		users = qr.query(sql, new BeanListHandler<UserInfo>(UserInfo.class), params);
		// 返回
		return users;
	}

	@Override
	public int usersCount() throws SQLException {
		// 定义返回值
		int totalCount = 0;
		// 编写sql语句
		String sql = "select count(1) from user_info";
		// 执行sql
		Number num = qr.query(sql, new ScalarHandler<Number>(1));
		// 转换
		totalCount = num.intValue();
		// 返回
		return totalCount;
	}

}

4、Service层接口

package com.gaj.service;

import java.sql.SQLException;
import java.util.List;

import com.gaj.entity.UserInfo;

/**
 * 业务层接口
 * @author Jan
 *
 */
public interface UserService {

	public int insertUser(UserInfo user) throws SQLException;
	
	public int updateUser(UserInfo user) throws SQLException;
	
	public int deleteUser(UserInfo user) throws SQLException;
	
	public UserInfo findUserById(Integer uid) throws SQLException;
	
	public List<UserInfo> findUsersByPage(Integer pageIndex, Integer pageSize) throws Exception;
	
	public int UsersCount() throws SQLException;
	
}

5、Service层实现类

package com.gaj.service.impl;

import java.sql.SQLException;
import java.util.List;

import com.gaj.dao.UserDAO;
import com.gaj.entity.UserInfo;
import com.gaj.service.UserService;

/**
 * 业务层实现类
 * @author Jan
 *
 */
public class UserServiceImpl implements UserService {
	
	private UserDAO userDAO;
	// spring用set方法注入该属性
	public void setUserDAO(UserDAO userDAO) {
		this.userDAO = userDAO;
	}

	@Override
	public int insertUser(UserInfo user) throws SQLException {
		return userDAO.insertUser(user);
	}

	@Override
	public int updateUser(UserInfo user) throws SQLException {
		return userDAO.updateUser(user);
	}

	@Override
	public int deleteUser(UserInfo user) throws SQLException {
		return userDAO.deleteUser(user);
	}

	@Override
	public UserInfo findUserById(Integer uid) throws SQLException {
		return userDAO.findUserById(uid);
	}

	@Override
	public List<UserInfo> findUsersByPage(Integer pageIndex, Integer pageSize) throws Exception {
		// 创建返回值
		List<UserInfo> list = null;
		if(pageIndex != null && pageSize != null){
			if(pageIndex > 0 && pageSize > 0){
				int start = (pageIndex - 1) * pageSize;
				int size = pageSize;
				list = userDAO.findUsersByPage(start, size);
			}
		}
		// 返回
		return list;
	}

	@Override
	public int UsersCount() throws SQLException {
		return userDAO.usersCount();
	}

}

三、XML部分

1、applicationContext.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"
    xsi:schemaLocation="
    	http://www.springframework.org/schema/beans
        http://www.springframework.org/schema/beans/spring-beans.xsd">
	
	<!-- 创建一个druid数据源并绑定连接属性 -->
	<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource">
		<property name="driverClassName" value="com.mysql.jdbc.Driver" />
		<property name="url" value="jdbc:mysql://127.0.0.1:3306/user_db?characterEncoding=utf-8" />
		<property name="username" value="root" />
		<property name="password" value="root" />
	</bean>
	
	<!-- 创建QueryRunner对象并注入druid数据源 -->
	<bean id="qr" class="org.apache.commons.dbutils.QueryRunner" scope="prototype">
		<constructor-arg name="ds" ref="dataSource" />
	</bean>
	
	<!-- 创建DAO层对象 -->
	<bean id="userDAO" class="com.gaj.dao.impl.UserDaoImpl">
		<property name="qr" ref="qr" />
	</bean>
	
	<!-- 创建Service层对象 -->
	<bean id="userService" class="com.gaj.service.impl.UserServiceImpl">
		<property name="userDAO" ref="userDAO" />
	</bean>
</beans>

四、测试CURD

1、QueryRunner测试

测试scope属性

package com.gaj.test;

import org.apache.commons.dbutils.QueryRunner;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

public class QueryRunnerTest {
	public static void main(String[] args) {
		ApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");
		System.out.println(context);
		QueryRunner qr1 = context.getBean(QueryRunner.class);
		QueryRunner qr2 = context.getBean(QueryRunner.class);
		System.out.println(qr1);
		System.out.println(qr2);
	}
}

2、CURD测试

package com.gaj.test;

import java.sql.SQLException;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Random;
import java.util.Scanner;

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

import com.gaj.entity.UserInfo;
import com.gaj.service.UserService;

/**
 * 测试类
 * 测试Spring版JDBC的增删改查
 * @author Jan
 *
 */
public class SpringTest {
	
	private ApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");
	private UserService userService = (UserService) context.getBean("userService");
	
	Scanner scanner = new Scanner(System.in);
	Random random = new Random();
	DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
	
	@Test
	public void addBatchTest() throws SQLException{
		UserInfo user = null;
		for (int i = 1; i <= 100; i++) {
			user = new UserInfo();
			user.setUname("用户" + i);
			user.setRegDate(new Date(System.currentTimeMillis() + 1000*60*60*24*random.nextInt(366)));
			user.setMoney(random.nextInt(50000) + 0D);
			int count = userService.insertUser(user);
			System.out.println(count>0 ? "添加成功！" : "添加失败！");
		}
	}
	
	@Test
	public void addTest() throws ParseException, SQLException{
		UserInfo user = new UserInfo();
		System.out.println("请输入会员姓名：");
		user.setUname(scanner.next());
		System.out.println("请输入入会时间：");
		user.setRegDate(df.parse(scanner.next()));
		System.out.println("请输入入会金额：");
		user.setMoney(scanner.nextDouble());
		int count = userService.insertUser(user);
		System.out.println(count>0 ? "添加成功！" : "添加失败！");
	}
	
	@Test
	public void updateTest() throws SQLException, ParseException{
		System.out.println("请输入要更新的会员编号：");
		int uid = scanner.nextInt();
		UserInfo user = userService.findUserById(uid);
		System.out.println("请输入会员姓名：");
		user.setUname(scanner.next());
		System.out.println("请输入入会时间：");
		user.setRegDate(df.parse(scanner.next()));
		System.out.println("请输入入会金额：");
		user.setMoney(scanner.nextDouble());
		int count = userService.updateUser(user);
		System.out.println(count>0 ? "更新成功！" : "更新失败！");
	}
	
	@Test
	public void deleteTest() throws SQLException{
		System.out.println("请输入要删除的会员编号：");
		int uid = scanner.nextInt();
		UserInfo user = userService.findUserById(uid);
		int count = userService.deleteUser(user);
		System.out.println(count>0 ? "删除成功！" : "删除失败！");
	}
	
	@Test
	public void QueryTest() throws Exception{
		// 总条数
		int totalCount = userService.UsersCount();
		// 页容量
		int pageSize = 10;
		// 总页数
		int totalPage = totalCount % pageSize == 0 ? totalCount/pageSize : totalCount/pageSize+1;
		for(int i = 1; i <= totalPage; i++){
			int pageIndex = i;
			System.out.println("总条数：" + totalCount + "\t\t页容量：" + pageSize + "\t\t总页数" + totalPage + "\t\t当前页：" + pageIndex);
			List<UserInfo> users = userService.findUsersByPage(pageIndex, pageSize);
			for (UserInfo userInfo : users) {
				System.out.println(userInfo);
			}
		}
	}
}