From 6559c51b0ddc00a14a7589bc57bbfec4a01a0b2f Mon Sep 17 00:00:00 2001
From: luxiaotao1123 <t1341870251@63.com>
Date: 星期四, 08 十二月 2022 11:16:15 +0800
Subject: [PATCH] #

---
 src/main/java/com/zy/common/service/erp/ErpService.java        |   22 +++
 src/main/java/com/zy/common/service/erp/task/ErpScheduler.java |   27 ++++
 src/main/java/com/zy/common/service/erp/ErpSqlServer.java      |  234 +++++++++++++++++++++++++++++++++++++++
 src/main/java/com/zy/common/properties/ErpDbProperties.java    |   30 +++++
 src/main/resources/application.yml                             |    7 +
 5 files changed, 320 insertions(+), 0 deletions(-)

diff --git a/src/main/java/com/zy/common/properties/ErpDbProperties.java b/src/main/java/com/zy/common/properties/ErpDbProperties.java
new file mode 100644
index 0000000..d389a51
--- /dev/null
+++ b/src/main/java/com/zy/common/properties/ErpDbProperties.java
@@ -0,0 +1,30 @@
+package com.zy.common.properties;
+
+import lombok.Data;
+import org.springframework.boot.context.properties.ConfigurationProperties;
+import org.springframework.stereotype.Component;
+
+/**
+ * Created by vincent on 2020/11/26
+ */
+@Data
+@ConfigurationProperties(prefix = "erp.db")
+@Component
+public class ErpDbProperties {
+
+    private String driver_class_name;
+
+    private String ur;
+
+    private String username;
+
+    private String password;
+
+//    {
+//        driver_class_name = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
+//        ur = "jdbc:sqlserver://127.0.0.1:1433;databasename=byrk";
+//        username = "sa";
+//        password = "sa@123";
+//    }
+
+}
diff --git a/src/main/java/com/zy/common/service/erp/ErpService.java b/src/main/java/com/zy/common/service/erp/ErpService.java
new file mode 100644
index 0000000..a3439b2
--- /dev/null
+++ b/src/main/java/com/zy/common/service/erp/ErpService.java
@@ -0,0 +1,22 @@
+package com.zy.common.service.erp;
+
+import lombok.extern.slf4j.Slf4j;
+import org.springframework.beans.factory.annotation.Autowired;
+import org.springframework.jdbc.core.JdbcTemplate;
+import org.springframework.stereotype.Service;
+
+/**
+ * Created by vincent on 2020/11/27
+ */
+@Slf4j
+@Service
+public class ErpService {
+
+    @Autowired
+    private ErpSqlServer erpSqlServer;
+    @Autowired
+    private JdbcTemplate jdbcTemplate;
+
+    //////////////////////鎴愬搧銆佸師鏉愭枡鍏ュ簱閫氱煡鍗曟暟鎹彁鍙�//////////////////////////////////////////////////
+
+}
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..37c7b15
--- /dev/null
+++ b/src/main/java/com/zy/common/service/erp/ErpSqlServer.java
@@ -0,0 +1,234 @@
+package com.zy.common.service.erp;
+
+import com.core.common.Cools;
+import com.zy.common.properties.ErpDbProperties;
+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.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 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) {
+                list.add(Cools.conver(entity, cls));
+            }
+        }
+        return list;
+    }
+
+    public List<Map<String, Object>> select(String sql) {
+        return executeQuery(sql);
+    }
+
+    /**
+     * 淇敼
+     */
+    public int update(String sql) {
+        return executeUpdate(sql);
+    }
+
+    /*****************************************************************************/
+    /*********************************   鏍稿績灞�   *********************************/
+    /*****************************************************************************/
+
+
+    private List<Map<String, Object>> executeQuery(String sql) {
+        Connection conn = null;
+        try {
+            conn = getConn();
+            pstm = conn.prepareStatement(sql);
+            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);
+            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);
+            } else {
+                log.error("鏇存柊ERP涓棿琛ㄥけ璐�===>>鏁版嵁搴撹繛鎺onn涓虹┖");
+                log.error("鏇存柊ERP涓棿琛ㄥけ璐�===>>" + sql);
+            }
+            return pstm.executeUpdate();
+        } catch (Exception e) {
+            e.printStackTrace();
+            log.error("鏇存柊ERP涓棿琛ㄥけ璐�===>>" + sql);
+            log.error("鏇存柊ERP涓棿琛ㄥけ璐�===>>" + 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();
+            conn = DriverManager.getConnection(erpDbProperties.getUr(), 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();
+    }
+
+}
diff --git a/src/main/java/com/zy/common/service/erp/task/ErpScheduler.java b/src/main/java/com/zy/common/service/erp/task/ErpScheduler.java
new file mode 100644
index 0000000..2ce259c
--- /dev/null
+++ b/src/main/java/com/zy/common/service/erp/task/ErpScheduler.java
@@ -0,0 +1,27 @@
+package com.zy.common.service.erp.task;
+
+import com.zy.asrs.task.AbstractHandler;
+import com.zy.common.service.erp.ErpService;
+import lombok.Synchronized;
+import lombok.extern.slf4j.Slf4j;
+import org.springframework.beans.factory.annotation.Autowired;
+import org.springframework.scheduling.annotation.Scheduled;
+import org.springframework.stereotype.Component;
+
+/**
+ * erp浠诲姟鎺у埗鍣�
+ * Created by vincent on 2020/11/27
+ */
+@Slf4j
+@Component
+public class ErpScheduler extends AbstractHandler<Exception> {
+
+    @Autowired
+    private ErpService erpService;
+
+    @Scheduled(cron = "0/30 * * * * ? ")
+    @Synchronized
+    public void syncMat() {
+    }
+
+}
diff --git a/src/main/resources/application.yml b/src/main/resources/application.yml
index 3ac6f54..36065e3 100644
--- a/src/main/resources/application.yml
+++ b/src/main/resources/application.yml
@@ -56,3 +56,10 @@
 store:
   bufferCount: 2
 
+erp:
+  db:
+    driver_class_name: com.microsoft.sqlserver.jdbc.SQLServerDriver
+    ur: jdbc:sqlserver://10.20.46.23:1433;databasename=xtyasrs_dual
+    username: poy-asrs
+    password: Poy@2022
+

--
Gitblit v1.9.1