creating new database manually for a software every time while distributing it to a new user?

Question

I've made a desktop based shop-management software using java that will save every product detail and sell history of a particular shop in a database over internet (phpmyadmin). I've a made a connector class and give the path of the database name which connects to the database. The problem is, for every distinct shop I need to create a different database. If i make a .exe file or installation file of my software and distribute it to users, how am i going to create different databases for each user (in this case each shop) through installation process. Is there any way of doing this dynamically or I have to create the database every time before distributing it to a new user (for a new shop actually) ?

Thanks in advance, I never make a software to distribute commercially. This is new to me.

Note 1: let me explain more about my problem, first take a look of my connector class

public class ispConnect {
static Connection connection;

public static Connection connecterDb(){
    try{
        Class.forName("com.mysql.jdbc.Driver");
        connection = DriverManager.getConnection("jdbc:mysql://ServeName/DatabaseName","root","");
        System.out.println("Connection Established!");
        return connection;
    }catch(Exception e){
            System.out.print("Connection Failed!");
            return null;
    }
}

this the connector class which connects with the database "DatabaseName". Now if I make a installer file I can't change the "DatabaseName". Let assume I have two customer X, Y the both need my application. for both of them i need to create two database name- let DatabaseName1 for X and DatabaseName2 for Y other-wise both of their data will save into the same database - "DatabaseName" . Now my question is do i have to change database name manually for each customer every time and then make a installer again before delivering my application to them or there is other option to do it !

Answer 1

Yes, it is possible to create a database dynamically. SQL language allows that, and you can invoke SQL expressions using java.

In SQL to create a database MyDatabase you need to invoke:

CREATE DATABASE MyDatabase

In java you can do it with this code:

Connection conn = DriverManager.getConnection("jdbc:mysql://databasehost/?user=root&password=rootpassword"); 
Statement statement = conn.createStatement();
int result=statement.executeUpdate("CREATE DATABASE MyDatabase");

Note that SQL is powerful language, you can also add/remove users, grant/revoke access permissions and so on. And you can invoke SQL expressions using Java code.

Check this link for MySQL SQL reference: http://dev.mysql.com/doc/refman/5.6/en/sql-syntax.html

---

Update №1:

Please note that databasehost, root username and password should not be hardcoded. Your application should ask this information upon first start, to generate the database.

---

Update №2:

In case you are providing MySQL server infrastructure to your customers for your application, and there's no requirement for customers to deploy and use separate independent MySQL instance on their computers, then you need a bit different approach.

Upon selling a license for your application, generate (using PHP for instance) MySQL database automatically using information provided by your customer. After that provide your customer with username/password and database name to access your server.

When your application starts, it asks for these database name, username/password, customer specifies this information at startup and starts working with your product.

As an option to provide more convenience, as you have generated server db, username and password, save this information in a (encrypted) settings file, and provide it along with the application, the customer is going to download.

---

Update №3

Don't hardcode database name, login and password into your java code. Declare variables:

String dbName;
String username;
String password;

And use these variables to connect to your db, like that:

connection = DriverManager.getConnection("jdbc:mysql://ServeName/" + dbName, username, password);

Store dbName, username and password in a separate text file, and let your java code to read this text file, to populate dbName, username and password variables.

You can generate this text file (also known as Java Properties file) when a user is going to download purchased software. You, on the server side, using PHP, should generate database, username and password and write this information (dbName, username and password) to the text file.

You can create and populate a text file using PHP. Here is the tutorial: http://www.w3schools.com/php/php_file_create.asp

And when text file is created, you can zip it along with your exe-file, also using PHP, here is the short howto: Creating .zip file

Hope this helps :)

Answer 2

You could bundle in the scripts which create the required database structure, thus, in short, when your application will attempt to connect to the database, and the database is not found, it would run a predefined script which would create all the necessary structures.

You could expand the above functionality to create the database whenever the application is unable to find the database, however, if you where to opt for this option I'd recommend showing a popup to the user first, informing them of what has happened and what will you do.