็ปงๆฟ Apache DBUtils ็ BasicRowProcessor
*/
public abstract class BaseDao<T extends BaseDBObject, K> extends BasicRowProcessor {
public static final Logger log = Logger.getLogger(BaseDao.class.getName());
// Apache DBUtils ้ป่ฎคไธป้ฎID่ทๅ็ Handler (ScalarHandler)
public final ResultSetHandler DEFAULT_PRIMARY_KEY_HANDLER = new ScalarHandler();
// Apache DBUtils ้่ฆ็ Bean ่ฝฌๆข็ฑป๏ผๅ่Apache DBUtils่ฏดๆ
private BeanListHandler beanListHandler;
// Apache DBUtils ๅๅผๆฅ่ฏข Handler
public final ResultSetHandler SINGLE_VALUE_HANDLER = new ScalarHandler<>(1);
// beanListHandler็ๅฏน่ฑก้๏ผ็จไบ้ฆๆฌก็ๆๅฏน่ฑกไฟๆค้
private Object lock = new Object();
// ็ปงๆฟๅๅฎ็ฐBasicRowProcessor็็ฑป๏ผๅ่Apache DBUtils่ฏดๆ
@OverRide
public TT toBean(ResultSet rs, Class type) throws SQLException {
T t = null;
try {
t = (T) getClazz().newInstance();
} catch (InstantiationException ex) {
log.error("<toBean> catch exception=" + ex.toString(), ex);
throw new RuntimeException(ex);
} catch (IllegalAccessException ex) {
log.error("<toBean> catch exception=" + ex.toString(), ex);
throw new RuntimeException(ex);
}
// ่ฐ็จๆจกๆฟ็ฑป็ toBean ๆนๆณๆฅๅฎ็ฐ JDBC ๆฅ่ฏข็ปๆ้ๅๅฐ ๆฐๆฎๅฏน่ฑก็่ฝฌๆข
t.toBean(rs, t.getData());
return (TT) t;
}
// ็ปงๆฟๅๅฎ็ฐBasicRowProcessor็็ฑป๏ผๅ่Apache DBUtils่ฏดๆ
@OverRide
public List toBeanList(ResultSet rs, Class type) {
try {
List newlist = new LinkedList();
while (rs.next()) {
newlist.add(toBean(rs, type));
}
return newlist;
} catch (SQLException ex) {
log.error(" catch exception=" + ex.toString(), ex);
throw new RuntimeException(ex);
}
}
// ๆ ๅฐ็ๆฐๆฎๅบ่กจๅ
public abstract String getTableName();
// ๅฏน่ฑก็ฑป
public abstract Class getClazz();
// ๆฐๆฎๅญๅจๅฏน่ฑกไธป้ฎๅ็งฐ
abstract public String getPrimaryKeyFieldName();
// ๆฐๆฎๅบๅญๅจๅฏน่ฑกไธป้ฎ๏ผไปๅญ็ฌฆไธฒๅผ่ฝฌๆขไธบๅฎ้
ๅฏน่ฑก๏ผๅฆInteger, Long๏ผ
abstract public K getPrimaryKeyFieldValue(String id);
// ่ทๅBeanListHandler๏ผ็จไบDBUtilsๆนๆณๆไฝ๏ผๅ่Apache DBUtils่ฏดๆ
public BeanListHandler getBeanListHandler() {
synchronized (lock) {
if (beanListHandler == null) {
beanListHandler = new BeanListHandler(getClazz(), this);
}
return beanListHandler;
}
}
// ๆ นๆฎ็ปๅฎ็ฑปๆ้ ๅฏน่ฑก
protected T newClassInstance(Class clazz) {
try {
return clazz.newInstance();
} catch (InstantiationException e) {
log.error(" please implement the default constructor for " + clazz.getSimpleName() + "!!! ", e);
return null;
} catch (IllegalAccessException e) {
log.error(" catch exception" + e.toString(), e);
return null;
}
}
// ๆทปๅ ๆๅ
ฅๅญๆฎตๅๅญๆฎตๅฏนๅบ็ๅฏน่ฑกๅผ๏ผๅฏน่ฑกๅผๅฟ
้กปๆฏ JDBC SQL ๆฏๆ็ๅฏน่ฑกๅผ
protected void addInsertField(String fieldType, Object fieldValue, List fieldList, List paramsList) {
if (fieldValue == null){
return;
}
fieldList.add(fieldType);
paramsList.add(fieldValue);
}
//
protected String buildBatchInsertValuesSQL(Object[][] paramsArrays) {
if (paramsArrays == null || paramsArrays.length == 0 || paramsArrays[0].length == 0) {
return "";
}
//ๆ้ ็ฌฌไธ่ก็๏ผๅทๅๆฐ
List<String> fList = new ArrayList<>();
for (int i = 0; i < paramsArrays[0].length; i++) {
if (paramsArrays[0][i] instanceof PGobject) {
fList.add("?::jsonb");
} else {
fList.add("?");
}
}
String fiParamsString = "(" + String.join(",", fList) + ")";
//ๆ้ ๆปๅๆฐ
// List allList = new ArrayList<>();
// for (int i = 0; i < paramsArrays.length; i++) {
// allList.add(fiParamsString);
// }
// String allParamsString = String.join(",", allList);
}
public K batchInsert(J[] pbObjects, OldAddFieldsInsertBatchInterface fieldsInterface, AddValuesInsertBatchInterface valuesInterface) throws SQLException {
if (pbObjects == null || pbObjects.length == 0) {
return null;
}
// queryRunner.batch
// Objects
// queryRunner.insertBatch()
List fieldsList = new ArrayList<>();
//่ฐ็จๆถๅบ่ฏฅๅจๅฎ็ฐไธญๅคๆฌก่ฐ็จaddInsertBatchField(fieldList,่ฆ็ฝฎๅ
ฅfieldList็ๅผ)๏ผไธvaluesInterface.addFValuesๅฏนๅบ
fieldsInterface.addFields(fieldsList);
if (fieldsList.size() == 0) {
return null;
}
//่ทๅไบ็ปดๆฐ็ปๅๆฐ
Object[][] paramsArrays = new Object[pbObjects.length][];
for (int i = 0; i < pbObjects.length; i++) {
List<Object> paramsList = new ArrayList<>();
J pbObject = pbObjects[i];
//ๅ
้จๆฅๅฃๅฎ็ฐๅบ่ฏฅๅคๆฌก่ฐ็จparamsList.add(pbObject.XXX),ไธfieldsInterface.addFieldsๅฏนๅบ
valuesInterface.addFValues(paramsList, pbObject);
if (paramsList.size() == 0) {
return null;
}
paramsArrays[i] = paramsList.toArray();
}
if (paramsArrays == null || paramsArrays.length == 0 || paramsArrays[0].length == 0) {
return null;
}
log.info("<batchInsert > ๅๆฐๆฐ้๏ผ" + fieldsList.size());
log.info("<batchInsert > ๅผ็ๆฐ้๏ผ" + paramsArrays[0].length);
if (fieldsList.size() != paramsArrays[0].length) {
log.error("<batchInsert > ๅๆฐไธๅๆฐๅผ็ๆฐ้ไธๅฏนๅบ,ๆๅ
ฅๅคฑ่ดฅ๏ผ");
return null;
}
//ๆ้ ไบ็ปดๆฐ็ปๅๆฐ็๏ผๅทๅ่กจ
String valuesString = buildBatchInsertValuesSQL(paramsArrays);
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.append("INSERT INTO " + getTableName());
sqlBuilder.append(" (");
sqlBuilder.append(buildInsertFieldsSQL(fieldsList));
sqlBuilder.append(" )");
sqlBuilder.append(" VALUES ");
sqlBuilder.append(valuesString);
String sql = sqlBuilder.toString();
//่พๅบๆฅๅฟ
log.info("<batchInsert > spl=" + sql);
for (int i = 0; i < paramsArrays.length; i++) {
StringBuilder sb = new StringBuilder();
for (int j = 0; j < paramsArrays[0].length; j++) {
sb.append(fieldsList.get(j) + "=" + paramsArrays[i][j] + " ");
}
log.info("<batchInsert > ็ฌฌ" + i + "ๆฐๆฎไธบ\t" + sb.toString());
}
//ๅผๅงๅๅคๆๅ
ฅๆฐๆฎ
// init query runner
QueryRunner queryRunner = new QueryRunner();
// get connection from transaction
Connection connection = TransactionManager.getConnection();
K k = queryRunner.insertBatch(connection, sql, DEFAULT_PRIMARY_KEY_HANDLER, paramsArrays);
log.info("<batchInsert > ่ตทๅงid=" + k);
log.info("<batchInsert > ๆๅ
ฅๆกๆฐ=" + pbObjects.length);
return k;
}
@FunctionalInterface
public static interface OldAddFieldsInsertBatchInterface {
public void addFields(List<String> fieldList) throws SQLException;
}
@FunctionalInterface
public static interface AddValuesInsertBatchInterface {
public void addFValues(List pbObjectList, J pbObject) throws SQLException;
}
// ๆๅ
ฅไธๆก่ฎฐๅฝ
public K insert(InsertSQLInterface insertSQLInterface) throws SQLException {
// init query runner
QueryRunner queryRunner = new QueryRunner();
// get connection from transaction
Connection connection = TransactionManager.getConnection();
List<String> fieldsList = new ArrayList<>();
List<Object> paramsList = new ArrayList<>();
insertSQLInterface.addInsertFieldValues(connection, fieldsList, paramsList);
if (fieldsList.size() == 0) {
// nothing to insert
return null;
}
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.append("INSERT INTO " + getTableName());
sqlBuilder.append(" (");
sqlBuilder.append(buildInsertFieldsSQL(fieldsList));
sqlBuilder.append(" )");
sqlBuilder.append(" VALUES (");
sqlBuilder.append(buildInsertValuesSQL(paramsList));
sqlBuilder.append(" )");
String sql = sqlBuilder.toString();
log.info("<insert> sql=" + sql + " , params=" + paramsList);
// init date field
K seqId = queryRunner.insert(connection, sql,
DEFAULT_PRIMARY_KEY_HANDLER,
paramsList.toArray());
log.info("<insert> " + getTableName() + ", return id=" + seqId);
return seqId;
}
// ๆ้ ๆๅ
ฅ็ๅญๆฎต็ VALUES ็ฉบไฝ ? ๅ่กจ
private String buildInsertValuesSQL(List paramsList) {
if (paramsList.size() == 0) {
return "";
}
List<String> list = new ArrayList<>();
for (int i = 0; i < paramsList.size(); i++) {
if (paramsList.get(i) instanceof PGobject) {
list.add("?::jsonb");
} else {
list.add("?");
}
}
return String.join(", ", list);
}
// ๆ้ ๆๅ
ฅ็ๅญๆฎตๅ่กจ
private String buildInsertFieldsSQL(List fieldsList) {
if (fieldsList.size() == 0) {
return "";
}
return String.join(", ", fieldsList);
}
// ๆ นๆฎๆฅ่ฏขๆกไปถ่ฟๅๅ่กจ
public List queryByCondition(String whereString, int offset, int limit, String[] returnFields, Object... params) throws SQLException {
// init query runner
QueryRunner queryRunner = new QueryRunner();
// get connection from transaction
Connection connection = TransactionManager.getConnection();
List<String> whereSQL = new ArrayList<>();
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.append("SELECT ");
sqlBuilder.append(buildSelectFields(returnFields));
sqlBuilder.append(" FROM " + getTableName() + " ");
sqlBuilder.append(whereString);
sqlBuilder.append(buildOffsetLimit(offset, limit));
// execute update
String sql = sqlBuilder.toString();
// execute query
log.info("<query> " + getTableName() + " " + sql + " , params=" + params);
List<T> list = queryRunner.query(connection, sql, getBeanListHandler(), params);
// handler return
if (list == null || list.size() == 0) {
log.info("<query> " + getTableName() + " but return no result");
return Collections.emptyList();
}
log.info("<query> " + getTableName() + " total " + list.size() + " returns");
return list;
}
// ๆ้ ่ฟๅ็่ฎฐๅฝๆฐ็ฎๅๅ็งป
private String buildOffsetLimit(int offset, int limit) {
String sql = "";
if (limit > 0) {
sql += " LIMIT " + limit;
}
if (offset > 0) {
sql += " OFFSET " + offset;
}
return sql;
}
// ๆ นๆฎ็ปๅฎ SQL ๅไผ ๅ
ฅๅๆฐๅผ๏ผๆง่กๆฅ่ฏข๏ผJDBC ๆ ๅ๏ผๅบไบApache DBUtils็ QueryRunner ๅฎ็ฐ
public List query(String sql, int offset, int limit, Object... params) throws SQLException {
// init query runner
QueryRunner queryRunner = new QueryRunner();
// get connection from transaction
Connection connection = TransactionManager.getConnection();
if (limit > 0) {
sql += " LIMIT " + limit;
}
if (offset > 0) {
sql += " OFFSET " + offset;
}
log.info("<query> " + getTableName() + " sql=" + sql + " , params=" + params);
// execute query
List<T> list = queryRunner.query(connection, sql, getBeanListHandler(), params);
// handler return
if (list == null) {
log.info("<query> " + getTableName() + " but return list null");
return Collections.emptyList();
}
log.info("<query> " + getTableName() + " total " + list.size() + " returns");
return list;
}
// ๆ นๆฎ็ปๅฎ็ๅญๆฎต๏ผๆ้ ่ฟๅ็ SELECT ๅญๆฎต็ๅญ็ฌฆไธฒ
// ๅฆๆๆช็ฉบๅ่ฟๅ * ๏ผSELECT ๏ผ
// ็คบไพ๏ผๅฆๆๆฏ ["name", "address"] ๅ่ฟๅ name,address
public String buildSelectFields(String[] returnFields) {
if (returnFields == null || returnFields.length == 0) {
return "";
} else {
String fields = String.join(", ", returnFields);
return fields;
}
}
// ๆ นๆฎๆไพ็ where ๆฅ่ฏขๆกไปถๆฅ่ฏข๏ผๅช่ฟๅ็ฌฌไธๆก่ฎฐๅฝ
public T queryByCondition(String whereString, String[] returnFields, Object... params) throws SQLException {
// init query runner
QueryRunner queryRunner = new QueryRunner();
// get connection from transaction
Connection connection = TransactionManager.getConnection();
List<Object> paramsList = new ArrayList<>();
//ๅขๅ ไธๅค๏ผไนๅparamsๅฏน่ฑกไธบ็ฉบ๏ผๆฅ้
for (Object oj : params) {
paramsList.add(oj);
}
List<String> whereSQL = new ArrayList<>();
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.append("SELECT ");
sqlBuilder.append(buildSelectFields(returnFields));
sqlBuilder.append(" FROM " + getTableName() + " ");
sqlBuilder.append(whereString);
sqlBuilder.append(" LIMIT 1");
// execute update
String sql = sqlBuilder.toString();
// execute query
log.info("<query> " + getTableName() + " " + sql + " , params size=" + paramsList.size() + ", params = " + paramsList);
List<T> list = queryRunner.query(connection, sql, getBeanListHandler(), paramsList.toArray());
// handler return
if (list == null || list.size() == 0) {
log.info("<query> " + getTableName() + " but return no result");
return null;
}
log.info("<query> " + getTableName() + " result=" + list.get(0));
return list.get(0);
}
// ๆ นๆฎๅญ็ฌฆไธฒ PRIMARY KEY ID ๆฅ่ฏข
public T queryById(String id, String... returnFields) throws SQLException {
// init query runner
QueryRunner queryRunner = new QueryRunner();
// get connection from transaction
Connection connection = TransactionManager.getConnection();
List<Object> paramsList = new ArrayList<>();
List<String> whereSQL = new ArrayList<>();
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.append("SELECT ");
sqlBuilder.append(buildSelectFields(returnFields)); // ๆๅฎ่ฟๅๅญๆฎต
sqlBuilder.append(" FROM " + getTableName());
// add where condition
addWhereValue(whereSQL, paramsList, getPrimaryKeyFieldName(), getPrimaryKeyFieldValue(id));
// add where string
if (whereSQL.size() > 0) {
sqlBuilder.append(" WHERE ");
sqlBuilder.append(buildWhereAndSQL(whereSQL));
sqlBuilder.append(" LIMIT 1");
} else {
// avoid select ALL records...
return null;
}
// execute update
String sql = sqlBuilder.toString();
// execute query
log.info("<query> " + getTableName() + " " + sql + " , params=" + paramsList);
List<T> list = queryRunner.query(connection, sql, getBeanListHandler(), paramsList.toArray());
// handler return
if (list == null || list.size() == 0) {
log.info("<query> " + getTableName() + " but return no result");
return null;
}
log.info("<query> " + getTableName() + " result=" + list.get(0));
return list.get(0);
}
// ๆ นๆฎ็ปๅฎ PRIMARY KEY ID ๅ่กจๆฅ่ฏข๏ผ่ฟๅ Map
public Map<K, T> queryMapByIdList(Collection idList) throws SQLException {
if (idList == null || idList.size() == 0) {
log.warn("<queryByIdList> " + getTableName() + " empty id list");
return Collections.emptyMap();
}
// init query runner
QueryRunner queryRunner = new QueryRunner();
// get connection from transaction
Connection connection = TransactionManager.getConnection();
List<Object> paramsList = new ArrayList<>();
List<String> whereSQL = new ArrayList<>();
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.append("SELECT ");
sqlBuilder.append("*");
sqlBuilder.append(" FROM " + getTableName());
// add where condition
// add where string
if (idList.size() > 0) {
sqlBuilder.append(" WHERE " + getPrimaryKeyFieldName() + " IN (");
List<String> stringValues = new ArrayList<>();
for (K id : idList) {
stringValues.add("?");
paramsList.add(id);
}
// ็ๆๅๆผๆฅ ?,?,?, ....
sqlBuilder.append(String.join(",", stringValues));
sqlBuilder.append(")");
} else {
// avoid select ALL records...
return Collections.emptyMap();
}
// execute update
String sql = sqlBuilder.toString();
// execute query
log.info("<queryByIdList> " + getTableName() + " " + sql + " , params=" + paramsList);
List<T> list = queryRunner.query(connection, sql, getBeanListHandler(), paramsList.toArray());
// handler return
if (list == null || list.size() == 0) {
log.info("<queryByIdList> " + getTableName() + " but return no result");
return Collections.emptyMap();
}
// store result in map
HashMap<K, T> map = new HashMap();
for (T t : list) {
map.put(getPrimaryKeyFieldValue(t.getStringId()), t);
}
return map;
}
// ๆ นๆฎ็ปๅฎ PRIMARY KEY ID ๅ่กจๆฅ่ฏข๏ผ่ฟๅๅ่กจ
public List queryByIdList(List idList) throws SQLException {
Map<K, T> map = queryMapByIdList(idList);
if (map == null || map.size() == 0) {
return Collections.emptyList();
}
// sort result by original ID list sequence
ArrayList<T> retList = new ArrayList();
for (K id : idList) {
if (map.containsKey(id)) {
T t = map.get(id);
retList.add(t);
}
}
log.info("<queryByIdList> " + getTableName() + ", total " + retList.size() + " result=" + retList);
return retList;
}
// ๆทปๅ WHERE ๆฅ่ฏขๆกไปถๅ้
// ๆฏๅฆ่ฆๆทปๅ ไธไธช name='Benson' ็ๆฅ่ฏขๆกไปถ
protected void addWhereValue(List whereSQL, List paramsList, String fieldName, Object value) {
whereSQL.add(fieldName + "=?");
paramsList.add(value);
}
public enum WhereType {
WHERE_EQUAL, // ็ธ็ญ
WHERE_GREATER_THAN_OR_EQUAL, // ๅคงไบ็ญไบ
WHERE_GREATER_THAN,
WHERE_LESS_THAN_OR_EQUAL, // ๅฐไบ็ญไบ
WHERE_LESS_THAN, // ๅฐไบ
WHERE_LIKE, // ๅ
ณ้ฎๅญๅ
ๅซ
WHERE_ARRAY_INTEGER_CONTAINS, // ๆฐ็ปๅ
ๅซ
WHERE_NOT_EQUAL, // ไธ็ญไบ
WHERE_IN_LIST // ๅฑไบไธไธชlistไธญ็ๆไธช
}
// ๆ้ whereๅญๆฎตlist ๆ้ ๅๆฐlist,้่ฝฝๅaddWhereValueๆนๆณ
protected void addWhereValue(List whereSQL, List paramsList, String fieldName, Object value, WhereType type) throws SQLException {
if (value == null)
return;
switch (type) {
case WHERE_EQUAL:
whereSQL.add(fieldName + "= ?");
paramsList.add(value);
break;
case WHERE_NOT_EQUAL:
whereSQL.add(fieldName + "!= ?");
paramsList.add(value);
break;
case WHERE_GREATER_THAN_OR_EQUAL:
whereSQL.add(fieldName + ">= ?");
paramsList.add(value);
break;
case WHERE_GREATER_THAN:
whereSQL.add(fieldName + "> ?");
paramsList.add(value);
break;
case WHERE_LESS_THAN_OR_EQUAL:
whereSQL.add(fieldName + "<= ?");
paramsList.add(value);
break;
case WHERE_LESS_THAN:
whereSQL.add(fieldName + "< ?");
paramsList.add(value);
break;
case WHERE_LIKE:
whereSQL.add(fieldName + " ILIKE ?");
paramsList.add("%" + value + "%");
break;
case WHERE_ARRAY_INTEGER_CONTAINS:
List<Integer> paramValues = null;
// ๅฐ่ฏ่ฝฌๆขๆIntegerๅ็list
try {
paramValues = (List<Integer>) value;
} catch (RuntimeException e) {
e.printStackTrace();
}
if (paramValues == null || paramValues.size() <= 0)
return;
whereSQL.add(fieldName + " @> ?");
Connection connection = TransactionManager.getConnection();
Array tagArray = DBUtils.toIntArray(connection, paramValues, false);
paramsList.add(tagArray);
break;
case WHERE_IN_LIST:
if (value instanceof List) {
List inList = (List) value;
if (inList == null || inList.size() == 0) {
return;
}
// ๆ้ ้ฎๅทๅ่กจ
List<String> markList = new ArrayList<>();
for (Object o : inList) {
markList.add("?");
}
String joinString = String.join(",", markList);
whereSQL.add(fieldName + " IN (" + joinString + ")");
// ๅฐๅๆฐๅ
จ้จๅ ๅ
ฅ
paramsList.addAll(inList);
} else {
return;
}
break;
}
}
// ๆทปๅ Update ็ Set ๆกไปถ
// ๆฏๅฆ่ฆๆทปๅ ไธไธช SET mobile='13912345555'
// forceUpdateNull : ๅฆๆ็ปๅฎๅญๆฎตๅผไธบ็ฉบ๏ผๆฏๅฆไนไฟๅญ่ฏฅๅผ๏ผไธ่ฌ้ป่ฎคๅปบ่ฎฎ็จ false๏ผๅณๅชๆ้็ฉบๆๆดๆฐ่ฏฅๅญๆฎต
protected void addUpdateField(List setFieldList, List paramsList, String fieldName, Object value, boolean forceUpdateNull) {
if (value == null && !forceUpdateNull) {
// no need to update
return;
}
setFieldList.add(fieldName + "=?");
paramsList.add(value);
}
// ่ฟๆฅๅคไธช set ๅญๆฎต๏ผ้ๅทๅ้
protected String buildSetSQL(List setFieldList) {
if (setFieldList.size() == 0) {
return null;
}
return String.join(", ", setFieldList);
}
// ่ฟๆฅๅคไธช where ๅญๆฎต๏ผๅชๆฏๆ AND ่ฟๆฅ
protected String buildWhereAndSQL(List fieldList) {
if (fieldList.size() == 0) {
return null;
}
return String.join(" AND ", fieldList);
}
// lamda ๅฝๆฐๅผๆฅๅฃ๏ผ็จไบinsertๆนๆณ
@FunctionalInterface
public static interface InsertSQLInterface {
// ๆทปๅ ่ฆๆๅ
ฅ็ๅญๆฎตๅๅผ๏ผไฟๅญๅฐ fieldList ๅ paramsList ไธญ
public void addInsertFieldValues(Connection connection, List fieldList, List paramsList) throws SQLException;
}
// lamda ๅฝๆฐๅผๆฅๅฃ๏ผ็จไบupdateๆนๆณ
@FunctionalInterface
public static interface UpdateSetInterface {
// ๆทปๅ ่ฆไฟฎๆน็ๅญๆฎตๅๅผ๏ผไฟๅญๅฐ updateSetSQL ๅ paramsList ไธญ
public void addUpdateSetFields(Connection connection, List updateSetSQL, List paramsList) throws SQLException;
}
// lamda ๅฝๆฐๅผๆฅๅฃ๏ผ็จไบupdateๆนๆณ
@FunctionalInterface
public static interface WhereIdInterface {
// ๆทปๅ ่ฆWHERE็ๆกไปถ๏ผไป
ๆฏๆ AND๏ผ๏ผไฟๅญๅฐ whereSQL ๅ paramsList ไธญ
public void addWhereFields(Connection connection, List whereSQL, List paramsList) throws SQLException;
}
/**
-
@param updateSetInterface ่ฆไฟฎๆน็ๅญๆฎตๅๅญๆฎตๅผ
-
@param whereIdInterface ไฟฎๆนๆปก่ถณ็ๆฅ่ฏขๆกไปถ
-
@return
-
@throws SQLException
*/
public int update(UpdateSetInterface updateSetInterface, WhereIdInterface whereIdInterface) throws SQLException {
// init query runner
QueryRunner queryRunner = new QueryRunner();
// get connection from transaction
Connection connection = TransactionManager.getConnection();
List paramsList = new ArrayList<>();
List updateSetSQL = new ArrayList();
List whereSQL = new ArrayList<>();
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.append("UPDATE " + getTableName() + " SET ");
updateSetInterface.addUpdateSetFields(connection, updateSetSQL, paramsList);
String setString = buildSetSQL(updateSetSQL);
if (setString == null) {
// nothing to update???
return 0;
}
// add update set string
sqlBuilder.append(setString);
// add update condition
whereIdInterface.addWhereFields(connection, whereSQL, paramsList);
// add where string
if (whereSQL.size() > 0) {
sqlBuilder.append(" WHERE ");
sqlBuilder.append(buildWhereAndSQL(whereSQL));
}
// execute update
String sql = sqlBuilder.toString();
log.info(" " + sql + " , params=" + paramsList);
int updates = queryRunner.update(connection, sql, paramsList.toArray());
log.info(" " + getTableName() + " total " + updates + " updates");
return updates;
}
// ๆง่ก SQL UPDATE ๆ่
DELETE ่ฏทๆฑ
protected int executeUpdate(String sql, Object... params) throws SQLException {
// init query runner
QueryRunner queryRunner = new QueryRunner();
// get connection from transaction
Connection connection = TransactionManager.getConnection();
log.info("<executeUpdate> " + sql);
log.info("<executeUpdate> params= " + Arrays.asList(params));
int affected = queryRunner.update(connection, sql, params);
log.info("<executeUpdate> " + getTableName() + " total " + affected + " affected");
return affected;
}
// ๆ นๆฎ็ปๅฎ ID ๅ ้ค่ฎฐๅฝ
protected int deleteById(String id) throws SQLException {
// init query runner
QueryRunner queryRunner = new QueryRunner();
// get connection from transaction
Connection connection = TransactionManager.getConnection();
List<Object> paramsList = new ArrayList<>();
List<String> whereSQL = new ArrayList<>();
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.append("DELETE FROM " + getTableName());
// add update condition
addWhereValue(whereSQL, paramsList, getPrimaryKeyFieldName(), getPrimaryKeyFieldValue(id));
// add where string
if (whereSQL.size() > 0) {
sqlBuilder.append(" WHERE ");
sqlBuilder.append(buildWhereAndSQL(whereSQL));
} else {
// avoid deleteById ALL records...
return 0;
}
// execute update
String sql = sqlBuilder.toString();
log.info("<deleteById> " + sql + " , id=" + id);
int updates = queryRunner.update(connection, sql, paramsList.toArray());
log.info("<deleteById> " + getTableName() + " total " + updates + " deleted");
return updates;
}
// ๆๅญๆฎตๅ ไธ่กจๅ๏ผ็จไบ่่กจๆฅ่ฏข
public String prefixField(String fieldName) {
return getTableName() + "." + fieldName;
}
// ๅๅผๆฅ่ฏข
protected R queryOfSingleValue(String sql, Class returnTypeClass, Object... params) throws SQLException {
if (StringUtil.isEmpty(sql)) {
return null;
}
//ๅผๅงๅๅคๆๅ
ฅๆฐๆฎ
log.info("<queryOfSingleValue> sql=" + sql);
// init query runner
QueryRunner queryRunner = new QueryRunner();
// get connection from transaction
Connection connection = TransactionManager.getConnection();
R result = queryRunner.query(connection, sql, new ScalarHandler<R>(1), params);
log.info("<queryOfSingleValue> " + getTableName() + " total " + result + " results");
return result;
}
/**
- ็จไบๆ้ WHEREๅญๅฅ๏ผๅชๆฏๆAND่ฟๆฅ
- ็จๆณ็คบไพ๏ผ
*
- WhereClauseBuilder where = new WhereClauseBuilder()
-
.and(UserIncomeAudit.FIELD_STATUS, status)
-
.and(UserIncomeAudit.FIELD_USER_NAME, username, WhereType.WHERE_LIKE);
- return queryByCondition(where.getSql(), offset, limit, null, where.getParams());
-
Created by Vincent
*/
protected class WhereClauseBuilder {
private List fields = new ArrayList<>();
private List params = new ArrayList<>();
public WhereClauseBuilder() {}
public WhereClauseBuilder and(String field, Object value) {
addWhereValue(fields, params, field, value);
return this;
}
public WhereClauseBuilder and(String field, Object value, WhereType whereType) {
try {
addWhereValue(fields, params, field, value, whereType);
return this;
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
public String getSql() {
if(fields == null || fields.isEmpty()) {
return null;
}
return " WHERE " + buildWhereAndSQL(fields);
}
public Object[] getParams() {
return params.toArray();
}
}
}
//
// Source code recreated from a .class file by IntelliJ IDEA
// (powered by Fernflower decompiler)
//
package org.apache.commons.dbutils;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Locale;
import java.util.Map;
import java.util.Map.Entry;
import org.apache.commons.dbutils.BeanProcessor;
import org.apache.commons.dbutils.RowProcessor;
public class BasicRowProcessor implements RowProcessor {
private static final BeanProcessor defaultConvert = new BeanProcessor();
private static final BasicRowProcessor instance = new BasicRowProcessor();
private final BeanProcessor convert;
/** @deprecated */
@Deprecated
public static BasicRowProcessor instance() {
return instance;
}
public BasicRowProcessor() {
this(defaultConvert);
}
public BasicRowProcessor(BeanProcessor convert) {
this.convert = convert;
}
public Object[] toArray(ResultSet rs) throws SQLException {
ResultSetMetaData meta = rs.getMetaData();
int cols = meta.getColumnCount();
Object[] result = new Object[cols];
for(int i = 0; i < cols; ++i) {
result[i] = rs.getObject(i + 1);
}
return result;
}
public <T> T toBean(ResultSet rs, Class<T> type) throws SQLException {
return this.convert.toBean(rs, type);
}
public <T> List<T> toBeanList(ResultSet rs, Class<T> type) throws SQLException {
return this.convert.toBeanList(rs, type);
}
public Map<String, Object> toMap(ResultSet rs) throws SQLException {
BasicRowProcessor.CaseInsensitiveHashMap result = new BasicRowProcessor.CaseInsensitiveHashMap();
ResultSetMetaData rsmd = rs.getMetaData();
int cols = rsmd.getColumnCount();
for(int i = 1; i <= cols; ++i) {
String columnName = rsmd.getColumnLabel(i);
if(null == columnName || 0 == columnName.length()) {
columnName = rsmd.getColumnName(i);
}
result.put(columnName, rs.getObject(i));
}
return result;
}
private static class CaseInsensitiveHashMap extends LinkedHashMap<String, Object> {
private final Map<String, String> lowerCaseMap;
private static final long serialVersionUID = -2848100435296897392L;
private CaseInsensitiveHashMap() {
this.lowerCaseMap = new HashMap();
}
public boolean containsKey(Object key) {
Object realKey = this.lowerCaseMap.get(key.toString().toLowerCase(Locale.ENGLISH));
return super.containsKey(realKey);
}
public Object get(Object key) {
Object realKey = this.lowerCaseMap.get(key.toString().toLowerCase(Locale.ENGLISH));
return super.get(realKey);
}
public Object put(String key, Object value) {
Object oldKey = this.lowerCaseMap.put(key.toLowerCase(Locale.ENGLISH), key);
Object oldValue = super.remove(oldKey);
super.put(key, value);
return oldValue;
}
public void putAll(Map<? extends String, ?> m) {
Iterator i$ = m.entrySet().iterator();
while(i$.hasNext()) {
Entry entry = (Entry)i$.next();
String key = (String)entry.getKey();
Object value = entry.getValue();
this.put(key, value);
}
}
public Object remove(Object key) {
Object realKey = this.lowerCaseMap.remove(key.toString().toLowerCase(Locale.ENGLISH));
return super.remove(realKey);
}
}
}
`
React
Typescript
Django
Laravel
Facebook
Microsoft
Google
Alibaba
D3
Tencent