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;
}
}