Example usage for java.sql ParameterMetaData getParameterType

List of usage examples for java.sql ParameterMetaData getParameterType

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. ParameterMetaData
  6. getParameterType

Introduction

In this page you can find the example usage for java.sql ParameterMetaData getParameterType.

Prototype

int getParameterType(int param) throws SQLException;

Source Link

Document

Retrieves the designated parameter's SQL type.

Usage

From source file:com.alibaba.wasp.jdbc.TestPreparedStatement.java

public void testParameterMetaData() throws SQLException {
    PreparedStatement prep = conn.prepareStatement("SELECT ?, ?, ? FROM DUAL");
    ParameterMetaData pm = prep.getParameterMetaData();
    assertEquals("java.lang.String", pm.getParameterClassName(1));
    assertEquals("VARCHAR", pm.getParameterTypeName(1));
    assertEquals(3, pm.getParameterCount());
    assertEquals(ParameterMetaData.parameterModeIn, pm.getParameterMode(1));
    assertEquals(Types.VARCHAR, pm.getParameterType(1));
    assertEquals(0, pm.getPrecision(1));
    assertEquals(0, pm.getScale(1));/*from  ww w  .j  av a2s.c om*/
    assertEquals(ResultSetMetaData.columnNullableUnknown, pm.isNullable(1));
    assertEquals(pm.isSigned(1), true);
    assertThrows(SQLErrorCode.INVALID_VALUE_2, pm).getPrecision(0);
    assertThrows(SQLErrorCode.INVALID_VALUE_2, pm).getPrecision(4);
    prep.close();
    assertThrows(SQLErrorCode.OBJECT_CLOSED, pm).getPrecision(1);

    Statement stat = conn.createStatement();
    stat.execute("CREATE TABLE TEST3(ID INT, NAME VARCHAR(255), DATA DECIMAL(10,2))");
    PreparedStatement prep1 = conn.prepareStatement("UPDATE TEST3 SET ID=?, NAME=?, DATA=?");
    PreparedStatement prep2 = conn.prepareStatement("INSERT INTO TEST3 VALUES(?, ?, ?)");
    checkParameter(prep1, 1, "java.lang.Integer", 4, "INTEGER", 10, 0);
    checkParameter(prep1, 2, "java.lang.String", 12, "VARCHAR", 255, 0);
    checkParameter(prep1, 3, "java.math.BigDecimal", 3, "DECIMAL", 10, 2);
    checkParameter(prep2, 1, "java.lang.Integer", 4, "INTEGER", 10, 0);
    checkParameter(prep2, 2, "java.lang.String", 12, "VARCHAR", 255, 0);
    checkParameter(prep2, 3, "java.math.BigDecimal", 3, "DECIMAL", 10, 2);
    PreparedStatement prep3 = conn
            .prepareStatement("SELECT * FROM TEST3 WHERE ID=? AND NAME LIKE ? AND ?>DATA");
    checkParameter(prep3, 1, "java.lang.Integer", 4, "INTEGER", 10, 0);
    checkParameter(prep3, 2, "java.lang.String", 12, "VARCHAR", 0, 0);
    checkParameter(prep3, 3, "java.math.BigDecimal", 3, "DECIMAL", 10, 2);
    stat.execute("DROP TABLE TEST3");
}

From source file:QueryRunner.java

/**
 * Fill the <code>PreparedStatement</code> replacement parameters with 
 * the given objects.//from ww  w .  ja  v  a  2s. c  o  m
 * @param stmt PreparedStatement to fill
 * @param params Query replacement parameters; <code>null</code> is a valid
 * value to pass in.
 * @throws SQLException if a database access error occurs
 */
