Example usage for java.sql PreparedStatement setObject

List of usage examples for java.sql PreparedStatement setObject

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

Introduction

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

Prototype

void setObject(int parameterIndex, Object x) throws SQLException;

Source Link

Document

Sets the value of the designated parameter using the given object.

Usage

From source file:com.oltpbenchmark.benchmarks.auctionmark.AuctionMarkProfile.java

protected final void saveProfile(Connection conn) throws SQLException {
    this.loaderStopTime = new Timestamp(System.currentTimeMillis());

    // CONFIG_PROFILE
    Table catalog_tbl = this.benchmark.getCatalog().getTable(AuctionMarkConstants.TABLENAME_CONFIG_PROFILE);
    assert (catalog_tbl != null);
    PreparedStatement stmt = conn.prepareStatement(SQLUtil.getInsertSQL(catalog_tbl));
    int param_idx = 1;
    stmt.setObject(param_idx++, this.scale_factor); // CFP_SCALE_FACTOR
    stmt.setObject(param_idx++, this.loaderStartTime); // CFP_LOADER_START
    stmt.setObject(param_idx++, this.loaderStopTime); // CFP_LOADER_STOP
    stmt.setObject(param_idx++, this.users_per_itemCount.toJSONString()); // CFP_USER_ITEM_HISTOGRAM
    int result = stmt.executeUpdate();
    stmt.close();/*from  w  w  w . j av a  2 s.c  o  m*/
    assert (result == 1);

    if (LOG.isDebugEnabled())
        LOG.debug("Saving profile information into " + catalog_tbl);
    return;
}

From source file:au.com.ish.derbydump.derbydump.main.DumpTest.java

@Test
public void theDumpTest() throws Exception {
    // Create table
    StringBuilder createTableBuffer = new StringBuilder();
    createTableBuffer.append("CREATE TABLE ");
    createTableBuffer.append(Configuration.getConfiguration().getSchemaName());
    createTableBuffer.append(".");
    createTableBuffer.append(tableName);
    createTableBuffer.append(" (");

    StringBuilder insertBuffer = new StringBuilder();
    insertBuffer.append("INSERT INTO ");
    insertBuffer.append(RESOURCE_SCHEMA_NAME);
    insertBuffer.append(".");
    insertBuffer.append(tableName);//from  w w w . ja  v a  2 s. c o  m
    insertBuffer.append(" VALUES (");

    for (String col : columns) {
        createTableBuffer.append(col.toUpperCase());
        //String[] c = col.split(" ");
        //insertBuffer.append(c[0].toUpperCase().trim());
        insertBuffer.append("?");
        if (!columns[columns.length - 1].equals(col)) {
            createTableBuffer.append(", ");
            insertBuffer.append(",");
        }
    }

    createTableBuffer.append(")");
    insertBuffer.append(")");

    config.setTableRewriteProperty("testSkip", "--exclude--");
    config.setTableRewriteProperty("testRename", "testRenameNew");
    config.setTruncateTables(truncate);

    File f = new File("./build/outputs/" + tableName + ".sql");
    if (f.exists()) {
        f.delete();
    }
    f.mkdirs();

    config.setOutputFilePath(f.getCanonicalPath());

    Connection connection = db.createNewConnection();
    Statement statement = connection.createStatement();
    PreparedStatement ps = null;

    try {
        statement.execute(createTableBuffer.toString());
        connection.commit();
        //config.setTableRewriteProperty("TABLE2", "--exclude--");

        for (Object o : valuesToInsert) {
            Object[] vals = (Object[]) o;
            if (vals.length > 0) {
                ps = db.getConnection().prepareStatement(insertBuffer.toString());
                for (int i = 0; i < vals.length; i++) {
                    if (vals[i] instanceof InputStream) {
                        ps.setBinaryStream(i + 1, (InputStream) vals[i]);
                    } else {
                        ps.setObject(i + 1, vals[i]);
                    }
                }
                ps.execute();
                connection.commit();
            }
        }

        OutputThread output = new OutputThread();
        Thread writer = new Thread(output, "File_Writer");
        writer.start();

        new DatabaseReader(output);
        // Let the writer know that no more data is coming
        writer.interrupt();
        writer.join();

        // Now let's read the output and see what is in it
        List<String> lines = FileUtils.readLines(f);

        assertEquals("Missing foreign key operations", "SET FOREIGN_KEY_CHECKS = 0;", lines.get(0));
        assertEquals("Missing foreign key operations", "SET FOREIGN_KEY_CHECKS = 1;",
                lines.get(lines.size() - 1));

        if (!skipped) {
            assertTrue("LOCK missing", lines.contains("LOCK TABLES `" + outputTableName + "` WRITE;"));
            assertTrue("UNLOCK missing", lines.contains("UNLOCK TABLES;"));

            int index = lines.indexOf("LOCK TABLES `" + outputTableName + "` WRITE;");

            if (truncate) {
                assertTrue("TRUNCATE missing", lines.contains("TRUNCATE TABLE " + outputTableName + ";"));
                assertTrue("INSERT missing, got " + lines.get(index + 2),
                        lines.get(index + 2).startsWith("INSERT INTO " + outputTableName));
            } else {
                assertTrue("INSERT missing, got " + lines.get(index + 1),
                        lines.get(index + 1).startsWith("INSERT INTO " + outputTableName));
            }

            for (String s : validOutputs) {
                assertTrue("VALUES missing :" + s, lines.contains(s));
            }
        } else {
            assertTrue("LOCK missing", !lines.contains("LOCK TABLES `" + outputTableName + "` WRITE;"));
        }
    } catch (Exception e) {
        e.printStackTrace();
        fail("failed to create test data" + e.getMessage());
    } finally {
        if (ps != null) {
            ps.close();
        }
        statement.close();
        connection.close();
    }
}

