connecting to remote database

Question

what is the best architecture to connect to a remote mysqlserver (database) from java swing standalone client app over the internet without using middle server

Answer 1

JDBC would be the best

try
{
  Class.forName("com.mysql.jdbc.Driver").newInstance();
  String url = "jdbc:mysql://HOST/DATABASE";
  conn = DriverManager.getConnection(url, "username", "password");
  doTests();
  conn.close();
}

Here is a collection of connectionstring to use on different databases

http://devdaily.com/java/jdbc-connection-string-mysql-postgresql-sqlserver

Answer 2

If you want to connect from stand alone application swings then JDBC is best suited.

Answer 3

You will definitely want to familiarize yourself with concurrency issues in Swing, as well as constructs like the SwingWorker and SwingUtitlies.invokeLater (see link below). Making remote calls from within the EventDispatchThread will hang your GUI and provide a very bad experience to your users.

http://java.sun.com/javase/7/docs/api/javax/swing/SwingUtilities.html#invokeLater%28java.lang.Runnable%29

Answer 4

You generally do not want to expose your database directly to the internet. IMHO you should really think about putting a (web or application) server in between. If you want to or have to, however, you should put real effort into securing it: very strong passwords, very restrictive permissions on everything, using SSL with client certificates or possibly even setting up a VPN ... Then this becomes less a question of programming than configuration and serverfault is a good place to get more information.

I found two interesting related questions on Serverfault that deal with this issue (not MySQL though):

1. The wisdom of exposing a database server on the internet?
2. How to expose SQL 2008 database to the world through the Internet?

From an architecture point of view, even with a thick client I would prefer to have a web server in between that uses SOAP or REST to provide database functionality to the client app.