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;
|
// 创建预编译语句对象，一般都是用这个而不用Statement
|
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);
|
}
|
// 其他类型可以类似处理
|
}
|
}
|
|
/**
|
* 扫描所有实力类属性里的TableField注解,并将里面的值作为column
|
*
|
* @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;
|
PreparedStatement pstm = null;
|
ResultSet rs = null;
|
try {
|
conn = getConn();
|
pstm = conn.prepareStatement(sql);
|
pstm.setQueryTimeout(QUERY_TIMEOUT_SECONDS);
|
rs = pstm.executeQuery();
|
|
// ✅ 先把 rs 转换完再关闭连接
|
return convertList(rs);
|
|
} catch (Exception e) {
|
e.printStackTrace();
|
return null;
|
} finally {
|
// ❗不能提前调用 release()，改为手动释放各对象
|
try {
|
if (rs != null) {
|
rs.close();
|
rs = null;
|
}
|
if (pstm != null) {
|
pstm.close();
|
pstm = null;
|
}
|
if (conn != null) {
|
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中间表失败===>>数据库连接conn为空");
|
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();
|
}
|
|
}
|
