JDBC优化之路-2（添加工具类DBUtil）

2.两个实例对象

users数据表：

admin数据表：

项目目录：

dao层：IUserDao.java

package com.hpe.dao;import java.util.List;import com.hpe.pojo.User;public interface IUserDao {User login(User user);List<User> selectAll();int add(User user);}

dao层：IAdminDao.java

package com.hpe.dao;import com.hpe.pojo.Admin;public interface IAdminDao {Admin login(Admin admin);}

dao实现层：UserDaoImpl.java

package com.hpe.dao.impl;import java.util.List;import com.hpe.dao.IUserDao;import com.hpe.pojo.User;import com.hpe.util.DBUtil;public class UserDaoImpl implements IUserDao {@Overridepublic User login(User user) {String sql = "select * from users where name=? and pwd=?";Object[] params = {user.getName(),user.getPwd()};User resUser =DBUtil.selectObject(sql, params, User.class);return resUser;}@Overridepublic List<User> selectAll() {String sql = "select * from users ";List<User>list=DBUtil.select(sql, null,User.class);return list;}@Overridepublic int add(User user) {String sql = "insert into users (name,pwd) values(?,?)";Object[] params = {user.getName(),user.getPwd()};int res = DBUtil.update(sql, params);return res;}}

dao实现层：AdminDaoImpl.java

package com.hpe.dao.impl;import com.hpe.dao.IAdminDao;import com.hpe.pojo.Admin;import com.hpe.util.DBUtil;public class AdminDaoImpl implements IAdminDao {@Overridepublic Admin login(Admin admin) {String sql = "select * from admin where adminname=? and adminpassword=?";Object[] params = {admin.getAdminname(),admin.getAdminpassword()};Admin resAdmin = DBUtil.selectObject(sql, params,Admin.class);return resAdmin;}}

实体类：User.java

package com.hpe.pojo;public class User {private int id;private String name;private String pwd;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 getPwd() {return pwd;}public void setPwd(String pwd) {this.pwd = pwd;}@Overridepublic String toString() {return "User [id=" + id + ", name=" + name + ", pwd=" + pwd + "]";}}

实体类：Admin.java

package com.hpe.pojo;public class Admin {private int adminid;private String adminname;private String adminpassword;public int getAdminid() {return adminid;}public void setAdminid(int adminid) {this.adminid = adminid;}public String getAdminname() {return adminname;}public void setAdminname(String adminname) {this.adminname = adminname;}public String getAdminpassword() {return adminpassword;}public void setAdminpassword(String adminpassword) {this.adminpassword = adminpassword;}@Overridepublic String toString() {return "Admin [adminid=" + adminid + ", adminname=" + adminname + ", adminpassword=" + adminpassword + "]";}}

工具类：DBUtil.java

