• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Passing values into the SQL connection class?

 
Melinda Savoy
Ranch Hand
Posts: 387
Eclipse IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I've never made a database connection in java and building a select statement to retrieve the info needed and am needing some help. I have a class that makes the connection:

// Method to get a connection to a SQL Server 2000 database.
public void SQLConn(){
Connection con = null;
String hostname = "localhost"; // local machine
try {
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
con = DriverManager.getConnection("jdbc:microsoft:sqlserver://"+ hostname +":1433;DatabaseName=esdev;User=test;Password=test");
String tablename = "POI";
String fields = "assetnumber, chargeto, hospcode, itemnumber, lineitemprice, nec, nextacct, purchaseum, quantityordered, recstatus, requisitionnumber, spcdescription, typeswitch";
String query = "";
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("SELECT " + fields + " FROM " + tablename);

while (rs.next()) {
String field = rs.getString(1);
String data = rs.getString(2);
// Do something like System.out.println(mykeyfield);
System.out.println("Field: " + field + " Value: " + data);
}
}
catch (Exception e) {
System.out.println(e.getMessage());
}
finally {
if(con != null){
try { con.close(); }
catch (Exception e) {System.out.println("Error closing connection: " + e.getMessage());
}
}
}
}

But I am needing to pass in the TABLENAME, FIELDS, and QUERY (WHERE statement) into this connection but I'm lost. I've tried googling for some sample code but to no avail. The class that I have that needs to make this call is as follows:

public class ReadSOAPImpl implements org.tempuri.Read_PortType{
public org.texashealth.www.SCM.DataTier.ReadResponse.ReadResponse read(
org.texashealth.www.SCM.DataTier.BTResponse.ReadRequest readRequest
) throws java.rmi.RemoteException {

// Create the "ReadResponse" and "Header" objects.
ReadResponse response = new ReadResponse();
Header responseheader = new Header();

// Stuff the responseheader with the hardcoded VERSION and STATUS.
responseheader.setVersion(BigInteger.valueOf(1));
responseheader.setStatus(Status.fromString("OK"));

//Stuff the "response" object variable by passing in the header object.
response.setHeader(responseheader);

// Is the version in the header valid?
ReadRequest request = new ReadRequest();
if (request.getHeader().getVersion().intValue() != 1){
responseheader.setStatus(Status.fromString("VersionNotSupported"));
return response;
}

// Is there a tablename, query and fieldnames?
if (request.getTableName().toString() == null || request.getQuery().toString() == null || request.getFieldNames().toString() == null){
responseheader.setStatus(Status.fromString("NoTableNameQueryInfoFieldNamesFound"));
return response;
}

// Is there a transaction ID?
Transaction transaction = new Transaction();
if (Transaction.get(readRequest.getTransactionID().intValue()) == null){
responseheader.setStatus(Status.fromString("InvalidTransactionID"));
return response;
}
else{
// Make a SQL connection.
transaction.SQLConn();

return response;
}
}
}

Any direction or help would be appreciated.

Regards.
 
Jacquie Barker
author
Ranch Hand
Posts: 201
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If I understand your question correctly, it would be as simple as changing your SQLConn() method to take String arguments representing the table name, etc.:

public void SQLConn(String fields, String table, String whereClause) { ...

Is this what you really mean, or am I missing some aspect of your question?

Regards,

Jacquie
 
Melinda Savoy
Ranch Hand
Posts: 387
Eclipse IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Jacquie,

Thanks for the time and your response. I think you understand correctly but what I don't think I made clear is what I actually wanted to do which is the two classes that I'm dealing with in my webservice. The SOAPImplentation class and a TRANSACTION class. The parameters that build the select query statement are coming from the SOAPImplentation class and need to be passed to the TRANSACTION where the SQL connection method is located. That is what I am trying to do unless there is another way to accomplish this process.

Thanks again for your reply.

Regards.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic