本文要實現Mybatis多參數查詢與列表查詢不同方式實現,以一個實例來說明。
本文示例工程下載
一、查詢所有數據,返回List
查詢出列表,也就是返回list, 在我們這個例子中也就是 List
<!-- 定義數據庫字段與實體對象的映射關系 --> <resultMap type="Employeer" id="resultMap"> <id property="employeer_id" column="employeer_id"/> <result property="employeer_name" column="employeer_name"/> <result property="employeer_age" column="employeer_age"/> <result property="employeer_department" column="employeer_department"/> <result property="employeer_worktype" column="employeer_worktype"/> </resultMap>
id、result是最簡單的映射,id為主鍵映射;result其他基本數據庫表字段到實體類屬性的映射。
查詢列表的語句在 Employeer.xml 中
<!-- 返回list 的select 語句,注意 resultMap 的值是指向前面定義好的 --> <select id="findEmployeerByName" parameterType="string" resultMap="resultMap"> select * from `t_employeer` where employeer_name like #{employeer_name} </select>在EmployeerMapper 接口中增加方法:public List
/** * 注意要和Employeer.xml的方法名對應 */ public ListfindEmployeerByName(String employeer_name);
/** * 查詢列表 */ public static ListgetEmployeerList(String employeer_name){ SqlSession session = null; List employeers=null; try { session = sqlSessionFactory.openSession(); EmployeerMapper employeerMapper=session.getMapper(EmployeerMapper.class); employeers = employeerMapper.findEmployeerByName(employeer_name); session.commit() ; } finally { session.close(); } return employeers; } public static void main(String[] args) { List employeers=getEmployeerList("張三"); for(Employeer employeer:employeers){ System.out.println(employeer); } }
結果:
其中的數據的之前插入的,現在要查找張三的列表全部都列出來了。
二、多參數查詢
(1)方法一
EmployeerMapper中定義
/** * 多參數查詢,注意要和Employeer.xml的方法名對應 */ public ListEmployeer.xml中定義findEmployeerByNameandDep(String employeer_name,String employeer_department);
<!-- 定義數據庫字段與實體對象的映射關系 --> <resultMap type="Employeer" id="resultMap"> <id property="employeer_id" column="employeer_id"/> <result property="employeer_name" column="employeer_name"/> <result property="employeer_age" column="employeer_age"/> <result property="employeer_department" column="employeer_department"/> <result property="employeer_worktype" column="employeer_worktype"/> </resultMap> <!--多參數 查找 返回list 的select 語句,注意 resultMap 的值是指向前面定義好的 --> <select id="findEmployeerByNameandDep" resultMap="resultMap"> select* from `t_employeer` where employeer_name=#{0} and employeer_department=#{1} </select>
由於是多參數那麼就不能使用parameterType, 改用#{index}是第幾個就用第幾個的索引,索引從0開始
測試代碼:
/** * 多參數查詢列表 */ public static ListgetEmployeerList(String employeer_name,String employeer_department){ SqlSession session = null; List employeers=null; try { session = sqlSessionFactory.openSession(); EmployeerMapper employeerMapper=session.getMapper(EmployeerMapper.class); employeers = employeerMapper.findEmployeerByNameandDep(employeer_name, employeer_department); session.commit() ; } finally { session.close(); } return employeers; }
public static void main(String[] args) { System.out.println("=========================使用多單參數查詢==========================="); Listemployeers1=getEmployeerList("張三","產品二部"); for(Employeer employeer1:employeers1){ System.out.println(employeer1); }
方法二:
將 resultMap="resultMap" 改成 resultType="Employeer"
<!--多參數 查找 返回list 的select 語句,注意 resultMap 的值是指向前面定義好的 --> <select id="findEmployeerByNameandDep" resultType="Employeer"> select* from `t_employeer` where employeer_name=#{0} and employeer_department=#{1} </select>
方法三:Map封裝多參數
/** * 多參數查詢,注意要和Employeer.xml的方法名對應 */ public ListEmployeer.xml中添加:findEmployeerByNameandDep1(Map map);
<!--多參數 查找 返回list 的select 語句,注意 resultMap 的值是指向前面定義好的,注意key1和key2為傳入的map的鍵值 --> <select id="findEmployeerByNameandDep1" parameterType="map" resultType="Employeer"> select* from `t_employeer` where employeer_name=#{key1} and employeer_department=#{key2} </select>
測試使用:
/** * 多參數查詢列表,使用map */ public static ListgetEmployeerList(Map map){ SqlSession session = null; List employeers=null; try { session = sqlSessionFactory.openSession(); EmployeerMapper employeerMapper=session.getMapper(EmployeerMapper.class); employeers = employeerMapper.findEmployeerByNameandDep1(map); session.commit() ; } finally { session.close(); } return employeers; }
System.out.println("=========================使用多單參數map方式查詢==========================="); Mapmap = new HashMap (); map.put("key1", "明明"); map.put("key2", "財會部"); List employeers2=getEmployeerList(map); for(Employeer employeer2:employeers2){ System.out.println(employeer2); }
結果: