Technology Blogs by Members
Explore a vibrant mix of technical expertise, industry insights, and tech buzz in member blogs covering SAP products, technology, and events. Get in the mix!
cancel
Showing results for 
Search instead for 
Did you mean: 
Sathees_P
Participant

Exposing MySQL database or any database as OData service endpoint using JPA (JAVA Persistence API) and Apache Olingo OData library on Apache Tomcat web server. The JPA Model can be exposed in the form of OData service endpoint, which allows the data to be accessed via http REST based protocol.

For more details on OData, please refer http://www.odata.org/

(I had a scenario, UI5 Application should run on SAP NetWeaver Gateway and as well in other OData source).

Developing of the OData endpoint using JPA Model and deployment of the OData service in Apache Tomcat in 7 steps.


Table of Contents




Prerequisite

  • MySQL Server 5.x
  • Apache Tomcat 7.x
  • Eclipse Kepler / Juno preferably J2EE edition
  • Dependent libraries refer topic 3


1. Create a dynamic web project in Eclipse

  • Launch Eclipse from extracted folder by double click eclipse.exe to open it. Eclipse will ask you for workspace location. Specify a location and click ok
  • To create a Dynamic Web Project, File -> New -> Dynamic Web Project as shown
  • Enter the project name for example "emplist-web"
  • In Target runtime Click on “New Runtime...” button to define Apache Tomcat v7.0 as HTTP server to expose our JPA Model as OData service endpoint. Provide your Apache Tomcat Server folder in Tomcat installation directory.

  • Click on “Modify...” button in the above window and in opened window, select the “JPA” check box and click on “OK” button

  • In the next window, choose Platform “EclipseLink 2.5.x”, JPA implementation Type: “Disable Library Configuration”, radio button “Annotated classes must be listed in persistence.xml” and click on the “Next” button

  • In the "Java - src" window, click on the “Next” button
  • In the "Web Module" window, check “Generate web.xml deployment descriptor” and click on the “Finish” button. Now project has been created in the project explorer window


2. Create a MySQL connection to be exposed as OData service endpoint

  • To create a MySQL Database connection, in the menu, Windows -> Show View -> Data Source Explorer
  • Go to “Data Source Explorer” tab, Right click on “Database Connection” node and click on “New...” menu item
  • In the Connection Profile window, select MySQL and click on "Next" button
  • Configure the MySQL JDBC Driver i.e. add latest driver by removing non existent old driver.
  • Specify the name for Database field and URL field end
  • Enter the Username, Password and click "Test Connection" button ensure DB connection Ping succeed.

  • In the “Data Source Explorer”, now you can see the MySQL connection and expand to see DB objects

3. Add the dependent libraries to the Project

