Solved: JPA with MySQL-Data-Source

Former Member · ‎03-20-2007

Hello Forum,

I have a question regarding usage of a MySQL-Data-Source in combination with JPA

on the SAP NetWeaver Application Server, Java ™ EE 5 Edition.

I have setup a custom datasource like explained in paper:

"Working with Database Tables, DataSources and JMS Resources"

- registered the database driver via telnet (Using mysql-connector-java-5.0.3-bin.jar)

- created the data-sources.xml file underneath the META-INF dir of the EAR project

[code]

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE data-sources SYSTEM "data-sources.dtd" >

<data-sources>

<data-source>

<data-source-name>titan_cruises_ds</data-source-name>

<driver-name>mysql-connector-java-5.0.3-bin.jar</driver-name>

<init-connections>1</init-connections>

<max-connections>10</max-connections>

<max-time-to-wait-connection>60</max-time-to-wait-connection>

<expiration-control>

<connection-lifetime>600</connection-lifetime>

<run-cleanup-thread>60</run-cleanup-thread>

</expiration-control>

<sql-engine>native_sql</sql-engine>

<jdbc-1.x>

<driver-class-name>com.mysql.jdbc.Driver</driver-class-name>

<url>jdbc:mysql://ourHost.internal.com:3306/practise_titan_cruises</url>

<user-name>myUser</user-name>

<password>myPass</password>

</jdbc-1.x>

</data-source>

</data-sources>

[/code]

After that I manually created the persistence.xml underneath the META-INF dir of the EJB project.

[code]

<persistence-unit name="titan_cruises_pu">

<jta-data-source>titan_cruises_ds</jta-data-source>

</persistence-unit>

</persistence>

[/code]

After that I created the Entity named "Cabin" and the corresponding table within the db.

Entity code:

[code]

package de.collogia.beans.pojo.ship;

import java.io.IOException;

import java.io.Serializable;

import javax.persistence.Column;

import javax.persistence.Entity;

import javax.persistence.GeneratedValue;

import javax.persistence.Id;

import javax.persistence.Table;

/**

This persisted POJO class models the cabin data.

<p>

In this class persistence annotations are placed on the getter methods

of the attributes. This tells the persistence manager to access them

via the corresponding get- and set-Methods.</p>

(Unfortunately this does not work on NetWeaver and I had to place them

on field level aggain...)

*

@author Henning Malzahn (malzahn@edvbm.de)

*

svn-revision: $Rev:: 670 $:

svn-lasted-edited-by: $Author:: henning $:

svn-last-changed: $Date:: 2007-02-21 21:49:51 +0100 (Wed, 21 Feb 2007) $:

*/

@Entity

@Table(name = "cabin")

