// @Test
public void testSelect() throws TorqueException, SQLException {
Criteria crit = new Criteria();
crit.setDbName("eagle");
crit.addSelectColumn(new ColumnImpl("column1"));
crit.addSelectColumn(new ColumnImpl("column2"));
crit.addSelectColumn(new ColumnImpl("column2/100"));
crit.where(new ColumnImpl("column1"), SqlEnum.GREATER_EQUAL);
crit.addFrom("tableName");
crit.addAlias("column1", "c1");
crit.addGroupByColumn(new ColumnImpl("column1"));
crit.addAscendingOrderByColumn(new ColumnImpl("column3"));
crit.setLimit(1000);
Query query = SqlBuilder.buildQuery(crit);
String sql = query.toString();
System.out.println(sql);
Connection connection = Torque.getConnection();
PreparedStatement statement = connection.prepareStatement(sql);
statement.setInt(1, 1000);
try {
ResultSet result = statement.executeQuery();
} catch (SQLException ex) {
LOG.warn(ex.getMessage(), ex);
} finally {
connection.close();
}
}
/**
* @param tableName a <code>String</code> value that is used to identify the row
* @return a <code>boolean</code> value
* @exception TorqueException if a Torque error occurs.
* @exception Exception if another error occurs.
*/
public boolean exists(String tableName) throws Exception {
String query =
new StringBuffer(100)
.append("select ")
.append(TABLE_NAME)
.append(" where ")
.append(TABLE_NAME)
.append("='")
.append(tableName)
.append('\'')
.toString();
boolean exists = false;
Connection dbCon = null;
try {
dbCon = Torque.getConnection(databaseName);
Statement statement = dbCon.createStatement();
ResultSet rs = statement.executeQuery(query);
exists = rs.next();
statement.close();
} finally {
// Return the connection to the pool.
try {
dbCon.close();
} catch (Exception e) {
log.error("Release of connection failed.", e);
}
}
return exists;
}
/**
* Constructor. Provided as long as both Constructors, IDBroker(DatabaseInfo) and
* IDBroker(TableMap), are around.
*
* @param databaseName the name of the database for which this IdBroker provides Ids.
*/
private IDBroker(String databaseName) {
this.databaseName = databaseName;
configuration = Torque.getConfiguration();
// Start the housekeeper thread only if prefetch has not been disabled
if (configuration.getBoolean(DB_IDBROKER_PREFETCH, true)) {
houseKeeperThread = new Thread(this);
// Indicate that this is a system thread. JVM will quit only when
// there are no more active user threads. Settings threads spawned
// internally by Torque as daemons allows commandline applications
// using Torque terminate in an orderly manner.
houseKeeperThread.setDaemon(true);
houseKeeperThread.setName("Torque - ID Broker thread");
houseKeeperThread.start();
}
// Check for Transaction support. Give warning message if
// IDBroker is being used with a database that does not
// support transactions.
Connection dbCon = null;
try {
dbCon = Torque.getConnection(databaseName);
} catch (Throwable t) {
log.error("Could not open a connection to the database " + databaseName, t);
transactionsSupported = false;
}
try {
transactionsSupported = dbCon.getMetaData().supportsTransactions();
} catch (Exception e) {
log.warn(
"Could not read from connection Metadata"
+ " whether transactions are supported for the database "
+ databaseName,
e);
transactionsSupported = false;
} finally {
try {
// Return the connection to the pool.
dbCon.close();
} catch (Exception e) {
log.warn(
"Could not close the connection which was used "
+ "for testing whether transactions are supported",
e);
}
}
if (!transactionsSupported) {
log.warn(
"IDBroker is being used with db '"
+ databaseName
+ "', which does not support transactions. IDBroker "
+ "attempts to use transactions to limit the possibility "
+ "of duplicate key generation. Without transactions, "
+ "duplicate key generation is possible if multiple JVMs "
+ "are used or other means are used to write to the "
+ "database.");
}
}
/**
* Retrieve a multiple objects by pk
*
* @param pks List of primary keys
* @throws TorqueException Any exceptions caught during processing will be rethrown wrapped into a
* TorqueException.
*/
public static List retrieveByPKs(List pks) throws TorqueException {
Connection db = null;
List retVal = null;
try {
db = Torque.getConnection(DATABASE_NAME);
retVal = retrieveByPKs(pks, db);
} finally {
Torque.closeConnection(db);
}
return (retVal);
}
/**
* Retrieve a multiple objects by pk
*
* @param pks List of primary keys
* @throws TorqueException Any exceptions caught during processing will be rethrown wrapped into a
* TorqueException.
*/
public static List<TRoleListType> retrieveByPKs(List<ObjectKey> pks) throws TorqueException {
Connection db = null;
List<TRoleListType> retVal = null;
try {
db = Torque.getConnection(DATABASE_NAME);
retVal = retrieveByPKs(pks, db);
} finally {
Torque.closeConnection(db);
}
return retVal;
}
/**
* Retrieve a single object by pk
*
* @param pk the primary key
* @throws TorqueException Any exceptions caught during processing will be rethrown wrapped into a
* TorqueException.
* @throws NoRowsException Primary key was not found in database.
* @throws TooManyRowsException Primary key was not found in database.
*/
public static Exam retrieveByPK(ObjectKey pk)
throws TorqueException, NoRowsException, TooManyRowsException {
Connection db = null;
Exam retVal = null;
try {
db = Torque.getConnection(DATABASE_NAME);
retVal = retrieveByPK(pk, db);
} finally {
Torque.closeConnection(db);
}
return (retVal);
}
/**
* This method allows you to get the number of ids that are to be cached in memory. This is either
* stored in quantityStore or read from the db. (ie the value in ID_TABLE.QUANTITY). Though this
* method returns a BigDecimal for the quantity, it is unlikey the system could withstand whatever
* conditions would lead to really needing a large quantity, it is retrieved as a BigDecimal only
* because it is going to be added to another BigDecimal.
*
* @param tableName The name of the table we want to query.
* @param connection a Connection
* @return An int with the number of ids cached in memory.
*/
private BigDecimal getQuantity(String tableName, Connection connection) {
BigDecimal quantity = null;
// If prefetch is turned off we simply return 1
if (!configuration.getBoolean(DB_IDBROKER_PREFETCH, true)) {
quantity = new BigDecimal((double) 1);
}
// Initialize quantity, if necessary.
else if (quantityStore.containsKey(tableName)) {
quantity = (BigDecimal) quantityStore.get(tableName);
} else {
Connection dbCon = null;
try {
if (connection == null || configuration.getBoolean(DB_IDBROKER_USENEWCONNECTION, true)) {
// Get a connection to the db
dbCon = Torque.getConnection(databaseName);
}
// Read the row from the ID_TABLE.
BigDecimal[] results = selectRow(dbCon, tableName);
// QUANTITY column.
quantity = results[1];
quantityStore.put(tableName, quantity);
} catch (Exception e) {
quantity = new BigDecimal((double) 10);
} finally {
// Return the connection to the pool.
try {
dbCon.close();
} catch (Exception e) {
log.error("Release of connection failed.", e);
}
}
}
return quantity;
}
