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();
|
// }
|
// }
|
}
|
|
}
|
