RESTful MySQL / Terminology / Passing Parameters / Returning Ints & Doubles

Question

So, in an attempt to create a RESTful frontend to a MySQL database, I've briefly looked at phprestql (easy & simple, but just too simple) and now I'm attempting to build it onto NetBeans' tutorial. I've got the basic tutorial completed and working with my database just fine. However, I'm trying to figure out how to customize it a bit.

1. All the results in JSON seem to be strings, even though in the MySQL table properties are Big Ints, Ints, and Doubles. The types also seem to be set correctly within the netbeans sources as well. However, JSON returns everything as strings. Any ideas where to address this? (Again, I'm just working from the tutorial above, albeit with my DB.)

2. I'm also trying to figure out how I can implement additional parameters in the URI, to further refine the DB results. (http://localhost/the_db/people_table/?gender_property=male&updated_property=2011-01-18) ... would return all people rows that fit those criteria. Part of my problem is I'm not even sure of the proper terminology for this kind of feature, so it's making it a little difficult to find examples and tutorials on it.

3. This may be related to the previous item, but I'd also like to use the URI to "drill-down" into the table/row/property to return individual values (in JSON) ... (http://localhost/the_db/people_table/42/lastname) ... would return {"Jones"}

Part of the problem is that I barely know Java from Ruby from Python. I'm pretty familiar with breaking things in Objective-C, PHP, and Perl though. However, tutorials for quick and easy Restful MySQL services with those don't seem very popular or prevalent.

[EDIT]

To the extent that this helps answer question #1, I'm attaching some of the java methods to indicate how the numerical properties are set/retrieved ... from what I can tell the actual JSON generation is automated by some library. I don't see it in here:

/** in the MySQL CommitteeObj table, the committeeId is set as follows */
/* `committeeId` bigint(11) NOT NULL auto_increment */

/** in committee.java */
public class committee implements Serializable {
    private static final long serialVersionUID = 1L;
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Basic(optional = false)
    @Column(name = "committeeId")
    private BigInteger committeeId;
//.....
}

public committee(BigInteger committeeId) {
    this.committeeId = committeeId;
}

@Override
    public String toString() {
        return "texlege.committee[committeeId=" + committeeId + "]";
    }

/** in committeeConverter.java */

@XmlElement
public BigInteger getCommitteeId() {
    return (expandLevel > 0) ? entity.getCommitteeId() : null;
}

public void setCommitteeId(BigInteger value) {
    entity.setCommitteeId(value);
}

/** in committeeResource.java */

@GET
    @Produces({"application/json"})
    public committeeConverter get(@QueryParam("expandLevel")
                                  @DefaultValue("1")
    int expandLevel) {
        return new committeeConverter(getEntity(), uriInfo.getAbsolutePath(), expandLevel);
    }

protected committee getEntity() {
        try {
            return (committee) em.createQuery("SELECT e FROM committee e where e.committeeId = :committeeId").setParameter("committeeId", id).getSingleResult();
        } catch (NoResultException ex) {
            throw new WebApplicationException(new Throwable("Resource for " + uriInfo.getAbsolutePath() + " does not exist."), 404);
        }
    }
}

And here's the output from a query of a specific committee. Notice the distinct lack of JSON numbers for the committeeId, committeeType, and parentId properties:

{
    "@uri":"http://localhost:8080/TexLegeRest/rest/committees/2735/",
    "clerk":"Amy Peterson",
    "committeeId":"2735",
    "committeeName":"Appropriations",
    "committeeType":"1",
    "parentId":"-1",
    "updated":"2011-02-20T00:00:00-06:00",
}

Answer 1

In short, this answer may not be what you are looking for at all as it's nothing to do with NetBeans. However it does provide a different way of doing what you want in providing a RESTful interface to a MySQL database.

I have uploaded a zip file with 4 Java files, 2 XML files and 1 text file to support this solution, otherwise the answer would have been very long.

In short this is a Maven/Java/Spring/Hibernate/MySQL solution, the reason being is that I have been using this architecture recently and found it quite simple and powerful to do what is really just converting SQL ↔ JSON!

This solution also uses a few other tools like Maven for compiling/packaging/deploying rather than an IDE, which in my opinion removes a level of complexity, but might put a few IDE-loving people off.

System Configuration

So firstly you will need to download and unzip/install Java and Maven if you don't have those already. I'll also assume Windows, mainly because that is what I am currently using. I have these installed the above applications in the following locations:

c:\apps\java\jdk1.6.0_24
c:\apps\apache-maven-3.0.3

