DynamicCriteria adding IN operator problem

[anepally yadagiri]

Dear All,

Greetings for the day.

Problem 1) I am trying to add DynamicCriteria IN operator but I am getting problem.

Ex:       StringBuilder orderBy = new StringBuilder(DynamicCriteria.ORDER_BY);
            orderBy.append("A.username");
            result = new DynamicCriteria(orderBy.toString());

            result.addWhere("A.status in?");


            I am adding the values like below.

            result.add("(\'A\',\'P\')");

Error :
(Messages : + [Cannot execute fetch: java.sql.SQLException: Syntax error or access violation,  message from server: "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 '' in(\'A\',\'P\')' ) AND )
   
Problem 2) How can we add group by for DynamicCriteria?

Kindly help me.

Thanks & Regards
Yadagiri

[John O'Hanley]

Hello,

Version 4.9.0+ (2013-10) of web4j has re-worked the whole mechanism for this task of building SQL.

The older versions tried to help by building only part of the SQL statement. But it didn't work very well, and the design wasn't very good.

The new mechanism is simpler, since it just lets you build the entire SQL statement as a string, with no restrictions as to its content (be careful with SQL injection).

My best advice is to migrate to the latest version of web4j. If you still experience problems with error messages from the database server, then that will only be a matter of proper SQL formatting, escaping characters correctly, and so on; that is, it shouldn't be a problem with the web4j API per se, I think.

Regards,
John

[anepally yadagiri]

Dear John,

Thank you for reply.

I am using older version of web4j(2009). When I migrate to the latest version of web4j, the existing functionality is getting disturbed(Not working). Previously I didn't get this type of situation. At present this situations comes into picture.

I tried with different types of SQL formatting, escaping characters. But no use.

How to resolve the group by clause issue?

Any other way to solve these problems?

Regards

Yadagiri

--
You received this message because you are subscribed to the Google Groups "web4j-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to web4j-users...@googlegroups.com.
To post to this group, send email to web4j...@googlegroups.com.
Visit this group at http://groups.google.com/group/web4j-users.
For more options, visit https://groups.google.com/groups/opt_out.

--
Thanks & Regards
Yadagiri,

Go Green : Think before you print this E-mail or its attachment. You can save a paper if you do  not really need to print.

[John O'Hanley]

2009 is pretty old. Yes, moving to new versions often breaks things. But, in the majority of cases, you can fix the breaks quickly, by reading the advice in the version history:
http://www.web4j.com/web4j/javadoc/hirondelle/web4j/doc-files/VersionHistory.html

At some point, you will likely benefit from moving to the latest web4j version, and working through all the breaks.

-------

If I remember correctly, you should be able to add a 'group-by' and 'having' with DynamicCriteria. I may be mistaken about that.

You should increase the logging, if necessary, to see the SQL statement before it gets sent to the server. Sometimes when you examine the whole SQL statement, you can see mistakes - items in the wrong sequence, for example.

[anepally yadagiri]

Dear John,

Thank you for your support.

IN operator issue is resolved.

I am not able to add group by clause to DynamicCriteria. I can able to add order by.

Ex:      StringBuilder orderBy = new StringBuilder(DynamicCriteria.ORDER_BY);

            orderBy.append("abc.username");
            result = new DynamicCriteria(orderBy.toString());

But I am not able to add group by. Is there any alternative to achieve this.

--

You received this message because you are subscribed to the Google Groups "web4j-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to web4j-users...@googlegroups.com.
To post to this group, send email to web4j...@googlegroups.com.
Visit this group at http://groups.google.com/group/web4j-users.
For more options, visit https://groups.google.com/groups/opt_out.

[John O'Hanley]

I think DynamicCriteria let's you do this.
It's not explicit about it, but I think that you can just add it to your StringBuilder.