package com.hpe.util;import java.beans.BeanInfo;import java.beans.IntrospectionException;import java.beans.Introspector;import java.beans.PropertyDescriptor;import java.lang.reflect.InvocationTargetException;import java.lang.reflect.Method;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.ResultSetMetaData;import java.sql.SQLException;import java.util.ArrayList;import java.util.List;public class DBUtil {/* * 获取数据库连接 */public static Connection getConn() throws ClassNotFoundException, SQLException{Class.forName("com.mysql.jdbc.Driver");String url = "jdbc:mysql://localhost:3306/work?useUnicode=true&characterEncoding=utf-8";String username="root";String password="root";    Connection conn=DriverManager.getConnection(url, username, password);    return conn;}/* * 关闭数据库资源 */public static void close(ResultSet rs,PreparedStatement ps,Connection conn){try {if(rs!=null){rs.close();}} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}try {if(ps!=null){ps.close();}} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}try {if(conn!=null){conn.close();}} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}/* * Class<T> type创建什么类型的对象 * T用来保存一个参数类型 */public static <T> List<T> select(String sql,Object[] params,Class<T> type){Connection conn=null;PreparedStatement ps=null;ResultSet rs=null;List<T>list =new ArrayList<>();try {conn=getConn();ps=conn.prepareStatement(sql);if(params != null){for(int i=0;i<params.length;i++){ps.setObject(i+1, params[i]);}}rs  = ps.executeQuery();ResultSetMetaData metaData = rs.getMetaData();//int colCount = metaData.getColumnCount();//获取列的数量while(rs.next()){/* * 把数据库查询出来的通过内省丢给Object */T obj = type.newInstance();BeanInfo beanInfo = Introspector.getBeanInfo(type);//obj的属性PropertyDescriptor[] properties = beanInfo.getPropertyDescriptors();//属性信息的数组for (int i = 0; i < colCount; i++) {String cloName = metaData.getColumnName(i+1);//获取到列名Object value = rs.getObject(i+1);//列的值/* * 遍历properties数组，找到一个名字和cloName一样的属性 */for(int j=0;j<properties.length;j++){PropertyDescriptor property = properties[j];String propertyName = property.getName();//属性名字Method method = property.getWriteMethod();//写方法if(propertyName.equals(cloName)){//属性名与数据表的列名一样，将值传给objmethod.invoke(obj, value);}}}list.add(obj);}} catch (ClassNotFoundException e) {// TODO Auto-generated catch blocke.printStackTrace();System.out.println("请正确配置驱动程序");} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();} catch (InstantiationException e) {// TODO Auto-generated catch blocke.printStackTrace();} catch (IllegalAccessException e) {// TODO Auto-generated catch blocke.printStackTrace();} catch (IntrospectionException e) {// TODO Auto-generated catch blocke.printStackTrace();} catch (IllegalArgumentException e) {// TODO Auto-generated catch blocke.printStackTrace();} catch (InvocationTargetException e) {// TODO Auto-generated catch blocke.printStackTrace();}finally {close(rs, ps, conn);}return list;}public static <T> T  selectObject(String sql,Object[] params,Class<T> type){List<T> list = select(sql, params,type);T resObj = null;if(list.size()>0){resObj=list.get(0);} return resObj;}/* * UPdate方法, 支持增,删,改 */public static int update(String sql, Object[] params){Connection conn = null;PreparedStatement ps = null;int res = 0;try {conn = getConn();ps = conn.prepareStatement(sql);if (params != null){for (int i=0; i<params.length; i++){ps.setObject(i+1, params[i]);}}res = ps.executeUpdate();} catch (SQLException e) {e.printStackTrace();} catch (ClassNotFoundException e) {// TODO Auto-generated catch blocke.printStackTrace();}finally {close(null, ps, conn);}return res;}}

测试类：MainTest.java

package com.hpe.test;import java.util.List;import org.junit.Test;import com.hpe.dao.IAdminDao;import com.hpe.dao.IUserDao;import com.hpe.dao.impl.AdminDaoImpl;import com.hpe.dao.impl.UserDaoImpl;import com.hpe.pojo.Admin;import com.hpe.pojo.User;public class MainTest {@Testpublic void user_login(){IUserDao iUserDao = new UserDaoImpl();User user = new User();user.setName("admin");user.setPwd("123");User resUser = iUserDao.login(user);if (resUser != null){System.out.println("登录成功");System.out.println(resUser);}else{System.out.println("登录失败");}}@Testpublic void selectAll(){IUserDao iUserDao = new UserDaoImpl();List<User> list = iUserDao.selectAll();for (User user : list) {System.out.println(user);}}@Testpublic void user_add(){IUserDao iUserDao = new UserDaoImpl();User user = new User();user.setName("mq");user.setPwd("111");int res = iUserDao.add(user);if(res == 1){System.out.println("添加成功");}else{System.out.println("添加失败");}}@Testpublic void admin_login(){IAdminDao iAdminDao = new AdminDaoImpl();Admin admin = new Admin();admin.setAdminname("admin");admin.setAdminpassword("123");Admin resAdmin = iAdminDao.login(admin);if(resAdmin !=null){System.out.println("管理员登录成功");System.out.println(resAdmin);}else {System.out.println("管理员登录失败");}}}