No result shown

[Levian]

Hi,

Well, I have a problem here with some database
I think I've set the code correctly, but I see nothing though no error detected as well.
FYI, I've checked the db with sql query n it shown the table columns n rows.
Also if I have that System.println() on in initDB()...it'll show connected so basically the db is connected, but somehow there's no result shown...I think I miss some basic thing as still in learning time.

File is TableTest.java n QueryTableModel.java

Here's the code :

import java.awt.EventQueue;
import javax.swing.JFrame;
import javax.swing.JPanel;
import javax.swing.*;
import javax.swing.border.CompoundBorder;

public class TableTest extends JFrame {

	/**
	 * 
	 */
	private static final long serialVersionUID = 4644572723995616224L;
	private JPanel contentPane;
	private JTable MatTable;

	/**
	 * Launch the application.
	 */
	public static void main(String[] args) {
		EventQueue.invokeLater(new Runnable() {
			public void run() {
				try {
					TableTest frame = new TableTest();
					frame.setVisible(true);
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		});
	}

	/**
	 * Create the frame.
	 */
	public TableTest() {
		setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
		setBounds(100, 100, 450, 300);
		contentPane = new JPanel();
		contentPane.setBorder(new CompoundBorder());
		setContentPane(contentPane);
		contentPane.setLayout(null);
		JScrollPane scrollPane = new JScrollPane();
		scrollPane.setBounds(10, 11, 422, 128);
		contentPane.add(scrollPane);
		scrollPane.setViewportView(MatTable);
		QueryTableModel qtm = new QueryTableModel();
		MatTable = new JTable(qtm);
		qtm.initDB();
		qtm.setQuery("SELECT * from test.persona;");
		qtm.closeDB();;
	}
}

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.util.Vector;
import javax.swing.table.AbstractTableModel;

class QueryTableModel extends AbstractTableModel {

	private static final long serialVersionUID = 3029751307840360263L;
	int colCount;
	String[] headers;
	Vector<String[]> cache;
	Connection db;
	Statement qDB;
	
	public Object getValueAt(int row, int col) {
	    return cache.elementAt(row)[col];
	}
	
	public String getColumnName(int i){
		return headers[i];
	}
	
	public int getColumnCount(){
		return colCount;
	}
	
	public int getRowCount(){
		return cache.size();
	}
	
	public void initDB() {
		String url = "jdbc:mysql://localhost:3306/test";
		String username = "root";
		String password = "root";
        try {
        	Connection db = DriverManager.getConnection(url, username, password);
        	if (db != null){
	        	qDB = db.createStatement();
//Connection check	        
//	        	System.out.println("Connected");
//end connection check
        	}
	    } catch (Exception e) {
	      System.out.println("Could not initialize the database.");
	      e.printStackTrace();
	    }
	  }
	
	public void closeDB() {
	    try {
	      if (qDB != null) {
	        qDB.close();
	      }
	      if (db != null) {
	        db.close();
	      }
	    } catch (Exception e) {
	      System.out.println("Could not close the current connection.");
	      e.printStackTrace();
	    }
	  }

	public void setQuery(String q) {
	    cache = new Vector<String[]>();
	    try {
	      // Execute the query and store the result set and its metadata
	      ResultSet rs = qDB.executeQuery(q);
	      ResultSetMetaData meta = rs.getMetaData();
	      colCount = meta.getColumnCount();

	      // Now we must rebuild the headers array with the new column names
	      headers = new String[colCount];
	      for (int h = 1; h <= colCount; h++) {
	        headers[h - 1] = meta.getColumnName(h);
	      }

	      // and file the cache with the records from our query. This would
	      // not be
	      // practical if we were expecting a few million records in response
	      // to our
	      // query, but we aren't, so we can do this.
	      while (rs.next()) {
	        String[] record = new String[colCount];
	        for (int i = 0; i < colCount; i++) {
	          record[i] = rs.getString(i + 1);
	        }
	        cache.addElement(record);
	      }
	      fireTableChanged(null); // notify everyone that we have a new table.
	    } catch (Exception e) {
//	      cache = new Vector<String[]>(); // blank it out and keep going.
	      e.printStackTrace();
	    }
	  }
}

Thanks in advance.

[Tolls]

Please use code tags when posting code.
I can't tell where you're going wrong until you do, but I can spot:

Connection db = DriverManager.getConnection(url, username, password);

That there is creating a connection which goes out of scope when you exit the initDb method.

Why are you creating and holding onto the connection and statement when all you need it for is to populate that table model?
Why not just create and use them in the setQuery() method?

And please don't abbreviate words like that...it makes it hard to read.

[Levian]

Thanks for the fast reply, I just edit it with code wrapper...didn't realize on that before.

Yes, what I need is to populate the table, but I think first it'll need to initiate connection...
anyway I'd updated as you said to just put the init in the setQuery() n with that removing the initDB() from the TableTest.java as well.

	public void setQuery(String q) {
	    cache = new Vector<String[]>();
	    try {
	      Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "root");
//Connection check	        
	      System.out.println("Connected");
// Execute the query and store the result set and its metadata
	      if (con == null){
        	  System.out.println("Unable to initiate database connection");
          } else {
	      qDB = con.createStatement();
	      ResultSet rs = qDB.executeQuery(q);
	      ResultSetMetaData meta = rs.getMetaData();
	      colCount = meta.getColumnCount();

// Now we must rebuild the headers array with the new column names
	      headers = new String[colCount];
	      for (int h = 1; h <= colCount; h++) {
	        headers[h - 1] = meta.getColumnName(h);
	      }

// and file the cache with the records from our query. This would
// not be
// practical if we were expecting a few million records in response
// to our
// query, but we aren't, so we can do this.
	      while (rs.next()) {
	        String[] record = new String[colCount];
	        for (int i = 0; i < colCount; i++) {
	          record[i] = rs.getString(i + 1);
	        }
	        cache.addElement(record);
	      }
	      fireTableChanged(null); // notify everyone that we have a new table.
	        }
          } catch (Exception e) {
//	      cache = new Vector<String[]>(); // blank it out and keep going.
	      e.printStackTrace();
          }
	}

but what I get is :
Connected

in console tab shown in Eclipse.

I don't know which part of abbreviation that you mean though.

[Tolls]

