Can we NOT return a java.sql.Connection type Object from a Webservice?

[sbanda]

Hi,

Thanks to everybody. I am using Netbeans IDE 6.5.1 with Glass Fish V2. I am trying to write a test webservice which does very little like

1. connecting to the MySQL database
2. returning the java.sql.Connection object back

However I am getting the below shown error while I am trying to "deploy" the webservice into GF 2. The webservice is being built successfully, though.

Error I am getting:
---------------------
Deploying application in domain failed; Deployment Error -- Exception occured in the wsgen process javax.xml.ws.WebServiceException: Unable to create JAXBContext
.........\nbproject\build-impl.xml:546: The module has not been deployed.
BUILD FAILED (total time: 2 seconds)

Here is the code given below.

/**
* Web service operation
*/
@WebMethod(operationName = "opConnect")
public Connection opConnect(@WebParam(name = "strDriver")
String strDriver, @WebParam(name = "strURL")
String strURL, @WebParam(name = "intPort")
String intPort, @WebParam(name = "strHost")
String strHost, @WebParam(name = "strUserName")
String strUserName, @WebParam(name = "strPasscode")
String strPasscode)
{

// MySQL database connection handle
java.sql.Connection connMysqlHandle = null;
try
{
// Get instance of the database driver - "com.mysql.jdbc.Driver"
Class.forName(strDriver).newInstance();

// Should be jdbc:mysql://localhost:3306 by now
strURL = strURL + strHost + ":" + intPort;

// Try connecting to the database
connMysqlHandle = DriverManager.getConnection
(strURL,strUserName,strPasscode);
}
catch (Exception e)
{
System.out.println("Error Connecting to database. Bye");
e.printStackTrace();
}
return connMysqlHandle;
}

Please note that if I replace the return type of opConnect to any primitive data type like java.lang.String or java.lang.Int or anything like that, the deployment is going through. It is therefore proved that the deployment is failing only and only when I try to return an object of type java.sql.Connection. Any prompt clue on this is highly appreciated.

Thanks
Suresh

[Antonio Varela]

Hi,

It's a weird idea to have a web method to return back an instance of
java.sql.Connection: Web services do not operate in this way. Some
objects are not intended to be serialized, and even less you would
want to send them serialized through the net. I.E., a display object
will only be meaningful on your screen type, resolution, color
support, etc. The same applies to database connection objects. The
java.sql.Connection interface even does not require you to implement
java.io.Serializable.

Best regards
Antonio.

On Thu, Nov 19, 2009 at 2:56 PM, sbanda <address-removed> wrote:

[sbanda]

Hi Antonio,

Make sense. For now, I got rid of this functional limitation through a workaround. I have taken the Connection object as "local" to the webservice.

My idea is to connect to database and get the handle back to my swing application so that I can use that through out a series of non continuous insert/select/update operations spanned over across my swing application. I thought it is not a bad idea of keeping the connection open until the application is running. However, having known that this may not work out, I now need to connect to the database everytime I encounter an insert/select/update operation.

Shouldn't this be expensive in busy application environments? Just curious to know your comments on this.

Many thanks for your clarification anyways and have a pleasant weekend

Thanks
Suresh

[Antonio Varela]

On Fri, Nov 20, 2009 at 6:39 PM, sbanda <address-removed> wrote:

[Christopher Lam]

Hi,

Web services are not meant to be used this way, in my opinion, database connections should be hidden from the clients. Each web services method should be specific and self-explanatory, and should do that job well, e.g. updateCustomer, or createCustomer, etc. By doing it your way, the application will not be efficient and cannot scale, and to some extend expensive because each client is holding on to a database connection even though the application may not be doing any database operations. One of the best practices of using database connection is to return it to the pool as soon as the intended operation is completed. Also this will defeat the purpose of having a Database connection pool which encourage database connection sharing. Opening and closing new database connections are expensive operations, and that has been overcomed by using connection pools.

Hope my view is of some help.

Best regards,
Christopher Lam

On 21-Nov-2009, at 9:39 AM, sbanda wrote: