import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;
public class DBUtil {
static {
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
static PropertiesUntil properties =new PropertiesUntil();
/**
* 获取连接
*
* @author SY
*/
private static Connection con() {
try {
String url=properties.getValue("jdbc.url");
String userName=properties.getValue("jdbc.userName");
String password=properties.getValue("jdbc.password");
return DriverManager.getConnection(url, userName, password);
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
/**
* 批量注册
*
* @author SY
*/
public static boolean betch (String sqls,Object[] ...param) {
Connection connection=null;
PreparedStatement preparedStatement=null;
try {
connection=con();
connection.setAutoCommit(false);
preparedStatement=connection.prepareStatement(sqls);
for(int i=0;i<param.length;i++) {
for(int j=1;j<=param[i].length;j++) {
preparedStatement.setObject(j, param[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(preparedStatement,connection);
}
return false;
}
/**
* 批量处理
*
* @author SY
*/
public static boolean betch (String ...sqls) {
Connection connection=null;
Statement statement=null;
try {
connection=con();
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(statement,connection);
}
return false;
}
/**
* 检查是否存在
*
* @author SY
*/
public static boolean exist(String sql,String...param) {
class RowMapper implements IRowMapper{
boolean flag;
@Override
public void rowMapper(ResultSet resultSet) {
try {
flag=resultSet.next();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
RowMapper iRowMapper =new RowMapper();
select(sql,iRowMapper,param);
return iRowMapper.flag;
}
/**
* 查询数据
*
* @author SY
*/
public static void select(String sql, IRowMapper iRowMapper) {
Statement statement=null;
Connection connection=null;
ResultSet resultSet=null;
try {
connection=con();
statement = connection.createStatement();
resultSet =statement.executeQuery(sql);
iRowMapper.rowMapper(resultSet);
}catch(Exception e){
e.printStackTrace();
}finally {
close(resultSet, statement, connection);
}
}
/**
* 查询数据
*
* @author SY
*/
public static void select(String sql,IRowMapper map,String ...mapper ) {
Connection connection=null;
ResultSet resultSet=null;
PreparedStatement preparedStatement=null;
try {
connection=con();
preparedStatement=connection.prepareStatement(sql);
for(int i=1;i<=mapper.length;i++) {
preparedStatement.setObject(i, mapper[i-1]);
}
resultSet=preparedStatement.executeQuery();
map.rowMapper(resultSet);
}catch(Exception e){
e.printStackTrace();
}finally {
close(resultSet, preparedStatement, connection);
}
}
/**
* 修改数据
*
* @author SY
*/
public static boolean apply(String information) {
Statement statement=null;
Connection connection=null;
try {
connection=con();
statement = connection.createStatement();
int result=statement.executeUpdate(information);
return result>0;
} catch (Exception e) {
e.printStackTrace();
}finally {
close(statement, connection);
}
return false;
}
/**
* 修改数据
*
* @author SY
*/
public static boolean apply(String sql,String ...param) {
Connection connection=null;
PreparedStatement preparedStatement=null;
try {
connection=con();
preparedStatement =connection.prepareStatement(sql);
for (int i = 1; i <= param.length; i++) {
preparedStatement.setObject(i, param[i-1]);
}
int result =preparedStatement.executeUpdate();
return result >0;
} catch (Exception e) {
e.printStackTrace();
}finally {
close(preparedStatement, connection);
}
return false;
}
/**
* 释放资源
*
* @author SY
*/
private static void close(Statement statement,Connection connection) {
if (statement!=null) {
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (connection!=null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* 释放资源
*
* @author SY
*/
private static void close(ResultSet resultSet,Statement statement,Connection connection) {
if (resultSet!=null) {
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
close(statement,connection);
}
}