import java.io.IOException; import java.io.InputStream; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.util.Properties; public class DBUtils { private static String driverName; private static String url; private static String username; private static String password; static{ try { //读取配置文件的信息 Properties properties = new Properties(); //从资源路径中去获取配置属性 InputStream is = DBUtils.class.getClassLoader().getResourceAsStream("dbconfig.properties"); try { properties.load(is); driverName = properties.getProperty("driverName"); url = properties.getProperty("url"); username = properties.getProperty("username"); password = properties.getProperty("password"); } catch (IOException e) { e.printStackTrace(); } //1. 加载驱动 Class.forName(driverName); } catch (ClassNotFoundException e) { e.printStackTrace(); } } public static Connection getConnection(){ //获取连接对象 Connection connection = null; try { connection = DriverManager.getConnection(url, username, password); } catch (SQLException e) { e.printStackTrace(); } return connection; } public static void closeAll(AutoCloseable...cs){ if(cs != null){ //如果只传了一个null过来，则不用进行遍历 for(AutoCloseable c: cs){ if(c != null){ //如果多个参数中有一个为null，则不用关闭 try { c.close(); } catch (Exception e) { e.printStackTrace(); } } } } } }
//CommonUtils 公共的数据库操作工具类 import java.io.IOException; import java.lang.reflect.Field; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import java.util.Properties; //公共的数据库操作工具类--增删改 public class CommonUtils <T> { //公共类--增删改 public static int updateCommon(String sql,Object...objs){ //获取连接对象，再获取执行对象，操作添加 Connection conn = null; PreparedStatement prst = null; try { conn = DBUtils.getConnection(); prst = conn.prepareStatement(sql); //遍历传入的对象的多个字段 for(int i=0;i<objs.length;i++){ prst.setObject(i+1, objs[i]); } return prst.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); }finally{ DBUtils.closeAll(prst,conn); } return 0; } //公共类---查询---注意：要传一个反射对象进来 public List<T> queryCommon(String sql,Class<T> clazz,Object...objs){ Connection conn = null; PreparedStatement prst = null; ResultSet resultSet = null; List<T> list = new ArrayList<>(); try { conn = DBUtils.getConnection(); //获取连接对象 prst = conn.prepareStatement(sql); //遍历传入的对象的多个字段 for(int i=0;i<objs.length;i++){ prst.setObject(i+1, objs[i]); } resultSet = prst.executeQuery(); //获取到结果集 //---最终将结果集的信息存储到List集合中--- while(resultSet.next()){ //循环一次，就是一条记录（一个对象） //clazz.getFields(); //私有的属性获取不到 Field[] fields = clazz.getDeclaredFields(); T t = clazz.newInstance(); //通过反射对象获取到实体类对象 for(Field f: fields){ String fieldName = f.getName(); //得到属性名 //获取字段对应值 Object value = null; try { value = resultSet.getObject(fieldName); //如何确定获取的字段---对应上属性名 } catch (SQLException e) { //获取映射属性中的字段名（is_admin） Properties properties = new Properties(); try { properties.load(CommonUtils.class.getClassLoader().getResourceAsStream("mapping.properties")); } catch (IOException e1) { e1.printStackTrace(); } fieldName = properties.getProperty("isAdmin"); value = resultSet.getObject(fieldName); } f.setAccessible(true); //设置权限 f.set(t, value); //把值设置的对象的属性中 } list.add(t); //将对象存储到集合中 } } catch (SQLException | InstantiationException | IllegalAccessException e) { e.printStackTrace(); }finally { DBUtils.closeAll(resultSet,prst,conn); } return list; } //-------分页获取总条数------- public static int getTotalCount(String sql){ //获取连接对象，再获取执行对象，操作添加 Connection conn = null; PreparedStatement prst = null; ResultSet resultSet = null; try { conn = DBUtils.getConnection(); prst = conn.prepareStatement(sql); resultSet = prst.executeQuery(); if(resultSet.next()){ return resultSet.getInt(1); //获取第一个字段 } } catch (SQLException e) { e.printStackTrace(); }finally{ DBUtils.closeAll(resultSet,prst,conn); } return -1; } }
//Dao层示例 import java.util.List; import com.qf.dao.IUserDao; import com.qf.entity.User; import com.qf.utils.CommonUtils; public class UserDaoImpl extends CommonUtils<User> implements IUserDao { @Override public int addUser(User user) { String sql = "insert into t_user(name,password,sex,phone,email,is_admin) values(?,?,?,?,?,?)"; int result = CommonUtils.updateCommon(sql, user.getName(),user.getPassword(),user.getSex() ,user.getPhone(),user.getEmail(),user.getIsAdmin()); return result; } @Override public int updateUser(User user) { String sql="update t_user set name=?,password=?,sex=?,phone=?,email=?,is_admin=? where id=?"; int result = CommonUtils.updateCommon(sql, user.getName(),user.getPassword(),user.getSex() ,user.getPhone(),user.getEmail(),user.getIsAdmin(),user.getId()); return result; } @Override public int delUserById(Integer id) { String sql="delete from t_user where id=?"; return CommonUtils.updateCommon(sql, id); } @Override public List<User> getUserList() { String sql="SELECT id, NAME, PASSWORD, CASE WHEN sex = 1 THEN '男' WHEN sex = 0 THEN '女' END AS sex, email, phone, CASE WHEN is_admin = 1 THEN '是' WHEN is_admin = 0 THEN '否' END AS is_admin FROM t_user"; return super.queryCommon(sql, User.class); } @Override public User getUserById(Integer id) { String sql="select * from t_user where id = ?"; List<User> list = super.queryCommon(sql, User.class,id); if(list.size()>0){ //如果取到数据，则返回该对象 return list.get(0); } return null; //取不到数据，则返回null } @Override public int getTotalCount() { String sql="select count(1) from t_user"; return CommonUtils.getTotalCount(sql); } @Override public List<User> getUserListPage(Integer startIndex, Integer pageSize) { String sql="SELECT id, NAME, PASSWORD, CASE WHEN sex = 1 THEN '男' WHEN sex = 0 THEN '女' END AS sex, email, phone, CASE WHEN is_admin = 1 THEN '是' WHEN is_admin = 0 THEN '否' END AS is_admin FROM t_user limit ?,?"; return super.queryCommon(sql, User.class,startIndex,pageSize); } @Override //批量删除 public int batchDelIds(String[] ids) { StringBuilder builder = new StringBuilder("delete from t_user where id in ("); for(int i=0;i<ids.length;i++){ if(i==ids.length-1){ //最后一个则不用+“，”；要加“）” builder.append(ids[i]+")"); }else{ builder.append(ids[i]+","); } } String sql = builder.toString(); return CommonUtils.updateCommon(sql); } @Override //验证用户名和密码是否正确 public User getUserByBackLogin(String name, String password) { String sql="select * from t_user where name=? and password=?"; List<User> list = super.queryCommon(sql, User.class, name,password); if(list.size()>0){ return list.get(0); } return null; } }
