详述DBUtil工具类
一、IRowMapper接口
package util;
import java.sql.ResultSet;
public interface IRowMapper {
void RowMapper(ResultSet resultSet);
}
二、 DBUtil工具类
package util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/**
* 数据库工具类
*
* @author 王豪斌
*/
public class DBUtil {
static {
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}//1、加载驱动程序类
}
/**
* 获取数据库连接
*
* @author 王豪斌
*/
public static Connection getConnection() {
try {
String url = PropertiesUtil.getValue("jdbc.url");
String username = PropertiesUtil.getValue("jdbc.username");
String password = PropertiesUtil.getValue("jdbc.password");
Connection connection = DriverManager.getConnection(url, username, password);//2、获取连接
return connection;
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
/**
* 修改数据
*
* @author 王豪斌
*/
public static boolean update(String sql) {
Connection connection = null;
Statement statement = null;
try {
connection = getConnection();
statement = connection.createStatement();//3、创建语句
int effectRows = statement.executeUpdate(sql);//4、执行语句
return effectRows > 0;//5、处理结果
} catch (Exception e) {
e.printStackTrace();
}finally {
close(statement,connection);
}
return false;
}
/**
* 修改数据
*
* @author 王豪斌
*/
public static boolean update(String sql,Object...prams) {
Connection connection = null;
Statement statement = null;
try {
connection = getConnection();
PreparedStatement preparedStatement= connection.prepareStatement(sql);//3、创建语句
for(int i = 1;i<=prams.length;i++) {
preparedStatement.setObject(i, prams[i-1]);
}
int effectRows = preparedStatement.executeUpdate();
return effectRows > 0;
} catch (Exception e) {
e.printStackTrace();
}finally {
close(statement,connection);
}
return false;
}
/**
* 判断数据是否存在
*
* @author 王豪斌
*/
public static boolean exist(String sql) {
class RowMapper implements IRowMapper{
boolean state;
@Override
public void RowMapper(ResultSet resultSet) {
try {
state = resultSet.next();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
RowMapper rowMapper = new RowMapper();
select(sql, rowMapper);
return rowMapper.state;
}
/**
* 判断数据是否存在
*
* @author 王豪斌
*/
public static boolean exist(String sql,Object...prams) {
class RowMapper implements IRowMapper{
boolean state;
@Override
public void RowMapper(ResultSet resultSet) {
try {
state = resultSet.next();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
RowMapper rowMapper = new RowMapper();
select(sql, rowMapper, prams);
return rowMapper.state;
}
/**
* 查询数据
*
* @author 王豪斌
*/
public static void select(String sql,IRowMapper rowMapper) {
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
connection = getConnection();
statement = connection.createStatement();//3、创建sql语句
resultSet = statement.executeQuery(sql);//4、执行语句
rowMapper.RowMapper(resultSet);
} catch (Exception e) {
e.printStackTrace();
}finally {
close(resultSet,statement,connection);
}
}
/**
* 查询数据
*
* @author 王豪斌
*/
public static void select(String sql,IRowMapper rowMApper,Object...prams) {
Connection connection = null;//2、获得连接
Statement statement = null;//3、创建语句
ResultSet resultSet = null;
try {
connection = getConnection();
PreparedStatement preparedStatement= connection.prepareStatement(sql);//3、创建语句
for(int i = 1;i<=prams.length;i++) {
preparedStatement.setObject(i, prams[i-1]);
}
resultSet = preparedStatement.executeQuery();
rowMApper.RowMapper(resultSet);
} catch (Exception e) {
e.printStackTrace();
}finally {
close(resultSet,statement,connection);
}
}
/**
* 批量处理
*
* @author 王豪斌
*/
public static boolean batch(String...strings) {
Connection connection = null;
Statement statement = null;
try {
connection = getConnection();
connection.setAutoCommit(false);
statement = connection.createStatement();
for (String string : strings) {
statement.addBatch(string);
}
statement.executeBatch();
connection.commit();
return true;
} catch (Exception e) {
if (connection!=null) {
try {
connection.rollback();
return false;
} catch (SQLException e1) {
e1.printStackTrace();
}
}
e.printStackTrace();
}finally {
close(statement, connection);
}
return false;
}
/**
* 批量处理
*
* @author 王豪斌
*/
public static boolean batch(String sql,Object[]...params) {
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
connection = getConnection();
connection.setAutoCommit(false);;
preparedStatement = connection.prepareStatement(sql);
for(int i = 0;i < params.length;i++) {
for(int j = 1;j <= params[i].length;j++) {
preparedStatement.setObject(j, params[i][j-1]);
}
preparedStatement.addBatch();
}
preparedStatement.executeBatch();
connection.commit();
return true;
} catch (Exception e) {
if (connection!=null) {
e.printStackTrace();
try {
connection.rollback();
System.out.println(111);
return false;
} catch (SQLException e1) {
e1.printStackTrace();
}
}
}finally {
close(preparedStatement, connection);
}
return false;
}
/**
* 释放资源
*
* @author 王豪斌
*/
public static void close(Statement statement,Connection connection) {
if (statement != null) {
try {
statement.close();
} catch (Exception e) {
e.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
/**
* 释放资源
*
* @author 王豪斌
*/
public static void close(ResultSet resultSet,Statement statement,Connection connection) {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}