From source file:org.pentaho.reporting.engine.classic.core.modules.misc.datafactory.sql.SimpleSQLReportDataFactory.java

private void parametrize(final DataRow parameters, final String[] params, final PreparedStatement pstmt,
        final boolean expandArrays, final int parameterOffset) throws SQLException {
    pstmt.clearParameters();/*from   w w  w.  j  a va  2  s  .  c om*/
    int paramIndex = parameterOffset;
    for (int i = 0; i < params.length; i++) {
        final String param = params[i];
        final Object pvalue = parameters.get(param);
        if (pvalue == null) {
            // this should work, but some driver are known to die here.
            // they should be fed with setNull(..) instead; something
            // we cant do as JDK1.2's JDBC does not define it.
            pstmt.setObject(paramIndex + 1, null);
            logger.debug("Parametrize: " + (paramIndex + 1) + " set to <null>");
            paramIndex++;
        } else if (expandArrays && pvalue instanceof Object[]) {
            final Object[] values = (Object[]) pvalue;
            if (values.length > 0) {
                for (int j = 0; j < values.length; j++) {
                    final Object ivalue = values[j];
                    if (ivalue instanceof java.sql.Date || ivalue instanceof java.sql.Time
                            || ivalue instanceof Timestamp) {
                        pstmt.setObject(paramIndex + 1, ivalue);
                    } else if (ivalue instanceof Date) {
                        // for now we're going to convert java.util.Date to java.sql.Timestamp
                        // this seems to be a better fit for most jdbc drivers/databases
                        // if problems come from this, we can create workaround them as discovered
                        final Date d = (Date) ivalue;
                        pstmt.setObject(paramIndex + 1, new Timestamp(d.getTime()));
                    } else {
                        pstmt.setObject(paramIndex + 1, ivalue);
                    }
                    logger.debug("Parametrize: Array: " + (paramIndex + 1) + ": " + ivalue);
                    paramIndex++;
                }
            } else {
                pstmt.setObject(paramIndex + 1, null);
                logger.debug("Parametrize: Array: " + (paramIndex + 1) + " set to <null> for empty array");
                paramIndex++;
            }
        } else {
            if (pvalue instanceof java.sql.Date || pvalue instanceof java.sql.Time
                    || pvalue instanceof Timestamp) {
                pstmt.setObject(paramIndex + 1, pvalue);
            } else if (pvalue instanceof Date) {
                // see comment above about java.util.Date/java.sql.Timestamp conversion
                final Date d = (Date) pvalue;
                pstmt.setObject(paramIndex + 1, new Timestamp(d.getTime()));
            } else {
                pstmt.setObject(paramIndex + 1, pvalue);
            }
            logger.debug("Parametrize: " + (paramIndex + 1) + ": " + pvalue);
            paramIndex++;
        }
    }
}

From source file:org.apache.ctakes.jdl.data.loader.XmlLoader.java

/**
 * @param jdlConnection// w  w w  . j a va2s .com
 *            the jdlConnection to manage
 */
