Mapping a Flat Data Model to a Relational Data Model Using Dozer and Proxies

Introduction

I’m working for an insurance company which uses the AL3 form standard along with the Acord XML standard for the insurance business.  Acord has created a mapping spreadsheet which maps AL3 fields to Acord XML fields.  This is great because it allows us to communicate business-to-business with our carriers.  But there is a problem with the mapping.  [no proprietary information is shared in this article]

You map MailingAddressStreet to /customer/address[1].street

Do you see the problem?  Customer can have zero to many addresses of types mailing and home.  This means that an address has a code ‘H’ or ‘M’ to designate which one it is.  This also means that a customer’s lists of addresses must have an Address with a code of ‘M’ at index 1 for this mapping to work.  And this is how the current version of our software works.

The scale of our system is small right now, so the problem is not yet apparent, but it must scale to a huge size by the end of our implementation.  I believe the chances of a vulnerability are high with this mapping design, and so I’ve created an alternative.

Technology

For my proof of concept I’ve chosen a simplified technology stack to test my design using Spring MVC, Spring JPA, Hibernate, SQL Server, JSP, and Dozer. You will recognize the first five, but you may not recognize the last.  Dozer is a Java Bean-To-Bean mapping API which we chose specifically for AL3 to Acord XML mapping.  It is elegantly simple.

Using an XML file (or annotations) I can map each field of a DTO (Data Transfer Object) to a JPA Entity with a single command:  mapper.map(DTO,Entity) and back mapper.map(Entity, DTO).  It’s a marvelous time saver.  Mapping from the web to the database is painstaking, low-level code made easy with Dozer and JPA.  I’m 100% sold on these technologies.

Dozer’s Solution

The Dozer folks have proposed exactly what the Acord folks propose

dozer-index-mapping

Look familiar?  In this example userName1 must be in the array first.  But what if you are using an ArrayList, which is unordered?  What if your business logic orders them differently?

My goal is to solve the the AL3 to Acord XML multiple Address problem and make the solution full proof while continuing to take advantage of the Dozer technology.

My Solution

My team has been looking at problems like this for many months, and one day a member of my team proposed this:  make the JPA Entity mirror the DTO on the surface and break up the data into objects underneath.

This means if the DTO bean has methods called setMailingStreet and getMailingStreet then the Entity, even though it does not having these properties, should have the same methods.  Dozer looks at the public getters and setters to map.  It doesn’t care what you do with the data once they are called.  We would be transiently augmenting the entity which simply means that we would add these new methods to the entity and they will not be persisting anything.  We do this with the @Transient annotation on the methods.  These transient methods are proxies for a second layer of conversion.

The Code

To use this technology stack, there is much configuration to do and I will not cover that.  Instead, I will you give you the essential snippets to demonstrate this design starting with the form.

The app is simple.  We have a Customer and he/she has Addresses.  I want to present the customer with only two kinds of addresses:  home and mailing.  In the database, Customer has a relationship with Address that will allow for zero to many addresses per customer, and the Entity is the same way.  To the user, it’s all one record.  To the database, it is many records;  one-to-many.

The Customer app is simply a CRUD (Create Read Update Delete) app.

The form is very simple and looks like this

customer-form

This maps to a DTO bean.  Here’s a snippet to give you the idea.

public class CustomerDTO
{
	private int id;

    private String firstName;

    private String middleInitial;

    private String lastName;

    private Integer homeAddressId;

    private String homeStreet;

    private String homeCity;

    private String homeState;

    private String homeZip;

    private Integer mailingAddressId;

    private String mailingStreet;

    private String mailingCity;

    private String mailingState;

    private String mailingZip;

    public int getId()
    {
        return id;
    }

    public void setId(int id)
    {
        this.id = id;
    }

    public String getFirstName()
    {
        return firstName;
    }

    public void setFirstName(String firstName)
    {
        this.firstName = firstName;
    }

    public String getMiddleInitial()
    {
        return middleInitial;
    }

    public void setMiddleInitial(String middleInitial)
    {
        this.middleInitial = middleInitial;
    }

    public String getLastName()
    {
        return lastName;
    }

    public void setLastName(String lastName)
    {
        this.lastName = lastName;
    }

    public String getHomeStreet()
    {
        return homeStreet;
    }

