1

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

}

}
Improve this question
2
  • I would use the standard classes and use the result set to fill the table model. See this answer for more information: stackoverflow.com/a/10625471 Commented Dec 3, 2015 at 9:24
  • Wouldn't that still do the same as my custom class? Commented Dec 3, 2015 at 12:43

2 Answers 2

Reset to default
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){

    }
Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

Thanks! I have two questions for that though. I don't have a resultSetToel method, haven't seen it in the example from which I learned about the custom tables either. What would this method look like / what does it do? And as far as I see it, I need to replace the "rs" in this example with my ResultSet which would be "BTRDaoImpl.resultset", right?
What about the resultSeToel method? I haven't seen it in an example so far and apparently it is crucial, because right now without it, it doesn't work. With the error "resultset cannot be resolved or is not a field" pointing at the BTRDaoImpl.resultset in said method.
resultset is imported from sql library import java.sql.ResultSet it will be used inside resultSetToTableModel()) by adding the object rs, this way u will populate ur results to jtabel, do not forget to get ur js2Xml jar from sourceforge
So do I need to change the arguement for this method, which is in my case: table.setModel(DbUtils.resultSetToTableModel(rs);, into something or do I need to declare rs in my View class as well first? Because like this it gives me an error, that it obviously doesn't know the variable.
in my answer I have defined rs as an ResultSet object and the way u see it in my answer works fine for me, what kind of error u get?
|
0

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().

Improve this answer

12 Comments

Wouldn't that mean I'd have to execute the PreparedStatement with the SQL query a second time and therefore slowing down my program a lot?
I think the getData method is only called once and therefore the query is not executed a second time. You can add a println to check that. Is the example above having the same issue as your code? If the example above has issues for you, which issues do you run into?
I got a problem with the invokeLater-method. It is not working like in your example telling me that it needs an arguement, so Iassumed that the try-catch block should be the parameter of that method, but that doesn't seem to work either.
Are you using Java 8 or an older version of Java? "Since April 2015, Oracle is no longer post updates of Java SE 7 to its public download sites."
See the alternative main method above for older versions of Java.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.