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

/**
 * @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);
    }
}
 

/**
 * @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);
    }
}
 

/**
 * 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;
}
 

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));
}
 

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);
		}
	}
}
 

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);
	}
}
 

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.");
}
 

@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();
}
 

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;
    }
}
 

@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());
    }
}
 

/**
 * @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);
        }
    }
}
 

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.");
}
 

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();
		}
	}
}
 

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;

}
 

/**
 * 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);
	}
}
 

@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();
}
 

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
      }
    }
  }
}
 

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!!");
    }
    
}
 

/** {@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;
}
 

/**
 * 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();
}