Search code examples
mysqlxmldatabaseodata

How to expose a MySQL database as OData

I currently have a mySQL database and want interact with it using OData.

In other words, I want to expose the data in this form: http://services.odata.org/Northwind/Northwind.svc/

I've found several tutorials on how to do this (for example, http://msdn.microsoft.com/en-us/vs2010trainingcourse_buildingappandservicesusingodatavs2010_topic3.aspx) if I have Microsoft Visual Studio. But I'm a mac girl. :(

I've also looked at the libraries at the odata homepage (under libraries, and then mysql). But I can't get them to work.

Is there any easy SIMPLE library that can handle this for me?

Thanks so much for the help!!

Solution

  • I've figured out how to do it - using odata4j. I've documented my steps below in case anyone else wants to do something similar.

    You will need to:

    • generate a JPA model from your database
    • use odata4j's NorthwindJpaProducerExample.java script

    Detailed steps are below:

    1. Odata4j is an open-source Odata Producer/Consumer in Java. Therefore, you will need to set up Eclipse for EE Developers with a database. I suggest this tutorial if you are new to Eclipse.
    2. Follow these instructions to generate a JPA model.
    3. Go to Odata4j and download the latest archive zip
    4. Add odata4j-bundle-x.x.jar to your build path (it is found in the bundles file).
    5. Insert the following scripts from Odata4j, found on their github: NorthwindJpaProducerExample, JPAProvider, and DatabaseUtils (requires slf4j). (To be honest, I just copy and pasted them into Eclipse). At this point, your Project Explorer bar should look like this (without model.main):

    Project Explorer Bar

    At this point, right-click the project and select Build Path > Configure Build Path. Add the following "External Jars" from your Odata4j archive file.

    External Jars

    Now edit the code in NorthwindJpaProducerExample in the following ways:

    1. Change the string "endpointUri" to whatever url you want the oData at
    2. Change the string "persistenceUnitName" to the name of your entity in persistence.xml (as you can in the image above, mine was called "createJPA".)

    code sample in NorthwindJpaProducerExample

    And then you have OData!

    My OData

    I ran in to a couple problems while following these steps and will document them here in case you have them also.

    • In order to successfully follow step 2 (generate JPA) each table MUST have a primary key. Do it - I'm not joking.
    • After editing the code, I had a "BigInteger" error. JPAProducer does not support bigInteger field types. Go back to your database and change the size of your column to a regular int.
    • After changing your database in ANY manner, make sure to refresh you database and to clean the project. This will make you and Eclipse happy.
    • I don't think this will be necessary for everyone, but I did need to add a HyperSQL driver dependency. If you get a HSQL error, go to here and get the latest stable version. Add the hsqldb.jar to your Build Path.

    Not SUPER easy, but a lot better than a 70+ page manual.