MyBatis总结（四）

基于注解的单表增删改查

MyBatis提供了@Insert，@Delete，@Update，@Select等常用注解，可以实现数据库的增删改查

public interface UserInfoMapper{
	@Select("select * from user_info where id=#{id}")
	public UserInfo findUserInfoById(int id);
	@Select("select * from user_info where userName like concat(concat('%',#{userName}),'%')")
	public List<UserInfo> findUserInfoByUserName(String userName);
	@Insert("insert into user_info(userName,password) values(#{userName},#{password})")
	public int addUserInfo(UserInfo ui);
	@Update("update user_info set userName=#{userName},password=#{password} where id=#{id}")
	public int updateUserInfo(UserInfo ui);
	@Delete("delete from user_info where id=#{id}")
	public int deleteUserInfo(int id);
}

如果实体类属性和数据表字段名保持一致，MyBatis会自动完成结果映射，如果不一致则需要@Result注解手动完成结果映射
mybatis配置文件

<mappers>
	<mapper class="com.mybatis.mapper.UserInfoMapper">
</mappers>

测试类

//根据id查询用户
@Test
public void testFindUserInfoById(){
	UserInfoMapper um=sqlSession.getMapper(UserInfoMapper.class);
	UserInfo ui = um.findUserInfoById(1);
	sout(ui.toString());
}
//根据用户名模糊查询用户信息
@Test
public void testFindUserInfoByUserName(){
	UserInfoMapper um=sqlSession.getMapper(UserInfoMapper.class)
	List<UserInfo> uis=um.findUserInfoByUserName("j");
	for(UserInfo ui:uis){
		sout(ui.toString());
	}
}
//添加用户
@Test
public void testAddUserInfo(){
	UserInfoMapper um = sqlSession.getMapper(UserInfoMapper.class);
	UserInfo ui = new UserInfo();
	ui.setUserName("mybatis");
	ui.setPassword("123456");
	int result = um.addUserInfo(ui);
	if(result>0){
		sout("插入成功");
	}else{
		sout("插入失败");
	}
}
//修改用户
@Test
public void testUpdateUserInfo(){
	UserInfoMapper um=sqlSession.getMapper(UserInfoMapper.class);
	UserInfo ui = um.findUserInfoById(8);
	ui.setPassword("123123");
	int result = um.updateUserInfo(ui);
	if(result>0){
		sout("插入成功");
		sout(ui);
	}else{
		sout("插入失败");
	}
}
//删除用户
@Test
public void testDeleteUserInfo(){
	UserInfoMapper um=sqlSession.getMapper(UserInfoMapper.class);
	int result = um.deleteUserInfo(8);
	if(result){
		sout("成功删除了"+result+"条记录");
	}else{
		sout("插入删除");
	}
}

基于注解的一对一关联映射

基于注解配置实现这两张表之间的一对一关联映射
使用数据库表idcard和person

IdcardMapper接口

@Select("select * from idcard where id=#{id}")
public Idcard findIdcardById(int id);

PersonMapper接口

public interface PersonMapper{
	@Select("select * from person where id=#{id}")
	@Results({@Result(column = "cid",property="idcard",
	one=@One(select="com.mybatis.mapper.IdcardMapper.findIdcardById"))})
	//根据id查询个人信息
	public Person findPersonById(int id);
}

在@Results注解中，可以包含多个@Result注解，一个Result注解完成实体类中一个属性和数据表中一个字段的映射
在Result注解中，property属性用来指定关联属性，one属性用来指定数据表属于那种关联关系，通过@One注解表明数据表idcard和person之间是一对一关联映射，column属性用于指定传入findIdcardById（int id）的方法的参数名
添加接口文件引用

<mappers>
	<mapper class="com.mybaits.mapper.IdcardMapper" />
	<mapper class="com.mybatis.mapper.PersonMapper" />
</mapper>

测试方法

public void testOne2One{
	PersonMapper pm=sqlSession.getMapper(PersonMapper.class);
	Person person = pm.findPersonById(1);
	sout(person);
}

查询了person的同时也查询了idcard

基于注解的一对多关联映射

使用数据库表type和product_info
ProductInfoMapper接口

public interface ProductInfoMapper{
	//根据类型编号查询所有商品
	@Select("select * from product_info where tid=#{tid}")
	List<ProductInfo> findProductInfoByTid(int tid);
	@Select("select * from prouct_info where id=#{id}")
	@Results({@Result(column="tid",property="type",
	one=@One(select="com.mybatis.mapper.TypeMapper.findTypeById"))})
	ProductInfo findProductInfoByid(int id);
}

