public void testScrollablePreparedStatement() throws Exception {
Statement stmt = con.createStatement();
makeTestTables(stmt);
makeObjects(stmt, 10);
stmt.close();
PreparedStatement pstmt =
con.prepareStatement(
"SELECT * FROM #test", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
ResultSet rs = pstmt.executeQuery();
assertTrue(rs.isBeforeFirst());
while (rs.next()) {}
// assertTrue( rs.isAfterLast() );
// This currently fails because the PreparedStatement
// Doesn't know it needs to create a cursored ResultSet.
// Needs some refactoring!!
while (rs.previous()) {}
// assertTrue( rs.isBeforeFirst() );
rs.close();
}
public String query7() {
String name, coach, output;
int budget;
String sqlText;
try {
sql = connection.createStatement();
sqlText =
"CREATE VIEW topScorer AS SELECT cid, MAX(goals) AS scores FROM player GROUP BY cid";
sql.executeUpdate(sqlText);
sqlText = "CREATE VIEW maxScore AS SELECT MAX(scores) AS maxscore FROM topScorer";
sql.executeUpdate(sqlText);
sqlText =
"CREATE VIEW topScorerTeam AS SELECT cid FROM topScorer JOIN maxScore ON (topScorer.scores = maxScore.maxscore)";
sql.executeUpdate(sqlText);
sqlText =
"CREATE VIEW budgetTeam AS SELECT cid, SUM(value) AS budget FROM player GROUP BY cid";
sql.executeUpdate(sqlText);
sqlText = "CREATE VIEW lowestBudget AS SELECT MIN(budget) AS minbudget FROM budgetTeam";
sql.executeUpdate(sqlText);
sqlText =
"CREATE VIEW lowestBudgetTeam AS SELECT cid, minbudget AS budget FROM lowestBudget JOIN budgetTeam ON (lowestBudget.minbudget = budgetTeam.budget)";
sql.executeUpdate(sqlText);
sqlText =
"CREATE VIEW countryLowestBudgetTopScorer AS SELECT topScorerTeam.cid AS cid, budget FROM topScorerTeam JOIN lowestBudgetTeam ON topScorerTeam.cid = lowestBudgetTeam.cid";
sql.executeUpdate(sqlText);
// We project Query7 over here.
sqlText =
"SELECT name, coach, budget FROM countryLowestBudgetTopScorer JOIN country ON (countryLowestBudgetTopScorer.cid = country.cid)";
rs = sql.executeQuery(sqlText);
if (!rs.isBeforeFirst()) {
return "";
} else {
rs.next();
// Query7 (String name, String coach, integer budget)
name = rs.getString("name");
coach = rs.getString("coach");
budget = rs.getInt("budget");
output = name + ":" + coach + ":" + budget;
while (rs.next()) {
name = rs.getString("name");
coach = rs.getString("coach");
budget = rs.getInt("budget");
output += "#" + name + ":" + coach + ":" + budget;
}
return output;
}
} catch (SQLException e) {
return "";
}
}
/**
* Retrieves all of the Discussion Threads associated with a Group Id
*
* @param groupId The Group Id to get Threads for
* @return A List of Discussion Threads that belong to the Group
*/
public List<DiscussionThread> getThreads(int groupId) {
ArrayList<DiscussionThread> threads = new ArrayList<>();
try {
// Create a prepared statement
PreparedStatement pstmt =
conn.prepareStatement("SELECT * FROM DiscussionThreads WHERE GroupId = ?");
// Set the required parameters and execute
pstmt.setInt(1, groupId);
ResultSet rs = pstmt.executeQuery();
// Get the results and add to the list
if (rs.isBeforeFirst()) {
while (!rs.isAfterLast()) {
DiscussionThread thread = DiscussionThread.fromResultSet(rs);
if (thread != null) {
threads.add(thread);
}
}
}
} catch (Exception e) {
logger.log(Level.SEVERE, "SQL Error", e);
}
return threads;
}
/**
* Retrieves a List of Discussion Posts for a given Thread Id
*
* @param threadId The Id of the Thread to query
* @return A List of Discussion Posts for the Thread
*/
public List<DiscussionPost> getPosts(int threadId) {
ArrayList<DiscussionPost> posts = new ArrayList<>();
try {
// Create a prepared statement
PreparedStatement pstmt =
conn.prepareStatement("SELECT * FROM DiscussionPosts WHERE ThreadId = ?");
// Set the required parameters adn execute
pstmt.setInt(1, threadId);
ResultSet rs = pstmt.executeQuery();
// Retrieve the results and add to the list
if (rs.isBeforeFirst()) {
while (!rs.isAfterLast()) {
DiscussionPost post = DiscussionPost.fromResultSet(rs);
if (post != null) posts.add(post);
}
}
} catch (Exception e) {
logger.log(Level.SEVERE, "SQL Error", e);
}
return posts;
}
public static boolean isMyResultSetEmpty(ResultSet rs) throws SQLException {
if (!rs.isBeforeFirst() && rs.getRow() == 0) {
return true;
} else return false;
}
public long getOID(Connection conn) throws SQLException {
String sql = "SELECT last_insert_id()";
ResultSet rs = executeQuery(sql, conn);
if (rs.isBeforeFirst()) rs.next();
long oid = rs.getLong(1);
rs.close();
return oid;
}
public String listPlayers(String fcname) {
String sqlText;
try {
sql = connection.createStatement();
sqlText = "SELECT DISTINCT fcid FROM club WHERE name = '" + fcname + "'";
rs = sql.executeQuery(sqlText);
String fname, lname, position, cname, output;
int goals;
if (!rs.isBeforeFirst()) {
return "";
} else {
rs.next();
sqlText =
"SELECT * FROM player JOIN country ON player.cid = country.cid WHERE fcid = "
+ rs.getInt("fcid");
rs = sql.executeQuery(sqlText);
rs.next();
fname = rs.getString("fname");
lname = rs.getString("lname");
position = rs.getString("position");
goals = rs.getInt("goals");
cname = rs.getString("name");
output = fname + ":" + lname + ":" + position + ":" + goals + ":" + cname;
while (rs.next()) {
fname = rs.getString("fname");
lname = rs.getString("lname");
position = rs.getString("position");
goals = rs.getInt("goals");
cname = rs.getString("name");
output += "#" + fname + ":" + lname + ":" + position + ":" + goals + ":" + cname;
}
return output;
}
} catch (SQLException e) {
return "";
}
}
public boolean updateValues(String cname, int incrV) {
String sqlText;
try {
sql = connection.createStatement();
sqlText = "SELECT DISTINCT cid FROM country WHERE name = '" + cname + "'";
rs = sql.executeQuery(sqlText);
if (!rs.isBeforeFirst()) {
return false;
}
rs.next();
sqlText = "UPDATE player SET value = value + " + incrV + " WHERE cid = " + rs.getInt("cid");
sql.executeUpdate(sqlText);
if (sql.getUpdateCount() > 0) {
return true;
} else {
return false;
}
} catch (SQLException e) {
return false;
}
}
public List<Customer> findCustomers(String id, String match, int limit) {
Connection connection = null;
CallableStatement callableStatement = null;
ResultSet resultSet = null;
boolean hasResults;
List<Customer> customers = new ArrayList<Customer>();
try {
connection = dataSource.getConnection(); // get connection from dataSource
connection.setTransactionIsolation(
Connection.TRANSACTION_READ_COMMITTED); // prevent dirty reads
callableStatement = connection.prepareCall(listCustomersSql); // prepare callable statement
if (id == null) {
callableStatement.setNull(1, Types.INTEGER);
} else {
callableStatement.setInt(1, Integer.parseInt(id));
}
if (match == null) {
callableStatement.setNull(2, Types.VARCHAR);
} else {
callableStatement.setString(2, match);
}
callableStatement.setInt(3, limit);
callableStatement.setNull(4, Types.INTEGER);
hasResults = callableStatement.execute();
if (hasResults) {
resultSet = callableStatement.getResultSet();
if (resultSet.isBeforeFirst()) { // customers have been returned
while (resultSet.next()) {
customers.add(
new Customer(
resultSet.getString("id"),
resultSet.getString("first_name"),
resultSet.getString("last_name"),
resultSet.getString("street_address"),
resultSet.getString("apt_address"),
resultSet.getString("city"),
resultSet.getString("state"),
resultSet.getString("zip"),
resultSet.getString("phone"),
resultSet.getString("email"),
resultSet.getString("notes")));
}
} else {
log.debug("No customers returned.");
}
} else {
log.debug("No customers returned.");
}
} catch (SQLException se) {
log.error("SQL error: ", se);
return null;
} finally {
try {
resultSet.close();
} catch (Exception se) {
log.error("Unable to close resultSet: ", se);
}
try {
callableStatement.close();
} catch (Exception se) {
log.error("Unable to close callableStatement: ", se);
}
try {
connection.close();
} catch (Exception se) {
log.error("Unable to close connection: ", se);
}
}
return customers;
}
