MariaDB的Pool connection

因為發現我們的JAVA程式連MySQL資料庫的建立連線過程浪費很多時間，故想用connection pool的觀念來作，找到mariaDB(MySQL的免費後繼版)有直接提供pool的功能給JAVA，故我就研究如何套用到程式上。

mariaDB原生的pool功能，測試後效能比沒用pool快4倍。要用mariaDB原生的Driver，要用JAVA 8以上的版本，資料庫軟體可直接去mariaDB的網站下載。

程式碼在最下面，程式中最後一個區塊是最佳pool範例程式，改程式很簡單。



資料庫連結的JAR檔可在下列原廠下載

https://mariadb.com/kb/en/library/mariadb-connector-j/



這裡有教Eclipse怎麼載入connector的JAR檔

https://www.jianshu.com/p/489e4f0c1621





import java.sql.*;

import org.mariadb.jdbc.MariaDbPoolDataSource;



public class helloworld {



    public static void main(String[] args) throws Exception {

        // TODO Auto-generated method stub

        long time1, time2, time3;

        Class.forName("org.mariadb.jdbc.Driver");

     

//**********************************

// Original Database connection method

//********************************** 

        time1 = System.currentTimeMillis();

        for(int i = 1; i <=1000;i++) {

            try(

 

                Connection conn = DriverManager.getConnection(

                    "jdbc:mysql://localhost:3306/drink?user=root&password=0000");

                Statement stmt = conn.createStatement();

                ResultSet rs = stmt.executeQuery("SELECT * FROM history LIMIT 6,5"))

            {

 

                rs.next();

                //System.out.println(i +  "\t" + rs.getString(2) );

            }

        }

        time2 = System.currentTimeMillis();

        time3 = time2-time1;

        System.out.println("花了：" + time3 + " ms");

     

//**********************************

// mariaDB pool with minimal modified

//**********************************     

        time1 = System.currentTimeMillis();

        for(int i = 1; i <=1000;i++) {

            String connectionString = "jdbc:mysql://localhost:3306/drink?user=root&password=0000&maxPoolSize=100&pool";

         

            try (Connection connection = DriverManager.getConnection(connectionString)) {

                try (Statement stmt = connection.createStatement()) {

                    ResultSet rs = stmt.executeQuery("SELECT * FROM history LIMIT 5,5");

                    rs.next();

                }

            }     

        }

        time2 = System.currentTimeMillis();

        time3 = time2-time1;

        System.out.println("花了：" + time3 + " ms");

     

     

     

     

//**********************************

// mariaDB pool (best)

//**********************************

//所有參數都在JDBC的連結中，而且增加了"&maxPoolSize=100"，設定pool大小是100

     

        time1 = System.currentTimeMillis();



        for(int i = 1; i <=1000;i++) {

        MariaDbPoolDataSource pool = new MariaDbPoolDataSource("jdbc:mysql://localhost:3306/drink?user=root&password=0000&maxPoolSize=100");

            try (Connection connection = pool.getConnection()) {

                try (Statement stmt = connection.createStatement()) {

                    ResultSet rs = stmt.executeQuery("SELECT * FROM history");

                    rs.next();

                    //System.out.println(i +  "\t" + rs.getString(2) );

                }

                connection.close();

            }

        }

        time2 = System.currentTimeMillis();

        time3 = time2-time1;

        System.out.println("花了：" + time3 + " ms");

     



    }



}