From c7884cb7dd4b50b1ae37fc956493e577a8535aed Mon Sep 17 00:00:00 2001
From: luxiaotao1123 <t1341870251@163.com>
Date: 星期二, 25 七月 2023 14:09:15 +0800
Subject: [PATCH] #
---
src/main/java/com/zy/crm/common/web/DatavController.java | 41 ++++++++
src/main/java/com/zy/crm/common/service/DbSqlServer.java | 192 ++++++++++++++++++++++++++++++++++++++
src/main/java/com/zy/crm/common/properties/DbProperties.java | 30 ++++++
src/main/resources/application.yml | 6 +
4 files changed, 269 insertions(+), 0 deletions(-)
diff --git a/src/main/java/com/zy/crm/common/properties/DbProperties.java b/src/main/java/com/zy/crm/common/properties/DbProperties.java
new file mode 100644
index 0000000..4e17286
--- /dev/null
+++ b/src/main/java/com/zy/crm/common/properties/DbProperties.java
@@ -0,0 +1,30 @@
+package com.zy.crm.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 = "db")
+@Component
+public class DbProperties {
+
+ 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/crm/common/service/DbSqlServer.java b/src/main/java/com/zy/crm/common/service/DbSqlServer.java
new file mode 100644
index 0000000..ef20ea2
--- /dev/null
+++ b/src/main/java/com/zy/crm/common/service/DbSqlServer.java
@@ -0,0 +1,192 @@
+package com.zy.crm.common.service;
+
+import com.core.common.Cools;
+import com.zy.crm.common.properties.DbProperties;
+import lombok.extern.slf4j.Slf4j;
+import org.springframework.beans.factory.annotation.Autowired;
+import org.springframework.stereotype.Service;
+
+import java.sql.*;
+import java.util.ArrayList;
+import java.util.HashMap;
+import java.util.List;
+import java.util.Map;
+
+@Slf4j
+@Service
+@SuppressWarnings("all")
+public class DbSqlServer {
+
+// private Connection conn;
+ private PreparedStatement pstm = null;
+ private ResultSet rs = null;
+
+ @Autowired
+ private DbProperties dbProperties;
+
+ /*****************************************************************************/
+ /********************************** 灏佽 **********************************/
+ /*****************************************************************************/
+
+ /**
+ * 鏌ヨ
+ */
+ 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();
+ 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();
+ 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("鏇存柊DB涓棿琛ㄥけ璐�===>>" + sql);
+ }
+ return pstm.executeUpdate();
+ } catch (Exception e) {
+ e.printStackTrace();
+ log.error("鏇存柊DB涓棿琛ㄥけ璐�===>>" + sql);
+ 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(dbProperties.getDriver_class_name()).newInstance();
+ conn = DriverManager.getConnection(dbProperties.getUr(), dbProperties.getUsername(), dbProperties.getPassword());
+ } catch (Exception e) {
+ log.error("鑾峰彇DB鏁版嵁搴撹繛鎺ュけ璐�");
+ e.printStackTrace();
+ throw new RuntimeException("鑾峰彇DB鏁版嵁搴撹繛鎺ュけ璐�");
+ }
+ return 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();
+// }
+// }
+ }
+
+}
diff --git a/src/main/java/com/zy/crm/common/web/DatavController.java b/src/main/java/com/zy/crm/common/web/DatavController.java
new file mode 100644
index 0000000..4994858
--- /dev/null
+++ b/src/main/java/com/zy/crm/common/web/DatavController.java
@@ -0,0 +1,41 @@
+package com.zy.crm.common.web;
+
+import com.core.common.R;
+import org.springframework.web.bind.annotation.GetMapping;
+import org.springframework.web.bind.annotation.RestController;
+
+/**
+ * Created by vincent on 2023/7/25
+ */
+@RestController
+public class DatavController {
+
+ @GetMapping("/1test")
+ public R test1() {
+ /**
+ * select
+ * (case when A.org='1002208180000267' then '鍢夊杽'
+ * when A.org='1002208180000506' then '骞垮痉'
+ * else '' end) as 缁勭粐,
+ * A.DocNo as 鍗曞彿,A3.Code as 椤圭洰鍙�,
+ * (case when A.DocState='0' then '寮�绔�'
+ * when A.DocState='1' then '宸插鏍�'
+ * when A.DocState='2' then '寮�宸�'
+ * when A.DocState='3' then '瀹屽伐'
+ * when A.DocState='4' then '鏍稿噯涓�'
+ * ELSE'' end) as 鐘舵��,
+ * A.StartDate as 璁″垝寮�宸ユ棩,
+ * A.CompleteDate as 璁″垝瀹屽伐鏃�,A2.Code as 鏂欏彿,A2.name as 鍝佸悕,A2.SPECS as 瑙勬牸 , a.ProductQty as 鐢熶骇鏁伴噺,TotalCompleteQty as 瀹屽伐鏁伴噺
+ * from MO_MO as A
+ * left join CBO_ItemMaster as A2 on (A.ItemMaster = A2.ID)
+ * left join CBO_Project as A3 on (A.Project = A3.ID)
+ * where
+ * A.MODocType != '1002306121624435' --杩囨护鍐呴儴鐮斿彂椤圭洰
+ * and A.IsWBSTask!=1 --杩囨护鐜板満鏂藉伐浠诲姟
+ * and A.DocState != '3' --杩囨护瀹屽伐鐘舵�佺殑璁㈠崟
+ * ORDER BY A.CompleteDate
+ */
+ return R.ok();
+ }
+
+}
diff --git a/src/main/resources/application.yml b/src/main/resources/application.yml
index 859690c..29da18b 100644
--- a/src/main/resources/application.yml
+++ b/src/main/resources/application.yml
@@ -59,3 +59,9 @@
secret: 84CHL7tF21LbU1qpaP0jn9mIAZP9bv
bucket: tjdt
endpoint: http://oss-cn-hangzhou.aliyuncs.com
+
+db:
+ driver_class_name: com.microsoft.sqlserver.jdbc.SQLServerDriver
+ ur: jdbc:sqlserver://rm-bp13167j4w0m2029meo.sqlserver.rds.aliyuncs.com:1433;databasename=xtyasrs_dual
+ username: u9c
+ password: Zhongyang@u9c$
--
Gitblit v1.9.1