ComparisonTypes
STARTS_WITH, ENDS_WITH, CONTAINS
look like leaky abstraction - pain without gain ;-(
(ENDS_WITH, CONTAINS are also DBA hell)
I think one single “LIKE” instead of them will be better.
With LIKE user can supply standard more powerful
like-expression e.g. “som_ lett%” (where _ and % are special).
Some SQL servers have much more powerful like syntax (with ranges, etc). You needn’t rewrite SQL server in plugin.
Then getPreparedStatementValue method can be removed.
In “passes” method you can use simple hand-made like analog (easy to write yourself, also can be found in string manipulation libraries)
Sorry for so many posts, but You see: I love SQLContainer
It will be good to have bundled in Vaadin core PropertyConverter (from org.vaadin.customfield.PropertyConverter)
and ItemConverter (simple Item wrapper, delegates all calls to other item, but You can “wrap” some properties in PropertyConverter).
Then SQLContainer can introduce RowIdPropertyConverter:
in your Form’s setItemDataSource you wrap newDataSource in ItemConverter and wrap it’s foreign key properties in RowIdPropertyConverter. Then all conversion between refId:int <->RowId will be made automaticly.
See “cities” ComboBox and PersonForm in tutorial for problem understanding.
RowIdPropertyConverter is a very interesting concept. I’m facing the exact same issue with my pet project container. There is a similar concept in BeanContainer.
I believe that the concept should be looked at for all database containers with foreign keys !
Can anyone explain when one would use SQLContainer.addReference() and its correct usage? I am was hoping that I could use this method to relate two SQLContainer objects together using a foreign key and that it would generate SQL performing a join. For example:
TableQuery q1 = new TableQuery("ui_group", connectionPool, new OracleGenerator());
q1.setVersionColumn("VERSION");
SQLContainer uiRuleGroupContainer = new SQLContainer(q1);
TableQuery q3 = new TableQuery("ui_rule", connectionPool, new OracleGenerator());
q3.setVersionColumn("VERSION");
SQLContainer uiRuleContainer = new SQLContainer(q3);
uiRuleContainer.addReference(uiRuleGroupContainer, "GROUP_ID", "GROUP_ID");
In my schema, the UI_RULE table has a foreign key column called GROUP_ID that references the UI_GROUP table (whose primary key is GROUP_ID). I know that I can use FreeformQuery and implement a FreeformStatementDelegate and write my own raw SQL to join these tables together but it would be cool if I could simply use the above approach.
Ultimately, I want to display the joined database tables in one Vaadin Table.
Hi.
I would like to populate tree component from Database please tell me in which way is better to do. Do I have to create several tables for that or It’s possible to do using one table. Which way is more comfortable to use using SQLContainer. And Is there an example populating tree using SQLContainer.
Please help me with this question.
It should be fairly easy to implement the Container.Hierarchical interface for the SQLContainer (for your specific case) if you extend SQLContainer. This would be for the case where you have your tree data in a single table. Most of the methods would be pretty straight forward to implement and for the trickier ones, see HierarchicalContainer for an example implementation.
Unfortunately there are no examples of how to implement this specifically with SQLContainer.
Unfortunately the SQLContainer does not currently support automatic generation of join statements. This feature has been planned to be implemented at some point but I don’t think there is a schedule for when this would be done.
The addReference and related methods are purely on the Java-side, not touching the database as such. They are merely a means for the lazy of us to do certain things a bit easier. This should be quite well explained in the chapter 4.4 of the SQLContainer manual.
In short, the functionality is basically identical to creating two SQLContainers, then manually fetching a foreing key from one container and fetching the correct item based on the foreign key from the other container. Hence, no SQL joins are involved. This merely allows you to skip a few lines of code by calling the getReferencedItem on the first container.
Bottom line: To use SQL joins you currently have to use FreeFormQuery and implement the FreeformStatementDelegate to generate the required statements.
Now I went quite a forward with that coonection. Here is how.
It’s the class that extends SQLContainer and implements Container.Hierarchical Interface :
public class HierarchicalConnection extends SQLContainer implements Container.Hierarchical {
public HierarchicalConnection(QueryDelegate delegate) throws SQLException {
super(delegate);
}
public Collection<?> getChildren(Object itemId) {
ArrayList<Object> myList = new ArrayList<Object>();
/// CREATING ITERATOR TO ITERATE TO COMPARE PARENTID WITH ID's ///
for (Object id : getItemIds()) {
if ((Integer) getItem(id).getItemProperty("parentID").getValue() == (Integer) getItem(itemId).getItemProperty("id").getValue()){
myList.add(id);
}
}
return myList;
}
public Object getParent(Object itemId) {
return getContainerProperty(itemId, "parentID");
}
public Collection<?> rootItemIds() {
ArrayList<Object> myList = new ArrayList<Object>();
//// ITERATING TO GET ROOT ITEMS /////
Object aa= 0;
for (Object id : getItemIds()) {
if ((Integer) getItem(id).getItemProperty("parentID").getValue()==0){
System.out.println(getItem(id).getItemProperty("parentID"));
myList.add(id);
}
}
return myList;
}
public boolean setParent(Object itemId, Object newParentId)
throws UnsupportedOperationException {
return true;
}
public boolean areChildrenAllowed(Object itemId) {
if (Integer.parseInt(getItem(itemId).getItemProperty("leaf").toString())==1) {
return false;
}
return true;
}
public boolean setChildrenAllowed(Object itemId, boolean areChildrenAllowed)
throws UnsupportedOperationException {
return true;
}
public boolean isRoot(Object itemId) {
return true;
}
public boolean hasChildren(Object itemId) {
return true;
}
}
And here is what I get :
So It works, But it works only for a small database table, but my real table contains 1000 rows. When I Use it with tableQuery and click on nodes. My JVM and MYSQL processes go to near 50% of CPU and operations are done veryslowly (displaying childes).
How can I figure out this situation.
I think If I’ll create 3 tables for each my nodes maybe it’ll be solution ???
What you think ?
I’ve already solved the problem about big database.
I’ve created another container of that table and added filter on that container and got the children nodes :
SQLContainer child = myCont.getChildrenPlan(); /// ANOTHER CONTAINER
Filter aa = new Filter("parentID", ComparisonType.EQUALS, (Integer) getItem(itemId).getItemProperty("id").getValue());
child.addFilter(aa);
myList.addAll(child.getItemIds());
child.removeAllContainerFilters("parentID");
So it’s begin works very fast. I think the problem was because Iterator. I think it was to hard to iterate over big table for JVM. But for MYSQL it wasn’t a problem.
Hi DEAR SQLContainer Developers .
I’m having such problem → I’m testing timeLine Addon using SQLContainer as datasource, But I’m having a problem, because timeLine as I explored is using Indexed container. So please give me an Idea how to make SQLContainer works with timeline, I’ve Created my own class-> IndexedSQLContainer that I’ve extended from SQLContainer and implements Container.Indexed. But I’m getting null poin exception.
Here is my IndexedSQLContainer class:
public class IndexedSQLContainer extends SQLContainer implements Container.Indexed {
public IndexedSQLContainer(QueryDelegate delegate) throws SQLException {
super(delegate);
// TODO Auto-generated constructor stub
}
}
And here is my timeLine declaration code:
public class VichakagrutyunTimeLine extends Timeline {
public VichakagrutyunTimeLine () {
setSizeFull();
IndexedConnection indexedConnection = new IndexedConnection();
indexedConnection.regIndexedContainer();
IndexedSQLContainer indexedContainer = indexedConnection.getIndexedContainer();
System.out.println(indexedContainer.getItem(indexedContainer.firstItemId()).getItemProperty("name").getValue());
addGraphDataSource(indexedContainer);
setGraphTimestampPropertyId(indexedContainer, "date");
setGraphValueProperyId(indexedContainer, "name");
}
Please tell me the way that I can make timeline works with SQLContainer and which method’s of indexed container do I have to declare to make things work.
Basically, I have successfully gotten an SQLContainer to read one of my database tables, display it in a UI table, add records to the database, and then fail miserably when I try to delete a record. The error I’m getting looks like this:
Exception: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘AND “startTime” = ‘1/2/3333 1:23’ AND “stopTime” = ‘1/2/3333 1:24’’ at line 1
As you can see from the other discussion thread, a test program was given to me that I implemented and it worked fine - deleted a database record without any problem. Replacing that database table with my own table fails. Also note that in my (failing) program, I have 4 other database tables that add and delete records successfully - so it is something specific to this database table that I’m doing wrong.
I could really use some help in tracking this down - I’m brand new to Vaadin and trying to figure out how it works along the way… I’m willing to assume that I’ve done something stupid (history tells me this is often a
valid assumption :)) but I’d be grateful if someone could show me exactly how I’m being stupid…
A couple of specific questions:
Is there some way of figuring out exactly what the SQL prepared statement looks like? I’m seeing what looks like the end of it here… It would be nice to see if the whole statement executes properly from the command line.
The fields startTime and stopTime are variable length character strings at the end of my database record definition. Since I’m providing the oid of the key field of the record (named ‘id’ in this case), why is it trying to execute a statement including the start and stop times - shouldn’t it be deleting the record where id = ???
I’ll be happy to provide any other information you need - just ask. Feel free to contact me directly if you want to avoid taking up bandwidth on this message board - I can provide a summary once I have a solution…
This indeed seems to be a problem with Timeline as it requires the timestamp property to be java.util.Date and does not accept any class that extends java.util.Date. I created
#6699 for this, should be fixed in the next release, which we hope to get out in 1-2 weeks.
I don’t directly see how it could be easily worked around but if you copy/paste the Timeline.class and change the
if (clazz == java.util.Date.class) {
in
setGraphTimestampPropertyId
to
if (java.util.Date.class.isAssignableFrom(clazz)) {
it should work.
I have a bit more information on my container problem - perhaps someone can point me in the right direction. My mysql database table is defined like this:
mysql> describe maintenance;
±-----------------±--------------±-----±----±--------±---------------+
| Field | Type | Null | Key | Default | Extra |
±-----------------±--------------±-----±----±--------±---------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| parentId | int(11) | NO | | NULL | |
| uid | int(11) | NO | MUL | NULL | |
| recType | int(11) | NO | | NULL | |
| sgid | int(11) | NO | MUL | NULL | |
| maintName | varchar(64) | NO | | NULL | |
| enteredHostList | varchar(4096) | NO | | NULL | |
| expandedHostList | varchar(4096) | NO | | NULL | |
| serviceList | varchar(4096) | YES | | NULL | |
| startTime | varchar(32) | NO | | NULL | |
| stopTime | varchar(32) | NO | | NULL | |
±-----------------±--------------±-----±----±--------±---------------+
11 rows in set (0.00 sec)
When I try to delete a record, I get this:
Delete Maintenance Record - OID = 100
Exception: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘AND “startTime” = ‘1/2/3333 1:23’ AND “stopTime” = ‘1/2/3333 1:24’’ at line 1
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘AND “startTime” = ‘1/2/3333 1:23’ AND “stopTime” = ‘1/2/3333 1:24’’ at line 1
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
So I went in an rebuilt the database and deleted the last 3 columns and repopulated it with exactly the same data (minus the serviceList which is always NULL at the moment, and the start/stop time fields. I then tried to delete a record and it worked just fine…
To make a long story short, I have tried building the database with various columns in/out (and sideways :)) and the result seems to be that if the serviceList column has a value of NULL, then the delete fails. If the serviceList column is not null, the delete succeeds.
Note that the serviceList column is allowed to be NULL - does that cause problems for the SQLContainer?
Is there something obvious that I’m missing here, or something I’m doing wrong in using the container? Or have I stumbled over a bug in the implementation?
At least I have a workaround now - I can force a dummy value into that column of the table and then it should work…
Your problem sure does seem like a bug in the SQLContainer implementation, I created
a ticket for the issue. It might have something to do with applying all filters even though only the ID is necessary, as you mentioned.
For future reference, you can call SQLContainer.setDebugMode(true) to make the SQLGenerators print debugging messages, including the queries being executed. I’d appreciate if you’d run your scenario with debugging turned on and add a comment with the query being run to
the ticket .
