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:Main.java

public static void main(String[] args) throws Exception {
    Connection conn = getHSQLConnection();
    System.out.println("Got Connection.");
    Statement st = conn.createStatement();
    st.executeUpdate("create table survey (id int,name varchar);");
    st.executeUpdate("create view surveyView as (select * from survey);");
    st.executeUpdate("insert into survey (id,name ) values (1,'nameValue')");

    PreparedStatement pstmt = null;
    ParameterMetaData paramMetaData = null;
    String query = "select * from survey where id > ? and name = ?";
    pstmt = conn.prepareStatement(query);
    paramMetaData = pstmt.getParameterMetaData();
    if (paramMetaData == null) {
        System.out.println("db vendor does NOT support ParameterMetaData");
    } else {/*from ww  w  .  j a  v a2 s  . com*/
        System.out.println("db vendor supports ParameterMetaData");
        // find out the number of dynamic parameters
        int paramCount = paramMetaData.getParameterCount();
        System.out.println("paramCount=" + paramCount);
        System.out.println("-------------------");
        for (int param = 1; param <= paramCount; param++) {
            System.out.println("param number=" + param);
            int paramMode = paramMetaData.getParameterMode(param);
            System.out.println("param mode=" + paramMode);
            if (paramMode == ParameterMetaData.parameterModeOut) {
                System.out.println("the parameter's mode is OUT.");
            } else if (paramMode == ParameterMetaData.parameterModeIn) {
                System.out.println("the parameter's mode is IN.");
            } else if (paramMode == ParameterMetaData.parameterModeInOut) {
                System.out.println("the parameter's mode is INOUT.");
            } else {
                System.out.println("the mode of a parameter is unknown.");
            }
        }
    }

    pstmt.close();
    conn.close();

}

From source file:Main.java

public static void main(String[] args) throws Exception {
    Connection conn = getHSQLConnection();
    Statement st = conn.createStatement();
    //    st.executeUpdate("drop table survey;");
    st.executeUpdate("create table survey (id int,name varchar(30));");
    st.executeUpdate("insert into survey (id,name ) values (1,'nameValue')");

    PreparedStatement pstmt = null;
    ParameterMetaData paramMetaData = null;
    String query = "select * from survey where id > ? ";

    System.out.println("conn=" + conn);
    pstmt = conn.prepareStatement(query);
    paramMetaData = pstmt.getParameterMetaData();
    if (paramMetaData == null) {
        System.out.println("db vendor does NOT support ParameterMetaData");
    } else {/*from  w w w  .  j a va 2s.  c o m*/
        System.out.println("db vendor supports ParameterMetaData");
        // find out the number of dynamic parameters
        int paramCount = paramMetaData.getParameterCount();
        System.out.println("paramCount=" + paramCount);
    }

    pstmt.close();
    conn.close();

}

From source file:Main.java

public static void main(String[] args) throws Exception {
    Connection conn = getConnection();

    Statement st = conn.createStatement();

    st.executeUpdate("create table survey (id int,myDate DATE);");
    String INSERT_RECORD = "insert into survey(id, myDate) values(?, ?)";

    PreparedStatement pstmt = conn.prepareStatement(INSERT_RECORD);
    pstmt.setString(1, "1");
    java.sql.Date sqlDate = new java.sql.Date(new java.util.Date().getTime());
    pstmt.setDate(2, sqlDate);//  ww  w .j  a v a2  s .  c  om

    pstmt.executeUpdate();

    System.out.println(
            "The type of the first parameter is: " + pstmt.getParameterMetaData().getParameterTypeName(1));
    System.out.println(pstmt.getParameterMetaData().isNullable(1));

    conn.close();
}

From source file:GetParamMetaData.java

