How to replace Derby with MySQL Ant properties & CLASSPATH reference

[Jack Bush]

Hi All,

I would like to run a JPA tutorial using MySQL instead which has been built with Derby pre-configured and is working fine using Ant built script. Below are some configuration files used by this exercise:

C:\ProJPA2\examples\Chapter2\employeeService\build.xml
<?xml version="1.0" encoding="iso-8859-1"?>

<project name="example" default="run">
 
    <property file="../../../config/common.properties"/>
    <import file="../../../config/common.xml" />

    <target name="run" depends="init,build,run_sql,run_client,clean" />
    <target name="interactive_client" depends="init,build,run_sql,run_interactive_client,clean" />

    <target name="init">
        <property name="example_name" value="employeeService_ch2" />
        <property name="DB_NAME" value="EmpServDB" />
    </target>

    <target name="build" depends="setup,build_jar">
         <javac srcdir="src/client" destdir="build/client" debug="on">
            <classpath>
               <pathelement path="${J2EE_CLASSPATH}" />
               <pathelement location="dist/lib/${example_name}.jar" />
            </classpath>
        </javac>
        <jar jarfile="dist/${example_name}_client.jar" update="yes">
            <fileset dir="build/client"/>
        </jar>
    </target>
    
    <target name="run_client">
	<java classname="examples.client.EmployeeTest" fork="true">
            <sysproperty key="derby.system.home" value="${DERBY_SYSTEM_HOME}"/>
            <classpath>
                <pathelement location="dist/lib/${example_name}.jar" />
                <pathelement location="dist/${example_name}_client.jar" />
                <pathelement path="${CLIENT_CLASSPATH}"/>
            </classpath>
        </java>
    </target>

    <target name="run_interactive_client" depends="setup">
	<java classname="examples.client.EmployeeTestInteractive" fork="true">
            <sysproperty key="derby.system.home" value="${DERBY_SYSTEM_HOME}"/>
            <sysproperty key="ddl-generation" value="dropandcreate"/>
            <classpath>
                <pathelement location="dist/lib/${example_name}.jar" />
                <pathelement location="dist/${example_name}_client.jar" />
                <pathelement path="${CLIENT_CLASSPATH}"/>
            </classpath>
        </java>
    </target>
</project>

C:\ProJPA2\Config\common.properties

##################################################
# Installation properties 
##################################################

EXAMPLES_HOME="C:/ProJPA2"
SERVER_HOME=C:/glassfish3

##################################################
# Glassfish properties
##################################################

ADMIN_USER=admin

PASSWORD_PREFIX=
PASSWORD_FILE=

# Uncomment out the following 2 lines if GlassFish was installed with an 
# admin password, and enter the password in the password.txt file.
#PASSWORD_PREFIX=--passwordfile
#PASSWORD_FILE="${EXAMPLES_HOME}/config/password.txt"

##################################################
# Derby Database properties 
##################################################

DERBY_INSTALL=${SERVER_HOME}/javadb
DERBY_PORT=1527
DERBY_HOST=localhost
DERBY_USER=APP
DERBY_PWD=APP
DERBY_SYSTEM_HOME=${EXAMPLES_HOME}/database
DERBY_CLASSPATH=${DERBY_INSTALL}/lib/derby.jar;${DERBY_INSTALL}/lib/derbynet.jar;${DERBY_INSTALL}/lib/derbyclient.jar;${DERBY_INSTALL}/lib/derbytools.jar

##################################################
# Additional library properties 
##################################################

ECLIPSELINK_CLASSPATH=${SERVER_HOME}/glassfish/modules/javax.persistence.jar;${SERVER_HOME}/glassfish/modules/org.eclipse.persistence.oracle.jar;${SERVER_HOME}/glassfish/modules/org.eclipse.persistence.core.jar;${SERVER_HOME}/glassfish/modules/org.eclipse.persistence.jpa.jar;${SERVER_HOME}/glassfish/modules/org.eclipse.persistence.asm.jar;${SERVER_HOME}/glassfish/modules/org.eclipse.persistence.antlr.jar;${SERVER_HOME}/glassfish/modules/org.eclipse.persistence.jpa.modelgen.jar;
SPRING_CLASSPATH=${EXAMPLES_HOME}/lib/spring.jar;${EXAMPLES_HOME}/lib/commons-logging.jar
J2EE_CLASSPATH=${SERVER_HOME}/glassfish/lib/javaee.jar
JPA_CLASSPATH=${SERVER_HOME}/glassfish/modules/javax.persistence.jar
#CLIENT_CLASSPATH=${ECLIPSELINK_CLASSPATH};${J2EE_CLASSPATH};${DERBY_CLASSPATH}
CLIENT_CLASSPATH=${ECLIPSELINK_CLASSPATH};${J2EE_CLASSPATH};E:/Oracle/glassfish-persistence-installer-v2.1-b60.jar;E:/Oracle/mysql-connector-java-5.1.6-bin.jar;

C:\ProJPA2\examples\Chapter2\employeeService\run.bat
@echo OFF
call %ANT_HOME%/bin/ant %1

