// 參考 https://medium.com/learning-from-jhipster/14-%E6%B7%B1%E5%85%A5-jdbc-connection-pool-%E4%B8%A6%E5%B0%8E%E5%85%A5-h2-db-939adee9c50
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class SQLDatabaseConnection {

  // Connect to your database.
  // Replace server name, username, and password with your credentials
  public static void main(String[] args) {
    // 1. 載入 SQLServer 的 JDBC Driver
    Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
    
    // 2. 編輯連線協定，
    String connectionUrl =
        "jdbc:sqlserver://yourserver.database.windows.net:1433;"
        + "database=AdventureWorks;"
        + "user=yourusername@yourserver;"
        + "password=yourpassword;";

    // 用來接住回傳結果
    ResultSet resultSet = null;

    // https://docs.oracle.com/javase/tutorial/essential/exceptions/tryResourceClose.html
    try (
      // 3. 建立連線
      Connection connection = DriverManager.getConnection(connectionUrl);
      // 4. 建立Statement物件供提交SQL命令使用
      Statement statement = connection.createStatement();
    ) {

      // Create and execute a SELECT SQL statement.
      String selectSql = "SELECT TOP 10 Title, FirstName, LastName from SalesLT.Customer";
      // 5. 提交SQL命令，並取得結果
      resultSet = statement.executeQuery(selectSql);

      // Print results from select statement
      while (resultSet.next()) {
        System.out.println(resultSet.getString(2) + " " + resultSet.getString(3));
      }
      
      // 結束工作
      // 6. 釋放資源，關閉Connection連線以及Statement
    }
    catch (SQLException e) {
      e.printStackTrace();
    }
  }
}

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashSet;
import java.util.Set;
import java.util.Stack;

public class MySQLConnectionPool {
    // 連線用的必要資訊
    private String databaseUrl;
    private String userName;
    private String password;
    // pool 的大小
    private int maxPoolSize = 10;
    // 目前連線的數量
    private int connNum = 0;

    private static final String SQL_VERIFYCONN = "select 1";

    // 目前可用的Connection
    Stack<Connection> freePool = new Stack<>();
    // 目前正在使用的Connection
    Set<Connection> occupiedPool = new HashSet<>();

    /**
     * Constructor: 創建一個Connection Pool
     * 
     * @param databaseUrl
     *            The connection url
     * @param userName
     *            user name
     * @param password
     *            password
     * @param maxSize
     *            max size of the connection pool
     */
    public MySQLConnectionPool(String databaseUrl, String userName,
            String password, int maxSize) {
        this.databaseUrl = databaseUrl;
        this.userName = userName;
        this.password = password;
        this.maxPoolSize = maxSize;
    }

    /**
     * Get an available connection
     * 
     * @return An available connection
     * @throws SQLException
     *             Fail to get an available connection
     */
    public synchronized Connection getConnection() throws SQLException {
        Connection conn = null;

        // 沒辦法在建立新的連線 (已達到最大連線數)
        if (isFull()) {
            throw new SQLException("The connection pool is full.");
        }

        // 回傳freePool中的連線，並且標記該連線為occupied
        conn = getConnectionFromPool();

        // 如果conn是空的表示freePool裡面沒有任何object，因此我們需要建立新的連線
        if (conn == null) {
            conn = createNewConnectionForPool();
        }

        // 有些database一段時間沒使用會自動斷連線，所以我們可以確認一下從freePool取出的connection是否還有連線著
        // For Azure Database for MySQL, if there is no action on one connection for some
        // time, the connection is lost. By this, make sure the connection is
        // active. Otherwise reconnect it.
        conn = makeAvailable(conn);
        return conn;
    }

    /**
     * 這邊注意的是，要使用synchronized才會把singleton的object lock住，確保不會有多個thread同時還回去多個connection
     * client使用完，要還回去Pool中
     * 
     * Return a connection to the pool
     * 
     * @param conn
     *            The connection
     * @throws SQLException
     *             When the connection is returned already or it isn't gotten
     *             from the pool.
     */
    public synchronized void returnConnection(Connection conn)
            throws SQLException {
        // 如果conn是空的，表示client沒有使用connection，因此不需要回傳
        if (conn == null) {
            throw new NullPointerException();
        }
        // 檢查 conn 是否在 occupiedPool 中，並且可以從 occupiedPool 中移除，如果不是，則拋出 SQLException
        if (!occupiedPool.remove(conn)) {
            throw new SQLException(
                    "The connection is returned already or it isn't for this pool");
        }
        // 如果沒跳Exception則已經從occupied移除，可以放回 freePool 中
        freePool.push(conn);
    }

    /**
     * 也要使用synchronized，確保資源被lock住，以正確做檢查freePool是否已經塞滿
     * 
     * Verify if the connection is full.
     * 
     * @return if the connection is full
     */
    private synchronized boolean isFull() {
        return ((freePool.size() == 0) && (connNum >= maxPoolSize));
    }