    public void setHomeStreet(String homeStreet)
    {
        this.homeStreet = homeStreet;
    }
...

As you can see, the DTO maps precisely to the form.  However, the Customer entity bean only has firstName, middleInitial, lastName, id, and a list of Address beans.  This is not a complete mapping.

The Dozer mapping file looks like this.

<?xml version="1.0" encoding="UTF-8"?>
<mappings xmlns="http://dozer.sourceforge.net" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 	xsi:schemaLocation="http://dozer.sourceforge.net           http://dozer.sourceforge.net/schema/beanmapping.xsd">
	<mapping>
		<class-a>com.mycompany.customer.dto.CustomerDTO</class-a>
		<class-b>com.mycompany.customer.domain.model.Customer</class-b>
	</mapping>
</mappings>

The way Dozer works is that it inspects each object for matching getters and setters and it transfers the data for the ones which are the same.  Anything that doesn’t match is ignored.  The mapping can be done explicitly to resolve when the field names do not match, but what do you do when the field just doesn’t exist as in the case of the address fields?

That’s when my proxy pattern comes into play.  I add those fields explicitly to the Customer bean.  I take each getter and setter from the DTO and add them to the Customer bean with the @Transient annotation so JPA knows not to mess with them.

This is where it gets tricky because we have to take a zip or a street or a state or a city and put it into an Address object with a matching code.  For example, a mailing zip needs to be in an Address object with a code of ‘M’ for mailing.  In this application there can be only one mailing address for a customer.  That takes a bit of doing.  Here’s one way of doing it.

@Transient
public String getMailingZip()
{
	Address mailingAddress = null;
	if (addressList != null)
	{
		for (Address address : addressList)
		{
			if (AddressCode.MAILING.equals(address.getCode().trim()))
			{
				mailingAddress = address;
			}
		}
	}
	if (mailingAddress != null)
	{
		return mailingAddress.getZip();
	}
}

@Transient
public void setMailingZip(String mailingZip)
{
	Address mailingAddress = null;
	if (addressList == null)
	{
		mailingAddress = new Address();
		addressList = new ArrayListAddress>();
		addressList.add(mailingAddress);
	} else
	{
		for (Address address : addressList)
		{
			if (AddressCode.MAILING.equals(address.getCode().trim()))
			{
				mailingAddress = address;
				mailingAddress.setZip(mailingZip);
			}
		}
	}
	mailingAddress = new Address();
	mailingAddress.setCustomerId(this);
	mailingAddress.setCode(AddressCode.MAILING);
	mailingAddress.setZip(mailingZip);
	addressList.add(mailingAddress);
     }

I’ll step you through it.

  1. To get a mailingZip we first need to find the mailing address.  The Customer bean has a list of of Address beans.  In this case there is one with an address code of ‘H’ and one with ‘M’.  We want ‘M’ for mailing.
  2.  Once we have that object we can get the zip and return it.  Dozer thinks there really is a mailingZip property in Customer and so it maps it.

To set a zip we do a similar thing.

