请问如何拼接sql语句,实现动态绑定sql

想动态绑定sql,用java 写的sql语句如下:

id,name,dept_id 都是传进来的值.
sql = "select id,name,dept_id from users where 1=1 ";
if(id!=null &&id.equals(""))
sql += " and id = ?";
if(name!=null &&name.equals(""))
sql += " and name = ?";
if(dept_id !=null &&dept_id .equals(""))
sql += " and dept_id = ?";

pstmt = conn.prepareStatement(sql);
这个地方怎么写pstmt.set.........("","");
rs = pstmt.executeQuery();

我想拼接的每个循环里面"pstmt.setString("+i+",id");但是java不执行,还有什么别的方法或思路吗?

4个回答

public void query(final Map map) throws Exception
{
StringBuffer sql = new StringBuffer("select id,name,dept_id from users where 1=1 ");

    for (final Entry<String, Object> entry : map.entrySet())
    {
        sql.append(", ").append(entry.getKey()).append("=?");
    }

    psmt = con.prepareStatement(sql.toString());

    int i = 0;

    for (final Entry<String, Object> entry : map.entrySet())
    {
        psmt.setObject(i, entry.getValue());
        i++;
    }
} 

我建议你把
[code="java"]if(id!=null &&id.equals(""))
sql += " and id = ?";
if(name!=null &&name.equals(""))
sql += " and name = ?";
if(dept_id !=null &&dept_id .equals(""))
sql += " and dept_id = ?";
[/code]
直接改为
[code="java"]if(id!=null &&id.equals(""))
sql += " and id = " + id;
if(name!=null &&name.equals(""))
sql += " and name = " + name;
if(dept_id !=null &&dept_id .equals(""))
sql += " and dept_id = "+ dept_id;
[/code]
要防止注入攻击的话,你就不应该这么判断了。

select id,name,dept_id from users
where (
(? is null or '' = ?)
or
(id = ?)
)
and (
(? is null or '' = ?)
or
(name = ?)
)
and (
(? is null or '' = ?)
or
(dept_id = ?)
)

你非要绑定就这么写,不需要判断,不用判断id, name, dept_id是null还是""
直接绑定进去就搞定了。

1,2,3绑定id
4,5,6绑定name
7,8,9绑定dept_id

public void query(final Map<String, Object> map) throws Exception
{
    StringBuffer sql = new StringBuffer("select id,name,dept_id from users where 1=1 "); 

    for (final Entry<String, Object> entry : map.entrySet())
    {
        sql.append(entry.getKey()).append("=?,");
    }

    sql.delete(sql.length() - 1, sql.length());

    psmt = con.prepareStatement(sql.toString());

    int i = 0;

    for (final Entry<String, Object> entry : map.entrySet())
    {
        psmt.setObject(i, entry.getValue());
    }
}

Map map = new HashMap();
map.put("id", 1);
map.put("name", "java");
map.put("dept_id", 1);

不知道这是不是你想要的。

Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问