wood burning stoves 2.0*
The moose likes Swing / AWT / SWT and the fly likes joining two tables into one database table Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Java » Swing / AWT / SWT
Bookmark "joining two tables into one database table " Watch "joining two tables into one database table " New topic
Author

joining two tables into one database table

Neha Kaushik
Greenhorn

Joined: Apr 03, 2013
Posts: 29
i have two different tables request and approval.
approved :
sno dat approved_by id hardware quantity approved_quantity
1 2013-04-06 A.S.C cm sssss3636 monitor 3 3
2 2013-04-06 A.S.C cm sssss3636 printer 4 4
3 2013-04-06 A.S.C it ddddd6086 monitor 3 3
4 2013-04-06 A.S.C it ddddd6086 printer 4 4
5 2013-04-06 A.S.C it ddddd6086 switch 2 2

request :

sno fileno filesub hardware quantity department_name id rdate
1 hgdsh12 emnefkjhjems mouse 1 cm cmemnefkjhjems966 2001-01-01
2 ss111 hgfh mouse 1 cm cmhgfh695 2013-01-16
3 ss111 hgfh printer 2 cm cmhgfh695 2013-01-16


and i have a jtable in which i want to insert data.
i want department_name, rdate,fileno, filesub from request table and dat, quantity ,approved_quantity from approval using where clause..
please help...
give code for it..
any help will be appreciated..




Swastik Dey
Rancher

Joined: Jan 08, 2009
Posts: 1447
    
    6

What is the relationship between these two tables? I mean what are the joining keys.


Swastik
Neha Kaushik
Greenhorn

Joined: Apr 03, 2013
Posts: 29
hardware is the joining key. which selected by the user from given list of hardware binded with database.
Swastik Dey
Rancher

Joined: Jan 08, 2009
Posts: 1447
    
    6

try this
-----------
select a.department_name,a.rdate,a.fileno,a.filesub,b.dat,b.quantity,b.approved_quantity from
request a inner join approval b on a.hardware=b.hardware and a.hardware='hardware given by user'
Neha Kaushik
Greenhorn

Joined: Apr 03, 2013
Posts: 29
i used your query.but my code execute upto a point i mentioned below.


try {
Class.forName("com.mysql.jdbc.Driver");//Connection establishment to the database

Connection con1 = DriverManager.getConnection("jdbc:mysql://localhost:3306/dbswing", "root", "root");

System.out.println("*** Connect to the database ***");
int index = jList1.getSelectedIndex();
String click = (jList1.getModel().getElementAt(index).toString());
String sql = "select a.department_name,a.rdate,a.fileno,a.filesub,b.dat,b.quantity,b.approved_quantity from request a inner join approval b on a.hardware=b.hardware and a.hardware='" + click + "'";
PreparedStatement pst = con1.prepareStatement(sql);
ResultSet rst = pst.executeQuery();
ResultSetMetaData rsMetaData = (ResultSetMetaData) rst.getMetaData();
int cols = rsMetaData.getColumnCount();
System.out.println("database" + cols);
//EXCECUTE UPTO HERE.

for(int i=1;i<cols;i++){
columnNames.addElement (rsMetaData.getColumnName(i));
}
while(rst.next()){
Vector row= new Vector(cols);

for(int i=1;i<=cols-1;i++){
row.addElement(rst.getObject(i));
}
int r=row.size();
System.out.println(r+"row val");
//data.addElement(row);
for(int i=1 ;i<=r;i++){
// if (rst.next()){
String dept = rst.getString("department_name");
System.out.println(dept);
String from = rst.getString("rdate");
System.out.println(from);
String to = rst.getString("dat");
System.out.println(to);
String fileno = rst.getString("fileno");
System.out.println(fileno);
String sub = rst.getString("filesub");
System.out.println(sub);
String quant = rst.getString("quantity");
System.out.println(quant);
String aquant = rst.getString("approved_quantity");
System.out.println(aquant);
//
DefaultTableModel model1 = (DefaultTableModel) mis_item.getModel();

model1.addRow(new Object[]{dept,from,to,fileno,sub,quant,aquant});}
} }

catch (Exception ex) {
JOptionPane.showMessageDialog(null, "Error in submitting data!" + ex);
}
please tell me the problem in this code.
Swastik Dey
Rancher

Joined: Jan 08, 2009
Posts: 1447
    
    6

Neha,

If the remaining part of the code is not executing, then it might be generating some kind of exception, try to trace that. Debug mode might a good option.
Neha Kaushik
Greenhorn

Joined: Apr 03, 2013
Posts: 29
sir.. it is not entering in the while loop..
while (rst.next())
Swastik Dey
Rancher

