joining two tables into one database table

[Neha Kaushik]

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]

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

[Neha Kaushik]

hardware is the joining key. which selected by the user from given list of hardware binded with database.

[Swastik Dey]

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]

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]

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]

sir.. it is not entering in the while loop..
while (rst.next())

[Swastik Dey]

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

[Neha Kaushik]

now what should i do..

[Swastik Dey]

Make sure you have matching records in the underlying table.

[Neha Kaushik]

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]

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]

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]

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]

By the way, what database are you using?

[Neha Kaushik]

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]

Can you tell what database you are using?

[Neha Kaushik]

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]

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

CREATE VIEW myview AS SELECT distinct id  FROM request;

Now try to use myview (created above) as binding source in netbeans.