Display ResultSet of a SQL query in a JTable (Java)

Question

I'm working on a program with which I can search through a database and display the results.

I'm stuck at the displaying part at the moment. My SQL query already works and returns the results. I'm using a PreparedStatement to fill my ResultSet. However I don't know how I can return the data of the ResultSet to a JTable which I want to use to display the data. Can anyone explain to me in detail how to do this or if there is a better way instead of a JTable I'm not seeing?

I'm using the MVC model and the DAO pattern, but I'm still pretty new to programming. So far from researching it I found the best solution to be to make a custom table class, but from there on I don't know how to progress.

My custom table class:

import java.sql.ResultSet;
import java.sql.ResultSetMetaData;

import java.util.Vector;

import javax.swing.table.DefaultTableModel;
import javax.swing.table.TableModel;


public class ResultSetTable {
 public static TableModel resultSetToTableModel(ResultSet rs) {
    try {
        ResultSetMetaData metaData = rs.getMetaData();
        int numberOfColumns = metaData.getColumnCount();
        Vector columnNames = new Vector();

        //Spaltennamen
        for (int column = 0; column < numberOfColumns; column++) {
            columnNames.addElement(metaData.getColumnLabel(column + 1));
        }

        //Alle Zeilen
        Vector rows = new Vector();

        while (rs.next()) {
            Vector newRow = new Vector();

            for (int i = 1; i <= numberOfColumns; i++) {
                newRow.addElement(rs.getObject(i));
            }

            rows.addElement(newRow);
        }

        return new DefaultTableModel(rows, columnNames);
    } catch (Exception e) {
        e.printStackTrace();

        return null;
    }
}
}

And the relevant part of my View class:

import java.awt.FlowLayout;
import javax.swing.JButton;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JTable;
import javax.swing.JTextField;

import dbconnect.dao.impl.BTRDaoImpl;