  1. We make sure that there is an addressList.  If this is a new customer there may not yet be one.  If there isn’t, then we create a new one.
  2.  Then we create a new Address, set the zip and add it to the list.
  3.  If there is already a list then we search for the ‘M’ code.  If we find it, then we set the zip, if we don’t then we create a new Address with the ‘M’ code and set the zip.

Dozer will call all of the sets or gets depending on which is the target and which the source.  Here is example of how to transfer from the DTO to the entity bean for editing.

To edit a customer, the method makes the customerDTO the source and the empty customer class as the destination if it’s a new customer, an existing customer if it’s an existing one.

New Customer

customer = mapper.map(customerDTO, Customer.class);

Existing Customer

customer = mapper.map(customerDTO, customer);

However, if we need to display a customer, we need to do the opposite.

CustomerDTO dto = mapper.map(customer, CustomerDTO.class);

What all of this means is that there are two mappings happening here.  Dozer maps to and from the proxy methods and then the proxy methods are smart enough to map to the correct Address objects.

As a bonus, I’ll show how I can further abstract the proxy mapping so that all of that code doesn’t have to appear for every method.  I’m sure I’ll eventually make it so that it can be used for any such entity, but this is good enough for now.

@Transient
public String getMailingZip()
{
	return(String)getAddressPart(AddressCode.HOME,"getZip",getAddressByCode(               AddressCode.MAILING));
}

@Transient
public void setMailingZip(String mailingZip)
{
	this.setAddressPart(AddressCode.MAILING, mailingZip, "setZip",String.class);
}

@Transient
private Address getAddressByCode(String code)
{
	if (addressList != null)
	{
		for (Address address : addressList)
		{
			if (code.trim().equals(address.getCode().trim()))
			{
				return address;
			}
		}
	}
	return null;
}
@Transient
private Object getAddressPart(String code, String methodName, Address addr)
{
	Class
<Address> addrClass = Address.class;
	Method getMethod = null;
	Object rtn = null;
	if (addr == null)
	{
		addr = new Address();
	}
	try
	{
		getMethod = addrClass.getMethod(methodName, new Class[]{});
		rtn = getMethod.invoke(addr);
	} catch (NoSuchMethodException | SecurityException | IllegalAccessException  IllegalArgumentException | NullPointerException | InvocationTargetException e)
	{
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	return rtn;
}
@Transient
private void setAddressPart(String code, Object addressPart, String methodName, Class dataType)
{
	Class
<Address> addrClass = Address.class;
	Method setMethod = null;
	if (addressList == null)
	{
		addressList = new ArrayList
<Address>();
	}
	try
	{
		setMethod = addrClass.getMethod(methodName, new Class[]{ dataType});
		for (Address existingAddr : addressList)
		{
			if (code.equals(existingAddr.getCode().trim()))
			{
				setMethod.invoke(existingAddr, addressPart);
				return;
			}
		}
		Address newAddr = new Address();
		newAddr.setCustomerId(this);
		newAddr.setCode(code);
		setMethod.invoke(newAddr, addressPart);
		addressList.add(newAddr);
	} catch (NoSuchMethodException | SecurityException | IllegalAccessException | IllegalArgumentException | InvocationTargetException e)
	{
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	return;
}

First, look at the getter and setter for zip.  I’ve reduced it to one unique line of code each.  All of the logic is now in getAddressByCode, getAddressPart, and setAddress part.  So we have a look up, a getter, and a setter which can be used for any part of any address.

Alternative

As I wrapped up this project, I thought of another possible way to do this.  Dozer is an extensible framework.  It allows you to add custom converters to the mapping that might do the same thing as what I’ve done here.  I like the notion of a customer converter because the DTO doesn’t need to know anything about the entity bean and vice versa.  My predecessor used custom converters, but they ended up doing the entire conversion, nearly removing Dozer from the picture completely.  I want Dozer to do the part it can do and have my solution do the rest. That may end up being a cleaner solution with a clearer separation of concerns.  You may see another blog post on this.  But for now, my company has a working solution and that may be all they care about.

Conclusion

To summarize, we’ve tricked Dozer into believing that it can map CustomerDTO with a Customer entity bean by adding transient proxy methods to the bean.  We’ve created internal mapping to shift the form data into a relational model between Customer and Address. We’ve secured the data by avoiding the deep indexing method proposed by AL3 and Dozer.

If you have suggestions or questions, please don’t hesitate to leave your comments.  I am, after all, just a Regular Average Java Programmer (RAJP).

Advertisements

9 Steps for dynamic filtering and paging of a JPA Entity

Folks, this isn’t by any means a show of brilliant software engineering, but I didn’t find anything exactly like it in the blogosphere. I have a really basic scenario to solve.  I have a JQuery grid in a jsp with filterable columns which are mapped via Stripes MVC and Spring to a JPA Entity.

The ajax call from JQuery gives me name/value pairs to filter.  It also gives me the row number to begin the page with and how many rows to retrieve.  From this, I can filter the entity list without hard-coding the Predicates or without building a JPQL string.  The following method goes in Spring Repository or where ever you are keeping business logic for entities.

Step 1:  #44 – Get a CriteriaBuilder from the entity manager

Step 2: #47 – Get a Root of type <YourEntity>

Step 3: #49 – declare a collection of Predicates

Step 4: #60 – For each name/value pair, instantiate a new Like Predicate and add it to the list.  Use The % sign around the data for wildcard searching

Step 5: #62 – Add all predicates to the query’s where clause

Step 6: #63 – create TypedQuery from the CriteriaQuery

Step 7: #70 – set the first row for your page

Step 8: #71 – set your max your for your page

Step 9: #72 – retrieve the data

FilterPaging

Performance Trap using JPA: Transfer Object pattern and JPQL to the Rescue!

DISCLAIMER:  I am a Regular Average Java Programmer; an anti-expert.  I like to do things right, but I’m generally satisfied with doing things that WORK.  I’m sure there’s some JE (Java Expert) out there who knows how to do it right and you can buy his book and worship his rightness at the Java Rock Star Hall of Fame.

Can I say that I love JAX-WS with JPA with Netbeans?  This is a golden combination folks.  Netbeans supports the hell out of these things.  But there are some traps.  One of which is the potential for very poor performance.  But I will show you how to avoid this with a simple design pattern:   Transfer Object.

Transfer Object is so crucial when using JPA.  I realize that we should all know this, but Netbeans does not generate Transfer Objects for you like they do Controllers and Entities.  You have to do it yourself.  And you probably should have to.  After all, how can Netbeans anticipate the specific data transfer needs of your crappy application?

Let’s break it down!

What is a Transfer Object?

Here’s a pretty comprehensive technical breakdown from java.sun.com  (he he…sun)

http://java.sun.com/blueprints/patterns/TransferObject.html

Now, let’s just put it into RAJP terms.  A Transfer Object (or Value Object) is just the attributes needed for a particular use case all wrapped up together in one object.

What Problems does it solve with JPA?

  1. Separation of the concerns of the Model with the View
  2. Poor performance due to Network Traffic
  3. Poor performance due to High Latency

In Java Persistence API (JPA), your basic representation of a row in the database is called an Entity.  An Entity is a special kind of Java Bean that knows a lot about how to map a database table to an object.  So it’s not just instance variables, getters, and setters.  It’s a powerful little guy that you don’t want just any tier of your enterprise to have access to.    It’s also an expensive little guy.  In fact, depending on how it relates to other entities it might not be so little at all.  One entity that is joined with other entities might pull in a whole universe of data. And worse, it might even be like a House of Mirrors; creating an infinite graph of data.  AHHHHHHH!!!  Let me out of here!!!!

For example, a Car might have a list of Dealers and a Dealer might have a list of Cars each of which has a list of Dealers, and so on!  Fortunately, JPA implementations do not infinitely load this stuff.  The problem only arises when you need to expose Entities to another tier or another application (say, with web services).

[Who am I kidding?  I don’t REALLY have tiers!!  Everything I do is in the web tier!  But I do have services!]

How do I do it?  “Is it magic?” you ask.  NO!!! It’s JAVA!  Nothing magical about it!  What do you think this is?  Ruby?

So, the idea is that you create these Transfer Objects with only the attributes that are required for the particular use case.    This separates the concerns of the Model from the View and minimizes network traffic, but it doesn’t necessarily solve the latency issue.  If your JPA calls are still retrieving and mapping gobs and gobs of data before you perform your transfer, then you may want to consider coding a more efficient JPQL query (or a stored procedure) and map the results directly into your Transfer Object.

Let’s look at how Netbeans builds entities and the inherent problems when it come to the way they handle web services.

Here’s an example of an Entity generated from a table in a relational database:


1
/*

 2  * To change this template, choose Tools | Templates
 3  * and open the template in the editor.
 4  */
 5
 6 package sample.persistence;
 7
 8 import java.io.Serializable;
 9 import java.math.BigDecimal;
 10 import java.util.List;
 11 import javax.persistence.Basic;
 12 import javax.persistence.CascadeType;
 13 import javax.persistence.Column;
 14 import javax.persistence.Entity;
 15 import javax.persistence.Id;
 16 import javax.persistence.JoinColumn;
 17 import javax.persistence.ManyToOne;
 18 import javax.persistence.NamedQueries;
 19 import javax.persistence.NamedQuery;
 20 import javax.persistence.OneToMany;
 21 import javax.persistence.Table;
 22
 23 /**
 24  *
 25  * @author david ctr wilson-bur
 26  */
 27 @Entity
 28 @Table(name = "PRODUCT", catalog = "", schema = "APP")
 29 @NamedQueries({
 30     @NamedQuery(name = "Product.findAll", query = "SELECT p FROM Product p"),
 31     @NamedQuery(name = "Product.findByProductId", query = "SELECT p FROM Product p WHERE p.productId = :productId"),
 32     @NamedQuery(name = "Product.findByPurchaseCost", query = "SELECT p FROM Product p WHERE p.purchaseCost = :purchaseCost"),
 33     @NamedQuery(name = "Product.findByQuantityOnHand", query = "SELECT p FROM Product p WHERE p.quantityOnHand = :quantityOnHand"),
 34     @NamedQuery(name = "Product.findByMarkup", query = "SELECT p FROM Product p WHERE p.markup = :markup"),
 35     @NamedQuery(name = "Product.findByAvailable", query = "SELECT p FROM Product p WHERE p.available = :available"),
 36     @NamedQuery(name = "Product.findByDescription", query = "SELECT p FROM Product p WHERE p.description = :description")})
 37 public class Product implements Serializable {
 38     private static final long serialVersionUID = 1L;
 39     @Id
 40     @Basic(optional = false)
 41     @Column(name = "PRODUCT_ID")
 42     private Integer productId;
 43     @Column(name = "PURCHASE_COST")
 44     private BigDecimal purchaseCost;
 45     @Column(name = "QUANTITY_ON_HAND")
 46     private Integer quantityOnHand;
 47     @Column(name = "MARKUP")
 48     private BigDecimal markup;
 49     @Column(name = "AVAILABLE")
 50     private String available;
 51     @Column(name = "DESCRIPTION")
 52     private String description;
 53     @JoinColumn(name = "PRODUCT_CODE", referencedColumnName = "PROD_CODE")
 54     @ManyToOne(optional = false)
 55     private ProductCode productCode;
 56     @JoinColumn(name = "MANUFACTURER_ID", referencedColumnName = "MANUFACTURER_ID")
 57     @ManyToOne(optional = false)
 58     private Manufacturer manufacturer;
 59     @OneToMany(cascade = CascadeType.ALL, mappedBy = "product")
 60     private List<PurchaseOrder> purchaseOrderList;
 61
 62     public Product() {
 63     }
 64
 65     public Product(Integer productId) {
 66         this.productId = productId;
 67     }
 68
 69     public Integer getProductId() {
 70         return productId;
 71     }
 72
 73     public void setProductId(Integer productId) {
 74         this.productId = productId;
 75     }
 76
 77     public BigDecimal getPurchaseCost() {
 78         return purchaseCost;
 79     }
 80
 81     public void setPurchaseCost(BigDecimal purchaseCost) {
 82         this.purchaseCost = purchaseCost;
 83     }
 84
 85     public Integer getQuantityOnHand() {
 86         return quantityOnHand;
 87     }
 88
 89     public void setQuantityOnHand(Integer quantityOnHand) {
 90         this.quantityOnHand = quantityOnHand;
 91     }
 92
 93     public BigDecimal getMarkup() {
 94         return markup;
 95     }
 96
 97     public void setMarkup(BigDecimal markup) {
 98         this.markup = markup;
 99     }
100
101     public String getAvailable() {
102         return available;
103     }
104
105     public void setAvailable(String available) {
106         this.available = available;
107     }
108
109     public String getDescription() {
110         return description;
111     }
112
113     public void setDescription(String description) {
114         this.description = description;
115     }
116
117     public ProductCode getProductCode() {
118         return productCode;
119     }
120
121     public void setProductCode(ProductCode productCode) {
122         this.productCode = productCode;
123     }
124
125     public Manufacturer getManufacturer() {
126         return manufacturer;
127     }
128
129     public void setManufacturer(Manufacturer manufacturer) {
130         this.manufacturer = manufacturer;
131     }
132
133     public List<PurchaseOrder> getPurchaseOrderList() {
134         return purchaseOrderList;
135     }
136
137     public void setPurchaseOrderList(List<PurchaseOrder> purchaseOrderList) {
138         this.purchaseOrderList = purchaseOrderList;
139     }
140
141     @Override
142     public int hashCode() {
143         int hash = 0;
144         hash += (productId != null ? productId.hashCode() : 0);
145         return hash;
146     }
147
148     @Override
149     public boolean equals(Object object) {
150         // TODO: Warning - this method won't work in the case the id fields are not set
151         if (!(object instanceof Product)) {
152             return false;
153         }
154         Product other = (Product) object;
155         if ((this.productId == null && other.productId != null) || (this.productId != null && !this.productId.equals(other.productId))) {
156             return false;
157         }
158         return true;
159     }
160
161     @Override
162     public String toString() {
163         return "sample.persistence.Product[productId=" + productId + "]";
164     }
165
166 }
167
168

Note that there are several joins with other entities.  Well each of those entities join back to Product!  This is helpful stuff, but there is the trap!  Do not try to return this entity from a web service and do not try to replicate these relationships in your Transfer Object!  And DON’T CROSS THE STREAMS!!!!!!

Here’s what will happen:

com.sun.istack.SAXException2: A cycle is detected in the object graph. This will cause infinitely deep XML

OH SNAP!!!  Whoa!!!  A bottomless pit of XML.  That sounds scary.  And I bet if you DID reach the bottom it would not be a soft landing.  XML is hard and scratchy!!!

Imagine with me a simple use case where a web application needs to show a Product catalog.  All you really need is Product Id (for retrieval purposes), Description, Cost,  and the name of the Manufacturer.  You might not want your web app to expose the Markup, the Quantity on Hand or other details.  So you design ProductTO (TO for TransferObject)  with only the fields needed for the catalog listing.


 1 /*
 2  * To change this template, choose Tools | Templates
 3  * and open the template in the editor.
 4  */
 5
 6 package sample.persistence;
 7
 8 import java.math.BigDecimal;
 9
10 /**
11  *
12  * @author david ctr wilson-bur
13  */
14 public class ProductTO {
15     private Integer productId;
16     private String description;
17     private BigDecimal purchaseCost;
18
19     public String getDescription() {
20         return description;
21     }
22
23     public void setDescription(String description) {
24         this.description = description;
25     }
26
27     public String getManufacturerName() {
28         return manufacturerName;
29     }
30
31     public void setManufacturerName(String manufacturerName) {
32         this.manufacturerName = manufacturerName;
33     }
34
35     public Integer getProductId() {
36         return productId;
37     }
38
39     public void setProductId(Integer productId) {
40         this.productId = productId;
41     }
42
43     public BigDecimal getPurchaseCost() {
44         return purchaseCost;
45     }
46
47     public void setPurchaseCost(BigDecimal purchaseCost) {
48         this.purchaseCost = purchaseCost;
49     }
50     private String manufacturerName;
51
52
53 }
54
55

One last convenience is to make a transfer method in your Entity class.

166     public ProductTO transfer(){
167         ProductTO to = new ProductTO();
168         to.setProductId(productId);
169         to.setDescription(description);
170         to.setPurchaseCost(purchaseCost);
171         to.setManufacturerName(this.manufacturer.getName());
172         return to;
173     }

The TransferObject solves the first two problems, but leaves the issue of latency.  When you retrieve a Product list, you are also retrieving all the entities joined to it.  Even if you configured the entity with lazy loading, you would still load Manufacturer entity when you try to access the manufacturer name in your transfer method.  Imagine THAT scenario compounded with multiple joins and large datasets.

Get the picture?

This is where some Java Persistence Query Language (JPQL) comes in handy.

SELECT p.productId, p.description, p.purchaseCost, p.manufacturer.name FROM Product p

Map this result set to your ProductTO and you solve your problems.


258
public List<ProductTO> findProductCatalog() {

259         EntityManager em = getEntityManager();
260         List<ProductTO> productTOs = new ArrayList<ProductTO>();
261         try {
262             Query q = em.createQuery("SELECT p.productId, p.description, p.purchaseCost, p.manufacturer.name FROM Product p");
263             List<Object[]> products = (List<Object[]>)q.getResultList();
264             for (Object[] objects : products) {
265                 ProductTO to = new ProductTO();
266                 to.setProductId((Integer) objects[0]);
267                 to.setDescription((String) objects[1]);
268                 to.setPurchaseCost((BigDecimal) objects[2]);
269                 to.setManufacturerName((String) objects[3]);
270                 productTOs.add(to);
271             }
272             return productTOs;
273         }
274         finally {
275             em.close();
276         }
277     }

Yes, I know this kind of defeats the purpose of automated ORM (Object Relational Mapping), but…

RAJP – [raj-pee] Just one of the thousands of regular, average java programmers trying to get their job done…

How to use a JNDI DataSource with JPA in Netbeans 6.01

When you create an Entity Class in Netbeans 6.01, you have to choices:

  1. New –> Entity Class
  2. New –> Entity Class from Database

Choosing “Entity Class from Database” brings up a wizard to choose which database table you would like to map. If your Netbeans project is a Web Application, you get a list of Data Sources from which to choose. It shows all the JDBC Resources that are configured in your application server. Piece of cake. You’re good to go.

fig13.jpg

This is great if you’re setting up your data model inside your web application. However, if you are setting up your data model in a Java Application project (like in the tutorial), you get the list of Database Connections that are configured on your Netbeans Services tab under the Databases node.

fig14.jpg

Perhaps Netbeans has a easy/slick way of switching from Database Connection to Data Source mode on this wizard, but I haven’t found it yet. Here’s my workaround:

1.) Go ahead and select your Database Connection and complete the wizard

2.) Open persistence.xml in XML mode

3.) Remove the following properties from the properties tag:
<property name=”toplink.jdbc.user” value=”app”/>
<property name=”toplink.jdbc.password” value=”app”/>
<property name=”toplink.jdbc.url” value=”jdbc:derby://localhost:1527/sample”/>
<property name=”toplink.jdbc.driver” value=”org.apache.derby.jdbc.ClientDriver”/>

