public void generateEnumeration() {
StyledDocument docExam = txtpaneExamGeneration.getStyledDocument();
StyledDocument docAnsKey = txtpaneAnswerKey.getStyledDocument();
Connection con = null;
Statement stmt = null;
try {
Class.forName("org.sqlite.JDBC");
con = DriverManager.getConnection("jdbc:sqlite:sql_items.sqlite");
stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("SELECT DISTINCT group_name FROM active_items;");
int num = numIndex;
char[] letterArray = "ABCDEFGHIJKLMNOPQRSTUVWXYZ".toCharArray();
int letterIndex = 0;
docExam.insertString(docExam.getLength(), "Enumeration - Enumerate the following:\n\n" , null);
while(rs.next()){
String groupNameHandler = rs.getString("group_name");
docExam.insertString(docExam.getLength(), letterArray[letterIndex] + ". " + groupNameHandler + ".\n", null);
ResultSet rs2 = stmt.executeQuery("SELECT * FROM active_items WHERE group_name = '" + groupNameHandler + "'");
while(rs2.next()) {
String itemNumbering = Integer.toString(num);
String ansHandler = rs.getString("item_name");
docAnsKey.insertString(docAnsKey.getLength(), itemNumbering + ". ", null);
docAnsKey.insertString(docAnsKey.getLength(), ansHandler + "\n", null);
num ++; // this is needed to increase the numbering while in the WHILE LOOP
numIndex ++; // this is needed to save the last number used after the WHILE LOOP so that it will be continued on the next function
} // end for ResultSet 2
letterIndex ++;
} // end ResultSet 1
letterIndex = 0;
docExam.insertString(docExam.getLength(), "\n\n" , null);
} catch (ClassNotFoundException | SQLException e) {
System.out.println(e);
} catch (BadLocationException ex) {
Logger.getLogger(generateExam.class.getName()).log(Level.SEVERE, null, ex);
} finally {
try {
con.close();
stmt.close();
} catch (SQLException ex) {
Logger.getLogger(generateExam.class.getName()).log(Level.SEVERE, null, ex);
}
}
}
the purpose of this code is to generate an Enumeration-Type Quiz. This will output into 2 JTextPanes, one for the docExam and one for the docAnsKey
ResultSet rs = stmt.executeQuery("SELECT DISTINCT group_name FROM active_items;"); should (and it does, becase I tested on SQLite and it works) retrieve 3 Rows from the database. However, the output goes like this:
docExam:
Enumeration - Enumerate the following
A. Enum question 1
and
in docAnsKey:
1. CorrectAns1
2. CorrectAns2
when in reality, the database have 12 Items in it.
It seems like while(rs.next()){ is only executed once, that's why the rest of the code is skipped. And I can't find where is my error here. Can somebody please point it out to me?
Content of the Database:
item_id --- item_name --- group_name
1 CorrectAns1 Enum question 1
2 CorrectAns2 Enum question 1
3 CorrectAns3 Enum question 2
4 CorrectAns4 Enum question 2
5 CorrectAns5 Enum question 2
6 CorrectAns6 Enum question 2
7 CorrectAns7 Enum question 2
8 CorrectAns8 Enum question 2
9 CorrectAns9 Enum question 2
10 CorrectAns10 Enum question 2
11 CorrectAns11 Enum question 3
12 CorrectAns12 Enum question 3
After several hours trials and errors, I conclude that ResultSet cannot handle multiple 'uses'. So when I used it for rs2.next(), it forgot the data from rs.next(). So my workaround is to put the entire 2nd portion of the function out from the while loop, since the ordering is similar to when I call SELECT DISTINCT group_name FROM active_items; and SELECT * FROM active_items and would not affect the integrity of the program.
final code here:
public void generateEnumeration() {
StyledDocument docExam = txtpaneExamGeneration.getStyledDocument();
StyledDocument docAnsKey = txtpaneAnswerKey.getStyledDocument();
Connection con = null;
Statement stmt = null;
try {
Class.forName("org.sqlite.JDBC");
con = DriverManager.getConnection("jdbc:sqlite:sql_items.sqlite");
stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("SELECT DISTINCT group_name FROM active_items;");
int num = numIndex;
char[] letterArray = "ABCDEFGHIJKLMNOPQRSTUVWXYZ".toCharArray();
int letterIndex = 0;
docExam.insertString(docExam.getLength(), "Enumeration - Enumerate the following:\n\n" , null);
while(rs.next()){
String groupNameHandler = rs.getString("group_name");
docExam.insertString(docExam.getLength(), letterArray[letterIndex] + ". " + groupNameHandler + ".\n", null);
letterIndex ++;
}
ResultSet rs2 = stmt.executeQuery("SELECT * FROM active_items");
while(rs2.next()) {
String itemNumbering = Integer.toString(num);
String ansHandler = rs.getString("item_name");
docAnsKey.insertString(docAnsKey.getLength(), itemNumbering + ". ", null);
docAnsKey.insertString(docAnsKey.getLength(), ansHandler + "\n", null);
num ++; // this is needed to increase the numbering while in the WHILE LOOP
numIndex ++; // this is needed to save the last number used after the WHILE LOOP so that it will be continued on the next function
}
letterIndex = 0;
docExam.insertString(docExam.getLength(), "\n\n" , null);
} catch (ClassNotFoundException | SQLException e) {
System.out.println(e);
} catch (BadLocationException ex) {
Logger.getLogger(generateExam.class.getName()).log(Level.SEVERE, null, ex);
} finally {
try {
con.close();
stmt.close();
} catch (SQLException ex) {
Logger.getLogger(generateExam.class.getName()).log(Level.SEVERE, null, ex);
}
}
}