public void fillStatement(PreparedStatement stmt, Object[] params) throws SQLException {

    if (params == null) {
        return;
    }

    ParameterMetaData pmd = stmt.getParameterMetaData();
    if (pmd.getParameterCount() < params.length) {
        throw new SQLException(
                "Too many parameters: expected " + pmd.getParameterCount() + ", was given " + params.length);
    }
    for (int i = 0; i < params.length; i++) {
        if (params[i] != null) {
            stmt.setObject(i + 1, params[i]);
        } else {
            // VARCHAR works with many drivers regardless
            // of the actual column type.  Oddly, NULL and 
            // OTHER don't work with Oracle's drivers.
            int sqlType = Types.VARCHAR;
            if (!pmdKnownBroken) {
                try {
                    sqlType = pmd.getParameterType(i + 1);
                } catch (SQLException e) {
                    pmdKnownBroken = true;
                }
            }
            stmt.setNull(i + 1, sqlType);
        }
    }
}

From source file:dbutils.DbUtilsTemplate.java

public void fillStatement(PreparedStatement stmt, Object... params) throws SQLException {

    // check the parameter count, if we can
    ParameterMetaData pmd = null;
    if (!pmdKnownBroken) {
        pmd = stmt.getParameterMetaData();
        int stmtCount = pmd.getParameterCount();
        int paramsCount = params == null ? 0 : params.length;

        if (stmtCount != paramsCount) {
            throw new SQLException(
                    "Wrong number of parameters: expected " + stmtCount + ", was given " + paramsCount);
        }//from   www .  j a v  a  2s .  c o m
    }

    // nothing to do here
    if (params == null) {
        return;
    }

    for (int i = 0; i < params.length; i++) {
        if (params[i] != null) {
            stmt.setObject(i + 1, params[i]);
        } else {
            // VARCHAR works with many drivers regardless
            // of the actual column type. Oddly, NULL and
            // OTHER don't work with Oracle's drivers.
            int sqlType = Types.VARCHAR;
            if (!pmdKnownBroken) {
                try {
                    /*
                     * It's not possible for pmdKnownBroken to change from
                     * true to false, (once true, always true) so pmd cannot
                     * be null here.
                     */
                    sqlType = pmd.getParameterType(i + 1);
                } catch (SQLException e) {
                    pmdKnownBroken = true;
                }
            }
            stmt.setNull(i + 1, sqlType);
        }
    }
}

From source file:org.apache.calcite.avatica.jdbc.JdbcMeta.java

/**
 * Converts from JDBC metadata to AvaticaParameters
 *///from w w w .  j  a  va2s. c o m
protected static List<AvaticaParameter> parameters(ParameterMetaData metaData) throws SQLException {
    if (metaData == null) {
        return Collections.emptyList();
    }
    final List<AvaticaParameter> params = new ArrayList<>();
    for (int i = 1; i <= metaData.getParameterCount(); i++) {
        params.add(new AvaticaParameter(metaData.isSigned(i), metaData.getPrecision(i), metaData.getScale(i),
                metaData.getParameterType(i), metaData.getParameterTypeName(i),
                metaData.getParameterClassName(i), "?" + i));
    }
    return params;
}

From source file:org.batoo.jpa.core.impl.criteria.QueryImpl.java

/**
 * Fills the statement with the parameters supplied.
 * /*w w  w  . j  a  va 2  s.  c  o  m*/
 * @param statement
 *            the statement
 * @param parameters
 *            the parameters
 * @param repeat
 *            the parameter repeat map
 * @throws SQLException
 *             thrown in case of an underlying SQL Exception
 * 
 * @since 2.0.0
 */
