Dynamically construct SQL in Java

[ellicott]

Hi,

I am new in the area. My project is to setup text search. The similler one is http://www.pir.uniprot.org/search/textSearch.shtml A user can add input box as needed. The backend part is to construct SQL query dynamically. I am wondering some experts here can give a hint for how to dynamically construct SQL in Java, such as where to find basic information and guide to the issue.

Your response will be appreciated,

[Waylander]

There is a few ways to go about it, you can just use string handling and build up the statement manually, having a bean object with all the fields for the search is also handy when using this approach.

You can also use an object relational mapping tool that has some dynamic statement handling functionality, ibatis has this, you can pass in hash maps of parameters and then you can embed logic in the statement based on which values are there or not and what the values are.

Waylander.

[ellicott]

There is a few ways to go about it, you can just use string handling and build up the statement manually, having a bean object with all the fields for the search is also handy when using this approach.

You can also use an object relational mapping tool that has some dynamic statement handling functionality, ibatis has this, you can pass in hash maps of parameters and then you can embed logic in the statement based on which values are there or not and what the values are.

Waylander.

Waylander,

I apprecaite your throughful thinking. I wonder I could ask a couple of question further. It looks iBATIS is right fremework to construct SQL statement at run time. I am trying to assess workload of using iBATIS framework compare to manually build up SQL statements. Have you used iBATIS before? How stable it is.

For manually building up SQL statement, I still don't know how it can be done. Is there any fregment code I can take a look? For instance, in following code:

public Person getWeight(String fname, String lname, String colname, String tablename) throws SQLException{
String sql = "select ? from ? where firstname=? and lastname=?";

PreparedStatement stmt = null;
ResultSet rs = null;
Person person = null;
try{
stmt =_conn.prepareStatement(sql);
stmt.setString(1, colname);
stmt.setString(2, tablename);
stmt.setString(3, fname);
stmt.setString(4, lname);
rs = stmt.executeQuery();
if(rs.next()){
Int weight = rs.getInt(1);
person = new Person();
person.setWeight(weight);

......
}
......
return person;
}



Thanks in advance again,

[Waylander]

Ibatis is very stable, I have a few web applications running on it now and I know a few others who use it too. As far as workload goes it is most likely the simplest object relational mapping tool at the moment, simplest in learning curve and in overhead.

Well anyway, your half way there with what you've got.

String sql = "select ? from ? where firstname=? and lastname=?";

With a parametric replacement like when your using prepared statement the values you pass to the function get inserted into where the question marks are. So you can pass in anything you want you just have to be sure your code can handle what ever is coming back.


Heres a fragment from a recent ibatis sqlmap ive done:


<!-- Get Paginated Component List -->

<select id="getPaginatedComponentList" resultMap="componentResultList" parameterClass="search">
SELECT * FROM Component WHERE $whereClause$ ORDER BY $orderBy$ $orderDirection$ LIMIT #startRecord#,#recordsPerPage#;
</select>

If you notice the variables surrounded by $ and # are where the statement picks up the values I want to be dynamic, I dont pass the table name though because I want to make sure I get back a specific bean.

To get the values into the statement I use a search bean which has private members that match up with the statement parameter names. Ibatis picks up set and get methods as long as you follow the normal java standard:


...
private String _whereClause;
...
private String _orderBy;
private String _orderDirection;
...
private Long _startRecord;
private Long _recordsPerPage;
...


then I can call the statement passing in the search bean and returning a list of component beans:


public List getPaginatedComponentList(Search search)
{
logger.debug("Method: getPaginatedComponentList");
return queryForList("getPaginatedComponentList", search);
}


What ive got on here is pretty complex... and there is alot in between to get it all to work.

Im not sure that you would need that level for your requirements?

However ibaits is fairly flexible, you dont need to send or return beans, you can use hashmaps and lists for every thing.

Waylander.

[ellicott]

Waylander,

Thanks for the direction! I am going to step met on iBATIS, and will report progress and questions if there is any.

[Waylander]

No Worries,

Good Luck :)

Waylander.