@Component
@Slf4j
public class ConnectSQLServer {
@Autowired
private SQLServerProperties properties;
/**
* 连接数据库
*
* @param sql
* @param clzz
* @param <T>
* @return
*/
public <T> List<T> querySQL(String sql, Class<T> clzz, Object... params) {
List<T> result = this.connectSQLServerQuery(properties.getDriverClassName(), properties.getUrl(),
properties.getUsername(), properties.getPassword(), sql, clzz, params);
return result;
}
/**
*
* 在连接数据库加密问题,在我们使用的url后面加上一个encrypt=false或者encrypt=true;trustServerCertificate=true
* @param driver
* @param dbUrl
* @param userName
* @param password
* @param sql
* @param clzz
* @param params
* @return
* @param <T>
*/
public static <T> List<T> connectSQLServerQuery(String driver, String dbUrl, String userName, String password, String sql, Class<T> clzz, Object... params) {
Connection conn = null;
ResultSet resultSet = null;
Statement statement = null;
PreparedStatement preparedStatement = null;
List<T> commonList = new ArrayList<>();
try {
Class.forName(driver);
conn = DriverManager.getConnection(dbUrl, userName, password);
// statement = conn.createStatement();
//预编译
preparedStatement = conn.prepareStatement(sql);
// 根据参数列表动态设置 PreparedStatement 参数
for (int i = 0; i < params.length; i++) {
preparedStatement.setObject(i + 1, params[i]);
}
resultSet = preparedStatement.executeQuery();
// resultSet = statement.executeQuery(sql);
while (resultSet.next()) {
ResultSetMetaData metaData = resultSet.getMetaData();
int columnCount = metaData.getColumnCount();
JSONObject jsonObject = new JSONObject();
for (int i = 1; i <= columnCount; i++) {
String columnName = metaData.getColumnName(i);
Object columnValue = resultSet.getObject(columnName);
jsonObject.put(columnName, columnValue);
}
commonList.add(JSONObject.toJavaObject(jsonObject, clzz));
// commonList.add(resultSet.unwrap(clzz));
}
log.info("connectSQLServerQuery result:{}", commonList);
return commonList;
}
catch (Exception e) {
log.error("executeSQL error", e);
return commonList;
}
finally {
//释放资源
if (resultSet != null) {
try {
resultSet.close();
}
catch (SQLException e) {
log.error("executeSQL resultSet.close error", e);
}
}
if (statement != null) {
try {
statement.close();
}
catch (SQLException e) {
log.error("executeSQL statement.close error", e);
}
}
if (conn != null) {
try {
conn.close();
}
catch (SQLException e) {
log.error("executeSQL connection.close error", e);
}
}
}
}
}