Java Code Examples for java.sql.PreparedStatement#clearParameters()

The following examples show how to use java.sql.PreparedStatement#clearParameters() . These examples are extracted from open source projects. You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may want to check out the right sidebar which shows the related API usage.
Example 1
private void addContentReports(NewsletterReport newsletterReport, Connection conn) {
	PreparedStatement stat = null;
	try {
		stat = conn.prepareStatement(ADD_CONTENT_REPORT);
		int reportId = newsletterReport.getId();
		for (ContentReport contentReport : newsletterReport.getContentReports().values()) {
			stat.setInt(1, contentReport.getId());
			stat.setInt(2, reportId);
			stat.setString(3, contentReport.getContentId());
			stat.setString(4, contentReport.getTextBody());
			stat.setString(5, contentReport.getHtmlBody());
			stat.addBatch();
			stat.clearParameters();
		}
		stat.executeBatch();
	} catch (BatchUpdateException e) {
		this.processDaoException(e.getNextException(), "Error adding contents for sent newsletter", 
				"addContentReports");
	} catch (Throwable t) {
		this.processDaoException(t, "Error adding contents for sent newsletter", "addContentReports");
	} finally {
		closeDaoResources(null, stat);
	}
}
 
Example 2
/**
 * @param conn
 * @param idPId
 * @param claims
 * @throws SQLException
 */
private void addIdPClaims(Connection conn, int idPId, int tenantId, Claim[] claims)
        throws SQLException {
    PreparedStatement prepStmt = null;

    if (claims == null || claims.length == 0) {
        return;
    }

    try {
        // SP_IDP_ID, SP_IDP_CLAIM
        String sqlStmt = IdPManagementConstants.SQLQueries.ADD_IDP_CLAIMS_SQL;
        prepStmt = conn.prepareStatement(sqlStmt);
        for (Claim claim : claims) {
            prepStmt.setInt(1, idPId);
            prepStmt.setInt(2, tenantId);
            prepStmt.setString(3, claim.getClaimUri());
            prepStmt.addBatch();
            prepStmt.clearParameters();
        }
        prepStmt.executeBatch();
    } finally {
        IdentityDatabaseUtil.closeStatement(prepStmt);
    }
}
 
Example 3
Source Project: spanner-jdbc   File: NullValueKeyIT.java    License: MIT License 6 votes vote down vote up
private void updateRecords() throws SQLException {
  String sql = "update table_with_null_keys set name=? where id1=? and id2=?";
  PreparedStatement ps = getConnection().prepareStatement(sql);

  // records without null values
  ps.setString(1, "updated");
  ps.setLong(2, 1L);
  ps.setLong(3, 1L);
  int updateCount = ps.executeUpdate();
  getConnection().commit();
  assertEquals(1, updateCount);
  assertEquals("updated", getName(1L, 1L));

  // records with null values
  ps.clearParameters();
  ps.setString(1, "updated");
  ps.setLong(2, 3L);
  ps.setNull(3, Types.BIGINT);
  updateCount = ps.executeUpdate();
  getConnection().commit();
  assertEquals(1, updateCount);
  assertEquals("updated", getName(3L, null));
}
 
Example 4
private void addTagsRelationsRecord(IIdea idea, PreparedStatement stat) throws ApsSystemException {
	if (idea.getTags().size() > 0) {
		try {
			Iterator<String> codeIter = idea.getTags().iterator();

			while (codeIter.hasNext()) {
				String code = codeIter.next();
				int i = 1;
				stat.setString(i++, idea.getId());
				stat.setString(i++, code);
				stat.addBatch();
				stat.clearParameters();
			}
		} catch (SQLException e) {
			_logger.error("Errore in aggiunta record tabella collaboration_idea_tags {}", idea.getId(),  e);
		throw new RuntimeException("Errore in aggiunta record tabella collaboration_idea_tags", e);
		}
	}
}
 
