JDBC的使用繁琐复杂,这里提供两个工具类仿照Mybatis对JDBC进行快速调用
1.创建一个resource包,添加jdbc.properties配置文件
输入你的数据库名和账号密码,在your_db替换为你的数据库名称
jdbc.url=jdbc:mysql://localhost:3306/your_db?serverTimezone=UTC
jdbc.username=root
jdbc.password=123456
2.创建包装JDBC类
注意,现在JDBC连接时不需要进行Class.forName操作,直接连接就行:
执行查询的结果将会得到一个ResultSet
public class JdbcWrapper {
private static Connection connection;
private static Properties properties;
private static Statement statement;
private static PreparedStatement preparedStatement;
//数据库连接
public static void connect() throws SQLException, IOException {
properties = new Properties();
//在这里填入你的jdbc配置文件路径
FileInputStream fis = new
FileInputStream("jdbc.properties");
properties.load(fis);
String url = properties.getProperty("jdbc.url");
String username = properties.getProperty("jdbc.username");
String password = properties.getProperty("jdbc.password");
connection = DriverManager.getConnection(url, username, password);
}
//不传入参数的sql
public static ResultSet executeQuery(String sql) throws SQLException {
statement = connection.createStatement();
return statement.executeQuery(sql);
}
//执行查询语句
public static ResultSet executeQuery(String sql, Object... params) throws SQLException {
preparedStatement = connection.prepareStatement(sql);
for (int i = 0; i < params.length; i++) {
preparedStatement.setObject(i + 1, params[i]);
}
return preparedStatement.executeQuery();
}
//执行更新语句
public static int executeUpdate(String sql, Object... params) throws SQLException {
PreparedStatement statement = connection.prepareStatement(sql);
for (int i = 0; i < params.length; i++) {
statement.setObject(i + 1, params[i]);
}
return statement.executeUpdate();
}
//关闭所有JDBC连接
public static void close() {
try {
if (statement != null) {
statement.close();
}
if (preparedStatement != null){
preparedStatement.close();
}
if (connection != null) {
connection.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
3.创建映射工具类ResultSetMapper
//指定返回的类型泛型
public class ResultSetMapper<T> {
private final Class<T> clazz;
public ResultSetMapper(Class<T> clazz) {
this.clazz = clazz;
}
//返回单个查询结果
public T mapSingleResult(ResultSet resultSet) throws SQLException {
if (resultSet.next()) {
return mapRow(resultSet);
}
return null;
}
//返回集合查询结果
public List<T> mapResultList(ResultSet resultSet) throws SQLException {
List<T> resultList = new ArrayList<>();
while (resultSet.next()) {
T object = mapRow(resultSet);
resultList.add(object);
}
return resultList;
}
//对类进行反射操作来映射
private T mapRow(ResultSet resultSet) throws SQLException {
try {
//通过构造器new出类型
T object = clazz.getDeclaredConstructor().newInstance();
//获取MetaData中的所有数据,也就是查询出的键和值
ResultSetMetaData metaData = resultSet.getMetaData();
int columnCount = metaData.getColumnCount();
//对这些进行对属性的一一映射
for (int i = 1; i <= columnCount; i++) {
String columnName = metaData.getColumnName(i);
Object columnValue = resultSet.getObject(i);
Field field = clazz.getDeclaredField(columnName);
field.setAccessible(true);
field.set(object, columnValue);
}
return object;
} catch (Exception e) {
throw new SQLException("映射类失败: " + clazz.getSimpleName(), e);
}
}
}
4.使用方法:
假设当前MySQL中存在表user,字段name,password
创建类:User,注意进行实现序列化的接口
public class User implements Serializable{
private static final long serialVersionUID = 52028138999216232L;
private String name;
private String password;
//你的get和set方法
}
对不需要传参的sql语句:这里用查询所有user数据举例
public List<User> getAllUser(){
ResultSet resultSet = null;
List<User> users = null;
try {
//输入sql语句,得到resultSet
resultSet = JdbcWrapper.executeQuery("select * from user");
//对resultSet进行映射,指定泛型
users = newsMapper.mapResultList(resultSet);
} catch (SQLException e) {
throw new RuntimeException(e);
}
return users;
}
结果返回一个User的List,这里的newsMapper是外面new的ResultSetMappet类。
对需要传参的语句:这里以用用户名删除用户举例
public boolean delUser(String name){
int done = -1;
try {
//执行语句
done = JdbcWrapper.executeUpdate("DELETE FROM user WHERE name = ?", name);
} catch (SQLException e) {
e.printStackTrace();
}
return done!=-1;
}
此处?为占位符,将会和后面传入的参数进行拼接再执行,例如,若传入的name值为admin.
则执行的sql语句为delete from user where name = admin
其他语句同理,现在你可以试着去写一个demo了