List of usage examples for java.sql PreparedStatement setDate
void setDate(int parameterIndex, java.sql.Date x) throws SQLException;
java.sql.Date value using the default time zone of the virtual machine that is running the application. From source file:org.apache.phoenix.query.BaseTest.java
protected static void initJoinTableValues(String url, byte[][] splits, Long ts) throws Exception { if (ts == null) { ensureTableCreated(url, JOIN_CUSTOMER_TABLE_FULL_NAME, splits); ensureTableCreated(url, JOIN_ITEM_TABLE_FULL_NAME, splits); ensureTableCreated(url, JOIN_SUPPLIER_TABLE_FULL_NAME, splits); ensureTableCreated(url, JOIN_ORDER_TABLE_FULL_NAME, splits); } else {/* w ww .j a v a2 s . c o m*/ ensureTableCreated(url, JOIN_CUSTOMER_TABLE_FULL_NAME, splits, ts - 2); ensureTableCreated(url, JOIN_ITEM_TABLE_FULL_NAME, splits, ts - 2); ensureTableCreated(url, JOIN_SUPPLIER_TABLE_FULL_NAME, splits, ts - 2); ensureTableCreated(url, JOIN_ORDER_TABLE_FULL_NAME, splits, ts - 2); } SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); if (ts != null) { props.setProperty(CURRENT_SCN_ATTRIB, ts.toString()); } Connection conn = DriverManager.getConnection(url, props); try { conn.createStatement().execute("CREATE SEQUENCE my.seq"); // Insert into customer table PreparedStatement stmt = conn.prepareStatement("upsert into " + JOIN_CUSTOMER_TABLE_FULL_NAME + " (\"customer_id\", " + " NAME, " + " PHONE, " + " ADDRESS, " + " LOC_ID, " + " DATE) " + "values (?, ?, ?, ?, ?, ?)"); stmt.setString(1, "0000000001"); stmt.setString(2, "C1"); stmt.setString(3, "999-999-1111"); stmt.setString(4, "101 XXX Street"); stmt.setString(5, "10001"); stmt.setDate(6, new Date(format.parse("2013-11-01 10:20:36").getTime())); stmt.execute(); stmt.setString(1, "0000000002"); stmt.setString(2, "C2"); stmt.setString(3, "999-999-2222"); stmt.setString(4, "202 XXX Street"); stmt.setString(5, null); stmt.setDate(6, new Date(format.parse("2013-11-25 16:45:07").getTime())); stmt.execute(); stmt.setString(1, "0000000003"); stmt.setString(2, "C3"); stmt.setString(3, "999-999-3333"); stmt.setString(4, "303 XXX Street"); stmt.setString(5, null); stmt.setDate(6, new Date(format.parse("2013-11-25 10:06:29").getTime())); stmt.execute(); stmt.setString(1, "0000000004"); stmt.setString(2, "C4"); stmt.setString(3, "999-999-4444"); stmt.setString(4, "404 XXX Street"); stmt.setString(5, "10004"); stmt.setDate(6, new Date(format.parse("2013-11-22 14:22:56").getTime())); stmt.execute(); stmt.setString(1, "0000000005"); stmt.setString(2, "C5"); stmt.setString(3, "999-999-5555"); stmt.setString(4, "505 XXX Street"); stmt.setString(5, "10005"); stmt.setDate(6, new Date(format.parse("2013-11-27 09:37:50").getTime())); stmt.execute(); stmt.setString(1, "0000000006"); stmt.setString(2, "C6"); stmt.setString(3, "999-999-6666"); stmt.setString(4, "606 XXX Street"); stmt.setString(5, "10001"); stmt.setDate(6, new Date(format.parse("2013-11-01 10:20:36").getTime())); stmt.execute(); // Insert into item table stmt = conn.prepareStatement("upsert into " + JOIN_ITEM_TABLE_FULL_NAME + " (\"item_id\", " + " NAME, " + " PRICE, " + " DISCOUNT1, " + " DISCOUNT2, " + " \"supplier_id\", " + " DESCRIPTION) " + "values (?, ?, ?, ?, ?, ?, ?)"); stmt.setString(1, "0000000001"); stmt.setString(2, "T1"); stmt.setInt(3, 100); stmt.setInt(4, 5); stmt.setInt(5, 10); stmt.setString(6, "0000000001"); stmt.setString(7, "Item T1"); stmt.execute(); stmt.setString(1, "0000000002"); stmt.setString(2, "T2"); stmt.setInt(3, 200); stmt.setInt(4, 5); stmt.setInt(5, 8); stmt.setString(6, "0000000001"); stmt.setString(7, "Item T2"); stmt.execute(); stmt.setString(1, "0000000003"); stmt.setString(2, "T3"); stmt.setInt(3, 300); stmt.setInt(4, 8); stmt.setInt(5, 12); stmt.setString(6, "0000000002"); stmt.setString(7, "Item T3"); stmt.execute(); stmt.setString(1, "0000000004"); stmt.setString(2, "T4"); stmt.setInt(3, 400); stmt.setInt(4, 6); stmt.setInt(5, 10); stmt.setString(6, "0000000002"); stmt.setString(7, "Item T4"); stmt.execute(); stmt.setString(1, "0000000005"); stmt.setString(2, "T5"); stmt.setInt(3, 500); stmt.setInt(4, 8); stmt.setInt(5, 15); stmt.setString(6, "0000000005"); stmt.setString(7, "Item T5"); stmt.execute(); stmt.setString(1, "0000000006"); stmt.setString(2, "T6"); stmt.setInt(3, 600); stmt.setInt(4, 8); stmt.setInt(5, 15); stmt.setString(6, "0000000006"); stmt.setString(7, "Item T6"); stmt.execute(); stmt.setString(1, "invalid001"); stmt.setString(2, "INVALID-1"); stmt.setInt(3, 0); stmt.setInt(4, 0); stmt.setInt(5, 0); stmt.setString(6, "0000000000"); stmt.setString(7, "Invalid item for join test"); stmt.execute(); // Insert into supplier table stmt = conn.prepareStatement("upsert into " + JOIN_SUPPLIER_TABLE_FULL_NAME + " (\"supplier_id\", " + " NAME, " + " PHONE, " + " ADDRESS, " + " LOC_ID) " + "values (?, ?, ?, ?, ?)"); stmt.setString(1, "0000000001"); stmt.setString(2, "S1"); stmt.setString(3, "888-888-1111"); stmt.setString(4, "101 YYY Street"); stmt.setString(5, "10001"); stmt.execute(); stmt.setString(1, "0000000002"); stmt.setString(2, "S2"); stmt.setString(3, "888-888-2222"); stmt.setString(4, "202 YYY Street"); stmt.setString(5, "10002"); stmt.execute(); stmt.setString(1, "0000000003"); stmt.setString(2, "S3"); stmt.setString(3, "888-888-3333"); stmt.setString(4, "303 YYY Street"); stmt.setString(5, null); stmt.execute(); stmt.setString(1, "0000000004"); stmt.setString(2, "S4"); stmt.setString(3, "888-888-4444"); stmt.setString(4, "404 YYY Street"); stmt.setString(5, null); stmt.execute(); stmt.setString(1, "0000000005"); stmt.setString(2, "S5"); stmt.setString(3, "888-888-5555"); stmt.setString(4, "505 YYY Street"); stmt.setString(5, "10005"); stmt.execute(); stmt.setString(1, "0000000006"); stmt.setString(2, "S6"); stmt.setString(3, "888-888-6666"); stmt.setString(4, "606 YYY Street"); stmt.setString(5, "10006"); stmt.execute(); // Insert into order table stmt = conn.prepareStatement("upsert into " + JOIN_ORDER_TABLE_FULL_NAME + " (\"order_id\", " + " \"customer_id\", " + " \"item_id\", " + " PRICE, " + " QUANTITY," + " DATE) " + "values (?, ?, ?, ?, ?, ?)"); stmt.setString(1, "000000000000001"); stmt.setString(2, "0000000004"); stmt.setString(3, "0000000001"); stmt.setInt(4, 100); stmt.setInt(5, 1000); stmt.setTimestamp(6, new Timestamp(format.parse("2013-11-22 14:22:56").getTime())); stmt.execute(); stmt.setString(1, "000000000000002"); stmt.setString(2, "0000000003"); stmt.setString(3, "0000000006"); stmt.setInt(4, 552); stmt.setInt(5, 2000); stmt.setTimestamp(6, new Timestamp(format.parse("2013-11-25 10:06:29").getTime())); stmt.execute(); stmt.setString(1, "000000000000003"); stmt.setString(2, "0000000002"); stmt.setString(3, "0000000002"); stmt.setInt(4, 190); stmt.setInt(5, 3000); stmt.setTimestamp(6, new Timestamp(format.parse("2013-11-25 16:45:07").getTime())); stmt.execute(); stmt.setString(1, "000000000000004"); stmt.setString(2, "0000000004"); stmt.setString(3, "0000000006"); stmt.setInt(4, 510); stmt.setInt(5, 4000); stmt.setTimestamp(6, new Timestamp(format.parse("2013-11-26 13:26:04").getTime())); stmt.execute(); stmt.setString(1, "000000000000005"); stmt.setString(2, "0000000005"); stmt.setString(3, "0000000003"); stmt.setInt(4, 264); stmt.setInt(5, 5000); stmt.setTimestamp(6, new Timestamp(format.parse("2013-11-27 09:37:50").getTime())); stmt.execute(); conn.commit(); } finally { conn.close(); } }
From source file:org.kawanfw.test.api.client.InsertAndUpdateBlobTest.java
/** * Insert a blob/*from w ww .ja v a 2s. co m*/ * * @throws Exception * it any Exception occurs */ public void insertLoopPrepStatement(Connection connection, int numberToInsert, File blobFile) throws Exception { // We can now use our Remote JDBC Connection as a regular Connection! connection.setAutoCommit(false); // We will do all our remote insert in a SQL Transaction try { String sql = "insert into orderlog values ( ?, ?, ?, ?, ?, ?, ?, ?, ? )"; // Create a new Prepared Statement PreparedStatement prepStatement = null; MessageDisplayer.display(""); MessageDisplayer.display("Inserting " + numberToInsert + " orderlog..."); for (int customerId = 1; customerId < numberToInsert + 1; customerId++) { int i = 1; long theTime = new java.util.Date().getTime(); // We will insert a Blob (the image of the product). // The transfer will be done in streaming both on the client // and on the Servlet Server: we can upload/download very big // files. // InputStream in = new BufferedInputStream(new // FileInputStream(blobFile)); InputStream in = new FileInputStream(blobFile); prepStatement = connection.prepareStatement(sql); prepStatement.setInt(i++, customerId); prepStatement.setInt(i++, customerId); prepStatement.setString(i++, "Item Description No " + customerId); prepStatement.setBigDecimal(i++, new BigDecimal(customerId)); prepStatement.setDate(i++, new java.sql.Date(theTime)); prepStatement.setTimestamp(i++, new Timestamp(theTime)); prepStatement.setBinaryStream(i++, in, (int) blobFile.length()); // prepStatement.setBoolean(i++, false); SqlUtil sqlUtil = new SqlUtil(connection); if (sqlUtil.isIngres() || sqlUtil.isPostgreSQL()) { prepStatement.setInt(i++, 0); } else { prepStatement.setBoolean(i++, false); } prepStatement.setInt(i++, customerId); // SystemOutHandle.display("Before executeUpdate..."); prepStatement.executeUpdate(); prepStatement.close(); in.close(); } // We do either everything in a single transaction or nothing connection.commit(); // Commit is propagated on Server MessageDisplayer.display("Remote Commit Done on AceQL Server!"); } catch (Exception e) { connection.rollback(); throw e; } finally { connection.setAutoCommit(true); } }
From source file:org.openehealth.coms.cc.web_frontend.consentcreator.service.Database.java
/** * Retrieves the user whose name, forename and birthdate match the given * parameters./*from w w w. ja va2 s . c om*/ * * @param name * @param forename * @param birthdate * @return - null if no user was found */ public User retrieveUser(String name, String forename, Date birthdate, String gender) { User user = null; String sql = "SELECT * FROM users WHERE name = ? AND forename = ? AND birthdate = ? AND gender = ?;"; Connection connection = null; PreparedStatement statement = null; ResultSet result = null; try { connection = conFact.getConnection(); statement = connection.prepareStatement(sql); statement.setString(1, name); statement.setString(2, forename); java.sql.Date sqlDate = new java.sql.Date(birthdate.getTime()); statement.setDate(3, sqlDate); statement.setString(4, gender); result = statement.executeQuery(); if (result.next()) { user = new User(); user.setActive(result.getInt("active")); user.setBirthdate(result.getDate("birthdate")); user.setCity(result.getString("city")); user.setEmailaddress(result.getString("emailaddress")); user.setForename(result.getString("forename")); user.setGender(result.getString("gender")); user.setID(result.getString("id")); user.setName(result.getString("name")); user.setPrivileges(result.getInt("privileges")); user.setStreet(result.getString("street")); user.setZipcode(result.getInt("zipcode")); } else { throw new ServiceException("Zu diesen Stammdaten existiert kein Benutzer."); } } catch (Exception e) { Logger.getLogger(this.getClass()).error(e); } finally { try { connection.close(); } catch (Exception e) { Logger.getLogger(this.getClass()).error(e); } } return user; }
From source file:org.apache.phoenix.query.BaseTest.java
protected static void populateMultiCFTestTable(String tableName, Date date) throws SQLException { Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); Connection conn = DriverManager.getConnection(getUrl(), props); try {/*from w w w . j a v a2s. c o m*/ String upsert = "UPSERT INTO " + tableName + " VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"; PreparedStatement stmt = conn.prepareStatement(upsert); stmt.setString(1, "varchar1"); stmt.setString(2, "char1"); stmt.setInt(3, 1); stmt.setLong(4, 1L); stmt.setBigDecimal(5, new BigDecimal("1.1")); stmt.setString(6, "varchar_a"); stmt.setString(7, "chara"); stmt.setInt(8, 2); stmt.setLong(9, 2L); stmt.setBigDecimal(10, new BigDecimal("2.1")); stmt.setString(11, "varchar_b"); stmt.setString(12, "charb"); stmt.setInt(13, 3); stmt.setLong(14, 3L); stmt.setBigDecimal(15, new BigDecimal("3.1")); stmt.setDate(16, date == null ? null : new Date(date.getTime() + MILLIS_IN_DAY)); stmt.executeUpdate(); stmt.setString(1, "varchar2"); stmt.setString(2, "char2"); stmt.setInt(3, 2); stmt.setLong(4, 2L); stmt.setBigDecimal(5, new BigDecimal("2.2")); stmt.setString(6, "varchar_a"); stmt.setString(7, "chara"); stmt.setInt(8, 3); stmt.setLong(9, 3L); stmt.setBigDecimal(10, new BigDecimal("3.2")); stmt.setString(11, "varchar_b"); stmt.setString(12, "charb"); stmt.setInt(13, 4); stmt.setLong(14, 4L); stmt.setBigDecimal(15, new BigDecimal("4.2")); stmt.setDate(16, date); stmt.executeUpdate(); stmt.setString(1, "varchar3"); stmt.setString(2, "char3"); stmt.setInt(3, 3); stmt.setLong(4, 3L); stmt.setBigDecimal(5, new BigDecimal("3.3")); stmt.setString(6, "varchar_a"); stmt.setString(7, "chara"); stmt.setInt(8, 4); stmt.setLong(9, 4L); stmt.setBigDecimal(10, new BigDecimal("4.3")); stmt.setString(11, "varchar_b"); stmt.setString(12, "charb"); stmt.setInt(13, 5); stmt.setLong(14, 5L); stmt.setBigDecimal(15, new BigDecimal("5.3")); stmt.setDate(16, date == null ? null : new Date(date.getTime() + 2 * MILLIS_IN_DAY)); stmt.executeUpdate(); conn.commit(); } finally { conn.close(); } }
From source file:org.ojbc.adapters.analyticaldatastore.dao.AnalyticalDatastoreDAOImpl.java
@Override public Integer savePerson(final Person person) { log.debug("Inserting row into Person table"); final String personStatement = "INSERT into Person (PersonSexID, PersonRaceID, PersonBirthDate, PersonUniqueIdentifier) values (?,?,?,?)"; KeyHolder keyHolder = new GeneratedKeyHolder(); jdbcTemplate.update(new PreparedStatementCreator() { public PreparedStatement createPreparedStatement(Connection connection) throws SQLException { PreparedStatement ps = connection.prepareStatement(personStatement, new String[] { "PersonSexID", "PersonRaceID", "PersonBirthDate", "PersonUniqueIdentifier" }); if (person.getPersonSexID() != null) { ps.setInt(1, person.getPersonSexID()); } else { ps.setNull(1, java.sql.Types.NULL); }//from ww w.j a v a2 s . c o m if (person.getPersonRaceID() != null) { ps.setInt(2, person.getPersonRaceID()); } else { ps.setNull(2, java.sql.Types.NULL); } if (person.getPersonBirthDate() != null) { ps.setDate(3, new java.sql.Date(person.getPersonBirthDate().getTime())); } else { ps.setNull(3, java.sql.Types.NULL); } ps.setString(4, String.valueOf(person.getPersonUniqueIdentifier())); return ps; } }, keyHolder); return keyHolder.getKey().intValue(); }
From source file:org.kawanfw.test.api.client.InsertAndUpdateBlobTestPsqlOID.java
/** * Insert a blob/*from w w w . j ava 2s . co m*/ * * @throws Exception * it any Exception occurs */ public void insertLoopPrepStatement(Connection connection, int numberToInsert, File blobFile) throws Exception { // We can now use our Remote JDBC Connection as a regular Connection! connection.setAutoCommit(false); // We will do all our remote insert in a SQL Transaction try { String sql = "insert into orderlog_2 values ( ?, ?, ?, ?, ?, ?, ?, ?, ? )"; // Create a new Prepared Statement PreparedStatement prepStatement = null; MessageDisplayer.display(""); MessageDisplayer.display("Inserting " + numberToInsert + " orderlog_2..."); for (int customerId = 1; customerId < numberToInsert + 1; customerId++) { int i = 1; long theTime = new java.util.Date().getTime(); // We will insert a Blob (the image of the product). // The transfer will be done in streaming both on the client // and on the Servlet Server: we can upload/download very big // files. // InputStream in = new BufferedInputStream(new // FileInputStream(blobFile)); InputStream in = new FileInputStream(blobFile); prepStatement = connection.prepareStatement(sql); prepStatement.setInt(i++, customerId); prepStatement.setInt(i++, customerId); prepStatement.setString(i++, "Item Description No " + customerId); prepStatement.setBigDecimal(i++, new BigDecimal(customerId)); prepStatement.setDate(i++, new java.sql.Date(theTime)); prepStatement.setTimestamp(i++, new Timestamp(theTime)); prepStatement.setBinaryStream(i++, in, (int) blobFile.length()); // prepStatement.setBoolean(i++, false); SqlUtil sqlUtil = new SqlUtil(connection); if (sqlUtil.isIngres() || sqlUtil.isPostgreSQL()) { prepStatement.setInt(i++, 0); } else { prepStatement.setBoolean(i++, false); } prepStatement.setInt(i++, customerId); // SystemOutHandle.display("Before executeUpdate..."); prepStatement.executeUpdate(); prepStatement.close(); in.close(); } // We do either everything in a single transaction or nothing connection.commit(); // Commit is propagated on Server MessageDisplayer.display("Remote Commit Done on AceQL Server!"); } catch (Exception e) { connection.rollback(); throw e; } finally { connection.setAutoCommit(true); } }
From source file:org.openehealth.coms.cc.web_frontend.consentcreator.service.Database.java
/** * Stores the given user to the database. * /*from w ww . j av a 2s . c om*/ * @param user * @return */ public boolean storeUser(User user) { boolean stored = false; String sql = "INSERT INTO users (name, forename, birthdate, gender, street, zipcode, city, emailaddress, id, privileges, active, password) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);"; Connection connection = null; PreparedStatement statement = null; int result = -1; try { connection = conFact.getConnection(); statement = connection.prepareStatement(sql); statement.setString(1, user.getName()); statement.setString(2, user.getForename()); java.sql.Date sqlDate = new java.sql.Date(user.getBirthdate().getTime()); statement.setDate(3, sqlDate); statement.setString(4, user.getGender()); statement.setString(5, user.getStreet()); statement.setInt(6, user.getZipcode()); statement.setString(7, user.getCity()); statement.setString(8, user.getEmailaddress().toLowerCase()); statement.setString(9, user.getID()); statement.setInt(10, user.getPrivileges()); statement.setInt(11, user.isActive()); statement.setString(12, user.getPassword()); result = statement.executeUpdate(); if (result == 1) { stored = true; } else { throw new ServiceException("Der Benutzer konnte nicht gespeichert werden!"); } } catch (SQLException e) { Logger.getLogger(this.getClass()).error(e); } finally { try { connection.close(); } catch (SQLException e) { Logger.getLogger(this.getClass()).error(e); } } return stored; }
From source file:org.openehealth.coms.cc.web_frontend.consentcreator.service.Database.java
/** * Updates the given users data. The user is identified by his unique ID. * /*w ww . j a v a2 s . co m*/ * @param user * @return */ public boolean updateUser(User user) { boolean updated = false; String sql = "UPDATE users SET name = ?, forename = ?, birthdate = ?, gender = ?, street = ?, zipcode = ?, city = ?, emailaddress = ?, privileges = ?, active = ? WHERE id = ?;"; Connection connection = null; PreparedStatement statement = null; int result = -1; try { connection = conFact.getConnection(); statement = connection.prepareStatement(sql); statement.setString(1, user.getName()); statement.setString(2, user.getForename()); java.sql.Date sqlDate = new java.sql.Date(user.getBirthdate().getTime()); statement.setDate(3, sqlDate); statement.setString(4, user.getGender()); statement.setString(5, user.getStreet()); statement.setInt(6, user.getZipcode()); statement.setString(7, user.getCity()); statement.setString(8, user.getEmailaddress()); statement.setInt(9, user.getPrivileges()); statement.setLong(10, user.isActive()); statement.setString(11, user.getID()); result = statement.executeUpdate(); if (result == 1) { updated = true; } else if (result != 0) { connection.rollback(); throw new ServiceException("Es wurden mehrere mgliche Benutzer gefunden!"); } } catch (SQLException e) { Logger.getLogger(this.getClass()).error(e); throw new ServiceException("Es ist ein Fehler bei der Verarbeitung ihrer Anfrage aufgetreten."); } finally { try { connection.close(); } catch (SQLException e) { Logger.getLogger(this.getClass()).error(e); } } return updated; }
From source file:org.openehealth.coms.cc.web_frontend.consentcreator.service.Database.java
/** * Checks whether the user with the given data already exists or not. * /*from w w w.j a v a2 s . c o m*/ * @param user * @return */ public boolean existsUser(User user) { boolean existsUser = true; String sql = "SELECT * FROM users WHERE name = ? AND forename = ? AND birthdate = ? AND gender = ?;"; String sql2 = "SELECT * FROM users WHERE emailaddress = ?;"; Connection connection = null; PreparedStatement statement = null; ResultSet result = null; try { connection = conFact.getConnection(); statement = connection.prepareStatement(sql); statement.setString(1, user.getName()); statement.setString(2, user.getForename()); java.sql.Date sqlDate = new java.sql.Date(user.getBirthdate().getTime()); statement.setDate(3, sqlDate); statement.setString(4, user.getGender()); result = statement.executeQuery(); if (!result.next()) { existsUser = false; } if (!existsUser) { statement = connection.prepareStatement(sql2); statement.setString(1, user.getEmailaddress()); result = statement.executeQuery(); if (!result.next()) { existsUser = false; } } } catch (Exception e) { Logger.getLogger(this.getClass()).error(e); throw new ServiceException("Bei der Verarbeitung Ihrer Anfrage ist ein Fehler aufgetreten."); } finally { try { connection.close(); } catch (Exception e) { Logger.getLogger(this.getClass()).error(e); } } return existsUser; }
From source file:org.wso2.carbon.registry.core.jdbc.dao.JDBCTagsVersionDAO.java
/** * Method to persist tags./*from w ww .j a v a 2 s . c om*/ * * @param resource the resource * @param taggingDOs the tags to be persisted. * * @throws RegistryException if some error occurs while adding tags */ public void addTaggings(ResourceImpl resource, TaggingDO[] taggingDOs) throws RegistryException { JDBCDatabaseTransaction.ManagedRegistryConnection conn = JDBCDatabaseTransaction.getConnection(); long now = System.currentTimeMillis(); for (TaggingDO taggingDO : taggingDOs) { PreparedStatement ps = null; PreparedStatement ps2 = null; ResultSet result = null; try { String sql = "INSERT INTO REG_TAG (REG_TAG_NAME, REG_USER_ID, REG_TAGGED_TIME, " + "REG_TENANT_ID) VALUES (?,?,?,?)"; String dbProductName = conn.getMetaData().getDatabaseProductName(); ps = conn.prepareStatement(sql, new String[] { DBUtils.getConvertedAutoGeneratedColumnName(dbProductName, "REG_ID") }); ps.setString(1, taggingDO.getTagName()); ps.setString(2, taggingDO.getTaggedUserName()); ps.setDate(3, new Date(now)); ps.setInt(4, CurrentSession.getTenantId()); ps.executeUpdate(); result = ps.getGeneratedKeys(); if (result.next()) { int tagId = result.getInt(1); String sql2 = "INSERT INTO REG_RESOURCE_TAG (REG_TAG_ID, REG_VERSION, " + "REG_TENANT_ID) VALUES(?,?,?)"; ps2 = conn.prepareStatement(sql2); ps2.setInt(1, tagId); ps2.setLong(2, resource.getVersionNumber()); ps2.setInt(3, CurrentSession.getTenantId()); ps2.executeUpdate(); } } catch (SQLException e) { String msg = "Failed to add tags to resource " + resource.getPath() + ". " + e.getMessage(); log.error(msg, e); throw new RegistryException(msg, e); } finally { // closing open prepared statements & result sets before moving on to next iteration try { try { if (result != null) { result.close(); } } finally { try { if (ps != null) { ps.close(); } } finally { if (ps2 != null) { ps2.close(); } } } } catch (SQLException ex) { String msg = RegistryConstants.RESULT_SET_PREPARED_STATEMENT_CLOSE_ERROR; log.error(msg, ex); } } } }