advanced dynamic sql

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

advanced dynamic sql

Sharad Yadav
Hi,

I am using Mybatis for my project.

I have a requirement where I have to create complete dynamic SQL including table name and does use UNION also. I checked the dynamic SQL builder but it does not support UNION clause. Is there any better way other then what I have done below. I have cut down other things to show the code in simple format.

Also I think I don't need to close the connection here right as it will be taken care by the framework as I am using spring transaction. Apart from this I am using mybatis normally also i.e. using the named queries.


               StringBuilder sql = new StringBuilder("");
int index = 1;
for (int id : filteredIds) {
sql.append("SELECT id from table_").append(id).append(
" WHERE cat_id = ").append(catId);
if (index < filteredIds.size()) {
sql.append(" UNION ");
}
++index;
}
List<Integer> selected = new ArrayList<Integer>();
ResultSet rs = null;
try {
rs = template.getDataSource().getConnection().createStatement()
.executeQuery(sql.toString());
while (rs.next()) {
selected.add(rs.getInt(1));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}

Thanks
Bhushan
Reply | Threaded
Open this post in threaded view
|

Re: advanced dynamic sql

Sharad Yadav
Any inputs please?

On Thu, Oct 21, 2010 at 8:42 AM, Sharad Yadav <[hidden email]> wrote:
Hi,

I am using Mybatis for my project.

I have a requirement where I have to create complete dynamic SQL including table name and does use UNION also. I checked the dynamic SQL builder but it does not support UNION clause. Is there any better way other then what I have done below. I have cut down other things to show the code in simple format.

Also I think I don't need to close the connection here right as it will be taken care by the framework as I am using spring transaction. Apart from this I am using mybatis normally also i.e. using the named queries.


               StringBuilder sql = new StringBuilder("");
int index = 1;
for (int id : filteredIds) {
sql.append("SELECT id from table_").append(id).append(
" WHERE cat_id = ").append(catId);
if (index < filteredIds.size()) {
sql.append(" UNION ");
}
++index;
}
List<Integer> selected = new ArrayList<Integer>();
ResultSet rs = null;
try {
rs = template.getDataSource().getConnection().createStatement()
.executeQuery(sql.toString());
while (rs.next()) {
selected.add(rs.getInt(1));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}

Thanks
Bhushan

Reply | Threaded
Open this post in threaded view
|

Re: advanced dynamic sql

Jeff Butler
There are two better ways to do this in Mybatis.  One is to use the
<iterate> tag in dynamic XML to create the union query.  The other is
to use the @SelectProvider annotation along with a class that builds
the string.  I think that the @SelectProvider annotation method might
be better in your case.

Either way, you are simply concerned with building the select string,
then the framework will execute it.  So there's no need to to
getConnection().createStatement().etc.

Jeff Butler


On Sun, Oct 24, 2010 at 10:03 PM, Kamran Yadav <[hidden email]> wrote:

> Any inputs please?
>
> On Thu, Oct 21, 2010 at 8:42 AM, Sharad Yadav <[hidden email]> wrote:
>>
>> Hi,
>> I am using Mybatis for my project.
>> I have a requirement where I have to create complete dynamic SQL including
>> table name and does use UNION also. I checked the dynamic SQL builder but it
>> does not support UNION clause. Is there any better way other then what I
>> have done below. I have cut down other things to show the code in simple
>> format.
>> Also I think I don't need to close the connection here right as it will be
>> taken care by the framework as I am using spring transaction. Apart from
>> this I am using mybatis normally also i.e. using the named queries.
>>
>>                StringBuilder sql = new StringBuilder("");
>> int index = 1;
>> for (int id : filteredIds) {
>> sql.append("SELECT id from table_").append(id).append(
>> " WHERE cat_id = ").append(catId);
>> if (index < filteredIds.size()) {
>> sql.append(" UNION ");
>> }
>> ++index;
>> }
>> List<Integer> selected = new ArrayList<Integer>();
>> ResultSet rs = null;
>> try {
>> rs = template.getDataSource().getConnection().createStatement()
>> .executeQuery(sql.toString());
>> while (rs.next()) {
>> selected.add(rs.getInt(1));
>> }
>> } catch (SQLException e) {
>> e.printStackTrace();
>> } finally {
>> if (rs != null) {
>> try {
>> rs.close();
>> } catch (SQLException e) {
>> e.printStackTrace();
>> }
>> }
>> }
>> Thanks
>> Bhushan
>
Reply | Threaded
Open this post in threaded view
|

Re: advanced dynamic sql

Sharad Yadav
Thanks Jeff

If I do not use getConnection().createStatement() etc then how do I execute the built SQL query and get the results?

On Mon, Oct 25, 2010 at 8:52 PM, Jeff Butler <[hidden email]> wrote:
There are two better ways to do this in Mybatis.  One is to use the
<iterate> tag in dynamic XML to create the union query.  The other is
to use the @SelectProvider annotation along with a class that builds
the string.  I think that the @SelectProvider annotation method might
be better in your case.

Either way, you are simply concerned with building the select string,
then the framework will execute it.  So there's no need to to
getConnection().createStatement().etc.

Jeff Butler


On Sun, Oct 24, 2010 at 10:03 PM, Kamran Yadav <[hidden email]> wrote:
> Any inputs please?
>
> On Thu, Oct 21, 2010 at 8:42 AM, Sharad Yadav <[hidden email]> wrote:
>>
>> Hi,
>> I am using Mybatis for my project.
>> I have a requirement where I have to create complete dynamic SQL including
>> table name and does use UNION also. I checked the dynamic SQL builder but it
>> does not support UNION clause. Is there any better way other then what I
>> have done below. I have cut down other things to show the code in simple
>> format.
>> Also I think I don't need to close the connection here right as it will be
>> taken care by the framework as I am using spring transaction. Apart from
>> this I am using mybatis normally also i.e. using the named queries.
>>
>>                StringBuilder sql = new StringBuilder("");
>> int index = 1;
>> for (int id : filteredIds) {
>> sql.append("SELECT id from table_").append(id).append(
>> " WHERE cat_id = ").append(catId);
>> if (index < filteredIds.size()) {
>> sql.append(" UNION ");
>> }
>> ++index;
>> }
>> List<Integer> selected = new ArrayList<Integer>();
>> ResultSet rs = null;
>> try {
>> rs = template.getDataSource().getConnection().createStatement()
>> .executeQuery(sql.toString());
>> while (rs.next()) {
>> selected.add(rs.getInt(1));
>> }
>> } catch (SQLException e) {
>> e.printStackTrace();
>> } finally {
>> if (rs != null) {
>> try {
>> rs.close();
>> } catch (SQLException e) {
>> e.printStackTrace();
>> }
>> }
>> }
>> Thanks
>> Bhushan
>

Reply | Threaded
Open this post in threaded view
|

Re: advanced dynamic sql

Jeff Butler
Yeah - there's not a great example of this anywhere I know of - but it
is a very cool feature of MyBatis.  Here are the the basics (I typed
this from memory - it may or may not compile, but this is the basic
idea)...

public class ParameterObject (
  private List<Integer> tableIds;
  private Integer catid;
  .. getters/setters
}

public interface MyMapper {
  @SelectProvider(type=MyProvider.class, method="myMethod")
  @Results ({
    @Result (column="some_column1", property="someProperty1"),
    @Result (column="some_column2", property="someProperty2")
  })
  List<SomeObject> getSomeObjects(ParameterObject param);
}

public class MyProvider {
  public String myMethod(ParameterObject param) {
    boolean union = false;
    StringBuilder sb = new StringBuilder();
    for (Integer id : param.getTableIds()) {
      if (union) {
        sb.append("union ");
      } else {
        union = true;
      }

      sb.append("select * from table_");
      sb.append(id);
      sb.append(" where cat_id = #{catid}");
    }

    return sb.toString();
  }
}

Then you can execute the mapper method getSomeObjects() with normal
MyBatis boilerplate code (get sql session, get mapper, execute mapper
method).


HTH -
Jeff Butler




On Mon, Oct 25, 2010 at 9:45 PM, Kamran Yadav <[hidden email]> wrote:

> Thanks Jeff
> If I do not use getConnection().createStatement() etc then how do I execute
> the built SQL query and get the results?
>
> On Mon, Oct 25, 2010 at 8:52 PM, Jeff Butler <[hidden email]> wrote:
>>
>> There are two better ways to do this in Mybatis.  One is to use the
>> <iterate> tag in dynamic XML to create the union query.  The other is
>> to use the @SelectProvider annotation along with a class that builds
>> the string.  I think that the @SelectProvider annotation method might
>> be better in your case.
>>
>> Either way, you are simply concerned with building the select string,
>> then the framework will execute it.  So there's no need to to
>> getConnection().createStatement().etc.
>>
>> Jeff Butler
>>
>>
>> On Sun, Oct 24, 2010 at 10:03 PM, Kamran Yadav <[hidden email]>
>> wrote:
>> > Any inputs please?
>> >
>> > On Thu, Oct 21, 2010 at 8:42 AM, Sharad Yadav <[hidden email]>
>> > wrote:
>> >>
>> >> Hi,
>> >> I am using Mybatis for my project.
>> >> I have a requirement where I have to create complete dynamic SQL
>> >> including
>> >> table name and does use UNION also. I checked the dynamic SQL builder
>> >> but it
>> >> does not support UNION clause. Is there any better way other then what
>> >> I
>> >> have done below. I have cut down other things to show the code in
>> >> simple
>> >> format.
>> >> Also I think I don't need to close the connection here right as it will
>> >> be
>> >> taken care by the framework as I am using spring transaction. Apart
>> >> from
>> >> this I am using mybatis normally also i.e. using the named queries.
>> >>
>> >>                StringBuilder sql = new StringBuilder("");
>> >> int index = 1;
>> >> for (int id : filteredIds) {
>> >> sql.append("SELECT id from table_").append(id).append(
>> >> " WHERE cat_id = ").append(catId);
>> >> if (index < filteredIds.size()) {
>> >> sql.append(" UNION ");
>> >> }
>> >> ++index;
>> >> }
>> >> List<Integer> selected = new ArrayList<Integer>();
>> >> ResultSet rs = null;
>> >> try {
>> >> rs = template.getDataSource().getConnection().createStatement()
>> >> .executeQuery(sql.toString());
>> >> while (rs.next()) {
>> >> selected.add(rs.getInt(1));
>> >> }
>> >> } catch (SQLException e) {
>> >> e.printStackTrace();
>> >> } finally {
>> >> if (rs != null) {
>> >> try {
>> >> rs.close();
>> >> } catch (SQLException e) {
>> >> e.printStackTrace();
>> >> }
>> >> }
>> >> }
>> >> Thanks
>> >> Bhushan
>> >
>
>