public class Cabin implements Serializable {

/** The generated serial version UID used for serialization. */

private static final long serialVersionUID = -8522497314498903378L;

/** The actual version number of this class used for serialization. */

private static int actualVersion = 1;

/** The cabin's id. */

@Id

@GeneratedValue

@Column(name = "id")

private long id;

/** The cabin's name */

@Column(name = "name")

private String name;

/** The cabin's deck level */

@Column(name = "deck_level")

private int deckLevel;

/** The cabin's ship id */

@Column(name = "ship_id")

private int shipId;

/** The cabin's bed count */

@Column(name="bed_count")

private int bedCount;

/----

Serialization/ Deserialization methods -

/

/**

Method that is responsible for deserialization of the object.

@param in The <code>ObjectInputStream</code> object to read

the data from.

@throws IOException That may occur when reading from the

<code>ObjectInputStream</code> object

@throws ClassNotFoundException That may occur when invoking the default

deserialization mechanism.

*/

private void readObject(final java.io.ObjectInputStream in)

throws IOException, ClassNotFoundException {

/* Invoke default deserialization mechanism. */

in.defaultReadObject();

/* Read the actual version number of the class. */

actualVersion = in.readInt();

} // End of readObject()

/----

/

/**

Method that is responsible for serialization of the object.

@param out The <code>ObjectOutputStream</code> object to write

the data to.

@throws IOException That may occur when writing to the

<code>ObjectOutputStream</code> object.

*/

private void writeObject(final java.io.ObjectOutputStream out)

throws IOException {

/* Invoke default serialization mechanism. */

out.defaultWriteObject();

/* Write the actual version number of the class. */

out.writeInt(actualVersion);

} // End of writeObject()

/----

Defining constructors -

/

/**

Private default constructor.

*/

private Cabin() {

} // End of default constructor

/----

/

/**

Full constructor.

@param name The cabin's name.

@param deckLevel The cabin's deck level.

@param shipId The cabin's ship id.

@param bedCount The cabin's bed count.

*/

public Cabin(final String name,

final int deckLevel,

final int shipId,

final int bedCount) {

this.name = name;

this.deckLevel = deckLevel;

this.shipId = shipId;

this.bedCount = bedCount;

} // End of full constructor

/----

Overridden class methods -

/

/**

Returns a string representation of the cabin's data.

@see java.lang.Object#toString()

*/

@Override

public String toString() {

StringBuffer strBuf = new StringBuffer();

strBuf.append(this.name);

strBuf.append("\n");

strBuf.append(this.deckLevel);

strBuf.append("\n");

strBuf.append(this.shipId);

strBuf.append("\n");

strBuf.append(this.bedCount);

return strBuf.toString();

} // End of toString()

/----

Defining instance methods -

/

/**

Get method for the member "<code>id</code>".

@return Returns the id.

*/

public long getId() {

return this.id;

}

/**

Set method for the member "<code>id</code>".

*

HTDODO hm: Check whether it is possible to have setId method

using private accesss level with NetWeaver JPA-Provider!

@param id The id to set.

*/

private void setId(final long id) {

this.id = id;

}

/----

/

/**

Get method for the member "<code>name</code>".

@return Returns the name.

*/

public String getName() {

return this.name;

}

/**

Set method for the member "<code>name</code>".

@param name The name to set.

*/

public void setName(final String name) {

this.name = name;

}

/----

/

/**

Get method for the member "<code>deckLevel</code>".

@return Returns the deckLevel.

*/

public int getDeckLevel() {

return this.deckLevel;

}

/**

Set method for the member "<code>deckLevel</code>".

@param deckLevel The deckLevel to set.

*/

public void setDeckLevel(final int deckLevel) {

this.deckLevel = deckLevel;

}

/----

/

/**

Get method for the member "<code>shipId</code>".

@return Returns the shipId.

*/

public int getShipId() {

return this.shipId;

}

/**

Set method for the member "<code>shipId</code>".

@param shipId The shipId to set.

*/

public void setShipId(final int shipId) {

this.shipId = shipId;

}

/----

/

/**

Get method for the member "<code>bedCount</code>".

@return Returns the bedCount.

*/

public int getBedCount() {

return this.bedCount;

}

/**

Set method for the member "<code>bedCount</code>".

@param bedCount The bedCount to set.

*/

public void setBedCount(final int bedCount) {

this.bedCount = bedCount;

}

/----

/

} // End of class Cabin

[/code]

After that I created the TravelAgentBean, a Stateless Session Bean, implementing

a remote interface that allows construction and persisting of new Cabin objects:

[code]

package de.collogia.beans.session.stateless;

import javax.ejb.Stateless;

import javax.persistence.EntityManager;

import javax.persistence.PersistenceContext;

import de.collogia.beans.pojo.ship.Cabin;

/**

Class that implements the <code>TravelAgentRemote</code> interface

and defines the business methods of the TravelAgent service.

@author Henning Malzahn (malzahn@edvbm.de)

*

svn-revision: $Rev:: 670 $:

svn-lasted-edited-by: $Author:: henning $:

svn-last-changed: $Date:: 2007-02-21 21:49:51 +0100 (Wed, 21 Feb 2007) $:

*/

@Stateless

