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;
|
// 创建预编译语句对象，一般都是用这个而不用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) {
|
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中间表失败===>>数据库连接conn为空");
|
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();
|
}
|
|
}
|
