JDBC【資料庫連線池、DbUtils框架、分頁】

Java3y發表於2018-02-19
JDBC資料庫框架

1.資料庫連線池

什麼是資料庫連線池

簡單來說：資料庫連線池就是提供連線的。。。

為什麼我們要使用資料庫連線池

  • 資料庫的連線的建立和關閉是非常消耗資源的
  • 頻繁地開啟、關閉連線造成系統效能低下

編寫連線池

  1. 編寫連線池需實現java.sql.DataSource介面
  2. 建立批量的Connection用LinkedList儲存【既然是個池，當然用集合儲存、、LinkedList底層是連結串列，對增刪效能較好】
  3. 實現getConnetion()，讓getConnection()每次呼叫，都是在LinkedList中取一個Connection返回給使用者
  4. 呼叫Connection.close()方法，Connction返回給LinkedList



    private static LinkedList<Connection> list = new LinkedList<>();
    
    //獲取連線只需要一次就夠了，所以用static程式碼塊
    static {
        //讀取檔案配置
        InputStream inputStream = Demo1.class.getClassLoader().getResourceAsStream("db.properties");

        Properties properties = new Properties();
        try {
            properties.load(inputStream);
            String url = properties.getProperty("url");
            String username = properties.getProperty("username");
            String driver = properties.getProperty("driver");
            String password = properties.getProperty("password");

            //載入驅動
            Class.forName(driver);

            //獲取多個連線，儲存在LinkedList集合中
            for (int i = 0; i < 10; i++) {
                Connection connection = DriverManager.getConnection(url, username, password);
                list.add(connection);
            }
            
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    //重寫Connection方法，使用者獲取連線應該從LinkedList中給他
    @Override
    public Connection getConnection() throws SQLException {
        System.out.println(list.size());
        System.out.println(list);

       //先判斷LinkedList是否存在連線
       return list.size() > 0 ? list.removeFirst() : null; 
    }



複製程式碼

我們已經完成前三步了，現在問題來了**。我們呼叫Conncetion.close()方法，是把資料庫的物理連線關掉，而不是返回給LinkedList的**

解決思路：

  1. 寫一個Connection子類，覆蓋close()方法
  2. 寫一個Connection包裝類，增強close()方法
  3. 用動態代理，返回一個代理物件出去，攔截close()方法的呼叫，對close()增強

分析第一個思路：

  • Connection是通過資料庫驅動載入的，儲存了資料的資訊。寫一個子類Connection，new出物件，子類的Connction無法直接繼承父類的資料資訊，也就是說子類的Connection是無法連線資料庫的，更別談覆蓋close()方法了。

分析第二個思路：

  • 寫一個Connection包裝類。
    1. 寫一個類，實現與被增強物件的相同介面【Connection介面】
    2. 定義一個變數，指向被增強的物件
    3. 定義構造方法，接收被增強物件
    4. 覆蓋想增強的方法
    5. 對於不想增強的方法，直接呼叫被增強物件的方法
  • 這個思路本身是沒什麼毛病的，就是實現介面時，方法太多了！，所以我們也不使用此方法

分析第三個思路程式碼實現：


    @Override
    public Connection getConnection() throws SQLException {

        if (list.size() > 0) {
            final Connection connection = list.removeFirst();

            //看看池的大小
            System.out.println(list.size());

            //返回一個動態代理物件
            return (Connection) Proxy.newProxyInstance(Demo1.class.getClassLoader(), connection.getClass().getInterfaces(), new InvocationHandler() {

                @Override
                public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {

                    //如果不是呼叫close方法，就按照正常的來呼叫
                    if (!method.getName().equals("close")) {
                        method.invoke(connection, args);
                    } else {

                        //進到這裡來，說明呼叫的是close方法
                        list.add(connection);

                        //再看看池的大小
                        System.out.println(list.size());

                    }
                    return null;
                }

            });
        }
        return null;
    }


複製程式碼

我們上面已經能夠簡單編寫一個執行緒池了。下面我們來使用一下開源資料庫連線池

DBCP

使用DBCP資料來源的步驟：

  1. 匯入兩個jar包【Commons-dbcp.jar和Commons-pool.jar】
  2. 讀取配置檔案
  3. 獲取BasicDataSourceFactory物件
  4. 建立DataSource物件

    private static DataSource dataSource = null;

    static {
        try {
            //讀取配置檔案
            InputStream inputStream = Demo3.class.getClassLoader().getResourceAsStream("dbcpconfig.properties");
            Properties properties = new Properties();
            properties.load(inputStream);

            //獲取工廠物件
            BasicDataSourceFactory basicDataSourceFactory = new BasicDataSourceFactory();
            dataSource = basicDataSourceFactory.createDataSource(properties);

        } catch (IOException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();

    }

    //這裡釋放資源不是把資料庫的物理連線釋放了，是把連線歸還給連線池【連線池的Connection內部自己做好了】
    public static void release(Connection conn, Statement st, ResultSet rs) {

        if (rs != null) {
            try {
                rs.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
            rs = null;
        }
        if (st != null) {
            try {
                st.close();
            } catch (Exception e) {
                e.printStackTrace();
            }

        }
        if (conn != null) {
            try {
                conn.close();
            } catch (Exception e) {
                e.printStackTrace();
            }

        }
    }


複製程式碼

C3P0

C3P0資料來源的效能更勝一籌，並且它可以使用XML配置檔案配置資訊！

步驟：

  1. 匯入開發包【c3p0-0.9.2-pre1.jar】和【mchange-commons-0.2.jar】
  2. 匯入XML配置檔案【可以在程式中自己一個一個配，C3P0的doc中的Configuration有XML檔案的事例】
  3. new出ComboPooledDataSource物件

    private static ComboPooledDataSource comboPooledDataSource = null;

    static {
        //如果我什麼都不指定，就是使用XML預設的配置，這裡我指定的是oracle的
        comboPooledDataSource = new ComboPooledDataSource("oracle");
    }

    public static Connection getConnection() throws SQLException {
        return comboPooledDataSource.getConnection();
    }

複製程式碼

Tomcat資料來源

Tomcat伺服器也給我們提供了連線池，內部其實就是DBCP

步驟：

  1. 在META-INF目錄下配置context.xml檔案【檔案內容可以在tomcat預設頁面的 JNDI Resources下Configure Tomcat's Resource Factory找到】
  2. 匯入Mysql或oracle開發包到tomcat的lib目錄下
  3. 初始化JNDI->獲取JNDI容器->檢索以XXX為名字在JNDI容器存放的連線池

context.xml檔案的配置：


<Context>

  <Resource name="jdbc/EmployeeDB"
            auth="Container"
            type="javax.sql.DataSource"
            
            username="root"
            password="root"
            driverClassName="com.mysql.jdbc.Driver"
            url="jdbc:mysql://localhost:3306/zhongfucheng"
            maxActive="8"
            maxIdle="4"/>
</Context>

複製程式碼

        try {

			//初始化JNDI容器
            Context initCtx = new InitialContext();

			//獲取到JNDI容器
            Context envCtx = (Context) initCtx.lookup("java:comp/env");

			//掃描以jdbc/EmployeeDB名字繫結在JNDI容器下的連線池
            DataSource ds = (DataSource)
                    envCtx.lookup("jdbc/EmployeeDB");

            Connection conn = ds.getConnection();
            System.out.println(conn);

        } 


複製程式碼

使用dbutils框架

dbutils它是對JDBC的簡單封裝，極大簡化jdbc編碼的工作量

DbUtils類

提供了關閉連線，裝載JDBC驅動，回滾提交事務等方法的工具類【比較少使用，因為我們學了連線池，就應該使用連線池連線資料庫】

QueryRunner類

該類簡化了SQL查詢，配合ResultSetHandler使用，可以完成大部分的資料庫操作，過載了許多的查詢，更新，批處理方法。大大減少了程式碼量

ResultSetHandler介面

該介面規範了對ResultSet的操作，要對結果集進行什麼操作，傳入ResultSetHandler介面的實現類即可。

  • ArrayHandler：把結果集中的第一行資料轉成物件陣列。
  • ArrayListHandler：把結果集中的每一行資料都轉成一個陣列，再存放到List中。
  • BeanHandler：將結果集中的第一行資料封裝到一個對應的JavaBean例項中。
  • BeanListHandler：將結果集中的每一行資料都封裝到一個對應的JavaBean例項中，存放到List裡。
  • ColumnListHandler：將結果集中某一列的資料存放到List中。
  • KeyedHandler(name)：將結果集中的每一行資料都封裝到一個Map裡，再把這些map再存到一個map裡，其key為指定的key。
  • MapHandler：將結果集中的第一行資料封裝到一個Map裡，key是列名，value就是對應的值。
  • MapListHandler：將結果集中的每一行資料都封裝到一個Map裡，然後再存放到List
  • ScalarHandler 將ResultSet的一個列到一個物件中。

使用DbUtils框架對資料庫的CRUD



/*
* 使用DbUtils框架對資料庫的CRUD
* 批處理
*
* */
public class Test {

    @org.junit.Test
    public void add() throws SQLException {

        //建立出QueryRunner物件
        QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());
        String sql = "INSERT INTO student (id,name) VALUES(?,?)";

        //我們發現query()方法有的需要傳入Connection物件，有的不需要傳入
        //區別：你傳入Connection物件是需要你來銷燬該Connection，你不傳入，由程式幫你把Connection放回到連線池中
        queryRunner.update(sql, new Object[]{"100", "zhongfucheng"});

    }

    @org.junit.Test
    public void query()throws SQLException {

        //建立出QueryRunner物件
        QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());
        String sql = "SELECT * FROM student";

        List list = (List) queryRunner.query(sql, new BeanListHandler(Student.class));
        System.out.println(list.size());

    }

    @org.junit.Test
    public void delete() throws SQLException {
        //建立出QueryRunner物件
        QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());
        String sql = "DELETE FROM student WHERE id='100'";

        queryRunner.update(sql);
    }

    @org.junit.Test
    public void update() throws SQLException {
        //建立出QueryRunner物件
        QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());
        String sql = "UPDATE student SET name=? WHERE id=?";

        queryRunner.update(sql, new Object[]{"zhongfuchengaaa", 1});
    }

    @org.junit.Test
    public void batch() throws SQLException {
        //建立出QueryRunner物件
        QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());
        String sql = "INSERT INTO student (name,id) VALUES(?,?)";

        Object[][] objects = new Object[10][];
        for (int i = 0; i < 10; i++) {
            objects[i] = new Object[]{"aaa", i + 300};
        }
        queryRunner.batch(sql, objects);
    }

}


