03-MyBatis_基本CRUD与动态SQL

今天药忘吃喽~ 2022-09-17 12:22 151阅读 0赞

#  #

# 0. 下载  #

[MyBatis基本CRUD与动态SQL(带log4j.properties).zip][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 实例 ##

/*
    
    <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();
    	}
    
    /*
    
    <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();
      }

[MyBatis_CRUD_SQL_log4j.properties_.zip]: http://download.csdn.net/detail/wuqinfei_cs/6423963