Hello, and I’m new to JDBC and SQL. My question is how do you access database twice for a given SQL query? I’m working on a small project which stores data in H2 DB and retrieve it from different ways. There are 2 buttons and if you click 1 it displays the data in JTable with the query. It shows the data from DB when I click a button to call any 1 of this first then the 2nd method won’t.
My problem is these 2 methods throw JDBC.Exception: No data available [2000-193] if I call 1 method first then the another
"SELECT * FROM table_name WHERE expirationDate > now() " and the other 1 displays expiredDate data in JTable using the query
"SELECT * FROM table_name WHERE expirationDate < now()" Here are my methods
[code]
public void readActiveData() throws IOException, InstantiationException, IllegalAccessException, SQLException, ClassNotFoundException {
try {
Class.forName("org.h2.Driver");
conn = DriverManager.getConnection(URL,USER,PASSWORD);
PreparedStatement pst = null;
ResultSet rs;
Statement st = conn.createStatement();
try {
rs = st.executeQuery("SELECT * FROM data WHERE expirationDate > NOW();");
rs.beforeFirst();
while(rs.next()) {
int id = rs.getInt(1);
String fullName = rs.getString(2);
String regNum = rs.getString(3);
String note = rs.getString(4);
java.sql.Date currentDate = rs.getDate(5);
int totalPay = rs.getInt(6);
expirationDate = rs.getDate(7);
rows++; }
regData = new Object[rows][7];
rs = st.executeQuery("SELECT * FROM data WHERE expirationDate > NOW();");
for (int i1 = 0; i1 < rows; i1++)
{ rs.next();
for(int j1 = 0; j1 < 7; j1++) {
regData[i1][j1]=rs.getString(j1+1); }
}
model = new DefaultTableModel(regData, colHeads);
table.setModel(model);
JScrollPane jsp = new JScrollPane(table,v,h);
activeDataPanel.add(jsp);
rs.close();
st.close();
conn.close(); }
catch(Exception e) {
JOptionPane.showMessageDialog(null,"Error: " + e); }
}
catch (ClassNotFoundException | SQLException ex){
JOptionPane.showMessageDialog(null,"Error: " + ex);
}
}[/code]
and the other method is pretty much similar but just the SQL queries are different
[code]
public void readExpiredData() throws IOException, InstantiationException, IllegalAccessException, SQLException, ClassNotFoundException {
try {
Class.forName("org.h2.Driver");
conn = DriverManager.getConnection(URL,USER,PASSWORD);
PreparedStatement pst = null;
ResultSet rs;
Statement st = conn.createStatement();
try {
rs = st.executeQuery("SELECT * FROM data WHERE expirationDate > NOW();");
rs.beforeFirst();
while(rs.next()) {
int id = rs.getInt(1);
String fullName = rs.getString(2);
String regNum = rs.getString(3);
String note = rs.getString(4);
java.sql.Date current = rs.getDate(5);
int totalPay = rs.getInt(6);
expirationDate = rs.getDate(7);
rows++; }
regData = new Object[rows][7];
rs = st.executeQuery("SELECT * FROM data WHERE expirationDate < NOW();");
for (int i1 = 0; i1 < rows; i1++)
{ rs.next();
for(int j1 = 0; j1 < 7; j1++) {
regData[i1][j1]=rs.getString(j1+1); }
}
model = new DefaultTableModel(regData, colHeads);
table.setModel(model);
JScrollPane jsp = new JScrollPane(table,v,h);
activeDataPanel.add(jsp);
rs.close();
st.close();
conn.close(); }
catch(Exception e) {
JOptionPane.showMessageDialog(null,"Error: " + e); }
}
catch (ClassNotFoundException | SQLException ex){
JOptionPane.showMessageDialog(null,"Error: " + ex);
}
}[/code]
Any suggestions how i can let this work?
