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

The following examples show how to use java.sql.PreparedStatement#clearParameters() . 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 check out the related API usage on the sidebar.
Example 1
Source File: IdPManagementDAO.java    From carbon-identity with Apache License 2.0 6 votes vote down vote up
/**
 * @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 2
Source File: IdPManagementDAO.java    From carbon-identity-framework with Apache License 2.0 6 votes vote down vote up
/**
 * @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 File: ConnectionBean.java    From jclic with GNU General Public License v2.0 6 votes vote down vote up
/**
 * 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 4
Source File: NullValueKeyIT.java    From spanner-jdbc with 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 5
Source File: IdeaDAO.java    From entando-components with GNU Lesser General Public License v3.0 6 votes vote down vote up
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 6
Source File: NewsletterDAO.java    From entando-components with GNU Lesser General Public License v3.0 6 votes vote down vote up
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 7
Source File: TodoCommand.java    From Yui with 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 8
Source File: PostgresBufferedInserter.java    From incubator-gobblin with Apache License 2.0 5 votes vote down vote up
@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 9
Source File: IPRangeConfig.java    From cosmic with 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 10
Source File: PreparedStatementAdapterTest.java    From shardingsphere with Apache License 2.0 5 votes vote down vote up
@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 11
Source File: FindFile.java    From evosql with 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 12
Source File: TodoCommand.java    From Yui with 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 13
Source File: JdbcTransactionalStorage.java    From iaf with 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 14
Source File: SiteGroupProfileDao.java    From wind-im with 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 15
Source File: ContentDAO.java    From entando-components with GNU Lesser General Public License v3.0 5 votes vote down vote up
/**
 * 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 16
Source File: DBTest.java    From canal-1.1.3 with 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
Source File: AbstractMiniHBaseClusterTest.java    From ambari-metrics with Apache License 2.0 4 votes vote down vote up
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 File: AbstractMysqlHandler.java    From adt with 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
Source File: IgniteJdbcSqlQueryBenchmark.java    From ignite with Apache License 2.0 3 votes vote down vote up
/** {@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 File: StatementHelper.java    From gemfirexd-oss with 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();
}