Apache提供的开源工具类对原生态JDBC的封装

package com.lin.test;

import java.util.List;

import org.junit.Test;

import com.lin.entity.User;
import com.lin.service.UserService;
import com.lin.service.impl.UserServiceImpl;

public class UserTest {
	
	private UserService us=new UserServiceImpl();
	@Test
	public void testAdd() {
		User user=new User();
		user.setUsername("张三");
		user.setPassword("123456");
		user.setAddress("北京");
		user.setPhone("263263");
		
		int count=us.add(user);
		System.out.println(count);
	}
	@Test
	public void testFindAll() {
		List<User>list=us.selectAll();
		if(list!=null) {
			for(User user:list) {
				System.out.println(user);
			}
		}
	}
	@Test
	public void testUpdata() {
		User user=new User();
		user.setUserId(3);
		user.setUsername("da");
		user.setPassword("123456");
		user.setAddress("北京");
		user.setPhone("263263");
		
		int count=us.update(user);
		System.out.println(count);
	}
}

package com.lin.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

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

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

import utils.MyDbutils;

/**
 * 持久层(用户数据访问的接口实现层)
 * 
 * @author zhangyang
 * 
 *         Apache:提供开源的工具类:DbUtils
 *
 *         ResultSetHandler接口：转换类型接口 BeanHandler类：实现类，把一条记录转换成对象
 *         BeanListHandler类：实现类，把多条记录转换成List集合。 ScalarHandler类：实现类，适合获取一行一列的数据。
 *         QueryRunner：执行sql语句的类 (执行对象:更新,也可以查询)
 * 
 *         构造方法: QueryRunner(DataSource ds)
 * 
 *         增、删、改：int update(); 查询：Object query();
 * 
 * 
 *         定义一个MyDbUtils工具类: 获取数据源 关闭资源
 */

public class UserDaoImpl implements UserDao {

	private Connection conn;
	private PreparedStatement stmt;
	private ResultSet rs;
	// 成员变量：声明执行Queryrunner
	private QueryRunner qr;

	@Override
	public int add(User user) {
		try {
			// 获取执行对象
			qr = new QueryRunner(MyDbutils.getDataSource());
			// 底层封装就是PreparedStatement
			// 执行更新
			// update(String sql,Object...params)
			// 参数1：执行的参数化sql
			// 参数2：parms:可变参数：理解为数组（设置多个实际参数：指定占位符）
			int count = qr.update("insert into user(username,password,address,phone)values(?,?,?,?);",
					user.getUsername(), user.getPassword(), user.getAddress(), user.getPhone());
			return count;

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return 0;
	}

	@Override
	public int updata(User user) {
		try {
			// 获取连接对象
			qr = new QueryRunner(MyDbutils.getDataSource());
			int count = qr.update("update user set username = ?,password = ?,address = ? ,phone = ? where userId = ?;",
					user.getUsername(),user.getPassword(),user.getAddress(),user.getPhone(),user.getUserId()) ;
			return count;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			MyDbutils.close(stmt, conn);
		}
		return 0;
	}

//	根据用户id删除用户信息
	@Override
	public int delete(int id) {

		return 0;
	}

	@Override
	public List<User> selectAll() {
		try {
			// 提供开源工具类：有一个接口ResultSetHandler接口
			// 子实现类：BeanListHandler<?>:将通过sql查询的结果封装到List集合中
			// 获取执行对象
			qr = new QueryRunner(MyDbutils.getDataSource());
			List list = qr.query("select * from user", new BeanListHandler<User>(User.class));
			return list;

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}

	// 通过id查询用户
	@Override
	public User findById(int id) {

		return null;
	}

}

 

package com.lin.dao;

import java.util.List;

import com.lin.entity.User;

/**
 * 持久层（用户数据访问接口层）
 * @author wl
 * */
public interface UserDao {
//	添加用户信息
	public abstract int add(User user);
	
//	更新操作
	public abstract int updata(User user);
	
//	删除：通过编号删除用户
	public abstract int delete(int id);
	
//	查询所有
	public abstract List<User> selectAll();
	
//	通过用户id查询用户
	public abstract User findById(int id);
}

 

package com.lin.entity;

public class User {
	//对应user表中的字段
	private int userId;
	private String username;
	private String password;
	private String address;
	private String phone;
	
	public User() {
		super();
		// TODO Auto-generated constructor stub
	}

	public User(int userId, String username, String password, String address, String phone) {
		super();
		this.userId = userId;
		this.username = username;
		this.password = password;
		this.address = address;
		this.phone = phone;
	}
	
	public int getUserId() {
		return userId;
	}

	public void setUserId(int userId) {
		this.userId = userId;
	}

	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 getAddress() {
		return address;
	}

	public void setAddress(String address) {
		this.address = address;
	}

	public String getPhone() {
		return phone;
	}

	public void setPhone(String phone) {
		this.phone = phone;
	}

	@Override
	public String toString() {
		return "User [userId=" + userId + ", username=" + username + ", password=" + password + ", address=" + address
				+ ", phone=" + phone + ", getClass()=" + getClass() + ", hashCode()=" + hashCode() + ", toString()="
				+ super.toString() + "]";
	}

	
}

 

package com.lin.service.impl;

import java.util.List;
/**
 * 用户的业务接口实现层
 * 调用dao层完成增删查改业务
 * @author wl
 * */

import com.lin.dao.UserDao;
import com.lin.dao.impl.UserDaoImpl;
import com.lin.entity.User;
import com.lin.service.UserService;

public class UserServiceImpl implements UserService {
	
//	在创建一个实例：持久层对象dao层
	private UserDao ud=new UserDaoImpl();

	@Override
	public int add(User user) {
		
		return ud.add(user);
	}

	@Override
	public int update(User user) {
		return ud.updata(user);
	}

	@Override
	public int delete(int id) {
		return ud.delete(id);
	}

	@Override
	public List<User> selectAll() {
		return ud.selectAll();
	}

	@Override
	public User find(int id) {
		return ud.findById(id);
	}

	@Override
	public long findCount() {
		return 0;
	}

}

 

package com.lin.service;

import java.util.List;

import com.lin.entity.User;

/*
 * 针对用户操作的业务接口层
 * */
public interface UserService {
	
	//添加用户信息
	public abstract int add(User user);
	
	//更新操作
	public abstract int update( User user);
	
	//删除：通过编号删除用户
	public abstract int delete(int id);
	
	//查询所有
	public abstract List<User> selectAll();
	
	//通过用户id查询所有
	public abstract User find(int id);
	
//	查询user表中记录
	public abstract long findCount();
}

 

package utils;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

import javax.sql.DataSource;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;

public class MyDbutils {
//	DruidDataSource:Druid的数据源 类 实现 接口DataSource
	private static DruidDataSource dataSource;
	static {
		try {
			Properties prop = new Properties();
			// 读取配置文件
			InputStream inputStream = MyDbutils.class.getClassLoader().
					getResourceAsStream("druid.properties") ;
//		加载
			prop.load(inputStream);
//			通过工厂类获取数据源
			dataSource = (DruidDataSource) (DruidDataSourceFactory.createDataSource(prop));

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	// 获取数据源
	public static DataSource getDataSource() {
		return dataSource;
	}

	// 释放资源
	public static void close(ResultSet rs, Statement stmt, Connection conn) {
		try {
			if (rs != null) {
				rs.close();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			if (stmt != null) {
				stmt.close();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			if (conn != null) {
				conn.close();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public static void close(Statement stmt, Connection conn) {

		try {
			if (stmt != null) {
				stmt.close();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			if (conn != null) {
				conn.close();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}