java - 如何优雅的根据查询条件拼接sql

Question

管理平台很多查询条件，需要根据不同的查询条件拼接sql比如 a='xx' b like 'xx' c is null 等。我知道用mybatis的话可以根据是否查询条件为空来拼接sql。但是用其他框架比如JFinal 怎么更好的拼接，又能有效的防止sql注入？请前辈给个思路。谢谢

Answer 1

String sql = "select * from user where 1 = 1 ";
        List<Object> params = new ArrayList<Object>();
        if(!StringUtils.isEmpty(username)){
            sql += " and username like ?";
            params.add("%"+username+"%");
        }
        if(!StringUtils.isEmpty(email)){
            sql +=" and email like ?";
            params.add("%"+email+"%");
        }
        if(!StringUtils.isEmpty(company)){
            sql +=" and company like ?";
            params.add("%"+company+"%");
        }
        if(status != null){
            sql += " and status = ?";
            params.add(status);
        }
        if(duestatus != null){
            if(duestatus == 1){
                sql += " and  date(duedate) < date(now())";
            }else{
                sql += " and  date(duedate) > date(now())";
            }
        }
        sql +=" order by regdate desc";
        return db().findList(sql, page, size, params.toArray());

Answer 2

You can use jOOQ, a lightweight ORM framework
Depending on your needs, you can just use the sql builder(doc)

// Fetch a SQL string from a jOOQ Query in order to manually execute it with another tool.
String sql = create.select(field("BOOK.TITLE"), field("AUTHOR.FIRST_NAME"), field("AUTHOR.LAST_NAME"))
                   .from(table("BOOK"))
                   .join(table("AUTHOR"))
                   .on(field("BOOK.AUTHOR_ID").equal(field("AUTHOR.ID")))
                   .where(field("BOOK.PUBLISHED_IN").equal(1948))
                   .getSQL();

Answer 3

This kind of user/interface driven combined query, I have handled it in a previous project and implemented it using Hibernate's Criteria Query.

The program processing is actually relatively simple. The focus is on the performance optimization of this type of real custom combination query. This is the key.

Hope it helps you.

P.S. In any situation, please do not assemble SQL directly. You may accidentally introduce a SQL injection point and make an irreparable mistake.

Answer 4

Adopt builder mode

Answer 5

How come your code is as bad as mine?

Answer 6

It is recommended to use a template engine to do complex SQL, see my article: http://blog.csdn.net/rocks_le...