在JTable(Java)中显示SQL查询的ResultSet

Question

我正在开发一个程序，使用该程序可以搜索数据库并显示结果。

我现在被困在展示部分了。我的SQL查询已经工作并返回结果。我正在使用PreparedStatement来填充我的ResultSet。然而，我不知道如何将ResultSet的数据返回到我想要用来显示数据的JTable。有人能向我详细解释如何做到这一点吗？或者是否有更好的方法来代替我看不到的JTable？

我正在使用MVC模型和DAO模式，但我对编程还是很陌生。到目前为止，在研究它的过程中，我发现最好的解决方案是创建一个自定义表类，但从那时起，我不知道如何前进。

我的自定义表类：

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;
    }
}
}

我的View类的相关部分：

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);
}

我的BTRDaoImpl类带有sql查询和结果集：

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;
}
}

我的BTRBean类：

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;
}
}

//编辑：

我的整个视图类：

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;
}
}

我的控制器类：

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

首先，在库中包含rs2xml.jar。你可以在这里找到

在JTable中填充MySQL查询的任何操作中，都要使用以下总体思想：

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

您的问题是什么？试着比"[…]"更具体，但从那以后我不知道如何进步。你想做什么？查询的结果在表中可见吗？

使用Paul Vargas在这里给出的答案从ResultSet中填充JTable的最简单的代码，您可以从以下内容开始（使用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);
    }
}

对于旧版本的Java，您应该能够使用以下版本的main方法：

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

编辑：连接控制器和表格

要使该表在视图之外可用，需要将View()构造函数中的table变量转换为字段（就像您对searchbtrname所做的那样），并为其创建getTable getter方法（就像对getSearchbtrname所做的一样）。在Controller.actionPerformed方法中，现在可以将view.table更改为view.getTable()。