private void fillStatement(PreparedStatement statement, Object[] parameters, Map<Integer, Integer> repeat)
        throws SQLException {
    // the following code has been adopted from Apache Commons DBUtils.

    // no paramaters nothing to do
    if ((parameters == null) || (parameters.length == 0)) {
        return;
    }

    final ParameterMetaData pmd = this.pmdBroken ? null : statement.getParameterMetaData();

    if (this.pmdBroken) {
        int total = parameters.length - repeat.size();

        if (repeat.size() > 0) {
            for (final Integer repeatSize : repeat.values()) {
                if (repeatSize != null) {
                    total += repeatSize;
                }
            }
        }

        ((PreparedStatementProxy) statement).setParamCount(total);
    }

    int index = 1;
    for (int i = 0; i < parameters.length; i++) {
        if (parameters[i] != null) {
            if (repeat.containsKey(i)) {
                final Object paramValue = parameters[i];

                if (paramValue instanceof Collection) {
                    final Collection<?> collection = (Collection<?>) paramValue;
                    for (final Object subParamValue : collection) {
                        statement.setObject(index++, subParamValue);
                    }
                } else {
                    final Object[] array = (Object[]) paramValue;
                    for (final Object subParamValue : array) {
                        statement.setObject(index++, subParamValue);
                    }
                }
            } else {
                statement.setObject(index++, parameters[i]);
            }
        } else {
            // VARCHAR works with many drivers regardless
            // of the actual column type. Oddly, NULL and
            // OTHER don't work with Oracle's drivers.
            int sqlType = Types.VARCHAR;
            if (!this.pmdBroken) {
                try {
                    sqlType = pmd.getParameterType(index + 1);
                } catch (final SQLException e) {
                    this.pmdBroken = true;
                }
            }

            statement.setNull(index++, sqlType);
        }
    }
}

From source file:org.batoo.jpa.core.impl.jdbc.dbutils.AbstractQueryRunner.java

/**
 * Fill the <code>PreparedStatement</code> replacement parameters with the given objects.
 * //from  w  ww. j  a v a 2s  .  co  m
 * @param stmt
 *            PreparedStatement to fill
 * @param params
 *            Query replacement parameters; <code>null</code> is a valid value to pass in.
 * @throws SQLException
 *             if a database access error occurs
 */
public void fillStatement(PreparedStatement stmt, Object... params) throws SQLException {

    // check the parameter count, if we can
    ParameterMetaData pmd = null;
    if (!this.pmdKnownBroken) {
        pmd = stmt.getParameterMetaData();
        final int stmtCount = pmd.getParameterCount();
        final int paramsCount = params == null ? 0 : params.length;

        if (stmtCount != paramsCount) {
            throw new SQLException(
                    "Wrong number of parameters: expected " + stmtCount + ", was given " + paramsCount);
        }
    }

    // nothing to do here
    if (params == null) {
        return;
    }

    for (int i = 0; i < params.length; i++) {
        if (params[i] != null) {
            stmt.setObject(i + 1, params[i]);
        } else {
            // VARCHAR works with many drivers regardless
            // of the actual column type. Oddly, NULL and
            // OTHER don't work with Oracle's drivers.
            int sqlType = Types.VARCHAR;
            if (!this.pmdKnownBroken) {
                try {
                    sqlType = pmd.getParameterType(i + 1);
                } catch (final SQLException e) {
                    this.pmdKnownBroken = true;
                }
            }
            stmt.setNull(i + 1, sqlType);
        }
    }
}

From source file:org.batoo.jpa.jdbc.dbutils.QueryRunner.java

/**
 * Fill the <code>PreparedStatement</code> replacement parameters with the given objects.
 * /*from   w w  w.  j a  v a2  s . c om*/
 * @param statement
 *            PreparedStatement to fill
 * @param params
 *            Query replacement parameters; <code>null</code> is a valid value to pass in.
 * @throws SQLException
 *             if a database access error occurs
 */
