java swing jtable add two table from database in single table

Question

I have table named tbl_income and tbl_expenses... tbl_income contain four column and four row and tbl_expenses contain four column and three row link in picture below.

I want to mank single table in java swing JTable link picture in below.

how can i make like this.... sorry for bad english..

This is what i've tried:

 DefaultTableModel model = (DefaultTableModel) tbldailybook.getModel();
        try {
            Statement stmt = db.cn.createStatement();
            ResultSet rs = stmt.executeQuery("select * from tblincome,tblexpenses");
            while (rs.next()) {
                model.addRow(new Object[]{rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(6), rs.getString(7), rs.getString(8)});
            }
        } catch (Exception e) {
        }

tblincome have single row but row reply

no without any relation.... i have two table in database and i want to join two table in single JTable — raman dangol, Nov 16 '17 at 06:57
So you have two basic questions; 1- How to merge the data from two tables into a single result set; 2- How to read data from the database and present that data in a `JTable`. Both questions not unique and have been asked more times in the past then I care recount. Before we can attempt to help, you need to make some effort to help yourself. I'd start by breaking down you problem into manageable chunks (like the two questions I've listed) and solve this independently, as a result, you will learn some new things and solve your own problem — MadProgrammer, Nov 16 '17 at 07:00
I do not quite agree with this commander. There is no relationship between the two tables which makes it difficult to use join and get a fair result. I think the right solution is to extract the two tables individually and use them in a common `TableModel`. And that does not make the issue completely trivial. — mr mcwolf, Nov 16 '17 at 07:39
@mrmcwolf Because SQL doesn't support arbiters joins on tables? Extracting the data separately still requires to create a "relationship" in order to be able to display it - which most database systems are more then capable of doing (it's one of the reasons why you have a `join` command) - [as a completely out of context example](https://stackoverflow.com/questions/45976657/join-two-tables-without-relationship) - in fact, based on the examples the OP has listed [this would probably work better](https://stackoverflow.com/questions/9245203/joining-two-sql-tables-with-no-relationship) — MadProgrammer, Nov 16 '17 at 07:48
@ramandangol In order to produce the solution you're trying to produce, you're going to have to devise some kind of relationship, virtually or physically, between the two tables, otherwise each time you try and present it, it may not display the results in the same order — MadProgrammer, Nov 16 '17 at 07:50
@MadProgrammer Using cross-referenced join (cross) will result in "spreading" the records. Left / right join attempts must match the number of entries in the two tables so that no data is lost. — mr mcwolf, Nov 16 '17 at 07:54
@mrmcwolf I tested `select * from income left join expenses on income.id = expenses.id` and it worked fine - On the last row, I got the columns from `income` and `null` for the values from `expenses` ... because non exist for the join — MadProgrammer, Nov 16 '17 at 10:12

mr mcwolf · Accepted Answer · 2017-11-17T10:37:55.170

This is a solution with separate extraction of the two tables and their integration into a common TableModel.

public class MyTableModel extends AbstractTableModel implements TableModel {
    private List<Data> inclomeList;
    private List<Data> expenseList;

    private void setData(List<Data> list, Data data) {
        int rows = getRowCount();
        int row = list.size();
        list.add(data);

        if(row < rows) {
            fireTableRowsUpdated(row, row);
        }
        else {
            fireTableRowsInserted(row, row);
        }
    }

    public void setIncomeData(Data data) {
        if(inclomeList == null) {
            inclomeList = new ArrayList<>();
        }

        setData(inclomeList, data);
    }

    public void setExpenseData(Data data) {
        if(expenseList == null) {
            expenseList = new ArrayList<>();
        }

        setData(expenseList, data);
    }

    @Override
    public String getColumnName(int column) {
        switch (column) {
            case 0:
            case 3:
                return "Date";

            case 1: return "Income";
            case 4: return "Expenses";


            case 2:
            case 5:
                return "Amount";

            default:
                return super.getColumnName(column);
        }
    }

    @Override
    public int getRowCount() {
        if(inclomeList == null || expenseList == null) {
            if(inclomeList != null) {
                return inclomeList.size();
            }
            else if(expenseList != null) {
                return expenseList.size();
            }

            return 0;
        }

        return Math.max(inclomeList.size(), expenseList.size());
    }

    @Override
    public int getColumnCount() {
        return 6;
    }

    @Override
    public Object getValueAt(int row, int column) {
        Data inclome = null;
        Data expense = null;

        if(inclomeList != null && inclomeList.size() > row) {
            inclome = inclomeList.get(row);
        }

        if(expenseList != null && expenseList.size() > row) {
            expense = expenseList.get(row);
        }

        switch (column) {
            case 0: return inclome != null ? inclome.getDate() : "";
            case 1: return inclome != null ? inclome.getName() : "";
            case 2: return inclome != null ? inclome.getAmount() : "";
            case 3: return expense != null ? expense.getDate() : "";
            case 4: return expense != null ? expense.getName() : "";
            case 5: return expense != null ? expense.getAmount() : "";
        }

        return null;
    }

    public void update() {
        SwingWorker<Void, Void> worker = new SwingWorker<Void, Void>() {
            @Override
            protected Void doInBackground() throws Exception {
                Database database = new Database();
                inclomeList = database.getData(Database.TBL_INCOME);
                expenseList = database.getData(Database.TBL_EXPENSES);

                return null;
            }

            @Override
            protected void done() {
                try {
                    get();
                    fireTableDataChanged();
                }
                catch (InterruptedException | ExecutionException e) {
                    e.printStackTrace();
                }
            }
        };

        worker.execute();
    }
}

Interface for working with the database:

public class Database {
    public static final String TBL_INCOME = "tbl_income";
    public static final String TBL_EXPENSES = "tbl_expenses";

    private Connection getConnection() throws SQLException {
        return DriverManager.getConnection("jdbc:mysql://192.168.40.5/test", "root", "");
    }

    public List<Data> getData(String tbl_name) throws SQLException {
        try (Connection connection = getConnection()) {
            String query = "select * from " + tbl_name;

            try(ResultSet rs = connection.createStatement().executeQuery(query)) {
                List<Data> list = new ArrayList<>();
                while (rs.next()) {
                    Data data = new Data();
                    data.setDate(rs.getDate("date"));
                    data.setName(rs.getString("particular"));
                    data.setAmount(rs.getDouble("amount"));

                    list.add(data);
                }

                return list;
            }
        }
    }
}

MainFrame.java

public class MainFrame extends JFrame {

    private JTable table = new JTable(new MyTableModel());

    public MainFrame() throws HeadlessException {
        super("MainFrame");
        createGUI();
    }

    private void createGUI() {
        setDefaultCloseOperation(JFrame.DISPOSE_ON_CLOSE);
        setPreferredSize(new Dimension(600, 400));

        JButton addI = new JButton("+");
        addI.addActionListener(e -> ((MyTableModel)table.getModel()).setIncomeData(new Data()));

        JButton addE = new JButton("+");
        addE.addActionListener(e -> ((MyTableModel)table.getModel()).setExpenseData(new Data()));

        JPanel panel = new JPanel(new FlowLayout(FlowLayout.RIGHT, 5, 0));
        panel.add(addI);
        panel.add(addE);

        JScrollPane scrollPane = new JScrollPane(table);
        add(scrollPane, BorderLayout.CENTER);
        add(panel, BorderLayout.PAGE_END);

        pack();
        setLocationRelativeTo(null);

        ((MyTableModel)table.getModel()).update();
    }

    public static void main(String[] args) {
        SwingUtilities.invokeLater(() -> new MainFrame().setVisible(true));
    }
}

@mrmcwolf how do you set data in Jtable from this method , i'm in confuse. — raman dangol, Nov 16 '17 at 09:00
Thank you @mrmcwolf it really works now i need to try in my project. But it is long process.... — raman dangol, Nov 16 '17 at 09:59
`DefaultTableModel` is a skeleton around which it is right to build the model that will be used in real terms. If you use the `DefaultTableModel` address somewhere, simply replace the `AbstractTableModel` with the `DefaultTableModel` in the `MyTableModel` statement. If you have difficulty using the custom table model (`MyTableModel`), tell what's going to make you difficult. — mr mcwolf, Nov 16 '17 at 11:04
i'm using drag and drop design in that jtable i need to set data by your process @mrmcwolf — raman dangol, Nov 16 '17 at 12:14
Simply add the data to the relevant `List`s with seters. The important thing here is when you add them to call `fireTableRowsUpdated()`/`fireTableRowsInserted()` to update the view. See the editorial above. — mr mcwolf, Nov 16 '17 at 12:41
@mrmcwolf i'm feeling difficult to add column title and last row. — raman dangol, Nov 17 '17 at 10:10
@ramandangol to add a column title, you must override `getColumnName(int)` in MyTableModel. A little later I will add it to the post. — mr mcwolf, Nov 17 '17 at 10:25
See this https://docs.oracle.com/javase/tutorial/uiswing/components/table.html#data — mr mcwolf, Nov 17 '17 at 10:26

MadProgrammer · Answer 2 · 2017-11-16T19:33:59.527

+----+------------+----------------+--------+
| id | dateValue  |   particular   | amount |
+----+------------+----------------+--------+
|  1 | 2017-02-02 | Cash Deposit   |      1 |
|  2 | 2017-02-03 | Cheque Deposit |      2 |
|  3 | 2017-02-08 | Cash Deposit   |      3 |
|  4 | 2017-02-07 | Product Sales  |      4 |
+----+------------+----------------+--------+

+----+------------+------------+--------+
| id | dateValue  | particular | amount |
+----+------------+------------+--------+
|  1 | 2017-05-07 | Factory    |      6 |
|  2 | 2017-06-02 | Staff      |      7 |
|  3 | 2017-06-03 | Travel     |      8 |
+----+------------+------------+--------+

I then used select * from income left join expenses on income.id = expenses.id to query the database and got:

+----+------------+----------------+--------+----+------------+------------+--------+--+
| id | dateValue  |   particular   | amount | id | dateValue  | particular | amount |  |
+----+------------+----------------+--------+----+------------+------------+--------+--+
|  1 | 2017-02-02 | Cash Deposit   |      1 |  1 | 2017-05-07 | Factory    |      6 |  |
|  2 | 2017-02-03 | Cheque Deposit |      2 |  2 | 2017-06-02 | Staff      |      7 |  |
|  3 | 2017-02-08 | Cash Deposit   |      3 |  3 | 2017-06-03 | Travel     |      8 |  |
|  4 | 2017-02-07 | Product Sales  |      4 |    |            |            |        |  |
+----+------------+----------------+--------+----+------------+------------+--------+--+

Which based on the currently available information is probably the best I can do

You could the use something like Most simple code to populate JTable from ResultSet or Retrieving Data from JDBC Database into Jtable to build the JTable ... as available examples

if second table have more row than table one .. record will not display which are more id in second table – raman dangol 8 hours ago

Also, if there are inconsistent IDs, the records will be lost. That's why I said this was not a trivial question

Then in those cases, something like FULL OUTER JOIN would be more useful. However, based on the information I have, full outer join is not supported in MySQL, because that would be useful. However, based on How to do a FULL OUTER JOIN in MySQL? we could do something like...

select * from income left join expenses on income.id = expenses.id union select * from income right join expenses on income.id = expenses.id where income.id is null

which can generator something like

+----+------------+----------------+--------+----+------------+------------+--------+
| id | datevalue  |   particular   | amount | id | datevalue  | particular | amount |
+----+------------+----------------+--------+----+------------+------------+--------+
|  1 | 2017-02-02 | Cash Deposit   |    1.0 |  1 | 2017-05-07 | Factory    |    6.0 |
|  2 | 2017-02-03 | Cheque Deposit |    2.0 |  2 | 2017-06-02 | Staff      |    7.0 |
|  3 | 2017-02-03 | Cash Deposit   |    3.0 |  3 | 2017-06-03 | Travel     |    8.0 |
|  4 | 2017-02-03 | Product Sales  |    4.0 |  4 | 2017-10-01 | Test 1     |   10.0 |
|  5 | 2017-10-02 | Test 2         |   20.0 |    |            |            |        |
+----+------------+----------------+--------+----+------------+------------+--------+

Or, if you prefer to keep things aligned to their "selected sides", something like

select income.id, income.datevalue, income.PARTICULAR, income.AMOUNT, 
       expenses.id, expenses.datevalue, expenses.PARTICULAR, expenses.AMOUNT 
       from income join expenses on income.id = expenses.id
union all
select income.id, income.datevalue, income.PARTICULAR, income.AMOUNT, 
       null, null, null, null 
       from INCOME where not exists (select expenses.id from expenses where expenses.id = income.id)
union all
select null, null, null, null, 
       expenses.id, expenses.datevalue, expenses.PARTICULAR, expenses.AMOUNT 
       from expenses where not exists (select income.id from income where income.id = expenses.id)

Which can generate something like...

+----+------------+----------------+--------+----+------------+------------+--------+
| id | datevalue  |   particular   | amount | id | datevalue  | particular | amount |
+----+------------+----------------+--------+----+------------+------------+--------+
|  1 | 2017-02-02 | Cash Deposit   |    1.0 |  1 | 2017-05-07 | Factory    |    6.0 |
|  2 | 2017-02-03 | Cheque Deposit |    2.0 |  2 | 2017-06-02 | Staff      |    7.0 |
|  3 | 2017-02-03 | Cash Deposit   |    3.0 |  3 | 2017-06-03 | Travel     |    8.0 |
|  4 | 2017-02-03 | Product Sales  |    4.0 |  4 | 2017-10-01 | Test 1     |   10.0 |
|    |            |                |        |  5 | 2017-10-02 | Test 2     |   20.0 |
+----+------------+----------------+--------+----+------------+------------+--------+

At the end of the day, it's still a database issue.

For simplicity, you could create one or more database views in order to simply the query

if second table have more row than table one .. record will not display which are more id in second table — raman dangol, Nov 16 '17 at 11:03
Also, if there are inconsistent IDs, the records will be lost. That's why I said this was not a trivial question. — mr mcwolf, Nov 16 '17 at 11:10
then i have income table and expenses table having date,particular,amount how can i display in jtable — raman dangol, Nov 16 '17 at 11:52
@mrmcwolf Then you look towards something like `full outer join` — MadProgrammer, Nov 16 '17 at 19:34
@ramandangol Then you look towards something like `full outer join` — MadProgrammer, Nov 16 '17 at 19:34
@MadProgrammer solution is almost like what you are looking for, but if you miss the ID, you will get "white spots". The reason for this is that in your decisions you rely on a relationship that does not exist. To use this approach, a match in artificial keys must be guaranteed. This can happen, say, with a `window` function that numbers the rows before using `full outher join`. — mr mcwolf, Nov 17 '17 at 04:12
@mrmcwolf Based on what the OP seems to want and my testing, if a id is missing in either sides, that side just returns a null values, doesn't matter from which side it's missing from, I've even tested with non-linear ids sequences and it seems to work. Since no actual relationship returns, and since the database makes bio guarantee of the order rows are returned in, either our solutions won't return the same result twice (or at least has a low probability). From my perspective, the OP would be better off defining some kind of relationship up front - but that's me — MadProgrammer, Nov 17 '17 at 05:59
@MadProgrammer here https://mcwolfmm.bitbucket.io/fulljoin/ I have shown what I mean. If there is a hole in the ID, a "white spot" is obtained if the key IDs are used. Therefore, if you follow this approach you should use a window function to number the rows. But here we are going through specific solutions for a specific database. — mr mcwolf, Nov 17 '17 at 07:18
@mrmcwolf Based on your linked example (thank you), that would, IMHO, appear to similar to what the OP is trying to achieve, but I don't have enough information available to know one way or another. But at least we've now demonstrated two database based solutions :P — MadProgrammer, Nov 17 '17 at 07:33

java swing jtable add two table from database in single table

2 Answers2