Informix and JPA - Those Java guys are at it again!

1. Informix and JPA - Those Java guys are at it again! Mary Lurie Session: D04 IBM Corporation Monday, 5/16/2011, 2:30pm

2. Abstract Informix is outstanding in multi-tier architectures. This session will examine how to use IDS with JPA - Java Persistence API. First we'll look at what JPA does and why Java programmers like JPA. We'll port an openJPA sample from Derby to Informix. We'll then look at what openJPA does to the database when objects are created in the Java program. As a follow on to last year's caching discussion we'll integrate a cache into openJPA and examine the impact on the application code. As always there will be a robust test harness so you can see smoke pour out of my laptop again.

3. Agenda • JPA - Java Persistence API. • Introduction • Sample code • HelloJPA – the “hello world” of JPA • HelloJPA – using Informix instead of Derby • OpenJPA – what is it doing on the Informix side? • Cranking up the volume – adding looping to Main.java in hellojpa • Next Steps 3

4. JPA - Java Persistence API http://en.wikipedia.org/wiki/Java_Persistence_API The Java Persistence API, sometimes referred to as JPA, is a Java programming language framework managing relational data in applications using Java Platform, Standard Edition and Java Platform, Enterprise Edition. … lines deleted ... Many enterprise Java developers use lightweight persistent objects provided by open-source frameworks or Data Access Objects instead of entity beans: entity beans and enterprise beans had a reputation of being too heavyweight and complicated, and one could only use them in Java EE application servers. Many of the features of the third-party persistence frameworks were incorporated into the Java Persistence API, and as of 2006 projects like Hibernate (version 3.2) and Open-Source Version TopLink Essentials have become implementations of the Java Persistence API. 4

5. Apache OpenJPA http://openjpa.apache.org 5 Informix and JPA, Those Java guys are at it again!

6. Download and Install Program to persist openjpa objects Definition of java object to store in database Config file to bind in eXtreme scale cache Ant file to configure database source and classpath 6