4.) Add the following tag after the provider tag

<non-jta-data-source>jdbc/sample</non-jta-data-source>

Then, we you drop the jar file for your JPA model into your web application, it will know to access the database through with a JNDI lookup at runtime.

2 cents from a RAJP:

cent 1

If you need to use this persistence unit (PU) in a web application, you ought to be using a data source (no question about it!). If you need to use the PU from a Java SE application as well, then set up two persistence units: 1 for the web that uses a Data Source and 1 for the SE app that uses a Database Connection. I haven’t tested this out, but it seems feasible.

cent 2

DO separate your JPA data model into a separate Netbeans Java Application project.  Don’t build your model in your web application.  You want a clear separation between the web tier and the persistence tier.

[EDIT:  I’ve rethought this advice about having a separate Netbeans project for the persistence tier.  It’s kind of a hassle.  I don’t do it anymore.  I just build it all into one Netbeans Web Application Project.  It’s simpler.]

2 cents is about all this is worth because I’m a JPA newb.

Thoughts as I’m reading “Introduction to the Java Persistence API”

I’m preparing for a presentation on JPA fundamentals for my team, so I’m reading Introduction to the Java Persistence API from Sun’s Java EE tutorial. I’d like to record some of my thoughts as I’m reading for later reference.

Thought #1

