List of usage examples for java.sql PreparedStatement setObject
void setObject(int parameterIndex, Object x) throws SQLException;
Sets the value of the designated parameter using the given object.
From source file:hnu.helper.DataBaseConnection.java
/** * Executes a SQL-String and returns a Resultset from database. * Can be used best for SELECT-Language. *///w w w. jav a 2s . c o m public ResultSet getRSfromStatement(String sql, Object[] parameters) { Connection conn = this.getDBConnection(); Statement stmt = null; PreparedStatement pStmt = null; ResultSet rs = null; log.debug("About to execute: " + sql); try { if (parameters == null || (parameters.length == 0)) { stmt = conn.createStatement(); rs = stmt.executeQuery(sql); } else { pStmt = conn.prepareStatement(sql); for (int i = 0; i < parameters.length; i++) { log.debug("parameter " + i + ": " + parameters[i]); pStmt.setObject(i + 1, parameters[i]); } rs = pStmt.executeQuery(); } log.debug(".. executed. "); } catch (SQLException sqlEx) { log.error("Error executing: '" + sql + "'", sqlEx); try { if (stmt != null) { stmt.close(); // only do that if there is an exception, since // closing the statement automatically closes the resultset! } if (pStmt != null) { pStmt.close(); } } catch (Exception ex) { log.error("Couldn't close the statement or connection.", ex); } finally { try { conn.close(); // only do this if there is an exception, since // closing the connection seems to close the rs. } catch (SQLException ex) { log.error("Couldn't close the connection.", ex); } } } return rs; }
From source file:org.apache.phoenix.end2end.DescColumnSortOrderTest.java
private void runQueryTest(String ddl, String[] columnNames, String[] projections, Object[][] rows, Object[][] expectedRows, WhereCondition whereCondition, HavingCondition havingCondition, OrderBy orderBy) throws Exception { Properties props = new Properties(TEST_PROPERTIES); Connection conn = DriverManager.getConnection(getUrl(), props); try {/*w ww .j a v a2 s. c om*/ conn.setAutoCommit(false); createTestTable(getUrl(), ddl); String columns = appendColumns(columnNames); String placeholders = appendPlaceholders(columnNames); String dml = "UPSERT INTO " + TABLE + " (" + columns + ") VALUES(" + placeholders + ")"; PreparedStatement stmt = conn.prepareStatement(dml); for (int row = 0; row < rows.length; row++) { for (int col = 0; col < rows[row].length; col++) { Object value = rows[row][col]; stmt.setObject(col + 1, value); } stmt.execute(); } conn.commit(); String selectClause = "SELECT " + appendColumns(projections) + " FROM " + TABLE; for (WhereCondition whereConditionClause : new WhereCondition[] { whereCondition, WhereCondition.reverse(whereCondition) }) { String query = WhereCondition.appendWhere(whereConditionClause, selectClause); query = HavingCondition.appendHaving(havingCondition, query); query = OrderBy.appendOrderBy(orderBy, query); runQuery(conn, query, expectedRows); } if (orderBy != null) { orderBy = OrderBy.reverse(orderBy); String query = WhereCondition.appendWhere(whereCondition, selectClause); query = HavingCondition.appendHaving(havingCondition, query); query = OrderBy.appendOrderBy(orderBy, query); runQuery(conn, query, reverse(expectedRows)); } } finally { conn.close(); } }
From source file:com.mirth.connect.server.userutil.DatabaseConnection.java
/** * Executes a prepared INSERT/UPDATE statement on the database and returns a CachedRowSet * containing any generated keys.//from w ww . ja v a2 s . c o m * * @param expression * The prepared statement to be executed. * @param parameters * The parameters for the prepared statement. * @return A CachedRowSet containing any generated keys. * @throws SQLException */ public CachedRowSet executeUpdateAndGetGeneratedKeys(String expression, List<Object> parameters) throws SQLException { PreparedStatement statement = null; try { statement = connection.prepareStatement(expression, Statement.RETURN_GENERATED_KEYS); logger.debug("executing prepared statement:\n" + expression); ListIterator<Object> iterator = parameters.listIterator(); while (iterator.hasNext()) { int index = iterator.nextIndex() + 1; Object value = iterator.next(); logger.debug("adding parameter: index=" + index + ", value=" + value); statement.setObject(index, value); } statement.executeUpdate(); CachedRowSet crs = new MirthCachedRowSet(); crs.populate(statement.getGeneratedKeys()); return crs; } catch (SQLException e) { throw e; } finally { DbUtils.closeQuietly(statement); } }
From source file:org.apache.phoenix.end2end.SortOrderFIT.java
private void runQueryTest(String ddl, String[] columnNames, String[] projections, Object[][] rows, Object[][] expectedRows, WhereCondition whereCondition, HavingCondition havingCondition, OrderBy orderBy) throws Exception { Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); Connection conn = DriverManager.getConnection(getUrl(), props); try {// w w w. j a va 2 s. c o m conn.setAutoCommit(false); createTestTable(getUrl(), ddl); String columns = appendColumns(columnNames); String placeholders = appendPlaceholders(columnNames); String dml = "UPSERT INTO " + TABLE + " (" + columns + ") VALUES(" + placeholders + ")"; PreparedStatement stmt = conn.prepareStatement(dml); for (int row = 0; row < rows.length; row++) { for (int col = 0; col < rows[row].length; col++) { Object value = rows[row][col]; stmt.setObject(col + 1, value); } stmt.execute(); } conn.commit(); String selectClause = "SELECT " + appendColumns(projections) + " FROM " + TABLE; for (WhereCondition whereConditionClause : new WhereCondition[] { whereCondition, WhereCondition.reverse(whereCondition) }) { String query = WhereCondition.appendWhere(whereConditionClause, selectClause); query = HavingCondition.appendHaving(havingCondition, query); query = OrderBy.appendOrderBy(orderBy, query); runQuery(conn, query, expectedRows); } if (orderBy != null) { orderBy = OrderBy.reverse(orderBy); String query = WhereCondition.appendWhere(whereCondition, selectClause); query = HavingCondition.appendHaving(havingCondition, query); query = OrderBy.appendOrderBy(orderBy, query); runQuery(conn, query, reverse(expectedRows)); } } finally { conn.close(); } }
From source file:com.nextep.designer.sqlclient.ui.services.impl.SQLClientService.java
private void fillPreparedStatement(PreparedStatement stmt, boolean isUpdate, boolean fillNullValues, ISQLRowResult row, Object newValue) throws SQLException { int i = 1;/*w w w . j a va2 s . c o m*/ // For update statement, we set new value as first argument if (isUpdate) { if (newValue != null) { stmt.setObject(i++, newValue); } else { stmt.setNull(i++, getSqlTypeFor(newValue)); } } // Passing all values final List<Object> values = row.getValues(); final List<Integer> columnTypes = row.getSqlTypes(); for (int rowIndex = 0; rowIndex < row.getValues().size(); rowIndex++) { Object o = values.get(rowIndex); if (o != null) { stmt.setObject(i++, o); } else { // If insert needed we explicitly set null values, otherwise it will be // written as "is null" in the statement if (fillNullValues) { stmt.setNull(i++, columnTypes.get(rowIndex)); } } } }
From source file:com.googlecode.fascinator.portal.services.impl.DatabaseServicesImpl.java
/** * Bind a parameter to a SQL statement. All Java types should be acceptable, * except NULL. Use 'IS NULL' in your SQL for this. * /*from w ww . ja v a 2 s .c o m*/ * @param sql The prepared statement to bind to. * @param index Specifies which placeholder to bind to (starts at 1). * @param data The data to bind to that placeholder. * @throws Exception if there is an error. */ @Override public void bindParam(PreparedStatement sql, int index, Object data) throws Exception { try { // log.debug("bindParam() ({}) => '{}'", index, data); if (data == null) { throw new Exception("NULL values are not accepted. Use 'IS NULL' or similar!"); } else { sql.setObject(index, data); // log.debug("bindParam() ({}) SUCCESS", index); } } catch (SQLException ex) { log.error("Error binding parameter:", ex); throw new Exception("Error binding parameter:", ex); } }
From source file:org.hsweb.ezorm.rdb.executor.AbstractJdbcSqlExecutor.java
/** * ?//w w w . j a v a 2 s . co m * * @param statement * @param info * @throws Exception */ protected void preparedParam(PreparedStatement statement, SQLInfo info) throws SQLException { int index = 1; //? for (Object object : info.getParam()) { if (object instanceof Date) statement.setTimestamp(index++, new java.sql.Timestamp(((Date) object).getTime())); else if (object instanceof byte[]) { statement.setBlob(index++, new ByteArrayInputStream((byte[]) object)); } else statement.setObject(index++, object); } }
From source file:com.tesora.dve.common.DBHelper.java
private boolean executePrepared(PreparedStatement ps, String query, List<Object> params) throws SQLException, PEException { if (ps == null) { throw new PEException("A prepared statement is not available to be executed - call prepare first"); }// ww w. ja v a2 s. c o m if (logger.isDebugEnabled() && query != null) logger.debug("Command: " + query); for (int i = 0; i < params.size(); i++) { ps.setObject(i + 1, params.get(i)); } boolean ret = ps.execute(); if (!ret) { // when stmt.execute returns false it means no result set is // expected get the number of rows affected rowCount = ps.getUpdateCount(); printLine(rowCount + " rows affected"); } else { // a prepStmt returning a result set was run resultSet = ps.getResultSet(); if (useBufferedQuery) resultSet.setFetchSize(Integer.MAX_VALUE); } return ret; }
From source file:com.alibaba.wasp.jdbc.TestPreparedStatement.java
public void testObject() throws SQLException { Statement stat = conn.createStatement(); ResultSet rs;//from ww w . j av a 2 s. co m stat.execute("CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR(255))"); stat.execute("INSERT INTO TEST VALUES(1, 'Hello')"); PreparedStatement prep = conn .prepareStatement("SELECT ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? FROM TEST"); prep.setObject(1, Boolean.TRUE); prep.setObject(2, "Abc"); prep.setObject(3, new BigDecimal("10.2")); prep.setObject(4, new Byte((byte) 0xff)); prep.setObject(5, new Short(Short.MAX_VALUE)); prep.setObject(6, new Integer(Integer.MIN_VALUE)); prep.setObject(7, new Long(Long.MAX_VALUE)); prep.setObject(8, new Float(Float.MAX_VALUE)); prep.setObject(9, new Double(Double.MAX_VALUE)); prep.setObject(10, Date.valueOf("2001-02-03")); prep.setObject(11, Time.valueOf("04:05:06")); prep.setObject(12, Timestamp.valueOf("2001-02-03 04:05:06.123456789")); prep.setObject(13, new java.util.Date(Date.valueOf("2001-02-03").getTime())); prep.setObject(14, new byte[] { 10, 20, 30 }); prep.setObject(15, new Character('a'), Types.OTHER); prep.setObject(16, "2001-01-02", Types.DATE); // converting to null seems strange... prep.setObject(17, "2001-01-02", Types.NULL); prep.setObject(18, "3.725", Types.DOUBLE); prep.setObject(19, "23:22:21", Types.TIME); prep.setObject(20, new java.math.BigInteger("12345"), Types.OTHER); rs = prep.executeQuery(); rs.next(); assertTrue(rs.getObject(1).equals(Boolean.TRUE)); assertTrue(rs.getObject(2).equals("Abc")); assertTrue(rs.getObject(3).equals(new BigDecimal("10.2"))); assertTrue(rs.getObject(4).equals((byte) 0xff)); assertTrue(rs.getObject(5).equals(new Short(Short.MAX_VALUE))); assertTrue(rs.getObject(6).equals(new Integer(Integer.MIN_VALUE))); assertTrue(rs.getObject(7).equals(new Long(Long.MAX_VALUE))); assertTrue(rs.getObject(8).equals(new Float(Float.MAX_VALUE))); assertTrue(rs.getObject(9).equals(new Double(Double.MAX_VALUE))); assertTrue(rs.getObject(10).equals(Date.valueOf("2001-02-03"))); assertEquals("04:05:06", rs.getObject(11).toString()); assertTrue(rs.getObject(11).equals(Time.valueOf("04:05:06"))); assertTrue(rs.getObject(12).equals(Timestamp.valueOf("2001-02-03 04:05:06.123456789"))); assertTrue(rs.getObject(13).equals(Timestamp.valueOf("2001-02-03 00:00:00"))); assertEquals(new byte[] { 10, 20, 30 }, (byte[]) rs.getObject(14)); assertTrue(rs.getObject(15).equals('a')); assertTrue(rs.getObject(16).equals(Date.valueOf("2001-01-02"))); assertTrue(rs.getObject(17) == null && rs.wasNull()); assertTrue(rs.getObject(18).equals(new Double(3.725))); assertTrue(rs.getObject(19).equals(Time.valueOf("23:22:21"))); assertTrue(rs.getObject(20).equals(new java.math.BigInteger("12345"))); // } else if(x instanceof java.io.Reader) { // return session.createLob(Value.CLOB, // TypeConverter.getInputStream((java.io.Reader)x), 0); // } else if(x instanceof java.io.InputStream) { // return session.createLob(Value.BLOB, (java.io.InputStream)x, 0); // } else { // return ValueBytes.get(TypeConverter.serialize(x)); stat.execute("DROP TABLE TEST"); }
From source file:broadwick.data.readers.DataFileReader.java
/** * Perform the insertion into the database. * @param connection the connection to the database. * @param tableName the name of the table into which the data will be put. * @param insertString the command used to insert a row into the database. * @param dataFile the [CSV] file that contained the data. * @param dateFormat the format of the date in the file. * @param insertedColInfo a map of column name to column in the data file. * @param dateFields a collection of columns in the csv file that contains date fields. * @return the number of rows inserted./*from w w w . j a va 2s . com*/ */ protected final int insert(final Connection connection, final String tableName, final String insertString, final String dataFile, final String dateFormat, final Map<String, Integer> insertedColInfo, final Collection<Integer> dateFields) { int inserted = 0; try { // Now do the insertion. log.trace("Inserting into {} via {}", tableName, insertString); PreparedStatement pstmt = connection.prepareStatement(insertString); log.trace("Prepared statement = {}", pstmt.toString()); try (FileInput instance = new FileInput(dataFile, ",")) { final StopWatch sw = new StopWatch(); sw.start(); List<String> data = instance.readLine(); while (data != null && !data.isEmpty()) { int parameterIndex = 1; for (Map.Entry<String, Integer> entry : insertedColInfo.entrySet()) { if (entry.getValue() == -1) { pstmt.setObject(parameterIndex, null); } else { final String value = data.get(entry.getValue() - 1); if (dateFields.contains(entry.getValue())) { int dateField = Integer.MAX_VALUE; if (value != null && !value.isEmpty()) { dateField = BroadwickConstants.getDate(value, dateFormat); } pstmt.setObject(parameterIndex, dateField); } else { pstmt.setObject(parameterIndex, value); } } parameterIndex++; } pstmt.addBatch(); try { pstmt.executeUpdate(); inserted++; } catch (SQLException ex) { if ("23505".equals(ex.getSQLState())) { //Ignore found duplicate from database view continue; } else { log.warn("Duplicate data found for {}: continuing despite errors: {}", data.get(0), ex.getLocalizedMessage()); log.trace("{}", Throwables.getStackTraceAsString(ex)); throw ex; } } if (inserted % 250000 == 0) { log.trace("Inserted {} rows in {}", inserted, sw.toString()); connection.commit(); pstmt.close(); pstmt = connection.prepareStatement(insertString); } data = instance.readLine(); } connection.commit(); } catch (IOException ex) { log.error("IO error : {}", ex.getLocalizedMessage()); log.trace("{}", Throwables.getStackTraceAsString(ex)); } catch (SQLException ex) { log.error("SQL Error : {}", ex.getLocalizedMessage()); log.trace("{}", Throwables.getStackTraceAsString(ex)); throw ex; } finally { pstmt.close(); } } catch (SQLException ex) { log.error("{}", ex.getLocalizedMessage()); log.trace("{}", Throwables.getStackTraceAsString(ex)); throw new BroadwickException(ex); } return inserted; }