From 48db618606f0f0cafd1ce795f8bf26a0bcec2581 Mon Sep 17 00:00:00 2001
From: zhangchao <zc857179121@qq.com>
Date: 星期六, 20 七月 2024 07:56:54 +0800
Subject: [PATCH] Merge remote-tracking branch 'origin/xgmFlasrs' into xgmFlasrs
---
src/main/java/com/zy/common/service/erp/ErpSqlServer.java | 420 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1 files changed, 420 insertions(+), 0 deletions(-)
diff --git a/src/main/java/com/zy/common/service/erp/ErpSqlServer.java b/src/main/java/com/zy/common/service/erp/ErpSqlServer.java
new file mode 100644
index 0000000..3469eec
--- /dev/null
+++ b/src/main/java/com/zy/common/service/erp/ErpSqlServer.java
@@ -0,0 +1,420 @@
+package com.zy.common.service.erp;
+
+import com.alibaba.fastjson.JSONObject;
+import com.baomidou.mybatisplus.annotations.TableField;
+import com.baomidou.mybatisplus.annotations.TableName;
+import com.core.common.Cools;
+import com.zy.third.erp.entity.InDetTB;
+import lombok.SneakyThrows;
+import lombok.extern.slf4j.Slf4j;
+import org.springframework.beans.factory.annotation.Autowired;
+import org.springframework.stereotype.Service;
+
+import java.io.ByteArrayInputStream;
+import java.io.ByteArrayOutputStream;
+import java.io.ObjectInputStream;
+import java.io.ObjectOutputStream;
+import java.lang.reflect.Field;
+import java.lang.reflect.Modifier;
+import java.sql.*;
+import java.util.ArrayList;
+import java.util.HashMap;
+import java.util.List;
+import java.util.Map;
+
+/**
+ * 绠�鍗曟寔涔呭眰妗嗘灦
+ * Created by vincent on 2020/11/26
+ */
+@Slf4j
+@Service
+public class ErpSqlServer {
+
+ private static final int CONNECTION_TIMEOUT_SECONDS = 10; // 杩炴帴瓒呮椂
+
+ private static final int QUERY_TIMEOUT_SECONDS = 30; // 鏌ヨ瓒呮椂
+
+ public static void main(String[] args) {
+ ErpSqlServer erpSqlServer = new ErpSqlServer();
+ // 鏌�
+// String sql = "select ise.* from InStockBillEntry ise left join InStockbill isb on isb.FInterID = ise.FInterID where 1=1";
+// List<InStockBillEntry> list = erpSqlServer.select(sql, InStockBillEntry.class);
+ // 鏀� / 鍒�
+// sql = "update InStockBill set Fflag_finish = 1 where FBillNo = ''{0}''";
+// sql = MessageFormat.format(sql, "123456");
+// erpSqlServer.update(sql);
+
+ HashMap<String, Object> stringStringHashMap = new HashMap<>();
+ stringStringHashMap.put("BillNo", "'1231312'");
+ stringStringHashMap.put("test", "'1231312'");
+ stringStringHashMap.put("BilhahahlNo", "'1231312'");
+ stringStringHashMap.put("B21312illNo", "'1231312'");
+ stringStringHashMap.put("testnum", 100);
+ erpSqlServer.insert(InDetTB.class, stringStringHashMap);
+ }
+
+ // 鏁版嵁搴撹繛鎺�
+// private Connection conn;
+ // 鍒涘缓棰勭紪璇戣鍙ュ璞★紝涓�鑸兘鏄敤杩欎釜鑰屼笉鐢⊿tatement
+ private PreparedStatement pstm = null;
+ // 鍒涘缓涓�涓粨鏋滈泦瀵硅薄
+ private ResultSet rs = null;
+
+ @Autowired
+ private ErpDbProperties erpDbProperties;
+
+ /*****************************************************************************/
+ /********************************** 灏佽 **********************************/
+ /*****************************************************************************/
+
+ /**
+ * 鏌ヨ
+ */
+ public <T> List<T> select(String sql, Class<T> cls) {
+ List<Map<String, Object>> result = executeQuery(sql);
+ List<T> list = new ArrayList<>();
+ if (null != result) {
+ for (Map<String, Object> entity : result) {
+ //鐢ㄦ渶绗ㄧ殑鏂规硶杞崲锛屼篃鍙互鐢ㄤ笅闈㈢殑process鏂规硶锛堣繕娌℃湁鍐欏叏锛�
+ list.add(JSONObject.parseObject(JSONObject.toJSONString(entity), cls));
+ //list.add(Cools.conver(entity, cls));
+ }
+ }
+ return list;
+ }
+
+ @SneakyThrows
+ public <T> void process(Map<String, Object> entity, Class<T> cls) {
+ for (Map.Entry<String, Object> entry : entity.entrySet()) {
+ String fieldName = entry.getKey();
+ Object value = entry.getValue();
+
+ Field field = cls.getDeclaredField(fieldName);
+ field.setAccessible(true);
+
+ if (value instanceof Integer && field.getType() == int.class || field.getType() == Integer.class) {
+ field.setInt(cls, (Integer) value);
+ } else if (value instanceof String && field.getType() == String.class) {
+ field.set(cls, value);
+ }
+ // 鍏朵粬绫诲瀷鍙互绫讳技澶勭悊
+ }
+ }
+
+ /**
+ * 鎵弿鎵�鏈夊疄鍔涚被灞炴�ч噷鐨凾ableField娉ㄨВ,骞跺皢閲岄潰鐨勫�间綔涓篶olumn
+ *
+ * @param cls
+ * @param <T>
+ * @return
+ */
+ public <T> String selectFrontPart(Class<T> cls) {
+ String tableName = cls.getDeclaredAnnotation(TableName.class).value();
+ List<HashMap<String, String>> columnAndField = new ArrayList<>();
+ String sql = "SELECT ";
+ if (Cools.isEmpty(tableName)) {
+ return null;
+ }
+ Field[] allFields = Cools.getAllFields(cls);
+ for (Field f : allFields) {
+ if (Modifier.isFinal(f.getModifiers())
+ || Modifier.isStatic(f.getModifiers())
+ || Modifier.isTransient(f.getModifiers())) {
+ continue;
+ }
+ String column = null;
+ if (f.isAnnotationPresent(TableField.class)) {
+ column = f.getAnnotation(TableField.class).value();
+ }
+ if (Cools.isEmpty(column)) {
+ column = f.getName();
+ }
+ String name = f.getName();
+ HashMap<String, String> container = new HashMap<>();
+ container.put(column, name);
+ columnAndField.add(container);
+ }
+ for (int i = 0; i < columnAndField.size(); i++) {
+ HashMap<String, String> stringStringHashMap = columnAndField.get(i);
+ if (i == columnAndField.size() - 1) {
+ for (String s : stringStringHashMap.keySet()) {
+ sql = sql + s + " AS " + stringStringHashMap.get(s);
+ }
+ } else {
+ for (String s : stringStringHashMap.keySet()) {
+ sql = sql + s + " AS " + stringStringHashMap.get(s) + ", ";
+ }
+ }
+ }
+ sql = sql + " FROM " + tableName;
+ return sql;
+ }
+
+ public String addWhere(String sql, Map<String, String> condition) {
+ int conditionSize = 1;
+ sql = sql + " WHERE ";
+ for (String s : condition.keySet()) {
+ if (conditionSize == condition.keySet().size()) {
+ sql = sql + s + " = " + condition.get(s);
+ } else {
+ sql = sql + s + " = " + condition.get(s) + " and ";
+ }
+ conditionSize = conditionSize + 1;
+ }
+ return sql;
+ }
+
+ /**
+ * map鐨勯敭鏄垪鍚�, 鍊肩瓑鍚屼簬鍊�
+ * key: id, value: 1 绛夊悓浜� WHERE id = 1
+ * 濡傛灉瑕佹煡璇㈠瓧绗︿覆闇�瑕佽嚜琛屽姞涓�''
+ * key:name, value: 'zhs'绛夊悓浜� WHERE name = 'zhs'
+ *
+ * @param cls
+ * @param condition
+ * @param <T>
+ * @return
+ */
+ public <T> List<T> selectList(Class<T> cls, Map<String, String> condition) {
+ String sql = selectFrontPart(cls);
+ String finalSQL = addWhere(sql, condition);
+ return select(finalSQL, cls);
+ }
+
+ public <T> List<T> selectAll(Class<T> cls) {
+ String sql = selectFrontPart(cls);
+ return select(sql, cls);
+ }
+
+ public List<Map<String, Object>> select(String sql) {
+ return executeQuery(sql);
+ }
+
+ /**
+ * 淇敼
+ */
+ public int update(String sql) {
+ return executeUpdate(sql);
+ }
+
+ public <T> int delete(Class<T> cls, Map<String, String> condition) {
+ String tableName = cls.getDeclaredAnnotation(TableName.class).value();
+ if (Cools.isEmpty(tableName)) {
+ return 0;
+ }
+ String sql = "DELETE FROM " + tableName;
+ String finalSQL = addWhere(sql, condition);
+ return executeUpdate(finalSQL);
+ }
+
+ public <T> int insert(Class<T> cls, Map<String, Object> condition) {
+ String tableName = cls.getDeclaredAnnotation(TableName.class).value();
+ if (Cools.isEmpty(tableName)) {
+ return 0;
+ }
+ String sql = "INSERT INTO " + tableName + " (";
+ int conditionSize = 1;
+ for (String s : condition.keySet()) {
+ if (conditionSize == condition.keySet().size()) {
+ sql = sql + s + ")";
+ } else {
+ sql = sql + s + ",";
+ }
+ conditionSize = conditionSize + 1;
+ }
+ sql = sql + " VALUES(";
+ int valuesSize = 1;
+ for (String s : condition.keySet()) {
+ if (valuesSize == condition.keySet().size()) {
+
+ sql = sql + condition.get(s) + ")";
+ } else {
+ sql = sql + condition.get(s) + ",";
+ }
+ valuesSize = valuesSize + 1;
+ }
+
+ return executeUpdate(sql);
+ }
+
+
+ /*****************************************************************************/
+ /********************************* 鏍稿績灞� *********************************/
+ /*****************************************************************************/
+
+
+ private List<Map<String, Object>> executeQuery(String sql) {
+ Connection conn = null;
+ try {
+ conn = getConn();
+ pstm = conn.prepareStatement(sql);
+
+ pstm.setQueryTimeout(QUERY_TIMEOUT_SECONDS);
+
+ rs = pstm.executeQuery();
+// List<Map<String, Object>> maps = convertList(rs);
+ return convertList(rs);
+ } catch (Exception e) {
+ e.printStackTrace();
+ return null;
+ } finally {
+ release();
+ if (conn != null) {
+ try {
+ conn.close();
+ conn = null;
+ } catch (SQLException e) {
+ e.printStackTrace();
+ }
+ }
+ }
+ }
+
+ public Number executeQueryCount(String sql, String column) {
+ Number value = 0;
+ Connection conn = null;
+ try {
+ conn = getConn();
+ pstm = conn.prepareStatement(sql);
+
+ pstm.setQueryTimeout(QUERY_TIMEOUT_SECONDS);
+
+ rs = pstm.executeQuery();
+// ResultSetMetaData metaData = rs.getMetaData();
+ while (rs.next()) {
+ value = (Number) rs.getInt(column);
+ }
+ return value;
+ } catch (Exception e) {
+ e.printStackTrace();
+ return 0;
+ } finally {
+ release();
+ if (conn != null) {
+ try {
+ conn.close();
+ conn = null;
+ } catch (SQLException e) {
+ e.printStackTrace();
+ }
+ }
+ }
+ }
+
+ private int executeUpdate(String sql) {
+ Connection conn = null;
+ try {
+ conn = getConn();
+ if (null != conn) {
+ pstm = conn.prepareStatement(sql);
+
+ pstm.setQueryTimeout(QUERY_TIMEOUT_SECONDS);
+
+ } else {
+ log.error("鏇存柊ERP涓棿琛ㄥけ璐�===>>鏁版嵁搴撹繛鎺onn涓虹┖");
+ log.error("鏇存柊ERP涓棿琛ㄥけ璐�===>>" + sql);
+ }
+ return pstm.executeUpdate();
+ } catch (Exception e) {
+ e.printStackTrace();
+ log.error("鏇存柊ERP涓棿琛ㄥけ璐�1===>>" + sql);
+ log.error("鏇存柊ERP涓棿琛ㄥけ璐�1===>>" + e);
+ return 0;
+ } finally {
+ release();
+ if (conn != null) {
+ try {
+ conn.close();
+ conn = null;
+ } catch (SQLException e) {
+ e.printStackTrace();
+ }
+ }
+ }
+ }
+
+ private static List<Map<String, Object>> convertList(ResultSet rs) throws SQLException {
+ List<Map<String, Object>> list = new ArrayList<>();
+ ResultSetMetaData md = rs.getMetaData();
+ int columnCount = md.getColumnCount();
+ while (rs.next()) {
+ Map<String, Object> rowData = new HashMap<>();
+ for (int i = 1; i <= columnCount; i++) {
+ rowData.put(md.getColumnName(i), rs.getObject(i));
+ }
+ list.add(rowData);
+ }
+ return list;
+ }
+
+ //synchronized
+ public Connection getConn() throws SQLException {
+ Connection conn = null;
+ try {
+ Class.forName(erpDbProperties.getDriver_class_name()).newInstance();
+
+ DriverManager.setLoginTimeout(CONNECTION_TIMEOUT_SECONDS);
+
+ conn = DriverManager.getConnection(erpDbProperties.getUrl(), erpDbProperties.getUsername(), erpDbProperties.getPassword());
+ } catch (Exception e) {
+ log.error("鑾峰彇ERP鏁版嵁搴撹繛鎺ュけ璐�");
+ e.printStackTrace();
+ throw new RuntimeException("鑾峰彇ERP鏁版嵁搴撹繛鎺ュけ璐�");
+ }
+ return conn;
+// if (null == this.conn || this.conn.isClosed()) {
+// try {
+// Class.forName(erpDbProperties.getDriver_class_name()).newInstance();
+// this.conn = DriverManager.getConnection(erpDbProperties.getUr(), erpDbProperties.getUsername(), erpDbProperties.getPassword());
+// } catch (Exception e) {
+// log.error("鑾峰彇ERP鏁版嵁搴撹繛鎺ュけ璐�");
+// e.printStackTrace();
+// throw new RuntimeException("鑾峰彇ERP鏁版嵁搴撹繛鎺ュけ璐�");
+// }
+// }
+// return this.conn;
+ }
+
+ private void release() {
+ if (rs != null) {
+ try {
+ rs.close();
+ } catch (SQLException e) {
+ e.printStackTrace();
+ }
+ }
+ if (pstm != null) {
+ try {
+ pstm.close();
+ } catch (SQLException e) {
+ e.printStackTrace();
+ }
+ }
+// if (conn != null) {
+// try {
+// conn.close();
+// conn = null;
+// } catch (SQLException e) {
+// e.printStackTrace();
+// }
+// }
+ }
+
+ /**
+ * 娣辨嫹璐�
+ */
+ public ArrayList deepClone(List list) throws Exception {
+ // 搴忓垪鍖�
+ ByteArrayOutputStream bos = new ByteArrayOutputStream();
+ ObjectOutputStream oos = new ObjectOutputStream(bos);
+
+ oos.writeObject(list);
+
+ // 鍙嶅簭鍒楀寲
+ ByteArrayInputStream bis = new ByteArrayInputStream(bos.toByteArray());
+ ObjectInputStream ois = new ObjectInputStream(bis);
+
+ return (ArrayList) ois.readObject();
+ }
+
+}
--
Gitblit v1.9.1