package com.zy.common.service.erp;
|
|
import com.core.common.Cools;
|
import com.zy.common.properties.ErpDbProperties;
|
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;
|
|
/**
|
* 简单持久层框架
|
* Created by vincent on 2020/11/26
|
*/
|
@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) {
|
try {
|
Connection conn = getConn();
|
pstm = conn.prepareStatement(sql);
|
rs = pstm.executeQuery();
|
return convertList(rs);
|
} catch (Exception e) {
|
e.printStackTrace();
|
return null;
|
} finally {
|
release();
|
}
|
}
|
|
private int executeUpdate(String sql) {
|
try {
|
Connection conn = getConn();
|
pstm = conn.prepareStatement(sql);
|
return pstm.executeUpdate();
|
} catch (Exception e) {
|
e.printStackTrace();
|
return 0;
|
} finally {
|
release();
|
}
|
}
|
|
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;
|
}
|
|
private Connection getConn() {
|
if (null == this.conn) {
|
try {
|
Class.forName(erpDbProperties.getDriver_class_name()).newInstance();
|
this.conn = DriverManager.getConnection(erpDbProperties.getUr(), erpDbProperties.getUsername(), erpDbProperties.getPassword());
|
} catch (Exception e) {
|
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();
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
}
|
}
|
|
}
|
