Java SQL PreparedStatement prepareStatement( Connection conn, String query, List params)

Description

prepare Statement

License

Open Source License

Declaration

public static <TYPE> PreparedStatement prepareStatement(
            Connection conn, String query, List<TYPE> params)
            throws SQLException 

Method Source Code

//package com.java2s;
/* ***** BEGIN LICENSE BLOCK *****
 *
 * This file is part of Weave./*from  w  w w  .  ja v  a  2s .  c  o m*/
 *
 * The Initial Developer of Weave is the Institute for Visualization
 * and Perception Research at the University of Massachusetts Lowell.
 * Portions created by the Initial Developer are Copyright (C) 2008-2015
 * the Initial Developer. All Rights Reserved.
 *
 * This Source Code Form is subject to the terms of the Mozilla Public
 * License, v. 2.0. If a copy of the MPL was not distributed with this file,
 * You can obtain one at http://mozilla.org/MPL/2.0/.
 * 
 * ***** END LICENSE BLOCK ***** */

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.SQLException;

import java.util.List;

public class Main {
    public static String MYSQL = "MySQL";
    public static String SQLITE = "SQLite";
    public static String POSTGRESQL = "PostgreSQL";
    public static String SQLSERVER = "Microsoft SQL Server";
    public static String ORACLE = "Oracle";

    public static <TYPE> PreparedStatement prepareStatement(
            Connection conn, String query, List<TYPE> params)
            throws SQLException {
        PreparedStatement cstmt = conn.prepareStatement(query);
        constrainQueryParams(conn, params);
        setPreparedStatementParams(cstmt, params);
        return cstmt;
    }

    public static <TYPE> PreparedStatement prepareStatement(
            Connection conn, String query, TYPE[] params)
            throws SQLException {
        PreparedStatement cstmt = conn.prepareStatement(query);
        constrainQueryParams(conn, params);
        setPreparedStatementParams(cstmt, params);
        return cstmt;
    }

    protected static <T> void constrainQueryParams(Connection conn,
            List<T> params) {
        if (isOracleServer(conn))
            for (int i = 0; i < params.size(); i++)
                params.set(i, constrainOracleQueryParam(params.get(i)));
    }

    protected static <T> void constrainQueryParams(Connection conn,
            T[] params) {
        if (isOracleServer(conn))
            for (int i = 0; i < params.length; i++)
                params[i] = constrainOracleQueryParam(params[i]);
    }

    public static <TYPE> void setPreparedStatementParams(
            PreparedStatement cstmt, List<TYPE> params) throws SQLException {
        int i = 1;
        for (TYPE param : params)
            cstmt.setObject(i++, param);
    }

    public static <TYPE> void setPreparedStatementParams(
            PreparedStatement cstmt, TYPE[] params) throws SQLException {
        int i = 1;
        for (TYPE param : params)
            cstmt.setObject(i++, param);
    }

    /**
     * This function checks if a connection is for an Oracle server.
     * @param conn A SQL Connection.
     * @return A value of true if the Connection is for an Oracle server.
     * @throws SQLException 
     */
    public static boolean isOracleServer(Connection conn) {
        return getDbmsFromConnection(conn).equals(ORACLE);
    }

    @SuppressWarnings("unchecked")
    protected static <T> T constrainOracleQueryParam(T param) {
        // constrain oracle double values to float range
        if (param instanceof Double)
            param = (T) (Float) ((Double) param).floatValue();
        return param;
    }

    public static String getDbmsFromConnection(Connection conn) {
        try {
            String dbms = conn.getMetaData().getDatabaseProductName();
            for (String match : new String[] { ORACLE, SQLSERVER, MYSQL,
                    SQLITE, POSTGRESQL })
                if (dbms.equalsIgnoreCase(match))
                    return match;
            return dbms;
        } catch (SQLException e) {
            return "";
        }
    }
}

Related

1. intToArray(final int[] arr)
2. isClosed(PreparedStatement ps, boolean defaultValue)
3. loadColumnTableNameList(PreparedStatement stmt)
4. LockRow(PreparedStatement pstmt, String tablename, boolean exclusiveMode)
5. logPreparedStatement(PreparedStatement p)
6. prepareStatement(Connection con,String sql,Object...params)
7. prepareStatement(Connection conn, Map psMap, String key, String sql)
8. prepareStatement(Connection conn, String name, String sql)
9. prepareStatement(Connection conn, String sql)