public class TravelAgentBean implements TravelAgentRemote {

/** The <code>Log</code> object for this class. */

// private static final Log LOGGER;

/** The <code>PersistenceManager</code> object. */

@PersistenceContext(unitName = "titan_cruises_pu")

EntityManager em;

/----

Static initializer -

/

// static {

// LOGGER = LogFactory.getLog(TravelAgentBean.class);

// } // End of static initializer block

/----

Implementing remote interface methods -

/

/**

{@inheritDoc}

*/

public void createCabin(final Cabin cabin) {

this.em.persist(cabin);

} // End of createCabin()

/----

/

} // End of class TravelAgentBean

[/code]

After that I created a Controller class containing a main method that looks up the remote

interface of the TravelAgentBena like explained in document "Accessing Enterprise JavaBeans Using JNDI

in SAP NetWeaver Application Server, Java ™ EE 5 Edition" written by Validimir Pavlov of SAP NetWeaver

development team.

Unfortunately I receive an Exception after invoking the createCabin(...) method.

On the console of the NWDS I receive:

[code]

javax.ejb.EJBException: Exception in getMethodReady() for stateless bean sap.com/test2Earannotation|test2Ejb.jarannotation|TravelAgentBean;

nested exception is: com.sap.engine.services.ejb3.util.pool.PoolException: javax.ejb.EJBException: Cannot perform injection over bean instance

....

Caused by: java.lang.RuntimeException: The persistence unit is inconsistent:

The entity >>de.collogia.beans.pojo.ship.Cabin<< is mapped to the table >>cabin<<, which does not exist.

[/code]

But if I look at the log file located in "C:\NWAS_JAVAEE5\JP1\JC00\j2ee\cluster\server0\log\defaultTrace.0.trc"

I see the real reason is:

[code]

[EXCEPTION]

#6#1064#42000#You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax

to use near '"cabin"' at line 1#collnx02.collogia.de:3306:null:practise_titan_cruises#select * from "cabin"#com.mysql.jdbc.exceptions.MySQLSyntaxErrorException:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"cabin"' at line 1

at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936)

at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2870)

at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1573)

at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1665)

at com.mysql.jdbc.Connection.execSQL(Connection.java:3124)

at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1149)

at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1262)

at com.sap.sql.jdbc.basic.BasicPreparedStatement.executeQuery(BasicPreparedStatement.java:99)

at com.sap.sql.jdbc.direct.DirectPreparedStatement.executeQuery(DirectPreparedStatement.java:307)

at com.sap.sql.jdbc.direct.DirectPreparedStatement.executeQuery(DirectPreparedStatement.java:264)

at com.sap.engine.services.dbpool.wrappers.PreparedStatementWrapper.executeQuery(PreparedStatementWrapper.java:274)

[/code]

My goodness - what a long post - sorry for this - I hope I provided all information

necessary to deal with the issue.

Am I thinking in the right direction to blame attribute [code]<sql-engine>native_sql</sql-engine>[/code]

of file data-sources.xml for the beaviour? Are there any other argument options than native_sql?

Thanks in Advance!

Henning Malzahn

adrian_goerler · ‎03-20-2007

Hi Henning,

defining the data source for <i>native sql</i> is OK.

I suggest to investigate in a different direction:

SAP JPA respects the case of table and column names. You have explicitly specified the name of the table "cabin" in lower case:

@Entity

@Table(name = "cabin")

public class Cabin ...

SAP JPA now validates that a table named "cabin" exists with the table name being lower case. This check fails. Please double check that you have created the table in lower case. Or change the @Table and @Column annotations so that they match the case of your table definition.

Best regards,

Adrian

JPA with MySQL-Data-Source

Accepted Solutions (1)

Accepted Solutions (1)

Answers (0)

Re: how to read UI values for BP_REL in derviation...

Re: Question about sap fiori elements list report ...

Re: The CrystalDecisions package is not visible to...

Re: Redirecting to Build Apps when trying to open ...

Re: Sap Analytics Cloud - Input a Date Parameter i...