- jdbc.properties数据库配置的属性文件内容如下
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost/xxxxx?seUnicode=true&characterEncoding=UTF8
jdbc.username=root
jdbc.password=xxxxxx
public class JDBCUtils {
private static final String JDBC_DRIVER;
private static final String JDBC_URL;
private static final String JDBC_USERNAME;
private static final String JDBC_PASSWORD;
static {
try (InputStream inputStream = JDBCUtils.class.getClassLoader().getResourceAsStream("com/zcl/jdbcutils/jdbc.properties")) {
Properties prop = new Properties();
prop.load(inputStream);
JDBC_DRIVER = prop.getProperty("jdbc.driver");
JDBC_URL = prop.getProperty("jdbc.url");
JDBC_USERNAME = prop.getProperty("jdbc.username");
JDBC_PASSWORD = prop.getProperty("jdbc.password");
// 加载驱动
Class.forName(JDBC_DRIVER);
} catch (Exception e) {
// 静态代码块中只能抛出运行时异常
throw new RuntimeException(e);
}
}
/**
* 创建连接
*
* @return
* @throws SQLException
*/
public static Connection createConnection() throws SQLException {
return DriverManager.getConnection(JDBC_URL, JDBC_USERNAME, JDBC_PASSWORD);
}
/**
* 带连接的更新操作
*
* @param conn
* @param sql
* @param paras
* @return
* @throws SQLException
*/
public static int executeUpdate(Connection conn, String sql, Object... paras) throws SQLException {
PreparedStatement stmt = null;
try {
stmt = conn.prepareStatement(sql);
for (int i = 0; i < paras.length; ++i) {
stmt.setObject(i + 1, paras[i]);
}
return stmt.executeUpdate();
} finally {
closeQuietly(stmt);
}
}
/**
* 不带连接的更新操作
*
* @param sql
* @param paras
* @return
* @throws SQLException
*/
public static int executeUpdate(String sql, Object... paras) throws SQLException {
Connection conn = null;
try {
conn = createConnection();
return executeUpdate(conn, sql, paras);
} finally {
closeQuietly(conn);
}
}
/**
* 带连接的查询操作
*
* @param conn
* @param sql
* @param paras
* @return
* @throws SQLException
*/
public static ResultSet executeQuery(Connection conn, String sql, Object... paras) throws SQLException {
PreparedStatement stmt = conn.prepareStatement(sql);
for (int i = 0; i < paras.length; ++i) {
stmt.setObject(i + 1, paras[i]);
}
return stmt.executeQuery();
}
/**
* 不带连接的查询操作
*
* @param sql
* @param paras
* @return
* @throws SQLException
*/
public static ResultSet executeQuery(String sql, Object... paras) throws SQLException {
Connection conn = createConnection();
return executeQuery(conn, sql, paras);
}
/**
* 带连接的查询操作（一次性取出所有数据放入内存中）
*
* @param conn
* @param sql
* @param paras
* @return
* @throws SQLException
*/
public static CachedRowSet executeQueryToCachedRowSet(Connection conn, String sql, Object... paras) throws SQLException {
PreparedStatement stmt = null;
ResultSet rs = null;
try {
stmt = conn.prepareStatement(sql);
for (int i = 0; i < paras.length; ++i) {
stmt.setObject(i + 1, paras[i]);
}
rs = stmt.executeQuery();
RowSetFactory rowSetFactory = RowSetProvider.newFactory();
CachedRowSet crs = rowSetFactory.createCachedRowSet();
crs.populate(rs);
return crs;
} finally {
closeQuietly(rs);
closeQuietly(stmt);
}
}
/**
* 不带连接的查询操作（一次性取出所有数据放入内存中）
*
* @param sql
* @param paras
* @return
* @throws SQLException
*/
public static CachedRowSet executeQueryToCachedRowSet(String sql, Object... paras) throws SQLException {
Connection conn = null;
try {
conn = createConnection();
return executeQueryToCachedRowSet(conn, sql, paras);
} finally {
closeQuietly(conn);
}
}
/**
* 带连接带事务的更新操作
*
* @param conn
* @param sqlList
* @param parasList
* @return
*/
public static boolean executeUpdateTrans(Connection conn, List<String> sqlList, List<Object[]> parasList) {
PreparedStatement stmt = null;
try {
conn.setAutoCommit(false);
for (int i = 0; i < sqlList.size(); ++i) {
stmt = conn.prepareStatement(sqlList.get(i));
stmt.clearParameters();
for (int j = 0; j < parasList.get(i).length; ++j) {
stmt.setObject(j + 1, parasList.get(i)[j]);
}
stmt.executeUpdate();
closeQuietly(stmt);
}
conn.commit();
return true;
} catch (Exception e) {
try {
conn.rollback();
} catch (SQLException ex) {
}
return false;
} finally {
closeQuietly(stmt);
}
}
/**
* 不带连接带事务的更新操作
*
* @param sqlList
* @param parasList
* @return
*/
public static boolean executeUpdateTrans(List<String> sqlList, List<Object[]> parasList) {
Connection conn = null;
try {
conn = createConnection();
return executeUpdateTrans(conn, sqlList, parasList);
} catch (Exception e) {
return false;
} finally {
closeQuietly(conn);
}
}
/**
* 带连接的批量更新操作
*
* @param conn
* @param sql
* @param parasList
* @return
*/
public static boolean executeUpdateBatch(Connection conn, String sql, List<Object[]> parasList) {
PreparedStatement stmt = null;
try {
conn.setAutoCommit(false);
stmt = conn.prepareStatement(sql);
for (int i = 0; i < parasList.size(); ++i) {
stmt.clearParameters();
for (int j = 0; j < parasList.get(i).length; ++j) {
stmt.setObject(j + 1, parasList.get(i)[j]);
}
stmt.addBatch();
if (i % 1000 == 0) {
stmt.executeBatch();
}
}
stmt.executeBatch();
conn.commit();
return true;
} catch (Exception e) {
try {
conn.rollback();
} catch (SQLException ex) {
}
return false;
} finally {
closeQuietly(stmt);
}
}
/**
* 不带连接的批量更新操作
*
* @param sql
* @param parasList
* @return
*/
public static boolean executeUpdateBatch(String sql, List<Object[]> parasList) {
Connection conn = null;
try {
conn = createConnection();
return executeUpdateBatch(conn, sql, parasList);
} catch (Exception e) {
return false;
} finally {
closeQuietly(conn);
}
}
/**
* 获取最后插入数据的自增主键值
*
* @param conn
* @return
* @throws SQLException
*/
public static int getLastInsertId(Connection conn) throws SQLException {
ResultSet rs = null;
try {
rs = executeQuery(conn, "select last_insert_id() id ");
rs.next();
return rs.getInt("id");
} finally {
closeRSAndPS(rs);
}
}
/**
* 关闭资源
*
* @param ac
*/
public static void closeQuietly(AutoCloseable ac) {
if (ac != null) {
try {
ac.close();
} catch (Exception e) {
}
}
}
/**
* 关闭结果集
*
* @param rs
*/
public static void closeRSAndPS(ResultSet rs) {
Statement stmt = null;
try {
stmt = rs.getStatement();
closeQuietly(rs);
closeQuietly(stmt);
} catch (Exception e) {
}
}
/**
* 关闭结果集
*
* @param rs
*/
public static void closeRSAndPSAndConn(ResultSet rs) {
Connection conn = null;
Statement stmt = null;
try {
stmt = rs.getStatement();
conn = stmt.getConnection();
closeQuietly(rs);
closeQuietly(stmt);
closeQuietly(conn);
} catch (Exception e) {
}
}
}