複製程式碼

分頁

分頁技術是非常常見的，在搜尋引擎下搜尋頁面，不可能把全部資料都顯示在一個頁面裡邊。所以我們用到了分頁技術。

Oracle實現分頁


	/*
	  Oracle分頁語法：
	    @lineSize---每頁顯示資料行數
	    @currentPage----當前所在頁
	
	*/
	SELECT *FROM (
	    SELECT 列名,列名,ROWNUM rn
	    FROM 表名
	    WHERE ROWNUM<=(currentPage*lineSize)) temp
	
	WHERE temp.rn>(currentPage-1)*lineSize;


複製程式碼

Oracle分頁原理簡單解釋：


	/*
	  Oracle分頁：
	    Oracle的分頁依賴於ROWNUM這個偽列，ROWNUM主要作用就是產生行號。
	
	  分頁原理：
	    1：子查詢查出前n行資料，ROWNUM產生前N行的行號
	    2：使用子查詢產生ROWNUM的行號，通過外部的篩選出想要的資料
	
	  例子：
	    我現在規定每頁顯示5行資料【lineSize=5】，我要查詢第2頁的資料【currentPage=2】
	    注：【對照著語法來看】
	
	  實現：
	    1：子查詢查出前10條資料【ROWNUM<=10】
	    2：外部篩選出後面5條資料【ROWNUM>5】
		3：這樣我們就取到了後面5條的資料
	*/

