dynamic queries

[kjvoogd]

hi,

I'm trying to implement some dynamic queries in springs dao framework. What i'm essentially trying to do is supply some parameters for other things than real sql parameters.

Essentially this is a paging query. Where I want the and v_key > ? to be configurable to either greather or lesser.

Should I parse the sql string myself or is there a sort of solution allready ??


Regards,


Kees Jan Voogd





private class KeyPageListMappingQuery extends MappingSqlQuery {


private final static String sql =
""
+ " SELECT "
+ " k_uid as id, "
+ " f_b_uid as b_id, "
+ " v_key as akey, "
+ " v_dscrpt as description "
+ " FROM tb_key "
+ " WHERE "
+ " f_b_uid = ? "
+ " and v_key ? ? " // > is an opteration an cannot be replace by a parameter....
+ " ORDER BY v_key limit ?";

public KeyPageListMappingQuery(DataSource ds) {

super(ds, sql);
super.declareParameter(new SqlParameter(Types.INTEGER));
super.declareParameter(new SqlParameter(Types.CHAR));
super.declareParameter(new SqlParameter(Types.CHAR));
super.declareParameter(new SqlParameter(Types.INTEGER));
super.compile();
}

public Object mapRow(ResultSet rs, int rowNumber) throws SQLException {

Key key = new Key();
key.setKey(rs.getString("akey"));
key.setBundleId( new Integer(rs.getInt("b_id")));
key.setDescription(rs.getString("description"));
key.setId(new Integer(rs.getInt("id")));
return key;
}
} // end inner class

[rhasselbaum]

You can use java.text.MessageFormat to set your own parameters in the SQL string prior to sending it to the database. In this case, it should work fine because you're generating the parameter values in code, but you should avoid this approach with user-specified parameter values, as the resulting SQL could be malformed (e.g. if you don't escape embedded quotes and the like).

[kjvoogd]

ok,

figured as much. TextFormat Class is a nice idea.

KJ

[rstearns01]

If your keys are symmetric, you could also use 'between'; since you are passing a limit in, that would reduce your parameter count by one.

Also, looking at your SQL it looks like you could miss rows in your "less-than" query if there were more rows available than your limit, as your order by would cause it to start at row 1 each time.

HTH

[kjvoogd]

Thnx.

Noticed that. Passing antoher parameter order by asc and desc and using comparable on my dto's to get the sorting right. I have to do this elswhere because mysql does not support unions.

Seems accessive but I don't want to get all rows.

I did not know mysql supported the between construct. That will probably look nicer.

Thanx for all the reactions !


KJ