I'm currently in the process of learning Java and swing and in doing so am trying to create a desktop app.
As part of this app I have set up a mysql database which the app is connected to, however I'm uncertain as to how this would work if I was to distribute the app for other users, how would I create a database that they are able to use on their system if they don't have mySQL installed or the database initiated.
As pvg has mentioned, a good option to use is an embedded database. The reason here is that the database can be included as a single JAR file, rather than needing to be installed on the system's user. Usually, you can access the databases using the standard JDBC API, so it really can act as a replacement for your current MySQL DB. The library will manage creation of DB files so you don't have to install anything.
Of course, these options are typically less robust than something like Oracle, however, it may suit your needs just fine.
For example, I recently utilized the H2 database, a 100% Java embedded DB, in a project of mine. http://www.h2database.com/html/main.html
Here is some example code which shows how using something like H2 would differ than MySQL when you want to get a connection object, but note, interacting with the DB to run queries would be the same as with MySQL:
import org.h2.jdbcx.JdbcDataSource; import java.sql.*; /** * Sets and returns a DB connection, if not already done */ private static Connection getDBConnection() { if (mDBConnection != null) return mDBConnection; Connection conn = null; try { JdbcDataSource ds = new JdbcDataSource(); ds.setURL("jdbc:h2:C:/MyDBDirectory/"); conn = ds.getConnection(); } catch (SQLException e) { mLogger.severe("Error opening DB connection or creating tables: " + e.getMessage()); } mDBConnection = conn; return mDBConnection; } You should look into one of the many embeddable pure java SQL dbs which you can embed and distribute with your app. Your existing SQL should work with those either as is or with minor modifications. Take a look at:
and
If you're just learning, I recommend that you put the details of deployment to one side at first and just assume that the database will always be there and will always be in the correct format. Once you've got your application to a state where you're reasonably happy with it, then you can address your concerns regarding the database.
When you do get to addressing these concerns, a general approach would be for your application to have a database setup script, this will be a set of SQL statements for creating the tables, indexes, permissions etc. Assuming that you're using JDBC, your application settings should specify the JDBC connection details (for example, JDBC Url, username and password). Your application should connect to this source and then run the setup script.
One caveat with your setup script is that it should work regardless of the state of the database. Suppose you have lots of statements of the form
CREATE TABLE FOO (id int....) If the table FOO already exists, you'll get an SQLException which you'll need to catch and ignore (but only for the specific case where the table exists, you can't ignore other errors).
Suppose a later version of your application needs a slightly different definition of FOO. In that case, you can't just change the CREATE statement, you'll need to keep your original CREATE statement followed by some ALTER statements.
This approach assumes that it will be possible for your application to connect to the JDBC data source. If you are planning for your application to use a standalone DBMS such as MySQL, Postgres, Oracle, SQL Server etc. then I would consider installing the DBMS to be the responsibility of the user of your application. The user will also need to figure out what the JDBC URL should be and provide that to your application.
If you want to avoid the user having to install a DBMS then it's possible to embed a pure Java DBMS with your application. JavaDB (AKA Derby) is one example, HSQLDB and H2 can also be used in this way. To take Derby as an example, you would need to include the derby libraries (jars) with your application and your application would need to create the database when it starts up. If you're using Derby in "embedded mode" your URL would look something like:
jdbc:derby:/home/fred/myapplicationdb
As far as I remember, the Derby library will create the database if it doesn't exist.
For a desktop application, it's usually better to deliver everything the end user will need in a single package. End users tend to dislike dependency requirements, because it only takes one or two conflicting packages requiring different versions of the same thing to completely ruin your day.
For a single-user application, you're probably best off using an embedded database. In fact, the more recent releases (from 6 onwards) of the JDK include one (JavaDB, aka Apache Derby.)
You should also bear in mind that maintaining a database server on your computer isn't a trivial task. Ask yourself if you really think your end users will bother with your application if you've added that burden to it. A server will also consume system resources, meaning the end user will either feel their system is being unnecessarily bloated, or will be stuck with the task of stopping and starting it in order to use your application.
If you're unsure of the environment the end user is working with, a good compromise is to bundle an embeddable DB engine with your application, use that as the default, and allow the user an option to configure a connection to an external database if they prefer. Individual end users can then use, say, Derby/JavaDB, while the power users and networked multiuser customers can connect to a database server such as MySQL or Oracle. The downside here is you'll need to watch your usage of SQL to ensure it works on all supported database engines, or to use an abstraction library.
You might have to use Sqlite as a database , because it is a standalone you have to just put inside in your source code only. It eliminates all coasting of mysql server set up and stuff like that ! By virtue of using sqlite you can remove the overhead of installing mysql through various process. However sqlite accepts almost all create , insert ,delete and update sql queries, it is smart enough to export data and import as well. As far as database heat is concerned using sqlite is a better solution where relatively small data to be stored.In case of large amount of data storage you might have to opt mysql or even oracle odd so, but for relatively small storage of data you can use sqlite very effectually.
