public interface SqlVsJPA
With the Smart GWT SQL DataSource, simple CRUD connectivity can be set up via a wizard and requires zero server side code. Only a DataSource descriptor (.ds.xml file) needs to exist; this descriptor can be generated by the wizard or created by hand. 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. Ibatis 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.
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.
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).
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.
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.
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.
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
$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.