下面列出了怎么用java.sql.PreparedStatement的API类实例代码及写法,或者点击链接到github查看源代码。
/**
* Insert {@link CustOrder} at h2 database.
*
* @param o CustOrder.
*/
private void insertInDb(CustOrder o) throws SQLException {
try (PreparedStatement st = conn.prepareStatement(
"insert into \"custord\".CustOrder (_key, _val, orderId, rootOrderId, date, alias, archSeq, origOrderId) " +
"values(?, ?, ?, ?, ?, ?, ?, ?)")) {
int i = 0;
st.setObject(++i, o.orderId);
st.setObject(++i, o);
st.setObject(++i, o.orderId);
st.setObject(++i, o.rootOrderId);
st.setObject(++i, o.date);
st.setObject(++i, o.alias);
st.setObject(++i, o.archSeq);
st.setObject(++i, o.origOrderId);
st.executeUpdate();
}
}
@Test
public void testNonfirstColumnGroupBy() throws Exception {
String pTSDBTableName = generateUniqueName();
String query = "SELECT HOST FROM "+pTSDBTableName+" WHERE INST='abc' GROUP BY HOST";
String url = getUrl();
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
Connection conn = DriverManager.getConnection(url, props);
try {
initPTSDBTableValues(null, pTSDBTableName);
PreparedStatement statement = conn.prepareStatement(query);
ResultSet rs = statement.executeQuery();
assertTrue(rs.next());
assertEquals("abc-def-ghi", rs.getString(1));
assertFalse(rs.next());
} finally {
conn.close();
}
}
private ResultSet getUniqQuery5(Connection conn, int whichQuery,
int tid, boolean[] success) {
PreparedStatement stmt;
ResultSet rs = null;
success[0] = true;
try {
Log.getLogWriter().info("which query is -- " + uniqSelect[whichQuery]);
stmt = conn.prepareStatement(uniqSelect[whichQuery]);
Log.getLogWriter().info("data used in query -- tid: "+ tid);
stmt.setInt(1, tid);
stmt.setInt(2, tid);
rs = stmt.executeQuery();
} catch (SQLException se) {
SQLHelper.printSQLException(se);
if (!isTicket42422Fixed && se.getSQLState().equalsIgnoreCase("0A000")) {
Log.getLogWriter().info("get unsupported exception for union query, continue testing");
success[0] = true;
return null; //only suitable for cases when derby DB is available.
}
if (!SQLHelper.checkDerbyException(conn, se)) success[0] = false; //handle lock could not acquire or deadlock
else if (!SQLHelper.checkGFXDException(conn, se)) success[0] = false; //hand X0Z01 and #41471
else SQLHelper.handleSQLException(se);
}
return rs;
}
protected boolean deleteFromDerbyTable(Connection dConn, int whichDelete,
int[]cid, int []sid, List<SQLException> exList){
PreparedStatement stmt = getStmt(dConn, delete[whichDelete]);
if (stmt == null) return false;
int tid = getMyTid();
int count = -1;
try {
for (int i=0; i<cid.length; i++) {
verifyRowCount.put(tid+"_delete"+i, 0);
count = deleteFromTable(stmt, cid[i], sid[i], tid, whichDelete);
verifyRowCount.put(tid+"_delete"+i, new Integer(count));
}
} catch (SQLException se) {
if (!SQLHelper.checkDerbyException(dConn, se))
return false;
else SQLHelper.handleDerbySQLException(se, exList); //handle the exception
}
return true;
}
@Override
@Nullable
public String getPlayerUsername(UUID player) {
String sql = String.format("SELECT `%s` FROM `%s` WHERE `%s`=?",
PLAYERS_IGN, PLAYERS_TABLE_NAME, PLAYERS_UUID);
try (PreparedStatement statement = prep(claimChunk, connection, sql)) {
statement.setString(1, player.toString());
try (ResultSet result = statement.executeQuery()) {
if (result.next()) return result.getString(1);
}
} catch (Exception e) {
Utils.err("Failed to retrieve player username: %s", e.getMessage());
e.printStackTrace();
}
return null;
}
protected static void setStatementParameters(Object[] values, PreparedStatement ps, int[] columnTypes) throws SQLException {
int colIndex = 0;
for (Object value : values) {
colIndex++;
if (value instanceof SqlParameterValue) {
SqlParameterValue paramValue = (SqlParameterValue) value;
StatementCreatorUtils.setParameterValue(ps, colIndex, paramValue, paramValue.getValue());
}
else {
int colType;
if (columnTypes == null || columnTypes.length < colIndex) {
colType = SqlTypeValue.TYPE_UNKNOWN;
}
else {
colType = columnTypes[colIndex - 1];
}
StatementCreatorUtils.setParameterValue(ps, colIndex, colType, value);
}
}
}
@Override
public PreparedStatement prepareStatement(String sql, int[] columnIndexes) throws SQLException {
PreparedStatement statement;
if (getJdbcSpecific().getAutoKeyType() == AutoGeneratedKeysType.SINGLE) {
if (columnIndexes != null) {
statement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
}else{
logger.warn("Columns are null");
logger.info("Returning generated keys switched off");
statement = connection.prepareStatement(sql);
}
}else{
statement = connection.prepareStatement(sql, columnIndexes);
}
optimizeStatement(statement);
return statement;
}
@Override
public final Serializable performInsert(
String insertSQL,
SharedSessionContractImplementor session,
Binder binder) {
try {
// prepare and execute the insert
PreparedStatement insert = prepare( insertSQL, session );
try {
binder.bindValues( insert );
return executeAndExtract( insert, session );
}
finally {
releaseStatement( insert, session );
}
}
catch (SQLException sqle) {
throw session.getJdbcServices().getSqlExceptionHelper().convert(
sqle,
"could not insert: " + MessageHelper.infoString( persister ),
insertSQL
);
}
}
/**
*
* @return
* @throws Exception
*/
public List<String> getDatabaseList() throws Exception {
List<String> databaseList = null;
ApiConnection apiConnection = null;
PreparedStatement statement = null;
ResultSet resultSet = null;
try {
apiConnection = getConnection();
statement = apiConnection.getStmtSelect("SHOW DATABASES");
resultSet = statement.executeQuery();
databaseList = new ArrayList<String>();
while (resultSet.next()) {
databaseList.add(resultSet.getString(1));
}
Collections.sort(databaseList);
} finally {
close(resultSet);
close(statement);
close(apiConnection);
}
return databaseList;
}
@Before
public void createAndFillTestTable() throws SQLException {
if (tableExists("test"))
return;
getConnection().createStatement().execute(
"create table test (id int64 not null, name string(100) not null) primary key (id)");
getConnection().setAutoCommit(false);
PreparedStatement ps =
getConnection().prepareStatement("insert into test (id, name) values (?,?)");
for (int i = 0; i < NUMBER_OF_ROWS; i++) {
ps.setLong(1, i);
ps.setString(2, String.valueOf(i));
ps.addBatch();
}
ps.executeBatch();
getConnection().commit();
}
protected boolean updateToDerbyTableTidListTx(Connection conn, int cid, int sid,
BigDecimal price, int qty, int whichUpdate, int tid) throws SQLException {
PreparedStatement stmt = conn.prepareStatement(updateByTidList[whichUpdate]);
int count = -1;
Log.getLogWriter().info("update portfolio table in derby, myTid is " + getMyTid());
Log.getLogWriter().info("update statement is " + updateByTidList[whichUpdate]);
try {
count = updateToTableTidListTx(stmt, cid, sid, price, qty, whichUpdate, tid);
} catch (SQLException se) {
if (!SQLHelper.checkDerbyException(conn, se)) { //handles the deadlock of aborting
Log.getLogWriter().info("detected the lock issue, will try it again");
return false;
} else throw se;
}
Log.getLogWriter().info("derby updated " + count + " rows");
return true;
}
@Override
public List<String> oldAccountsNotActivated(Date date) {
List<String> usernames = new ArrayList<String>();
Timestamp timeBound = new Timestamp(date.getTime());
Connection conn = null;
PreparedStatement stat = null;
ResultSet res = null;
String username = null;
try {
conn = this.getConnection();
stat = conn.prepareStatement(USERNAMES_FROM_OLD_ACCOUNT_REQUESTS);
stat.setTimestamp(1, timeBound);
res = stat.executeQuery();
while (res.next()) {
username = res.getString("username");
usernames.add(username);
}
} catch (Throwable t) {
_logger.error("error extracting oldAccountsNotActivated", t);
throw new RuntimeException("error extracting oldAccountsNotActivated", t);
} finally {
closeDaoResources(res, stat, conn);
}
return usernames;
}
@Test
public void testCoerceLongToDecimal2() throws Exception {
String query = "SELECT entity_id FROM ATABLE WHERE organization_id=? AND x_integer <= x_decimal";
String url = PHOENIX_JDBC_URL + ";" + PhoenixRuntime.CURRENT_SCN_ATTRIB + "=" + (ts + 5); // Run query at timestamp 5
Properties props = new Properties(TEST_PROPERTIES);
Connection conn = DriverManager.getConnection(url, props);
try {
PreparedStatement statement = conn.prepareStatement(query);
statement.setString(1, tenantId);
ResultSet rs = statement.executeQuery();
assertTrue(rs.next());
assertEquals(ROW9, rs.getString(1));
assertFalse(rs.next());
} finally {
conn.close();
}
}
protected void insertToSecuritiesFulldataset (Connection conn, int sec_id, String symbol, BigDecimal price, String exchange, int tid){
//manually update fulldataset table for above entry.
try{
Log.getLogWriter().info(" Trigger behaviour is not defined for putDML hence deleting the row from TRADE.SECURITIES_FULLDATASET with data SEC_ID:" + sec_id );
conn.createStatement().execute("DELETE FROM TRADE.SECURITIES_FULLDATASET WHERE sec_id = " + sec_id );
PreparedStatement preparedInsertStmt = conn.prepareStatement("insert into trade.SECURITIES_fulldataset values (?,?,?,?,?)");
preparedInsertStmt.setInt(1, sec_id);
preparedInsertStmt.setString(2, symbol);
preparedInsertStmt.setBigDecimal(3, price);
preparedInsertStmt.setString(4, exchange);
preparedInsertStmt.setInt(5, tid);
Log.getLogWriter().info(" Trigger behaviour is not defined for putDML hence inserting the row into TRADE.SECURITIES_FULLDATASET with data SEC_ID:" + sec_id + ",SYMBOL" + symbol + ",EXCHANGE:" + exchange + ",PRICE:" + price + ".TID:" + tid );
preparedInsertStmt.executeUpdate();
} catch (SQLException se) {
Log.getLogWriter().info("Error while updating TRADE.SECURITIES_FULLDATASET table. It may cause Data inconsistency " + se.getMessage() );
}
}
@Test
public void testPercentileDiscAsc() throws Exception {
long ts = nextTimestamp();
String tenantId = getOrganizationId();
initATableValues(tenantId, null, getDefaultSplits(tenantId), null, ts);
String query = "SELECT PERCENTILE_DISC(0.9) WITHIN GROUP (ORDER BY A_INTEGER ASC) FROM aTable";
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB,
Long.toString(ts + 2)); // Execute at
// timestamp 2
Connection conn = DriverManager.getConnection(getUrl(), props);
try {
PreparedStatement statement = conn.prepareStatement(query);
ResultSet rs = statement.executeQuery();
assertTrue(rs.next());
int percentile_disc = rs.getInt(1);
assertEquals(9, percentile_disc);
assertFalse(rs.next());
} finally {
conn.close();
}
}
@Override
public void updateBook(Book book) throws SQLException {
final String updateBookString = "UPDATE books5 SET author = ?, createdBy = ?, createdById = ?, "
+ "description = ?, publishedDate = ?, title = ?, imageUrl = ? WHERE id = ?";
try (Connection conn = dataSource.getConnection();
PreparedStatement updateBookStmt = conn.prepareStatement(updateBookString)) {
updateBookStmt.setString(1, book.getAuthor());
updateBookStmt.setString(2, book.getCreatedBy());
updateBookStmt.setString(3, book.getCreatedById());
updateBookStmt.setString(4, book.getDescription());
updateBookStmt.setString(5, book.getPublishedDate());
updateBookStmt.setString(6, book.getTitle());
updateBookStmt.setString(7, book.getImageUrl());
updateBookStmt.setLong(8, book.getId());
updateBookStmt.executeUpdate();
}
}
@Override
public void add(final TransactionLog transactionLog) {
String sql = "INSERT INTO `transaction_log` (`id`, `transaction_type`, `data_source`, `sql`, `parameters`, `creation_time`) VALUES (?, ?, ?, ?, ?, ?);";
try (
Connection conn = dataSource.getConnection();
PreparedStatement preparedStatement = conn.prepareStatement(sql)) {
preparedStatement.setString(1, transactionLog.getId());
preparedStatement.setString(2, SoftTransactionType.BestEffortsDelivery.name());
preparedStatement.setString(3, transactionLog.getDataSource());
preparedStatement.setString(4, transactionLog.getSql());
preparedStatement.setString(5, new Gson().toJson(transactionLog.getParameters()));
preparedStatement.setLong(6, transactionLog.getCreationTime());
preparedStatement.executeUpdate();
} catch (final SQLException ex) {
throw new TransactionLogStorageException(ex);
}
}
@Override
@DB
//In the List<string> argument each string is a vlan. not a vlanRange.
public void add(final long dcId, final long physicalNetworkId, final List<String> vnets) {
final String insertVnet = "INSERT INTO `cloud`.`op_dc_vnet_alloc` (vnet, data_center_id, physical_network_id) VALUES ( ?, ?, ?)";
final TransactionLegacy txn = TransactionLegacy.currentTxn();
try {
txn.start();
final PreparedStatement stmt = txn.prepareAutoCloseStatement(insertVnet);
for (int i = 0; i <= vnets.size() - 1; i++) {
stmt.setString(1, vnets.get(i));
stmt.setLong(2, dcId);
stmt.setLong(3, physicalNetworkId);
stmt.addBatch();
}
stmt.executeBatch();
txn.commit();
} catch (final SQLException e) {
throw new CloudRuntimeException(e.getMessage());
}
}
private static void insertSkill(Skill skill) throws SQLException {
Connection conn = null;
PreparedStatement stmt = null;
try {
conn = connection();
stmt = conn.prepareStatement( "INSERT INTO skills VALUES(?, ?)" );
stmt.setInt( 1, skill.getId() );
stmt.setString( 2, skill.getName() );
stmt.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally {
if (stmt != null) {
stmt.close();
}
if (conn != null) {
conn.close();
}
}
}
@Override
public List<ODataEntry> readTable(String tableName) throws ODataServiceFault {
ResultSet resultSet = null;
Connection connection = null;
PreparedStatement statement = null;
try {
connection = initializeConnection();
String query = "select * from " + tableName;
statement = connection.prepareStatement(query);
resultSet = statement.executeQuery();
return createDataEntryCollectionFromRS(tableName, resultSet);
} catch (SQLException e) {
throw new ODataServiceFault(e, "Error occurred while reading entities from " + tableName + " table. :" +
e.getMessage());
} finally {
releaseResources(resultSet, statement);
releaseConnection(connection);
}
}
@Override
public String getValue(final String propertyName) {
try {
final Connection connection = dataSource.getConnection();
final PreparedStatement query =
connection.prepareStatement("SELECT VALUE FROM CONFIGURATIONS WHERE NAME = ?");
query.setString(1, propertyName);
final ResultSet value = query.executeQuery();
if (value.next()) {
return value.getString(1);
}
DbUtils.closeQuietly(value);
DbUtils.closeQuietly(query);
DbUtils.closeQuietly(connection);
} catch (final SQLException e) {
e.printStackTrace();
}
return null;
}
private static void printHintSimpleSelect(final DataSource dataSource) throws SQLException {
String sql = "SELECT i.* FROM t_order o JOIN t_order_item i ON o.order_id=i.order_id";
try (
HintManager hintManager = HintManager.getInstance();
Connection conn = dataSource.getConnection();
PreparedStatement preparedStatement = conn.prepareStatement(sql)) {
hintManager.addDatabaseShardingValue("t_order", "user_id", 10);
hintManager.addTableShardingValue("t_order", "order_id", 1001);
try (ResultSet rs = preparedStatement.executeQuery()) {
while (rs.next()) {
System.out.println(rs.getInt(1));
System.out.println(rs.getInt(2));
System.out.println(rs.getInt(3));
}
}
}
}
@Test
public void testFilterOnTrailingKeyColumn2() throws Exception {
String tablename=generateUniqueName();
String tenantId = getOrganizationId();
String query = "SELECT organization_id, \"DATE\", feature FROM "+tablename+" WHERE substr(organization_id,1,3)=? AND \"DATE\" > to_date(?)";
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
Connection conn = DriverManager.getConnection(getUrl(), props);
try {
initTableValues(tablename, tenantId, getSplits(tenantId));
PreparedStatement statement = conn.prepareStatement(query);
statement.setString(1, tenantId.substring(0,3));
statement.setString(2, DS4);
ResultSet rs = statement.executeQuery();
assertTrue(rs.next());
assertEquals(tenantId, rs.getString(1));
assertEquals(D5.getTime(), rs.getDate(2).getTime());
assertEquals(F3, rs.getString(3));
assertTrue(rs.next());
assertEquals(tenantId, rs.getString(1));
assertEquals(D6, rs.getDate(2));
assertEquals(F1, rs.getString(3));
assertFalse(rs.next());
} finally {
conn.close();
}
}
protected void deleteFromGFETable(Connection gConn, int whichDelete, int cid){
PreparedStatement stmt = getStmt(gConn, delete[whichDelete]);
if (SQLTest.testSecurity && stmt == null) {
if (SQLSecurityTest.prepareStmtException.get() != null) {
SQLSecurityTest.prepareStmtException.set(null);
return;
} else ; //need to find out why stmt is not obtained
} //work around #43244
if (setCriticalHeap && stmt == null) {
return; //prepare stmt may fail due to XCL54 now
}
if (stmt == null && alterTableDropColumn) {
Log.getLogWriter().info("prepare stmt failed due to missing column");
return; //prepare stmt may fail due to alter table now
}
int tid = getMyTid();
try {
deleteFromTable(stmt, cid, tid, whichDelete);
} catch (SQLException se) {
if ((se.getSQLState().equals("42500") || se.getSQLState().equals("42502"))
&& testSecurity) {
Log.getLogWriter().info("Got the expected exception for authorization," +
" continuing tests");
} else if (alterTableDropColumn && se.getSQLState().equals("42X14")) {
Log.getLogWriter().info("Got expected column not found exception in delete, continuing test");
} else
SQLHelper.handleSQLException(se); //handle the exception
}
}
public static int executeUpdateGetKeys(PreparedStatement stmt, Connection conn) {
try {
if (stmt.executeUpdate() > 0) {
ResultSet rs = stmt.getGeneratedKeys();
if (rs.next()) {
return rs.getInt(1);
}
}
} catch (SQLException e) {
logger.error("SQLException while executing Update and getting generated Keys for statement\n\t" + stmt, e);
} finally {
DB.cleanup(conn, stmt);
}
return -1;
}
@Test
public void testJoinWithDifferentDateJoinKeyTypes() throws Exception {
Connection conn = getConnection();
String query = "SELECT \"order_id\", c.name, o.\"DATE\" FROM " + getTableName(conn, JOIN_ORDER_TABLE_FULL_NAME) + " o INNER JOIN "
+ getTableName(conn, JOIN_CUSTOMER_TABLE_FULL_NAME) + " c ON o.\"customer_id\" = c.\"customer_id\" AND o.\"DATE\" = c.\"DATE\"";
try {
PreparedStatement statement = conn.prepareStatement(query);
ResultSet rs = statement.executeQuery();
assertTrue (rs.next());
assertEquals(rs.getString(1), "000000000000001");
assertEquals(rs.getString(2), "C4");
assertEquals(rs.getTimestamp(3), new Timestamp(format.parse("2013-11-22 14:22:56").getTime()));
assertTrue (rs.next());
assertEquals(rs.getString(1), "000000000000002");
assertEquals(rs.getString(2), "C3");
assertEquals(rs.getTimestamp(3), new Timestamp(format.parse("2013-11-25 10:06:29").getTime()));
assertTrue (rs.next());
assertEquals(rs.getString(1), "000000000000003");
assertEquals(rs.getString(2), "C2");
assertEquals(rs.getTimestamp(3), new Timestamp(format.parse("2013-11-25 16:45:07").getTime()));
assertTrue (rs.next());
assertEquals(rs.getString(1), "000000000000005");
assertEquals(rs.getString(2), "C5");
assertEquals(rs.getTimestamp(3), new Timestamp(format.parse("2013-11-27 09:37:50").getTime()));
assertFalse(rs.next());
} finally {
conn.close();
}
}
/**
* @see org.kuali.kfs.module.cam.document.dataaccess.DepreciationBatchDao#updateAssetsCreatedInLastFiscalPeriod(java.lang.Integer,
* java.lang.Integer)
*/
@Override
public void updateAssetsCreatedInLastFiscalPeriod(final Integer fiscalMonth, final Integer fiscalYear) {
// If we are in the last month of the fiscal year
if (fiscalMonth == 12) {
if ( LOG.isInfoEnabled() ) {
LOG.info(CamsConstants.Depreciation.DEPRECIATION_BATCH + "Starting updateAssetsCreatedInLastFiscalPeriod()");
}
// Getting last date of fiscal year
final UniversityDate lastFiscalYearDate = universityDateDao.getLastFiscalYearDate(fiscalYear);
if (lastFiscalYearDate == null) {
throw new IllegalStateException(kualiConfigurationService.getPropertyValueAsString(KFSKeyConstants.ERROR_UNIV_DATE_NOT_FOUND));
}
Collection<String> movableEquipmentObjectSubTypes = parameterService.getParameterValuesAsString(Asset.class, CamsConstants.Parameters.MOVABLE_EQUIPMENT_OBJECT_SUB_TYPES);
// Only update assets with a object sub type code equals to any MOVABLE_EQUIPMENT_OBJECT_SUB_TYPES.
if (!movableEquipmentObjectSubTypes.isEmpty()) {
getJdbcTemplate().update("UPDATE CM_CPTLAST_T SET CPTL_AST_IN_SRVC_DT=?, CPTL_AST_DEPR_DT=?, FDOC_POST_PRD_CD=? , FDOC_POST_YR=? WHERE CPTLAST_CRT_DT > ? AND FIN_OBJ_SUB_TYP_CD IN (" + buildINValues(movableEquipmentObjectSubTypes) + ")", new PreparedStatementSetter() {
@Override
public void setValues(PreparedStatement ps) throws SQLException {
ps.setDate(1, lastFiscalYearDate.getUniversityDate());
ps.setDate(2, lastFiscalYearDate.getUniversityDate());
ps.setString(3, fiscalMonth.toString());
ps.setInt(4, fiscalYear);
ps.setDate(5, lastFiscalYearDate.getUniversityDate());
}
});
}
if ( LOG.isInfoEnabled() ) {
LOG.info(CamsConstants.Depreciation.DEPRECIATION_BATCH + "Finished updateAssetsCreatedInLastFiscalPeriod()");
}
}
}
private void update(Collection<PlayerStats> stats) {
if (stats.isEmpty()) {
return;
}
//Save the stats to the database
try (Connection conn = dataSource.getConnection();
PreparedStatement stmt = conn.prepareStatement("UPDATE player_stats "
+ "SET kills=?, deaths=?, killstreak=?, mobkills=?, last_online=CURRENT_TIMESTAMP, playername=? "
+ "WHERE id=?")) {
conn.setAutoCommit(false);
for (PlayerStats stat : stats) {
stmt.setInt(1, stat.getKills());
stmt.setInt(2, stat.getDeaths());
stmt.setInt(3, stat.getKillstreak());
stmt.setInt(4, stat.getMobkills());
stmt.setString(5, stat.getPlayername());
stmt.setInt(6, stat.getId());
stmt.addBatch();
}
stmt.executeBatch();
conn.commit();
} catch (Exception ex) {
logger.error("Error updating profiles", ex);
}
}
public void setNull(int parameterIndex, int sqlType, String typeName) throws SQLException {
try {
if (this.wrappedStmt != null) {
((PreparedStatement) this.wrappedStmt).setNull(parameterIndex, sqlType, typeName);
} else {
throw SQLError.createSQLException("No operations allowed after statement closed", SQLError.SQL_STATE_GENERAL_ERROR, this.exceptionInterceptor);
}
} catch (SQLException sqlEx) {
checkAndFireConnectionError(sqlEx);
}
}
/**
* Retrieves Device Registration metadata data from store.
*
* @param username The username of the Device Registration.
* @return Collection of Device Registration.
* @throws FIDOAuthenticatorServerException when SQL statement can not be executed.
*/
public ArrayList<String> getDeviceMetadata(String username, String tenantDomain, String userStoreDomain)
throws FIDOAuthenticatorServerException {
ResultSet resultSet = null;
ArrayList<String> devicesMetadata = new ArrayList<String>();
if (log.isDebugEnabled()) {
log.debug("getDeviceRegistration inputs {username: " + username + ", tenantDomain: " + tenantDomain +
", userStoreDomain : " + userStoreDomain +"}");
}
Connection connection = IdentityDatabaseUtil.getDBConnection();
PreparedStatement preparedStatement = null;
try {
preparedStatement = connection.prepareStatement(FIDOAuthenticatorConstants.SQLQueries.GET_DEVICE_REGISTRATION_QUERY);
preparedStatement.setInt(1, IdentityTenantUtil.getTenantId(tenantDomain));
preparedStatement.setString(2, userStoreDomain);
preparedStatement.setString(3, username);
resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {
String deviceRemark = resultSet.getTimestamp(FIDOAuthenticatorConstants.U2F_DEVICE_METADATA).toString();
devicesMetadata.add(deviceRemark);
}
} catch (SQLException e) {
throw new FIDOAuthenticatorServerException(
"Error executing get device registration SQL : " +
FIDOAuthenticatorConstants.SQLQueries.GET_DEVICE_REGISTRATION_QUERY, e
);
} finally {
IdentityDatabaseUtil.closeAllConnections(connection, resultSet, preparedStatement);
}
return devicesMetadata;
}