Htykuut

I have a sql like:

String sql = """SELECT id, name, sex, age, bron_year, address, phone, state, comment, is_hbp, is_dm, is_cva, is_copd, is_chd, is_cancer, is_floating, is_poor, is_disability, is_mental

 FROM statistics_stin WHERE 1=1

 ${p.team_num == 0 ? "" : "AND team_num = ${p.team_num}"}

 ${p.zone == 0 ? "" : "AND team_id = ${p.zone}"}

 ${p.is_hbp == 2 ? "" : "AND is_hbp = ${p.is_hbp}"}

 ${p.is_dm == 2 ? "" : "AND is_dm = ${p.is_dm}"}

 ${p.is_chd == 2 ? "" : "AND is_chd = ${p.is_chd}"}

 ${p.is_cva == 2 ? "" : "AND is_cva = ${p.is_cva}"}

 ${p.is_copd == 2 ? "" : "AND is_copd = ${p.is_copd}"}

 ${p.is_cancer == 2 ? "" : "AND is_cancer = ${p.is_cancer}"}

 ${p.is_floating == 2 ? "" : "AND is_floating = ${p.is_floating}"}

 ${p.is_poor == 2 ? "" : "AND is_poor = ${p.is_poor}"}

 ${p.is_disability == 2 ? "" : "AND is_disability = ${p.is_disability}"}

 ${p.is_mental == 2 ? "" : "AND is_mental = ${p.is_mental}"}

 ${p.is_aged == 2 ? "" : (p.is_aged == 1 ? " AND age >= 65" : " AND age < 65")}

 ${p.is_prep_aged == 2 ? "" : (p.is_prep_aged == 1 ? "AND (age BETWEEN 60 AND 64)" : "AND (age < 60 OR age > 64)")}

 ${p.is_young == 2 ? "" : (p.is_young == 1 ? " AND age < 60" : " AND age >= 60")}

 ORDER BY team_id ASC, id ASC

 LIMIT ${start}, ${page_size}

 """;

Then use :

def datasource = ctx.lookup("jdbc/mysql");

def executer = Sql.newInstance(datasource);

def rows = executer.rows(sql);

Here the p is a json object like:

p = {is_aged=2, is_cancer=2, is_chd=1, is_copd=2, is_cva=2, is_disability=2, is_dm=2, is_floating=2, is_hbp=1, is_mental=2, is_poor=2, pn=1, team_num=0, zone=0}

This way has sql injection. I know I can use params type like:

executer.rows('SELECT * from statistics_stin WHERE is_chd=:is_chd', [is_chd: 1]);

But this case has to many AND conditions, use or not will be decided by the json p.

How to do this please?

You have a problem of dynamic SQL binding, i.e. the number of bind parameters is not constant, but depend on the input.

There is an elegant solution from Tom Kyte
which has even more elegant implementation in Groovy

The basic idea is simple bind all variables, the variables that have an input value and should be used are processed normally, e.g

 col1 = :col1

the variables that have no input (and shall be ignored) are binded with a dummy construct:

 (1=1 or :col2 is NULL)

i.e. they are effectively ignored by shortcut evaluation.

Here two examples for three columns

 def p = ["col1" : 1, "col2" : 2, "col3" : 3]

This input leads to a full query

SELECT col1, col2, col3

 FROM tab WHERE

 col1 = :col1 AND

 col2 = :col2 AND

 col3 = :col3

ORDER by col1,col2,col3

For limited input

 p = [ "col3" : 3] 

you get this query

SELECT col1, col2, col3

 FROM tab WHERE

 (1=1 or :col1 is NULL) AND

 (1=1 or :col2 is NULL) AND

 col3 = :col3

ORDER by col1,col2,col3

Here the Groovy creation of the SQL Statement

String sql = """SELECT col1, col2, col3

 FROM tab WHERE   

 ${(!p.col1) ? "(1=1 or :col1 is NULL)" : "col1 = :col1"} AND

 ${(!p.col2) ? "(1=1 or :col2 is NULL)" : "col2 = :col2"} AND

 ${(!p.col3) ? "(1=1 or :col3 is NULL)" : "col3 = :col3"}  

ORDER by col1,col2,col3

""" 

You can even get rid of the ugly 1=1 predicate;)

Another option is to build your bindings as you build the query then execute the appropriate implementation of rows

def query = new StringBuilder( "SELECT id, name, sex, age, bron_year, address, phone, state, comment, is_hbp, is_dm, is_cva, is_copd, is_chd, is_cancer, is_floating, is_poor, is_disability, is_mental FROM statistics_stin WHERE 1=1" )

def binds = 

if ( p.team_num == 0 ) {

    query.append( ' AND team_num = ? ' )

    binds << p.team_num

}

if ( p.zone == 0 ) {

    query.append( ' AND team_id = ? ' )

    binds << p.zone == 0

}

...

executer.rows(query.toString(), binds);