permaculture playing cards*
The moose likes Beginning Java and the fly likes Passing values into the SQL connection class? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of JavaScript Promises Essentials this week in the JavaScript forum!
JavaRanch » Java Forums » Java » Beginning Java
Bookmark "Passing values into the SQL connection class?" Watch "Passing values into the SQL connection class?" New topic
Author

Passing values into the SQL connection class?

Melinda Savoy
Ranch Hand

Joined: Jun 21, 2005
Posts: 387

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

Joined: Dec 20, 2000
Posts: 201
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


Author of Beginning Java Objects, Beginning C# Objects, and Taming the Technology Tidal Wave
Melinda Savoy
Ranch Hand

Joined: Jun 21, 2005
Posts: 387

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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Passing values into the SQL connection class?