I want to develop a website that works with MySQL, but at the same time I want that platform to be used from a desktop software. I'm doing it with java, and I'm new to that, I want to know how I could use a fully local database with java, which is MySQL, so I can use it as a mirror database, the one that uses the website.
The truth is that at the time of development, the database of a web app and a desktop app does not differ.
IMPORTANT: before connecting, you must create a BBDD in MySQL with its corresponding user and administrator password.
If you do not want to complicate your life use MySQL WorkBench or any other graphical interface
Register the driver on your system ( EYE: the jar has to be in the classpath !!! ):
try {
Class.forName("com.mysql.jdbc.Driver");
System.out.println("Driver registrado con existo!");
} catch (ClassNotFoundException e) {
throw new IllegalStateException("No se ha encontrado el driver en el classpath!", e);
}
Configure the connection to DB:
With MysqlDataSource (preferred form)
MysqlDataSource dataSource = new MysqlDataSource();
// usuario y pass
dataSource.setUser("usuario");
dataSource.setPassword("password");
// servidor y puerto (he puesto el 3306 que es el std, pero cambialo sino!)
dataSource.setServerName("localhost");
dataSource.setPort(3306);
// base de datos creada
dataSource.setDatabaseName("nombreBBDD");
// crear conexion a partir de los datos
Connection conn = dataSource.getConnection();
Another older way is using DriverManager :
String URL = "jdbc:mysql:3306//localhost/nombreBBDD";
String USER = "username";
String PASS = "password"
Connection conn = DriverManager.getConnection(URL, USER, PASS);
Once this is done, you can make a query like this:
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM TU_TABLA");
// haz cosas con el resultado!
rs.close();
stmt.close();
conn.close();
What you need is a distributed database, the changes you make in the master DB will be replicated in the slave DB, and the modifications can be made in several ways, this is a BD problem.
I recommend that read the following article , where They explain this topic very well and help you to make an example.
If the users are few and the db in local can work in read mode, you can configure a local replica of the master db using the web server.
Published on 09-26-2016:
What you say can be done, but I do not know any out-of-the-box solution. I did it myself with a "dirty" table with two columns. Join the table that contained the dirty and its id. In this way, I could find the locally modified ones easily and quickly, and more importantly, if there are local changes to be synchronized.