Example 5
/**
 * Creates a PreparedStatement based on the current connection and the provided SQL expression.<BR>
 * <B>Important:</B> Never call the <B>close()</B> method on Prepared statements obtained
 * from <CODE>ConnectionBean</CODE> objects created with the <CODE>mapStatements</CODE> option.
 * @param sql The SQL statement.
 * @throws SQLException If something goes wrong
 * @return The {@link java.sql.PreparedStatement} object.<BR>
 */    
public PreparedStatement getPreparedStatement(String sql) throws SQLException{
    if(closed!=null)
        throw new SQLException("Connection closed!");
    lastUsed=new Date();
    lastStatement=sql;
    PreparedStatement stmt=(statements==null) ? null : (PreparedStatement)statements.get(sql);
    if(stmt==null){
        stmt=con.prepareStatement(sql);
        if(statements!=null)
            statements.put(sql, stmt);
    }
    else{
        try{
            // try - catch because
            // odbc.jdbc driver throws NullPoiterException on 
            // PreparedStatement.clearParameters
            //
            stmt.clearParameters();
        } catch(Exception ex){
            // eat exception
        }
    }
    return stmt;
}
 
Example 6
/**
 * @param conn
 * @param idPId
 * @param claims
 * @throws SQLException
 */
private void addIdPClaims(Connection conn, int idPId, int tenantId, Claim[] claims)
        throws SQLException {

    PreparedStatement prepStmt = null;

    if (claims == null || claims.length == 0) {
        return;
    }

    try {
        // SP_IDP_ID, SP_IDP_CLAIM
        String sqlStmt = IdPManagementConstants.SQLQueries.ADD_IDP_CLAIMS_SQL;
        prepStmt = conn.prepareStatement(sqlStmt);
        for (Claim claim : claims) {
            prepStmt.setInt(1, idPId);
            prepStmt.setInt(2, tenantId);
            prepStmt.setString(3, claim.getClaimUri());
            prepStmt.addBatch();
            prepStmt.clearParameters();
        }
        prepStmt.executeBatch();
    } finally {
        IdentityDatabaseUtil.closeStatement(prepStmt);
    }
}
 
Example 7
/**
 * Add a record in the table 'contentrelations' for every resource, page,
 * other content, role and category associated to the given content).
 * 
 * @param content
 * The current content.
 * @param conn
 * The connection to the database.
 * @throws ApsSystemException
 * when connection error are detected.
 */
protected void addContentRelationsRecord(Content content, Connection conn) throws ApsSystemException {
	PreparedStatement stat = null;
	try {
		stat = conn.prepareStatement(ADD_CONTENT_REL_RECORD);
		this.addCategoryRelationsRecord(content, true, stat);
		this.addGroupRelationsRecord(content, stat);
		EntityAttributeIterator attributeIter = new EntityAttributeIterator(content);
		while (attributeIter.hasNext()) {
			AttributeInterface currAttribute = (AttributeInterface) attributeIter.next();
			if (currAttribute instanceof IReferenceableAttribute) {
				IReferenceableAttribute cmsAttribute = (IReferenceableAttribute) currAttribute;
				List<CmsAttributeReference> refs = cmsAttribute.getReferences(this.getLangManager().getLangs());
				for (int i = 0; i < refs.size(); i++) {
					CmsAttributeReference ref = refs.get(i);
					stat.setString(1, content.getId());
					stat.setString(2, ref.getRefPage());
					stat.setString(3, ref.getRefContent());
					stat.setString(4, ref.getRefResource());
					stat.setString(5, null);
					stat.setString(6, null);
					stat.addBatch();
					stat.clearParameters();
				}
			}
		}
		stat.executeBatch();
	} catch (BatchUpdateException e) {
		_logger.error("Error saving record into contentrelations {}", content.getId(), e.getNextException());
		throw new RuntimeException("Error saving record into contentrelations " + content.getId(), e.getNextException());
	} catch (Throwable t) {
		_logger.error("Error saving record into contentrelations {}", content.getId(), t);
		throw new RuntimeException("Error saving record into contentrelations " + content.getId(), t);
	} finally {
		closeDaoResources(null, stat);
	}
}
 