複製程式碼

Mysql實現分頁


	/*
	  Mysql分頁語法：
	  @start---偏移量，不設定就是從0開始【也就是(currentPage-1)*lineSize】
	  @length---長度，取多少行資料
	
	*/
	SELECT *
	FROM 表名
	LIMIT [START], length;
	
	/*
	  例子：
	    我現在規定每頁顯示5行資料，我要查詢第2頁的資料
	
	  分析：
	    1：第2頁的資料其實就是從第6條資料開始，取5條
	
	  實現：
	    1：start為5【偏移量從0開始】
	    2：length為5

*/

複製程式碼

總結：

  • Mysql從(currentPage-1)*lineSize開始取資料，取lineSize條資料
  • Oracle先獲取currentPage*lineSize條資料，從(currentPage-1)*lineSize開始取資料

使用JDBC連線資料庫實現分頁

下面是常見的分頁圖片

JDBC【資料庫連線池、DbUtils框架、分頁】

配合圖片，看下我們的需求是什麼：

  1. 算出有多少頁的資料，顯示在頁面上
  2. 根據頁碼，從資料庫顯示相對應的資料。

分析：

  1. 算出有多少頁資料這是非常簡單的【在資料庫中查詢有多少條記錄，你每頁顯示多少條記錄，就可以算出有多少頁資料了】
  2. 使用Mysql或Oracle的分頁語法即可