public static void main(String args[]) {

    String url = "jdbc:mySubprotocol:myDataSource";

    Connection con;/*from  ww w.  j  a v a2s .co m*/
    PreparedStatement pstmt;
    ParameterMetaData pmd;

    String sql = "UPDATE COFFEES SET SALES = ? " + "WHERE COF_NAME = ?";

    try {

        Class.forName("myDriver.ClassName");

    } catch (java.lang.ClassNotFoundException e) {
        System.err.print("ClassNotFoundException: ");
        System.err.println(e.getMessage());
    }

    try {

        con = DriverManager.getConnection(url, "myLogin", "myPassword");

        pstmt = con.prepareStatement(sql);

        pmd = pstmt.getParameterMetaData();

        int totalDigits = pmd.getPrecision(1);
        int digitsAfterDecimal = pmd.getScale(1);
        boolean b = pmd.isSigned(1);
        System.out.println("The first parameter ");
        System.out.println("    has precision " + totalDigits);
        System.out.println("    has scale " + digitsAfterDecimal);
        System.out.println("    may be a signed number " + b);

        int count = pmd.getParameterCount();
        System.out.println("count is " + count);

        for (int i = 1; i <= count; i++) {
            int type = pmd.getParameterType(i);
            String typeName = pmd.getParameterTypeName(i);
            System.out.println("Parameter " + i + ":");
            System.out.println("    type is " + type);
            System.out.println("    type name is " + typeName);
        }

        pstmt.close();
        con.close();

    } catch (Exception e) {
        e.printStackTrace();

    }
}

From source file:org.thingsboard.server.service.install.sql.SqlDbHelper.java

private static void setColumnValue(int index, String column, CSVRecord record,
        PreparedStatement preparedStatement) throws SQLException {
    String value = record.get(column);
    int type = preparedStatement.getParameterMetaData().getParameterType(index + 1);
    preparedStatement.setObject(index + 1, value, type);
}

From source file:com.example.querybuilder.server.Jdbc.java

public static ParameterMetaData getParameterMetaData(PreparedStatement preparedStatement) {
    try {//from  w  ww  .  ja v  a2  s  .c  om
        return preparedStatement.getParameterMetaData();
    } catch (SQLException e) {
        throw new SqlRuntimeException(e);
    }
}

From source file:com.dynamobi.db.conn.couchdb.CouchUdx.java

/**
 * Called by a custom LucidDB function for each view.
 * @param userName - CouchDB user name/*  w  w w .j av a  2s . co  m*/
 * @param pw - CouchDB password
 * @param url - CouchDB REST URL
 * @param view - CouchDB REST view -- concatenated on the end of URL with
 *               a slash prefix if necessary.
 * @param limit - Limit parameter passed to couchdb
 * @param reduce - if false, we pass &amp;reduce=false to the view.
 * @param groupLevel - sent to view for group reduction, default 'EXACT'
 * Possible values: 'EXACT', sends &amp;group=true.
 * 'NONE': sends &amp;group=false.
 * 1-N: sends &amp;group_level=x to the view or summarizer. 1 says to group
 * on the first index of the array key, 2 says the first two indexes,
 * N all indexes (equivalent to 'EXACT').
 *
 * (the following should be done in logic rewrite rule?) TODO:
 * 'CALCULATE': typically set by the pushdown optimizer, instructs
 * this udx to best-guess what group level we can/should push down.
 * The basic idea is that if the columns in a GROUP BY statement belong
 * to objects defined as elements of the key array for the first key-value
 * pair returned by a view, we will push down the number of columns being
 * grouped by and ignore the grouping on the LucidDB end. Otherwise all
 * group by's will still be done by LucidDB.
 * @param resultInserter - Table for inserting results. Assumed to have the
 * necessary column names in the order we get them.
 */
public static void query(String userName, String pw, String url, String view, String limit, boolean reduce,
        String groupLevel, boolean outputJson, PreparedStatement resultInserter) throws SQLException {

    // Specialize so we can column names for our resultInserter
    // instead of assuming an order.
    ParameterMetaData pmd = resultInserter.getParameterMetaData();
    FarragoParameterMetaData fpmd = (FarragoParameterMetaData) pmd;
    int paramCount = fpmd.getParameterCount();
    String[] paramNames = new String[paramCount];
    for (int i = 0; i < paramCount; i++) {
        paramNames[i] = fpmd.getFieldName(i + 1); // JDBC offset
    }

    RowProducer producer = new RowProducer();
    JSONParser parser = new JSONParser();

    InputStreamReader in = getViewStream(userName, pw, url, view, limit, reduce, groupLevel, true);

    while (!producer.isDone()) {
        try {
            parser.parse(in, producer, true);
        } catch (Throwable e) { // IOException, ParseException
            throw new SQLException(e);
        }

        if (!producer.getKey().equals("key"))
            continue;
        Object key = producer.getValue();

        try {
            parser.parse(in, producer, true);
        } catch (Throwable e) { // IOException, ParseException
            throw new SQLException(e);
        }

        assert (producer.getKey().equals("value"));
        Object value = producer.getValue();

        if (outputJson) {
            // put key in first col, val in second col, escape.
            resultInserter.setString(1, key.toString());
            resultInserter.setString(2, value.toString());
            resultInserter.executeUpdate();
            continue;
        }

        Map<String, Object> params = new HashMap<String, Object>(paramNames.length);
        int dupes = mergeParams(params, key, "KEY");
        dupes += mergeParams(params, value, "VALUE");

        if (params.size() - dupes != paramNames.length) {
            // We have more params than columns..
            throw new SQLException("Read " + params.size() + " params and " + paramNames.length
                    + " columns, which need to match. Did you " + "add column(s) for both the key and value?");
        }

        for (int c = 0; c < paramNames.length; c++) {
            Object o = params.get(paramNames[c]);
            if (o != null) {
                resultInserter.setObject(c + 1, o);
            }
        }

        resultInserter.executeUpdate();
    }
}

