Win a copy of Mesos in Action this week in the Cloud/Virtualizaton forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

joining two tables into one database table

 
Neha Kaushik
Greenhorn
Posts: 29
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1604
5
Android Java Java ME
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What is the relationship between these two tables? I mean what are the joining keys.
 
Neha Kaushik
Greenhorn
Posts: 29
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
hardware is the joining key. which selected by the user from given list of hardware binded with database.
 
Swastik Dey
Rancher
Posts: 1604
5
Android Java Java ME
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 29
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1604
5
Android Java Java ME
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 29
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
sir.. it is not entering in the while loop..
while (rst.next())
 
Swastik Dey
Rancher
Posts: 1604
5
Android Java Java ME
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It means its not getting any matching records corresponding to your query.
 
Neha Kaushik
Greenhorn
Posts: 29
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
now what should i do..
 
Swastik Dey
Rancher
Posts: 1604
5
Android Java Java ME
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Make sure you have matching records in the underlying table.
 
Neha Kaushik
Greenhorn
Posts: 29
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1604
5
Android Java Java ME
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 29
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1604
5
Android Java Java ME
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1604
5
Android Java Java ME
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
By the way, what database are you using?
 
Neha Kaushik
Greenhorn
Posts: 29
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1604
5
Android Java Java ME
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Can you tell what database you are using?
 
Neha Kaushik
Greenhorn
Posts: 29
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1604
5
Android Java Java ME
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Create a view in mysql with the distinct query for e.g.



Now try to use myview (created above) as binding source in netbeans.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic