ibatis内联参数，带有#in order by子句 [英] ibatis inline parameter with # in order by clause
本文介绍了ibatis内联参数，带有#in order by子句的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
下面是我的ibatis地图配置，
below is my ibatis map configuration,
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqlMap
PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"
"http://ibatis.apache.org/dtd/sql-map-2.dtd">
<sqlMap namespace="Contact">
<!--- Showing all data of table -->
<select id="getAll" resultClass="com.nik.Contact">
select * from contact
<dynamic prepend="where ">
salary like '%'
<isNotNull property="orderby" >
order by #orderby#, #orderby2#
</isNotNull>
</dynamic>
</select>
</sqlMap>
这是我的情话
package com.nik;
public class Contact {
private String firstName;
private String lastName;
private String email;
private String salary;
private String mobile;
private String orderby;
private String orderby2;
private int id;
public Contact() {}
public Contact(
String firstName,
String lastName,
String email) {
this.firstName = firstName;
this.lastName = lastName;
this.email = email;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public String getFirstName() {
return firstName;
}
public void setFirstName(String firstName) {
this.firstName = firstName;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getLastName() {
return lastName;
}
public void setLastName(String lastName) {
this.lastName = lastName;
}
public String getSalary() {
return salary;
}
public void setSalary(String salary) {
this.salary = salary;
}
public String getMobile() {
return mobile;
}
public void setMobile(String mobile) {
this.mobile = mobile;
}
public String getOrderby() {
return orderby;
}
public void setOrderby(String orderby) {
this.orderby = orderby;
}
public String getOrderby2() {
return orderby2;
}
public void setOrderby2(String orderby2) {
this.orderby2 = orderby2;
}
}
这是我的测试课，
package com.nik;
import com.ibatis.common.resources.Resources;
import com.ibatis.sqlmap.client.SqlMapClient;
import com.ibatis.sqlmap.client.SqlMapClientBuilder;
import java.io.*;
import java.sql.SQLException;
import java.util.*;
import org.apache.log4j.Logger;
public class IbatisExample{
public static void main(String[] args)
throws IOException,SQLException{
// get a logger instance named "com.foo"
Logger logger = Logger.getLogger("com.nik");
Reader reader = Resources.getResourceAsReader("ibatis-config.xml");
SqlMapClient sqlMap =
SqlMapClientBuilder.buildSqlMapClient(reader);
//Output all contacts
System.out.println("All Contacts");
Contact c1 = new Contact();
c1.setOrderby("salary");
c1.setOrderby2("mobile");
List<Contact> contacts = (List<Contact>)
sqlMap.queryForList("Contact.getAll",c1);
Contact contact = null;
for (Contact c : contacts) {
System.out.print(" " + c.getId());
System.out.print(" " + c.getFirstName());
System.out.print(" " + c.getLastName());
System.out.print(" " + c.getEmail());
System.out.print(" " + c.getSalary());
System.out.print(" " + c.getMobile());
contact = c;
System.out.println("");
}
}
}
这里的问题是order by子句无效...
The problem here is the order by clause has no effect...
我们在这里输出
All Contacts
DEBUG [main] - Created connection 71786792.
DEBUG [main] - {conn-100000} Connection
DEBUG [main] - {pstm-100001} PreparedStatement: select * from contact where salary like '%' order by ?, ?
DEBUG [main] - {pstm-100001} Parameters: [salary, mobile]
DEBUG [main] - {pstm-100001} Types: [java.lang.String, java.lang.String]
DEBUG [main] - {rset-100002} ResultSet
DEBUG [main] - {rset-100002} Header: [id, firstName, lastName, email, salary, mobile]
DEBUG [main] - {rset-100002} Result: [1, abc, 111, abc@hyahoo.com, 5000, 400]
DEBUG [main] - {rset-100002} Result: [2, def, 222, def@yahoo.com, 2000, 100]
DEBUG [main] - {rset-100002} Result: [3, xyz, 333, xyz@yahoo.com, 3000, 300]
DEBUG [main] - Returned connection 71786792 to pool.
1 abc 111 abc@hyahoo.com 5000 400
2 def 222 def@yahoo.com 2000 100
3 xyz 333 xyz@yahoo.com 3000 300
如果我在地图配置中将#"更改为"$"(例如，order by $ orderby $，$ orderby2 $)，那么它将起作用，
If I change the "#" with "$" in map config (e.g. order by $orderby$, $orderby2$) then it works,
All Contacts
DEBUG [main] - Created connection 71786792.
DEBUG [main] - {conn-100000} Connection
DEBUG [main] - {pstm-100001} PreparedStatement: select * from contact where salary like '%' order by salary, mobile
DEBUG [main] - {pstm-100001} Parameters: []
DEBUG [main] - {pstm-100001} Types: []
DEBUG [main] - {rset-100002} ResultSet
DEBUG [main] - {rset-100002} Header: [id, firstName, lastName, email, salary, mobile]
DEBUG [main] - {rset-100002} Result: [2, def, 222, def@yahoo.com, 2000, 100]
DEBUG [main] - {rset-100002} Result: [3, xyz, 333, xyz@yahoo.com, 3000, 300]
DEBUG [main] - {rset-100002} Result: [1, abc, 111, abc@hyahoo.com, 5000, 400]
DEBUG [main] - Returned connection 71786792 to pool.
2 def 222 def@yahoo.com 2000 100
3 xyz 333 xyz@yahoo.com 3000 300
1 abc 111 abc@hyahoo.com 5000 400
关于为什么带有#的内联参数不能按顺序工作的任何提示?我不能使用$，因为这是强化360的安全隐患:(
any clue on why inline parameter with # are not working in order by?? I can not use $ as this is as security risk as per fortify 360 :(
推荐答案
这不是一个优雅的解决方案，但由于不想更改代码，所以使用了它，
not an elegant solution but I used this as didnt want to do code change,
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqlMap
PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"
"http://ibatis.apache.org/dtd/sql-map-2.dtd">
<sqlMap namespace="Contact">
<!--- Showing all data of table -->
<select id="getAll" resultClass="com.nik.Contact" parameterClass="java.util.Map">
select * from contact where salary like '%'
<dynamic prepend="order by">
<isEqual
property="orderby"
compareValue="salary">
salary,
</isEqual>
<isEqual
property="orderby"
compareValue="mobile">
mobile,
</isEqual>
<isEqual
property="orderby2"
compareValue="salary">
salary
</isEqual>
<isEqual
property="orderby2"
compareValue="mobile">
mobile
</isEqual>
</dynamic>
</select>
</sqlMap>
这篇关于ibatis内联参数，带有#in order by子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
