ORM工具类
package per.nonoas.orm;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
@SuppressWarnings("unused")
public abstract class MyOrmUtil<T> {
final protected T selectOne(String sql, Object... params) {
T t = null;
PreparedStatement ps = null;
ResultSet rs;
try {
ps = getGeneralPreparedStatement(sql, params);
rs = ps.executeQuery();
if (rs.next())
t = mapToBean(rs, getBeanClass());
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
Connection c = null;
if (ps != null) {
c = ps.getConnection();
ps.close();
}
if (c != null)
c.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return t;
}
final protected List<T> select(String sql, Object... params) {
List<T> list = new ArrayList<>(8);
PreparedStatement ps = null;
ResultSet rs = null;
try {
ps = getGeneralPreparedStatement(sql, params);
rs = ps.executeQuery();
while (rs.next())
list.add(mapToBean(rs, getBeanClass()));
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (ps != null)
ps.close();
if (rs != null)
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (list.size() == 0)
return null;
return list;
}
final protected int insert(String sql, T t) {
PreparedStatement ps = getPrepareStatement(sql, t);
if (ps != null) {
try {
int res = ps.executeUpdate();
} catch (SQLException throwable) {
throwable.printStackTrace();
}
}
return 0;
}
final protected int update(String sql, T t) {
PreparedStatement ps = getPrepareStatement(sql, t);
if (ps != null) {
try {
return ps.executeUpdate();
} catch (SQLException throwable) {
throwable.printStackTrace();
}
}
return 0;
}
final protected boolean delete(String sql, T t) {
try {
return execute(sql, t);
} catch (SQLException throwable) {
throwable.printStackTrace();
}
return false;
}
final protected void executeBatch(String sql, List<T> ts) {
List<String> list = getParams(sql);
sql = getSql(sql);
try (Connection con = getConnection(); PreparedStatement ps = con.prepareStatement(sql)) {
for (T t : ts) {
Class<?> beanClass = t.getClass();
for (int i = 0; i < list.size(); i++) {
String colName = list.get(i);
String methodName = "get" + underlineToBigCamel(colName);
Method method = beanClass.getDeclaredMethod(methodName);
Object value = method.invoke(t);
ps.setObject(i + 1, value);
}
ps.addBatch();
}
ps.executeBatch();
} catch (SQLException | NoSuchMethodException | SecurityException | IllegalAccessException | IllegalArgumentException | InvocationTargetException e) {
e.printStackTrace();
}
}
final protected boolean executeTransaction(List<AbstractTransaction> transactions) {
boolean flag = true;
Connection conn = getConnection();
try {
conn.setAutoCommit(false);
} catch (SQLException e) {
e.printStackTrace();
}
try {
for (AbstractTransaction transaction : transactions) {
if (transaction instanceof BeanTransaction) {
Object bean = transaction.getParams();
String sql = transaction.getSQL();
Class<?> beanClass = bean.getClass();
List<String> paramNames = getParams(sql);
sql = getSql(sql);
PreparedStatement ps = conn.prepareStatement(sql);
for (int i = 0; i < paramNames.size(); i++) {
String colName = paramNames.get(i);
String methodName = "get" + underlineToBigCamel(colName);
Method method = beanClass.getDeclaredMethod(methodName);
Object value = method.invoke(bean);
ps.setObject(i + 1, value);
}
ps.execute();
} else {
String sql = getSql(transaction.getSQL());
Object[] params = (Object[]) transaction.getParams();
PreparedStatement ps = conn.prepareStatement(getSql(sql));
for (int i = 0; i < params.length; i++) {
ps.setObject(i + 1, params[i]);
}
ps.execute();
}
}
} catch (InvocationTargetException | NoSuchMethodException | SQLException | IllegalAccessException e) {
e.printStackTrace();
try {
conn.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
flag = false;
} finally {
try {
conn.commit();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return flag;
}
final protected boolean execute(String sql, Object... params) throws SQLException {
PreparedStatement ps = getGeneralPreparedStatement(sql, params);
return ps.execute();
}
private boolean execute(String sql, T t) throws SQLException {
PreparedStatement ps = getPrepareStatement(sql, t);
if (ps != null) {
return ps.execute();
}
return false;
}
private PreparedStatement getGeneralPreparedStatement(String sql, Object... params) {
Connection conn = getConnection();
PreparedStatement ps = null;
try {
ps = conn.prepareStatement(getSql(sql));
for (int i = 0; i < params.length; i++) {
ps.setObject(i + 1, params[i]);
}
} catch (SQLException e) {
e.printStackTrace();
}
return ps;
}
private PreparedStatement getPrepareStatement(String sql, T t) {
Class<?> beanClass = t.getClass();
List<String> list = getParams(sql);
sql = getSql(sql);
Connection con = getConnection();
PreparedStatement ps = null;
try {
ps = con.prepareStatement(sql);
for (int i = 0; i < list.size(); i++) {
String colName = list.get(i);
String methodName = "get" + underlineToBigCamel(colName);
Method method = beanClass.getDeclaredMethod(methodName);
Object value = method.invoke(t);
ps.setObject(i + 1, value);
}
} catch (SQLException | NoSuchMethodException | SecurityException | IllegalAccessException | IllegalArgumentException | InvocationTargetException e) {
e.printStackTrace();
}
return ps;
}
private T mapToBean(ResultSet rs, Class<T> beanClass) {
List<String> list = getColumnNames(rs);
T t = null;
try {
t = beanClass.newInstance();
for (String colName : list) {
String methodName = "set" + underlineToBigCamel(colName);
Field field = beanClass.getDeclaredField(underlineToCamel(colName));
Class<?> type = field.getType();
Object value = rs.getObject(colName);
Method method = beanClass.getDeclaredMethod(methodName, type);
if (value != "" && value != null) {
if (value.getClass().getSimpleName().equals("BigDecimal")
&& type.toString().equals("class java.lang.Integer")) {
value = Integer.parseInt(value.toString());
}
} else {
field.getType().cast(value);
}
method.invoke(t, value);
}
} catch (InstantiationException | IllegalAccessException | NoSuchFieldException | SecurityException | SQLException | NoSuchMethodException | IllegalArgumentException | InvocationTargetException e) {
e.printStackTrace();
}
return t;
}
private List<String> getColumnNames(ResultSet rs) {
List<String> list = new ArrayList<>(8);
ResultSetMetaData rsm;
int columnCount;
try {
rsm = rs.getMetaData();
columnCount = rsm.getColumnCount();
for (int i = 0; i < columnCount; i++) {
list.add(rsm.getColumnName(i + 1));
}
} catch (SQLException e) {
e.printStackTrace();
}
return list;
}
private List<String> getParams(String sql) {
List<String> list = new ArrayList<>(8);
Pattern p = Pattern.compile("[#]\\{\\w*[}]");
Matcher m = p.matcher(sql);
while (m.find()) {
String str = m.group();
String column = str.substring(2, m.group().length() - 1);
list.add(column);
}
return list;
}
private String underlineToBigCamel(String str) {
String[] strs = str.split("_");
StringBuilder sb = new StringBuilder();
for (int i = 0; i < strs.length; i++) {
strs[i] = strs[i].substring(0, 1).toUpperCase() + strs[i].substring(1);
sb.append(strs[i]);
}
return sb.toString();
}
private String underlineToCamel(String str) {
String[] strs = str.split("_");
StringBuilder sb = new StringBuilder(strs[0]);
for (int i = 1; i < strs.length; i++) {
strs[i] = strs[i].substring(0, 1).toUpperCase() + strs[i].substring(1);
sb.append(strs[i]);
}
return sb.toString();
}
private String getSql(String sql) {
return sql.replaceAll(("[#]\\{\\w*[}]"), "?");
}
protected abstract Connection getConnection();
protected abstract Class<T> getBeanClass();
}
使用案例
package indi.nonoas.crm.dao;
import java.util.ArrayList;
import indi.nonoas.crm.bean.GoodsBean;
public class GoodsDao extends MyDao<GoodsBean> {
private static final String SELECT_ALL = "select * from goods_info";
private static final String SELECT_BY_FILTRATE = "select * from goods_info where (id like #{id} or name like #{name}) and type like #{type}";
private static final String INSERT_INFO = "insert into goods_info(id,name,sell_price,purchas_price,quantity,min_discount,deduction,deduction_rate,base_unit,type,photo) "
+ "values(#{id},#{name},#{sell_price},#{purchas_price},#{quantity},#{min_discount},#{deduction},#{deduction_rate},#{base_unit},#{type},#{photo})";
private static final String DELETE_BY_ID = "delete from goods_info where id=#{id}";
private static final String UPDATE = "update goods_info set name=#{name},sell_price=#{sell_price},purchas_price=#{purchas_price},quantity=#{quantity},min_discount=#{min_discount},deduction=#{deduction},deduction_rate=#{deduction_rate},base_unit=#{base_unit},type=#{type},photo=#{photo} where id=#{id}";
private static final GoodsDao INSTANCE = new GoodsDao();
private GoodsDao() {
}
public static GoodsDao getInstence() {
return INSTANCE;
}
public ArrayList<GoodsBean> selectAll() {
ArrayList<GoodsBean> list = null;
list = (ArrayList<GoodsBean>) select(SELECT_ALL);
return list;
}
public ArrayList<GoodsBean> selectByFiltrate(String id, String name, String type) {
ArrayList<GoodsBean> list = null;
list = (ArrayList<GoodsBean>) select(SELECT_BY_FILTRATE, id, name, type);
return list;
}
public void deleteByID(GoodsBean bean) {
delete(DELETE_BY_ID, bean);
}
public void insertInfo(GoodsBean bean) {
insert(INSERT_INFO, bean);
}
public void update(GoodsBean goodsBean) {
update(UPDATE, goodsBean);
}
@Override
protected Class<GoodsBean> getBeanClass() {
return GoodsBean.class;
}
}