Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Supplying a database with a Java .jar file?

I want to create a program which users run just by running a .jar file. I want this program to have access to a database, which is pre-filled with some data when the program is first downloaded, and as the user uses it, more data is added to the db.

The db should be locally stored on the user's computer.

What is my best option for this? Which database engine should I go with, and what do I have to do to make it so the user won't have to setup the db when he installs the app, it will come pre-filled with the .jar file?

like image 310
Ali Avatar asked Oct 18 '12 18:10

Ali


People also ask

Can Java be used for database?

Java uses something called JDBC (Java Database Connectivity) to connect to databases. There's a JDBC API, which is the programming part, and a JDBC Driver Manager, which your programmes use to connect to the database.

Which database we can use with Java?

Java DB. Java DB is Oracle's supported distribution of the open source Apache Derby database. Its ease of use, standards compliance, full feature set, and small footprint make it the ideal database for Java developers. Java DB is written in the Java programming language, providing "write once, run anywhere" portability ...

How do I add a JAR file to my server?

You can use setJars to tell Spark where to find jar files you want to deploy to the cluster before you build SparkSession. In my case I was using maven-shade-plugin to build oneJar in compile time, and then it was deployed to Spark Cluster from target directory.


1 Answers

A simple solution would be to use an embedded database stored locally on the disk (for example near the jar file). You can use h2, or even sqlite for this.

When loaded the program will check for the existence of the database, if it's not here, just play a setup SQL script that will create the database structure and initial data, otherwise you're good to go, just do the stuff that your application was created to do.

Here is a really simple example with h2 database:

Say you are packaging a SQL file named init.sql in the /resources/ folder of your JAR, that will create a table, something like:

create table foobar(bazz VARCHAR);
insert into foobar values('foo');
insert into foobar values('bar');
// and so on

Then somewhere in your code, where you need to access the data you will try to load the DB or create it if it does not exists yet.

Connection loadDatabase() throws Exception {
    Class.forName("org.h2.Driver");
    Connection con = null;
    try {
        // throws an exception if the database does not exists
        con = DriverManager.getConnection("jdbc:h2:./foo.db;ifexists=true");
    } catch (SQLException e) {
        // if the database does not exists it will be created
        conn = DriverManager.getConnection("jdbc:h2:./foo.db");
        // init the db
        initDatabase(con);
    }

    return con;
}

void initDatabase(Connection con) throws Exception {
    // load the init.sql script from JAR
    InputStreamReader isr = new InputStreamReader(
        getClass().getResourceAsStream("/resources/init.sql"));
    // run it on the database to create the whole structure, tables and so on
    RunScript.execute(con, isr);
    isr.close();
}

void doSomeStuffWithDb() throws Exception {
    Connection con = loadDatabase();
    Statement stmt = conn.createStatement();
    ResultSet rs = stmt.executeQuery("select * from foobar");
    // will print foo, then bar
    while (rs.next()) {
        System.out.println(rs.getString(1));
    }
    rs.close();
    stmt.close();
    conn.close();
}

After execution, you should have a file named foo.db next to your application where lies the created tables etc.

This is a really simple example, of course you can use any wrapper around JDBC to avoid use of ResultSet etc, like spring jdbcTemplate, even to load the connection instance etc.

like image 103
Alex Avatar answered Sep 25 '22 01:09

Alex