Java SQL Table quoteSchemaTable(Connection conn, String schema, String table)

Description

Returns quoted schema & table to use in SQL queries for the given Connection.

License

Open Source License

Parameter

Parameter	Description
conn	An SQL connection.
schema	The schema the table resides in.
table	The table.

Return

The schema & table name surrounded in quotes, usable in queries for the specified connection.

Declaration

public static String quoteSchemaTable(Connection conn, String schema,
        String table) throws SQLException 

Method Source Code

//package com.java2s;
/* ***** BEGIN LICENSE BLOCK *****
 *
 * This file is part of Weave./*from w w  w  .j a  va2 s  .co  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.SQLException;

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";

    /**
     * Returns quoted schema & table to use in SQL queries for the given DBMS.
     * @param dbms The name of a DBMS (MySQL, PostgreSQL, ...)
     * @param schema The schema the table resides in.
     * @param table The table.
     * @return The schema & table name surrounded in quotes, usable in queries for the specified DBMS.
     */
    public static String quoteSchemaTable(String dbms, String schema,
            String table) {
        if (schema.length() == 0)
            return quoteSymbol(dbms, table);

        if (dbms.equalsIgnoreCase(ORACLE))
            schema = schema.toUpperCase();

        if (dbms.equalsIgnoreCase(SQLITE))
            return quoteSymbol(dbms, table);

        return quoteSymbol(dbms, schema) + "." + quoteSymbol(dbms, table);
    }

    /**
     * Returns quoted schema & table to use in SQL queries for the given Connection.
     * @param conn An SQL connection.
     * @param schema The schema the table resides in.
     * @param table The table.
     * @return The schema & table name surrounded in quotes, usable in queries for the specified connection.
     */
    public static String quoteSchemaTable(Connection conn, String schema,
            String table) throws SQLException {
        String dbms = getDbmsFromConnection(conn);
        return quoteSchemaTable(dbms, schema, table);
    }

    /**
     * @param dbms The name of a DBMS (MySQL, PostgreSQL, ...)
     * @param symbol The symbol to quote.
     * @return The symbol surrounded in quotes, usable in queries for the specified DBMS.
     */
    public static String quoteSymbol(String dbms, String symbol)
            throws IllegalArgumentException {
        //the quote symbol is required for names of variables that include spaces or special characters

        String openQuote, closeQuote;
        if (dbms.equalsIgnoreCase(MYSQL)) {
            openQuote = closeQuote = "`";
        } else if (dbms.equalsIgnoreCase(POSTGRESQL)
                || dbms.equalsIgnoreCase(ORACLE)
                || dbms.equalsIgnoreCase(SQLITE)) {
            openQuote = closeQuote = "\"";
        } else if (dbms.equalsIgnoreCase(SQLSERVER)) {
            openQuote = "[";
            closeQuote = "]";
        } else
            throw new IllegalArgumentException("Unsupported DBMS type: "
                    + dbms);

        if (symbol.contains(openQuote) || symbol.contains(closeQuote))
            throw new IllegalArgumentException(
                    String.format(
                            "Unable to surround SQL symbol with quote marks (%s%s) because it already contains one: %s",
                            openQuote, closeQuote, symbol));

        return openQuote + symbol + closeQuote;
    }

    /**
     * @param conn An SQL connection.
     * @param symbol The symbol to quote.
     * @return The symbol surrounded in quotes, usable in queries for the specified connection.
     */
    public static String quoteSymbol(Connection conn, String symbol)
            throws SQLException, IllegalArgumentException {
        String dbms = getDbmsFromConnection(conn);
        return quoteSymbol(dbms, symbol);
    }

    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. isFileImportable(File file, String prefix)
2. isTableEmpty(Connection con, String schemaName, String tableName)
3. normalizeTableName(String table, Connection con)
4. parse(Connection connection, String tableName, String type)
5. queryAllFromTable(String tableName)
6. truncateTable(Connection conn)
7. truncateTable(Connection conn, String table_name)
8. truncateTable(Connection conn, String tablename)