I was concerned about how JPA handles complex data models from the database (one-to-many, many-to-many). Having managed this myself in applications, I know how tricky it can be. I felt very comforted by this segment of the reading

Multiplicity in Entity Relationships

There are four types of multiplicities: one-to-one, one-to-many, many-to-one, and many-to-many.

One-to-one: Each entity instance is related to a single instance of another entity. For example, to model a physical warehouse in which each storage bin contains a single widget, StorageBin and Widget would have a one-to-one relationship. One-to-one relationships use the javax.persistence.OneToOne annotation on the corresponding persistent property or field.

One-to-many: An entity instance can be related to multiple instances of the other entities. A sales order, for example, can have multiple line items. In the order application, Order would have a one-to-many relationship with LineItem. One-to-many relationships use the javax.persistence.OneToMany annotation on the corresponding persistent property or field.

Many-to-one: Multiple instances of an entity can be related to a single instance of the other entity. This multiplicity is the opposite of a one-to-many relationship. In the example just mentioned, from the perspective of LineItem the relationship to Order is many-to-one. Many-to-one relationships use the javax.persistence.ManyToOne annotation on the corresponding persistent property or field.

Many-to-many: The entity instances can be related to multiple instances of each other. For example, in college each course has many students, and every student may take several courses. Therefore, in an enrollment application, Course and Student would have a many-to-many relationship. Many-to-many relationships use the javax.persistence.ManyToMany annotation on the corresponding persistent property or field.

