com.smartgwt.client.docs

Interface SqlVsJPA

public interface SqlVsJPA

SQL DataSource vs JPA, EJB, MyBatis and other technologies

If you are free to choose which persistence mechanism your application will use, you should consider using the Smart GWT SQL DataSource instead of a more heavyweight, bean-based solution. This article discusses the advantages of doing so.

Simplicity

With the Smart GWT SQL DataSource, simple CRUD connectivity can easily be set up in a way that requires zero server side code. Only a DataSource descriptor (.ds.xml file) needs to exist; this descriptor can include field-level details explicitly, if you choose to provide them, or can be inferred from your schema via DataSource.autoDeriveSchema. The descriptor actually serves double duty by also providing the configuration for UI components - in other words, this is information that you would need to express anyway.

Semi-technical product managers, testers, business analysts and IT staff who have no familiarity with Java can easily comprehend DataSource definitions and even customized SQL queries, allowing them to go further with prototyping efforts, provide more specific feedback and capture more relevant diagnostics when reporting issues.

This level of simplicity is lost when using more heavyweight systems. JPA / EJB best practices indicate creation of a bean class for every domain object, as well as related "services" or "session beans", DTOs (Data Transfer Objects) and other unnecessary scaffolding. MyBatis avoids some of this scaffolding, but requires every SQL query to be written by hand. In contrast the SQL DataSource supports basic CRUD queries out of the box.

Performance

Systems like JPA work nicely when dealing with a single object at a time, but enterprise applications routinely work with lists or trees of objects that draw data from multiple tables. In these situations, it's trivial to express an efficient SQL query for retrieving the desired results (as shown in @see this example). Fetching the same data using getter methods on Java Beans often leads to nightmare performance scenarios (such as 3 or more separate SQL queries per object retrieved).

Trying to "trick" the persistence system into generating efficient queries doesn't make sense - this just leads to a far more complex and fragile solution that now requires deep knowledge of how the ORM system generates SQL as well as SQL itself.

SQLDataSource allows you to directly write SQL when it makes sense, and to use beans when object oriented approaches are clearer and simpler. When you do write SQL directly, you override just the parts of the query that you need to change - you still leverage SQLDataSource's ability to generate cross-database SQL for complex search criteria, efficient data paging and sorting, even in a complex reporting query (see this example).

Portability

Smart GWT DataSources provide cross-database portability like JPA and other solutions. However, DataSources can also be replaced with an entirely different integration strategy or entirely different server platform, such as a SOA architecture where the browser contacts WSDL web services directly. The clear data requirements definition represented by a DataSource makes such drastic technology changes much easier with the SQL DataSource than with any other technology.

Power

The SQL DataSource has out of the box support for server-side advanced filtering without the need to write any code (see the SQL Advanced Filtering example), and Smart GWT provides pre-built user interfaces for filtering. The effort required to develop similar functionality with another persistence mechanism would vary from substantial to spectacular.

You can leverage advanced, automatic SQL generation, such as advanced filter criteria, GROUP BY and ORDER BY clauses, and selection of row ranges, even in very heavily customized queries. The Dynamic Reporting example shows this.

With the SQL DataSource and Transaction Chaining, you can chain together multiple SQL queries, or a mixture of SQL queries and other data access, with simple declarations right in the DataSource, as this example demonstrates.

Because you write the SQL, you can use database-specific features when absolutely necessary. Features such as query optimizer hints or stored procedures are thus accessible but, importantly, are within the same processing model used for all other data access.

Security

Because the central DataSource definition expresses all the available operations, how they are performed and who has access to them, things are clear and simple. It's much easier to understand and audit a DataSource definition than a slew of Java classes.

There is no information leakage from server to client with the SQL DataSource. All server-side declarations, such as SQL templates, are automatically stripped out of the DataSource definition before the browser sees it.

Custom SQL in a Smart GWT SQL DataSource is protected from SQL injection attacks. It is impossible for a developer to write a SQL template that is vulnerable to SQL injection without going through the $rawValue feature, a rarely used feature that is very prominently flagged in the documentation as requiring special care. Other ORM systems tend to require hand-coded SQL queries for advanced use cases such as reporting; these hand-written queries are where most security holes appear. By providing a safe environment for SQL customizations, SQL DataSource removes these risks.