SQLite & Its Drivers

Question

I'm new to the SQLite database, and more generally, to the concept of embedded databases altogether. I'm used to creating a connection string and connecting to a remote DB server (MySQL, MSSQL Srv, Oracle, etc.). I know this question is probably quite silly, but being in uncharted waters here, I can't seem to find the answer to this on my own.

So I'm writing a Java app that uses SQLiteJDBC as the Java driver for SQLite (the app's embedded db) and am creating the tables and inserting records into them from the Java app itself. What I'd like to do is download/install SQLite on my system - completely independent of the Java app - and then write SQL scripts that will do the "skeletonizing" (creating & insertions) of the database file itself, then copy that .sqlite file into my project directory where the app can then use it.

I'm just finding it incredibly difficult to develop database schema from inside the Java app itself; just seems like an unnecessary step.

So, my question:

Is this even possible? To create, say, myProgramDB.sqlite off the command line with the SQLite tool, and then (essentially) cut-n'-paste that file into my Eclipse/NetBeans project (of course, in the right directory!) and have it work? This is also assuming I have correctly imported the SQLiteJDBC JAR into my project through the IDE. I just want to create the DB somewhere else, then copy it into my project, instead of developing the DB through my app directly.

Thanks for any insight!

Answer 1

Just think of the database as a normal file which your app refers to either by an absolute or relative file path, so with that in mind embed it in your project like you would any other file in Eclipse (or point to a specific location where you expect it to be).

If you're going to create your db manually, SQLiteStudio (http://sqlitestudio.one.pl/) is free tool which will help you build the schema.

It also lets you export the structure and/or data as sql statements, which you can then use to build a copy of your database elsewhere.

Answer 2

Is this even possible? To create, say, myProgramDB.sqlite off the command line with the SQLite tool, and then (essentially) cut-n'-paste that file into my Eclipse/NetBeans project (of course, in the right directory!) and have it work?

Yes of course, you can do it. Haven't you got somewhere in your code a getConnection method call? It's used to connect to the desired database. In your case should be something like:

DriverManager.getConnection("jdbc:sqlite:" + databaseName);

I just want to create the DB somewhere else, then copy it into my project, instead of developing the DB through my app directly.

That's reasonable. The only thing that you might consider is this: if your application depends on the database "skeleton" as you said, then a database file (talking about SQLite) must always be available in order to proper run your program. Embedding inside your application, the basic instructions to create the database tables required, could permit to the application to rebuild a minimal database if the file is accidentally lost.

• IAmYourFaja: Thanks @0verbose - that reminds me: I'm at the downloads page for SQLite and have 3 binaries (for Windows users) to choose from: a Command Line Shell, a DLL and an analyzer tool. I just downloaded the Shell and tried using it to create a database from the command line, and its not doing anything. Am I being dumb? Does the shell require the DLL to operate?
• Heisenbug: @Mara: honestly I've always managed my SQLite tables from inside my Java apps. So I can't tell you anything about command line.

Answer 3

There are a number of GUI schema-creation and browsing clients for SQLite.

CAVEAT: There are some differences in the way various implementations of SQLite differentiate (or don't differentiate) between INTEGER datatype and the other ways of expressing integer, such as INT, INT32, BIGINT, etc., especially when then column is a primary key.

If creating a SQLite schema outside of the implementation where you plan to use it, use "INTEGER" (verbatim) when assigning integer data type affinity to a column; do not use any of the other variants of int.