通用的增删改查方法（反射）附带MySQL数据库连接

/**

*数据库连接类
* getConnection获取一个Connection对象，利用的是从文件中读取配置文件，此处这个配置文件在项目下，不在包下。
*getPreparedStatement(String sql)赋值前的preparedStatment
*setPreparedStatement(PreparedStatement statement, Object[] obj)赋值后的preparedStatment
*Close(Connection conn, Statement st, ResultSet rs)关闭资源
* @author wangxiaojun
*
*/

public class DButils {

private static Connection conn = null;

public static Connection getConnection(){
Properties ps = new Properties();
try {
FileInputStream fis = new FileInputStream("connection.properties");
ps.load(fis);
fis.close();
String driver = ps.getProperty("driver");
String url = ps.getProperty("url");
String user = ps.getProperty("user");
String password = ps.getProperty("password");
Class.forName(driver);
conn = DriverManager.getConnection(url, user, password);
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return conn;

}
//赋值前的preparedStatment
public static PreparedStatement getPreparedStatement(String sql) throws SQLException{
return getConnection().prepareStatement(sql);
}
//赋值后的preparedStatment
public static PreparedStatement setPreparedStatement(PreparedStatement statement, Object[] obj) throws SQLException{
for(int i=0; i<obj.length; i++){
statement.setObject(i+1, obj[i]);
}
return statement;
}

public static void Close(Connection conn, Statement st, ResultSet rs){
try {
if ((rs != null) && (!rs.isClosed())) {
rs.close();
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
try {
if ((st != null) && (!st.isClosed())) {
st.close();
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
try {
if ((conn != null) && (conn.isClosed())) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}

}

/**
* 这是一个定义操作方法的接口，如想要实现下面的操作，就得实现
* @author Administrator
*T可以包含任何数据类型
* @param <T>
*/

public interface AbstractBaseDao<T> {
public int add(T t);
public int delete(int id);
public int update(T t);
public List<T> getAll();
public T getOne(int id);
}

/**
* 实现上面的接口中的方法增删改查（反射）
* @author Administrator
*
* @param <T>
*/

public class BaseDao<T> implements AbstractBaseDao<T>{

private Class<T> entityClass;//声明大的Class

/** 操作常量操作类型 */
public static final String SQL_INSERT = "insert";
public static final String SQL_UPDATE = "update";
public static final String SQL_DELETE = "delete";
public static final String SQL_GET = "get";
public static final String SQL_GETALL = "getAll";

private PreparedStatement statement;

private String sql; //sql语句
private Object argType[];//实参的Object数组
private ResultSet rs;//结果集
private int ret = 0;//executeUpdate()操作返回值
//拿到T的实际的类型entityClass，T.class
@SuppressWarnings("unchecked")
public BaseDao() {
super();
//ParameterizedType是用来的到类的模板参数的类型的
//getGenericSuperclass,返回表示此 Class 所表示的实体（类、接口、基本类型或 void）的直接超类的 Type。
//getActualTypeArguments,返回表示此类型实际类型参数的 Type 对象的数组
ParameterizedType type= (ParameterizedType) this.getClass().getGenericSuperclass();
this.entityClass=(Class<T>) type.getActualTypeArguments()[0];
}

@Override
public int add(T t) {
//拿到insert的sql
sql=this.getSql(SQL_INSERT);
try {
//argType是实参的Object数组
argType=setArgs(t,SQL_INSERT);
//实例化PreparedStatement.
statement=DButils.getPreparedStatement(sql);
//为sql语句赋值.
statement=DButils.setPreparedStatement(statement, argType);
ret = statement.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
}finally{
DButils.Close(null, statement, null);
}
return ret;
}

@Override
public int delete(int id) {
//拿到insert的sql
sql=this.getSql(SQL_DELETE);
try {
//argType=setArgs(t,SQL_DELETE);
argType=new Object[]{id};
//实例化PreparedStatement.
statement=DButils.getPreparedStatement(sql);
//为sql语句赋值.
statement=DButils.setPreparedStatement(statement, argType);
ret = statement.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
}finally{
DButils.Close(null, statement, null);
}
return ret;
}

@Override
public int update(T t) {

//拿到insert的sql
sql=this.getSql(SQL_UPDATE);
try {
argType=setArgs(t,SQL_UPDATE);
//实例化PreparedStatement.
statement=DButils.getPreparedStatement(sql);
//为sql语句赋值.
statement=DButils.setPreparedStatement(statement, argType);
statement.executeUpdate();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
DButils.Close(null, statement, null);
}
return ret;

}

@Override
public List<T> getAll() {
sql = this.getSql(SQL_GETALL);
List<T> list = new ArrayList<T>();
try {
//argType = setArgs(t, SQL_GETALL);
statement = DButils.getPreparedStatement(sql);
//statement = DBUtils.setPreparedStatementParam(statement,argType);
rs = statement.executeQuery();
Field fields[] = entityClass.getDeclaredFields();
while (rs.next()) {
T obj = entityClass.newInstance();
for (int i = 0; i < fields.length; i++) {
fields[i].setAccessible(true);
fields[i].set(obj, rs.getObject(fields[i].getName()));
}
list.add(obj);
}
} catch (Exception e) {
e.printStackTrace();
}
return list;
}

@Override
public T getOne(int id) {
sql = this.getSql(SQL_GET);
T obj = null;
try {
argType = new Object[]{id};
statement = DButils.getPreparedStatement(sql);
statement = DButils.setPreparedStatement(statement,argType);
rs = statement.executeQuery();
Field fields[] = entityClass.getDeclaredFields();
while (rs.next()) {
obj = entityClass.newInstance();//创建对象
for (int i = 0; i < fields.length; i++) {
fields[i].setAccessible(true);
fields[i].set(obj, rs.getObject(fields[i].getName()));
}
}
} catch (Exception e) {
e.printStackTrace();
}
return obj;
}

// sql拼接函数形如 : insert into User(id,username,password,gender,createdatetime) values(?,?,?,?,?)
private String getSql(String operator){//参数是选项（insert、、、）
StringBuffer sql=new StringBuffer();//用来拼接sql语句

Field[] fields=this.entityClass.getDeclaredFields();//包括类的所有属性
if(operator.equals(SQL_INSERT)){
//insert into User
sql.append("insert into ").append(this.entityClass.getSimpleName());//这里的反射返回表名
sql.append("(");
//System.out.println(this.entityClass.getSimpleName());
for(int i=0;fields!=null && i<fields.length;i++){
fields[i].setAccessible(true);//这句话必须要有,否则会抛出异常。这是获取权限，否则private属性拿不到
String column=fields[i].getName();//获取属性的名称
//System.out.println(column);
sql.append(column).append(",");
}
//sql = insert into Users(id,username,password,gender,createDateTime,
//删除最后一个字符“,”
sql = sql.deleteCharAt(sql.length() - 1);
sql.append(") values(");
//sql = insert into Users(id,username,password,gender,createDateTime) values(
for(int i=0;fields!=null && i<fields.length;i++){
sql.append("?,");
}
sql = sql.deleteCharAt(sql.length() - 1);
sql.append(")");
//insert into Users(id,username,password,gender,createDateTime) values(?,?,?,?,?)
//update User set username=?,password=?,gender=?,createdatetime=? where id=?
}else if(operator.equals(SQL_UPDATE)){
sql.append("update ").append(this.entityClass.getSimpleName());
sql.append(" set ");
for(int i=0;fields!=null && i<fields.length;i++){
fields[i].setAccessible(true);
String column=fields[i].getName();
if(column.equals("id")){
continue;
}
sql.append(column).append("=?,");
}
sql = sql.deleteCharAt(sql.length() - 1);
sql.append(" where id=?");
}else if(operator.equals(SQL_DELETE)){
sql.append("delete from ").append(this.entityClass.getSimpleName());
sql.append(" where id=?");
}else if(operator.equals(SQL_GET)){
sql.append("select * from ").append(this.entityClass.getSimpleName());
sql.append(" where id=?");
}else if(operator.equals(SQL_GETALL)){
sql.append("select * from ").append(this.entityClass.getSimpleName());
}
System.out.println(sql.toString());
return sql.toString();
}
//获取参数，argType中没有具体的值，设置数组中各个元素
private Object[] setArgs(T entity,String operator) throws IllegalArgumentException, IllegalAccessException{
Field fields[] = entityClass.getDeclaredFields();
//for(Object obj:fields){
// System.out.println(obj);
//}

if (operator.equals(SQL_INSERT)) {
Object[] obj = new Object[fields.length];//5个
for (int i = 0; obj != null && i < fields.length; i++) {
fields[i].setAccessible(true);
obj[i] = fields[i].get(entity);// 返回指定对象(entity)上此 Field 表示的字段的值
//System.out.println(obj[i]);
}
return obj;//拿到一个数组，数组中存放具体的参数值
} else if (operator.equals(SQL_UPDATE)) {
Object[] tempObj = new Object[fields.length];
for (int i = 0; tempObj != null && i < fields.length; i++) {
fields[i].setAccessible(true);
tempObj[i] = fields[i].get(entity);// 返回指定对象上此 Field 表示的字段的值
}
Object[] obj = new Object[fields.length];
//System.arraycopy(src, srcPos, dest, destPos, length);
System.arraycopy(tempObj, 1, obj, 0, tempObj.length - 1);
obj[obj.length - 1] = tempObj[0];
return obj;
} else if (operator.equals(SQL_DELETE) || operator.equals(SQL_GET)) {
Object[] obj = new Object[1];
fields[0].setAccessible(true);
obj[0] = fields[0].get(entity);
return obj;
}
return null;
}
}

本人水平有限，如有错误或纰漏，望不吝指教。共同学习

更多相关文章

随机推荐