Joined: Jan 08, 2009
Posts: 1447
    
    6

It means its not getting any matching records corresponding to your query.
Neha Kaushik
Greenhorn

Joined: Apr 03, 2013
Posts: 29
now what should i do..
Swastik Dey
Rancher

Joined: Jan 08, 2009
Posts: 1447
    
    6

Make sure you have matching records in the underlying table.
Neha Kaushik
Greenhorn

Joined: Apr 03, 2013
Posts: 29
thank you..
i solved my problem..
i used this query..


"SELECT request.department_name , request.rdate , request.fileno , request.filesub , approval.dat , approval.quantity , approval.approved_quantity FROM request INNER JOIN approval ON request.id = approval.id WHERE approval.hardware = '" + click + "' ";



i have another problem
please help..


i have a jlist binded with database.. in database i stored multiple items in single id.. it is displaying it as every item with that id..
for eg..
i selected from a jlist mouse, printer, ups and stored it with a unique id..
in database it is showing like this

hardware quantity id
mouse 1 it request8696
printer 2 it request8696
switch 34 it request8696

now i have bind this db table with another jlist which showing only id's..

i want it to show it request8696 to b shown only once.. but it is showing 3 times..
i have used netbeans bind elements to bind jlist..
please help..
thank you in advance..
Swastik Dey
Rancher

Joined: Jan 08, 2009
Posts: 1447
    
    6

I am not too sure about netbeans binding properties, but there must be a property there where you are specifying a table name. Just see if you can specify a sql query instead of table name, if this works, try a query with distinct clause.
Neha Kaushik
Greenhorn

Joined: Apr 03, 2013
Posts: 29
here is binding code from netbeans...

jList2 = new javax.swing.JList();


org.jdesktop.swingbinding.JListBinding jListBinding = org.jdesktop.swingbinding.SwingBindings.createJListBinding(org.jdesktop.beansbinding.AutoBinding.UpdateStrategy.READ_WRITE, requestList, jList2);
jListBinding.setDetailBinding(org.jdesktop.beansbinding.ELProperty.create("${id}"));
bindingGroup.addBinding(jListBinding);

jList2.addMouseListener(new java.awt.event.MouseAdapter() {
public void mouseClicked(java.awt.event.MouseEvent evt) {
jList2MouseClicked(evt);
}
});

jScrollPane2.setViewportView(jList2);


where should i use query or DISTINCT clause??
Swastik Dey
Rancher

Joined: Jan 08, 2009
Posts: 1447
    
    6

Neha,

This is the generated code, but in netbeans IDE there must be an option from where you are specifying the table name. Instead of the table name just see can you use a sql query there.
Swastik Dey
Rancher

Joined: Jan 08, 2009
Posts: 1447
    
    6

By the way, what database are you using?
Neha Kaushik
Greenhorn

Joined: Apr 03, 2013
Posts: 29
sir, are you talking about this code..
komalPUEntityManager = java.beans.Beans.isDesignTime() ? null : javax.persistence.Persistence.createEntityManagerFactory("komalPU").createEntityManager();
requestQuery = java.beans.Beans.isDesignTime() ? null : komalPUEntityManager.createQuery("SELECT d FROM Request d");
requestList = java.beans.Beans.isDesignTime() ? java.util.Collections.emptyList() : requestQuery.getResultList();


Swastik Dey
Rancher

Joined: Jan 08, 2009
Posts: 1447
    
    6

Can you tell what database you are using?
Neha Kaushik
Greenhorn

Joined: Apr 03, 2013
Posts: 29
i am using MySql database........
sir kindly help me please .i am facing this problem since last month.i tried and search each solution from google.
this is code from requestQuery binded with jlist

bindingGroup = new org.jdesktop.beansbinding.BindingGroup();

komalPUEntityManager = java.beans.Beans.isDesignTime() ? null : javax.persistence.Persistence.createEntityManagerFactory("komalPU").createEntityManager();
requestQuery = java.beans.Beans.isDesignTime() ? null : komalPUEntityManager.createQuery("SELECT DISTINCTROW d FROM Request d ");
requestList = java.beans.Beans.isDesignTime() ? java.util.Collections.emptyList() : requestQuery.getResultList();

this code is present in a generated code named initcomponent()....

