import com.ruoyi.common.core.domain.entity.SysDept;
import com.ruoyi.common.utils.SecurityUtils;
import org.springframework.cglib.beans.BeanMap;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class JdbcUtil {
//APP泛型
public static <T> List<T> getConnectionTAPP(Object sql,SysDept sysDept, Class<T> cla){
System.out.println("SQLT1:"+sql);
sql = changeSQL(sysDept.getDataUrl(),sql);
System.out.println("SQLT2:"+sql);
List<T> list = new ArrayList<T>();
Connection connection = null;
CallableStatement callableStatement = null;
try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
connection = DriverManager.getConnection(sysDept.getDataUrl(),sysDept.getDataName(),sysDept.getDataPassword());
//编写存储过程语句
String sqlStr = sql.toString();
//设置入参
callableStatement = connection.prepareCall(sqlStr);
//执行
callableStatement.executeQuery();
//获取查询结果
ResultSet rs = callableStatement.getResultSet();
//处理数据
if (null != rs) {
ResultSetMetaData metaData = rs.getMetaData(); // 获得列的结果
List<String> colNameList = new ArrayList<String>();
int cols_len = metaData.getColumnCount(); // 获取总的列数
for (int i = 0; i < cols_len; i++) {
colNameList.add(metaData.getColumnName(i + 1));
}
while (rs.next()) {
Map<String, Object> mapTMP = new HashMap<String, Object>();
for (int i = 0; i < cols_len; i++) {
String key = colNameList.get(i);
Object value = rs.getObject(colNameList.get(i));
mapTMP.put(key, value);
}
Object instance = cla.newInstance();
BeanMap beanMap = BeanMap.create(instance);
beanMap.putAll(mapTMP);
list.add(cla.cast(instance));
}
}
} catch (Exception e) {
e.printStackTrace();
}
return list;
}
//新增和编辑
public static int getConnectionSaveAndUpdateApp(Object sql,SysDept sysDept){
System.out.println("SQLSaveAndUpdate1:"+sql);
sql = changeSQL(sysDept.getDataUrl(),sql);
System.out.println("SQLSaveAndUpdate2:"+sql);
int count = 0;
Connection connection = null;
CallableStatement callableStatement = null;
try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
connection = DriverManager.getConnection(sysDept.getDataUrl(),sysDept.getDataName(),sysDept.getDataPassword());
//编写存储过程语句
String sqlStr = sql.toString();
//设置入参
callableStatement = connection.prepareCall(sqlStr);
//执行
count = callableStatement.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
}
return count;
}
//总条数
public static Long getConnectionCountAPP(Object sql,SysDept sysDept){
System.out.println("SQLCount1:"+sql);
sql = changeSQL(sysDept.getDataUrl(),sql);
System.out.println("SQLCount2:"+sql);
Long count = 0L;
Connection connection = null;
CallableStatement callableStatement = null;
try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
connection = DriverManager.getConnection(sysDept.getDataUrl(),sysDept.getDataName(),sysDept.getDataPassword());
//编写存储过程语句
String sqlStr = sql.toString();
//设置入参
callableStatement = connection.prepareCall(sqlStr);
//执行
callableStatement.executeQuery();
//获取查询结果
ResultSet rs = callableStatement.getResultSet();
//处理数据
if (null != rs) {
ResultSetMetaData metaData = rs.getMetaData(); // 获得列的结果
List<String> colNameList = new ArrayList<String>();
int cols_len = metaData.getColumnCount(); // 获取总的列数
for (int i = 0; i < cols_len; i++) {
colNameList.add(metaData.getColumnName(i + 1));
}
while (rs.next()) {
Object value = rs.getString(colNameList.get(0));
count = Long.parseLong(value.toString());
}
}
} catch (Exception e) {
e.printStackTrace();
}
return count;
}
//sql
public static List<Map<String, Object>> getConnectionMap(Object sql){
SysDept sysDept = SecurityUtils.getLoginUser().getUser().getDept();
System.out.println("SQL1:"+sql);
sql = changeSQL(sysDept.getDataUrl(),sql);
System.out.println("SQL2:"+sql);
List<Map<String, Object>> mapObjList = new ArrayList<>();
Connection connection = null;
CallableStatement callableStatement = null;
try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
connection = DriverManager.getConnection(sysDept.getDataUrl(),sysDept.getDataName(),sysDept.getDataPassword());
//编写存储过程语句
String sqlStr = sql.toString();
//设置入参
callableStatement = connection.prepareCall(sqlStr);
//执行
callableStatement.executeQuery();
//获取查询结果
ResultSet rs = callableStatement.getResultSet();
//处理数据
if (null != rs) {
ResultSetMetaData metaData = rs.getMetaData(); // 获得列的结果
List<String> colNameList = new ArrayList<String>();
int cols_len = metaData.getColumnCount(); // 获取总的列数
for (int i = 0; i < cols_len; i++) {
colNameList.add(metaData.getColumnName(i + 1));
}
while (rs.next()) {
Map<String, Object> mapTMP = new HashMap<String, Object>();
for (int i = 0; i < cols_len; i++) {
String key = colNameList.get(i);
Object value = rs.getString(colNameList.get(i));
mapTMP.put(key, value);
}
mapObjList.add(mapTMP);
}
}
} catch (Exception e) {
e.printStackTrace();
}
return mapObjList;
}
//泛型
public static <T> List<T> getConnectionT(Object sql, Class<T> cla){
SysDept sysDept = SecurityUtils.getLoginUser().getUser().getDept();
System.out.println("SQLT1:"+sql);
sql = changeSQL(sysDept.getDataUrl(),sql);
System.out.println("SQLT2:"+sql);
List<T> list = new ArrayList<T>();
Connection connection = null;
CallableStatement callableStatement = null;
try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
connection = DriverManager.getConnection(sysDept.getDataUrl(),sysDept.getDataName(),sysDept.getDataPassword());
//编写存储过程语句
String sqlStr = sql.toString();
//设置入参
callableStatement = connection.prepareCall(sqlStr);
//执行
callableStatement.executeQuery();
//获取查询结果
ResultSet rs = callableStatement.getResultSet();
//处理数据
if (null != rs) {
ResultSetMetaData metaData = rs.getMetaData(); // 获得列的结果
List<String> colNameList = new ArrayList<String>();
int cols_len = metaData.getColumnCount(); // 获取总的列数
for (int i = 0; i < cols_len; i++) {
colNameList.add(metaData.getColumnName(i + 1));
}
while (rs.next()) {
Map<String, Object> mapTMP = new HashMap<String, Object>();
for (int i = 0; i < cols_len; i++) {
String key = colNameList.get(i);
Object value = rs.getObject(colNameList.get(i));
mapTMP.put(key, value);
}
Object instance = cla.newInstance();
BeanMap beanMap = BeanMap.create(instance);
beanMap.putAll(mapTMP);
list.add(cla.cast(instance));
}
}
} catch (Exception e) {
e.printStackTrace();
}
return list;
}
//总条数
public static Long getConnectionCount(Object sql){
SysDept sysDept = SecurityUtils.getLoginUser().getUser().getDept();
System.out.println("SQLCount1:"+sql);
sql = changeSQL(sysDept.getDataUrl(),sql);
System.out.println("SQLCount2:"+sql);
Long count = 0L;
Connection connection = null;
CallableStatement callableStatement = null;
try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
connection = DriverManager.getConnection(sysDept.getDataUrl(),sysDept.getDataName(),sysDept.getDataPassword());
//编写存储过程语句
String sqlStr = sql.toString();
//设置入参
callableStatement = connection.prepareCall(sqlStr);
//执行
callableStatement.executeQuery();
//获取查询结果
ResultSet rs = callableStatement.getResultSet();
//处理数据
if (null != rs) {
ResultSetMetaData metaData = rs.getMetaData(); // 获得列的结果
List<String> colNameList = new ArrayList<String>();
int cols_len = metaData.getColumnCount(); // 获取总的列数
for (int i = 0; i < cols_len; i++) {
colNameList.add(metaData.getColumnName(i + 1));
}
while (rs.next()) {
Object value = rs.getString(colNameList.get(0));
count = Long.parseLong(value.toString());
}
}
} catch (Exception e) {
e.printStackTrace();
}
return count;
}
//新增和编辑
public static int getConnectionSaveAndUpdate(Object sql){
SysDept sysDept = SecurityUtils.getLoginUser().getUser().getDept();
System.out.println("SQLSaveAndUpdate1:"+sql);
sql = changeSQL(sysDept.getDataUrl(),sql);
System.out.println("SQLSaveAndUpdate2:"+sql);
int count = 0;
Connection connection = null;
CallableStatement callableStatement = null;
try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
connection = DriverManager.getConnection(sysDept.getDataUrl(),sysDept.getDataName(),sysDept.getDataPassword());
//编写存储过程语句
String sqlStr = sql.toString();
//设置入参
callableStatement = connection.prepareCall(sqlStr);
//执行
count = callableStatement.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
}
return count;
}
//删除
public static int getConnectionDelete(Object sql){
SysDept sysDept = SecurityUtils.getLoginUser().getUser().getDept();
System.out.println("SQLCount1:"+sql);
sql = changeSQL(sysDept.getDataUrl(),sql);
System.out.println("SQLCount2:"+sql);
int count = 0;
Connection connection = null;
CallableStatement callableStatement = null;
try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
connection = DriverManager.getConnection(sysDept.getDataUrl(),sysDept.getDataName(),sysDept.getDataPassword());
//编写存储过程语句
String sqlStr = sql.toString();
//设置入参
callableStatement = connection.prepareCall(sqlStr);
//执行
count = callableStatement.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
}
return count;
}
//指定SQL中的表
public static Object changeSQL(String url,Object sql){
String targetStr = url.substring(url.indexOf("DatabaseName=")+13,url.length());
String sqlOrg = sql.toString();
sqlOrg = sqlOrg.replaceAll("XXXX.dbo",targetStr+".dbo");
sql = sqlOrg;
return sql;
}
}