Java tutorial
/* * To change this license header, choose License Headers in Project Properties. * To change this template file, choose Tools | Templates * and open the template in the editor. */ package com.cn.controller; import com.alibaba.fastjson.JSONArray; import com.alibaba.fastjson.JSONObject; import com.cn.bean.FieldDescription; import com.cn.util.DatabaseOpt; import com.cn.util.Units; import java.io.File; import java.io.FileInputStream; import java.io.InputStream; import java.lang.reflect.Field; import java.lang.reflect.Method; import java.lang.reflect.Modifier; import java.sql.BatchUpdateException; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Types; import java.util.ArrayList; import java.util.Arrays; import java.util.Iterator; import java.util.List; import java.util.Map; import java.util.Set; import org.apache.log4j.Logger; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.dom4j.Element; /** * * @author LFeng */ public class CommonController { private static final Logger logger = Logger.getLogger(CommonController.class); /** * ?? * * @param datas ? * add???: [{obj1}, {obj2}, ...] * update???: [{updateObj1}, {whereObj1}, {updateObj2}, {whereObj2}, ...] * delete???: [{whereObj1}, {whereObj2}, ...] * @param beanPackage , : com.cn.bean.in. * @param tableName ??, : DJInWareHouse * @param operate ?, ??: add, update, delete * @param connType ? * @return 0 -- ?? | -1 -- ? | 1 -- ? | 2 -- ? * @throws java.lang.Exception */ public ArrayList<Integer> dataBaseOperate(String datas, String beanPackage, String tableName, String operate, String connType) throws Exception { ArrayList<Integer> results = new ArrayList<>(); int[] exeResult = null; Class objClass = Class.forName(beanPackage + tableName); CallableStatement statement = null; DatabaseOpt opt = new DatabaseOpt(); Connection conn = null; try { JSONArray arrayData = JSONArray.parseArray(datas); if (arrayData == null || arrayData.isEmpty()) { results.add(0, 2); return results; } conn = opt.getConnection(connType); switch (operate) { //<editor-fold desc="??"> case "add": { StringBuilder builder; builder = new StringBuilder("insert into tbl" + tableName + " () values ()"); JSONObject firstObj = arrayData.getJSONObject(0); Iterator<String> keysIterator = firstObj.keySet().iterator(); while (keysIterator.hasNext()) { String key = keysIterator.next(); if (!isInput(objClass, key)) { continue; } if (builder.indexOf(",") == -1) { builder.insert(builder.indexOf("(") + 1, key + ","); builder.insert(builder.lastIndexOf("(") + 1, "?,"); } else { builder.insert(builder.indexOf(",)") + 1, key + ","); builder.insert(builder.lastIndexOf(",)") + 1, "?,"); } } if (builder.indexOf(",)") != -1) { builder.deleteCharAt(builder.indexOf((",)"))); builder.deleteCharAt(builder.lastIndexOf(",)")); } String sql = builder.toString(); //System.out.println("add sql:" + sql); conn.setAutoCommit(false); statement = conn.prepareCall(sql); //?? for (int i = 0; i < arrayData.size(); i++) { JSONObject object = arrayData.getJSONObject(i); keysIterator = object.keySet().iterator(); int itemCount = 1; while (keysIterator.hasNext()) { String key = keysIterator.next(); if (!isInput(objClass, key)) { continue; } try { setFieldValue(objClass, key, object.getString(key), statement, itemCount); } catch (NoSuchFieldException ex) { logger.error("", ex); statement.setString(itemCount, object.getString(key)); } itemCount++; } statement.addBatch(); } break; } //</editor-fold> //<editor-fold desc="??"> case "update": { if (arrayData.size() % 2 != 0) { results.add(0, 1); return results;//??? } StringBuilder builder; builder = new StringBuilder("update tbl" + tableName); JSONObject firstSetObj = arrayData.getJSONObject(0); JSONObject firstWhereObj = arrayData.getJSONObject(1); //setsql boolean isFirse = true; Iterator<String> keysIterator = firstSetObj.keySet().iterator(); while (keysIterator.hasNext()) { String key = keysIterator.next(); if (!hasField(objClass, key)) { continue; } if (!isInput(objClass, key)) { continue; } if (isFirse) { builder.append(" set ").append(key).append(" = ").append("?"); isFirse = false; } else { builder.append(",").append(key).append(" = ").append("?"); } } //where?sql isFirse = true; keysIterator = firstWhereObj.keySet().iterator(); while (keysIterator.hasNext()) { String key = keysIterator.next(); if (!hasField(objClass, key)) { continue; } if (!isInput(objClass, key)) { continue; } if (isFirse) { builder.append(" where ").append(key).append(" = ").append("?"); isFirse = false; } else { builder.append(" and ").append(key).append(" = ").append("?"); } } String sql = builder.toString(); //System.out.println("update sql:" + sql); conn.setAutoCommit(false); statement = conn.prepareCall(sql); //?? for (int i = 0; i < arrayData.size(); i = i + 2) { JSONObject setObject = arrayData.getJSONObject(i); JSONObject whereObject = arrayData.getJSONObject(i + 1); //set? int itemCount = 1; keysIterator = setObject.keySet().iterator(); while (keysIterator.hasNext()) { String key = keysIterator.next(); if (!hasField(objClass, key)) { continue; } if (!isInput(objClass, key)) { continue; } try { setFieldValue(objClass, key, setObject.getString(key), statement, itemCount); } catch (NoSuchFieldException ex) { logger.error("", ex); statement.setString(itemCount, setObject.getString(key)); } itemCount++; } //where? //itemCount = 1; keysIterator = whereObject.keySet().iterator(); while (keysIterator.hasNext()) { String key = keysIterator.next(); if (!hasField(objClass, key)) { continue; } if (!isInput(objClass, key)) { continue; } try { setFieldValue(objClass, key, whereObject.getString(key), statement, itemCount); } catch (NoSuchFieldException ex) { logger.error("", ex); statement.setString(itemCount, whereObject.getString(key)); } itemCount++; } statement.addBatch(); } break; } //</editor-fold> //<editor-fold desc="??"> case "delete": { StringBuilder builder; builder = new StringBuilder("delete from tbl" + tableName); JSONObject firstObj = arrayData.getJSONObject(0); //setsql boolean isFirst = true; synchronized (this) { Iterator<String> keysIterator = firstObj.keySet().iterator(); while (keysIterator.hasNext()) { String key = keysIterator.next(); if (!hasField(objClass, key)) { continue; } if (!isInput(objClass, key)) { continue; } if (isFirst) { builder.append(" where ").append(key).append(" = ").append("?"); isFirst = false; } else { builder.append(" and ").append(key).append(" = ").append("?"); } } String sql = builder.toString(); //System.out.println("delete sql:" + sql); conn.setAutoCommit(false); statement = conn.prepareCall(sql); //?? for (int i = 0; i < arrayData.size(); i++) { JSONObject setObject = arrayData.getJSONObject(i); //set? int itemCount = 1; keysIterator = setObject.keySet().iterator(); while (keysIterator.hasNext()) { String key = keysIterator.next(); if (!hasField(objClass, key)) { continue; } if (!isInput(objClass, key)) { continue; } try { setFieldValue(objClass, key, setObject.getString(key), statement, itemCount); } catch (NoSuchFieldException ex) { logger.error("", ex); statement.setString(itemCount, setObject.getString(key)); } itemCount++; } statement.addBatch(); } } break; } //</editor-fold> } if (statement != null) { try { exeResult = statement.executeBatch(); conn.commit(); results.add(0, 0); if (exeResult != null) { for (int i = 0; i < exeResult.length; i++) { results.add(exeResult[i]); } } return results; } catch (BatchUpdateException e) { try { if (conn != null) { conn.rollback(); } } catch (SQLException ex1) { logger.error("?", ex1); } //???: 547 //Check???: 547 //???: 2627 logger.error("?:" + e.getErrorCode() + "," + e.getMessage()); exeResult = e.getUpdateCounts(); results.add(0, e.getErrorCode()); if (exeResult != null) { for (int i = 0; i < exeResult.length; i++) { results.add(exeResult[i]); } } return results; } } else { results.add(0, 1); return results;//?, ? } } catch (SQLException ex) { try { if (conn != null) { conn.rollback(); } } catch (SQLException ex1) { logger.error("?", ex1); } logger.error("?", ex); } finally { try { if (statement != null) { statement.close(); } if (conn != null) { conn.close(); } } catch (SQLException ex) { logger.error("?", ex); } } //-1 results.add(0, -1); if (exeResult != null) { for (int i = 0; i < exeResult.length; i++) { results.add(exeResult[i]); } } return results; } /** * ?? * * @param type -- table?, tableNametbl?; view * tableNameview? * @param beanPackage , : com.cn.bean.in. * @param tableName ??, : DJInWareHouse * @param fields , : * * @param wherecase ? * @param pageSize ?? * @param pageIndex ? * @param orderField ? * @param orderFlag ?? * @param connType ? * @return * @throws Exception */ public List<Object> dataBaseQuery(String type, String beanPackage, String tableName, String fields, String wherecase, int pageSize, int pageIndex, String orderField, int orderFlag, String connType) throws Exception { //System.out.println("wherecase:" + wherecase); CallableStatement statement = null; DatabaseOpt opt = new DatabaseOpt(); Connection conn = null; ArrayList<Object> result; Class objClass = Class.forName(beanPackage + tableName); try { conn = opt.getConnection(connType); statement = conn.prepareCall("{call tbGetRecordPageList(?, ?, ?, ?, ?, ?, ?, ?)}"); if (type.compareTo("table") == 0) { statement.setString("tableName", "tbl" + tableName); } if (type.compareTo("view") == 0) { statement.setString("tableName", "view" + tableName); } statement.setString("fields", fields); statement.setString("wherecase", wherecase); statement.setInt("pageSize", pageSize); statement.setInt("pageIndex", pageIndex); statement.setString("orderField", orderField); statement.setInt("orderFlag", orderFlag); statement.registerOutParameter("recordCount", Types.INTEGER); ResultSet set = statement.executeQuery(); Method[] methods = objClass.getMethods(); result = new ArrayList<>(); while (set.next()) { Object object = objClass.newInstance(); for (Method method : methods) { String methodName = method.getName(); if (methodName.startsWith("set") && !Modifier.isStatic(method.getModifiers())) { // ?????? String columnName = methodName.substring(3, methodName.length()); if (Units.isExistColumn(set, columnName)) { // ? Class[] parmts = method.getParameterTypes(); if (parmts[0] == int.class) { method.invoke(object, set.getInt(columnName)); } else if (parmts[0] == boolean.class) { method.invoke(object, set.getBoolean(columnName)); } else if (parmts[0] == float.class) { method.invoke(object, set.getFloat(columnName)); } else if (parmts[0] == double.class) { method.invoke(object, set.getDouble(columnName)); } else { method.invoke(object, set.getString(columnName)); } } } } result.add(object); } objClass.getMethod("setRecordCount", int.class).invoke(null, statement.getInt("recordCount")); set.close(); return result; } catch (SQLException ex) { logger.error("?", ex); } finally { try { if (statement != null) { statement.close(); } if (conn != null) { conn.close(); } } catch (SQLException ex) { logger.error("?", ex); } } return null; } public List<Object> dataBaseQueryWithNotCloseConn(String type, String beanPackage, String tableName, String fields, String wherecase, int pageSize, int pageIndex, String orderField, int orderFlag, Connection conn) throws Exception { CallableStatement statement = null; ArrayList<Object> result; Class objClass = Class.forName(beanPackage + tableName); try { statement = conn.prepareCall("{call tbGetRecordPageList(?, ?, ?, ?, ?, ?, ?, ?)}"); if (type.compareTo("table") == 0) { statement.setString("tableName", "tbl" + tableName); } if (type.compareTo("view") == 0) { statement.setString("tableName", "view" + tableName); } statement.setString("fields", fields); statement.setString("wherecase", wherecase); statement.setInt("pageSize", pageSize); statement.setInt("pageIndex", pageIndex); statement.setString("orderField", orderField); statement.setInt("orderFlag", orderFlag); statement.registerOutParameter("recordCount", Types.INTEGER); ResultSet set = statement.executeQuery(); Method[] methods = objClass.getMethods(); result = new ArrayList<>(); while (set.next()) { Object object = objClass.newInstance(); for (Method method : methods) { String methodName = method.getName(); if (methodName.startsWith("set") && !Modifier.isStatic(method.getModifiers())) { // ?????? String columnName = methodName.substring(3, methodName.length()); if (Units.isExistColumn(set, columnName)) { // ? Class[] parmts = method.getParameterTypes(); if (parmts[0] == int.class) { method.invoke(object, set.getInt(columnName)); } else if (parmts[0] == boolean.class) { method.invoke(object, set.getBoolean(columnName)); } else if (parmts[0] == float.class) { method.invoke(object, set.getFloat(columnName)); } else if (parmts[0] == double.class) { method.invoke(object, set.getDouble(columnName)); } else { method.invoke(object, set.getString(columnName)); } } } } result.add(object); } objClass.getMethod("setRecordCount", int.class).invoke(null, statement.getInt("recordCount")); set.close(); return result; } catch (SQLException ex) { logger.error("?", ex); } finally { try { if (statement != null) { statement.close(); } } catch (SQLException ex) { logger.error("?", ex); } } return null; } /** * ?? * @param proceduceName ?? * @param params ?, json?: "paramsName": "paramsType,paramsValue", "partCode":"string,1100010-FA01" * @param connType ? * @return * @throws Exception */ public ArrayList<Integer> proceduceForUpdate(String proceduceName, JSONObject params, String connType) throws Exception { JSONArray array = new JSONArray(); array.add(params); return proceduceForUpdate(proceduceName, array, connType); } /** * ??(??) * @param proceduceName ?? * @param params ?, json?: "paramsName": "paramsType,paramsValue", "partCode":"string,1100010-FA01" * @param connType ? * @return * @throws Exception */ public ArrayList<Integer> proceduceForUpdate(String proceduceName, JSONArray params, String connType) throws Exception { CallableStatement statement = null; DatabaseOpt opt = new DatabaseOpt(); Connection conn = null; ArrayList<Integer> results = new ArrayList<>(); try { conn = opt.getConnection(connType); int[] exeResult = null; //SQL? StringBuilder builder; builder = new StringBuilder("{call " + proceduceName + "()}"); Iterator<String> keysIterator = params.getJSONObject(0).keySet().iterator(); while (keysIterator.hasNext()) { if (builder.indexOf(",") == -1) { builder.insert(builder.indexOf("(") + 1, "?,"); } else { builder.insert(builder.indexOf(",)") + 1, "?,"); } keysIterator.next(); } if (builder.indexOf(",)") != -1) { builder.deleteCharAt(builder.indexOf(",)")); } String sql = builder.toString(); // System.out.println("sql:" + sql); conn.setAutoCommit(false); //? statement = conn.prepareCall(sql); // JSONArray array = JSONObject.parseArray(datas); for (int i = 0; i < params.size(); i++) { //? JSONObject object = params.getJSONObject(i); keysIterator = object.keySet().iterator(); while (keysIterator.hasNext()) { String key = keysIterator.next(); String[] keyValue = object.getString(key).split(","); if (keyValue[0].compareToIgnoreCase("int") == 0) { statement.setInt(key, Integer.valueOf(keyValue[1])); } if (keyValue[0].compareToIgnoreCase("string") == 0) { statement.setString(key, keyValue[1]); } } statement.addBatch(); } try { exeResult = statement.executeBatch(); conn.commit(); results.add(0, 0); if (exeResult != null) { for (int i = 0; i < exeResult.length; i++) { results.add(exeResult[i]); } } return results; } catch (BatchUpdateException e) { try { if (conn != null) { conn.rollback(); } } catch (SQLException ex1) { logger.error("?", ex1); } logger.error("?:" + e.getErrorCode() + "," + e.getMessage()); exeResult = e.getUpdateCounts(); results.add(0, e.getErrorCode()); if (exeResult != null) { for (int i = 0; i < exeResult.length; i++) { results.add(exeResult[i]); } } return results; } } catch (SQLException e) { try { if (conn != null) { conn.rollback(); } } catch (SQLException ex1) { logger.error("?", ex1); } logger.error("?", e); } finally { try { if (statement != null) { statement.close(); } if (conn != null) { conn.close(); } } catch (SQLException ex) { logger.error("?", ex); } } return null; } /** * ?? * * @param proceduceName * @param params ??, : ?(params1 int, params2 * varchar(20)),?: {"params1": "int,params1Val", "params2": "string,params2Val"} * @param className ???, : com.cn.bean.in.DJInWareHouse * @param connType * @return * @throws java.lang.Exception */ public List<Object> proceduceQuery(String proceduceName, JSONObject params, String className, String connType) throws Exception { CallableStatement statement = null; DatabaseOpt opt = new DatabaseOpt(); Connection conn = null; try { conn = opt.getConnection(connType); //SQL? StringBuilder builder; builder = new StringBuilder("{call " + proceduceName + "()}"); Iterator<String> keysIterator = params.keySet().iterator(); while (keysIterator.hasNext()) { if (builder.indexOf(",") == -1) { builder.insert(builder.indexOf("(") + 1, "?,"); } else { builder.insert(builder.indexOf(",)") + 1, "?,"); } keysIterator.next(); } if (builder.indexOf(",)") != -1) { builder.deleteCharAt(builder.indexOf(",)")); } String sql = builder.toString(); // System.out.println("sql:" + sql); //? statement = conn.prepareCall(sql); //? keysIterator = params.keySet().iterator(); while (keysIterator.hasNext()) { String key = keysIterator.next(); String[] keyValue = params.getString(key).split(",", 2); // System.out.println(key + ":" + keyValue[1] + "," + keyValue[1].length()); if (keyValue[0].compareToIgnoreCase("int") == 0) { statement.setInt(key, Integer.valueOf(keyValue[1])); } if (keyValue[0].compareToIgnoreCase("string") == 0) { statement.setString(key, keyValue[1]); } if (keyValue[0] == null || keyValue[0].isEmpty() || keyValue[0].compareToIgnoreCase("null") == 0) { statement.setString(key, null); } if (keyValue[0].compareToIgnoreCase("out") == 0) { statement.registerOutParameter(key, Integer.valueOf(keyValue[1])); } } ResultSet set = statement.executeQuery(); Class objClass = Class.forName(className); Method[] methods = objClass.getMethods(); ArrayList<Object> result = new ArrayList<>(); while (set.next()) { Object object = objClass.newInstance(); for (Method method : methods) { String methodName = method.getName(); if (methodName.startsWith("set") && !Modifier.isStatic(method.getModifiers())) { // ?????? String columnName = methodName.substring(3, methodName.length()); if (Units.isExistColumn(set, columnName)) { // ? Class[] parmts = method.getParameterTypes(); if (parmts[0] == int.class) { method.invoke(object, set.getInt(columnName)); } else if (parmts[0] == boolean.class) { method.invoke(object, set.getBoolean(columnName)); } else if (parmts[0] == float.class) { method.invoke(object, set.getFloat(columnName)); } else if (parmts[0] == double.class) { method.invoke(object, set.getDouble(columnName)); } else { method.invoke(object, set.getString(columnName)); } } } } result.add(object); } set.close(); return result; } catch (SQLException e) { logger.error("?" + e.getErrorCode(), e); } finally { try { if (statement != null) { statement.close(); } if (conn != null) { conn.close(); } } catch (SQLException ex) { logger.error("?", ex); } } return null; } /** * ?JavaBean????, ????? * * @param objClass * @param fieldName * @param fieldValue * @param statement * @param fieldIndex * @throws NoSuchFieldException * @throws java.sql.SQLException */ public void setFieldValue(Class objClass, String fieldName, String fieldValue, CallableStatement statement, int fieldIndex) throws Exception { //String fieldType = objClass.getDeclaredField(fieldName).getGenericType().toString(); //System.out.println("fieldName:" + fieldName + ",fieldValue:" + fieldValue + ",fieldType:" + fieldType + ",fieldIndex:" + fieldIndex); if (objClass.getDeclaredField(fieldName).getType() == int.class) { statement.setInt(fieldIndex, Integer.valueOf(Units.strIsEmpty(fieldValue) ? ("0") : (fieldValue))); } else if (objClass.getDeclaredField(fieldName).getType() == float.class) { statement.setFloat(fieldIndex, Float.valueOf(Units.strIsEmpty(fieldValue) ? ("0") : (fieldValue))); } else if (objClass.getDeclaredField(fieldName).getType() == double.class) { statement.setDouble(fieldIndex, Double.valueOf(Units.strIsEmpty(fieldValue) ? ("0") : (fieldValue))); } else if (objClass.getDeclaredField(fieldName).getType() == boolean.class) { statement.setString(fieldIndex, Boolean.valueOf(fieldValue) ? "1" : "0"); } else { statement.setString(fieldIndex, Units.strIsEmpty(fieldValue) ? null : (fieldValue)); } } /** * ? * * @param objClass * @param keyWord ? * @param rely ?(json) * @param isAll ?? * @return */ public String getWhereSQLStr(Class objClass, String keyWord, String rely, boolean isAll) { String result = null; Field[] fields = objClass.getDeclaredFields(); JSONObject object = JSONObject.parseObject(rely); Iterator<String> iterator = object.keySet().iterator(); while (iterator.hasNext()) { String key = iterator.next(); if (hasField(objClass, key)) { if (result == null) { result = "(" + key + " = '" + object.getString(key) + "')"; } else { result += " and " + "(" + key + " = '" + object.getString(key) + "')"; } } } if (!isAll) { return (result == null) ? "" : result; } String commonResult = null; Set<String> keySet = object.keySet(); for (Field field : fields) { if (Modifier.isStatic(field.getModifiers())) { //System.out.println("isStatic"); continue; } if (!isInput(objClass, field.getName())) { //System.out.println("is not input"); continue; } //?, ? if (keySet != null && keySet.contains(field.getName())) { //System.out.println("contain field"); continue; } String fieldType = field.getGenericType().toString(); if (fieldType.contains("Integer") || fieldType.contains("Double") || fieldType.contains("Float")) { if (commonResult == null) { commonResult = "(" + field.getName() + " = " + keyWord + ")"; } else { commonResult += " or " + "(" + field.getName() + " = " + keyWord + ")"; } } else if (fieldType.contains("String")) { if (commonResult == null) { commonResult = "(" + field.getName() + " like '%" + keyWord + "%')"; } else { commonResult += " or " + "(" + field.getName() + " like '%" + keyWord + "%')"; } } } if (result != null) { result += " and (" + commonResult + ")"; } else { result = commonResult; } // System.out.println("where SQL:" + result); return (result == null) ? "" : "(" + result + ")"; } /** * ?? * * @param objClass * @param keyWord ? * @param rely ?(json:{start:startTime, end: endTime}) * @param isAll ?? * @return */ public String getWhereSQLStrWithDate(Class objClass, String keyWord, String rely, boolean isAll) { String result = null; JSONObject object = JSONObject.parseObject(rely); String commonResult = null; Field[] fields = objClass.getDeclaredFields(); for (Field field : fields) { if (Modifier.isStatic(field.getModifiers())) { continue; } //?? if (field.isAnnotationPresent(FieldDescription.class)) { FieldDescription description = field.getAnnotation(FieldDescription.class); if (description.type() != null && description.type().compareTo("date") == 0 && !Units.strIsEmpty(object.getString("start")) && !Units.strIsEmpty(object.getString("end"))) { if (result == null) { result = "(" + field.getName() + " between '" + object.getString("start") + "' and '" + object.getString("end") + "')"; } else { result += " and (" + field.getName() + " between '" + object.getString("start") + "' and '" + object.getString("end") + "')"; } continue; } } String fieldType = field.getGenericType().toString(); if (fieldType.contains("Integer") || fieldType.contains("Double") || fieldType.contains("Float")) { if (commonResult == null) { commonResult = "(" + field.getName() + " = " + keyWord + ")"; } else { commonResult += " or " + "(" + field.getName() + " = " + keyWord + ")"; } } else if (fieldType.contains("String")) { if (commonResult == null) { commonResult = "(" + field.getName() + " like '%" + keyWord + "%')"; } else { commonResult += " or " + "(" + field.getName() + " like '%" + keyWord + "%')"; } } } if (result != null) { result += " and (" + commonResult + ")"; } else { result = commonResult; } // System.out.println("where date sql:" + result); return (result == null) ? "" : "(" + result + ")"; } public String getWhereSQLStrWithObject(Class objClass, JSONObject obj) { Iterator iterator = obj.entrySet().iterator(); String item = null; while (iterator.hasNext()) { Map.Entry<String, String> entry = (Map.Entry<String, String>) iterator.next(); if (!isInput(objClass, entry.getKey())) { continue; } if (item != null) { item += " and " + entry.getKey() + " = '" + entry.getValue() + "'"; } else { item = entry.getKey() + " = '" + entry.getValue() + "'"; } } return "(" + item + ")"; } public String getWhereSQLStrWithArray(JSONArray array) { String result = null; for (int i = 0; i < array.size(); i++) { JSONObject obj = array.getJSONObject(i); Iterator iterator = obj.entrySet().iterator(); String item = null; while (iterator.hasNext()) { Map.Entry<String, String> entry = (Map.Entry<String, String>) iterator.next(); if (item != null) { item += " and " + entry.getKey() + " = '" + entry.getValue() + "'"; } else { item = entry.getKey() + " = '" + entry.getValue() + "'"; } } if (result != null) { result += " or (" + item + ")"; } else { result = "(" + item + ")"; } } return result; } public String getWhereSQLStrAllField(Class objClass, String keyWord) { Field[] fields = objClass.getDeclaredFields(); String commonResult = null; for (Field field : fields) { if (Modifier.isStatic(field.getModifiers())) { continue; } if (field.isAnnotationPresent(FieldDescription.class)) { FieldDescription description = field.getAnnotation(FieldDescription.class); if (description.type() != null && description.operate().compareTo("display") == 0) { continue; } } String fieldType = field.getGenericType().toString(); if (fieldType.contains("Integer") || fieldType.contains("Double") || fieldType.contains("Float")) { if (commonResult == null) { commonResult = "(" + field.getName() + " = " + keyWord + ")"; } else { commonResult += " or " + "(" + field.getName() + " = " + keyWord + ")"; } } else if (fieldType.contains("String")) { if (commonResult == null) { commonResult = "(" + field.getName() + " like '%" + keyWord + "%')"; } else { commonResult += " or " + "(" + field.getName() + " like '%" + keyWord + "%')"; } } } return commonResult; } /** * ?????SQL * * @param objClass * @param fieldName * @param fieldValue * @return * @throws NoSuchFieldException */ public String getFieldSQLStr(Class objClass, String fieldName, String fieldValue) throws Exception { String fieldSQLStr; String fieldType = objClass.getField(fieldName).getGenericType().toString(); if (fieldType.contains("Integer") || fieldType.contains("Float") || fieldType.contains("Double")) { fieldSQLStr = fieldValue; } else if (fieldType.contains("Boolean")) { if (Boolean.valueOf(fieldValue)) { fieldSQLStr = "1"; } else { fieldSQLStr = "0"; } } else { fieldSQLStr = "'" + fieldValue + "'"; } return fieldSQLStr; } /** * ?????json * * @param element * @param roleRightList * @return */ public String hasRight(Element element, ArrayList<String> roleRightList) { String menuJson = ""; String roleCode = element.attributeValue("id"); if (roleRightList.contains(roleCode)) { if (element.elementIterator().hasNext()) { menuJson += "\"" + element.attributeValue("text") + "\":{"; Iterator<Element> iterator = element.elementIterator(); while (iterator.hasNext()) { menuJson += hasRight(iterator.next(), roleRightList); } menuJson = menuJson.substring(0, menuJson.length() - 1); menuJson += "},"; } else { menuJson += "\"" + element.attributeValue("text") + "\":"; menuJson += "\"" + element.attributeValue("hypelnk") + "," + element.attributeValue("url") + "," + element.attributeValue("id") + "," + element.attributeValue("icon") + "\","; } } return menuJson; } public String hasAppRight(Element element, ArrayList<String> roleRightList) { String menuJson = ""; String roleCode = element.attributeValue("id"); if (roleRightList.contains(roleCode) && roleCode.startsWith("80")) { if (element.elementIterator().hasNext()) { menuJson += "\"" + element.attributeValue("text") + "\":{"; Iterator<Element> iterator = element.elementIterator(); while (iterator.hasNext()) { menuJson += hasAppRight(iterator.next(), roleRightList); } menuJson = menuJson.substring(0, menuJson.length() - 1); menuJson += "},"; } else { menuJson += "\"" + element.attributeValue("text") + "\":"; menuJson += "\"" + element.attributeValue("hypelnk") + "," + element.attributeValue("url") + "," + element.attributeValue("id") + "," + element.attributeValue("icon") + "\","; } } return menuJson; } /** * ?????? * * @param objClass * @param fieldName * @return */ public boolean hasField(Class objClass, String fieldName) { Field[] fields = objClass.getDeclaredFields(); for (Field field : fields) { if (field.getName().compareToIgnoreCase(fieldName) == 0) { return true; } } return false; } /** * ????? * * @param objClass * @param fieldName * @return */ public boolean isInput(Class objClass, String fieldName) { Field[] fields = objClass.getDeclaredFields(); for (Field field : fields) { if (field.getName().compareToIgnoreCase(fieldName) == 0) { if (field.isAnnotationPresent(FieldDescription.class)) { FieldDescription description = field.getAnnotation(FieldDescription.class); //?inputimport if (description.operate().compareTo("input") == 0 || description.operate().compareTo("import") == 0) { return true; } } } } return false; } /** * ? * * @param beanPackage * @param className * @param fileName * @return * @throws Exception */ public ArrayList<Object> importData(String beanPackage, String className, String fileName) throws Exception { //??? Class objClass = Class.forName(beanPackage + className); Field[] fields = objClass.getDeclaredFields(); ArrayList<Field> accessFields = new ArrayList<>(); ArrayList<String> fieldDes = new ArrayList<>(); for (Field field : fields) { if (field.isAnnotationPresent(FieldDescription.class)) { FieldDescription description = field.getAnnotation(FieldDescription.class); if (description.operate().compareTo("import") == 0) { fieldDes.add(description.description()); accessFields.add(field); } } } //?, ?Excel? InputStream inputStream = null; File file = new File(fileName); inputStream = new FileInputStream(file); Sheet sheet; if (fileName.endsWith(".xls")) { HSSFWorkbook workbook = new HSSFWorkbook(inputStream); sheet = workbook.getSheetAt(0); } else if (fileName.endsWith(".xlsx")) { XSSFWorkbook workbook = new XSSFWorkbook(inputStream); sheet = workbook.getSheetAt(0); } else { logger.info("?Excel!"); return null; } Row headerRow = sheet.getRow(0); //????, ??? //System.out.println("cells num:" + headerRow.getPhysicalNumberOfCells() + ",des size:" + fieldDes.size()); if (headerRow == null || headerRow.getPhysicalNumberOfCells() != fieldDes.size()) { //json = Units.objectToJson(-1, "???, ?, ???", null); return null; } //????? int[] templateDataIndex = new int[fieldDes.size()]; for (int i = 0; i < fieldDes.size(); i++) { Cell cell = headerRow.getCell(i); cell.setCellType(Cell.CELL_TYPE_STRING); String fieldName = cell.getStringCellValue(); if (fieldDes.indexOf(fieldName) != -1) { templateDataIndex[fieldDes.indexOf(fieldName)] = i; } else { return null; } } ArrayList<Object> result = new ArrayList<>(); //??, List for (int i = 1; i <= sheet.getPhysicalNumberOfRows(); i++) { Row row = sheet.getRow(i); if (row == null) { continue; } if (Units.isEmptyRowForExcel(row)) { continue; } Object object = objClass.newInstance(); for (int j = 0; j < accessFields.size(); j++) { Field field = accessFields.get(j); field.setAccessible(true); Cell cell = row.getCell(templateDataIndex[j]); if (field.getType() == int.class) { if (cell == null) { field.set(object, 0); } else { if (Units.strIsEmpty(Units.getStringCellValue(cell))) { field.set(object, 0); } else { field.set(object, Double.valueOf(Units.getStringCellValue(cell)).intValue()); } } } else if (field.getType() == float.class) { if (cell == null) { field.set(object, 0); } else { if (Units.strIsEmpty(Units.getStringCellValue(cell))) { field.set(object, 0); } else { field.set(object, Double.valueOf(Units.getStringCellValue(cell)).floatValue()); } } } else if (field.getType() == double.class) { if (cell == null) { field.set(object, 0); } else { if (Units.strIsEmpty(Units.getStringCellValue(cell))) { field.set(object, 0); } else { field.set(object, Double.valueOf(Units.getStringCellValue(cell))); } } } else if (field.getType() == boolean.class) { if (cell == null) { field.set(object, false); } else { if (Units.strIsEmpty(Units.getStringCellValue(cell))) { field.set(object, false); } else { field.set(object, Boolean.valueOf(Units.getStringCellValue(cell))); } } } else { if (cell == null) { field.set(object, ""); } else { field.set(object, Units.getStringCellValue(cell)); } } } result.add(object); } return result; } }