通過上面分析，我們會發現需要用到4個變數

  • currentPage--當前頁【由使用者決定的】
  • totalRecord--總資料數【查詢表可知】
  • lineSize--每頁顯示資料的數量【由我們開發人員決定】
  • pageCount--頁數【totalRecord和lineSize決定】

        //每頁顯示3條資料
        int lineSize = 3;

        //總記錄數
        int totalRecord = getTotalRecord();

        //假設使用者指定的是第2頁
        int currentPage = 2;

        //一共有多少頁
        int pageCount = getPageCount(totalRecord, lineSize);

        //使用什麼資料庫進行分頁，記得要在JdbcUtils中改配置
        List<Person> list = getPageData2(currentPage, lineSize);
        for (Person person : list) {
            System.out.println(person);
        }

    }

    //使用JDBC連線Mysql資料庫實現分頁
    public static List<Person> getPageData(int currentPage, int lineSize) throws SQLException {

        //從哪個位置開始取資料
        int start = (currentPage - 1) * lineSize;

        QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());
        String sql = "SELECT name,address  FROM person LIMIT ?,?";

        List<Person> persons = (List<Person>) queryRunner.query(sql, new BeanListHandler(Person.class), new Object[]{start, lineSize});
        return persons;

    }

    //使用JDBC連線Oracle資料庫實現分頁
    public static List<Person> getPageData2(int currentPage, int lineSize) throws SQLException {

        //從哪個位置開始取資料
        int start = (currentPage - 1) * lineSize;

        //讀取前N條資料
        int end = currentPage * lineSize;

        QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());
        String sql = "SELECT " +
                "  name, " +
                "  address " +
                "FROM ( " +
                "  SELECT " +
                "    name, " +
                "    address , " +
                "    ROWNUM rn " +
                "  FROM person " +
                "  WHERE ROWNUM <= ? " +
                ")temp WHERE temp.rn>?";

        List<Person> persons = (List<Person>) queryRunner.query(sql, new BeanListHandler(Person.class), new Object[]{end, start});
        return persons;

    }

    public static int getPageCount(int totalRecord, int lineSize) {

        //簡單演算法
        //return (totalRecord - 1) / lineSize + 1;

        //此演算法比較好理解，把資料代代進去就知道了。
        return totalRecord % lineSize == 0 ? (totalRecord / lineSize) : (totalRecord / lineSize) + 1;

    }


    public static int  getTotalRecord() throws SQLException {

        //使用DbUtils框架查詢資料庫表中有多少條資料
        QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());
        String sql = "SELECT COUNT(*) FROM person";

        Object o = queryRunner.query(sql, new ScalarHandler());

        String ss = o.toString();
        int  s = Integer.parseInt(ss);
        return s;
    }


複製程式碼

如果文章有錯的地方歡迎指正，大家互相交流。習慣在微信看技術文章的同學，可以關注微信公眾號:Java3y。

相關文章