Mybatis注解开发
注解开发只需要写mapper接口即可，无需再书写对应的xxMapper.xml映射文件
对于单表操作还是比较方便的，但对多表操作就维护起来就比较麻烦了
数据库准备
添加依赖
<!--lombok--> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <version>1.18.12</version> <scope>provided</scope> </dependency> <!-- mybatis --> <dependency> <groupId>org.mybatis</groupId> <artifactId>mybatis</artifactId> <version>3.5.6</version> </dependency> <!-- mysql驱动 --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.16</version> </dependency> <!-- 测试用例 --> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>4.12</version> </dependency> <!-- 日志 --> <dependency> <groupId>org.slf4j</groupId> <artifactId>slf4j-log4j12</artifactId> <version>1.6.4</version> </dependency>
核心配置文件
mybatis-config.xml
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd"> <configuration> <!-- 引入外部的配置文件 --> <properties resource="jdbc.properties"/> <!-- 开启驼峰匹配 --> <settings> <setting name="mapUnderscoreToCamelCase" value="true"/> </settings> <!-- 连接数据库相关的配置 后面不用写，spring代替--> <environments default="development"> <environment id="development"> <transactionManager type="JDBC"/> <dataSource type="POOLED"> <property name="driver" value="${driverClass}"/> <property name="url" value="${url}"/> <property name="username" value="${jdbc.username}"/> <property name="password" value="${jdbc.password}"/> </dataSource> </environment> </environments> <mappers> <package name="cn.yanqi.mapper"/> </mappers> </configuration>
jdbc.properties
driverClass=com.mysql.jdbc.Driver url=jdbc:mysql:///mybatis jdbc.username=root jdbc.password=root
单表操作
实体类
@Data public class User { private Long id; // 用户名 private String userName; // 密码 private String password; // 姓名 private String name; // 年龄 private Integer age; // 性别，1男性，2女性 private Integer sex; // 出生日期 private Date birthday; // 创建时间 private Date created; // 更新时间 private Date updated; }
Mapper接口
/** * @Auther: yanqi * @Date: * @Dese: */ public interface UserMapper { @Select("select * from tb_user where id = #{id}") User findById(int id); @Select("select * from tb_user") List<User> selectAll(); @Insert("INSERT INTO tb_user(id, user_name, password, name, age, sex, birthday, created, updated) VALUES (null,#{userName},#{password},#{name},#{age},#{sex},#{birthday},#{created},#{updated})") void adduser(User user); @Delete("delete from tb_user where id = #{id}") void delete(int id); @Update("update tb_user set user_name =#{userName} , updated = #{updated} where id =#{id}") void updateuser(User user); }
测试
/** * @Auther: yanqi * @Date: * @Dese: */ public class UserMapperTest { private UserMapper userMapper; private SqlSession sqlSession ; private SqlSessionFactory sqlSessionFactory ; @Before public void setUp() throws Exception { String resource = "mybatis-config.xml"; InputStream inputStream = Resources.getResourceAsStream(resource); sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); sqlSession = sqlSessionFactory.openSession(); userMapper = sqlSession.getMapper(UserMapper.class); } /** * 查询单个 */ @Test public void select() { User user = this.userMapper.findById(1); System.out.println(user); } /** * 查询所有 */ @Test public void selectAll() { List<User> users = this.userMapper.selectAll(); for(User user : users){ System.out.println(user); } } /** * 添加 */ @Test public void adduser() { User user = new User(); user.setUserName("小江江"); user.setPassword("123"); user.setName("一燕"); user.setAge(27); user.setSex(2); user.setBirthday(new Date()); user.setCreated(new Date()); user.setUpdated(new Date()); this.userMapper.adduser(user); this.sqlSession.commit(); } /** * 删除 */ @Test public void delete() { this.userMapper.delete(114); this.sqlSession.commit(); } /** * 修改 */ @Test public void updateuser() { User user = new User(); user.setId(118L); user.setUserName("哈哈2"); user.setUpdated(new Date()); this.userMapper.updateuser(user); this.sqlSession.commit(); } }
一对一
实体类
/** * 订单表 */ @Data public class Order { private Integer id; private Long userId; private String orderNumber; private User user; }
Mapper接口
/** * @Auther: yanqi * @Dese: OrderMapper接口 实现一对一 */ public interface OrderMapper { /** * 查询订单，并且查询出下单人的信息 * @param * @return */ @Select("select * from tb_order where order_number = #{orderNum}") @Results({ /*映射user column: order表中user_id外键，传递给UserMapper.findById的id property: order实体类中的user字段 */ @Result( column = "id", property ="user", javaType = User.class, one = @One(select = "cn.yanqi.mapper.UserMapper.findById")) }) List<Order> oneToOne(String orderNum); }
public interface UserMapper { @Select("select * from tb_user where id = #{id}") User findById(int id); }
测试
/** * @Auther: yanqi * @Date: * @Dese: */ public class UserMapperTest { private UserMapper userMapper; private OrderMapper orderMapper; private SqlSession sqlSession ; private SqlSessionFactory sqlSessionFactory ; @Before public void setUp() throws Exception { String resource = "mybatis-config.xml"; InputStream inputStream = Resources.getResourceAsStream(resource); sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); sqlSession = sqlSessionFactory.openSession(); userMapper = sqlSession.getMapper(UserMapper.class); orderMapper = sqlSession.getMapper(OrderMapper.class); } /** * 测试一对一 */ @Test public void oneToOne(){ List<Order> orders = this.orderMapper.oneToOne("20200921002"); for(Order order : orders){ System.out.println(order); } }
一对多
实体类
/** * 订单表 * */ @Data public class Order { private Integer id; private Long userId; private String orderNumber; private User user; private List<Orderdetail> orderdetails; }
@Data public class Orderdetail { private Integer id; private Double totalPrice; private Integer status; }
Mapper接口
/** * @Auther: yanqi * @Date: * @Dese: */ public interface OrderMapper { /** * 查询订单，查询出下单人信息并且查询出订单详情。 */ @Select("select * from tb_order where order_number = #{orderNum}") @Results({ /*映射user*/ @Result(column = "id", property ="user", javaType = User.class, one = @One(select = "cn.yanqi.mapper.UserMapper.findById")), /*映射orderdetails*/ @Result(column = "id", property = "orderdetails", javaType =List.class, many = @Many(select = "cn.yanqi.mapper.OrderDetailMapper.findById")) }) List<Order> oneToMany(String orderNum); }
/** * @Auther: yanqi */ public interface OrderDetailMapper { /** * 根据id查询订单详情 * @param id */ @Select("select * from tb_orderdetail where id = #{id}") Orderdetail findById(int id); }
测试
/** * @Auther: yanqi * @Date: * @Dese: */ public class UserMapperTest { private UserMapper userMapper; private OrderMapper orderMapper; private SqlSession sqlSession ; private SqlSessionFactory sqlSessionFactory ; @Before public void setUp() throws Exception { String resource = "mybatis-config.xml"; InputStream inputStream = Resources.getResourceAsStream(resource); sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); sqlSession = sqlSessionFactory.openSession(); userMapper = sqlSession.getMapper(UserMapper.class); orderMapper = sqlSession.getMapper(OrderMapper.class); } /** * 测试一对多 */ @Test public void oneToMany(){ List<Order> orders = this.orderMapper.oneToMany("20200921002"); for(Order order : orders){ System.out.println(order); }
多对多
实体类
/** * 订单详情 */ @Data public class Orderdetail { private Integer id; private Double totalPrice; private Integer status; //商品信息 private Item item; }
/** * 商品表 */ @Data public class Item { private Integer id; private String itemName; private Float itemPrice; private String itemDetail; }
Mapper接口
public interface OrderMapper { /** * 查询订单，查询出下单人信息并且查询出订单详情中的商品数据。 */ @Select("select * from tb_order where order_number = #{orderNum}") @Results({ /*映射user*/ @Result(column = "id", property ="user", javaType = User.class, one = @One(select = "cn.yanqi.mapper.UserMapper.findById")), /*映射orderdetails*/ @Result(column = "id", property = "orderdetails", javaType =List.class, //查询Item商品 many = @Many(select = "cn.yanqi.mapper.ItemMapper.findById")) }) List<Order> manyToMany(String orderNum); }
public interface ItemMapper { @Select("select * from tb_item where id = #{id}") List<Item> findById(Integer id); }
测试
/** * 测试多对多 */ @Test public void manyToMany() { List<Order> orders = this.orderMapper.manyToMany("20200921002"); for (Order order : orders) { System.out.println(order); } }