Example 8
Source Project: wind-im   File: SiteGroupProfileDao.java    License: Apache License 2.0 5 votes vote down vote up
public boolean rmGroupProfile(String groupId) throws SQLException {
	long startTime = System.currentTimeMillis();
	String sql1 = "DELETE FROM " + GROUP_PROFILE_TABLE + "  WHERE site_group_id=?;";
	String sql2 = "DELETE FROM " + SQLConst.SITE_USER_GROUP + "  WHERE site_group_id=?;";

	int result1;
	int result2;
	Connection conn = null;
	PreparedStatement pst = null;
	try {
		conn = DatabaseConnection.getConnection();

		pst = conn.prepareStatement(sql1);
		pst.setString(1, groupId);
		result1 = pst.executeUpdate();

		pst.clearParameters();
		pst.close();

		pst = conn.prepareStatement(sql2);
		pst.setString(1, groupId);
		result2 = pst.executeUpdate();

	} catch (Exception e) {
		throw e;
	} finally {
		DatabaseConnection.returnConnection(conn, pst);
	}

	LogUtils.dbDebugLog(logger, startTime, result1, sql1, groupId);
	LogUtils.dbDebugLog(logger, startTime, result2, sql2, groupId);
	return result1 > 0 && result2 > 0;

}
 
Example 9
Source Project: iaf   File: JdbcTransactionalStorage.java    License: Apache License 2.0 5 votes vote down vote up
private boolean isMessageDifferent(Connection conn, String messageId, S message) throws SQLException{
	PreparedStatement stmt = null;
	int paramPosition=0;
	
	try{
		// preparing database query statement.
		stmt = conn.prepareStatement(selectDataQuery2);
		stmt.clearParameters();
		stmt.setString(++paramPosition, messageId);
		// executing query, getting message as response in a result set.
		ResultSet rs = stmt.executeQuery();
		// if rs.next() needed as you can not simply call rs.
		if (rs.next()) {
			String dataBaseMessage = retrieveObject(rs, 1).toString();
			String inputMessage = message.toString();
			if (dataBaseMessage.equals(inputMessage)) {
				return false;
			}
			return true;
		}
		return true;
	} catch (Exception e) {
		log.warn("Exception comparing messages", e);
		return true;
	} finally {
		if (stmt != null) {
			stmt.close();
		}
	}
}
 
Example 10
Source Project: Yui   File: TodoCommand.java    License: Apache License 2.0 5 votes vote down vote up
public void handleClear(MessageReceivedEvent e, String[] args) throws SQLException
{
    checkArgs(args, 2, "No todo ListName was specified. Usage: `" + getAliases().get(0) + " clear [ListName]`");

    String label = args[2];
    TodoList todoList = todoLists.get(label);
    if (todoList == null)
    {
        sendMessage(e, "Sorry, `" + label + "` isn't a known todo list.");
        return;
    }

    if (todoList.locked && !todoList.isAuthUser(e.getAuthor()))
    {
        sendMessage(e, "Sorry, the `" + label +"` todo list is locked and you do not have permission to modify it.");
        return;
    }

    int clearedEntries = 0;
    PreparedStatement removeTodoEntry = Database.getInstance().getStatement(REMOVE_TODO_ENTRY);
    for (Iterator<TodoEntry> it = todoList.entries.iterator(); it.hasNext();)
    {
        TodoEntry todoEntry = it.next();
        if (todoEntry.checked)
        {
            removeTodoEntry.setInt(1, todoEntry.id);
            if (removeTodoEntry.executeUpdate() == 0)
                throw new SQLException(REMOVE_TODO_ENTRY + " reported no updated rows!");
            removeTodoEntry.clearParameters();

            it.remove();
            clearedEntries++;
        }
    }
    sendMessage(e, "Cleared **" + clearedEntries + "** completed entries from the `" + label + "` todo list.");
}
 