	      [B]Connection con[/B] = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "root");
//Connection check	        
	      System.out.println("Connected");
// Execute the query and store the result set and its metadata
	      if ([B]db == null[/B]){

Your connection is called con, not db.
db is never created so is always null.

At the end of that code you'll also want to close the connection in a finally block.

I don't know which part of abbreviation that you mean though.

This sort of thing:

the setQuery() n with that

Txt speak makes things harder to understand.
There's no reason to abbreviate words like that.

[Levian]

Yes, I just figure out the db and con mismatched and edit the post thus the null problem is gone
and about that 'n', well...I'll take a note on that since it was kinda a habit of mine.

try-catch-finally...OK, I'll re-read of that part and see whether the problem persists or not.

[Tolls]

So are you still not seeing anything?

[Levian]

Yes, I'd re-read about try-catch-finally...what I get from it is that it's the same with throws SQLException and that "finally" bock is optional...overall basically try-catch-finally will give a better clue of what the Exception is.
*this is what I get so far, tell me if I'm wrong*

And still I don't get what "finally" got to do with populating the db into the JTable...besides as it's optional.
I might need to get deeper into this whole exception things...as I'll most likely find myself dealing with it every now and then.

Anyway, cause of that I try to break down to pieces and start from the very beginning of the stuffs (hoping that this will give a clue as to what happened with the code), and as of now I can get connection and queries done and showing result in console but somehow there's a problem with metadata.

Wonder if I should make a new thread for this, but I guess I'll just write it down here.

import java.sql.*;

public class QTest {
	/**
	 * @param args
	 */
//start of main class
	public static void main(String[] args) throws SQLException {
		// TODO Auto-generated method stub
		
//		String[] headers = new String[colCount];
//		int i;
		
		Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "root");
//start if-else1		
		if (con == null){
			System.out.println("Connection failed");	
		}
		else {
			System.out.println("Connected");
			Statement qDB = con.createStatement();
			ResultSet rsDB = qDB.executeQuery("SELECT name, address from test.persona");
			ResultSetMetaData metaDB = rsDB.getMetaData();
			int colCount = metaDB.getColumnCount();
			System.out.println("Total Entry : " + colCount);			
			while (rsDB.next()){
				String AnsName = rsDB.getString("name");
				String AnsAdd = rsDB.getString("address");
				System.out.println("Name is : " + AnsName);
				System.out.println("Address is : " + AnsAdd);
			}
		}
//end if-else1			
	}
//end of main class			
}

On Console :
Connected
Total Entry : 2
Name is : aaf
Address is : adss
Name is : sfa
Address is : sdsa
Name is : dff
Address is : sdsa

as seen the entry stated 2 (which is from my previous query result with WHERE), however after taking off the WHERE part the ResultSet itself shows 3, but what stated with :

			int colCount = metaDB.getColumnCount();
			System.out.println("Total Entry : " + colCount);

still gives me :
Total Entry : 2

[Levian]

Anyway with that I've been reading ResultSetMetaData in my API ref for some time

[Levian]

OK, mistake on reading and stuff...column and record...guess I'll just work on the next piece.

