adding sum column on jtable that is binded to a database table

[madcloud]

Hi all,

I hope this is the correct thread to make this post.
I have a database application created with NetBeans very similar to this one:
Creating a Custom Java Desktop Database Application - NetBeans 6.5 Tutorial

I would like to add another column on the customer (master) table that will display the sum of the orders cost that each customer has done.

I dont think I need to create another column on the database table to hold that value. I tried to create a new property on the Customer entity but it requires that this property exists in the database table as well so I removed the new property.

I also tried to change the query to
SELECT c, SUM(c.orders.price) From Customer c
and then map the sum to the appropriate column but also didnt work.

Any ideas on how I could make this work?

Thank you
GK

[Fubarable]

My guess is that you'll need to change the TableModel for the JTable to have one more column and then override the table model's getValueAt method to display a sum of data from the row. What kind of TableModel are you currently using?

[madcloud]

Thanks for the quick reply.

I m using the autogenerated table model. I believe its the default one. This is the code for the customerTable:

customerTable.setName("customerTable"); // NOI18N

        org.jdesktop.swingbinding.JTableBinding jTableBinding = org.jdesktop.swingbinding.SwingBindings.createJTableBinding(org.jdesktop.beansbinding.AutoBinding.UpdateStrategy.READ_WRITE, list, customerTable);
        org.jdesktop.swingbinding.JTableBinding.ColumnBinding columnBinding = jTableBinding.addColumnBinding(org.jdesktop.beansbinding.ELProperty.create("${custSurname}"));
        columnBinding.setColumnName("Cust Surname");
        columnBinding.setColumnClass(String.class);
        columnBinding.setEditable(false);
        columnBinding = jTableBinding.addColumnBinding(org.jdesktop.beansbinding.ELProperty.create("${custName}"));
        columnBinding.setColumnName("Cust Name");
        columnBinding.setColumnClass(String.class);
        columnBinding.setEditable(false);
        columnBinding = jTableBinding.addColumnBinding(org.jdesktop.beansbinding.ELProperty.create("${custTel}"));
        columnBinding.setColumnName("Cust Tel");
        columnBinding.setColumnClass(String.class);
        columnBinding.setEditable(false);
        columnBinding = jTableBinding.addColumnBinding(org.jdesktop.beansbinding.ELProperty.create("${custAddr}"));
        columnBinding.setColumnName("Cust Addr");
        columnBinding.setColumnClass(String.class);
        columnBinding.setEditable(false);
        columnBinding = jTableBinding.addColumnBinding(org.jdesktop.beansbinding.ELProperty.create("${comments}"));
        columnBinding.setColumnName("Comments");
        columnBinding.setColumnClass(String.class);
        columnBinding.setEditable(false);
        columnBinding = jTableBinding.addColumnBinding(org.jdesktop.beansbinding.ObjectProperty.create());
        columnBinding.setEditable(false);
        bindingGroup.addBinding(jTableBinding);
        jTableBinding.bind();

I can add a column but I m not sure how the binding can be done. I was thinking something like:

jTableBinding.addColumnBinding(org.jdesktop.beansb inding.ELProperty.create("${customer.orders.price} "));

but it is not allowed by netbeans and its not working since it also needs to get a sum function in there somehow.

I also have created a query that returns the customers with the total price of each ones orders but I dont think this is how it will work since the data is binded directly to the table.

:confused:

Thanks,
GK

[hotplugin]

You should try adding a transient field in customer entity class and setting sum value to it.

[madcloud]

Yeap, that did the trick.

Thanks!!!! :D