MyBatis注解式单表查询
准备测试用的数据库和表
-- 创建用户表
CREATE TABLE USER(
uid INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(20),
PASSWORD VARCHAR(20)
);
-- 添加数据
INSERT INTO USER(username,PASSWORD) VALUES('zhangsan','123');
INSERT INTO USER(username,PASSWORD) VALUES('lisi','777');
INSERT INTO USER(username,PASSWORD) VALUES('wangwu','666');
创建UserDao映射接口
创建一个UserDao接口,并在接口的方法上用注解绑定SQL语句就可以了
public interface UserDao {
@Insert("INSERT INTO USER(username,PASSWORD) VALUES(#{username},#{password})")
public void save(User user);
@Delete("delete from user where uid=#{uid}")
public void delete(Integer id);
@Update("update user set username=#{username},password=#{password} where uid=#{uid}")
public void update(User user);
@Select("select * from user")
public List<User> findAll();
@Select("select * from user where uid=#{uid}")
public User findUserById(Integer id);
}
注册映射接口
在mybatis-config.xml的核心配置文件中注册映射接口
<!--注册映射文件-->
<mappers>
<mapper class="com.itheima.dao.UserDao"/>
</mappers>
编写测试类
public class UserDaoTest {
private UserDao mapper = null;
private SqlSession sqlSession =null;
//@Before执行在每一个测试方法之前,这里用于加载MyBatis核心配置文件
@Before
public void before() {
SqlSessionFactory ssf = null;
try {
ssf = new SqlSessionFactoryBuilder().build(Resources.getResourceAsReader("mybatis-config.xml"));
sqlSession= ssf.openSession();
mapper = sqlSession.getMapper(UserDao.class);
} catch (IOException e) {
e.printStackTrace();
}
}
//测试添加功能
@Test
public void testAdd() {
User user = new User();
user.setUsername("张三");
user.setPassword("abc");
int count = mapper.save(user);
System.out.println(count>0?"添加成功":"添加失败");
}
//测试查询功能
@Test
public void testSelect() {
List<User> all = mapper.findAll();
for (User user : all) {
System.out.println(user);
}
System.out.println("----");
User user = mapper.findUserById(3);
System.out.println(user);
}
//测试修改功能
@Test
public void testUpdate() {
User user = new User();
user.setUsername("张三");
user.setPassword("abc");
user.setUid(1);
int count = mapper.update(user);
System.out.println(count>0?"修改成功":"修改失败");
}
//测试删除功能
@Test
public void testDelete() {
int count = mapper.delete(3);
System.out.println(count>0?"删除成功":"删除失败");
}
//@After执行在每一个测试方法之后,这里用于提交事务
@After
public void after(){
sqlSession.commit();
}
}
MyBatis注解式一对一查询
身份证和人员就是一对一的关系,一个人有一张身份证,一张身份证也只属于一个人。
准备测试用的数据库和表
-- 身份证表
CREATE TABLE idcard(
id INT PRIMARY KEY AUTO_INCREMENT,
icode VARCHAR(18)
);
-- 添加身份证数据
INSERT INTO idcard(icode) VALUES('420923199111060616');
INSERT INTO idcard(icode) VALUES('422209199210010620');
INSERT INTO idcard(icode) VALUES('422209199512011119');
-- 人员表
CREATE TABLE person(
pid INT PRIMARY KEY AUTO_INCREMENT,
pname VARCHAR(20),
page INT,
idcard_id INT UNIQUE,
CONSTRAINT idcard_id_fk FOREIGN KEY(idcard_id) REFERENCES idcard(id)
);
-- 添加人员信息
INSERT INTO USER(username,PASSWORD) VALUES('zhangsan','123');
INSERT INTO USER(username,PASSWORD) VALUES('lisi','777');
INSERT INTO USER(username,PASSWORD) VALUES('wangwu','666');
创建IdCard类
public class IdCard {
private Integer id;
private String icode;
//此处省略get和set方法
@Override
public String toString() {
return "Idcard{" +
"id=" + id +
", icode='" + icode + '\'' +
'}';
}
}
创建Person类
public class Person {
private Integer pid;
private String pname;
private Integer page;
//个人身份证关联
private IdCard idcard;
//此处省略get和set方法
@Override
public String toString() {
return "Person{" +
"pid=" + pid +
", pname='" + pname + '\'' +
", page=" + page +
", idcard=" + idcard +
'}';
}
}
创建IdCardDao映射接口
public interface IdCardDao {
//根据id查询一个身份证信息
@Select("select * from idcard where id=#{id}")
public IdCard findIdCardById(int id);
}
创建PersonDao映射接口
public interface PersonDao {
//查询一个人员的身份信息,一个人员只有一个身份信息
@Select("select * from person")
@Results({
@Result(id=true,property = "pid",column = "pid"),
@Result(property = "pname",column = "pname"),
@Result(property = "page",column = "page"),
//把查询到的结果赋值给Person对象的idcard
@Result(property = "idcard",column = "idcard_id",
//查询的结果封装为IdCard对象
javaType = IdCard.class,
//使用IdCardDao到的findIdCardById方法查询:根据id查询一个身份证信息
one = @One(select = "com.itheima.annodao.IdCardDao.findIdCardById"))
})
public List<Person> findAllPerson();
}
所用注解解释
@Results: 用来封装多个@Result
@Result: 表示实体类和表字段的映射关系
id=true:表示这一列为主键列
property:表示实体类的属性
column:表示表字段列
javaType = IdCard.class: 查询结果的数据类型
//表示一对一的查询
one = @One(select = "com.itheima.annodao.IdCardDao.findIdCardById")
注册映射文件
<!--注册映射文件-->
<mappers>
<mapper class="com.itheima.annodao.PersonDao"/>
<mapper class="com.itheima.annodao.IdCardDao"/>
</mappers>
编写测试类
public class AnnoPersonAndIdCardTest {
private PersonDao mapper = null;
private SqlSession sqlSession =null;
@Before
public void before() {
SqlSessionFactory ssf = null;
try {
ssf = new SqlSessionFactoryBuilder().build(Resources.getResourceAsReader("mybatis-config.xml"));
sqlSession= ssf.openSession();
mapper = sqlSession.getMapper(PersonDao.class);
} catch (IOException e) {
e.printStackTrace();
}
}
@Test
public void test1(){
List<Person> allPerson = mapper.findAllPerson();
for (Person person : allPerson) {
System.out.println(person);
}
}
@After
public void after(){
sqlSession.commit();
}
}
MyBatis注解式一对多查询
用户和订单就是一对多的关系,一个用户可以有多个订单,但是一个订单不能给多个用户。
准备测试用的数据库和表
-- 创建用户表
CREATE TABLE USER(
uid INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(20),
PASSWORD VARCHAR(20)
);
INSERT INTO USER(username,PASSWORD) VALUES('zhangsan','123');
INSERT INTO USER(username,PASSWORD) VALUES('lisi','777');
INSERT INTO USER(username,PASSWORD) VALUES('wangwu','666');
-- 创建订单表
-- 订单表关联用户表:一个订单属于一个用户,但是一个用户可以有多个订单
CREATE TABLE orders(
id INT PRIMARY KEY AUTO_INCREMENT, -- 主键
ordersn VARCHAR(10), -- 订单号
user_id INT, -- 用户id
CONSTRAINT user_id FOREIGN KEY (user_id) REFERENCES USER(uid)
);
-- 添加订单信息
INSERT INTO orders(ordersn,user_id) VALUES('2019081701',1);
INSERT INTO orders(ordersn,user_id) VALUES('2019081702',1);
INSERT INTO orders(ordersn,user_id) VALUES('2019081703',2);
INSERT INTO orders(ordersn,user_id) VALUES('2019081704',2);
创建User实体类
public class User {
private Integer uid;
private String username;
private String password;
//一个用户可以有多个订单
private List<Orders> ordersList;
//此处省略了get和set方法
@Override
public String toString() {
return "User{" +
"uid=" + uid +
", username='" + username + '\'' +
", password='" + password + '\'' +
", ordersList=" + ordersList +
'}';
}
}
创建Orders实体类
package com.itheima.domain;
import java.util.List;
public class Orders {
private int id;
private String ordersn;
//此处省略了get和set方法
@Override
public String toString() {
return "Orders{" +
"id=" + id +
", ordersn='" + ordersn + '\'' +
", productList=" + productList +
'}';
}
}
创建OrdersDao映射接口
public interface OrdersDao {
//查询一个用户的多个订单信息
@Select("select * from orders where user_id=#{user_id}")
public List<Orders> findOrdersByUid(int id);
}
创建UserDao映射接口
public interface UserDao {
//查询所有的用户的多个订单信息
@Select("select * from user")
//每一个用户信息包括(uid,username,password)
//每一个订单信息为List<Orders>,从OrdersDao的findOrdersByUid方法获取
@Results({
@Result(id = true,property = "uid",column = "uid"),
@Result(property = "username",column = "username"),
@Result(property = "password",column = "password"),
@Result(property = "ordersList",column = "uid",
javaType = List.class,
many = @Many(select = "com.itheima.annodao.OrdersDao.findOrdersByUid")
)
})
public List<User> findAllUserAndOrders();
}
注册映射文件
<!--注册映射文件-->
<mappers>
<mapper class="com.itheima.annodao.UserDao"/>
<mapper class="com.itheima.annodao.OrdersDao"/>
</mappers>
编写测试类
public class UserAndOrdersDaoTest {
private UserDao mapper = null;
private SqlSession sqlSession =null;
@Before
public void before() {
SqlSessionFactory ssf = null;
try {
ssf = new SqlSessionFactoryBuilder().build(Resources.getResourceAsReader("mybatis-config.xml"));
sqlSession= ssf.openSession();
mapper = sqlSession.getMapper(UserDao.class);
} catch (IOException e) {
e.printStackTrace();
}
}
@Test
public void test1(){
List<User> allUser = mapper.findAllUserAndOrders();
for (User user : allUser) {
System.out.println(user);
}
}
@After
public void after(){
sqlSession.commit();
}
}
MyBatis注解多对多查询
商品和订单就是多对多的关系,一个订单可以有多个商品,一个商品可以属于多个订单。
准备测试用的数据库和表
-- 创建用户表(前面创建过)
CREATE TABLE USER(
uid INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(20),
PASSWORD VARCHAR(20)
);
-- 添加产品信息(前面添加过)
INSERT INTO USER(username,PASSWORD) VALUES('zhangsan','123');
INSERT INTO USER(username,PASSWORD) VALUES('lisi','777');
INSERT INTO USER(username,PASSWORD) VALUES('wangwu','666');
/*下面的表示新创建的表*/
-- 创建订单表
CREATE TABLE orders(
id INT PRIMARY KEY AUTO_INCREMENT, -- 主键
ordersn VARCHAR(10), -- 订单号
user_id INT, -- 用户id
CONSTRAINT user_id FOREIGN KEY (user_id) REFERENCES USER(uid)
);
-- 添加订单信息
INSERT INTO orders(ordersn,user_id) VALUES('2019081701',1);
INSERT INTO orders(ordersn,user_id) VALUES('2019081702',1);
INSERT INTO orders(ordersn,user_id) VALUES('2019081703',2);
INSERT INTO orders(ordersn,user_id) VALUES('2019081704',2);
-- 创建商品表
CREATE TABLE product(
id INT PRIMARY KEY AUTO_INCREMENT,
pname VARCHAR(20),
price DOUBLE
);
-- 添加商品信息
INSERT INTO product(pname,price) VALUES('小米9',2799);
INSERT INTO product(pname,price) VALUES('魅族',3699);
INSERT INTO product(pname,price) VALUES('Iphone11',7999);
INSERT INTO product(pname,price) VALUES('OPPO9',3789);
INSERT INTO product(pname,price) VALUES('华为P30',5699);
INSERT INTO product(pname,price) VALUES('坚果3',3466);
-- 订单和商品的中间表(订单详情表)
CREATE TABLE orders_detail(
id INT PRIMARY KEY AUTO_INCREMENT,
orders_id INT,
product_id INT,
CONSTRAINT orders_id FOREIGN KEY (orders_id) REFERENCES orders(id),
CONSTRAINT product_id FOREIGN KEY (product_id) REFERENCES product(id)
);
-- 添加订单信息
INSERT INTO orders_detail(orders_id,product_id) VALUES(1,1);
INSERT INTO orders_detail(orders_id,product_id) VALUES(1,2);
INSERT INTO orders_detail(orders_id,product_id) VALUES(3,2);
INSERT INTO orders_detail(orders_id,product_id) VALUES(3,3);
INSERT INTO orders_detail(orders_id,product_id) VALUES(2,3);
INSERT INTO orders_detail(orders_id,product_id) VALUES(2,4);
INSERT INTO orders_detail(orders_id,product_id) VALUES(4,3);
INSERT INTO orders_detail(orders_id,product_id) VALUES(4,1);
创建Pruduct类
package com.itheima.domain;
import java.util.List;
public class Product {
private int pid;
private String pname;
private Double price;
//一个商品可以被多个订单引用
private List<Orders> ordersList;
//此处省略的get和set方法
@Override
public String toString() {
return "Product{" +
"pid=" + pid +
", pname='" + pname + '\'' +
", price=" + price +
", ordersList=" + ordersList +
'}';
}
}
创建Orders类
package com.itheima.domain;
import java.util.List;
public class Orders {
private int id;
private String ordersn;
//一个订单也可以包含多个商品
private List<Product> productList;
//此处省略了get和set方法
@Override
public String toString() {
return "Orders{" +
"id=" + id +
", ordersn='" + ordersn + '\'' +
", productList=" + productList +
'}';
}
}
创建ProductDao映射接口
public interface ProductDao {
//查询product表、orders表、orders_detail表(中间表)
//查询一个订单的多个产品信息
@Select("SELECT * FROM product p, orders_detail od WHERE p.id=od.product_id and od.orders_id=#{orders_id}")
public List<Product> findAllProdcutByOrderId(int id);
}
创建OrdersDao映射接口
public interface OrdersDao {
//查询每一个订单的多个产品信息
@Select("select * from orders")
@Results({
@Result(id = true,property = "id",column = "id"),
@Result(property = "ordersn",column = "ordersn"),
@Result(property = "productList",column = "id",
javaType = List.class,
many = @Many(select = "com.itheima.annodao.ProductDao.findAllProdcutByOrderId")
)
})
public List<Orders> findOrdersAndProducts();
}
注册映射文件
<!--注册映射文件-->
<mappers>
<mapper class="com.itheima.annodao.ProductDao"/>
<mapper class="com.itheima.annodao.OrdersDao"/>
</mappers>
编写测试类
public class AnnoOrdersAndProductTest {
private OrdersDao mapper = null;
private SqlSession sqlSession =null;
@Before
public void before() {
SqlSessionFactory ssf = null;
try {
ssf = new SqlSessionFactoryBuilder().build(Resources.getResourceAsReader("mybatis-config.xml"));
sqlSession= ssf.openSession();
mapper = sqlSession.getMapper(OrdersDao.class);
} catch (IOException e) {
e.printStackTrace();
}
}
@Test
public void test1(){
List<Orders> allOrders = mapper.findOrdersAndProducts();
for (Orders orders : allOrders) {
System.out.println(orders);
}
}
}