3

My aim is to create a local database that can be read and written to with Java. I have some experience with manipulating a local sqlite database with Python, and with interacting with existing networked databases on Microsoft Azure via VB.Net, but the Java formulation for creating a database is escaping me.

Most sources (like the JDBC Docs) seem to assume that you are accessing a database through a network protocol, or a database hosted on localhost. My desired implementation is to create and store the database in a file (or collection of files), so that it can be stored and accessed locally, without network connectivity (presumable through the "file:" protocol).

The JDBC Tutorial looks like it will be very useful once I am up and running, but is currently beyond my scope, since I don't even have an existing database yet.

Many sources have suggested solutions like H2, MySQL, Derby, or Hypersonic DB. However, I'm loath to install extensions (if that's the right term) for a number of reasons:

  • This project is initially intended to help me learn my way around Java - widening the scope of the project will dilute my experience with the "base" language and, probably, increase the temptation to engage in "cargo cult programming"
  • If this project does ever get distributed to other users (admittedly unlikely, but still!), I don't want to force them into installing more than the core of Java.
  • I simply don't know how to install extensions (add-ons? modules?) in Java - one baby-step at a time!

For similar reasons, installing Microsoft SQL Server would not be productive.

This answer looks close to what I'm aiming for; however, it gives the error:

java.sql.SQLException: No suitable driver found for jdbc:mysql://localhost/?user=root&password=rootpassword

and trying "jdbc:file://targetFile.sql" gives a similar error.

I've seen the term "embedded" database, which I think is a subset of "local database" (i.e. a local database is stored on the same system - an embedded database is a local database that is only used by a single application) - if I've got those definitions wrong, please feel free to correct me!

Community
  • 1
  • 1
scubbo
  • 4,969
  • 7
  • 40
  • 71
  • H2 is very powerful and it's a simple jar – Aubin Dec 03 '12 at 22:04
  • What do you mean by "install extensions"? What precisely do you want if not a database? There is no database in core Java; core Java is just core Java. When you distribute your project you can distribute whatever you want with it--including a pure Java DB like H2 or Derby, and they won't have to do anything. You *do* want an embedded DB, although I would provide your users the opportunity to use an arbitrary DB, which is what JDBC allows you to do. – Dave Newton Dec 03 '12 at 22:05
  • I would recommend a in-memory DB like H2 that is able to cache-to-disk when the JVM exits: http://stackoverflow.com/questions/1932254/in-memory-java-db – djangofan Dec 03 '12 at 22:19
  • @DaveNewton - what I meant by "install extensions" (which I acknowledge is probably the wrong terminology) is that I wouldn't want anyone else using the system to be required to download or install anything else. I didn't realise it was possible to distribute additional code along with your own - thanks! – scubbo Dec 03 '12 at 23:23

4 Answers4

6

Most likely, the reason for which you are getting the error, is due to the fact that you are not registering the driver (using reflection...) before actually using it for establishing a connection and so on.

Presumably you will want to do something along the lines of Class.forName("driver")

and then cast that if necessary and then registering it in the DriverManager before calling the getConnection() method.

Here is a very useful link that might help you out in solving the issue:

http://www.kfu.com/~nsayer/Java/dyn-jdbc.html

However, if you really want to use a local database/file you might want to have a look at SQLite, that might be one way to go about it, although I recommend going for the MySQL approach, as it is a lot easier to configure and learn how stuff works with JDBC.

If you are still considering SQLite check this out:

Java and SQLite

I see you need some guidance in importing external .jar files into your code (i.e. 3rd party libraries like the ones you will be using for a JDBC driver). Are you using an IDE (e.g. Eclipse, Netbeans, etc.) or are you writing in a text editor and compiling manually?

Community
  • 1
  • 1
Roveris
  • 108
  • 1
  • 7
  • I'm using Eclipse - so far, I've downloaded the H2 files and am following the quickstart guide to get it set up. – scubbo Dec 03 '12 at 23:53
  • I've added h2-1.3.170.jar to the CLASSPATH environment variable, and I copy-pasted the following code from the tutorial try { Class.forName("org.h2.Driver"); Connection conn = DriverManager. getConnection("jdbc:h2:~/test", "sa", ""); // add application code here conn.close(); } catch (Exception e) { e.printStackTrace(); } It gave me the error `java.lang.ClassNotFoundException: org.h2.Driver` – scubbo Dec 04 '12 at 00:00
  • Hmm, ok. Is the error at the line with Class.forName("org.h2.Driver")? Try clean and build once more (sometimes Eclipse gets cloggy, and messes up the build paths). I'm not sure how it actually works with the classpath env. variable in Eclipse, but what I would do is download the .jar file, select the project->properties->java build path->libraries and then add the external jar and that will properly add it to your classpath. – Roveris Dec 04 '12 at 00:07
  • 1
    It worked! Fantastic, thanks! I'd added the .jar to the Windows Environment Variable, not Eclipse's. Thank you! – scubbo Dec 04 '12 at 00:09
  • Cool! Yeah, that's one way to go about it as well, haha! – Roveris Dec 04 '12 at 00:10
3

A number of embedded pure Java databases appeared recently, which have a really simple interface, usually just java.util.Map, don't involve using JDBC or other SQL artifacts, and store their data in a single file or directory:

The main downside is that most of such databases provide only the simples key-value model.

leventov
  • 14,760
  • 11
  • 69
  • 98
0

DBC can be used with any database that has a JDBC driver, which isn't necessarily a database in "network mode", it can be used with embedded databases as well.

Here are some Java and embeddable databases:

http://www.h2database.com/html/main.html

http://db.apache.org/derby/

http://hsqldb.org/

Bobb Dizzles
  • 533
  • 3
  • 12
0

Java's JDK does not include any implementation of a database nor drivers to access it. It only provides JDBC as an abstraction to connect to a "database". Is up to you to include all the needed libraries in your code.

If you want to have a self contained code you can simply include the .jar file of a embeddable database in you classpath. That way you can create the instance of the database in your code and minimize the external dependencies.

You can find here a list of java embeddable databases

You can find here an example of how to embed HSQLDB in your code.

barracel
  • 1,831
  • 13
  • 24