Problem in viewing records ?

[Jan Michael Soan]

Hello to everyone;
I have a problem viewing my existing 2 tables in my database, in which the fields will be displayed in the JTextFields.. A book gave me an idea like this :
try {
Statement statement = connect.createStatement();
String query = "SELECT * FROM Records " +
"WHERE Lastname = '" + t1.getText() + "'";

ResultSet rs = statement.executeQuery( query );
display( rs );
statement.close();
}
catch ( SQLException sqlex ) {
sqlex.printStackTrace();
}
}
public void display( ResultSet rs )
{
try {
rs.next();
t1.setText( rs.getString( 1 ) );
t2.setText( rs.getString( 2 ) );
t3.setText( rs.getString( 3 ) );
t4.setText( rs.getString( 4 ) );
b1.setText( rs.getString( 5 ) );
t5.setText( rs.getString( 6 ) );
t6.setText( rs.getString( 7 ) );
t7.setText( rs.getString( 8 ) );
t8.setText( rs.getString( 9 ) );
t9.setText( rs.getString( 10 ) );
}
catch ( SQLException sqlex ) {
sqlex.printStackTrace();
System.out.println( sqlex.toString() );
}
}
});
Problem :
My problem is, I tried getting the information of the 2 tables but instead of getting the 1st and 2nd table it displayed only the 2nd table.
The specific thing that I want to do is to retrieve the information of 2 tables by getting the IdNumber specified by the user.
Problem Codes :
try {
Statement statement = connect.createStatement();
String query1 = "SELECT * FROM Records " +
"WHERE IdNumber = '" + t1.getText() + "'";
String query2 = "SELECT * FROM Deductions " +
"WHERE IdNumber = '" + t1.getText() + "'";
ResultSet rs1 = statement.executeQuery( query1 );
ResultSet rs2 = statement.executeQuery( query2 );
display1( rs1 );
display1( rs2 );
statement.close();
}
catch ( SQLException sqlex ) {
sqlex.printStackTrace();
}
}
public void display1( ResultSet rs1 )
{
try {
rs1.next();
t1.setText( rs1.getString( 1 ) );
t2.setText( rs1.getString( 2 ) );
t3.setText( rs1.getString( 3 ) );
t4.setText( rs1.getString( 4 ) );
b1.setText( rs1.getString( 5 ) );
t5.setText( rs1.getString( 6 ) );
t6.setText( rs1.getString( 7 ) );
t7.setText( rs1.getString( 8 ) );
t8.setText( rs1.getString( 9 ) );
t9.setText( rs1.getString( 10 ) );
t10.setText( rs1.getString( 11 ) );
t11.setText( rs1.getString( 12 ) );
t12.setText( rs1.getString( 13 ) );
t13.setText( rs1.getString( 14 ) );
t14.setText( rs1.getString( 15 ) );
t15.setText( rs1.getString( 16 ) );
t16.setText( rs1.getString( 17 ) );
t17.setText( rs1.getString( 18 ) );
t18.setText( rs1.getString( 19 ) );
t19.setText( rs1.getString( 20 ) );
t20.setText( rs1.getString( 21 ) );
t21.setText( rs1.getString( 22 ) );
}
catch ( SQLException sqlex ) {
sqlex.printStackTrace();
System.out.println( sqlex.toString() );
}
}
public void display2( ResultSet rs2 )
{
try {
rs2.next();
t22.setText( rs2.getString( 23 ) );
t23.setText( rs2.getString( 24 ) );
t24.setText( rs2.getString( 25 ) );
t25.setText( rs2.getString( 26 ) );
t26.setText( rs2.getString( 27 ) );
t27.setText( rs2.getString( 28 ) );
t28.setText( rs2.getString( 29 ) );
t29.setText( rs2.getString( 30 ) );
t30.setText( rs2.getString( 31 ) );
t31.setText( rs2.getString( 32 ) );
}
catch ( SQLException sqlex ) {
sqlex.printStackTrace();
System.out.println( sqlex.toString() );
}
}
});
Hope someone will help me, thanks God Bless . . .

[Avi Abrami]

Hi Jan Michael,
I don't know if your database supports it (since you didn't mention what database you are using), but you can retrieve the data from both tables, with one query, using a table join. With an Oracle database, the query would be something like this:

select
   R.*
  ,D.*
from
   RECORDS R
  ,DEDUCTIONS D
where
   R.IDNUMBER = D.IDNUMBER
and
   D.IDNUMBER = ?

Of-course, you need to replace the question-mark ("?") with the relevant IDNUMBER value.
Hope this helps.
Good Luck,
Avi.

[Jan Michael Soan]

I'm currently using an Access Database from microsoft. I think Access doesnt support Joining of tables because I've already tried doing the same prototype.
Is there any other way? thanks !

[John Dunn]

Don't know your app, but you may want to consider using SimpleDb or MySQL instead. This way you can get more power out of SQL and still have a very cheap and easy db. These dbs have gui front ends too.
There is another link, a couple below on this forum that talks about java dbs.
I had to migrate a huge db schema in Access to SQLServer and I first moved it into InstantDB, (now SimpleDB). I had to do this in order to be able to use joins, etc. InstantDb had an import command so I did a db copy from one system into the other. I did do some massaging of the data's key fields so the final schema was normalized.
When I had the Access data migrated, I was able to do anything I wanted via SQL and eventually do a straight import into SQLServer.
With a little pain, a lot of your problems will go away.

[Bharat Ramchandani]

if u noticed ur code
display1(rs1);
display1(rs2);
maybe u need to use your second display method. i.e display2(rs2) for ur second query result.. ??
maybe i am wrong.. or its just a typo.

[Avi Abrami]

Looking through the "help" documentation on my MS Access 2002 (installed on my Windows XP machine), it certainly seems that Access supports table joins. I mean, it's only one of the main foundations upon which relational database theory is based. I don't think even Microsoft would dare to make an RDBMS that doesn't support table joins.
By the way, have you searched the "help" on your Access for the word "join"?
Maybe you need to go back and have another look?
Good Luck,
Avi.

[Kevin Mukhar]

Originally posted by Jan Michael Soan:

try {
Statement statement = connect.createStatement();
String query1 = "SELECT * FROM Records " +
"WHERE IdNumber = '" + t1.getText() + "'";
String query2 = "SELECT * FROM Deductions " +
"WHERE IdNumber = '" + t1.getText() + "'";
ResultSet rs1 = statement.executeQuery( query1 );
ResultSet rs2 = statement.executeQuery( query2 );

The proble is your use of a single Statement object to create two ResultSet objects. As the Javadoc for Statement says:
"By default, only one ResultSet object per Statement object can be open at the same time. Therefore, if the reading of one ResultSet object is interleaved with the reading of another, each must have been generated by different Statement objects. All execution methods in the Statement interface implicitly close a statment's current ResultSet object if an open one exists."
So, when you execute query2, the ResultSet from query1 is automatically closed.
You should first attempt doing a join as someone else has suggested.
If you still think a join won't work for you, another option is to use two statement objects. You can have multiple open Statement objects created by the same connection without any problem.