文章目錄

• • • • JDBCUtils
      • 數(shù)據(jù)庫連接池
      • Apache—DBUtils
      • BasicDAO

??JDBC就是Java做了一套關(guān)于數(shù)據(jù)庫操作的接口，交給由不同的數(shù)據(jù)庫產(chǎn)商去具體實現(xiàn)。這樣，無論是那種類型的數(shù)據(jù)庫，我們導入相應的 jar包，只操作這一套接口就可以實現(xiàn)功能。
??下面我做了一張JDBC常用API的思維導圖：

---

------- 2023.1.15加更

JDBCUtils

??以上是JDBC的一些常用API，但是連接數(shù)據(jù)庫依然很繁瑣。連接數(shù)據(jù)庫和關(guān)閉資源是必須的操作，我們可以寫一個JDBCUtils，封裝連接資源和關(guān)閉資源。

public class JDBCutils {public static String user;public static String password;public static String url;public static String driver;static {try {Properties properties = new Properties();properties.load(new FileInputStream("D:\\code\\JDBC\\src\\mysql.properties"));user = properties.getProperty("user");password = properties.getProperty("password");url = properties.getProperty("url");driver = properties.getProperty("driver");} catch (IOException e) {throw new RuntimeException(e);}}public static Connection getConnection(){try {return DriverManager.getConnection(url,user,password);} catch (SQLException e) {throw new RuntimeException(e);}}public static void close(ResultSet set, Statement statement,Connection connection){try {if (set != null){set.close();}if (statement != null){statement.close();}if (connection != null){connection.close();}} catch (SQLException e) {throw new RuntimeException(e);}} }

??這樣獲取連接和關(guān)閉資源我們就不用每次都寫一大推重復的代碼了，調(diào)用這個工具類傳參數(shù)就可以了。
??批處理
??需要處理大量的sql語句時，我們可以采取批處理的方式，即，把sql語句打個包，發(fā)到數(shù)據(jù)庫執(zhí)行時讓其一次性執(zhí)行多條。而不是收一條處理一條，再收一條再處理一條。這樣提高了效率：

public void tran() throws SQLException {Connection connection = JDBCutils.getConnection();String sql = "insert into account01 values (null,?,?)";PreparedStatement preparedStatement = connection.prepareStatement(sql);try {System.out.println("開始執(zhí)行");long start = System.currentTimeMillis();for (int i = 0; i < 5000; i++) {preparedStatement.setString(1,"jack" + i);preparedStatement.setString(2,"666");preparedStatement.addBatch();if ((i + 1) % 1000 == 0){preparedStatement.executeBatch();preparedStatement.clearBatch();}}long end = System.currentTimeMillis();System.out.println("批處理耗時：" + (end - start));} catch (SQLException e) {throw new RuntimeException(e);}finally {JDBCutils.close(null, preparedStatement,connection );}}

數(shù)據(jù)庫連接池

??頻繁連接、斷開數(shù)據(jù)庫性能較低，而且訪問量突增的情況下會使得數(shù)據(jù)庫癱瘓，因此引入了數(shù)據(jù)庫連接池技術(shù)。連接池作為一個緩沖，既提高了效率，又保護了數(shù)據(jù)庫安全。德魯伊連接池目前性能最好，應用廣泛。

public class JDBCUtilsByDruid {private static DataSource ds;static {Properties properties = new Properties();try {properties.load(new FileInputStream("D:\\code\\JDBC\\src\\druid.properties"));ds = DruidDataSourceFactory.createDataSource(properties);} catch (Exception e) {throw new RuntimeException(e);}}public static Connection getConnection() throws SQLException {return ds.getConnection();}public static void close(ResultSet resultSet, Statement statement, Connection connection) {try {if (resultSet !=null){resultSet.close();}if (statement != null){statement.close();}if (connection != null){connection.close();}} catch (SQLException e) {throw new RuntimeException(e);}} }

Apache—DBUtils

??關(guān)閉connection連接后，結(jié)果集resultSet也不能再使用了。但是，一直不關(guān)連接會浪費資源，因此需要把結(jié)果集封裝到集合中去。

public ArrayList<Actor> testArraylist(){Connection connection = null;String aql = "select * from actor where id >= ?"; // String aql = "select *from actor";PreparedStatement preparedStatement = null;ResultSet resultSet = null;ArrayList<Actor> list = new ArrayList<>();try {connection = JDBCUtilsByDruid.getConnection();preparedStatement = connection.prepareStatement(aql);preparedStatement.setInt(1,1);resultSet = preparedStatement.executeQuery();while (resultSet.next()) {int id =resultSet.getInt("id");String name = resultSet.getString("name");String sex = resultSet.getString("sex");String brithday = resultSet.getString("brithday");String phone = resultSet.getString("phone");list.add(new Actor(id,name,sex,brithday,phone));}System.out.println("list = " + list);} catch (SQLException e) {throw new RuntimeException(e);} finally {JDBCUtilsByDruid.close(resultSet,preparedStatement,connection);}return list;}

??以上是韓老師教我們自己動手封裝的辦法，相當于是說一下原理和思路。而Apache—DBUtils則提供了一系列辦法，我們導入jar包直接調(diào)用即可。簡化了JDBC開發(fā)。開發(fā)中我們使用druid和commons.dbutils進行JDBC開發(fā)。

BasicDAO

??在進一步的使用中，我們封裝了BasicDAO類，引入泛型和可變參數(shù)進一步提高了通用性和效率。實例見在下的另外一篇文章，滿漢樓項目。

public class BasicDAO<T> {private QueryRunner qr = new QueryRunner();public int update(String sql, Object... parameters) {Connection connection = null;try {connection = JDBCUtilsByDruid.getConnection();int update = qr.update(connection, sql, parameters);return update;} catch (SQLException e) {throw new RuntimeException(e);} finally {JDBCUtilsByDruid.close(null,null,connection);}}public List<T> quertMutli(String sql,Class<T> clazz,Object... parameters){return null;}public T quertSingle(String sql,Class<T> clazz,Object... parameters){Connection connection = null;try {connection = JDBCUtilsByDruid.getConnection();return qr.query(connection,sql,new BeanHandler<T>(clazz),parameters);} catch (SQLException e) {throw new RuntimeException(e);} finally {JDBCUtilsByDruid.close(null,null,connection);}} }

總結(jié)

以上是生活随笔為你收集整理的JDBC的一些API的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。