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