private void initComponents() {
bindingGroup = new org.jdesktop.beansbinding.BindingGroup();

komalPUEntityManager = java.beans.Beans.isDesignTime() ? null : javax.persistence.Persistence.createEntityManagerFactory("komalPU").createEntityManager();
requestQuery = java.beans.Beans.isDesignTime() ? null : komalPUEntityManager.createQuery("SELECT DISTINCTROW d FROM Request d ");
requestList = java.beans.Beans.isDesignTime() ? java.util.Collections.emptyList() : requestQuery.getResultList();
idQuery = java.beans.Beans.isDesignTime() ? null : komalPUEntityManager.createQuery("SELECT i FROM Id i");
idList = java.beans.Beans.isDesignTime() ? java.util.Collections.emptyList() : idQuery.getResultList();
jPanel1 = new javax.swing.JPanel();
jLabel1 = new javax.swing.JLabel();
jLabel2 = new javax.swing.JLabel();
jLabel3 = new javax.swing.JLabel();
approved_by = new javax.swing.JComboBox();
jScrollPane1 = new javax.swing.JScrollPane();
jTable1 = new javax.swing.JTable();
ok_approval = new javax.swing.JButton();
jDateChooser1 = new com.toedter.calendar.JDateChooser();
jScrollPane2 = new javax.swing.JScrollPane();
jList2 = new javax.swing.JList();

setDefaultCloseOperation(javax.swing.WindowConstants.DISPOSE_ON_CLOSE);
setTitle("APPROVAL");
addWindowListener(new java.awt.event.WindowAdapter() {
public void windowClosed(java.awt.event.WindowEvent evt) {
close(evt);
}
});

jLabel1.setFont(new java.awt.Font("Times New Roman", 1, 18)); // NOI18N
jLabel1.setText("APPROVAL DATE");

jLabel2.setFont(new java.awt.Font("Times New Roman", 1, 18)); // NOI18N
jLabel2.setText("APPROVED BY");

jLabel3.setFont(new java.awt.Font("Times New Roman", 1, 18)); // NOI18N
jLabel3.setText("REQUEST ID");

approved_by.setFont(new java.awt.Font("Times New Roman", 0, 18)); // NOI18N
approved_by.setModel(new javax.swing.DefaultComboBoxModel(new String[] { "A.S.C", "SECY", "TECH" }));
approved_by.addActionListener(new java.awt.event.ActionListener() {
public void actionPerformed(java.awt.event.ActionEvent evt) {
approved_byActionPerformed(evt);
}
});

jTable1.setModel(new javax.swing.table.DefaultTableModel(
new Object [][] {

},
new String [] {
"hardware", "quantity", "ap quantity"
}
) {
boolean[] canEdit = new boolean [] {
false, false, true
};

public boolean isCellEditable(int rowIndex, int columnIndex) {
return canEdit [columnIndex];
}
});
jScrollPane1.setViewportView(jTable1);
jTable1.getColumnModel().getColumn(0).setResizable(false);
jTable1.getColumnModel().getColumn(1).setResizable(false);
jTable1.getColumnModel().getColumn(2).setResizable(false);

ok_approval.setFont(new java.awt.Font("Times New Roman", 1, 18)); // NOI18N
ok_approval.setText("OK");
ok_approval.addActionListener(new java.awt.event.ActionListener() {
public void actionPerformed(java.awt.event.ActionEvent evt) {
ok_approvalActionPerformed(evt);
}
});

jDateChooser1.setDateFormatString("d MMM,yyyy");
jDateChooser1.setFont(new java.awt.Font("Arial", 0, 18)); // NOI18N
jDateChooser1.setMinSelectableDate(new java.util.Date(1325359910000L));

org.jdesktop.swingbinding.JListBinding jListBinding = org.jdesktop.swingbinding.SwingBindings.createJListBinding(org.jdesktop.beansbinding.AutoBinding.UpdateStrategy.READ_WRITE, requestList, jList2, "");
jListBinding.setDetailBinding(org.jdesktop.beansbinding.ELProperty.create("${id}"));
bindingGroup.addBinding(jListBinding);

jList2.addMouseListener(new java.awt.event.MouseAdapter() {
public void mouseClicked(java.awt.event.MouseEvent evt) {
jList2MouseClicked(evt);
}
});
jScrollPane2.setViewportView(jList2);

javax.swing.GroupLayout jPanel1Layout = new javax.swing.GroupLayout(jPanel1);
jPanel1.setLayout(jPanel1Layout);
jPanel1Layout.setHorizontalGroup(
jPanel1Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
.addGroup(jPanel1Layout.createSequentialGroup()
.addGroup(jPanel1Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
.addGroup(jPanel1Layout.createSequentialGroup()
.addGap(305, 305, 305)
.addComponent(ok_approval))
.addGroup(jPanel1Layout.createSequentialGroup()
.addGap(34, 34, 34)
.addGroup(jPanel1Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
.addComponent(jLabel1, javax.swing.GroupLayout.PREFERRED_SIZE, 177, javax.swing.GroupLayout.PREFERRED_SIZE)
.addComponent(jLabel2, javax.swing.GroupLayout.PREFERRED_SIZE, 156, javax.swing.GroupLayout.PREFERRED_SIZE)
.addComponent(jLabel3, javax.swing.GroupLayout.PREFERRED_SIZE, 168, javax.swing.GroupLayout.PREFERRED_SIZE))
.addGap(69, 69, 69)
.addGroup(jPanel1Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING, false)
.addComponent(jDateChooser1, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
.addComponent(approved_by, 0, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
.addComponent(jScrollPane2, javax.swing.GroupLayout.PREFERRED_SIZE, 309, javax.swing.GroupLayout.PREFERRED_SIZE)))
.addGroup(jPanel1Layout.createSequentialGroup()
.addGap(48, 48, 48)
.addComponent(jScrollPane1, javax.swing.GroupLayout.PREFERRED_SIZE, 587, javax.swing.GroupLayout.PREFERRED_SIZE)))
.addContainerGap(51, Short.MAX_VALUE))
);
jPanel1Layout.setVerticalGroup(
jPanel1Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
.addGroup(jPanel1Layout.createSequentialGroup()
.addGap(30, 30, 30)
.addGroup(jPanel1Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
.addComponent(jLabel1, javax.swing.GroupLayout.PREFERRED_SIZE, 47, javax.swing.GroupLayout.PREFERRED_SIZE)
.addComponent(jDateChooser1, javax.swing.GroupLayout.PREFERRED_SIZE, 42, javax.swing.GroupLayout.PREFERRED_SIZE))
.addGap(18, 18, 18)
.addGroup(jPanel1Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING, false)
.addComponent(jLabel2, javax.swing.GroupLayout.DEFAULT_SIZE, 42, Short.MAX_VALUE)
.addComponent(approved_by))
.addGap(18, 18, 18)
.addGroup(jPanel1Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
.addComponent(jLabel3, javax.swing.GroupLayout.PREFERRED_SIZE, 30, javax.swing.GroupLayout.PREFERRED_SIZE)
.addComponent(jScrollPane2, javax.swing.GroupLayout.PREFERRED_SIZE, 115, javax.swing.GroupLayout.PREFERRED_SIZE))
.addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.UNRELATED)
.addComponent(jScrollPane1, javax.swing.GroupLayout.PREFERRED_SIZE, 254, javax.swing.GroupLayout.PREFERRED_SIZE)
.addGap(18, 18, 18)
.addComponent(ok_approval)
.addContainerGap(88, Short.MAX_VALUE))
);

