import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
/**
* @author yechengling
* @date 2022/5/24
*/
public class JdbcEngine {
/**
* 获取一个连接
* @return
* @throws Exception
*/
public static Connection getConnection() throws Exception {
InputStream is = JdbcEngine.class.getResourceAsStream("/jdbc.properties");
Properties properties = new Properties();
properties.load(is);
String user = properties.getProperty("user");
String password = properties.getProperty("password");
String url = properties.getProperty("url");
String driverClass = properties.getProperty("driverClass");
// 2. 加载驱动
Class.forName(driverClass);
Connection connection = DriverManager.getConnection(url, user, password);
return connection;
}
/**
* 关闭连接
* @param connection
* @param statement
*/
public static void closeConnection(Connection connection, Statement statement) {
if (connection != null){
try {
connection.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (statement != null){
try {
statement.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
/**
* 关闭连接
* @param connection
* @param statement
*/
public static void closeConnection(Connection connection, Statement statement, ResultSet resultSet) {
if (resultSet != null){
try {
resultSet.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (connection != null){
try {
connection.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (statement != null){
try {
statement.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
public static JSONArray query(Connection connection, String sql) {
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
JSONArray jsonArray = null;
try {
// 2.预编译一个sql语句,返回一个PrepareStatement对象
preparedStatement = connection.prepareStatement(sql);
// 4。执行sql,得到结果集
resultSet = preparedStatement.executeQuery();
// 5.获取元数据
ResultSetMetaData metaData = resultSet.getMetaData();
int columnCount = metaData.getColumnCount();
jsonArray = new JSONArray();
// 7.遍历得到每一行数据
while (resultSet.next()){
JSONObject row = new JSONObject();
for (int i = 0; i < columnCount; i++) {
// 7.1获取列值
Object object = resultSet.getObject(i + 1);
// 7.2获取列别名
String columnLabel = metaData.getColumnLabel(i + 1);
row.put(columnLabel,object);
}
jsonArray.add(row);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
closeConnection(null, preparedStatement, resultSet);
}
return jsonArray;
}
public static int update(Connection connection, String sql){
PreparedStatement preparedStatement = null;
try {
// 2.预编译sql,返回一个PrepareStatement实例
preparedStatement = connection.prepareStatement(sql);
// 4.执行
return preparedStatement.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally {
// 5.关闭资源
closeConnection(null, preparedStatement);
}
return 0;
}
}
JDBC工具类
最新推荐文章于 2024-03-11 21:29:57 发布