    /**
     * 因為connection必須是singleton才不會無謂的一直建立資源
     * 因此有一個private的constructor，確保只有透過getInstance()才能取得實例
     * 
     * Create a connection for the pool
     * 
     * @return the new created connection
     * @throws SQLException
     *             When fail to create a new connection.
     */
    private Connection createNewConnectionForPool() throws SQLException {
        Connection conn = createNewConnection();
        connNum++;
        occupiedPool.add(conn);
        return conn;
    }

    /**
     * 創建連線，必須是private，確保只有透過createNewConnectionForPool()才能取得實例
     * 
     * Crate a new connection
     * 
     * @return the new created connection
     * @throws SQLException
     *             When fail to create a new connection.
     */
    private Connection createNewConnection() throws SQLException {
        Connection conn = null;
        conn = DriverManager.getConnection(databaseUrl, userName, password);
        return conn;
    }

    /**
     * 從freePool中取出一個連線，並且放入occupiedPool
     * 
     * Get a connection from the pool. If there is no free connection, return
     * null
     * 
     * @return the connection.
     */
    private Connection getConnectionFromPool() {
        // 先reset 
        Connection conn = null;
        // 如果有空閒的連線，就從freePool中取出，並且放入佔用中的occupiedPool
        if (freePool.size() > 0) {
            conn = freePool.pop();
            occupiedPool.add(conn);
        }
        // 回傳連線
        return conn;
    }

    /**
     * 檢查連線是否還連線著，如果沒有則重新連線
     * 
     * Make sure the connection is available now. Otherwise, reconnect it.
     * 
     * @param conn
     *            The connection for verification.
     * @return the available connection.
     * @throws SQLException
     *             Fail to get an available connection
     */
    private Connection makeAvailable(Connection conn) throws SQLException {

        // 透過下Query來確認連線是否還連線著
        if (isConnectionAvailable(conn)) {
            return conn;
        }

        // 如果發現沒有連線，就把該連線從occupiedPool中移除，並且關閉該連線
        occupiedPool.remove(conn);
        connNum--;
        conn.close();

        // 然後重新建立一個新的，並且放入occupiedPool
        conn = createNewConnection();
        occupiedPool.add(conn);
        connNum++;
        return conn;
    }

    /**
     * 確認一下目前連線是否還連線著，可以透過下Query來確認
     * 
     * By running a sql to verify if the connection is available
     * 
     * @param conn
     *            The connection for verification
     * @return if the connection is available for now.
     */
    private boolean isConnectionAvailable(Connection conn) {
        try (Statement st = conn.createStatement()) {
            st.executeQuery(SQL_VERIFYCONN);
            return true;
        } catch (SQLException e) {
            return false;
        }
    }
    
    // Just an Example
    public static void main(String[] args) throws SQLException {
        Connection conn = null;
        // 建立資源池 
        MySQLConnectionPool pool = new MySQLConnectionPool(
                "jdbc:mysql://mysqlaasdevintic-sha.cloudapp.net:3306/<Your DB name>",
                "<Your user>", "<Your Password>", 2);
        try {
            // 取得連線 
            conn = pool.getConnection();

            // 準備一個Query來執行
            try (Statement statement = conn.createStatement())
            {
                ResultSet res = statement.executeQuery("show tables");
                System.out.println("There are below tables:");
                while (res.next()) {
                    String tblName = res.getString(1);
                    System.out.println(tblName);
                }
            }
        }
         finally {
            if (conn != null) {
                // 釋放連線
                pool.returnConnection(conn);
            }
        }
    }

}

import javax.sql.DataSource;
import org.springframework.jdbc.core.JdbcTemplate;

public class RunAQuery {

    private JdbcTemplate jdbcTemplate;

    // 只提供基本的連線
    public void setDataSource(DataSource dataSource) {
        this.jdbcTemplate = new JdbcTemplate(dataSource);
    }

    public int getCount() {
        return this.jdbcTemplate.queryForObject("select count(*) from mytable", Integer.class);
    }

    public String getName() {
        return this.jdbcTemplate.queryForObject("select name from mytable", String.class);
    }
}

import org.springframework.data.repository.CrudRepository;
@Repository
public interface UserRepository extends CrudRepository<User, Long> {
}

  private static <T extends DataSource> MappedDataSourceProperties<T> lookupPooled(ClassLoader classLoader,
		Class<T> type) {
	MappedDataSourceProperties<T> result = null;
	result = lookup(classLoader, type, result, "com.zaxxer.hikari.HikariDataSource",
			HikariDataSourceProperties::new);
	result = lookup(classLoader, type, result, "org.apache.tomcat.jdbc.pool.DataSource",
			TomcatPoolDataSourceProperties::new);
	result = lookup(classLoader, type, result, "org.apache.commons.dbcp2.BasicDataSource",
			MappedDbcp2DataSource::new);
	result = lookup(classLoader, type, result, "oracle.ucp.jdbc.PoolDataSourceImpl",
			OraclePoolDataSourceProperties::new, "oracle.jdbc.OracleConnection");
	result = lookup(classLoader, type, result, "com.mchange.v2.c3p0.ComboPooledDataSource",
			ComboPooledDataSourceProperties::new);
	return result;
}