Populate data according to column from JDBC to JTable

[mocha song]

I'm an extreme newbie in JDBC. Thing is, I have a mysql database (server side online) and I'm trying to populate the data from there to a jtable (on the client side). Sorry for the messy codes

Server code:

		String basicsql = "SELECT * FROM Table";
		PreparedStatement pstmt;
		pstmt = conn.prepareStatement(basicsql);
		ResultSet result = pstmt.executeQuery();
		ResultSetMetaData metaData = result.getMetaData();

		columnCount = metaData.getColumnCount();
		out.println(Integer.toString(columnCount));
	
		
		for(int e=1 ; e<=columnCount; e++){
			out.println(metaData.getColumnName(e));
			
		}
		
		Map<String, String> map = new HashMap<String, String>();
		List<Map<String, String>> info = new ArrayList<Map<String, String>>();

	

		while (result.next()) {
			
			map.put("name", result.getString("Name"));
			map.put("type", result.getString("Type"));
			map.put("price", result.getString("Price"));

			  for(Map.Entry<String, String> entry: map.entrySet()){
				
					out.println(entry.getKey() + entry.getValue());
					
			    }
	info.add(map);
    }
		
		result.close();
		pstmt.close();
		conn.close();
}

Client side:

 public DefaultTableModel getTable(){
		DefaultTableModel model = null;
		
    	try  {...
    	
    	    model = new DefaultTableModel();
    	    int colCount = Integer.parseInt(in.readLine()); 
    	    String [] colName = new String[colCount];
    
    	    for(int m =0; m<=colCount; m++){
    	    	colName[m] = in.readLine();
    	    	model.setColumnIdentifiers(colName);
    	    	
    	    	
    	    	
    	    }
    	    catch {...}

return model;

*** On the client side, im using a BufferedReader to read whats given in the server side.
So far, I've managed to get the column names of the table over to my client side. However, my problem for now would be how to get the data which is being passed from the server side into the client side? Of course, the data has to match with the columns (name,type,price)
note: client side is being passed as a DefaultTableModel and it'll then be passed into a JTable in the GUI file.

*** On the server side, I managed to get this printed:
name : XXX
type : XXX
price : 0.00

but i'm unsure how to pass it into the client side as a JTable data. I'll still have to implement a couple of functions like add data from table (client side), and it updates the SQL database (server side).

Kindly advice me on this! Much help is appreciated!

[Brian Tkatch]

mocha song wrote:

String basicsql = "SELECT * FROM Table";

I am no expert in jdbc, but i would like to comment on the SQL itself. IMO, SELECT * should only be used in EXISTS() and ad hoc queries. (In EXISTS() the SELECT * FROM is redundant, and in ad hoc queries, not much matters.) Instead, spell out the column names. This is self-documenting, and usually helps to protect the code from column changes, such as additions, subtractions, and reorderings. This latter point may not be relevant in your code, as you seek to use the metadata to get the column names and data types. Though, i do believe this is not common, and certainly adds complexity to the code.

If you are having issues, might i suggest that you go for a simpler version, at least at first. That is, spell out the three columns you want, comment out the metadata code, and just hardcode the retrieval of those three. With a more simple approach, it might be easier to identify any issues that arise.

[mocha song]

Brian Tkatch wrote:

mocha song wrote:

String basicsql = "SELECT * FROM Table";

I am no expert in jdbc, but i would like to comment on the SQL itself. IMO, SELECT * should only be used in EXISTS() and ad hoc queries. (In EXISTS() the SELECT * FROM is redundant, and in ad hoc queries, not much matters.) Instead, spell out the column names. This is self-documenting, and usually helps to protect the code from column changes, such as additions, subtractions, and reorderings. This latter point may not be relevant in your code, as you seek to use the metadata to get the column names and data types. Though, i do believe this is not common, and certainly adds complexity to the code.

If you are having issues, might i suggest that you go for a simpler version, at least at first. That is, spell out the three columns you want, comment out the metadata code, and just hardcode the retrieval of those three. With a more simple approach, it might be easier to identify any issues that arise.

alright thanks! I'll try that out anyway. But if it's a usual JTable you're populating, do you have any idea how do you populate it according to the column name?

[Paul Clapham]

How to cause the table model to contain the data from that query?

Seems to me you would have to read rows from the query and, for each query row, add the data in it to a new row in the table model.

[mocha song]

Paul Clapham wrote:How to cause the table model to contain the data from that query?

Seems to me you would have to read rows from the query and, for each query row, add the data in it to a new row in the table model.

How do you do that?
As of now, I've managed to get the data from SQL db and stored it as a HashMap<String,Object>.
example: {12.50,type2,Y},{12.00, type1,Z}...etc

map.put("name", result.getString("Name"));
map.put("type", result.getString("Type"));
map.put("price", result.getString("Price"));

my problem now is that I initially called it by - name, type, price, but what I'm getting is price, type, name.. how do I populate it according to the columnName in JTable? I'll also have to split it according to the curly braces {} , as each curly brace is a different entry...

Thanks in advance!!

[Paul Clapham]

mocha song wrote:

Paul Clapham wrote:How to cause the table model to contain the data from that query?

Seems to me you would have to read rows from the query and, for each query row, add the data in it to a new row in the table model.

How do you do that?

Consider using the table model's addRow() method. You can pass an array of Objects or a Vector to that method; in either case you would get the data from a row of your ResultSet.

As of now, I've managed to get the data from SQL db and stored it as a HashMap<String,Object>.
example: {12.50,type2,Y},{12.00, type1,Z}...etc

Yes, I noticed that. But maps aren't going to be of any use here. I assumed you had that code left over from some previous application, so I didn't comment on it, but I don't see any use in creating Map objects if you really need table rows.

[mocha song]

Paul Clapham wrote:

mocha song wrote:

Paul Clapham wrote:How to cause the table model to contain the data from that query?

Seems to me you would have to read rows from the query and, for each query row, add the data in it to a new row in the table model.

How do you do that?

Consider using the table model's addRow() method. You can pass an array of Objects or a Vector to that method; in either case you would get the data from a row of your ResultSet.

As of now, I've managed to get the data from SQL db and stored it as a HashMap<String,Object>.
example: {12.50,type2,Y},{12.00, type1,Z}...etc

Yes, I noticed that. But maps aren't going to be of any use here. I assumed you had that code left over from some previous application, so I didn't comment on it, but I don't see any use in creating Map objects if you really need table rows.

Hi Paul! I've completely changed the parsing from a map into Vector.
now I have such values:

column : [Name, Type,Price]
data: [[A, Type1, 0.00],[b, Type2, 9.08]]

Can you please advice me on how do you about the looping to match the data to each column? Thanks again!

edit: On my client side, I tried to populate it using a DefaultTableModel

model.setDataVector(data, column);
but I can't seem to change my String as a Vector object. I tried using that Arrays.asList(string) method, but to no avail. I'm really horrible at looping

edit2: I'm thinking if the problem is the part where I'm trying to pass a Vector object to the BufferedReader and it gets accepted as a String at the Client side. Please advice me on this :/

Console:
eg1: Column[Name,Type, Price]
eg2: ColumnStr[Ljava.lang.String;@1a7c9625

eg1 : are the columns being passed by the JDBC.
eg2 : I added this to the column printed to test if it was easier to pass it as an arraylist into the bufferedReader

String [] columnStr = column.toArray(new String[column.size()]);	
		out.println(columnStr);

[Paul Clapham]

mocha song wrote:Hi Paul! I've completely changed the parsing from a map into Vector.
now I have such values:

column : [Name, Type,Price]
data: [[A, Type1, 0.00],[b, Type2, 9.08]]

Can you please advice me on how do you about the looping to match the data to each column? Thanks again!

What looping? What you have there in "data" appears to be a Vector of Vectors and as such is exactly what you want to pass to the constructor of your DefaultTableModel. And I don't understand what you mean by matching the data to each column -- that's the job of the table model.

Of course you've just shown some text representation so maybe you don't have a Vector of Vectors. So how about if you show us the code which goes through the ResultSet and produces that?

[mocha song]

Paul Clapham wrote:
Of course you've just shown some text representation so maybe you don't have a Vector of Vectors. So how about if you show us the code which goes through the ResultSet and produces that?

{
		
		String sql = "SELECT * FROM Table”;
		PreparedStatement ps;
		ps = conn.prepareStatement(sql);
		ResultSet rs = ps.executeQuery();
		ResultSetMetaData md = rs.getMetaData();

		int colCount = md.getColumnCount();
		Vector<String> col = new Vector(colCount);

		Vector<Vector<Object>> data = new Vector<Vector<Object>>();
		Vector<Object> row;

		for (int i = 1; i <= colCount; i++) {
			col.add(md.getColumnName(i));

			while (rs.next()) {
				row = new Vector<Object>();
				for (int k = 1; k <= columnCount; k++) {
					row.add(rs.getString(k));
				}

				data.add(row);
			}
		}

		//this is read by the buffered reader after in another file
		out.println(col); 
		out.println(data);
		
		rs.close();
		ps.close();
		conn.close();
		}

Kindly please advise me on how am I passing this from DefaultTableModel to a JTable. :/ Thanks Paul!

[Paul Clapham]

Yup, that's a pretty simple piece of code and it looks like it should do what you want, as far as I can tell.

mocha song wrote:Kindly please advise me on how am I passing this from DefaultTableModel to a JTable. :/ Thanks Paul!

Well, there's a JTable constructor which takes a TableModel as a parameter. You could use that. Or JTable has a setModel() method which also takes a TableModel as a parameter, and you could use that too. The latter might be better, because if you want to update the JTable with new data which might be in the database, the easiest way is to just generate a new table model (using that code which you posted) and call your table's setModel() method with it.

[mocha song]

Paul Clapham wrote:

Well, there's a JTable constructor which takes a TableModel as a parameter. You could use that. Or JTable has a setModel() method which also takes a TableModel as a parameter, and you could use that too. The latter might be better, because if you want to update the JTable with new data which might be in the database, the easiest way is to just generate a new table model (using that code which you posted) and call your table's setModel() method with it.

My issue here is probably that I'm passing it through a buffered Reader before setting it into a DefaultTableModel.. so when i test it using sytem.out.println it goes:
column : [Name, Type,Price]
data: [[A, Type1, 0.00],[b, Type2, 9.08]]

rather than it being set directly into a JTable (like an Microsoft excel table spreadsheet format). In order to send it back to the client, my programme reads it using a buffered reader which is what makes things complicated :/ Kindly advise me on this! thanks again Paul!

[Paul Clapham]

mocha song wrote:My issue here is probably that I'm passing it through a buffered Reader before setting it into a DefaultTableModel

Well, if you're doing that then surely you're going to have an issue. So stop doing that. You already have something from which you can create a DefaultTableModel directly.

[Paul Clapham]

And I notice you don't seem to have a JDBC problem. Your queries and your extraction code are perfectly fine. You just don't know how to get the stuff into a JTable, right? So let's move this thread to the Swing forum.

[mocha song]

Paul Clapham wrote:

Well, if you're doing that then surely you're going to have an issue. So stop doing that. You already have something from which you can create a DefaultTableModel directly.

The structure of my programme is that I'll have to pass it back using a reader.. (it affects others functions- which pulls data from JDBC into the client side GUI)
So would you recommend me setting it into a DefaultTableModel before passing it into a reader?

Paul Clapham wrote:
And I notice you don't seem to have a JDBC problem. Your queries and your extraction code are perfectly fine. You just don't know how to get the stuff into a JTable, right? So let's move this thread to the Swing forum.

yes sure! thanks Paul!

[Paul Clapham]

mocha song wrote:The structure of my programme is that I'll have to pass it back using a reader.. (it affects others functions- which pulls data from JDBC into the client side GUI)
So would you recommend me setting it into a DefaultTableModel before passing it into a reader?

No. I would recommend getting rid of that requirement for this part of your application. You have gone to a lot of trouble to extract the data from the database and put it into a structured object which is needed elsewhere. Smashing all of that structure and sending ordinary text makes absolutely no sense. Really. And anyway this part of your application is only going to be producing a JTable. If you have other parts of the application which need to get data from a Reader (which still seems completely wrong to me) then let them continue to do that.

Now if you have a boss who says "Reader or you're fired" then we might want to discuss how to satisfy the boss's design. Otherwise, as you see, I recommend against that.

[mocha song]

Paul Clapham wrote:

mocha song wrote:The structure of my programme is that I'll have to pass it back using a reader.. (it affects others functions- which pulls data from JDBC into the client side GUI)
So would you recommend me setting it into a DefaultTableModel before passing it into a reader?

No. I would recommend getting rid of that requirement for this part of your application. You have gone to a lot of trouble to extract the data from the database and put it into a structured object which is needed elsewhere. Smashing all of that structure and sending ordinary text makes absolutely no sense. Really. And anyway this part of your application is only going to be producing a JTable. If you have other parts of the application which need to get data from a Reader (which still seems completely wrong to me) then let them continue to do that.

Now if you have a boss who says "Reader or you're fired" then we might want to discuss how to satisfy the boss's design. Otherwise, as you see, I recommend against that.

Unfortunately it's a group project and I'll have to go with the flow.. So yes I'll have to go on with the usage of passing the Vector objects into a reader and create a defaultTableModel out of it then only set the tableModel to a Jtable on the GUI itself

[Campbell Ritchie]

mocha song wrote:. . . Unfortunately it's a group project and I'll have to go with the flow.. . . .

Unfortunately I read that as, “It everybody else in the group gets it wrong, I'll get it wrong too.” You will have to convice the rest of the error of their ways.

[Paul Clapham]

Okay, fine. So don't create a DefaultTableModel in that method. Design a suitable XML document to contain a text version of the data from your database query and produce a String containing that XML. Then return a StringReader based on that String. You could even wrap that in a BufferedReader if that's a requirement.

Second step: read the XML document from the StringReader, parse it, and produce a DefaultTableModel using something like the code you already have.

You may think this is a ridiculous amount of extra work to do. I agree completely.

[mocha song]

Paul Clapham wrote:Okay, fine. So don't create a DefaultTableModel in that method. Design a suitable XML document to contain a text version of the data from your database query and produce a String containing that XML. Then return a StringReader based on that String. You could even wrap that in a BufferedReader if that's a requirement.

Second step: read the XML document from the StringReader, parse it, and produce a DefaultTableModel using something like the code you already have.

You may think this is a ridiculous amount of extra work to do. I agree completely.

Sorry my bad.. Paul & Campbell, I've double checked and its a PrintWriter instead of a BufferedReader.

 out.println(col); 
        out.println(data);

note: "out" here is a printWriter
I sincerely apologise for all the confusion I've caused.