下面列出了java.sql.PreparedStatement#clearParameters ( ) 实例代码,或者点击链接到github查看源代码,也可以在右侧发表评论。
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);
}
}
/**
* @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);
}
}
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);
}
}
}
/**
* 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;
}
/**
* @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);
}
}
/**
* 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);
}
}
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;
}
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 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.");
}
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.");
}
/**
* @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);
}
}
}
@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());
}
}
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;
}
}
@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();
}
@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();
}