03-MyBatis_基本CRUD与动态SQL

0. 下载 

      MyBatis基本CRUD与动态SQL(带log4j.properties).zip

1. 基本 CRUD

 1.1 select

  1.1.1 查询单条记录

/*	
<select id="selectUserById" parameterType="string" resultType="com.hehe.mybatis.domain.User">
	select * from user where id = #{id}
</select>	
*/	@Test public void testSelectUserById() throws Exception {
		SqlSession session = sqlSessionFactory.openSession();
		// namespace + SQL-id 唯一标识一条SQL
		// namespace : com.hehe.mybatis.domain.User
		// SQL-id : selectUserById
		User user = session.selectOne("com.hehe.mybatis.domain.User.selectUserById", "0001");
		System.out.println(user);
		session.close();
	}

/*
<select id="selectUserByIdForMap" parameterType="string" resultType="java.util.HashMap">
	select * from user where id = #{id}
</select>	
*/	@Test public void testSelectUserByIdForMap() throws Exception {
		 SqlSession session = sqlSessionFactory.openSession();
	
		 Map<String, User> usermap= session.selectMap("com.hehe.mybatis.domain.User.selectUserByIdForMap", "0001", "id");	
		 
		 System.out.println(usermap.get("0001"));
		 
		 session.close();
	}

  1.1.2 查询多条记录

/*
<select id="selectAllUsers" resultType="com.hehe.mybatis.domain.User">
	select * from user
</select>	
*/	@Test public void testSelectAllUsers() throws Exception {
		SqlSession session = sqlSessionFactory.openSession();
		List<User> users = session.selectList("com.hehe.mybatis.domain.User.selectAllUsers");
		for (User user : users) {
			System.out.println(user);
		}
		session.close();
	}

 1.2 insert

/*
<insert id="insertUser" parameterType="com.hehe.mybatis.domain.User">
	insert into user(id, name, address) 
		values(#{id}, #{name}, #{address})
</insert>	
*/	@Test public void testInsertUser() throws Exception {
		SqlSession session = sqlSessionFactory.openSession();
		 
		User user = new User();
		user.setId("0003");
		user.setName("wangwu");
		user.setAddress("Guangdong");
		
		int affectedRows = session.insert("com.hehe.mybatis.domain.User.insertUser", user);
		System.out.println(affectedRows);
		
		// 注意要提交事务
		session.commit();
		session.close();
	}
 
 /*
<insert id="insertUserForMap" parameterType="java.util.HashMap">
	insert into user(id, name, address) 
		values(#{id}, #{name}, #{address})
</insert>
*/	@Test public void testInsertUserForMap() throws Exception {
		SqlSession session = sqlSessionFactory.openSession();
	  
		Map<String, Object> usermap = new HashMap<String, Object>();
		usermap.put("id", "0004");
		usermap.put("name", "zhaoliu");
		usermap.put("address", "Shenzhen");
		
		int affectedRows = session.insert("com.hehe.mybatis.domain.User.insertUserForMap", usermap);
		System.out.println(affectedRows);
		
		// 注意要提交事务
		session.commit();
		session.close();
 	}

 1.3 delete

/*
<delete id="deleteUsreById" parameterType="java.util.String">
	delete from user where id = #{id}	
</delete>
*/	@Test public void testDeleteUsreById() throws Exception {
	 	SqlSession session = sqlSessionFactory.openSession();
 
		int affectedRows = session.delete("com.hehe.mybatis.domain.User.deleteUsreById", "0004");
		System.out.println(affectedRows);
		
		// 注意要提交事务
		session.commit();
		session.close();
	}

 1.4 update    

/*
<update id="updateUserById" parameterType="com.hehe.mybatis.domain.User">
	update user
	set name =#{name}
		,address = #{address}
	where id = #{id}			
</update>
*/	@Test public void testUpdateUserById() throws Exception {
	  	SqlSession session = sqlSessionFactory.openSession();
	  
	  	User user = session.selectOne("com.hehe.mybatis.domain.User.selectUserById", "0001");
	  	user.setName("zhangsan00");
	  
	  	int affectedRows = session.update("com.hehe.mybatis.domain.User.updateUserById", user);
	  	System.out.println(affectedRows);
		  
	  	// 注意要提交事务
	  	session.commit();
	  	session.close();
  }

2. 动态SQL

 2.1 应用场景

    ①组合查询 <where> , 第一个条件无需 and来连接
    ②更新记录的某些字段 <set>, 第一个字段无需set关键字,且后需写逗号","

    ③等

    

 2.2  实例

/*
<!-- dynamic SQL -->
<select id="selectUserByCondition" parameterType="com.hehe.mybatis.domain.User" resultType="com.hehe.mybatis.domain.User">
	select * from user 
	where 1 = 1
	  <if test="id != null"> and id = #{id} </if>
	  <if test="name != null"> and name = #{name} </if>
	  <if test="address != null"> and address = #{address} </if>
</select>	
*/	@Test public void testSelectUserByCondition() throws Exception {
		 SqlSession session = sqlSessionFactory.openSession();
		 
		 User user = new User();
//		 user.setId("0001");
		 user.setName("lisi");
		 
		 List<User> users = session.selectList("com.hehe.mybatis.domain.User.selectUserByCondition", user);
		 
		 for (User u : users) {
			 System.out.println(u);
		 }
		 
		 session.close();
	}

/*
<!-- dynamic SQL -->
<select id="selectUserByCondition2" parameterType="com.hehe.mybatis.domain.User" resultType="com.hehe.mybatis.domain.User">
	select * from user 
	<where>
	  <if test="id != null"> id = #{id} </if>
	  <if test="name != null"> and name = #{name} </if>
	  <if test="address != null"> and address = #{address} </if>
	</where>
</select>	
*/	@Test public void testSelectUserByCondition2() throws Exception {
		 SqlSession session = sqlSessionFactory.openSession();
		 
		 User user = new User();
//		 user.setId("0001");
		 user.setName("lisi");
		 
		 List<User> users = session.selectList("com.hehe.mybatis.domain.User.selectUserByCondition2", user);
		 
		 for (User u : users) {
			 System.out.println(u);
		 }
		 
		 session.close();
	}

/*
<update id="updateUserById2" parameterType="com.hehe.mybatis.domain.User">
	update user
	<set>
		<if test="name != null">
			name =#{name},
		</if>
		<if test="address != null">
			address = #{address}
		</if>			
	</set>
	where id = #{id}			
</update>	
*/	@Test public void testUpdateUserById2() throws Exception {
	  	SqlSession session = sqlSessionFactory.openSession();
	  
	  	User user = new User();
	  	user.setId("0001");
//	  	user.setName("zhangsan999");
	  	user.setAddress("Changchun2");
	  
	  	int affectedRows = session.update("com.hehe.mybatis.domain.User.updateUserById2", user);
	  	System.out.println(affectedRows);
		  
	  	// 注意要提交事务
	  	session.commit();
	  	session.close();
  }