DBUtil工具类
IRowMapper接口
import java.sql.ResultSet;
@FunctionalInterface
public interface IRowMapper{
void mapRow(ResultSet resultSet);
}
DBUtil类
import java.sql.*;
/**
* @author venking
*/
public class DBUtil {
static {
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
private DBUtil() { }
/**
* 执行SQL查询，返回一个ResultSet对象,使用完毕后记得调用closeAll方法关闭资源
* @param sql SQL语句
* @return 根据SQL语句查询到的ResultSet
*/
public static ResultSet select(String sql){
try{
Connection connection = getConnection();
Statement statement = connection.createStatement();
return statement.executeQuery(sql);
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
/**
* 以回调的方式查询并处理数据
* @param sql SQL语句
* @param rowMapper 处理数据的对象
*/
public static void select(String sql,IRowMapper rowMapper){
try(
Connection connection = getConnection();
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery(sql)
){
rowMapper.mapRow(resultSet);
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
* 以预处理的方式，查询结果，并返回包含查询结果的ResultSet，使用完Result之后记得调用closeAll()关闭资源
* @param sql 要查询的SQL语句
* @param objects 要传入的参数
* @return 返回一个包含查询数据的ResultSet
*/
public static ResultSet preparedSelect(String sql,Object...objects){
try {
Connection connection = getConnection();
PreparedStatement preparedStatement = connection.prepareStatement(sql);
for (int i = 0; i < objects.length; i++) {
preparedStatement.setObject(i+1,objects[i]);
}
return preparedStatement.executeQuery();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return null;
}
/**
* 使用PreparedStatement查询并使用RowMapper处理数据
* @param sql SQL语句
* @param rowMapper 回调接口IRowMapper的实现对象
* @param objects PrepareStatement参数列表
*/
public static void preparedSelect(String sql, IRowMapper rowMapper,Object...objects){
ResultSet resultSet = preparedSelect(sql,objects);
rowMapper.mapRow(resultSet);
closeAll(resultSet);
}
/**
* 判断用户名是否存在
* @param userName 要判断的用户名
* @return 存在返回true,否则返回false
*/
public static boolean exists(String userName){
return exists("user_name",userName);
}
/**
* 判断指定列名里面是包含有指定的值
* @param columnName 列名
* @param value 值
* @return 如果有，返回true，否则返回false，异常时会返回true
*/
public static boolean exists(String columnName,String value){
String sql = "select id from user_info where " + columnName + " = '" + value + "'";
ResultSet resultSet = select(sql);
try {
return resultSet != null && resultSet.next();
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
closeAll(resultSet);
}
return false;
}
/**
* 采用防止SQL注入的方式，判断用户名是否存在，存在返回true,否则返回false
* @param userName 要判断的用户名
* @return 存在返回true，否则返回false
*/
public static boolean preparedExists(String userName){
return preparedExists("user_name",userName);
}
/**
* 采用防止SQL注入的方式，判断指定列名里面是包含有指定的值
* @param columnName 列名
* @param value 值
* @return 如果有，返回true，否则返回false
*/
public static boolean preparedExists(String columnName, String value){
String sql = "select id from user_info where +" + columnName +"= ?";
ResultSet resultSet = preparedSelect(sql,value);
try {
return resultSet != null && resultSet.next();
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
closeAll(resultSet);
}
return false;
}
/**
* 获取数据库连接
* @return Connection对象
* @throws SQLException 获取连接时可能会发生错误，抛出一个检查时异常SQLException
*/
public static Connection getConnection() throws SQLException {
String url = PropertiesUtil.getValue("url");
String user = PropertiesUtil.getValue("user");
String password = PropertiesUtil.getValue("password");
return DriverManager.getConnection(url,user,password);
}
/**
* 执行DML语句，根据受影响的行数返回true或者false
* @param sql SQL语句
* @return 受影响的行数 > 0 返回true，否则返回false
*/
public static boolean update(String sql){
try (
Connection connection = getConnection();
Statement statement = connection.createStatement()
){
int effect = statement.executeUpdate(sql);
return effect > 0;
} catch (SQLException throwable) {
throwable.printStackTrace();
}
return false;
}
/**
* 以PreparedStatement的方式执行DML语句，并根据受影响的行数返回true或者false
* @param sql SQL语句
* @param objects PrepareStatement中的参数
* @return 受影响的行数 > 0 返回true，否则返回false
*/
public static boolean preparedUpdate(String sql,Object...objects){
try (
Connection connection = getConnection();
PreparedStatement preparedStatement = connection.prepareStatement(sql)
){
for (int i = 0; i < objects.length; i++) {
preparedStatement.setObject(i + 1, objects[i]);
}
int effect = preparedStatement.executeUpdate();
return effect > 0;
} catch (SQLException throwable) {
throwable.printStackTrace();
}
return false;
}
public static boolean isEmpty(ResultSet resultSet){
try {
return resultSet != null && resultSet.next();
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
closeAll(resultSet);
}
return false;
}
/**
* 根据传入的ResultSet获取Statement和Connection，关闭这三个资源
*/
public static void closeAll(ResultSet resultSet){
try {
close(resultSet, resultSet.getStatement(), resultSet.getStatement().getConnection());
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
/**
* 关闭ResultSet资源
* @param resultSet 要关闭的ResultSet
*/
private static void close(ResultSet resultSet){
if (resultSet != null){
try {
resultSet.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
private static void close(Statement statement, Connection connection){
if(statement != null){
try {
statement.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(connection != null){
try {
connection.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
private static void close(ResultSet resultSet,Statement statement, Connection connection){
close(resultSet);
close(statement,connection);
}
public static void main(String[] args) {
System.out.println(DBUtil.preparedExists("admin"));
System.out.println(DBUtil.exists("admin"));
}
}
