Vaadin SQLContainer

Vaadin 6 / 7 / 8 Framework Data Binding
81

Hello Jonathan,

maybe a more general question:

How is SQLContainer designed to handle joined database tables?
I have been exploring a bit in the source code and for example in SQLContainer.java line 1023:

/*
 * Make sure not to add the same colName twice. This can easily
* happen if the SQL query joins many tables with an ID column.
*/
if (!propertyIds.contains(colName)) {
     propertyIds.add(colName);
}

So not all columns are added to the container?
Or how would you handle table joins?
Is there a way to differentiate between ambiguous database columns? Like in plain SQL to prefix columns with the tablename:

SELECT tableA.id, tableB.id FROM tableA LEFT JOIN tableB ON...

I could of course add ‘generated columns’ but then a lot of extra query’s are generated and I lose sorting and filtering capabilities…

Thank you for your help,
Frederik

82

Hi Frederik,

Sorry for the (very) long response time. Your problem seems to be due to the quoting of the column names by default. This causes problems when you have a joined table and generates the column identifiers as “tablename.columnname” instead of the correct “tablename”.“columnname”. This is a bug and I created
#7756
 for it. In the meantime you can disable the quoting by adding this line to your FreeformStatementDelegate constructor:

QueryBuilder.setStringDecorator(new StringDecorator("", ""));

As for the more general question, it is correct that SQLContainer only supports unique property IDs. If you require to show both, you should “rename” the colliding IDs in your query. An example:

SELECT tableA.id AS a_id, tableB.id AS b_id FROM tableA LEFT JOIN tableB ON...

The above query will, when in an SQLContainer attached to a table, render the columns “a_id” and “b_id” in the table.

HTH,
/Jonatan

(And sorry for the long response time)

83

hi i want to show in table data from two different tables. having relationship via foreign key…

Is it possible using sql container.?

Please provide sample code.

If not then which container to use.

84

I’m quite sure this can be done by the sql query container and the jpa container. Another container that supports this is the
JPA 2.0 Criteria Lazy Container
 add-on supports the BeanTupleContainer exactly for that purpose (joined tables)

85

thanks jean for reply but i am looking for working example for sqlContainer can you help with that.
Also I want to know how to use addReference() ,getReferencedItemId(),getReferencedItem(),setReferencedItem()

Currently i am not able to understand these and how to use these.

86

Hi Richi,

There’s an example of this in the AddressBook demo (
browse source
). The relationship between Person and City is handled in the example. This demo application is described in the
SQLContainer tutorial
.

HTH,
/Jonatan

87

Hi Manvel:

I’m trying to implement the solution you posted for the tree (using SQL Container), and eclipse throws me errors in “ComparisonType” and in “addFilter”. Also, I don’t get where you do the connection with the table…

Could you help me up here?

regards,

Hugo

88

Hugo,

The Filtering API was redesigned in Vaadin 6.6 and SQLContainer dropped the old custom filtering API and moved to completely use Vaadin’s filtering API. This happened after Manvel’s posts, so this is probably why you get error messages on those lines. See
the com.vaadin.data.util.filter package
 for more info on Vaadin filtering.

HTH
/Jonatan

89

Jonatan!
Please!

Fix current J2EEConnectionPool nasty behavior with autocommit.

Set autoCommit BACK in releaseConnection.


http://dev.vaadin.com/ticket/7936

90

BoneCP will fix your pool behavior, but in version 0.8…
I use 0.7.1, so YOU are my only hope.

FYI

https://bugs.launchpad.net/bonecp/+bug/790585

PS: Of course I have workaround - J2EEConnectionPool descendant with overriden method.

91

Hi there,
I’ve got a problem with the SQLContainer add-on when running the application with concurrent users. When an user applies a filter on SQLContainer, data change for other users too. Any idea about how to solve this?

I hope you can help me. Thank you very much in advance

92

Hi Carmelo,

Do you, by any chance, store the reference to the SQLContainer in a static field? Or is it accessed through a static field?
This means that all users of the application will use
the same instance
 of the SQLContainer for everything, which would explain why
you are seeing the same filters being applied to all users.

HTH,
/Jonatan

93

Problem solved :grin:

It was due to a static reference to the DBHelper class.

Thank you too Jonatan, even if I read your post after going crazy searching and founding the solution :smiley:

94

Hey,

I´d like to know, if there is any chance to escape table names with a grave accent (`) as it is quiet usual to do this in mysql.

I already tried with DefaultSQLGenerator(“", "”), wich wont work and even a TableQuery with a table name like “right” will change the table name to an empty string.

As you can see my tables name is “right”, wich is an SQL constant. But a statement like “SELCT * FROM right” would - of course - work.

Thank you for your help,
Gregor

95

Hello, today a had a big problem with freeformquery.

I have a little connectionpoll and an “connection limit reached” error was thrown very often.

This is the code:



		FreeformQuery ffq = null;
		SQLContainer c = null;
		try {
		
			ffq = new FreeformQuery(sql, cp, nomeColonnaPK);
			System.out.println("Totale righe dalla FreeFormQuery: " + ffq.getCount());
			c = new SQLContainer(ffq);
			c.commit();
			return c;
		} catch (Exception e) {
			System.out.println(e.getMessage());
			throw new Exception(e.getMessage());
		}

When I called ffq.getcount() quickly, the error above was thrown.

When I removed that line, the code worked right.

In freeformquery is the connectionpool handled right ?

Why without that line the console print the stacktrace of sqlcontainer anyway (i don’t want the stacktrace printed on console)?

96

Hi,

I am encountering an issue while committing the updated row back to the table by calling commit(). Well commit() method is not working as expected and its not committing the data back to table.

I have posted the details in the following thread, please help me out. Thanks.

https://vaadin.com/forum/-/message_boards/view_message/2241180

97

Hi guys, any idea when this addon will be available for Vaadin 7? Otherwise is there an alternative in Vaadin 7 that’s worth trying?

Many thanks,

Andrew

98

I think I’ve just found it in Vaadin 7 by default… should have looked there first!

99

Hi,
I want to filter a database query using Double data stored as Strings in MySQL Database with some double data in-memory.

Is it possible ?

Vikas

100

Is there any way to save vaadin Spreadsheet data in local mysql database if any CURD example application available the please share with me.