Take a look the following debug message: (I am using ibatis 2.1.16)
PreparedStatement: select PARAM1 as m_data1,lab_role_Fnc(PARAM2) as
m_data2,USERID as m_da
ta3,Name_Fnc(PARAM2) as m_data4,PARAM2 as m_data5, APPLCD m_data6
FROM AUT
HORIZE.USER_ROLES where APPLCD='COREYP' AND
PARAM1='2' OR
DER BY 2
11:25:51,156 DEBUG - [sql.PreparedStatement.debug] {pstm-100004}
Parameters: []
11:25:51,156 DEBUG - [sql.PreparedStatement.debug] {pstm-100004} Types: []
11:25:51,156 DEBUG - [sql.ResultSet.debug] {rset-100005} ResultSet
11:25:51,171 DEBUG - [sql.ResultSet.debug] {rset-100005} Header:
[M_DATA1, M_DAT
A2, M_DATA3, M_DATA4, M_DATA5, M_DATA6]
11:25:51,171 DEBUG - [sql.ResultSet.debug] {rset-100005} Result: [2,
DIRECTOR, j
eschwar, Schwartz,Jessica, 01043411, COREYP]
1=2, 2=DIRECTOR, 3=jeschwar, 4=Schwartz,Jessica, 5=01043411, 6=COREYP
so far so good
11:25:51,187 DEBUG - [sql.Connection.debug] {conn-100006} Connection
11:25:51,187 DEBUG - [sql.PreparedStatement.debug] {pstm-100007}
PreparedStateme
nt: select LAB_ID as m_data1,LAB_ID || ' - ' || LAB_NAME as
m_data2 F
ROM LABS ORDER BY LAB_NAME
11:25:51,187 DEBUG - [sql.PreparedStatement.debug] {pstm-100007}
Parameters: []
11:25:51,187 DEBUG - [sql.PreparedStatement.debug] {pstm-100007} Types: []
11:25:51,203 DEBUG - [sql.ResultSet.debug] {rset-100008} ResultSet
11:25:51,203 DEBUG - [sql.ResultSet.debug] {rset-100008} Header:
[M_DATA1, M_DAT
A2]
11:25:51,203 DEBUG - [sql.ResultSet.debug] {rset-100008} Result: [2, 2 -
CMB lab
I]
error:
org.springframework.jdbc.BadSqlGrammarException: Bad SQL grammar [] in
task 'SqlMapClient operation'; nested exception is
com.ibatis.common.jdbc.exception.NestedSQLException: --- The error
occurred in edu/umich/med/umms/coreyp/dao/ibatis_map/misc.xml. --- The
error occurred while applying a result map. --- Check the
select_list_col_str-AutoResultMap. --- Check the result mapping for the
'm_data3' property. --- Cause: java.sql.SQLException: Invalid column name
org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator(SQLErrorCodeSQLExceptionTranslator.java:223)
somehow, ibatis/sql tried to take partial of first statemen in to the
second one.
here is my map:
<select id="select_list_col_str"
resultClass="Strdata">
select $col_name_str$
FROM $table_name$
<dynamic prepend="where">
<isNotEmpty prepend="and" property="where_str">
$where_str$
</isNotEmpty>
</dynamic>
ORDER BY $order_str$
</select>
Java code:
lab_id = "2";
where_str = "APPLCD='COREYP' AND PARAM1='"
+ lab_id + "'";
col_name_list =
"PARAM1 as m_data1,"
+ "lab_role_Fnc(PARAM2) as m_data2,"
+ "USERID as m_data3,"
+ "Name_Fnc(PARAM2) as m_data4,"
+ "PARAM2 as m_data5, APPLCD m_data6";
List lst2 = m_misc.SelectList_col_str(
col_name_list,
"AUTHORIZE.USER_ROLES",
where_str, "2");
if (lst2 != null && lst2.size() > 0) {
st = (Strdata)lst2.get(0);
System.out.println(
"1="+st.getM_data1()+
", 2="+ st.getM_data2() +
", 3="+ st.getM_data3() +
", 4="+ st.getM_data4() +
", 5="+ st.getM_data5() +
", 6="+ st.getM_data6());
form.setCnt_1(lst2.size());
form.setList_1(lst2);
}
where_str = null;
order_str = "LAB_NAME";
col_name_list = "LAB_ID as m_data1,"
+ "LAB_ID || ' - ' || LAB_NAME as m_data2";
lst = m_misc.SelectList_col_str(
col_name_list,
"LABS", where_str, order_str);
if (lst != null && lst.size() > 0) {
form.setCnt_2(lst.size());
form.setList_2(lst);
} else {
return;
}
public List SelectList_col_str(String col_str, // required
String table_name, // required
String where_str, // optional
String order_str) // required
{
Map map = new HashMap(4);
map.put("col_name_str", col_str); // required
map.put("table_name", table_name); // required
if (where_str != null && where_str.length() > 0) {
map.put("where_str", where_str);
} else {
map.put("where_str", "");
}
map.put("order_str", order_str); // required
return getSqlMapClientTemplate().queryForList(
"select_list_col_str", map);
}
-Henry
