Vaadin SQLContainer

Jonatan · September 6, 2010, 9:11am

Hi guys!

Just wanted to let you know that we just released
Vaadin SQLContainer
v0.3. This is the first preview of the SQLContainer, which should replace QueryContainer in Vaadin 7. This container can be used when JPA feels like overkill for your small app.

It currently supports freeform queries, and version 0.5, to be released in a couple of weeks, will support table queries as well. Table queries will automatically support writing, sorting, filtering and lazy loading for the following database engines: HSQLDB, MySQL, MSSQL, Oracle and PostgreSQL. With freeform queries, you must implement some of the functionality yourself by implementing the FreeformQueryDelegate interface.

Unfortunately documentation is very sparse in this first experimental release, but before 1.0 we will have a full manual for you. While this is the case, I would very much appreciate your help in testing and reporting any bugs or issues you find!

A simple demo can be found
here
and the source code to the demo can be found
here
. The demo contains a simple example implementation of the FreeformQueryDelegate interface that you can use as a stepping stone.

Hope you like this first taste, there’s more to come!
/Jonatan

Jonatan · September 22, 2010, 7:57am

Hi guys,

A short update on the SQLContainer: The feature complete version 0.8 was just released. Please download it from
the directory page
and try it out. If you find bugs, please report them at
dev.vaadin.com
.

A stable version 1.0 will be released in a couple of weeks with fixes for the bugs found in this version.

Have fun!
/Jonatan

Thorsten · November 12, 2010, 12:27pm

Hi,

I’m just trying to implement a table based on
SQLContainer addon
with a
J2EEConnectionPool
connection. Unfortunately I got some errors. Maybe someone can help me.

Here is my code:

SQLContainer sqlContainer = null;
J2EEConnectionPool connectionPool = new J2EEConnectionPool("java:comp/env/jdbc/testcon");

/* Approach 1 */
try {
  TableQuery tq = new TableQuery("MYTABLE", connectionPool);
  tq.setVersionColumn("id");
  sqlContainer = new SQLContainer(tq);
} catch (SQLException e) {
  e.printStackTrace();
}

/* Approach 2 */
// try {
//   sqlContainer = new SQLContainer(new FreeformQuery("SELECT * FROM MYTABLE", 
//   Arrays.asList("id"), connectionPool));
// } catch (SQLException e) {
//   e.printStackTrace();
// }
      
Table table = new Table("Example", sqlContainer);
addComponent(table);

If I use “Approach 1” then I got this error message:

Cause: java.lang.RuntimeException: Failed to fetch property id’s.
at com.vaadin.event.ListenerMethod.receiveEvent(ListenerMethod.java:507)
…
Caused by: java.lang.RuntimeException: Failed to fetch property id’s.
at com.vaadin.addon.sqlcontainer.SQLContainer.getPropertyIds(Unknown Source)
…
Incorrect syntax near ‘LIMIT’.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:197)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1493)

If I use “Approach 2” then I got this:

Cause: java.lang.IllegalStateException: A transaction is already active!
at com.vaadin.event.ListenerMethod.receiveEvent(ListenerMethod.java:507)
at com.vaadin.event.EventRouter.fireEvent(EventRouter.java:161)
…
Caused by: java.lang.IllegalStateException: A transaction is already active!
at com.vaadin.addon.sqlcontainer.query.FreeformQuery.beginTransaction(Unknown Source)
at com.vaadin.addon.sqlcontainer.SQLContainer.getPage(Unknown Source)
…
java.lang.IllegalStateException: A transaction is already active!
at com.vaadin.addon.sqlcontainer.query.FreeformQuery.beginTransaction(Unknown Source)
at com.vaadin.addon.sqlcontainer.SQLContainer.getPage(Unknown Source)

Any ideas? I use SQL Server 2005 and the DB table MYTABLE has a primary key called “id”.
Thanks, Thorsten

••••••••••
Eclipse 3.5.1 • Vaadin 6.4.7 • Vaadin Eclipse Integration 1.2.0 • JRE 6
Windows XP • IE7 (7.0.5730) • Tomcat 5.5.28 • SQL Server 2005

Jonatan · November 15, 2010, 9:47am

Hi Thorsten,

For the first case, you need to use the public TableQuery(String tableName, JDBCConnectionPool connectionPool, SQLGenerator sqlGenerator) constructor, and specify MSSQLGenerator as the sqlGenerator. Using the other constructor will create a DefaultSQLGenerator that supports HSQLDB, MySQL and PostgreSQL. For Oracle and MSSQL you need to use the specialized implementations.

As for the second issue, this is a (very bad) bug that somehow got released. It is fixed in the SVN and a version containing this fix ought to be released sometime this week.

HTH,
/Jonatan

Thorsten · November 15, 2010, 1:00pm

Hi Jonatan,

great… Approach 1 works fine now. Thanks for your help!

