总共4个public方法:增删改查
package com.xy;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.*;
/**
* @ClassName JdbcUtil
* @Description
* @Author xy
* @Date 2022/4/22
* @Version 1.0
**/
public class JdbcUtil {
private static String driver = "com.mysql.jdbc.Driver";
private static String url = "jdbc:mysql://localhost:3306/mysql"; //连接数据库(mysql是数据库名)
private static String user = "root";//连接mysql的用户名
private static String password = "123";//连接mysql的密码
private static Connection conn = null;
static {
try {
// readProperties();
/*
jdk1.6以后会去读取mysql META-INF/services/java.sql.Driver下自动注册
mysqL驱动5.1.6版本会有META-INF/services/java.sql.Driver
*/
// jdk1.6、mysql 5.1.6以后不再需要手动加载 Class.forName(driver);
} catch (Exception e) {
e.printStackTrace();
}
}
private static void readProperties() throws IOException {
//读取配置文件
InputStream in = JdbcUtil.class.getResourceAsStream("db.properties");
Properties properties = new Properties();
//加载配置文件
properties.load(in);
//获取配置文件中的数据
driver = properties.getProperty("driver");
url = properties.getProperty("url");
user = properties.getProperty("user");
password = properties.getProperty("password");
}
/**
* 获取一个数据库链接
*/
private static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url, user, password);
}
private static void closeConn(Connection conn) {
try {
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
private static void execute(String sql) {
try {
conn = getConnection();
conn.setAutoCommit(false);
PreparedStatement preparedStatement = conn.prepareStatement(sql);
preparedStatement.executeUpdate();
conn.commit();
} catch (SQLException e) {
e.printStackTrace();
try {
conn.rollback();
} catch (SQLException ex) {
ex.printStackTrace();
}
} finally {
closeConn(conn);
}
}
/**
* 增加数据
*/
public static void create(String sql) {
// String sql = "INSERT INTO USER (user_name,user_password,user_age) VALUES('老王','123456',18)";
execute(sql);
}
/**
* 删除数据
*/
public static void delete(String sql) {
// String sql = "DELETE FROM USER WHERE USER.user_name = '老王'";
execute(sql);
}
/**
* 修改数据
*/
public static void update(String sql) {
// String sql = "UPDATE USER SET USER.user_name = '老李'";
execute(sql);
}
private static ResultSet getResultSet(String sql) throws SQLException {
PreparedStatement preparedStatement = null;
//执行查询语句并返回结果集
ResultSet resultSet = null;
conn = getConnection();
preparedStatement = conn.prepareStatement(sql);
resultSet = preparedStatement.executeQuery();
return resultSet;
}
public static List<Map<String, Object>> queryForListMap(String sql) {
ResultSet resultSet = null;
List<Map<String, Object>> result = new ArrayList<>();
try {
resultSet = getResultSet(sql);
ResultSetMetaData rsmd = resultSet.getMetaData();//获得结果集结构信息,元数据
int numberOfColumns = rsmd.getColumnCount();//获得列数
while (resultSet.next()) {
Map<String, Object> rowData = new HashMap<>();
for (int j = 1; j <= numberOfColumns; j++) {
rowData.put(rsmd.getColumnName(j), resultSet.getString(j));
}
result.add(rowData);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
closeConn(conn);
}
return result;
}
public static void main(String[] args) {
System.out.println(queryForListMap("select db,user from db"));
}
}
拓展
jdk1.6 使用了jdbc4,以后会去读取mysql META-INF/services/java.sql.Driver下自动注册 mysqL驱动5.1.6版本会有META-INF/services/java.sql.Driver