MyBatis作为一个轻量级的半ORM框架,由于其上手快、开发快捷,在企业中的中小型项目应用较广泛,下面我们来演示一下如何使用mybatis的注解开发方式来进行代码编写。
【开发环境介绍】:
数据库使用MYSQL,mybatis使用3.4.1(没有使用spring整合),mysql驱动5.1.36。
【POJO+表】
提供两个POJO类:User(用户)和Orders(订单),分别对应的数据库表下图:
User类:
/**
* 用户表(id,name,birthday)对应POJO
*/
public class User {
private int id;
private String name;
private Date birthday;
//一个用户下多个订单 一对多
private Set<Orders> orderSet;
//……省略get set方法
}
用户数据库表
Orders类
/**
* 订单表(id,user_id,number,createtime,price)对应POJO
*
*/
public class Orders {
private Integer id;
private Integer userId;
private String number;
private Date createtime;
private BigDecimal price;
//一个order输入一个用户(一对一)
private User user;
//...省略getter setter方法
}
Orders表
数据以及对应关系如下:
【功能点】
1)根据用户ID查询用户
2)新增用户,并获得用户的ID
3)查询所有订单信息,并能够查询该订单属于哪个用户
4)查询所有用户信息,如果用户有订单,则将用户的订单信息一并查出
【实现代码】
【步骤一】准备mybatis主配置文件
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/crms"/>
<property name="username" value="root"/>
<property name="password" value="root"/>
</dataSource>
</environment>
</environments>
<!-- 在配置文件中 关联包下的 接口类-->
<mappers>
<mapper class="com.itheima.ssm.mapper.UserMapper"/>
<mapper class="com.itheima.ssm.mapper.OrdersMapper"/>
</mappers>
</configuration>
【步骤二】实现具体功能(在代码调用时,省略SqlSessionFactory的提供代码)
1)根据用户ID查询用户
接口:
@Select(value="select id,name,birthday from user where id = #{id}")
public User getUserAccordSQL(Integer userId);
调用测试:
//1。根据用户ID查询用户(sql语句返回的字段和User类的属性相对应)
@Test
public void selectUserAccordSQL() {
try {
SqlSession openSession = sqlSessionFactory.openSession();
User user = openSession.selectOne("getUserAccordSQL", 1);
System.out.println(user);
} catch (Exception e) {
e.printStackTrace();
}
}
打印结果:
DEBUG [main] - ==> Preparing: select id,name,birthday from user where id = ?
DEBUG [main] - ==> Parameters: 1(Integer)
DEBUG [main] - <== Total: 1
User [id=1, name=张三, birthday=Sun Jun 18 14:14:51 CST 2017, orderSet=null]
2)新增用户,并获得用户的ID(id为自增字段)
接口:
@Insert("insert into user(name,birthday) values(#{name},#{birthday})")
//表中id字段为自增字段。在插入之后执行MYSQL的select last_insert_id()函数查询本次事务中插入的该条记录id,并将值赋予到传入的user对象中
@SelectKey(before=false,keyProperty="id",statement="select last_insert_id()",
resultType = Integer.class)
public void insertUser(User user);
调用测试:
//2.保存用户并且将用户的ID赋值
@Test
public void insertUser() {
SqlSession openSession = sqlSessionFactory.openSession();
User user = new User();
user.setName("黑马");
user.setBirthday(new Date());
openSession.insert("insertUser", user);
System.out.println(user.getId());
openSession.commit();
openSession.close();
}
打印结果(id为26,新增到了数据库表中):
DEBUG [main] - ==> Preparing: insert into user(name,birthday) values(?,?)
DEBUG [main] - ==> Parameters: 黑马(String), 2018-03-27 18:42:23.601(Timestamp)
DEBUG [main] - <== Updates: 1
DEBUG [main] - ==> Preparing: select last_insert_id()
DEBUG [main] - ==> Parameters:
DEBUG [main] - <== Total: 1
26
DEBUG [main] - Committing JDBC Connection [com.mysql.jdbc.JDBC4Connection@68a70c74]
3)查询所有订单信息,并能够查询该订单属于哪个用户
接口:
@Select("select o.*,u.id as userid,u.name,u.birthday from orders o inner join user u on o.user_id = u.id")
@Results(value= {
@Result(id=true,column="id",property="id"),
@Result(column="user_id",property="userId"),
@Result(column="number",property="number"),
@Result(column="birthday",property="user.birthday"),//注意,此处使用的是user.xxx(xxx为user对象的属性)
@Result(column="name",property="user.name"),
@Result(column="userid",property="user.id"),
})
public List<Orders> getOrdersUserMy();
调用测试:
@Test
public void selectOrdersUserMy() {
SqlSession openSession = sqlSessionFactory.openSession();
List<Orders> orders = openSession.selectList("getOrdersUserMy");
System.out.println(orders);
}
打印结果(可以看到有三个订单,1和2订单属于用户张三,3订单属于李四):
DEBUG [main] - ==> Preparing: select o.*,u.id as userid,u.name,u.birthday from orders o inner join user u on o.user_id = u.id
DEBUG [main] - ==> Parameters:
DEBUG [main] - <== Total: 3
[Orders [id=1, userId=1, number=10086, createtime=Tue Mar 06 14:51:36 CST 2018, price=100.50, user=User [id=1, name=张三, birthday=Sun Jun 18 14:14:51 CST 2017, orderSet=null]],
Orders [id=2, userId=1, number=10087, createtime=Tue Mar 06 14:51:55 CST 2018, price=365.00, user=User [id=1, name=张三, birthday=Sun Jun 18 14:14:51 CST 2017, orderSet=null]],
Orders [id=3, userId=2, number=10088, createtime=Sun Mar 04 14:51:55 CST 2018, price=1004.00, user=User [id=2, name=李四, birthday=Mon Jun 05 14:41:56 CST 2017, orderSet=null]]]