Creating liferay portal, liferay query, liferay tomcat, liferay code, Liferay expert, sql query, liferay class, mysql

Moore  :  Hi Liferay expert,

             I have query in liferay portal development we are using MYSQL

I'm creating my custom query with liferay 5.2.3 and tomcat 5. The query is in the following:

            

             <![CDATA[

             SELECT * FROM etc_georeferencedasset

             INNER JOIN TagsAsset ta on ta.assetid = etc_georeferencedasset.assetid

             WHERE

             (ta.visible > 0) AND

             (lower(ta.title) LIKE ?) AND

             ta.assetid in

             (

             select TagsAsset.assetid FROM TagsAsset INNER JOIN

             TagsAssets_TagsEntries ON

             (TagsAssets_TagsEntries.assetId = TagsAsset.assetId)

             INNER JOIN

             TagsEntry ON

             (TagsEntry.entryId = TagsAssets_TagsEntries.entryId)

             WHERE (TagsEntry.name IN ([$TAGS$]))

             )

             ]]>

             The code to manage the query is this:

             public List<etc_georeferencedAsset> findByTitleAndTag(

             String title, String[] tag, int start, int end) throws SystemException {

            

             Session session = null;

            

             try {

            

             session = openSession();

            

             String sql = CustomSQLUtil.get(FIND_BY_TITLE_AND_TAGS);

            

             if ((title == null) || title.equals("")) {

            

             sql = StringUtil.replace(sql, "(lower(ta.title) LIKE ?) AND", "");

             }

             if(tag.length!=0){

             String tmp="";

             for(String s:tag){

             tmp+="'"+s+"',";

             }

             sql=StringUtil.replace(sql, "[$TAGS$]", tmp.substring(0, tmp.length()-1));

             } else {

             sql=StringUtil.replace(sql, "[$TAGS$]", "");

             }

             logger.info(sql);

             SQLQuery q = session.createSQLQuery(sql);

             q.setCacheable(false);

             q.addEntity("etc_georeferencedAsset", etc_georeferencedAssetImpl.class);

            

             QueryPos qPos = QueryPos.getInstance(q);

            

             if ((title != null) && !title.equals("")) {

            

             qPos.add(title.toLowerCase());

             }

            

             return q.list();

             // return (List<etc_georeferencedAsset>)QueryUtil.list(

             // q, getDialect(), start, end);

             }

            

             catch (Exception e) {

             logger.error(e.getMessage(),e);

             throw new SystemException(e);

             }

            

             finally {

             closeSession(session);

             }

             }

            

             The query works when title is null, if title is a string it returns 0 items, but if I run the query directly on the dbms it returns an item. I'm using mysql and I tried also with a general log file working and the resulting query is ok and returns the item. Please suggest me a way to debug or a way to solve this!!!

            

Bailey    :          Hi developer,

            

             Can you try by appending '"' to positional parameter which is String.

            

             that is

            

             if ((title != null) && !title.equals("")) {

                        qPos.add("\"" + title.toLowerCase()"\"");

}

            

             Hope it will work.

             Regards,

            

Moore   :          I'm sorry, it works! Thank a lot for your liferay tutorial : the liferay problem was mine, I passed wrong parameters in TAGS.