public class View extends JFrame{
public View() {

    JTable table = new JTable(new ResultSetTable(BTRDaoImpl.resultset);

    this.setSize(600, 400);
    setResizable(false);
}

My BTRDaoImpl class with the sql query and resultset:

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import mvc.View;
import dao.BTRbDao;
import business.BTRBean;

public class BTRDaoImpl extends AbstractDao implements BTRDao {

private Connection dbConnection = null;
private PreparedStatement preparedStatement = null;

public void sqlquery() {
    try {

        String btrname = View.searchbbtrname.getText();
        String btrplz = View.searchbtrplz.getText();
        btrname = btrname.trim().toUpperCase();
        btrplz = btrplz.trim().toUpperCase();

        if (btrplz.isEmpty()) {
            String btrResult = "SELECT BBSTBBNR, BBSTNABE, BBSTPLZ FROM BP.TBBBST WHERE BBSTNABEG = ?";

            dbConnection = AbstractDao.getConnection();
            preparedStatement = dbConnection.prepareStatement(btrResult);
            preparedStatement.setString(1, btrname);


        } else {
            String btrResult = "SELECT BBSTBBNR, BBSTNABE, BBSTPLZ FROM BP.TBBBST WHERE BBSTNABEG = ? AND BBSTPLZ = ?";

            dbConnection = AbstractDao.getConnection();
            preparedStatement = dbConnection.prepareStatement(btrResult);
            preparedStatement.setString(1, btrname);
            preparedStatement.setString(2, btrplz);

        }
    } catch (SQLException e1) {
        System.out.println("An error with the SQL query occured: ");
        e1.printStackTrace();
    } 
}

public Collection<BtrBean> getBTR() throws SQLException,
        IOException {
    sqlquery();
    final Collection<BtrBean> result = new ArrayList<BtrBean>();

    ResultSet resultset = null;
    try {
        resultset = preparedStatement.executeQuery();

        // while loop to get data
        while (resultset.next()) {
            BtrBean btr = new BtrBean();
            int btrid = resultset.getInt(1);
            String btrplz = resultset.getString(3);
            String btrname = resultset.getString(2);
            btr.setBetriebnr(btrid);
            btr.setBetriebplz(btrplz);
            btr.setBetriebname(btrname);
            result.add(btr);
        //  System.out.println("BTR-ID: " + btrid + " BTR PLZ: " + btrplz + " BTR: " + btrname);
        }

    } catch (SQLException e) {
        e.printStackTrace();
        System.out.println("An error processing the SQL occured: ");
        e.printStackTrace();
    } catch (NullPointerException npe) {
        System.out.println("NullPointerException: ");
        npe.printStackTrace();
    } finally {
        if (preparedStatement != null) preparedStatement.close();
        closeConnection(resultset);
    }
    return result;
}

}

My BTRBean class:

public class BetriebBean {

private String betriebname;
private int betriebnr;
private String betriebplz;

public BetriebBean() {

}

public BetriebBean(String betriebname, int betriebnr, String betriebplz) {
    super();
    this.betriebname = betriebname;
    this.betriebnr = betriebnr;
    this.betriebplz = betriebplz;
}
public String getBetriebname() {
    return betriebname;
}
public void setBetriebname(String betriebname) {
    this.betriebname = betriebname;
}
public int getBetriebnr() {
    return betriebnr;
}
public void setBetriebnr(int betriebnr) {
    this.betriebnr = betriebnr;
}
public String getBetriebplz() {
    return betriebplz;
}
public void setBetriebplz(String betriebplz) {
    this.betriebplz = betriebplz;
}

}

//edit:

My whole View.class:

import java.awt.FlowLayout;
import javax.swing.JButton;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JTable;
import javax.swing.JTextField;

public class View extends JFrame{

private static final long serialVersionUID = 1L;

public static final String SEARCH = "SEARCH";

private JLabel searchbtrlabel = new JLabel("BTR name:");
public static JTextField searchbtrname = new JTextField(10);
private JLabel searchbtrlabel = new JLabel("PLZ:");
public static JTextField searchbtrplz = new JTextField(10);
private JButton searchbutton = new JButton();

public View() {
    this.setTitle("BTR search TBBBST");

    this.setDefaultCloseOperation(EXIT_ON_CLOSE);

    this.setLayout(new FlowLayout());

    this.add(searchbtrlabel);
    this.add(searchbtrname);
    this.add(searchbtrplzlabel);
    this.add(searchbtrplz);

    searchbutton.setText("Search");
    searchbutton.setActionCommand(View.SEARCH);
    this.add(searchbutton);

    JTable table = new JTable();
    this.add(table);

    this.setSize(600, 400);
    setResizable(false);
    //this.pack();
}

public JTextField getSearchbtrname() {
    return searchbetriebname;
}

public JTextField getSearchbbtrplz() {
    return searchbetriebplz;
}

public JButton getSearchbutton() {
    return searchbutton;
}
}

My Controller class:

import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.util.Observable;
import java.util.Observer;

import mvc.Model;
import dbconnect.dao.impl.BTRDaoImpl;

public class Controller implements Observer, ActionListener{

private Model model;
@SuppressWarnings("unused")
private View view;

public Controller(Model model, View view) {
    this.model = model;
    this.view = view;

    model.addObserver(this);
    view.getSearchbutton().addActionListener(this);
    view.setVisible(true);
}

@Override
public void actionPerformed(ActionEvent e) {
    switch (e.getActionCommand()) {
    case View.SEARCH:
        model.search();
        view.table.setModel(ResultSetToTable.buildTableModel(BTRDaoImpl.resultset));
        break;

    default:
        System.out.println("Error : " + e.getActionCommand());
        break;
    }

}

@Override
public void update(Observable o, Object arg) {
    // TODO Auto-generated method stub

}

}

Answer 1

Firstly, include rs2xml.jar in your libraries. You can find it here

In whatever action to populate your MySQL query in your JTable use following general idea:

public void sqlquery() {
    try {
        String btrResult = "SELECT BBSTBBNR, BBSTNABE, BBSTPLZ FROM BP.TBBBST WHERE BBSTNABEG = ?";
        preparedStatement = dbConnection.prepareStatement(btrResult);
        dbConnection.setString(1, btrname);
        ResultSet rs =dbConnection.executeQuery();
        Ur_table_name.setModel(DbUtils.resultSetToTableModel(DbUtils.resultSetToel(rs));  //this line of code will show it in your JTable
    }catch(Exception e){

    }

Answer 2

What is your question? Try being more specific than "[...] but from there on I don't know how to progress." What do you want to do? Are the results from your query visible in the table?

Using the answer from Paul Vargas over here Most simple code to populate JTable from ResultSet, you could start with something like this (using Java 8):

import java.sql.*;
import java.util.Vector;
import javax.swing.*;
import javax.swing.table.*;

public class ResultSetToTable {
    public static void main(final String[] arguments) {
        SwingUtilities.invokeLater(() -> {
            try {
                new ResultSetToTable().createAndShowGui();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        });
    }

    private void createAndShowGui() throws SQLException {
        final JFrame frame = new JFrame("Stack Overflow");
        frame.setBounds(100, 100, 800, 600);
        frame.setDefaultCloseOperation(WindowConstants.EXIT_ON_CLOSE);

        final JPanel panel = new JPanel();
        final TableModel tableModel = buildTableModel(getData("Audi"));
        final JTable table = new JTable(tableModel);
        panel.add(new JScrollPane(table));
        frame.getContentPane().add(panel);

        frame.setVisible(true);
    }

    private ResultSet getData(final String btrName) throws SQLException {
        final String url = "jdbc:h2:/Freek/TBBBST";
        final Connection connection = DriverManager.getConnection(url, "me", "123");
        final String sql = "SELECT BBSTBBNR, BBSTNABE, BBSTPLZ " +
                           "FROM BP.TBBBST " +
                           "WHERE BBSTNABEG = ?";
        final PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1, btrName);
        return preparedStatement.executeQuery();
    }

    /**
     * See https://stackoverflow.com/a/10625471/1694043
     */
    public static TableModel buildTableModel(final ResultSet resultSet)
            throws SQLException {
        int columnCount = resultSet.getMetaData().getColumnCount();

        // Column names.
        Vector<String> columnNames = new Vector<>();
        for (int columnIndex = 1; columnIndex <= columnCount; columnIndex++) {
            columnNames.add(resultSet.getMetaData().getColumnName(columnIndex));
        }

        // Data of the table.
        Vector<Vector<Object>> dataVector = new Vector<>();
        while (resultSet.next()) {
            Vector<Object> rowVector = new Vector<>();
            for (int columnIndex = 1; columnIndex <= columnCount; columnIndex++) {
                rowVector.add(resultSet.getObject(columnIndex));
            }
            dataVector.add(rowVector);
        }

        return new DefaultTableModel(dataVector, columnNames);
    }
}

For older versions of Java, you should be able to use this version of the main method:

public static void main(final String[] arguments) {
    SwingUtilities.invokeLater(new Runnable() {
        @Override
        public void run() {
            try {
                new ResultSetToTable().createAndShowGui();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    });
}

Edit: connecting controller and table

To make the table available outside the view, you need to convert the table variable in the View() constructor into a field (like you have done with searchbtrname) and create a getTable getter method for it (like you have done with getSearchbtrname). In the Controller.actionPerformed method you can now change view.table into view.getTable().