Dynamic SQL - IBatis

[sattyani]

I have a question regarding dynamic select statements using iBatis. Based on user input in the GUI (JSP page), the controller needs to verify the input values in the command object, generate select statement then pass the select statement to iBatis.
With static select statements things are working fine, but I am not sure how to configure and use dynamic select statements (driven by user input)

By the way, I am using PostgreSQL 8.2

I appreciate your input.

[sattyani]

iBatis DataMapper API provides a good solution, see link below.

http://ibatis.apache.org/docs/dotnet....html#id386238

I will give it a try!

Another question though, how to specify less than operator in the SQL statement. For example is i am checking for startdate property

select * from TABLE1
where startdate < #fromdate#

When I do that eclipse is thinks the "<" is a begenning of a tag not "less than" operator

Please advice

[lumpynose]

The less than is in an xml file isn't it? In that case I think you have to do it with the 4 character &lt; thing. This is documented in the iBatis manual.

[lumpynose]

Hmm, now I can't find it in the iBatis document; I did find an example using CDATA though:

<select id="getPersonsByAge" parameterClass=”int” resultClass="examples.domain.Person">
SELECT *
FROM PERSON
WHERE AGE <![CDATA[ > ]]> #value#
</select>

[sattyani]

Thanks lumpynose it worked!

[shriyakish]

I am stuck up with this problem.
i am new to iBatis.
I am supposed to modify the join in the query depending upon the parameters from the jsp.
say for param1,I have to use a LEFT OUTER JOIN
for param2 FULL OUTER JOIN
and for param3 no join.

Can you give me a code eg.as to how I do it.

Thanks in advance.

Vidhya

[lumpynose]

One way would be to create 3 different queries, and then in your java code examine the params and use the appropriate query. (This may not be the best way.)

@Repository
public final class SomethingDao extends SqlMapClientDaoSupport implements ISomethingDao {
    private final transient Logger log = LoggerFactory.getLogger(getClass());

    @Autowired
    public SqlMapClient sqlMapClient;

    @PostConstruct
    public void init() {
        this.setSqlMapClient(sqlMapClient);
    }

    @Override
    public void somethingFondler(Something param1, Something param2, Something param3) {
        getSqlMapClientTemplate().execute(new SqlMapClientCallback() {
            @SuppressWarnings("synthetic-access")
            public Object doInSqlMapClient(final SqlMapExecutor executor)
                    throws SQLException {
                executor.startBatch();

                if (leftOuterJoinNeeded(param1, param2))
                    executor.queryForList("Something.leftOuterJoin", param1);
                else if (fullOuterJoinNeeded(param1, param2))
                    executor.queryForList("Something.fullOuterJoin", param2);
                else
                    executor.queryForList("Something.query", param3);

                executor.executeBatch();

                return (null);
            }
        });
    }

You may not need the executor with its batch stuff; that's from the code I copied this from before I changed it for this example. The code I copied from was doing an update.

[lumpynose]

Also, check the iBatis web site; there is an iBatis mailing list where you may find better help.

[burki]

You don't have to stick with one ORM solution, in my opinion, one of the best advantages of using spring templating is opportunity of using heteregenous orm mechanisms. You can inject a jdbcTemplate using same datasource, and write your own sql for such kind of complex query situations.

[shriyakish]

I am kind of confused now.

ok.
Wat abt having 2 tables in from class and I will get the name as parameters.
Is that possible.