It goes on to describe some of the fine-grained controls that further define relationships. It’s clear to me, though, that JPA can do the heavy lifting.

Thought #2

Holy Crap! If I’m understanding this correctly, JPA essentially sets up an object-oriented model of the data store as it applies to an application, and abstracts all of the best practices of data manipulation into pure business terms. Ahhhhh…the beauty of Object-Oriented design applied to data persistence. I realize I’m years behind on this little revelation, but better late then never!

Thought #3

Query Language: The FETCH JOIN.

This is one of those features that just made me say “Suuuuuweet!”. It looks like this:

A FETCH JOIN is a join operation that returns associated entities as a side-effect of running the query. In the following example, the query returns a set of departments, and as a side-effect, the associated employees of the departments, even though the employees were not explicitly retrieved by the SELECT clause.

SELECT dFROM Department d LEFT JOIN FETCH d.employeesWHERE d.deptno = 1

The first thing that comes to mind is when you are retrieving a record to populate a form with drop-down fields. Perhaps this is a quick ‘n easy way to get the entities for the drop-downs in one call. We’ll see. Somethings are not always as they seem.

Thought #4

Query Language: Navigation

You’re BLOWING MY MIND here Query Language!

Navigation

A path expression enables the query to navigate to related entities. The terminating elements of an expression determine whether navigation is allowed. If an expression contains a single-valued relationship field, the navigation can continue to an object that is related to the field. However, an expression cannot navigate beyond a persistent field or a collection-valued relationship field. For example, the expression p.teams.league.sport is illegal, because teams is a collection-valued relationship field. To reach the sport field, the FROM clause could define an identification variable named t for the teams field:

