SELECT Statements --- Vectors

[Guest_imported]

I am trying to create a SelectComponent class for selecting data from Maintenance Tables. This class will be used for all the tables. I am trying to use vector objects. But, I am not sure how to do this. The constructor in the code below should take sql statements of the form: Example: "SELECT * FROM TABLENAME WHERE FIELDNAME= ? and FIELDNAME= ?". And, the following code has a few errors too.

Code:

import java.io.*;
import java.sql.*;
import java.util.*;

public class SelectComponent {
private int rowCount;
private int columnCount;
private int currentRow;
private Statement stmt;
private ResultSet rs;
}

/**

Insert the method's description here.
Creation date: (5/2/01 10:24:34 AM)
@param c java.sql.Connection
@param sql java.lang.String
@param input java.lang.String
/
//String input is what the users enter on the screen

public SelectComponent(Connection c, String sql, String input) {

try {

java.util.Vector v = new java.util.Vector();

PreparedStatement p = c.prepareCall(sql);

v.addElement();

for (int i=0; i < v.size(); i++) {
Object o = v.elementAt(i);
if (o instanceof integer) {
p.setInt();
}
else if (o instanceof String) {
p.setString();
}
else if (o instanceof Double) {
p.setDouble();
}

}
}
catch (Exception e) {

e.printStackTrace();

}
}

If I am working with a particular table and its values then I will type in something like this:

&quot;SELECT * FROM ISPROJ WHERE Proj = ? and dept = ? and Cost_Center = ?&quot;;

java.util.Vector v = new java.util.Vector();
v.addElement (new Integer (Proj));
v.addElement (dept);
v.addElement (new Double (Cost_Center);

and so on...

But, how do I do it for multiple tables and values???

I will greatly appreciate any help.

Thanks,

Naveen

 

[LeonTang]

Hi Naveen,

Just my own opinion, I don't feel that there is a need to create a class for this. I feel that having a method would be better.

Firstly, if you declared the connection as global within the own class, you wouldn't have to pass in the connection as a parameter.

Secondly, since you retrieved data from the database, you would have to process it and in this case, you would have to return the vector to the calling class. But if you have a method and declared a global Vector within the class, you wouldn't have to return the Vector.


Ok, now some solutions to the problem. What you can do is to pass in a String with the name of the table. Although this isn't a good way but it will be able to solve your problem temporary (because if there is a change in your database in the future, you would have to change your codes as well). Using the name passed in, you can just hardcode the column names in each table and then add them to the Vector. It would be something like this,

while (rs.next())
{
//tname would be the String with the value of the table name
if (tname.equals(&quot;ISPROJ&quot)
{
v.addElement(new Integer(rs.getInt(&quot;Proj&quot));
v.addElement(new String(rs.getString(&quot;dept&quot));
v.addElement(new Double(rs.getDouble(&quot;Cost_Centre&quot));
}
else if (//other table names)
...
}

Another method is to retrieve all values as Strings, then when you are about to process the data, change them to the appropriate data type.

try
{
while (rs.next())
{
int i=0;
for (;
v.addElement(new String(rs.getString(i)));
}
}
catch (Exception e)
{
return v;
}


Hope it helps,
Leon If you need additional help, you can email to me at zaoliang@hotmail.com I don't guaranty that I will be able to solve your problems but I will try my best