java.sql.PreparedStatement Java Examples

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

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

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