[Tolls]

Yes, I'd re-read about try-catch-finally...what I get from it is that it's the same with throws SQLException and that "finally" bock is optional...overall basically try-catch-finally will give a better clue of what the Exception is.
*this is what I get so far, tell me if I'm wrong*

And still I don't get what "finally" got to do with populating the db into the JTable...besides as it's optional.
I might need to get deeper into this whole exception things...as I'll most likely find myself dealing with it every now and then.

finally is not about getting the data displayed. In your current setup you are leaving that connection open to the databse. You need to call close() on the connection.
However, if you just call close at the end of the method then you could still end up with leftover databse connections. This is because if an exception occurs in your code then that close() will not be called.

This is where the finally block comes in. Java guarantees a finally block will be called when an exception is thrown in the associated try{} block. And inside that finally block you call close():

Connection con = null;
try {
    // Get connection
    // do other stuff
} finally {
    con.close();
}

They're all about ensuring you don't leak resources (in this case database connections).

Anyway, cause of that I try to break down to pieces and start from the very beginning of the stuffs (hoping that this will give a clue as to what happened with the code), and as of now I can get connection and queries done and showing result in console but somehow there's a problem with metadata.

Wonder if I should make a new thread for this, but I guess I'll just write it down here.

import java.sql.*;

public class QTest {
	/**
	 * @param args
	 */
//start of main class
	public static void main(String[] args) throws SQLException {
		// TODO Auto-generated method stub
		
//		String[] headers = new String[colCount];
//		int i;
		
		Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "root");
//start if-else1		
		if (con == null){
			System.out.println("Connection failed");	
		}
		else {
			System.out.println("Connected");
			Statement qDB = con.createStatement();
			ResultSet rsDB = qDB.executeQuery("SELECT name, address from test.persona");
			ResultSetMetaData metaDB = rsDB.getMetaData();
			int colCount = metaDB.getColumnCount();
			System.out.println("Total Entry : " + colCount);			
			while (rsDB.next()){
				String AnsName = rsDB.getString("name");
				String AnsAdd = rsDB.getString("address");
				System.out.println("Name is : " + AnsName);
				System.out.println("Address is : " + AnsAdd);
			}
		}
//end if-else1			
	}
//end of main class			
}

On Console :
Connected
Total Entry : 2
Name is : aaf
Address is : adss
Name is : sfa
Address is : sdsa
Name is : dff
Address is : sdsa

as seen the entry stated 2 (which is from my previous query result with WHERE), however after taking off the WHERE part the ResultSet itself shows 3, but what stated with :

			int colCount = metaDB.getColumnCount();
			System.out.println("Total Entry : " + colCount);

still gives me :
Total Entry : 2

If you query hasn't changed beyond the WHERE cluase then that count will remain the same. ResultSetMetaData describes the data coming back. It doesn't say how much data is coming back. In your case it is telling you that it is returning data for two columns...

[Levian]

I see, so that's how finally works...
As of the metadata, I figured out last time after a few running codes.

I'll hold this for a while and trying that finally block and maybe as well as getting a hang on the API itself.

Thanks for clearing up things Tolls.

[Levian]

I add the finally block to set the close connection, but as it gives me Unhandled exception type SQLException...so I add throw SQLException into it and so here's the full codes and the error generated :

import java.awt.EventQueue;

public class TableTest extends JFrame {

	/**
	 * 
	 */
	private static final long serialVersionUID = 4644572723995616224L;
	private JPanel contentPane;
	private JTable MatTable;

