Displaying SELECT results into swing interface

[Taylor Manley]

I am implementing a search interface in eclipse working with mysql. I believe I have the SELECT statement right, but I am unsure of how to display the results of the search on my interface. I am working on the search for the 'city search' currently and haven't finished the select statement for the 'tag search' yet as I don't want to get ahead of myself. Is 'result' used to display the selected values from my database table or something else?

I originally had a JList instead of a JTable to try and display the search results in but switched it. It wasn't working right than either.

package project_files;

import java.awt.EventQueue;

import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JButton;
import javax.swing.JList;
import javax.swing.JOptionPane;

import java.awt.event.ActionListener;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.awt.event.ActionEvent;
import javax.swing.JTextField;
import javax.swing.table.DefaultTableModel;
import javax.swing.JTable;

public class VIdeo_search_test {

	protected static final JLabel listCity = null;
	private JFrame frame;
	private JTextField txtCity;
	private JTable table;

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

	/**
	 * Create the application.
	 */
	public VIdeo_search_test() {
		initialize();
	}

	/**
	 * Initialize the contents of the frame.
	 */
	private void initialize() {
		frame = new JFrame();
		frame.setBounds(200, 200, 450, 400);
		frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
		frame.getContentPane().setLayout(null);
		
		JLabel lblVideoSearch = new JLabel("Video Search");
		lblVideoSearch.setBounds(179, 17, 89, 15);
		frame.getContentPane().add(lblVideoSearch);
		
		JButton btnSearchCity = new JButton("Search City");
		btnSearchCity.setBounds(44, 104, 114, 25);
		btnSearchCity.addActionListener(new ActionListener() {
			public void actionPerformed(ActionEvent arg0) {
				
			
				DefaultTableModel model = new DefaultTableModel(new String[]{"video name", "video description", "video_city"}, 0);
				model=(DefaultTableModel) table.getModel();

try {
					
					Class.forName("com.mysql.cj.jdbc.Driver");
					Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/userdatabase", "root", "pass1234");
					
					String sql = ("SELECT video_name, video_description, video_city, video_subject, video_tags FROM video WHERE video_city= (?)");
					String city =txtCity.getText();
					
				
				
					/* etc.. */

					PreparedStatement prep = con.prepareStatement(sql);
					prep.setString(1,city);
				
					
					ResultSet rs = prep.executeQuery(sql);
			     
			
							while(((ResultSet) prep).next())
							{
							    String d = rs.getString("video name");
							    String e = rs.getString("video description");
							    String f = rs.getString("video city");
							    model.addRow(new Object[]{d, e, f});
							}
							table.setModel(model);
							
		                       JOptionPane.showMessageDialog(null, "Search SUCCESSFULL");
		                      
							con.close();
				} catch(Exception e) {System.out.print (e);}
				
			}
		});
		frame.getContentPane().add(btnSearchCity);
		
		JButton btnSearchTag = new JButton("Search Tag");
		btnSearchTag.setBounds(255, 44, 114, 25);
		frame.getContentPane().add(btnSearchTag);
		
		JList listTag = new JList();
		listTag.setBounds(255, 104, 129, 207);
		frame.getContentPane().add(listTag);
		
		txtCity = new JTextField();
		txtCity.setBounds(58, 46, 86, 20);
		frame.getContentPane().add(txtCity);
		txtCity.setColumns(10);
		
		table = new JTable();
		table.setBounds(58, 171, 100, 100);
		frame.getContentPane().add(table);
	}
}

[Paul Clapham]

That all looked pretty good to me the first, and second, time I looked through it. You've got a JTable, you fill a TableModel with data from the ResultSet, you make that be the JTable's model -- that's all right and should work.

Then the third time I scanned the code:

DefaultTableModel model = new DefaultTableModel(new String[]{"video name", "video description", "video_city"}, 0);

Yup, that looks more or less right. There could be some details you got wrong there which I didn't recognize but then I don't write this code every day.

model=(DefaultTableModel) table.getModel();

And then you throw that table model away and replace it with another one? Don't do that.

By the way it's clear that you weren't happy about the output of your code. It would help in future if you describe what you saw and how it differs from what you expected.

[Taylor Manley]

I want the output to be displayed in the Jtable, but I don't get any output at all. So the lack of output comes from creating a different .getModel?

[Paul Clapham]

Not necessarily. It might be that your query doesn't return any rows. But I'd fix the known error first.

I'm assuming that you see an empty JTable in your GUI? You haven't said what you do see yet.

[Taylor Manley]

So when I run the program, my gui pops up and the JTable is empty. If I enter something for the "?" parameter and hit the "search city" button, I get no response from the program. I do have at least row that wold fill the parameter, but I get no return output into the JTable and it remains empty.

[Taylor Manley]

I believe I got it to work. I'm now receiving an output in the Jtable. Thank you for all your help.

One more question, is there a way to have the SELECT statement search for semantics or partial words or does it only search for verbatim results?

My current code for the database part is

                DefaultTableModel model = new DefaultTableModel(new String[]{"video name", "video description", "video_city", "video_subject", "video_tags"}, 0);
              
 
try {
                     
                    Class.forName("com.mysql.cj.jdbc.Driver");
                    Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/userdatabase", "root", "pass1234");
                     
                    Statement stmt= con.createStatement();
					String sql = "Select * from video where video_city = '" +txtCity.getText()+"'";
					ResultSet rs=stmt.executeQuery(sql);
					if(rs.next())
					{
						 String d = rs.getString("video_name");
                         String e = rs.getString("video_description");
                         String f = rs.getString("video_city");
                         String g = rs.getString("video_subject");
                         String h = rs.getString("video_tags");
                         model.addRow(new Object[]{d, e, f, g, h});
                            table.setModel(model);
                             
                               JOptionPane.showMessageDialog(null, "Search SUCCESSFULL");
                               
                               con.close();}
					} catch(Exception e) {System.out.print (e);}

[Paul Clapham]

Good work! Glad to hear it's working.

Taylor Manley wrote:One more question, is there a way to have the SELECT statement search for semantics or partial words or does it only search for verbatim results?

Yes, I'm sure you can do that. I think you'd use the LIKE operator rather than the = operator.