mybatis实践之动态sql+重用语句块（include）

1.com.dao（.java+.xml）

public interface ShopMapper {

	List<Shop> queryLike(String shopName);
	
	List<Shop> queryByCondition(Shop shop);
	
	List<Shop> queryByCwo(Shop shop);
	
	List<Shop> queryWhere(Shop shop);
	
	List<Shop> queryForeachByList(List<Integer> list);
	
	List<Shop> queryForeachByArray(int[] ids);
	
	List<Shop> queryForeachByMap(Map<String, int[]> map);
	
	void callProc(Map<String, String> map);
}

————————————————————————————————————————
<mapper namespace="com.dao.ShopMapper">
	<!-- 指定可重用的语句块 -->
	<sql id="commSql">
		<choose>
			<when test="shopName != null and shopName != ''">
				and shopname like '%${shopName}%'
			</when>
			
			<when test="shopAddress != null and shopAddress != ''">
				and shopAddress like '%${shopAddress}%'
			</when>
			
			<when test="_parameter.contact != null and _parameter.contact != ''">
				and contact like '%${contact}%'
			</when>
			
			<otherwise>
				and shopid=5
			</otherwise>
		</choose>
	</sql>

	<select id="queryLike" resultType="Shop">
		select * from shop where 1=1
		<!-- <if test="value != null and value != ''">
			shopname like '%${value}%'
		</if> -->
		<!-- _parameter:表示接口方法中的参数 -->
		<if test="_parameter != null and _parameter != ''">
			and shopname like '%${value}%'
		</if>
	</select>
	
	<select id="queryByCondition" resultType="Shop">
		select * from shop where 1=1
		<!-- _parameter:表示接口方法中的参数 -->
		<!-- <if test="_parameter != null"> -->
			<if test="shopName != null and shopName != ''">
				and shopname like '%${shopName}%'
			</if>
			
			<if test="shopAddress != null and shopAddress != ''">
				and shopAddress like '%${shopAddress}%'
			</if>
			
			<if test="_parameter.contact != null and _parameter.contact != ''">
				and contact like '%${contact}%'
			</if>
		<!-- </if> -->
	</select>
	
	<select id="queryByCwo" resultType="Shop">
		select * from shop where 1=1
		<include refid="commSql"/>
	</select>
	
	<select id="queryWhere" resultType="Shop">
		select * from shop
		<where>
			<include refid="commSql"/>
		</where>
	</select>

	<select id="queryForeachByList" resultType="shop">
		select * from shop where shopid in
		<foreach collection="list" item="sid" open="(" separator="," close=")">
			#{sid}
		</foreach>
	</select>
	
	<select id="queryForeachByArray" resultType="shop">
		select * from shop where shopid in
		<foreach collection="array" item="sid" open="(" separator="," close=")">
			#{sid}
		</foreach>
	</select>
	
	<select id="queryForeachByMap" resultType="shop">
		select * from shop where shopid in
		<foreach collection="ids" item="sid" open="(" separator="," close=")">
			#{sid}
		</foreach>
	</select>
	
	<select id="callProc" statementType="CALLABLE">
		call proc_shop(#{shopId},#{aaa,mode=OUT,jdbcType=VARCHAR})
	</select>
</mapper>

2.com.pojo

public class Shop {

	private String shopId;
	
	private String shopName;
	
	private String shopAddress;
	
	private String contact;

	public String getShopId() {
		return shopId;
	}

	public void setShopId(String shopId) {
		this.shopId = shopId;
	}

	public String getShopName() {
		return shopName;
	}

	public void setShopName(String shopName) {
		this.shopName = shopName;
	}

	public String getShopAddress() {
		return shopAddress;
	}

	public void setShopAddress(String shopAddress) {
		this.shopAddress = shopAddress;
	}

	public String getContact() {
		return contact;
	}

	public void setContact(String contact) {
		this.contact = contact;
	}
}

3.com.service

public class ShopService {

	public List<Shop> queryLike(String shopName) {
		
		SqlSession session = null;
		
		try {
			session = MyBatisUtil.getSqlSession();
			ShopMapper mapper = session.getMapper(ShopMapper.class);
			return mapper.queryLike(shopName);
		} finally {
			MyBatisUtil.close(session);
		}
	}
	
	public List<Shop> queryByCondition(Shop shop) {
		
		SqlSession session = null;
		
		try {
			session = MyBatisUtil.getSqlSession();
			ShopMapper mapper = session.getMapper(ShopMapper.class);
			return mapper.queryByCondition(shop);
		} finally {
			MyBatisUtil.close(session);
		}
	}
	
	public List<Shop> queryByCwo(Shop shop) {
		
		SqlSession session = null;
		
		try {
			session = MyBatisUtil.getSqlSession();
			ShopMapper mapper = session.getMapper(ShopMapper.class);
			return mapper.queryByCwo(shop);
		} finally {
			MyBatisUtil.close(session);
		}
	}
	
	public List<Shop> queryWhere(Shop shop) {
		
		SqlSession session = null;
		
		try {
			session = MyBatisUtil.getSqlSession();
			ShopMapper mapper = session.getMapper(ShopMapper.class);
			return mapper.queryWhere(shop);
		} finally {
			MyBatisUtil.close(session);
		}
	}
	
	public List<Shop> queryForeachByList(List<Integer> list) {
		
		SqlSession session = null;
		
		try {
			session = MyBatisUtil.getSqlSession();
			ShopMapper mapper = session.getMapper(ShopMapper.class);
			return mapper.queryForeachByList(list);
		} finally {
			MyBatisUtil.close(session);
		}
	}
	
	public List<Shop> queryForeachByArray(int[] ids) {
		
		SqlSession session = null;
		
		try {
			session = MyBatisUtil.getSqlSession();
			ShopMapper mapper = session.getMapper(ShopMapper.class);
			return mapper.queryForeachByArray(ids);
		} finally {
			MyBatisUtil.close(session);
		}
	}
	
	public List<Shop> queryForeachByMap(Map<String, int[]> map) {
		
		SqlSession session = null;
		
		try {
			session = MyBatisUtil.getSqlSession();
			ShopMapper mapper = session.getMapper(ShopMapper.class);
			return mapper.queryForeachByMap(map);
		} finally {
			MyBatisUtil.close(session);
		}
	}
	
	public void callProc(Map<String, String> map) {
		
		SqlSession session = null;
		
		try {
			session = MyBatisUtil.getSqlSession();
			ShopMapper mapper = session.getMapper(ShopMapper.class);
			mapper.callProc(map);
		} finally {
			MyBatisUtil.close(session);
		}
	}
}

4.com.test

public class TestMyBatis {

	private ShopService service = new ShopService();
	
	@Test
	public void testQueryLike() {
		
		List<Shop> list = service.queryLike("店");
		
		for(Shop shop : list) {
			String shopId = shop.getShopId();
			String shopName = shop.getShopName();
			String shopAddress = shop.getShopAddress();
			String contact = shop.getContact();
			
			System.out.println(shopId + "\t" + shopName + "\t" + shopAddress + "\t" + contact);
		}
	}
	
	@Test
	public void testQueryByCondition() {
		
		Shop s = new Shop();
		s.setContact("56");
//		s.setShopAddress("号");
//		s.setShopName("店");
		
		List<Shop> list = service.queryByCondition(s);
		
		for(Shop shop : list) {
			String shopId = shop.getShopId();
			String shopName = shop.getShopName();
			String shopAddress = shop.getShopAddress();
			String contact = shop.getContact();
			
			System.out.println(shopId + "\t" + shopName + "\t" + shopAddress + "\t" + contact);
		}
	}
	
	@Test
	public void testQueryByCwo() {
		
		Shop s = new Shop();
		s.setContact("56");
		s.setShopAddress("号");
//		s.setShopName("店");
		
		List<Shop> list = service.queryByCwo(s);
		
		for(Shop shop : list) {
			String shopId = shop.getShopId();
			String shopName = shop.getShopName();
			String shopAddress = shop.getShopAddress();
			String contact = shop.getContact();
			
			System.out.println(shopId + "\t" + shopName + "\t" + shopAddress + "\t" + contact);
		}
	}
	
	@Test
	public void testQueryWhere() {
		
		Shop s = new Shop();
		s.setContact("56");
		s.setShopAddress("号");
		s.setShopName("店");
		
		List<Shop> list = service.queryWhere(s);
		
		for(Shop shop : list) {
			String shopId = shop.getShopId();
			String shopName = shop.getShopName();
			String shopAddress = shop.getShopAddress();
			String contact = shop.getContact();
			
			System.out.println(shopId + "\t" + shopName + "\t" + shopAddress + "\t" + contact);
		}
	}
	
	@Test
	public void testForeachList() {

		List<Integer> ids = new ArrayList<Integer>();
		ids.add(1);
		ids.add(3);
		ids.add(5);
		
		List<Shop> list = service.queryForeachByList(ids);
		
		for(Shop shop : list) {
			String shopId = shop.getShopId();
			String shopName = shop.getShopName();
			String shopAddress = shop.getShopAddress();
			String contact = shop.getContact();
			
			System.out.println(shopId + "\t" + shopName + "\t" + shopAddress + "\t" + contact);
		}
	}
	
	@Test
	public void testForeachArray() {

		int[] ids = {2,4,6};
		
		List<Shop> list = service.queryForeachByArray(ids);
		
		for(Shop shop : list) {
			String shopId = shop.getShopId();
			String shopName = shop.getShopName();
			String shopAddress = shop.getShopAddress();
			String contact = shop.getContact();
			
			System.out.println(shopId + "\t" + shopName + "\t" + shopAddress + "\t" + contact);
		}
	}
	
	@Test
	public void testForeachMap() {

		int[] ids = {8,9,10};
		Map<String, int[]> map = new HashMap<String, int[]>();
		map.put("ids", ids);
		
		List<Shop> list = service.queryForeachByMap(map);
		
		for(Shop shop : list) {
			String shopId = shop.getShopId();
			String shopName = shop.getShopName();
			String shopAddress = shop.getShopAddress();
			String contact = shop.getContact();
			
			System.out.println(shopId + "\t" + shopName + "\t" + shopAddress + "\t" + contact);
		}
	}
	
	@Test
	public void testProc() {

		Map<String, String> map = new HashMap<String, String>();
		map.put("shopId", "5");

		service.callProc(map);
		
		System.out.println(map.get("aaa"));
	}
}

5.com.util

public final class MyBatisUtil {

	private static SqlSessionFactory factory = buildSqlSessionFactory();
	
	private MyBatisUtil() {}
	
	private static final SqlSessionFactory buildSqlSessionFactory() {
		
		String resource = "resources/mybatis-config.xml";
		
		try {
			InputStream inputStream =      Resources.getResourceAsStream(resource);
			return new SqlSessionFactoryBuilder().build(inputStream);
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return null;
	}
	
	public static final SqlSession getSqlSession() {
		
		return factory.openSession();
	}
	
	public static final void close(SqlSession sqlSession) {
		
		if(sqlSession != null) {
			sqlSession.close();
		}
	}
}

6.resources(db.properties+mybatis-config.xml)

jdbc.driver=oracle.jdbc.OracleDriver
jdbc.url=jdbc:oracle:thin:@localhost:1521:orcl
jdbc.username=meitao
jdbc.password=123
___________________________________________
<configuration>
	<!-- 外部属性文件 -->
	<properties resource="resources/db.properties"/>

	<settings>
		<!-- 控制台显示sql语句(开发使用) -->
		<setting name="logImpl" value="STDOUT_LOGGING"/>
	</settings>
	
	<typeAliases>
  		<package name="com.pojo"/>
	</typeAliases>

	<environments default="development">
		<environment id="development">
			<transactionManager type="JDBC"/>
			
			<dataSource type="POOLED">
				<property name="driver" value="${jdbc.driver}"/>
				<property name="url" value="${jdbc.url}"/>
				<property name="username" value="${jdbc.username}"/>
				<property name="password" value="${jdbc.password}"/>
			</dataSource>
		</environment>
	</environments>
	
	<mappers>
		<!-- <mapper resource="resources/ShopMapper.xml"/> -->
		
		<!-- 注册指定包中的所有映射器接口(推荐) -->
		<package name="com.dao"/>
	</mappers>
</configuration>