Applet connection to MySQL database using JDBC

[Ryco]

Hi,

For coursework at school I am using java to create a system which uses a database to store information and a java applet to display and insert from and into the database.

I have been developing the program with the database and applet both running on the same computer, but in reality the database is server side and the applet is client side.

When i attempt to connect to the database on another server, i am unable to make a connection, although i do change the address from localhost to the domain on which the database is hosted.

Here is the code:

	public static void setupConnection() {
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			con = DriverManager.getConnection("jdbc:mysql://thenicestthing.co.uk/music?user=username&password=******");
		}catch(Exception e){
			e.printStackTrace();
		}
	}

Annnnd this is the error I get, which would be expeced:

com.mysql.jdbc.exceptions.jdbc4.CommunicationsExce ption: Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)... etc.

Some help would be brilliant!

[travishein]

yea, This keeps coming up. where applets trying to make JDBC database connections don't work in general. I should write a post or a top level FAQ for this or something.

Mainly because JDBC is a server side technology. Where applets may only always and forever communicate with the HTTP server of which they originated from (same server as their codebase). and in general the database will almost always be on a different server.

So, we need some kind of web service or custom query mechanism, eg. XML RPC over HTTP, or JSON over HTTP, perhaps RMI, but likely something over HTTP is better.

where we have the APPLET invoke HTTP requests to a Servlet in the server of which receives these requests (also handles authentication, authrorization) and then uses a JDBC connection to the database to invoke some query, unpack the result set into the HTTP servlet response in a suitable format (e.g XML, JSON, etc, what ever was used for request and what ever the applet is expecting as a response).

Which means you'd need a webapp at least with a servlet listening on a url that your applet is able to invoke, and have the database JDBC connection available to this servlet.

[Ryco]

yea, This keeps coming up. where applets trying to make JDBC database connections don't work in general. I should write a post or a top level FAQ for this or something.

Mainly because JDBC is a server side technology. Where applets may only always and forever communicate with the HTTP server of which they originated from (same server as their codebase). and in general the database will almost always be on a different server.

So, we need some kind of web service or custom query mechanism, eg. XML RPC over HTTP, or JSON over HTTP, perhaps RMI, but likely something over HTTP is better.

where we have the APPLET invoke HTTP requests to a Servlet in the server of which receives these requests (also handles authentication, authrorization) and then uses a JDBC connection to the database to invoke some query, unpack the result set into the HTTP servlet response in a suitable format (e.g XML, JSON, etc, what ever was used for request and what ever the applet is expecting as a response).

Which means you'd need a webapp at least with a servlet listening on a url that your applet is able to invoke, and have the database JDBC connection available to this servlet.

Thanks a lot for the reply.

I was thinking of using a php script to simply deal with SQL queries, and then request the page and read the output as a string, as I'm not at all confident in writing jsp, especially with a problem as complex as this, I'm doing my computer science degree next year, I'm pretty amateur at the moment!

My current code makes use of result sets, would there be a way of creating a result set from the php page output?

[travishein]

maybe render some simple xml-like output to model rows and columns.

<resultset>
  <r>
   <c name="mycolumn1">some value</c>
   <c>bla bla</c>
   <c>
  </r>
  . . .
</resultset>

[Syahrul]

You can also use Applet parameter to pass data between Javascript and use Ajax to update/insert the database using PHP.