private void fillStatement(PreparedStatement statement, Object... params) throws SQLException {
    // use local variable for performance
    boolean pmdKnownBroken = this.pmdKnownBroken;
    ParameterMetaData pmd = this.pmd;
    final boolean hasLob = this.hasLob;

    if (pmdKnownBroken) {
        ((PreparedStatementProxy) statement).setParamCount(params.length);
    } else {
        ((PreparedStatementProxy) statement).setParamCount(-1);
    }

    // if the jdbc adaptor wants to modify the parameters we let it do it its own way
    final JdbcAdaptor jdbcAdaptor = this.jdbcAdaptor;
    if ((jdbcAdaptor != null) && jdbcAdaptor.modifiesParameters()) {
        pmd = this.pmd = statement.getParameterMetaData();

        jdbcAdaptor.modifyParameters(pmd, params);
    }

    for (int i = 0; i < params.length; i++) {
        final Object param = params[i];
        if ((param != null) && (param != Void.TYPE)) {
            if (hasLob && (param instanceof Clob)) {
                if (this.jdbcAdaptor instanceof OracleAdaptor) {
                    statement.setCharacterStream(i + 1, ((Clob) param).getCharacterStream());
                } else {
                    statement.setClob(i + 1, (Clob) param);
                }
            } else if (hasLob && (param instanceof Blob)) {
                if (this.jdbcAdaptor instanceof OracleAdaptor) {
                    statement.setBinaryStream(i + 1, ((Blob) param).getBinaryStream());
                } else {
                    statement.setBlob(i + 1, (Blob) param);
                }
            } else {
                statement.setObject(i + 1, param);
            }
        } else {
            if (!pmdKnownBroken && (pmd == null)) {
                pmd = this.pmd = statement.getParameterMetaData();
            }

            // VARCHAR works with many drivers regardless of the actual column type.
            // Oddly, NULL and OTHER don't work with Oracle's drivers.
            int sqlType = Types.VARCHAR;
            if (!pmdKnownBroken) {
                try {
                    sqlType = pmd.getParameterType(i + 1);
                } catch (final SQLException e) {
                    pmdKnownBroken = this.pmdKnownBroken = true;
                }
            }
            if (param != Void.TYPE) {
                statement.setNull(i + 1, sqlType);
            }
        }
    }
}

From source file:org.pentaho.di.core.database.Database.java

public RowMetaInterface getParameterMetaData(PreparedStatement ps) {
    RowMetaInterface par = new RowMeta();
    try {//from   w w  w  .  j a  v a 2 s .  c om
        ParameterMetaData pmd = ps.getParameterMetaData();
        for (int i = 1; i <= pmd.getParameterCount(); i++) {
            String name = "par" + i;
            int sqltype = pmd.getParameterType(i);
            int length = pmd.getPrecision(i);
            int precision = pmd.getScale(i);
            ValueMeta val;

            switch (sqltype) {
            case java.sql.Types.CHAR:
            case java.sql.Types.VARCHAR:
                val = new ValueMeta(name, ValueMetaInterface.TYPE_STRING);
                break;
            case java.sql.Types.BIGINT:
            case java.sql.Types.INTEGER:
            case java.sql.Types.NUMERIC:
            case java.sql.Types.SMALLINT:
            case java.sql.Types.TINYINT:
                val = new ValueMeta(name, ValueMetaInterface.TYPE_INTEGER);
                break;
            case java.sql.Types.DECIMAL:
            case java.sql.Types.DOUBLE:
            case java.sql.Types.FLOAT:
            case java.sql.Types.REAL:
                val = new ValueMeta(name, ValueMetaInterface.TYPE_NUMBER);
                break;
            case java.sql.Types.DATE:
            case java.sql.Types.TIME:
            case java.sql.Types.TIMESTAMP:
                val = new ValueMeta(name, ValueMetaInterface.TYPE_DATE);
                break;
            case java.sql.Types.BOOLEAN:
            case java.sql.Types.BIT:
                val = new ValueMeta(name, ValueMetaInterface.TYPE_BOOLEAN);
                break;
            default:
                val = new ValueMeta(name, ValueMetaInterface.TYPE_NONE);
                break;
            }

            if (val.isNumeric() && (length > 18 || precision > 18)) {
                val = new ValueMeta(name, ValueMetaInterface.TYPE_BIGNUMBER);
            }

            par.addValueMeta(val);
        }
    } catch (AbstractMethodError e) {
        // Oops: probably the database or JDBC doesn't support it.
        return null;
    } catch (SQLException e) {
        return null;
    } catch (Exception e) {
        return null;
    }

    return par;
}