package util; import org.slf4j.Logger; import org.slf4j.LoggerFactory; 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.List; /** * 数据库工具类 * @author ruijie liruijie0603@126.com * @date 2014-11-28 */ public class DBUtil { private final static Logger logger = LoggerFactory.getLogger(DBUtil.class); private static DBUtil dbUtil; private String driverClass = ""; private String url = ""; private String username = ""; private String password = ""; private DBUtil() { driverClass = Constant.dbdriver; url = Constant.dburl; username = Constant.dbusername; password = Constant.dbpassword; } public static DBUtil newInstance() { if (dbUtil == null) { dbUtil = new DBUtil(); } return dbUtil; } public Connection getConn() { Connection conn = null; try { Class.forName(driverClass); conn = DriverManager.getConnection(url, username, password); } catch (ClassNotFoundException e) { logger.error(e.getMessage(), e); } catch (SQLException e) { logger.error(e.getMessage(), e); } return conn; } public void insert(String sql) { Connection conn = getConn(); Statement statement = null; try { statement = conn.createStatement(); statement.execute(sql); } catch (SQLException e) { e.printStackTrace(); } finally { try { if (statement != null) statement.close(); if (conn != null) conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } public ResultSet query(String sql) { Connection conn = getConn(); Statement statement = null; ResultSet rs = null; try { statement = conn.createStatement(); rs = statement.executeQuery(sql); } catch (SQLException e) { e.printStackTrace(); } return rs; } public void excute(String sql) { Connection conn = getConn(); Statement statement = null; try { statement = conn.createStatement(); boolean b = statement.execute(sql); } catch (SQLException e) { e.printStackTrace(); }finally { release(statement,conn); } } public PreparedStatement getStatement(Connection conn, String sql) { PreparedStatement pstmt = null; try { pstmt = conn.prepareStatement(sql); } catch (SQLException e) { logger.error(e.getMessage(), e); } return pstmt; } public void release(ResultSet rs, PreparedStatement pstmt, Connection conn) { try { if (rs != null) { rs.close(); rs = null; } } catch (SQLException e) { logger.error(e.getMessage(), e); } finally { try { if (pstmt != null) { pstmt.close(); pstmt = null; } } catch (SQLException e) { logger.error(e.getMessage(), e); } finally { try { if (conn != null) { conn.close(); conn = null; } } catch (SQLException e) { logger.error(e.getMessage(), e); } } } } public void release(Statement pstmt, Connection conn) { try { if (pstmt != null) { pstmt.close(); pstmt = null; } } catch (SQLException e) { logger.error(e.getMessage(), e); } finally { try { if (conn != null) { conn.close(); conn = null; } } catch (SQLException e) { logger.error(e.getMessage(), e); } } } public void ExcuteNonQueryBatch(List<String> sqlList) { Connection conn = null; Statement _Statement = null; try { if (sqlList != null && sqlList.size() > 0) { conn = getConn(); conn.setAutoCommit(false); for (int i = 0; i < sqlList.size(); i++) { _Statement = conn.createStatement(); logger.info(sqlList.get(i)); _Statement.executeUpdate(sqlList.get(i)); // System.out.println(sqlList.get(i)); } conn.commit(); } } catch (Exception e) { try { if (_Statement != null) _Statement.close(); if (conn != null) conn.close(); } catch (SQLException e1) { e1.printStackTrace(); } e.printStackTrace(); } } }