Example 11
Source Project: Yui   File: TodoCommand.java    License: Apache License 2.0 5 votes vote down vote up
public void handleRemove(MessageReceivedEvent e, String[] args) throws SQLException
{
    checkArgs(args, 2, "No todo ListName was specified. Usage: `" + getAliases().get(0) + " remove [ListName]`");

    String label = args[2].toLowerCase();
    TodoList todoList = todoLists.get(label);
    if (todoList == null)
    {
        sendMessage(e, "Sorry, `" + label + "` isn't a known todo list.");
        return;
    }

    if (todoList.locked && !todoList.isAuthUser(e.getAuthor()))
    {
        sendMessage(e, "Sorry, the `" + label +"` todo list is locked and you do not have permission to modify it.");
        return;
    }

    PreparedStatement removeTodoList = Database.getInstance().getStatement(REMOVE_TODO_LIST);
    removeTodoList.setInt(1, todoList.id);
    if (removeTodoList.executeUpdate() == 0)
        throw new SQLException(REMOVE_TODO_LIST + " reported no updated rows!");
    removeTodoList.clearParameters();

    todoLists.remove(label);
    sendMessage(e, "Deleted the `" + label + "` todo list.");
}
 
Example 12
Source Project: evosql   File: FindFile.java    License: Apache License 2.0 5 votes vote down vote up
/**
 * @throws SQLException
 */
static void fillPath(String path, String name,
                     PreparedStatement prep) throws SQLException {

    File f = new File(path);

    if (f.isFile()) {

        // Clear all Parameters of the PreparedStatement
        prep.clearParameters();

        // Fill the first parameter: Path
        prep.setString(1, path);

        // Fill the second parameter: Name
        prep.setString(2, name);

        // Its a file: add it to the table
        prep.execute();
    } else if (f.isDirectory()) {
        if (!path.endsWith(File.separator)) {
            path += File.separator;
        }

        String[] list = f.list();

        // Process all files recursivly
        for (int i = 0; (list != null) && (i < list.length); i++) {
            fillPath(path + list[i], list[i], prep);
        }
    }
}
 
Example 13
@Test
public void assertClearParameters() throws SQLException {
    for (PreparedStatement each : preparedStatements) {
        Object obj = new Object();
        each.setObject(1, obj);
        each.setObject(2, obj, 0);
        each.setObject(3, null);
        each.setObject(4, null);
        each.setObject(5, obj, 0, 0);
        assertThat(((ShardingSpherePreparedStatement) each).getParameters().size(), is(5));
        each.clearParameters();
        assertTrue(((ShardingSpherePreparedStatement) each).getParameters().isEmpty());
    }
}
 
Example 14
Source Project: cosmic   File: IPRangeConfig.java    License: Apache License 2.0 5 votes vote down vote up
private boolean isPublicIPAllocated(final long ip, final long vlanDbId, final PreparedStatement stmt) {
    try (ResultSet rs = stmt.executeQuery()) {
        stmt.clearParameters();
        stmt.setLong(1, ip);
        stmt.setLong(2, vlanDbId);
        if (rs.next()) {
            return (rs.getString("allocated") != null);
        } else {
            return false;
        }
    } catch (final SQLException ex) {
        System.out.println(ex.getMessage());
        return true;
    }
}
 
Example 15
@Override
protected boolean insertBatch(PreparedStatement pstmt)
    throws SQLException {
  int i = 0;
  pstmt.clearParameters();
  for (JdbcEntryData pendingEntry : PostgresBufferedInserter.this.pendingInserts) {
    for (JdbcEntryDatum datum : pendingEntry) {
      pstmt.setObject(++i, datum.getVal());
    }
  }
  log.debug("Executing SQL " + pstmt);
  return pstmt.execute();
}
 