TypeMapper接口

public interface TypeMapper{
	@Select("select * from type where id=#{id}")
	@Results({@Result(id = true,column="id",property="id"),
	@Result(column="name",property="name"),
	@Result(column="id",property="pis",
	many=@Many(select="com.mybatis.mapper.ProductInfoMapper.findProductInfoById"))})
	Type findTypeById(int id);
}

接口文件的引用

<mappers>
	<mapper class="com.mybatis.mapper.TypeMapper" />
	<mapper class="com.mybatis.mapper.ProductInfoMapper" />
</mappers>

测试类

//一对多映射
@Test
public void testOne2Many(){
	TypeMapper tm = sqlSession.getMapper(TypeMapper.class)
	Type type = tm.findTypeById(1);
	sout(type.toString());
}
//多对一映射
@Test
public void testMany2One(){
	ProductInfoMapper pim=sqlSession.getMapper(ProductInfoMapper.class);
	ProductInfo pi = pim.findProductInfoById(1);
	sout(pi.toStirng());;
	sout(pi.getType());
}

基于注解的多对多关联映射

FunctionMapper接口

public interface FunctionMapper{
	//根据管理员id获取其功能权限
	@Select("select * from funcitons where id in(select fid from powers where aid=#{id})")
	List<Functions> findFunctionsByAid(int aid);
}

AdminInfoMapper接口

public interface AdminInfoMapper(){
	//根据管理员id查询管理员信息
	@Select("select * from admin_info where id=#{id}")
	@Results({@Result(id="true" column="id" property="id"),
	@Result (column="name" property="name"),
	@Result(column="id" 
	property="fs",many=@Many(select="com.mybaits.mapper.FunctionsMapper.findFunctionMapper"))})
	public AdminInfo findAdminInfoById(int id);
}

然后添加接口文件引用和测试类

基于注解的动态SQL

1. @SelectProvider
   编写UserInfoMapper接口

public interface UserInfoMapper{
	@SelectProvider(type=UserInfoDynaSqlProvider.class,method="selectWithParam")
	List<UserInfo> findUserInfoByCond(Map<String,Object> param);
}

编写UserInfoDynaSqlProvider

public class UserInfoDynaSqlProvider{
	public String selectWithParam(Map<String,Object> param){
		return new SQL(){
		{
			SELECT("*");
			FROM("user_info");
			if(param.get("id")!=null){
				WHERE("id=#{id}");
			}
			if(param.get("userName")!=null){
				WHERE("userName=#{userName}");
			}
			if(param.get("password")!=null){
				WHERE("password=#{password}");
				}
		}
	}.toString();
	}
}

根据param中的内容构建动态SELECT语句
添加接口文件的引用

<mappers>
	<mapper class="com.mybaits.mapper.UserInfoMapper" />
</mappers>

测试类方法

@Test
public void testfindUserInfoByCond(){
	UserInfoMapper uim=sqlSession.getMapper(UserInfoMapper.class);
	Map<String,Object> param= new HashMap<String,Object>();
	param.put("userName","tom");
	param.put("password","123456");
	List<UserInfo> uis=uim.findUserInfoByCond(param);
	for(UserInfo ui :uis){
		sout(ui.toString());
	}
}

2. @InsertProvider
   编写接口

@InsertProvide(type=UserInfoDynaSqlProvider.class,method="insertUserInfo")
@Options(useGenerateKeys = true,keyproperty="id")
int insertUserInfo(UserInfo ui);

因为数据表中有一个自增主键id，为了能在插入数据后自动获取该主键值，可以使用@Options注解返回添加的主键值，
keypropery属性用来设置主键值对应的字段名，将userGeneratedKeys属性设置为true，自动将主键字段id的自增值赋值给插入的对象的id

编写insert实现类

public String insertUserInfo(UserInfo ui){
	return new SQL(){
	{
		INSERT_INTO("user_info");
		if(ui.getUserName()!=null){
			VALUES("userName","#{userName}");
		}
		if(ui.getPassword()!=null){
			VALUES("password","#{password}");
		}
	}
	}.toString();
}

引用接口文件 略
测试代码

@Test
public void testInsertUserInfo(){
	UserInfoMapper uim=sqlSession.getMapper(UserInfoMapper.class);
	UserInfo ui = new UserInfo();
	ui.setUserName("mybatis2");
	ui.setPassword("123456");
	//直接调用接口方法
	uim.insertUserInfo(ui);
	sout(ui.getId());
}

3. @UpdateProvider
4. @DeleteProvider
   剩下的这两个和前面的没什么区别