Thorsten

Jonatan · November 19, 2010, 6:48am

Hi!

Version 1.0.pre1 can now be found from
here
. This should fix the problem that Thorsten had with FreeformQuery and also allow for using FreeformQuery without specifying any ID columns.

/Jonatan

Thorsten · November 19, 2010, 7:08am

Hi,

yes - FreeformQuery is working now.

Thanks for your help!
Thorsten

Alberto2 · November 28, 2010, 7:22pm

Hi. I’m using SqlContainer with Mysql and would like to know if Is it possible to use a view or a stored procedure instead of a table and in that case, how to do it.

Thanks in adavance and congratulations for you great product.

Jonatan · December 2, 2010, 8:43am

Hi Alberto,

I’m sorry to say that currently there is no support for views or stored procedures in the SQLContainer. It does, however sound like a good thing to add support for in the next version, so could you please file an enhancement ticket on
dev.vaadin.com
? (component = SQLContainer add-on)

Thanks
/Jonatan

Thorsten · December 2, 2010, 9:46am

Hi,

you can handle database Views if you use
FreeformQuery
.

Example:

myContainer = 
  new SQLContainer(new FreeformQuery("select * from myView", Arrays.asList("myid"),connectionPool));

Just make sure that there is a field with unique values (“myid” in the example).

Regards,
Thorsten

Manvel · January 10, 2011, 5:13pm

Hi.
Please tell me is there an example with Mysql… ? And which database engine is better to use ?

Jonatan · January 12, 2011, 12:27pm

Hi Manvel,

Unfortunately there’s no ready made example using MySQL. However, the only changes you need to make to the code in the demo applications should be the initialization of the SimpleJDBCConnectionPool and fix the table creation SQL statements to work for your chosen DB.

As for which DB is “the best”, this is very subjective. It’s all a matter of personal preference. I’ve had success with MySQL, PostgreSQL and others as well.

HTH,
/Jonatan

Stefano · January 26, 2011, 8:35pm

Hi…

This Add-on is not bad, but has some limitation that are terrible. If I’ve to have a lazy loading using FreeFormQuery, how can I do?

I need to filter my data in this way:

select * from MyTable WHERE user = 'Stefano' AND ( field1 = '1234' OR field2 = '1234' )

Is there a solution for this ?

Thanks

Peter13 · January 26, 2011, 11:07pm

hello

I have read the blog post about the 1.0 release
I followed the link found in it to the tutorial

My one and only question is that where can I found the complete source code for the demo application made with this wonderfull addon ?

Peter13 · January 26, 2011, 11:13pm

One more little bug report :

I have noticed that the ‘Discussion Forum’ link on the addon’s page points to a wrong place

jean-francois.lamy · January 27, 2011, 2:01am

For an alternative approach:

I just uploaded a major update to the add-on
JPA 2.0 Criteria LazyQueryContainer
that allows you to dynamically build the query you want without writing any SQL: JPA 2.0 has an API that allows you to specify your query dynamically as Java objects (i.e there is a CriteriaBuilder that can create the from, where, select, group, etc.), without maniplating textual query language (which is extremely error-prone and not type-safe)

I wrote two containers on top of LazyQueryContainer that use this approach. One manages entities (i.e. it returns objects from one class). The other returns a Tuple, that is, the set of objects that are connected together by a join or some other arbitrarily complex conditions. Vaadin’s JPA Container has some notion of joining queries, but this approach allows just about anything you might think of. The containers allow computed expressions in the SQL, and support sorting on any property that is Comparable.

If you look at the sample applications, I’ve created five variations on how to create queries and perform filtering.

Jonatan · January 27, 2011, 7:57am

Hi!

If you need lazy loading while using FreeFormQuery you must implement FreeformStatementDelegate (and use it by calling myFreeFormQuery.setDelegate(myFreeformStatementDelegate)). It is not feasible to generically implement lazyloading, etc for any type of SQL query, so this is left up to you to implement for your specific cases.

A simple example of a FreeformStatementDelegate implementation can be found in the demo application source
here
.

HTH,
/Jonatan

Jonatan · January 27, 2011, 7:59am

Hi!

The source code can be browsed at
dev.vaadin.com/browser/…
and you can check out the entire SQLContainer project by anonymous SVN at http://dev.vaadin.com/svn/addons/SQLContainer/trunk

(Oh, and I fixed the forum link, thanks for the heads up!)

HTH,
/Jonatan

Stefano · January 27, 2011, 12:44pm

Wow!!!

In my opinion, this is the best solution for data lazy loading. I’ve just create that to use direct query and the result is fantastic: performance are very very very fast!

Thanks a lot for this component:
Vaadin SQLContainer rulez!

Peter13 · January 27, 2011, 1:59pm

ok, I have check-ed out everything from the given url into a folder

now what ?

How to create a vaadin project with the demo application from these files?