Example 16
Source Project: canal-1.1.3   File: DBTest.java    License: Apache License 2.0 4 votes vote down vote up
@Test
public void test01() throws SQLException {
    DruidDataSource dataSource = new DruidDataSource();
    // dataSource.setDriverClassName("oracle.jdbc.OracleDriver");
    // dataSource.setUrl("jdbc:oracle:thin:@127.0.0.1:49161:XE");
    // dataSource.setUsername("mytest");
    // dataSource.setPassword("m121212");

    dataSource.setDriverClassName("com.mysql.jdbc.Driver");
    dataSource.setUrl("jdbc:mysql://127.0.0.1:3306/mytest?useUnicode=true");
    dataSource.setUsername("root");
    dataSource.setPassword("121212");

    dataSource.setInitialSize(1);
    dataSource.setMinIdle(1);
    dataSource.setMaxActive(2);
    dataSource.setMaxWait(60000);
    dataSource.setTimeBetweenEvictionRunsMillis(60000);
    dataSource.setMinEvictableIdleTimeMillis(300000);

    dataSource.init();

    Connection conn = dataSource.getConnection();

    conn.setAutoCommit(false);
    PreparedStatement pstmt = conn
        .prepareStatement("insert into user (id,name,role_id,c_time,test1,test2) values (?,?,?,?,?,?)");

    java.util.Date now = new java.util.Date();
    for (int i = 1; i <= 10000; i++) {
        pstmt.clearParameters();
        pstmt.setLong(1, (long) i);
        pstmt.setString(2, "test_" + i);
        pstmt.setLong(3, (long) i % 4 + 1);
        pstmt.setDate(4, new java.sql.Date(now.getTime()));
        pstmt.setString(5, null);
        pstmt.setBytes(6, null);

        pstmt.execute();
        if (i % 5000 == 0) {
            conn.commit();
        }
    }
    conn.commit();

    pstmt.close();

    // Statement stmt = conn.createStatement();
    // ResultSet rs = stmt.executeQuery("select * from user t where 1=2");
    //
    // ResultSetMetaData rsm = rs.getMetaData();
    // int cnt = rsm.getColumnCount();
    // for (int i = 1; i <= cnt; i++) {
    // System.out.println(rsm.getColumnName(i) + " " + rsm.getColumnType(i));
    // }

    // rs.close();
    // stmt.close();

    // PreparedStatement pstmt = conn
    // .prepareStatement("insert into tb_user (id,name,role_id,c_time,test1,test2)
    // values (?,?,?,?,?,?)");
    // pstmt.setBigDecimal(1, new BigDecimal("5"));
    // pstmt.setString(2, "test");
    // pstmt.setBigDecimal(3, new BigDecimal("1"));
    // pstmt.setDate(4, new Date(new java.util.Date().getTime()));
    // byte[] a = { (byte) 1, (byte) 2 };
    // pstmt.setBytes(5, a);
    // pstmt.setBytes(6, a);
    // pstmt.execute();
    //
    // pstmt.close();

    conn.close();
    dataSource.close();
}
 
Example 17
protected void insertMetricRecords(Connection conn, TimelineMetrics metrics)
  throws SQLException, IOException {

  List<TimelineMetric> timelineMetrics = metrics.getMetrics();
  if (timelineMetrics == null || timelineMetrics.isEmpty()) {
    LOG.debug("Empty metrics insert request.");
    return;
  }

  PreparedStatement metricRecordStmt = null;

  try {
    metricRecordStmt = conn.prepareStatement(String.format(
      UPSERT_METRICS_SQL, METRICS_RECORD_TABLE_NAME));

    for (TimelineMetric metric : timelineMetrics) {
      metricRecordStmt.clearParameters();

      if (LOG.isTraceEnabled()) {
        LOG.trace("host: " + metric.getHostName() + ", " +
          "metricName = " + metric.getMetricName() + ", " +
          "values: " + metric.getMetricValues());
      }
      double[] aggregates =  AggregatorUtils.calculateAggregates(
        metric.getMetricValues());

      byte[] uuid = metadataManager.getUuid(metric, true);
      if (uuid == null) {
        LOG.error("Error computing UUID for metric. Cannot write metrics : " + metric.toString());
        continue;
      }
      metricRecordStmt.setBytes(1, uuid);
      metricRecordStmt.setLong(2, metric.getStartTime());
      metricRecordStmt.setDouble(3, aggregates[0]);
      metricRecordStmt.setDouble(4, aggregates[1]);
      metricRecordStmt.setDouble(5, aggregates[2]);
      metricRecordStmt.setInt(6, (int) aggregates[3]);
      String json = TimelineUtils.dumpTimelineRecordtoJSON(metric.getMetricValues());
      metricRecordStmt.setString(7, json);

      try {
        int row = metricRecordStmt.executeUpdate();
        LOG.info("Inserted " + row + " rows.");
      } catch (SQLException sql) {
        LOG.error(sql);
      }
    }

    conn.commit();

  } finally {
    if (metricRecordStmt != null) {
      try {
        metricRecordStmt.close();
      } catch (SQLException e) {
        // Ignore
      }
    }
  }
}
 
