MyBatis期末复习(SIT)

1.导入jar包

2.在类路径下创建包结构

3.编写db.properties和mybatis-config配置文件

在pojo下编写（也可称为bean，domain等）

编写必要的getter,setter,constructor(无参，全参)，toString方法

public interface UserMapper {
	
	List findAllUser();
	
    //@Param("xxx")中与#{xxx}应一致 
	User findUserById(@Param("id")Integer id);
	
	int deleteUserById(@Param("id")Integer id);
	
	int insertUser(User user);
	//使用动态sql查询
	int updateUser(User user);
	
	//动态sql条件查询
	User findUserByCase(Map map);
}

注意：

因为我们数据库中的字段名称和实体类的不一致，因此我们需要使用到ResultMap

此外应注意动态sqL中后有逗号，中没有逗号

	
		select * from users
	
	
	
		select * from users where userid = #{id}
	
	
	
		insert into users(uname,upwd,limit) values(#{username},#{password},#{limit})
	
	
	
		delete from users where userid=#{id} 
	
	
	
		update users 
		
			
				uname = #{username},
			
			
				upwd = #{password},
			
			
				limit = #{limit}
			
		
		where userid = #{id}
	
	
	
		select * from users
		
			
				uname like '%'+#{username}+'%'
			
			
				and upwd = #{password}
			
			
				and limit = #{limit}
			
			
				 and userid = #{id}
			
		
	
	
	
		
		
		
		
	
    

 
5.编写测试类 
package com.heng.test;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import com.heng.dao.UserMapper;
import com.heng.pojo.User;
import com.heng.utils.MybatisUtils;

public class UserTest {
	
	@Test
	public void testFindUserAll(){
		SqlSession sqlSession = MybatisUtils.getSqlSession();
		
		UserMapper mapper = sqlSession.getMapper(UserMapper.class);
		
		List findAllUser = mapper.findAllUser();
		
		for(User user : findAllUser){
			System.out.println(user);
		}
		
		sqlSession.close();
	}
	
	
	@Test
	public void testFindUserById(){
		SqlSession sqlSession = MybatisUtils.getSqlSession();
		
		UserMapper mapper = sqlSession.getMapper(UserMapper.class);
		
		User findUserById = mapper.findUserById(8);
		System.out.println(findUserById);
		
		sqlSession.close();
	}
	
	@Test
	public void testAddUser(){
		SqlSession sqlSession = MybatisUtils.getSqlSession();
		
		UserMapper mapper = sqlSession.getMapper(UserMapper.class);
		
		User user = new User();
		user.setLimit(1);
		user.setUsername("zmz");
		user.setPassword("123456");
		int insertUser = mapper.insertUser(user);
		if(insertUser>0){
			System.out.println("添加成功");
		}else{
			System.out.println("添加失败");
		}
		sqlSession.close();
		
	}
	
	@Test
	public void deleteUser(){
		SqlSession sqlSession = MybatisUtils.getSqlSession();
		UserMapper mapper = sqlSession.getMapper(UserMapper.class);
		int deleteUserById = mapper.deleteUserById(17);
		
		if(deleteUserById>0){
			System.out.println("删除成功");
		}
		
		sqlSession.close();
	}
	
	@Test
	public void updateUser(){
		SqlSession sqlSession = MybatisUtils.getSqlSession();
		UserMapper mapper = sqlSession.getMapper(UserMapper.class);
		
		User user = new User(15,"测试","123456",1);
		int updateUser = mapper.updateUser(user);
		
		if (updateUser>0) {
			System.out.println("数据修改成功");
		}
		sqlSession.close();
		
	}
	
	@Test
	public void  findUserByCase(){
		SqlSession sqlSession = MybatisUtils.getSqlSession();
		UserMapper mapper = sqlSession.getMapper(UserMapper.class);
		
		Map map = new HashMap<>();
		map.put("id", "8");
		map.put("username", "xl");
		User findUserByCase = mapper.findUserByCase(map);
		
		System.out.println(findUserByCase);
		
		sqlSession.close();
	}

}
 
3.多表多对一 
类型------>用户
 
3.1 环境搭建 
在原有User表的基础上增加一个角色权限表Power
 
 是User表中limit字段设为外键，关联与Power表中的id字段
 
User实体类
 
 Power实体类
 
public class Power {
	private int id;
	private String role;
    //toString方法
    //getter和setter
    //constructor有参，无参
}
 
3.2 基于xml实现 
UsersMapper
 
public interface UsersMapper {
	List findAllUser();
}
 
PowerMapper
 
public interface PowerMapper {

	@Select("select * from power")
	List powerList();
    
	//根据id查询身份信息
	@Select("select * from power where id = #{id}")
	Power findPowerById(@Param("id") Integer id);
	
}
 
UserMapper
 





	
		select * from users
	
	
	 
		
		
		
		
		
	
	

 
3.3 基于注解实现 
public interface UsersMapper {
	List findAllUser();
	
	
	@Select("select * from users where userid = #{id}")
	@Results({
		@Result(column ="userid",property="id"),
		@Result(column ="uname",property="username"),
		@Result(column ="upwd",property="password"),
		@Result(column ="limit",property="power",one=@One(select = "com.heng.dao.PowerMapper.findPowerById"))
	})
	//根据id查询用户信息
	Users findUsersById(@Param("id")Integer id);
}
 
4.多表一对多 
4.1环境搭建 
角色对应多个用户
 
Power实体类
 
public class Power {
	private int id;
	private String role;
	private List users;
    //getter和setter方法
    //有参无参构造
    //toString
}
 
User实体类
 
public class Users {
	private Integer id;
	private String username;
	private String password;
	private Integer limit
	private Power power;
    //getter和setter方法
    //有参无参构造
    //toString 我们这里不需要重写带有参数power的toString方法
}
 
4.2 子查询实现 
PowerMapper接口
 
public interface PowerMapper {

	Power findPowerById(@Param("id") Integer id);
}
 
PowerMapper.xml
 





	
		select * from power where id = #{id}
	
	
	 
		
		
		
		
	
	

UserMapper接口

这里用注解实现

public interface UsersMapper {
	
	@Select("select * from users where limit = #{id}")
	@Results({
		@Result(column ="userid",property="id"),
		@Result(column ="uname",property="username"),
		@Result(column ="upwd",property="password")
	})
	Users findUsersById1(@Param("id")Integer id);
	
}

		select p.id pid,p.role role,u.* from power p,users u where p.id = u.limit and p.id = #{id}
	
	
	
		
		
		
		
			
			
			
		
	

在删除一对多多表中的字段（Power表中的role时），将所有一表中关联此字段的所有信息删除

PowerMapper接口

public interface PowerMapper {
	
	int deletePower(Integer id);
}

PowerMapper.xml

		delete from power where id = #{id}
		delete from users where limit = #{id}

说明：

如果我们使用的MySQL数据库，当我们连接MySQL服务器时候需要加上参数 ?allowMultiQueries=true,SQLServer不加也可以执行成功

PowerMapper接口

public interface PowerMapper {
    int addPower(Power power);
}

PowerMapper.xml

==注意：==这里使用SQLSserver的方法，其没有内置函数LAST_INSERT_ID

		insert into power(role) values(#{role})

UserMapper接口

public interface UsersMapper {
	int insertUsers(Users users);
}

UserMapper.xml

	
		insert into users(uname,upwd,limit) values(#{username},#{password},#{limit})
	

Test

@Test
public void test(){
    Power power = new Power();
    power.setRole("董事长");
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    PowerMapper mapper = sqlSession.getMapper(PowerMapper.class);
    mapper.addPower(power);

    //创建Users对象
    Users users = new Users();
    users.setUsername("lxz");
    users.setPassword("123123");
    System.out.println(power.getId());
    users.setLimit(power.getId());

    UsersMapper mapper2 = sqlSession.getMapper(UsersMapper.class);
    mapper2.insertUsers(users);

    sqlSession.close();

}

最后预祝大家都不挂科，实习练得一身本领，好好复习，90+没问题！