import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
/**
*
* 数据库工具类
*
* @author 刘晓阳
*/
public class DBUtil {
static {
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
/**
*
* 建立连接
*
* @author 刘晓阳
*/
public static Connection getConnection() {
try {
String url = PropertiesUtil.getValue("jdbc.url");
String username = PropertiesUtil.getValue("jdbc.username");
String password = PropertiesUtil.getValue("jdbc.password");
return DriverManager.getConnection(url, username, password);
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
/**
*
* 判断数据是否存在
*
* @author 刘晓阳
*/
public static int exist(String sql, Object... params) {
class RowRapper implements IRowMapper {
int flag;
@Override
public void rowMapper(ResultSet resultSet) {
try {
if (resultSet.next()) {
flag = -1;// 学号存在,无法再添加,可以查询修改和删除
} else {
flag = 1;// 学号不存在,可以添加,不能查询修改和删除
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
RowRapper rowRapper = new RowRapper();
select(sql, rowRapper, params);
return rowRapper.flag;
}
/**
*
* 修改数据
*
* @author 刘晓阳
*/
public static boolean update(String sql) {
Connection connection = null;
Statement statement = null;
try {
connection = getConnection();
statement = connection.createStatement();
int result = statement.executeUpdate(sql);
return result > 0;
} catch (Exception e) {
e.printStackTrace();
} finally {
close(connection, statement);
}
return false;
}
/**
*
* 修改数据(防sql注入)
*
* @author 刘晓阳
*/
public static boolean update(String sql, Object... params) {
PreparedStatement preparedStatement = null;
try {
Connection connection = getConnection();
preparedStatement = connection.prepareStatement(sql);
for (int i = 1; i <= params.length; i++) {
preparedStatement.setObject(i, params[i - 1]);
}
int result = preparedStatement.executeUpdate();
if (result > 0) {
return result > 0;
} else {
return false;
}
} catch (Exception e) {
e.printStackTrace();
}
return false;
}
/**
*
* 查询数据
*
* @author 刘晓阳
*/
public static void select(String sql, IRowMapper rowmapper) {
Connection connection = null;
Statement statement = null;
ResultSet resultset = null;
try {
connection = getConnection();
statement = connection.createStatement();
resultset = statement.executeQuery(sql);
rowmapper.rowMapper(resultset);
} catch (Exception e) {
e.printStackTrace();
} finally {
close(connection, statement, resultset);
}
}
/**
*
* 查询数据(防sql注入)
*
* @author 刘晓阳
*/
public static void select(String str, IRowMapper rowmapper, Object... params) {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultset = null;
try {
connection = getConnection();
preparedStatement = connection.prepareStatement(str);
for (int i = 1; i <= params.length; i++) {
preparedStatement.setObject(i, params[i - 1]);
}
resultset = preparedStatement.executeQuery();
rowmapper.rowMapper(resultset);
} catch (Exception e) {
e.printStackTrace();
} finally {
close(connection, preparedStatement, resultset);
}
}
/**
*
* 批量处理
*
* @author 刘晓阳
*/
public static boolean batch(String... sqls) {
Connection connection = null;
Statement statement = null;
try {
connection = getConnection();
connection.setAutoCommit(false);
statement = connection.createStatement();
for (String sql : sqls) {
statement.addBatch(sql);
}
statement.executeBatch();
connection.commit();
return true;
} catch (Exception e) {
e.printStackTrace();
if (connection != null) {
try {
connection.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
}
} finally {
close(connection, statement);
}
return false;
}
/**
*
* 批量处理(防sql注入)
*
* @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) {
e.printStackTrace();
if (connection != null) {
try {
connection.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
}
} finally {
close(connection, preparedstatement);
}
return false;
}
/**
*
* 关闭接口(释放资源)
*
* @author 刘晓阳
*/
public static void close(Connection connection, Statement statement) {
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
*
* 关闭接口(释放资源)
*
* @author 刘晓阳
*/
public static void close(Connection connection, Statement statement, ResultSet resultset) {
if (resultset != null) {
try {
resultset.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
close(connection, statement);
}
}