Accessing Database in Java with JDO
I haven’t written Java code to access relational database for a while. Over the years there have been lots of progresses, especially with various Object Relational Mapping (ORM) frameworks. With these frameworks, accessing relational databases becomes pretty easy. In some cases, you don’t have to know SQL at all. Of course understanding SQL is always an advantage.
In my recent project, I have to use relational database as object repository. Like other existing projects, the build process has been nicely automated and I don’t even have to know about the framework used, which is good when it’s under pressure to deliver something but not so good to understand a technology. To my curiosity, I decided to try a new project with database interaction from scratch.
Time to learn how to "Google" and manage your VMware and clouds in a fast and secureHTML5 App
One leading ORM framework is the Java Data Objects (JDO) specification started by Sun Microsystem in 2000. It’s now an Apache project. The JDO provides a consistent interface across different data sources, including relational databases, XML, LDAP, etc. An introduction can be found at here. Another competing spec from Sun/Oracle is the Java Persistence API (JPA). There is a good doc comparing these two. Note that it’s from JDO camp and could be biased.
To get me started, I tried to find an easy HelloWorld type of sample and found one from datanucleus.org. It has a nice high level introduction on, but has not provided ready to run code, especially these build scripts or POM. Luckily, I found a few samples on Github by Andy Jefferson and others. The full URL to the GIT repository is: https://github.com/datanucleus/samples-jdo.git. You can just clone the repository to your own IDE. In my case, I use Netbeans and opened one of the projects “DataNucleus AccessPlatform Tutorial for JDO”. There are other samples for more advanced use cases in the repository.
Building the project and running the Main.java are pretty straight-forward. It uses h2 in memory database out of box. To switch to other database, for example, MySQL, you need to tweak it a little bit.
First, create a database in MySQL. I just named it as doublecloud using SQL as follows. If you use MySQL Workbench, it’s even easier. Just look for other schemas in the left side pane and right click for “Create Schema …” context menu which pops a wizard to guide you through.
CREATE SCHEMA `doublecloud`;
Second, you need to un-comment the MySQL portion in the META-INF/persistence.xml file and comment out the h2 related portion. You should change the database server name, database name, user name, and password to reflect your environment.
<!-- ENABLE THESE FOR MySQL/MariaDB --> <property name="javax.jdo.option.ConnectionURL" value="jdbc:mysql://192.168.8.168:3306/doublecloud?useServerPrepStmts=false"/> <property name="javax.jdo.option.ConnectionDriverName" value="com.mysql.jdbc.Driver"/> <property name="javax.jdo.option.ConnectionUserName" value="root"/> <property name="javax.jdo.option.ConnectionPassword" value="secret"/> <property name="javax.jdo.option.Mapping" value="mysql"/>
Third, change the pom.xml and un-comment the MySQL driver and datanucleus RDBMS adapter. You can leave the h2 dependency there.
<!-- ENABLE THIS FOR MYSQL --> <dependency> <groupId>org.datanucleus</groupId> <artifactId>datanucleus-rdbms</artifactId> <version>[4.0.0-m1, )</version> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.17</version> </dependency>
After these changes, you can build the code and run the Main.java again. Then you should see 4 tables created in the doublecloud database: BOOKS, INVENTORIES, INVENTORY_PRODUCTS, PRODUCTS for the 3 Java objects, Book, Inventory, and Product. Book is a subclass of Product. The inventory is a simple container that holds a name as primary key, and a Set of Product objects.
Once the sample runs, it should serve a good starting point to develop your persistence code. You can add new, remove existing, Java classes, change the persistence file so that the framework knows what classes to enhance.
Using a framework like datanecleus really makes life easier. There is of course a performance penalty. I haven’t compare this with using JDBC directly, but I am sure JDBC will be faster with a prices that you write a lot more code and know enough SQL. It’s really a trade-off. In general, it should be good to start with a ORM framework and then optimize it when performance becomes a problem.