众所周知,编写JDBC语句是一件十分繁琐的事情,毕竟JDBC太多的重复操作,不免让人心生倦意,所以在没有使用Dao层框架框架之前,我们可以编写一个工具类来提高我们的开发效率。
以下代码纯个人编写,如果有好的建议,请告知与我,方便后期改善,谢谢!
工具类如下:
public class JDBCUtils {
/**
* 通用查询方法
* @param sql SQL语句
* @param entityClass 实体类类型
* @param a 参数数组
* @param <T>
* @return 返回值是一个集合
*/
public static <T> List<T> executeQurey(String sql, Class<T> entityClass,Object... a) {
try {
//MyConnectionPool是我的数据库连接池
Connection connection = MyConnectionPool.getConnection();
PreparedStatement prep = connection.prepareStatement(sql);
if (a.length > 0 && a[0].getClass() == entityClass)//如果传入的参数是实体类
ifParamEntity(sql, a[0], prep);
else {
for (int i = 0; i < a.length; ++i)
prep.setObject(i + 1, a[i]);
}
ResultSet resultSet = prep.executeQuery();
List<T> ts = processResultSet(resultSet, entityClass);
resultSet.close();
prep.close();
connection.close();
return ts;
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
/**
* 通用修改方法
* @param sql SQL语句
* @param a 参数数组
* @return
*/
public static int executeUpdate(String sql,Object... a){
try {
Connection connection = MyConnectionPool.getConnection();
PreparedStatement prep = connection.prepareStatement(sql);
if (!(a[0].getClass().isPrimitive() || a[0] instanceof Number || a[0] instanceof String))//如果传入的参数是实体类
ifParamEntity(sql, a[0], prep);
else {
for (int i = 0; i < a.length; ++i)
prep.setObject(i + 1, a[i]);
}
int i = prep.executeUpdate();
prep.close();
connection.close();
return i;
} catch (Exception e) {
e.printStackTrace();
}
return 0;
}
/**
* 判断传入参数是为实体类
* @param sql
* @param entity
* @param prep
* @param <T>
* @throws IllegalAccessException
* @throws SQLException
*/
private static <T> void ifParamEntity(String sql, T entity, PreparedStatement prep) throws IllegalAccessException, SQLException {
//使用正则表达式获取sql中的字段
Pattern compile = null;
String testSql = sql.toLowerCase();
if(sql.indexOf("insert") < 0 )
compile = Pattern.compile("[']?(\\w+)[']?\\s*=\\s*\\?");
else
compile = Pattern.compile("[']?(\\w+)[']?[,)]+");
Matcher matcher = compile.matcher(testSql);
List<String> db_fields = new ArrayList<>();
//数据库字段统一小写且去除下划线
while (matcher.find())
db_fields.add(matcher.group(1).replaceAll("_", "").trim());
//数据库字段与实体类字段匹配,并赋值
for (Field f : entity.getClass().getDeclaredFields())
if (db_fields.contains(f.getName().toLowerCase())) {
f.setAccessible(true);
prep.setObject(db_fields.indexOf(f.getName().toLowerCase())+1, f.get(entity));
}
}
/**
* 处理结果集
* @param rs
* @param entityClass
* @param <T>
* @return
* @throws SQLException
* @throws IllegalAccessException
* @throws InstantiationException
*/
private static <T> List<T> processResultSet(ResultSet rs,Class<T> entityClass ) throws SQLException, IllegalAccessException, InstantiationException {
List<T> list = null;
Map<String,Integer> db_fields_index = new HashMap<>();
while (rs != null && rs.next()){
if(list ==null){
list = new ArrayList<>();
for(int i = 1 ; i <= rs.getMetaData().getColumnCount(); ++i)
db_fields_index.put(rs.getMetaData().getColumnName(i).replaceAll("_","").toLowerCase(),i);
}
T t = entityClass.newInstance();
for(Field f : entityClass.getDeclaredFields())
if(db_fields_index.containsKey(f.getName().toLowerCase())){
f.setAccessible(true);
Object object = rs.getObject(db_fields_index.get(f.getName().toLowerCase()));
if(object instanceof Date)
object = object.toString();
else if(object instanceof BigDecimal){
if(f.getType() == Integer.class)
object = ((BigDecimal) object).intValue();
else if(f.getType() == Float.class)
object = ((BigDecimal) object).floatValue();
else if( f.getType() == Double.class)
object = ((BigDecimal) object).doubleValue();
}
f.set(t,object);
}
list.add(t);
}
return list;
}
}