@Override
public final void dataInsert(final JdlConnection jdlConnection) {
    String sql = getSqlInsert(loader);
    Number ncommit = loader.getCommit();
    int r = 0;
    try {
        Iterator<?> iterator = context.iteratePointers(loader.getXroot());
        while (iterator.hasNext()) {
            r++;
            NodePointer pointer = (NodePointer) iterator.next();
            Node node = (Node) pointer.getNode();
            JXPathContext context = JXPathContext.newContext(DomUtil.nodeToDocument(node));
            try {
                int c = 0;
                PreparedStatement preparedStatement = jdlConnection.getOpenConnection().prepareStatement(sql);
                if (ncommit == null) {
                    jdlConnection.setAutoCommit(true);
                } else {
                    jdlConnection.setAutoCommit(false);
                }
                for (Column column : loader.getColumn()) {
                    c++;
                    Object value = column.getConstant();
                    if (value == null) {
                        if (column.getSeq() != null) {
                            value = r + column.getSeq().intValue();
                        } else if (column.getXpath() != null) {
                            value = this.context.getValue(column.getXpath());
                        } else {
                            value = context.getPointer(column.getXleaf()).getValue();
                        }
                    }
                    preparedStatement.setObject(c, value);
                }
                executeBatch(preparedStatement);
                if (!jdlConnection.isAutoCommit() && (r % ncommit.intValue() == 0)) {
                    jdlConnection.commitConnection();
                }
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
    } catch (InstantiationException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    } catch (IllegalAccessException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    } catch (ClassNotFoundException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
    try {
        if (!jdlConnection.isAutoCommit()) {
            jdlConnection.commitConnection();
        }
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
}

From source file:org.riversun.d6.core.D6Crud.java

/**
 * Set object to the preparedStatement//from ww  w .  j a va 2  s  .com
 * 
 * @param parameterIndex
 * @param preparedStmt
 * @param value
 * @throws SQLException
 */
private void setObject(int parameterIndex, PreparedStatement preparedStmt, Object value) throws SQLException {

    preparedStmt.setObject(parameterIndex, value);
}

From source file:org.apache.oozie.util.db.SqlStatement.java

/**
 * Assign the values to Prepared Statement. setValues should be called to set the Real Values for place holders
 *
 * @param pStmt Prepared Statement// w  ww  . j a  v  a 2s  . c  o  m
 * @return PreparedStatement with values set
 * @throws SQLException
 */
public PreparedStatement prepare(PreparedStatement pStmt) throws SQLException {
    int i = 1;
    pStmt.clearParameters();
    for (Object value : this.values) {
        pStmt.setObject(i, value);
        i++;
    }
    return pStmt;
}

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

/**
 * Set the specified PreparedStatement parameter to null,
 * respecting database-specific peculiarities.
 *///w w  w  . j  a  v a2  s .co m
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:org.apache.hadoop.hive.jdbc.TestJdbcDriver.java

private PreparedStatement createPreapredStatementUsingSetObject(String sql) throws SQLException {
    PreparedStatement ps = con.prepareStatement(sql);

    ps.setObject(1, true); //setBoolean
    ps.setObject(2, true); //setBoolean

    ps.setObject(3, Short.valueOf("1")); //setShort
    ps.setObject(4, 2); //setInt
    ps.setObject(5, 3f); //setFloat
    ps.setObject(6, Double.valueOf(4)); //setDouble
    ps.setObject(7, "test'string\""); //setString
    ps.setObject(8, 5L); //setLong
    ps.setObject(9, (byte) 1); //setByte
    ps.setObject(10, (byte) 1); //setByte
    ps.setString(11, "2012-01-01"); //setString

    ps.setMaxRows(2);//from   www .  j av  a2 s .  c  om
    return ps;
}

From source file:QueryRunner.java

/**
 * Fill the <code>PreparedStatement</code> replacement parameters with 
 * the given objects./*from   w ww.  jav a 2 s . 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:org.ralasafe.db.sql.Query.java

private ResultSet getResultSet(User user, Map context, PreparedStatement pstmt) throws SQLException {
    int valueCount = values.size();
    for (int i = 0; i < valueCount; i++) {
        Value value = (Value) values.get(i);
        Object setValue = value.getValue(user, context);
        if (value.isBehindLike()) {
            if (setValue instanceof java.util.Date) {
                SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm");
                setValue = format.format((java.util.Date) setValue);
            }/*from   w  ww  .j  a v a 2 s.  c  o  m*/
            pstmt.setObject(i + 1, "%" + setValue + "%");
        } else {
            if (setValue instanceof java.util.Date) {
                java.util.Date utilDate = (java.util.Date) setValue;
                java.sql.Date sqlDate = new java.sql.Date(utilDate.getTime());
                pstmt.setDate(i + 1, sqlDate);
            } else {
                pstmt.setObject(i + 1, setValue);
            }
        }
    }
    // long start=System.currentTimeMillis();
    ResultSet executeQuery = pstmt.executeQuery();
    // long end=System.currentTimeMillis();
    // System.out.println( "Oracle Query Cost Time(ms):" + (end-start) );

    return executeQuery;
}