Java SQL PreparedStatement getPreparedStatement(String sql)

Description

Get prepared statement

License

Open Source License

Parameter

Parameter	Description
sql	a parameter

Return

null if exception occurred

Declaration

public static PreparedStatement getPreparedStatement(String sql) 

Method Source Code

//package com.java2s;
//License from project: Open Source License 

import java.sql.Connection;
import java.sql.Driver;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import java.util.concurrent.ConcurrentHashMap;

public class Main {
    private static String dbuser = "", dbpwd = "", dbname = "", dbhost = "";
    private static Driver driver = null;
    private static Object NULL = new Object();
    private static List<Connection> freeConns = new ArrayList<Connection>();
    private static Map<Connection, Object> usedConns = new ConcurrentHashMap<Connection, Object>();
    private static Map<Statement, Connection> statConns = new ConcurrentHashMap<Statement, Connection>();

    /**//from  w  ww  . ja  va2 s  .  c o m
     * Get prepared statement
     * 
     * @param sql
     * @return null if exception occurred
     */
    public static PreparedStatement getPreparedStatement(String sql) {
        try {
            Connection conn = getConnection();
            PreparedStatement pstmt = conn.prepareStatement(sql);
            statConns.put(pstmt, conn);
            return pstmt;
        } catch (Exception e) {
            return null;
        }
    }

    /**
     * Get prepared statement, change dbname if necessary, not in connection
     * pool
     * 
     * @param dbname
     * @param sql
     * @return null if exception occurred
     */
    public static PreparedStatement getPreparedStatement(String dbname, String sql) {
        try {
            if (driver == null)
                driver = new org.gjt.mm.mysql.Driver();
            Connection conn = driver.connect(
                    String.format("jdbc:mysql://%s/%s?user=%s&password=%s", dbhost, dbname, dbuser, dbpwd), null);
            PreparedStatement pstmt = conn.prepareStatement(sql);
            return pstmt;
        } catch (Exception e) {
            return null;
        }
    }

    /**
     * Get db connection
     * 
     * @return null if exception occurred
     */
    public synchronized static Connection getConnection() {
        try {
            Connection conn = null;
            if (freeConns.isEmpty()) {
                if (driver == null)
                    driver = new org.gjt.mm.mysql.Driver();
                conn = driver.connect(
                        String.format("jdbc:mysql://%s/%s?user=%s&password=%s", dbhost, dbname, dbuser, dbpwd),
                        null);
            } else {
                conn = freeConns.get(freeConns.size() - 1);
                freeConns.remove(conn);
                try {
                    Statement stmt = conn.createStatement();
                    stmt.execute("SELECT 1");
                    stmt.close();
                    stmt = null;
                } catch (Exception e) {
                    if (!conn.isClosed())
                        conn.close();
                    conn = null;
                    return getConnection();
                }
                // if (conn.isClosed()) {
                // // break weak reference, notify system gc
                // conn = null;
                // return getConnection();
                // }
            }
            usedConns.put(conn, NULL);
            return conn;
        } catch (Exception e) {
            return null;
        }
    }
}

Related

1. getLastId(PreparedStatement s)
2. getLimitedBatchSizePreparedStatement(PreparedStatement pstmt, int maxBatchSize)
3. getNewPreparedStatement(String format)
4. getPreparedStatement(Connection conn, String sql)
5. getPreparedStatement(Connection conn, String sql, List