From source file:com.example.querybuilder.server.Jdbc.java

public static void initializeParameters(PreparedStatement preparedStatement, Object... parameters) {
    for (int columnOffset = 0, columnNumber = 1; columnOffset < parameters.length; columnOffset++, columnNumber++) {
        Object value = parameters[columnOffset];
        try {//from ww w .  j av  a 2 s  . c o m
            if (value == null) {
                int parameterType = preparedStatement.getParameterMetaData().getParameterType(columnNumber);
                preparedStatement.setNull(columnNumber, parameterType);
            } else {
                preparedStatement.setObject(columnNumber, value);
            }
        } catch (SQLException e) {
            throw new SqlRuntimeException(
                    e.toString() + "\n" + "columnNumber=" + columnNumber + ", value=" + value);
        }
    }
}

From source file:org.springframework.jdbc.core.StatementCreatorUtils.java

/**
 * Set the specified PreparedStatement parameter to null,
 * respecting database-specific peculiarities.
 *//*from ww w.  ja va 2  s.c  om*/
private static void setNull(PreparedStatement ps, int paramIndex, int sqlType, @Nullable String typeName)
        throws SQLException {

    if (sqlType == SqlTypeValue.TYPE_UNKNOWN || sqlType == Types.OTHER) {
        boolean useSetObject = false;
        Integer sqlTypeToUse = null;
        if (!shouldIgnoreGetParameterType) {
            try {
                sqlTypeToUse = ps.getParameterMetaData().getParameterType(paramIndex);
            } catch (SQLException ex) {
                if (logger.isDebugEnabled()) {
                    logger.debug("JDBC getParameterType call failed - using fallback method instead: " + ex);
                }
            }
        }
        if (sqlTypeToUse == null) {
            // Proceed with database-specific checks
            sqlTypeToUse = Types.NULL;
            DatabaseMetaData dbmd = ps.getConnection().getMetaData();
            String jdbcDriverName = dbmd.getDriverName();
            String databaseProductName = dbmd.getDatabaseProductName();
            if (databaseProductName.startsWith("Informix")
                    || (jdbcDriverName.startsWith("Microsoft") && jdbcDriverName.contains("SQL Server"))) {
                // "Microsoft SQL Server JDBC Driver 3.0" versus "Microsoft JDBC Driver 4.0 for SQL Server"
                useSetObject = true;
            } else if (databaseProductName.startsWith("DB2") || jdbcDriverName.startsWith("jConnect")
                    || jdbcDriverName.startsWith("SQLServer") || jdbcDriverName.startsWith("Apache Derby")) {
                sqlTypeToUse = Types.VARCHAR;
            }
        }
        if (useSetObject) {
            ps.setObject(paramIndex, null);
        } else {
            ps.setNull(paramIndex, sqlTypeToUse);
        }
    } else if (typeName != null) {
        ps.setNull(paramIndex, sqlType, typeName);
    } else {
        ps.setNull(paramIndex, sqlType);
    }
}

From source file:io.kamax.mxisd.backend.sql.GenericSqlDirectoryProvider.java

protected void setParameters(PreparedStatement stmt, String searchTerm) throws SQLException {
    for (int i = 1; i <= stmt.getParameterMetaData().getParameterCount(); i++) {
        stmt.setString(i, searchTerm);/*  w  w w . j a  v  a 2 s .c o m*/
    }
}