Download and copy the required libraries to “<your eclipse project>/WebContent/WEB-INF/lib” folder.


  1. JPA: Add EclipseLink and JPA Persistence JAR files to “Lib” folder
    1. eclipselink.jar
    2. javax.persistence_2.1.0.v201304241213.jar
  2. Database: Add MySQL connector java to “Lib” folder
    1. mysql-connector-java-5.1.28-bin.jar
  3. OData: Add Apache Olingo OData libraries JAR files to “Lib” folder
    1. olingo-odata2-core-incubating-1.0.0.jar
    2. olingo-odata2-api-incubating-1.0.0.jar
    3. olingo-odata2-api-annotation-incubating-1.0.0.jar
    4. olingo-odata2-jpa-processor-core-incubating-1.0.0.jar
    5. olingo-odata2-jpa-processor-api-incubating-1.0.0.jar
    6. javax.ws.rs-javax.ws.rs-api-2.0-m10.jar
  4. Service: Other libraries required    (main: http://central.maven.org/maven2/org/apache/cxf/)
    1. cxf-api-2.7.5.jar                          (../cxf-api/2.7.5/cxf-api-2.7.5.jar)
    2. cxf-rt-bindings-xml-2.7.5.jar          (../cxf-rt-bindings-xml/2.7.5/cxf-rt-bindings-xml-2.7.5.jar)
    3. cxf-rt-core-2.7.5.jar                     (../cxf-rt-core/2.7.5/cxf-rt-core-2.7.5.jar
    4. cxf-rt-frontend-jaxrs-2.7.5.jar       (../cxf-rt-frontend-jaxrs/2.7.5/cxf-rt-frontend-jaxrs-2.7.5.jar)
    5. cxf-rt-transports-http-2.7.5.jar      (../cxf-rt-transports-http/2.7.5/cxf-rt-transports-http-2.7.5.jar)
  5. Links: downloading for libraries
    1. JPA: http://www.eclipse.org/eclipselink/downloads/
    2. MySQL DB driver: http://dev.mysql.com/downloads/connector/j/
    3. OData: http://www.apache.org/dyn/closer.cgi/incubator/olingo/odata2/rel-1.0.0/olingo-odata2-dist-jpa-incuba...
    4. Service: There itself


  • After copying libraries to “/WebContent/WEB-INF/lib” folder, go to menu “Project” --> “Clean” and select your project by choosing “Clean projects selected below” and click on “OK” button.
  • After creating JPA entities from table, Eclipse shows error then rightclick on “WebContent/WEB-INF/lib” folder and choose “Validate” menu item.

4. Create a JPA Model from MySQL database connection

  1. To create JPA Model, go to File --> New --> Other --> JPA Entities from Tables and click on next button

  2. Select MySQL connection and click connection button and select the schema where tables are available. Choose tables to be exposed as OData service and check “List generated classes in persistence.xml” and next window is “Table Associations”, here just click “Next” button.

  3. In Next window, check “Always generate optional JPA annotations and DDL parameters” and package can be configured here (com.<your company>.<project name>.model) and click on “Next” button.
  4. Next window click on “Finish” button.
  5. Now in “src” folder, Java classes are created as JPA Model entities.

  6. Double click on “persistence.xml” and select the tab “Connection”
  7. Transaction type: choose “Resource local” and EclipseLink connection pool area click on link “Populate from connection...” and choose Local MySQL and will populate the connection fields.

  8. Click source or select “persistence.xml” file from your project explorer will contain as follows

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

<persistence version="2.1" xmlns="http://xmlns.jcp.org/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd">

       <persistence-unit name="emplist-web" transaction-type="RESOURCE_LOCAL">

              <class>model.Employee</class>

              <class>model.Group</class>

              <properties>

                     <property name="javax.persistence.jdbc.url" value="jdbc:mysql://localhost:3306/test"/>

                     <property name="javax.persistence.jdbc.user" value="root"/>

                     <property name="javax.persistence.jdbc.password" value="King1234"/>

                     <property name="javax.persistence.jdbc.driver" value="com.mysql.jdbc.Driver"/>

              </properties>

       </persistence-unit>

</persistence>

After creating JPA entities from table, Eclipse shows error then right click on “WebContent/WEB-INF/lib” folder and choose “Validate” menu item.


5. Expose your JPA Model as OData service using Apache Olingo

To Expose the JPA Model from MySQL as OData service, we use Apache Olingo library. For more information: http://olingo.incubator.apache.org/

Using Olingo, exposing JPA Model as OData service simple and it consists of three steps

  • Extend the “ODataJPAServiceFactory” class by (EmployeeListServiceFactory.java)
  • Creating a new class file “EmployeeListServiceFactory.java” in package “main” with following code (copy paste).

package main;

import javax.persistence.EntityManagerFactory;

import javax.persistence.Persistence;

import org.apache.olingo.odata2.processor.api.jpa.ODataJPAContext;

import org.apache.olingo.odata2.processor.api.jpa.ODataJPAServiceFactory;

import org.apache.olingo.odata2.processor.api.jpa.exception.ODataJPARuntimeException;

public class EmployeeListServiceFactory extends ODataJPAServiceFactory {

  private static final String PERSISTENCE_UNIT_NAME = "emplist-web";

  @Override

  public ODataJPAContext initializeODataJPAContext()

      throws ODataJPARuntimeException {

    ODataJPAContext oDatJPAContext = this.getODataJPAContext();

    try {

      EntityManagerFactory emf = Persistence.createEntityManagerFactory(PERSISTENCE_UNIT_NAME);

oDatJPAContext.setEntityManagerFactory(emf);

      oDatJPAContext.setPersistenceUnitName(PERSISTENCE_UNIT_NAME);

      return oDatJPAContext;

    } catch (Exception e) {

      throw new RuntimeException(e);

    }

  }

}

  • Add necessary tags in “web.xml” file
  • Copy the contents in <servlet> and <servlet-mapping> tags

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

<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://java.sun.com/xml/ns/javaee" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd" id="WebApp_ID" version="3.0">

  <display-name>emplist-web</display-name>

  <welcome-file-list>

    <welcome-file>index.html</welcome-file>

  </welcome-file-list>

  <servlet>

    <servlet-name>ODataServlet</servlet-name>

    <servlet-class>org.apache.cxf.jaxrs.servlet.CXFNonSpringJaxrsServlet</servlet-class>

    <init-param>

      <param-name>javax.ws.rs.Application</param-name>

      <param-value>org.apache.olingo.odata2.core.rest.app.ODataApplication</param-value>

    </init-param>

    <init-param>

      <param-name>org.apache.olingo.odata2.service.factory</param-name>

      <param-value>main.EmployeeListServiceFactory</param-value>

    </init-param>

    <load-on-startup>1</load-on-startup>

  </servlet>

  <servlet-mapping>

    <servlet-name>ODataServlet</servlet-name>

    <url-pattern>/emplist.svc/*</url-pattern>

  </servlet-mapping>

</web-app>

  • Add “index.html” to “WebContent” (Right click -> New HTML file) folder with following content

<!DOCTYPE html>

<html>

    <head>

        <meta charset="utf8">

        <title>Employee List OData</title>

    </head>

    <body>

        <h1>Employee List OData</h1><br><br>

        <a href="/emplist-web/emplist.svc/">Service Document</a>

    </body>

</html>

6. Testing OData service endpoint

  • Right click on your project in “Project Explorer”, select menu items “Run As” -> “Run on Server”.
  • In the window, choose “Tomcat v7.0 Server at localhost” and click on “Finish” button
  • After successfully web application deployed, you will get the below window (internal browser) or configured browser. In browser, “index.html” is shown and by clicking the link “Service Document”. Browser will show your OData Service document (http://localhost:8080/emplist-web/emplist.svc/). Better use Google Chrome browser. To access OData Metadata Service document add $metadata in the link (http://localhost:8080/emplist-web/emplist.svc/$metadata)

7. OData service in CORS

  • Cross-origin resource sharing (CORS) is a mechanism that allows JavaScript on a web page to make XMLHttpRequests (AJAX calls) to another domain, not the domain the JavaScript originated from. Such "cross-domain" requests would otherwise be forbidden by web browsers, per the same origin security policy.
  • UI5 application developed in other domain, requires access to your OData service forbidden by browsers.
  • Allow access to OData service in other domain (CORS) then configure below tags in “/WebContent/ WEB-INF/web.xml” file in your project or globally for all web application then update in Apache Tomcat Server folder “/conf/web.xml”.

  <!-- CORS To allow access OData service by JavaScript in other domain  -->

  <filter>

      <filter-name>CorsFilter</filter-name>

      <filter-class>org.apache.catalina.filters.CorsFilter</filter-class>

  </filter>

  <filter-mapping>

      <filter-name>CorsFilter</filter-name>

      <url-pattern>/*</url-pattern>

  </filter-mapping>


SAP Blog: Developing the OData Service | SCN

This blog helped me to find this solution and it is inspired me to write my own blog.

74 Comments
Labels in this area