javax.swing.GroupLayout layout = new javax.swing.GroupLayout(getContentPane());
getContentPane().setLayout(layout);
layout.setHorizontalGroup(
layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
.addGroup(layout.createSequentialGroup()
.addContainerGap()
.addComponent(jPanel1, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
.addGap(27, 27, 27))
);
layout.setVerticalGroup(
layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
.addGroup(javax.swing.GroupLayout.Alignment.TRAILING, layout.createSequentialGroup()
.addContainerGap(13, Short.MAX_VALUE)
.addComponent(jPanel1, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
.addGap(335, 335, 335))
);

bindingGroup.bind();

eroor occur on execution...........
Exception in thread "AWT-EventQueue-0" java.lang.IllegalArgumentException: An exception occurred while creating a query in EntityManager:
Exception Description: Error compiling the query [SELECT DISTINCTROW d FROM Request d ], line 1, column 35: multiple declaration of identification variable [d], previously declared as [DISTINCTROW d].
at org.eclipse.persistence.internal.jpa.EntityManagerImpl.createQuery(EntityManagerImpl.java:1477)
at com.dbmanagement.frame.Approval.initComponents(Approval.java:54)
at com.dbmanagement.frame.Approval.<init>(Approval.java:34)



sir please help me..
Swastik Dey
Rancher

Joined: Jan 08, 2009
Posts: 1447
    
    6

Create a view in mysql with the distinct query for e.g.



Now try to use myview (created above) as binding source in netbeans.
 
jQuery in Action, 2nd edition
 
subject: joining two tables into one database table
 
Similar Threads
ambiguity in jlist binded with database in netbeans
can you do that in one query?
Get SUM of product quantity for product 'A' of Company 'XYZ' from database monthwise for whole year.
Weblogic server window exit when try to start the server
Get Sum of column and the retrive the data month wise.