One Database Connectin Object 1

[meltdown]

Has anyone written or seen a class somewhere that takes in an SQL and returns the result set one row at a time back to the calling object. I am trying to simplify all my classes and putting all the database stuff in one place would be great.

thanks

 

[swaroop]

Declare a vector and push the record as a string and use it any where you need it.

Swaroop.

 

[ahujadeepa]

i have done all this in one program. u can use the same. i m attaching it to this message.


Following method returns result set

public java.util.Collection select(String Str_sqlstmt) {
/** Statement object to save & execute the query
*/
Statement select_stmt = null;

/** To save the result of the select query
*/
Collection records = null;

/** To save the ResultSet object
*/
ResultSet rs = null;

/** To save the ResultSetMetaData object
*/
ResultSetMetaData rsmd = null;

/** To save the total number of columns
*/
int columncount = 0;

/** To save the column value
*/
String strValue = null;

NECPCLogger.logMessage("3", "The select statement is : " + Str_sqlstmt, DBAccessObject.class);

records = new ArrayList();
try
{
select_stmt = this.conn.createStatement();
rs = select_stmt.executeQuery(Str_sqlstmt);
rsmd = rs.getMetaData();
columncount = rsmd.getColumnCount();
strValue ="";
while(rs.next()) {
Collection columns = new ArrayList();
for(int i=1; i<=columncount; i++) {
strValue=rs.getString(i);
columns.add(strValue);
}
records.add(columns);
}
}
catch (SQLException sqlex)
{
NECPCException.logException(&quot;5&quot;, &quot;Exception in DBAccessObject.select(String)&quot;, DBAccessObject.class, sqlex);
sqlex.printStackTrace();
}
return records;
}


Follwoing method uses result set to get one row at a time


public Member getMember(String Str_sql) {
Collection records = this.select(Str_sql);
Iterator iterator = records.iterator();
while(iterator.hasNext()) {
member = new Member();
ArrayList row = (ArrayList)iterator.next();
member.setUserId((String)row.get(0)); //Primary Key
member.setCustomerType((String)row.get(1));
member.setUserName((String)row.get(2));
member.setUserpassword((String)row.get(3));
member.setCompanyname((String)row.get(4));
member.setAddress1((String)row.get(5));
member.setAddress2((String)row.get(6));
member.setContactpersonname((String)row.get(7));
member.setContactpersonemail((String)row.get(8));
member.setUserRole((String)row.get(9));
member.setZip(((String)row.get(10)) + ((String)row.get(11)));
member.setTelNo(((String)row.get(12)) + ((String)row.get(13)) + ((String)row.get(14)));

NECPCLogger.logMessage(&quot;3&quot;, &quot;Member:: Id:: &quot; + member.getUserId() + &quot; User:: &quot;+ member.getUserName() , MemberStore.class);
}
return member;
}



Each row is saved in a Member object