Example usage for java.sql PreparedStatement getParameterMetaData

List of usage examples for java.sql PreparedStatement getParameterMetaData

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. PreparedStatement
  6. getParameterMetaData

Introduction

In this page you can find the example usage for java.sql PreparedStatement getParameterMetaData.

Prototype

ParameterMetaData getParameterMetaData() throws SQLException;

Source Link

Document

Retrieves the number, types and properties of this PreparedStatement object's parameters.

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));//  ww w. j  a  v a  2  s.  c o m
    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  w ww  . j  a 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:com.gs.obevo.db.impl.core.jdbc.JdbcHelper.java

public int[] batchUpdate(Connection conn, String sql, Object[][] argsArray) {
    PreparedStatement ps = null;
    try {/*w  w w  . j  a v a  2  s. c  o m*/
        this.jdbcHandler.preUpdate(conn, this);

        if (LOG.isDebugEnabled()) {
            LOG.debug("Executing batch update on connection {}: {} with args: {}", displayConnection(conn), sql,
                    argsArray);
        }
        ps = conn.prepareStatement(sql);
        for (Object[] args : argsArray) {
            for (int j = 0; j < args.length; j++) {
                if (!parameterTypeEnabled || args[j] != null) {
                    ps.setObject(j + 1, args[j]);
                } else {
                    ps.setNull(j + 1, ps.getParameterMetaData().getParameterType(j + 1));
                }
            }
            ps.addBatch();
        }

        return ps.executeBatch();
    } catch (SQLException e) {
        LOG.error("Error during batch execution; will print out the full batch stack trace: ");
        this.logSqlBatchException(e, 0);
        throw new DataAccessException(e);
    } finally {
        DbUtils.closeQuietly(ps);
    }
}

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

private void checkParameter(PreparedStatement prep, int index, String className, int type, String typeName,
        int precision, int scale) throws SQLException {
    ParameterMetaData meta = prep.getParameterMetaData();
    assertEquals(className, meta.getParameterClassName(index));
    assertEquals(type, meta.getParameterType(index));
    assertEquals(typeName, meta.getParameterTypeName(index));
    assertEquals(precision, meta.getPrecision(index));
    assertEquals(scale, meta.getScale(index));
}

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  w  w w  .  j a va  2  s . c om
    }

    // 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.zaproxy.zap.extension.websocket.db.TableWebSocket.java

public synchronized int getIndexOf(WebSocketMessageDTO criteria, List<Integer> opcodes,
        List<Integer> inScopeChannelIds) throws DatabaseException {
    try {//from  www . jav  a 2 s. c  om
        String query = "SELECT COUNT(m.message_id) " + "FROM websocket_message AS m "
                + "LEFT OUTER JOIN websocket_message_fuzz f "
                + "ON m.message_id = f.message_id AND m.channel_id = f.channel_id "
                + "<where> AND m.message_id < ?";
        PreparedStatement stmt = buildMessageCriteriaStatement(query, criteria, opcodes, inScopeChannelIds);

        int paramsCount = stmt.getParameterMetaData().getParameterCount();
        stmt.setInt(paramsCount, criteria.id);

        try {
            return executeAndGetSingleIntValue(stmt);
        } finally {
            stmt.close();
        }
    } catch (SQLException e) {
        throw new DatabaseException(e);
    }
}

From source file:org.zaproxy.zap.extension.sse.db.TableEventStream.java

/**
 * Retrieves list of {@link ServerSentEvent}, but loads only parts of the payload.
 *
 * @param criteria//w w w  . j a  va 2  s .com
 * @param inScopeStreamIds
 * @param offset
 * @param limit
 * @param payloadPreviewLength
 * @return Events that fulfill given template.
 * @throws DatabaseException
 */
public synchronized List<ServerSentEvent> getEvents(ServerSentEvent criteria, List<Integer> inScopeStreamIds,
        int offset, int limit, int payloadPreviewLength) throws DatabaseException {
    try {
        String query = "SELECT e.event_id, e.stream_id, e.timestamp, e.last_event_id, e.event_type, e.data, e.reconnection_time, e.raw_event "
                + "FROM event_stream_event AS e " + "<where> "
                + "ORDER BY e.timestamp, e.stream_id, e.event_id " + "LIMIT ? " + "OFFSET ?";

        PreparedStatement stmt;
        try {
            stmt = buildEventCriteriaStatement(query, criteria, inScopeStreamIds);
        } catch (DatabaseException e) {
            if (getConnection().isClosed()) {
                return new ArrayList<>(0);
            }

            throw e;
        }

        try {
            int paramsCount = stmt.getParameterMetaData().getParameterCount();
            stmt.setInt(paramsCount - 1, limit);
            stmt.setInt(paramsCount, offset);

            stmt.execute();

            return buildEvents(stmt.getResultSet(), true, payloadPreviewLength);
        } finally {
            stmt.close();
        }
    } catch (SQLException e) {
        throw new DatabaseException(e);
    }
}

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

public StatementHandle prepare(ConnectionHandle ch, String sql, long maxRowCount) {
    try {//from  w  ww .  j  av a 2 s .  co m
        final Connection conn = getConnection(ch.id);
        final PreparedStatement statement = conn.prepareStatement(sql);
        final int id = System.identityHashCode(statement);
        statementCache.put(id, new StatementInfo(statement));
        StatementHandle h = new StatementHandle(ch.id, id,
                signature(statement.getMetaData(), statement.getParameterMetaData(), sql));
        if (LOG.isTraceEnabled()) {
            LOG.trace("prepared statement " + h);
        }
        return h;
    } catch (SQLException e) {
        throw propagate(e);
    }
}

From source file:org.apache.tajo.catalog.store.XMLCatalogSchemaManager.java

private boolean checkExistenceByQuery(PreparedStatement pstmt, BaseSchema baseSchema, String... params)
        throws SQLException {
    int paramIdx = 1;
    boolean result = false;

    if (baseSchema.getSchemaName() != null && !baseSchema.getSchemaName().isEmpty()) {
        pstmt.setString(paramIdx++, baseSchema.getSchemaName().toUpperCase());
    }/*from   www  .jav  a  2 s  . c  o  m*/

    for (; paramIdx <= pstmt.getParameterMetaData().getParameterCount(); paramIdx++) {
        pstmt.setString(paramIdx, params[paramIdx - 1].toUpperCase());
    }

    ResultSet rs = null;
    try {
        rs = pstmt.executeQuery();
        while (rs.next()) {
            if (rs.getString(1).toUpperCase().equals(params[params.length - 1].toUpperCase())) {
                result = true;
                break;
            }
        }
    } finally {
        CatalogUtil.closeQuietly(rs);
    }

    return result;
}

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  av a  2  s  .  com*/
 * @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);
            }
        }
    }
}