	/**
	 * Launch the application.
	 */
	public static void main(String[] args) {
		EventQueue.invokeLater(new Runnable() {
			public void run() {
				try {
					TableTest frame = new TableTest();
					frame.setVisible(true);
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		});
	}

	/**
	 * Create the frame.
	 * @throws SQLException 
	 */
	public TableTest() throws SQLException {
		setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
		setBounds(100, 100, 450, 300);
		contentPane = new JPanel();
		contentPane.setBorder(new CompoundBorder());
		setContentPane(contentPane);
		contentPane.setLayout(null);
		JScrollPane scrollPane = new JScrollPane();
		scrollPane.setBounds(10, 11, 422, 128);
		contentPane.add(scrollPane);
		scrollPane.setViewportView(MatTable);
		QueryTableModel qtm = new QueryTableModel();
		MatTable = new JTable(qtm);
		MatTable.setAutoResizeMode(JTable.AUTO_RESIZE_OFF);
		qtm.setQuery("SELECT * from test.persona;");
	}
}

import java.sql.Connection;

class QueryTableModel extends AbstractTableModel {
	
	private static final long serialVersionUID = 3029751307840360263L;
	int colCount;
	String[] headers;
	@SuppressWarnings("unchecked")
	Vector<String[]> cache;
	Connection con;
	Statement qDB;	

	public Object getValueAt(int row, int col) {
	    return cache.elementAt(row)[col];
	}
	
	public String getColumnName(int i){
		return headers[i];
	}
	
	public int getColumnCount(){
		return colCount;
	}
	
	public int getRowCount(){
		return cache.size();
	}
	
	public QueryTableModel() throws SQLException {
	    cache = new Vector<String[]>();
	    new com.mysql.jdbc.Driver();
	  }	  

	public void closeDB() {
	    try {
	      if (con != null) {
	        con.close();
	        System.out.println("Connection closed");
	      }
	      if (con != null) {
	        con.close();
	        System.out.println("Connection closed");
	      }
	    } catch (Exception e) {
	      System.out.println("Could not close the current connection.");
	      e.printStackTrace();
	    }
	  }

	public void setQuery(String q) throws SQLException {
	    cache = new Vector<String[]>();
	    try {
	      Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "root");
//Connection check	        
	      System.out.println("Connected");
// Execute the query and store the result set and its metadata
	      if (con == null){
        	  System.out.println("Unable to initiate database connection");
          } else {
	      qDB = con.createStatement();
	      ResultSet rs = qDB.executeQuery(q);
	      ResultSetMetaData meta = rs.getMetaData();
	      colCount = meta.getColumnCount();

// Now we must rebuild the headers array with the new column names
	      headers = new String[colCount];
	      for (int h = 1; h <= colCount; h++) {
	        headers[h - 1] = meta.getColumnName(h);
	      }

// and file the cache with the records from our query. This would
// not be
// practical if we were expecting a few million records in response
// to our
// query, but we aren't, so we can do this.
	      while (rs.next()) {
	        String[] record = new String[colCount];
	        for (int i = 0; i < colCount; i++) {
	          record[i] = rs.getString(i + 1);
	        }
	        cache.addElement(record);
	      }
	      fireTableChanged(null); // notify everyone that we have a new table.
	        }
          } catch (Exception e) {
	      cache = new Vector<String[]>(); // blank it out and keep going.
	      e.printStackTrace();
          } finally {
        	  con.close();
          }
	}
}

Error generated at console :
Connected
java.lang.NullPointerException
at QueryTableModel.setQuery(QueryTableModel.java:96)
at TableTest.<init>(TableTest.java:50)
at TableTest$1.run(TableTest.java:23)
at java.awt.event.InvocationEvent.dispatch(Unknown Source)
at java.awt.EventQueue.dispatchEvent(Unknown Source)
at java.awt.EventDispatchThread.pumpOneEventForFilter s(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForFilter(U nknown Source)
at java.awt.EventDispatchThread.pumpEventsForHierarch y(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.run(Unknown Source)

[Tolls]

And?
What line is throwing that exception?

I can guess at the con.close()...because you have an attribute called con and you've declared a local con in the method. The attribute con is always null and is the one you are attempting to call close() on.

[Levian]

Yes, it's the con.close() as that's the only added line.

I don't quite get what you said with :

The attribute con is always null and is the one you are attempting to call close() on.

[Levian]

This has taken for days so I asked in another forum as well I hope it's fine with that, here's the link to avoid doubles :
Question about populating JTable with a class extended from AbstractTableModel - Stack Overflow

time is indeed a problem with incapability of being 24/7 nowadays and my time is getting shorter, need to fix this fast, so if there's anyone who knows besides Tolls who's always replying sadly on time when I'm about to be offline, I appreciate for it.


Thanks in advance

[Tolls]

	Connection con;  // This is declared at the top of your class and is never set, so is always null.

	public void setQuery(String q) throws SQLException {
	    cache = new Vector<String[]>();
	    try {
	      Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "root"); // This is not the above con.  This is local to the try block.
...
          } finally {
        	  con.close();  // This is the class con, not the try block con which has gone out of scope.  This is null.
          }

[Levian]

I don't quite get it...
I added that finally block as you're saying it...it wasn't there in the first place

Without it I get "Connected" in console, I initially thought there's 2 way it could happens...the database connection and data query can't be done or JTable isn't showing the data.
As what I get in the other forum it maybe a JTable problem than a database problem. I'll do a little check on it to see whether data query can be done or not

[Levian]

		      qDB = con.createStatement();
		      ResultSet rs = qDB.executeQuery(q);
		      ResultSetMetaData meta = rs.getMetaData();
	//Query check
		      System.out.print(meta.getColumnLabel(1));

Checked, OK this might be posted in a wrong place.
As I add a line to print a column label, it works, so basically database connection established and query can be done

[Levian]

solved, thanks for the help Tolls