FROM Player AS p, IN (p.teams) t WHERE t.league.sport = 'soccer'

Are you seeing what I’m seeing here?!! They are using the ‘league’ field of t (a collection of teams) to get to it’s ‘sport’ field. Essentially, JPQL is an object-oriented query language. And how about using fields in your select to construct a new object?

Constructor Expressions

Constructor expressions allow you to return Java instances that store a query result element instead of an Object[].

The following query creates a CustomerDetail instance per Customer matching the WHERE clause. A CustomerDetail stores the customer name and customer’s country name. So the query returns a List of CustomerDetail instances:

SELECT NEW com.xyz.CustomerDetail(c.name, c.country.name)
FROM customer c
WHERE c.lastname = ‘Coss’ AND c.firstname = ‘Roxane’

What an exciting time for software development!

Why I chose O/R Mapping (JPA) over Stored Procedures for CRUD

I don’t choose easily when a large project is on the line. It took me months to choose JSF over Swing. And most recently, I’ve chosen JPA over Stored Procedures. I know it may be frustrating for my teammates to have to witness my arduous decision making process. With this decision in particular, I went back and forth many times before I was able to settle on something I was comfortable with. I have to look at things from many different angles, and come up with answers for many different questions.

Why did I feel I needed to choose between JPA and Stored Procedures? Are they really mutually exclusive?

Before we even decided to migrate our application from Powerbuilder to the web, we had already planned to move the data layer to Stored Procedures. There are really only 2 things a Powerbuilder programmer can do to separate presentation from business. 1.) Break out business logic into Non-Visual Objects (NVOs) 2.) Put all SQL into Stored Procedures. Otherwise, you get this classic pile of Client-Server spaghetti where all the business logic is hidden behind Button controls and such. So, it just made sense to go with Stored Procedures…initially. Once, we decided to migrate to Java, we just carried forward with the assumption that we would use Stored Procedures. That’s where the training dollars had gone, and most of us had already had experience with SPs. Well, it didn’t take long to realize that SPs were not exactly all the rage with n-tier, enterprise java applications. Instead, we found that POJO persistence was the standard for middle-tier. Oops!

At first, I thought maybe these two technologies might fit together. So I contacted Doug Clarke, Director of Product Management for Oracle’s Toplink. He wrote the following:

It is possible to use stored procedures with JPA as of our Oracle TopLink 11gR1 version (currently in preview) as well as in the new open source EclipseLink project. We have added @NamedStoredProcedureQuery annotations to facilitate this.

Here is the 11gR1 docs:
http://www.oracle.com/technology/products/ias/toplink/doc/11110/devguide/jpatlext.htm#BABEJGGI
Here is the similar docs for EclipseLink
http://wiki.eclipse.org/Using_EclipseLink_JPA_Extensions_%28ELUG%29#Using_EclipseLink_JPA_Extensions_for_Stored_Procedure_Query

