How to store data into MySQL database?

Question

I have created a java application which stores data into MySQL database.

For that I have done the following things:

1. I have installed MySQL database on my computer.
2. I have created a database on that MySQL server.
3. I have created a table in the database with the required schema.

Now I want to deliver this application to various clients but my clients are not technical persons and I don't want to give instructions to each of my client to do the above three steps.

1. How can I integrate some functionality into my app so that it can do atleast step 2 and step 3 automatically so that the client needs to install only MySQL database.

2. It would be much better if the code can install the MySQL database automatically from the setup file attached with the application.

3. How the applications available in the market manage information?

Answer 1

For 2 and 3 you just need two SQL statements to run during installation: CREATE DATABASE and CREATE TABLE.

As an alternative I would suggest you to use SQLite for which your clients wouldn't need to install any database servers.

Answer 2

For steps 2 and 3 better create an OS shell script (bat or bash) that will execute mysql cli tool to create database and schema from your file

mysql -u root -p superpwd < create_database.sql

the create_database.sql better to create with help of mysqldump cli tool from your own database Later you can include this script into your MySQL bundled installation.

Answer 3

I would think that you could do a data dump via phpAdmin which should script out the tables of the database along with insert statements for the actual data. I'm not a Java developer, but I think you should be able to use the functionality of the Java libraries that give you database access to turn back around and load your scripted out database as a file, read that file and then execute it against a database that you create via code.

Hope this helps for you.

For reference, here is how to create a mySql database via a command line .

Answer 4

Personally, I like how Confluence (for example) deals with that:

• The Confluence installation includes an embedded HSQLDB database, supplied for evaluation purposes. This is what you get when using the automatic installer on Windows.
• As documented, the embedded database is Not Suitable for Production Instances of Confluence so they suggest to use an external database for production and provide detailed Database Setup Guides (installation, schema and user creation) for MySQL, PostgreSQL, Oracle, DB2, SQL Server and generic instructions for others databases.
• The application will take care of creating the tables on startup if the schema is empty.
• For those who prefer to create the tables manually, they provide a database creation script.
• When upgrading to a higher version of Confluence, the Confluence application takes care of the schema update.