Since there is no IDE in this solution, the application is built and run from the command line. There is a tiny amount of configuration here, so just execute the following to set up some environment variables:

set JAVA_HOME=c:\apps\java\jdk1.6.0_24 Enter

set M2_HOME=c:\apps\apache-maven-3.0.3 Enter

set PATH=%PATH%;%M2_HOME%\bin;%JAVA_HOME%\bin Enter

Typing mvn --version can then be used to verify that Java and Maven are installed and found correctly.

Project Creation

Create a directory for your source, let's use c:\src\project1

On the command line again, navigate to that directory and execute:

mvn archetype:generate -DgroupId=my.group -DartifactId=project1 -DarchetypeArtifactId=maven-archetype-quickstart

Maven will download some standard libraries and eventually prompt you to "Define value for property 'version':" - just Enter to continue. Maven will then ask you to confirm the project settings so just hit Enter again to confirm. You will end up with a directory structure in which you will find a pom.xml file and two Java files. The Project Object Model (POM) file tells Maven how to build/test/package/deploy (and more) your project. You need to add some libraries to that file so that we can use Spring, JSON, Jetty and other functionality. So edit the pom.xml adding the following to the XML structure:

Under <project> element (i.e. as a sibling of the <url> element) add:

<properties>
  <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
  <spring.version>3.0.5.RELEASE</spring.version>
</properties>
<repositories>
  <repository>
    <id>JBoss</id>
    <url>https://repository.jboss.org/nexus/content/groups/public/</url>
    <releases>
      <enabled>true</enabled>
      <updatePolicy>always</updatePolicy>
      <checksumPolicy>warn</checksumPolicy>
    </releases>
  </repository>
</repositories>

Under <dependencies> element add the contents of the dependencies.txt file from the zip file linked above. Those changes will allow Maven to find the latest Hibernate and Spring libraries which are not always present in the default Maven repositories and also other libraries like the HSQLDB - an in-memory database used to test this example and JSON ↔ Java conversion.

Also under the <project> element (I added this just after the </dependencies> element) add the following:

<build>
  <plugins>
    <plugin>
      <groupId>org.mortbay.jetty</groupId>
      <artifactId>maven-jetty-plugin</artifactId>
      <version>6.1.26</version>
      <configuration>
        <contextPath>/${project.artifactId}</contextPath>
        <scanIntervalSeconds>10</scanIntervalSeconds>
        <webXml>${project.build.directory}/${project.build.finalName}/WEB-INF/web.xml</webXml>
      </configuration>
    </plugin>
  </plugins>
</build>

This is the embedded web server that we will use to run the .war file you are about to build, which leads us to the final change in the pom.xml… the <packaging> element near the top of the file needs to be changed to war instead of jar.

On the command line again, navigate to the project directory you just created where the pom.xml is (probably cd project1) and type mvn compile. Maven should download all he new libraries we just added in the POM and hopefully compile without error. Now we need to just configure Spring to wire up all the RESTful URLs & beans and configure the web application itself.

Create 2 new directories under /src/main called resources and webapp/WEB-INF. Your directory structure should now look like this:

src
src/main
src/main/java
src/main/resources
src/main/webapp/WEB-INF

In the resources add the file called applicationContext.xml from the zip file. The ApplicationContext is the configuration for the application.

In the WEB-INF directory add the file called web.xml from the zip file. The web.xml describes how a web container (e.g. Tomcat or in our case Jetty) should deploy the application.

Now we need to add in some code! Instead of adding the code here and making this answer longer than it already is, the zip file contains 4 classes. Simply copy those into the src/main/java/my/group directory, overwriting App.java in the process.

Compilation and Execution

This is where you should cross your fingers… as you should be able to use mvn compile to compile the classes and then if successful mvn jetty:run-war to run the web server with the application war file. If there are no errors in starting the application, there should be some logging that looks like INFO: Mapped URL path [/people] onto handler 'app' as the initialization of Jetty finishes.

Testing the REST Interface

Now we can test the RESTful URLs. I recommend using the Poster addon for Firefox (not compatible with Firefox 4 though), so install this and we can use it to do PUT and GET requests on the project1 web-app. Once installed either select Tools → Poster or Ctrl+Alt+P.

Firstly, since we are using Spring content negotiation (scroll down to the Content Negotiation section) you will need to configure Poster to add the correct Content Type. Just add application/json to this field. To add a person to our database, just add

{"firstName" : "foo", "lastName" : "bar"}

to the body (this is the large area in the Poster addon) and use the PUT button. You should get a reponse back from the web-app and see logging on the command window. The response should be:

{"name":"foo bar","id":1,"height":1.8}

This is valid JSON and you can see integers and doubles are appearing just fine. If you have a look at the Person.java class from the zip file, you can see that firstName and lastName are the names of the actual class members which match the names of the JSON keys that were PUT. I have added a @JsonIgnore annotation to those and created a different @JsonProperty to return the full name instead. In practice you probably would not do this otherwise it would be difficult to update just the first or last name but in this example I am just using it to show that you have full control of the JSON entities returned and their names/values. Also note the Person class has a hard-coded Double (the height member) to demonstrate that numbers are serialized correctly to JSON.

You can then retrieve person 1 by changing the URL to http://localhost:8080/project1/people/1 and using the GET button instead, which just returns the same JSON.

Swapping HSQLDB for MySQL

You might have noticed that there is no MySQL database so far. You will need to change some of the configuration to point to a MySQL database instead of the in-memory HSQL database that was used up until now. The "dataSource" bean properties should be updated like so:

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close"
  p:driverClassName="com.mysql.jdbc.Driver"
  p:url="jdbc:mysql://localhost:3306/name_of_your_database_instance"
  p:username="your_database_username"
  p:password="your_database_password"/>

where you need to specify the connection details of your database.

Lastly the hibernate dialect needs updating to be the MySQL one, so replace the org.hibernate.dialect.HSQLDialect with

<prop key="hibernate.dialect">org.hibernate.dialect.MySQL5InnoDBDialect</prop>

in the applicationContext.xml file.

Be warned that when Hibernate is configured with the following property <prop key="hibernate.hbm2ddl.auto">create</prop> it will destroy the database when the web application is started, since Hibernate is scanning the Person class and creating the table from the @Table, @Entity and @Column (and other) annotations.

Please note that unfortunately I've not tested this last part as I don't have MySQL installed but hopefully it should work.

Answering your questions (or not)

1) No idea, sorry. It looks like just a conversion/serialization problem which could probably be solved looking at what beans are used in the tutorial and checking their documentation.

2) and 3) With the solution above you can add as many path variables as you require, e.g.

@RequestMapping(value = "/people/gender/{gender}/updated/{lastUpdated}", method = RequestMethod.GET)
@ResponseBody
public Person findByGenderAndUpdated(@PathVariable String gender, @PathVariable String lastUpdated) {}

It might not be practical to create an API to expose the individual properties. Returning full resources by your URLs is more practical and then just let the downstream component to pick out the lastName from a Person JSON object if that is all it needs. However I could see the need for a cut-down JSON representation of a Person if there are is a lot of data. i.e. it is more efficient from a bandwidth perspective to exlucde certain large data properties.

You would have to implement these methods yourself for each combination. This effectively constitutes your RESTful API. If you need to create a document to describe your API then the Atlassian guidelines are very well written.

Summary

There are many variations on this solution and in practice you should put the classes in a better directory structure (model, view, controller) and create some Java class templates for handling the persistence, since all models will probalby need a "save", "find" method for example.

I hope this solution is useful to someone :-)

Answer 2

All the results in JSON seem to be strings, even though in the MySQL table properties are Big Ints, Ints, and Doubles. The types also seem to be set correctly within the netbeans sources as well. However, JSON returns everything as strings. Any ideas where to address this? (Again, I'm just working from the tutorial above, albeit with my DB.)

Numerics in JSON will be without quotes. For ex {"person": { "name":"Fred", "age":24 }} . So the JSON parser should be able to handle it as numeric.

I'm also trying to figure out how I can implement additional parameters in the URI, to further refine the DB results. (http://localhost/the_db/people_table/?gender_property=male&updated_property=2011-01-18) ... would return all people rows that fit those criteria. Part of my problem is I'm not even sure of the proper terminology for this kind of feature, so it's making it a little difficult to find examples and tutorials on it.

These are called query params. They are very useful especially when you have a lot of optional parameters in your request

This may be related to the previous item, but I'd also like to use the URI to "drill-down" into the table/row/property to return individual values (in JSON) ... (http://localhost/the_db/people_table/42/lastname) ... would return {"Jones"}

Query params can be used here. If a particular parameter is passed through url, then it will be set or it will be null. Check null condition of each query parameter and construct where clause based on parameters which are not null. That will give the required result.

UPDATE: If returning numbers as string is the only issue that you are facing, add the following to web.xml. This would make the pojo support on. This will return numbers as numbers.

 <init-param>
   <param-name>com.sun.jersey.api.json.POJOMappingFeature</param-name>
   <param-value>true</param-value>
 </init-param>