C:\ProJPA2\examples\Chapter2\employeeService\etc\persistence\META-INF\persistence.xml

                                     Changed the content from

<persistence xmlns="http://java.sun.com/xml/ns/persistence" version="1.0">
    <persistence-unit name="EmployeeService" transaction-type="RESOURCE_LOCAL">
        <class>examples.model.Employee</class>
        <validation-mode>NONE</validation-mode>
		<properties>
            <property name="javax.persistence.jdbc.driver" value="org.apache.derby.jdbc.ClientDriver"/>
            <property name="javax.persistence.jdbc.url" value="jdbc:derby://localhost:1527/EmpServDB;create=true"/>
            <property name="javax.persistence.jdbc.user" value="APP"/>
            <property name="javax.persistence.jdbc.password" value="APP"/>
            <!-- enable this property to see SQL and other logging -->
            <!-- property name="eclipselink.logging.level" value="FINE"/ -->
        </properties>
    </persistence-unit>
</persistence>

                                                  to

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.0" xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd">
  <persistence-unit name="EmployeeService" transaction-type="RESOURCE_LOCAL">
    <provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>
    <shared-cache-mode>ALL</shared-cache-mode>
    <validation-mode>NONE</validation-mode>
    <properties>
      <property name="javax.persistence.jdbc.driver" value="com.mysql.jdbc.Driver"/>
      <property name="javax.persistence.jdbc.url" value="jdbc:mysql://localhost:3306/EmployeeDB"/>
      <property name="javax.persistence.jdbc.password" value="abcdefg"/>
      <property name="javax.persistence.jdbc.user" value="applicationuser"/>
      <property name="eclipselink.ddl-generation" value="drop-and-create-tables"/>
      <!--  enable this property to see SQL and other logging  -->
      <property name="eclipselink.logging.level" value="FINE"/>
    </properties>
  </persistence-unit>
</persistence>

Also tried to include the latest Eclipse JPA (E:/Oracle/glassfish-persistence-installer-v2.1-b60.jar) & MySQL JDBC driver (E:/Oracle/mysql-connector-java-5.1.6-bin.jar) in CLIENT_CLASSPATH of common.properties file. This same example built from scratch in Netbeans 6.9.1 including these jar files in the CLASSPATH has worked beautifully.

Some system environmental variables used by Java & Ant are:
ANT_HOME=C:\Ant\apache-ant-1.8.2
CLASSPATH=.;C:\Program Files\Java\jre6\lib\ext\QTJava.zip
JAVA_HOME=C:\Program Files\Java\jdk1.6.0_20
Path=C:\Program Files\Java\jdk1.6.0_20\bin;C:\Ant\apache-ant-1.8.2\bin;C:\Program Files\MySQL\MySQL Server 5.1\bin;C:\glassfish3\bin;C:\glassfish3\glassfish\bin

However, the following exception kept occurring and I am can do with some advice on how to correctly update these configuration files in order to persist the data into MySQL:

C:\ProJPA2\examples\Chapter2\employeeService>dir
 Volume in drive C has no label.
 Volume Serial Number is 6473-5938

 Directory of C:\ProJPA2\examples\Chapter2\employeeService

10/02/2011  05:31 PM    <DIR>          .
10/02/2011  05:31 PM    <DIR>          ..
30/01/2010  11:03 PM             2,022 build - original.bak
30/01/2010  11:03 PM             2,022 build.xml
01/02/2011  04:33 PM    <DIR>          etc
09/02/2011  05:49 PM    <DIR>          lib
09/02/2011  05:21 PM                42 run.bat
07/01/2010  06:02 PM                52 runInteractive.bat
01/02/2011  04:33 PM    <DIR>          src
               4 File(s)          4,138 bytes
               5 Dir(s)  13,030,256,640 bytes free

C:\ProJPA2\examples\Chapter2\employeeService>run.bat
Buildfile: C:\ProJPA2\examples\Chapter2\employeeService\build.xml

init:

setup:
    [mkdir] Created dir: C:\ProJPA2\examples\Chapter2\employeeService\build\model
    [mkdir] Created dir: C:\ProJPA2\examples\Chapter2\employeeService\build\client
    [mkdir] Created dir: C:\ProJPA2\examples\Chapter2\employeeService\build\test
    [mkdir] Created dir: C:\ProJPA2\examples\Chapter2\employeeService\build\servlet\WEB-INF\classes
    [mkdir] Created dir: C:\ProJPA2\examples\Chapter2\employeeService\dist
    [mkdir] Created dir: C:\ProJPA2\examples\Chapter2\employeeService\dist\lib

build_jar:
     [copy] Copying 2 files to C:\ProJPA2\examples\Chapter2\employeeService\build\model
    [javac] C:\ProJPA2\config\common.xml:38: warning: 'includeantruntime' was not set, defaulting to build.sysclasspath=last; set to false for repeatable builds
    [javac] Compiling 2 source files to C:\ProJPA2\examples\Chapter2\employeeService\build\model
    [javac] Note: C:\ProJPA2\examples\Chapter2\employeeService\src\model\examples\model\EmployeeService.java uses unchecked or unsafe operations.
    [javac] Note: Recompile with -Xlint:unchecked for details.
      [jar] Building jar: C:\ProJPA2\examples\Chapter2\employeeService\dist\lib\employeeService_ch2.jar