7. Java @Entity and DDL @Entity public class Message { @Id private long id = System.currentTimeMillis(); @Basic private String message; @Basic private Date created = new Date(); ========================================================== Column name Type Nulls id decimal(32,0) no created datetime year to fraction(3) yes message varchar(255) yes Do you think OpenJPA knows to create an index on ID? Yes/No ? 7

8. Create and Object and store it • // from the file Main.java • EntityManager em = factory.createEntityManager(); • // Begin a new local transaction so that we can persist a new entity • em.getTransaction().begin(); • // Create and persist a new Message entity • em.persist(new Message("Hello Persistence!")); • // Commit the transaction, which will cause the entity to • // be stored in the database • em.getTransaction().commit();

9. OpenJPA vs JDBC • EntityManager em = factory.createEntityManager(); • em.getTransaction().begin(); • em.persist(new Message("Hello Persistence!")); • em.getTransaction().commit(); • ==================== VS ==================== import java.sql.*; public class SQLStatement { public static void main(String args[]) { String url = "jdbc:mySubprotocol:myDataSource"; Connection con; String query = "select SUPPLIERS.SUP_NAME, COFFEES.COF_NAME " + "from COFFEES, SUPPLIERS " + "where SUPPLIERS.SUP_NAME like 'Acme, Inc.' and " + "SUPPLIERS.SUP_ID = COFFEES.SUP_ID"; Statement stmt; try { Class.forName("myDriver.ClassName"); } catch(java.lang.ClassNotFoundException e) { System.err.print("ClassNotFoundException: "); System.err.println(e.getMessage()); }

10. Now read it back • // Create a fresh, new EntityManager • EntityManager em2 = factory.createEntityManager(); • // Perform a simple query for all the Message entities • Query q = em2.createQuery("select m from Message m"); • // Go through each of the entities and print out each of their • // messages, as well as the date on which it was created • for (Message m : (List<Message>) q.getResultList()) { • System.out.println(m.getMessage() • + " (created on: " + m.getCreated() + ")"); }

11. Now read it back • // Message m123 = em.find(Message.class, 1302828985429 ); • long myquerykey=1303489111501L; • Message m123 = em2.find(Message.class, myquerykey ); • System.out.println("\n\n Singleton query output " + m123.getMessage() • + " (created on: " + m123.getCreated() + ") \n\n\n ");

12. Running the Hellojpa Example lurie@merlin:/work/dwOpenJPA/work/openjpa/apache-openjpa-2.0.1/examples/hellojpa$ ant Buildfile: build.xml pre-compile: compile: [javac] Compiling 2 source files run: [java] 4232 hellojpa TRACE [main] openjpa.jdbc.SQL - t 8204368, conn 15666395 executing stmnt 22424679 CREATE TABLE Message (id BIGINT NOT NULL, created TIMESTAMP, message VARCHAR(255), PRIMARY KEY (id)) 12

13. Running the Hellojpa Example [java] 4347 hellojpa TRACE [main] openjpa.jdbc.SQL - t 8204368, conn 15666395 [115 ms] spent [java] 4683 hellojpa TRACE [main] openjpa.jdbc.SQL - t 8204368, conn 15975876 executing prepstmnt 29412736 INSERT INTO Message (id, created, message) VALUES (?, ?, ?) [params=?, ?, ?] [java] 4695 hellojpa TRACE [main] openjpa.jdbc.SQL - t 8204368, conn 15975876 [12 ms] spent 13

14. Running the Hellojpa Example CONTINUTED: [java] 4969 hellojpa TRACE [main] openjpa.jdbc.SQL - t 8204368, conn 20336357 executing prepstmnt 32685187 SELECT t0.id, t0.created, t0.message FROM Message t0 [java] 4970 hellojpa TRACE [main] openjpa.jdbc.SQL - t 8204368, conn 20336357 [1 ms] spent [java] Hello Persistence! (created on: Mon Jan 03 13:18:48 EST 2011) BUILD SUCCESSFUL Total time: 10 seconds 14

15. Observations • Running the program is as simple as typing: ant • Table created by OpenJPA • Java programmer has no visibility to database • Derby database is embedded, no explicit management • We’re only reading one row. Only meaningful as functional test • At the TRACE level this thing is really verbose 15

16. Replace Derby with Informix • build.xml for Derby <project default="usagewarning">> <property name="parent" value="${basedir}/.."/> <property name="root" value="${parent}/.."/> <!-- database connection properties <property name="dbdriver" value="org.apache.derby.jdbc.EmbeddedDriver"/> <property name="dburl" value="jdbc:derby:${basedir}/${example}-database;create=true"/> <property name="dbuser" value=""/> <property name="dbpass" value=""/> --> 16

17. Replace Derby with Informix • build.xml for Informix <project default="usagewarning">> <property name="parent" value="${basedir}/.."/> <property name="root" value="${parent}/.."/> <!-- database connection properties --> <!-- informix instead of derby --> <property name="dbdriver" value="com.informix.jdbc.IfxDriver"/> <property name="dburl" value="jdbc:informix-sqli://merlin:54321/idsopenjpa:informixserver=ids115"/> <property name="dbuser" value="informix"/> <property name="dbpass" value=“pleaseUseYours"/> <target name="usagewarning"> 17

18. Replace Derby with Informix • build.xml for Informix - Where are the java classes? <path id="classpath" description="The classpath to use for compiling and running"> <pathelement path="${parent}"/> <fileset dir="${root}"> <include name="**/*.jar"/> </fileset> <pathelement path="xs"/> <fileset dir="/home/lurie/tmp/xs71/ObjectGrid/lib"> <include name="objectgrid.jar"/> </fileset> <pathelement path="ifx"/> <fileset dir="/opt/IBM/informix/jdbc/lib"> <include name="ifxjdbc.jar"/> </fileset> </path> 18

19. Replace Derby with Informix • Create an Informix Database – note that logging is required Don't create any tables, just a database. It seems almost too easy. informix@merlin:~$ dbaccess - - > create database idsopenjpa with buffered log; Database created. 19

20. Add eXtreme Scale Cache • In the file persistence.xml • <property name="openjpa.ConnectionPassword" • value="secret"/> • <property name="openjpa.DataCache" • value="com.ibm.websphere.objectgrid.openjpa.ObjectGridDataCache( • ObjectGridName=BasicTestObjectGrid,ObjectGridType=EMBEDDED, • maxNumberOfReplicas=4)"/> • <property name="openjpa.QueryCache" • value="com.ibm.websphere.objectgrid.openjpa.ObjectGridQueryCache()"/> • <property name="openjpa.RemoteCommitProvider" value="sjvm"/> • <properties>

21. Process more than 1 row  // *** loop here to put a bunch of rows in the database for ( int i=0; i<10000;i++) { // *** don't forget to put a closing } below // Begin a new local transaction so that we can persist a new entity em.getTransaction().begin(); // Create and persist a new Message entity em.persist(new Message("Hello Persistence!")); // Commit the transaction, which will cause the entity to // be stored in the database em.getTransaction().commit(); // *** end of loop } 21

22. Read twice – prime cache/read cache // *** report on the first run long end1sttm = System.currentTimeMillis(); System.out.println("\n\nfirst run took: " + (end1sttm-starttm) + " millisec\n\n\n"); // **** second run should be in local cache // Perform a simple query for all the Message entities Query q2 = em2.createQuery("select m from Message m"); // Go through each of the entities and print out each of their // messages, as well as the date on which it was created for (Message m : (List<Message> q2.getResultList()) { //System.out.println(m.getMessage() //+ " (created on: " + m.getCreated() + ")"); } long end2ndtm = System.currentTimeMillis(); System.out.println("\n\nsecond run, from cache took: " + (end2ndtm-end1sttm) + " millisec\n\n\n"); // Again, it is always good to clean up after ourselves em2.close(); 22

23. Performance Results [java] [1/26/11 17:28:38:751 EST] 72887288 SystemOut O [java] [java] first run took: 1096 millisec rows processed= 10000 [java] [java] [java] [java] [1/26/11 17:28:38:800 EST] 72887288 SystemOut O [java] [java] second run, from cache took: 49 millisec [java] 23

24. Live Demo DEMO 2424

25. Next Steps • Try this at home! • Developerworks article complete with sample code: http://www.ibm.com/developerworks/data/library/techarticle/dm-1103pojo/?ca=drs- 25

26. Questions ?!? DEMO 2626

27. Informix and JPA - Those Java guys are at it again! Marty Lurie lurie@us.ibm.com