Replace previous rows in jtable with query on button click - java swing

Question

I am new to jtable and I am working on them with h2 database. I have millions of records in my table so I am using pagination to get the required data per page from DB. So far, I have done with creating pagination buttons, and get data from query according to page number.

For example, limit set to 100000, If I am on page 1, then offset in query will be 0, if I am on page 2, offset will be 200001 and so on .....

What I am doing, I am calling an eventActionListener or pagination button click. Which gets the number written on button. Multiply it with recorsPerPage and add 1 to it. which gives me the correct offset, then I pass with new offset to the query and get new results.

What I need here, is that after getting new rows. previous rows should be deleted or hide and new rows should be embedded on jtable. I hop I explained the question.

So, here is my code:

public class Try {

    static Connection conn;
    static JPanel panel;
    static JTable table;
    static JButton buttons;
    static int buttonID;
    static int totalRows;
    static int recordPerPage = 100000;
    static int totalPages = 0;
    static int offSet = 0;

    private static JPanel contentPane;

    /**
     * Launch the application.
     */
    public static void main(String[] args) {
        EventQueue.invokeLater(new Runnable() {
            public void run() {
                try {
                    createAndShowGUI();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

        });
    }

    protected static void createAndShowGUI() throws SQLException {
        JFrame frame = new JFrame();
        frame.setVisible(true);
        frame.setDefaultCloseOperation(frame.EXIT_ON_CLOSE);
        frame.setBounds(30, 50, 1300, 600);
        contentPane = new JPanel();
        contentPane.setBorder(new EmptyBorder(5, 5, 5, 5));
        contentPane.setLayout(new BorderLayout(0, 0));
        frame.setContentPane(contentPane);

        UIManager.put("TabbedPane.selected", Color.lightGray);
        JTabbedPane tabbedPane = new JTabbedPane();
        tabbedPane.setBorder(new EmptyBorder(10, 10, 10, 10));
        frame.add(tabbedPane);

        panel = new JPanel();
        tabbedPane.addTab("TABLE", null, panel, null);
        tabbedPane.setFont(new Font("Dialog", Font.BOLD | Font.ITALIC, 16));
        panel.setBackground(Color.white);

        JPanel panel_1 = new JPanel();
        tabbedPane.addTab("GRAPH", null, panel_1, null);
        panel_1.setBackground(Color.white);

        JTable table = new JTable();
        panel.add(table);
        table.setFillsViewportHeight(true);

        createDBConnection();
    }

    private static void createDBConnection() throws SQLException {
        try {
            Class.forName("org.h2.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        try {
            conn = DriverManager.getConnection("jdbc:h2:file:G:/hs_data/h2_db/test", "sa", "sa");
        } catch (SQLException e) {
            System.out.println("Unable to make connection with DB");
            e.printStackTrace();
        }
        createPaginationButtons(conn);
    }

    private static void createPaginationButtons(Connection conn) throws SQLException {
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT count(*) FROM cdr");
        while (rs.next()) {
            totalRows = rs.getInt(1);
        }
        int v = totalRows % recordPerPage == 0 ? 0 : 1;
        totalPages = totalRows / recordPerPage + v;
        createButton(totalPages);
    }

    private static void createButton(int totalPages) throws SQLException {
        for (int i = 0; i < totalPages;) {
            buttons = new JButton("" + (i + 1));
            buttons.addActionListener(listener);
            panel.add(buttons);
            i++;
        }
        createTable(offSet);

    }

    static ActionListener listener = new ActionListener() {
        public void actionPerformed(ActionEvent e) {
            if (e.getSource() instanceof JButton) {
                String text = ((JButton) e.getSource()).getText();
                buttonID = Integer.parseInt(text);
                offSet = (buttonID * recordPerPage) + 1;
                try {
                    createTable(offSet);
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
        }
    };

    private static void createTable(int offSet) throws SQLException {
        table = new JTable();
        Statement stmt1 = conn.createStatement();
        JOptionPane.showMessageDialog(null, recordPerPage);
        JOptionPane.showMessageDialog(null, offSet);

        ResultSet rs1 = stmt1.executeQuery(
                "SELECT ANUMBER,BNUMBER,DATETIME FROM CDR LIMIT '" + recordPerPage + "' OFFSET '" + offSet + "' ");
        JOptionPane.showMessageDialog(null, offSet);
        table.setModel(DbUtils.resultSetToTableModel(rs1));
        panel.add(new JScrollPane(table));

    }



}

The value of offset in createTable() method changed according to my needs but it doesn't change the table rows? How can I embed new rows and hide/delete previous ?

Look at this answers - https://stackoverflow.com/questions/1481138/how-to-provide-pagination-support-to-a-jtable-in-swing and https://stackoverflow.com/questions/6175139/jtable-row-limitation this might be helpful to you. — Darshit Chokshi, Jul 10 '17 at 06:00
I'd advise against creating a new `TableModel` and definitely not re-add the table to the panel each time you call `createTable` — MadProgrammer, Jul 10 '17 at 06:09
Nope, nothing. I have already seen these questions. But nothing defines my problem — Noob Player, Jul 10 '17 at 06:10
@MadProgrammer I will add the table to panel once but still I need to add new rows. How can I do this ? — Noob Player, Jul 10 '17 at 06:11
@BilalZafar Update the table model, but since you're using `DbUtils`, it's creating a new `TableModel`, which isn't that efficient and effects a lot of the other properties of the `JTable` — MadProgrammer, Jul 10 '17 at 06:12

score 1 · Answer 1 · answered Jul 10 '17 at 06:48

The basic idea is you just need to update the TableModel

Currently, you're using DbUtils which creates a new TableModel each time you call, while you can do this, it's not particularly efficient and will affect other properties of the JTable (like the column widths)

A better solution would be to use a DefaultTableModel and updated it yourself, for example...

private DefaultTableModel model;

private void loadTable(int offset) throws SQLException {
    try (PreparedStatement stmt = conn.prepareStatement("SELECT ANUMBER,BNUMBER,DATETIME FROM CDR LIMIT ? OFFSET ?")) {
        stmt.setInt(1, recordPerPage);
        stmt.setInt(1, offset);
        try (ResultSet rs = stmt.executeQuery()) {
            ResultSetMetaData rsmd = rs.getMetaData();
            int columnCount = rsmd.getColumnCount();
            if (model == null) {
                String[] columnNames = new String[columnCount];
                for (int col = 0; col < columnCount; col++) {
                    columnNames[col] = rsmd.getColumnName(col + 1);
                }
                model = new DefaultTableModel(columnNames, 0);
                table.setModel(model);
            }
            model.setRowCount(0);
            while (rs.next()) {
                Object[] row = new Object[columnCount];
                for (int col = 0; col < columnCount; col++) {
                    row[col] = rs.getObject(col + 1);                        
                }
                model.addRow(row);
            }
        }
    }
}

This assumes you've previously created a JTable and added it to the GUI

yes thanks, I have done it in very simple way with TableModel. Thanks :) — Noob Player, Jul 10 '17 at 06:50

score 0 · Answer 2 · answered Jul 10 '17 at 06:50

So, this is what I changed in my original code, and it works

private static void createTable(int offSet) throws SQLException {

        model.addColumn("ANUMBER");
        model.addColumn("BNUMBER");
        model.addColumn("DATETIME");

        panel.add(new JScrollPane(table));
        populateTable(offSet);
    }

    private static void populateTable(int offSet2) throws SQLException {

        while (model.getRowCount() > 0) {
            model.removeRow(0);
        }
        Statement stmt1 = conn.createStatement();
        ResultSet rs1 = stmt1.executeQuery(
                "SELECT ANUMBER,BNUMBER,DATETIME FROM CDR LIMIT '" + recordPerPage + "' OFFSET '" + offSet + "' ");
        while(rs1.next()) {
            x = rs1.getString("ANUMBER");
            y = rs1.getString("BNUMBER");
            z = rs1.getString("DATETIME");
            model.addRow(new Object[]{x, y, z});
        }

    }

Either uses a `DefaultTableModel`, the `setRowCount` method is VERY efficient, or roll your own custom model backed by something like an `ArrayList` which is quick to resize — MadProgrammer, Jul 10 '17 at 06:54

Replace previous rows in jtable with query on button click - java swing

2 Answers2