build:
    [javac] C:\ProJPA2\examples\Chapter2\employeeService\build.xml:17: warning: 'includeantruntime' was not set, defaulting to build.sysclasspath=last; set to false for repeatable builds
    [javac] Compiling 2 source files to C:\ProJPA2\examples\Chapter2\employeeService\build\client
      [jar] Building jar: C:\ProJPA2\examples\Chapter2\employeeService\dist\employeeService_ch2_client.jar

run_sql:

BUILD FAILED
C:\ProJPA2\config\common.xml:144: java.sql.SQLNonTransientConnectionException: java.net.ConnectException : Error connecting to server localhost on port 152
7 with message Connection refused: connect.
        at org.apache.derby.client.am.SQLExceptionFactory40.getSQLException(Unknown Source)
        at org.apache.derby.client.am.SqlException.getSQLException(Unknown Source)
        at org.apache.derby.jdbc.ClientDriver.connect(Unknown Source)
        at org.apache.tools.ant.taskdefs.JDBCTask.getConnection(JDBCTask.java:370)
        at org.apache.tools.ant.taskdefs.SQLExec.getConnection(SQLExec.java:942)
        at org.apache.tools.ant.taskdefs.SQLExec.execute(SQLExec.java:614)
        at org.apache.tools.ant.UnknownElement.execute(UnknownElement.java:291)
        at sun.reflect.GeneratedMethodAccessor4.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:597)
        at org.apache.tools.ant.dispatch.DispatchUtils.execute(DispatchUtils.java:106)
        at org.apache.tools.ant.Task.perform(Task.java:348)
        at org.apache.tools.ant.Target.execute(Target.java:390)
        at org.apache.tools.ant.Target.performTasks(Target.java:411)
        at org.apache.tools.ant.Project.executeSortedTargets(Project.java:1399)
        at org.apache.tools.ant.Project.executeTarget(Project.java:1368)
        at org.apache.tools.ant.helper.DefaultExecutor.executeTargets(DefaultExecutor.java:41)
        at org.apache.tools.ant.Project.executeTargets(Project.java:1251)
        at org.apache.tools.ant.Main.runBuild(Main.java:809)
        at org.apache.tools.ant.Main.startAnt(Main.java:217)
        at org.apache.tools.ant.launch.Launcher.run(Launcher.java:280)
        at org.apache.tools.ant.launch.Launcher.main(Launcher.java:109)
Caused by: org.apache.derby.client.am.DisconnectException: java.net.ConnectException : Error connecting to server localhost on port 1527 with message Connection refused: connect.
        at org.apache.derby.client.net.NetAgent.<init>(Unknown Source)
        at org.apache.derby.client.net.NetConnection.newAgent_(Unknown Source)
        at org.apache.derby.client.am.Connection.<init>(Unknown Source)
        at org.apache.derby.client.net.NetConnection.<init>(Unknown Source)
        at org.apache.derby.client.net.NetConnection40.<init>(Unknown Source)
        at org.apache.derby.client.net.ClientJDBCObjectFactoryImpl40.newNetConnection(Unknown Source)
        ... 20 more
Caused by: java.net.ConnectException: Connection refused: connect
        at java.net.PlainSocketImpl.socketConnect(Native Method)
        at java.net.PlainSocketImpl.doConnect(PlainSocketImpl.java:333)
        at java.net.PlainSocketImpl.connectToAddress(PlainSocketImpl.java:195)
        at java.net.PlainSocketImpl.connect(PlainSocketImpl.java:182)
        at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:366)
        at java.net.Socket.connect(Socket.java:529)
        at java.net.Socket.connect(Socket.java:478)
        at java.net.Socket.<init>(Socket.java:375)
        at java.net.Socket.<init>(Socket.java:189)
        at javax.net.DefaultSocketFactory.createSocket(SocketFactory.java:206)
        at org.apache.derby.client.net.OpenSocketAction.run(Unknown Source)
        at java.security.AccessController.doPrivileged(Native Method)
        ... 26 more

The issue is how to replace Derby properties & driver with MySQL equivalents.

I am running JDK1.6.0_20, GF3.1, Ant 1.8.2 on Windows XP.

Your suggestion would be much appreciated.

Thanks,

Jack

[Jack Bush]

Forgotten to include C:\ProJPA2\examples\Chapter2\employeeService\etc\sql\db.sql which consists of:

DROP TABLE EMPLOYEE;
CREATE TABLE EMPLOYEE (ID INTEGER NOT NULL, NAME VARCHAR(255), SALARY BIGINT, NUMBER INTEGER, PRIMARY KEY (ID));

Also C:\ProJPA2\examples\Chapter2\employeeService\lib is made up of the following files:

eclipselink.jar,
glassfish-persistence-installer-v2.1-b60.jar,
mysql-connector-java-5.1.6-bin.jar.

Thanks,

Jack