Mybatis注解
一、使用MyBatis注解开发，可以省去类配置文件，简洁方便。但是比较复杂的SQL和动态SQL还是建议书写类配置文件。
二、具体实例：
1、定义实体类（Employee）：
public class Employee implements Serializable {
private static final long serialVersionUID = 1L;
private Integer id;
private BigInteger empid;
private String name;
private String sex;
private Date birthday;
private String address;
private String education;
}
2、定义代理(EmployeeMapper)
public interface EmployeeMapper {
@Select("select * from employee where id=#{id}")
public Employee selectEmployeeById(Integer id)throws Exception;
@Select("select * from employee")
public List<Employee> selectAllEmployee()throws Exception;
@Insert("insert into employee(empid,name,sex,birthday,address,education)")
public void insertEmployee(Employee e)throws Exception;
@Update("update employee set empid=#{empid},name=#{name},sex={sex},birthday=#{birthday},address=#{address},education=#{education}")
public void updateEmployee(Employee e)throws Exception;
@Delete("delete * from employee where id=#{id}")
public void deleteEmployee(Integer id)throws Exception;
}
3、在mybatis-config中加载（不需要EmployeeMapper.xml文件）
<mappers>
<mapper class="com.mybatis.mapper.EmployeeMapper"/>
</mappers>
4、测试
SqlSession session = DBTools.getSqlSession();
EmployeeMapper mapper = session.getMapper(EmployeeMapper.class);
List<Employee> list = mapper.selectAllEmployee();
for(Employee e : list) {
System.out.println(e);
}
三、关键注解词 ：
@Insert ： 插入sql , 和xml insert sql语法完全一样
@Select ： 查询sql, 和xml select sql语法完全一样
@Update ： 更新sql, 和xml update sql语法完全一样
@Delete ： 删除sql, 和xml delete sql语法完全一样
@Param ： 入参
@Results ： 结果集合
@Result ： 结果
示例：
1、定义实体类(Customer)
public class Customer implements Serializable {
private static final long serialVersionUID = 1L;
private Integer c_id;
private String c_name;
private String c_sex;
private Date c_birthday;
private String c_address;
}
2、定义代理(CustomerMapper)
public interface CustomerMapper {
@Insert("insert into customer(name,gender,birthday,address)values(#{c_name},"+
"#{c_sex},#{c_birthday},#{c_address})")
@Options(useGeneratedKeys=true,keyProperty="c_id")
int saveCustomer(Customer customer);
@Delete("delete from customer where id=#{id}")
int removeCustomer(@Param("c_id") Integer id);
@Update("update customer set name=#{c_name},birthday=#{c_birthday},sex=#{c_sex},address=#{c_address}"+
"where id=#{c_id}")
void modifyCustomer(Customer customer);
@Select("select * from customer where e_id=#{id}")
@Results({
@Result(id=true,column="id",property="c_id"),
@Result(column="name",property="c_name"),
@Result(column="sex",property="c_sex"),
@Result(column="birthday",property="c_birthday"),
@Result(column="address",property="c_address")
})
Customer selectCustomerById(Integer id);
@Select("select * from customer")
List<Customer> selectAllCustomer();
}
3、主描述文件中增加如下语句：
<mapper class="com.mybatis.mapper.CustomerMapper"/>
4、测试(有注解就不需要映射文件)
SqlSession session = DBTools.getSqlSession();
CustomerMapper mapper = session.getMapper(CustomerMapper.class);
Customer customer = new Customer();
customer.setC_name("江小白");
customer.setC_sex("男");
customer.setC_address("四川夔州");
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
customer.setC_birthday(dateFormat.parse("2012-09-09"));
int n = mapper.saveCustomer(customer);
session.commit();
System.out.println(n>0?"true":"false");
注：
1、调用saveCustomer方法，会执行@Insert注解中的sql语句。该方法中还使用了@Options注解；属性useGeneratedKeys=true
表示使用数据库自动增长的主键，该操作需要底层数据库的支持。keyProperty="c_id"表示将插入数据库生成的主键设置到customer
对象的c_id当中。
2、调用selectCustomerById方法，会执行@Select注解中的SQL语句。@Result注解用于列和属性之间的结果映射，如果列和属性名称相同
则可以省略该注解，Mybatis会自动进行映射
四、一对一关联：
1、定义代理：
public interface EmployeeMapper {
@Select("select * from employee")
@Results({
@Result(id=true,column="e_id",property="e_id"),
@Re sult(column="e_name",property="e_name"),
@Result(column="e_sex",property="e_sex"),
@Result(column="e_birthday",property="e_birthday"),
@Result(column="e_address",property="e_address"),
@Result(column="dep_id",property="department",one=@One(select="findDepartmentById",fetchType=FetchType.LAZY))
})
public List<Employee> findEmpoyeeAndDepartment();
@Select("select * from department where dep_id=#{id}")
public Department findDepartmentById(Integer id);
}
2、测试：
SqlSession session = DBTools.getSqlSession();
EmployeeMapper mapper = session.getMapper(EmployeeMapper.class);
List<Employee> emp = mapper.findEmpoyeeAndDepartment();
emp.forEach(Employee->System.out.println(emp));
五、一对多关联：客户与订单之间的关系（一个客户有多个订单，设计表时在多端设有一端的主键列，形成主外键关联）
1、实体类的定义：
public class User implements Serializable {
private static final long serialVersionUID = 1L;
private int id; //编号
private String username; //用户名
private String gender; //性别
private Date birthday; //生日
private String address; //地址
private List<Orders> OrdersList;//订单列表
}
public class Orders implements Serializable {
private static final long serialVersionUID = 1L;
private Integer id;//流水号
private Integer userId;//用户编号
private String number;//订单编号
private Date createTime;//创建时间
private String note;//备注
}
2、创建代理（Mapper）:
public interface UserMapper {
@Select("select * from user")
@Results({
@Result(id=true,column="user_id",property="id"),
@Result(column="user_name",property="username"),
@Result(column="user_gender",property="gender"),
@Result(column="user_birthday",property="birthday"),
@Result(column="user_address",property="address"),
@Result(property="OrdersList",column="user_id",many=@Many(select="findOrdersById",fetchType=FetchType.LAZY))
})
public List<User> findUserAndOrders() throws Exception;
@Select("select * from orders where user_id=#{id}")
@Results({
@Result(id=true,column="order_id",property="id"),
@Result(column="user_id",property="userId"),
@Result(column="order_createTime",property="createTime"),
@Result(column="order_note",property="note"),
@Result(column="order_number",property="number")
})
public List<Orders> findOrdersById(Integer id)throws Exception;
}
六、多对多关联
1、概念模型（CDM）：
2、物理模型（PDM）:
3、类的定义：
public class Users implements Serializable {
private static final long serialVersionUID = 1L;
private Integer uid;
private String uname;
private Set<Roles> roles;
}
public class Roles implements Serializable {
private static final long serialVersionUID = 1L;
private Integer rid;
private String rname;
private Set<Users> users;
}
4、定义代理：
public interface IUsersMapper {
@Select("select * from user where u_id=#{id}")
@Results({
@Result(id=true,column="u_id",property="uid"),
@Result(column="u_name",property="uname"),
@Result(column="u_id",property="roles",many=@Many(select="com.mybatis.mapper.IRolesMapper.getRolesByUid",fetchType=FetchType.LAZY))
})
public Users getUsersById(Integer id);
}
public interface IRolesMapper {
@Select("select * from role where r_id in(select r_id from user_role where u_id=#{id})")
@Results({
@Result(id=true,column="r_id",property="rid"),
@Result(column="r_name",property="rname")
})
public List<Roles> getRolesByUid(Integer id);
}
5、配置全局配置文件：
<mapper class="com.mybatis.mapper.IUsersMapper"/>
<mapper class="com.mybatis.mapper.IRolesMapper"/>
6、测试：
SqlSession session = DBTools.getSqlSession();
IUsersMapper mapper = session.getMapper(IUsersMapper.class);
Users users = mapper.getUsersById(1);
System.out.println(users.getUname());
for(Roles roles:users.getRoles()) {
System.out.println(roles.getRname());
}
session.close();
七、动态SQL：对应关系如下
@Insert ：@InsertProvider
@Select ：@SelectProvider
@Update ：@UpdateProvider
@Delete ：@DeleteProvider
1、四个provider注解标识 使用了动态SQL, 使用语法格式 ：
@UpdateProvider(type = UserProvider.class, method = "updateSQL")
四个provider注解标识都要type属性，用来指定一个类，method属性用来指定该类的方法，这个方法用来提供要执行的SQL语句
2、构造动态SQL
public class UserProvider {
public String updateSQL(final User user) {
return new SQL() {
{
UPDATE("t_user");
SET("user_birthday = now()");
if (user.getUserName() != null) {
SET("user_name = #{userName}");
}
WHERE("id = #{id}}");
}
}.toString();
}
}
示例：使用动态SQL实现查询所有和模糊查询
1、定义获取动态SQL语句类：
public class UsersSQL {
public String selectUserSQL(Integer id) {
return new SQL()
{
{
SELECT("*");
FROM("user");
if(id != null) {
WHERE(" u_id=#{id}");
}
}
}.toString();
}
}
2、定义代理：
@SelectProvider(type=com.mybatis.sql.UsersSQL.class,method="selectUserSQL")
@Results({
@Result(id=true,column="u_id",property="uid"),
@Result(column="u_name",property="uname")
})
public Users selectUser(Map<String,Object> map);
3、测试：
SqlSession session = DBTools.getSqlSession();
IUsersMapper mapper = session.getMapper(IUsersMapper.class);
Map<String,Object> map = new HashMap<>();
List<Users> users = mapper.selectUser(map);
users.forEach(Users->System.out.println(Users));
session.close();
Mybatis提供了一个SQL工具类org.apache.ibatis.jdbc.SQL，该类不用字符串拼接的方式，而是以合适的空格前缀和后缀来构造SQL语句