But he went on to write:

The challenge with combining stored procedures with JPA is that you may be sacrificing some of the inherent benefits of JPA. I am a big fan of using stored procedures where they offer performance optimizations of bottle-neck queries. Using them as a general data access mechanism will prevent the JPA provider from being able to leverage its cache for reading and some of the SQL generation optimizations when reading graphs of objects.

It became clear to me that it would be better to just choose one way or the other. So I began to search for arguments for and against. I soon discovered that this was not a simple choice. There are good arguments on both sides, and in the end, both paths are valid and can lead to a successful system.

Why even consider using Stored Procedures?

Some of the best arguments for using SPs come from Eric Bruno. In his article Use Stored Procedures for Java Persistence, he listed the following problems with using JPA that Stored Procedures solves:

Using stored procedures to encapsulate data manipulation helps to solve the following problems often found when this logic—the associated queries—exists within the Java layer:

  • Client/server data shuttling: When queries are made from Java code, potentially large amounts of data are shuttled back and forth between the application server and the database. This can greatly impact performance.
  • Transactions opened from outside the database: A transaction is often created for each query executed from the Java code within an application server. When a single logical operation requires multiple queries, many individual transactions may be created.
  • Tight coupling of database schema and Java code: The database structure is exposed to the Java layer. Hence, future changes to database structure may require changes to the Java code. As with Java class structure and member data, the database structure should be abstracted from other layers in the software.
  • Software release coordination: Because the Java code depends on the database structure, database releases that contain schema changes must be coordinated with releases of the dependent Java code. It’s often difficult to coordinate such releases. Furthermore, if one system must be rolled-back due to a problem, dependent systems must be rolled back also.

The article was written in September 2005 (kind of old), so I shot him an email to see if he still supported his assertions. He wrote:

I still support the use of stored procedures for data-related business logic. In short, any queries that involve joins, multi-table lookups, or any other reason that multiple calls to the database are required, are best done in the database. This helps to limit the IO usage from un-needed data shuttling, as well as extra 2PC transactions being created from the Java layer.

Also, my database administrator made the following strong argument:

Why not let the RDBMS do what it does best? What better to manage your Relational Database than a Relational Database Management System?

I also know that a few of our guys were around when our system was migrated from VS Wang to Powerbuilder. They might be thinking, “Ya know? If we’d used stored procedures from the beginning, it would be a hell of a lot easier to migrate from Powerbuilder to Java. What’s going to happen when Java goes out of style and we need to migrate this sucker again? We’re going to be stuck with the same situation we’re in now!”

Stored Procedures run fast. They can be used by just about any kind of application. They’re tried and proven.

The list is long.

Why I ultimately chose ORM

It’s not hard to find good reasons to use ORM technology such as JPA, Hibernate, and Toplink. The internet is buzzing with it. Here are some reasons I want to use it:

  • Boosts productivity – JPA frameworks handles the low-level work of writing lots of sql, mapping data to objects and objects to data, and managing database connections properly. It is also becoming increasingly well-supported by Java IDEs… a drag here, a drop here, a wizard there, a tweak there, and POOF!
  • All the cool kids are doing it – It’s no fun to be outside of the current trend. When you go with the flow, you get a lot of community support and involvement. Conferences, JUGS, forums are a lot more valuable when you’re doing what everyone else is doing. If 100,000 other guys are doing exactly what you’re doing, then someone’s bound to have solved the same problems you’re encountering.
  • It just makes sense -Think about it,
    • Objects are relational in nature
    • Relational Databases are relational in nature
    • JPA capitalizes on their shared relational nature

Ok, so here are my answers to some of the strongest arguments made for using Store Procedures instead of JPA:

Argument 1: Stored Procedures are faster and they minimize the number of trips to the database.

Answer: This would be true if there was no caching for JPA…but there is. With SPs, every interaction with the data will require a call to a stored procedure which resides in the database tier. Both Hibernate and Toplink employ very sophisticated 1st and 2nd level caching to prevent unnecessary trips to the database. Nothing is faster than no call at all!

Argument 2: Why not let the database do what it does best?

Answer: Persisting data is what Database Servers do best. Persisting business logic is what Application Servers do best. In addition, why would I want to use a procedural language to take something relational, flatten it out into unrelated result sets, and put them all back together again into something relational? And why would I want to relinguish Object-Oriented design for an entire tier of my application?

Argument 3: “Ya know? If we’d used stored procedures from the beginning, it would be a hell of a lot easier to migrate from Powerbuilder to Java. What’s going to happen when Java goes out of style and we need to migrate this sucker again? We’re going to be stuck with the same situation we’re in now!”

Answer: Next time we migrate, it may be the database technology that we’re migrating. Then we’ll be glad that we used a platform independent technology instead of tying everything to Oracle PL/SQL Stored Procedures. Now get off your lazy ass and learn something new for a change! (talking to myself here)