Example 18
Source Project: adt   File: AbstractMysqlHandler.java    License: Apache License 2.0 4 votes vote down vote up
public int executeUpdate(String sql, int shardIndex, List<Object> parameters) throws Exception{
    
    final int maxTryCount = 3;
    Exception lastException = null;
    for(int currentTryCount = 1; currentTryCount <= maxTryCount; currentTryCount ++){
        
        Connection conn = null;
        try{
            conn = dataSourceList.get(shardIndex).getConnection();
            conn.setAutoCommit(true);
            
            final PreparedStatement pstmt = conn.prepareStatement(sql);
            for(int i=0; i<parameters.size(); i++){
                pstmt.setObject(i+1, parameters.get(i));
            }
            int result = pstmt.executeUpdate();
            pstmt.clearParameters();
            return result;
        }catch(Exception e){
            
            LOGGER.error("Failed to executeUpdate. "
                    + "(" + currentTryCount + "/" + maxTryCount + ") " 
                    + ", sql=" + sql 
                    + ", shardIndex=" + shardIndex 
                    + ", param=" + parameters, e);
            lastException = e;
            
        }finally{
            if(conn != null){
                conn.close();
            }
        }
    }
    
    if(lastException != null){
        throw lastException;
    }
    else{
        throw new IllegalStateException("retry failed, but no exception. you should find this bug and fix it!!");
    }
    
}
 
Example 19
/** {@inheritDoc} */
@Override public boolean test(Map<Object, Object> ctx) throws Exception {
    PreparedStatement stm = (PreparedStatement)ctx.get(0);

    if (stm == null) {
        stm = createStatement();

        stms.add(stm);

        ctx.put(0, stm);
    }

    double salary = ThreadLocalRandom.current().nextDouble() * args.range() * 1000;

    double maxSalary = salary + 1000;

    stm.clearParameters();

    stm.setDouble(1, salary);
    stm.setDouble(2, maxSalary);

    ResultSet rs = stm.executeQuery();

    while (rs.next()) {
        double sal = rs.getDouble("salary");

        if (sal < salary || sal > maxSalary)
            throw new Exception("Invalid person retrieved [min=" + salary + ", max=" + maxSalary + ']');
    }

    return true;
}
 
Example 20
Source Project: gemfirexd-oss   File: StatementHelper.java    License: Apache License 2.0 3 votes vote down vote up
/**
 * Reset a PreparedStatement. Closes its open ResultSet
 * and clears the parameters. While clearing the parameters
 * is not required since any future execution will override
 * them, it is done here to reduce the chance of errors.
 * E.g. using the wrong prepared statement for a operation
 * or not setting all the parameters.
 * It is assumed the prepared statement was just executed.
 * @throws SQLException 
 */
protected void reset(PreparedStatement ps) throws SQLException {
    ResultSet rs = ps.getResultSet();
    if (rs != null)
        rs.close();
    ps.clearParameters();
}