一、数据库 字段名与实体类属性名不相同问题
1.1 准备数据表和数据
?
CREATE TABLE orders(
order_id INT PRIMARY KEY AUTO_INCREMENT,
order_no VARCHAR(20),
order_price FLOAT
);
INSERT INTO orders(order_no, order_price) VALUES('aaaa', 23);
INSERT INTO orders(order_no, order_price) VALUES('bbbb', 33);
INSERT INTO orders(order_no, order_price) VALUES('cccc', 22);
1.2 定义实体类
?
?
public class Order {
private int id;
private String orderNo;
private float price;
}
1.3 查询数据实现
?
?
方式一: 通过在sql语句中定义别名
select order_id id, order_no orderNo,order_price price from orders where order_id=#{id}
方式二: 通过
select * from orders where order_id=#{id}
二、一对一关联表查询
?
2.1 创建表和数据
?
CREATE TABLE teacher(
t_id INT PRIMARY KEY AUTO_INCREMENT,
t_name VARCHAR(20)
);
CREATE TABLE class(
c_id INT PRIMARY KEY AUTO_INCREMENT,
c_name VARCHAR(20),
teacher_id INT
);
ALTER TABLE class ADD CONSTRAINT fk_teacher_id FOREIGN KEY (teacher_id) REFERENCES teacher(t_id);
INSERT INTO teacher(t_name) VALUES('LS1');
INSERT INTO teacher(t_name) VALUES('LS2');
INSERT INTO class(c_name, teacher_id) VALUES('bj_a', 1);
INSERT INTO class(c_name, teacher_id) VALUES('bj_b', 2);
?
提出需求：根据班级id查询班级信息（带老师的信息）
2.2 定义实体类
?
public class Teacher {
private int id;
private String name;
}
public class Classes {
private int id;
private String name;
private Teacher teacher;
}
2.3 定义SQL映射文件ClassMapper.xml
?
?
select * from class c, teacher t where c.teacher_id=t.t_id and c.c_id=#{id}
select * from class where c_id=#{id}
SELECT t_id id, t_name name FROM teacher WHERE t_id=#{id}
?
三、一对多的关联查询
3.1 增加学生表
?
CREATE TABLE student(
s_id INT PRIMARY KEY AUTO_INCREMENT,
s_name VARCHAR(20),
class_id INT
);
INSERT INTO student(s_name, class_id) VALUES('xs_A', 1);
INSERT INTO student(s_name, class_id) VALUES('xs_B', 1);
INSERT INTO student(s_name, class_id) VALUES('xs_C', 1);
INSERT INTO student(s_name, class_id) VALUES('xs_D', 2);
INSERT INTO student(s_name, class_id) VALUES('xs_E', 2);
INSERT INTO student(s_name, class_id) VALUES('xs_F', 2);
?
?
public class Student {
private int id;
private String name;
}
public class Classes {
private int id;
private String name;
private Teacher teacher;
private List students;
}
3.2 定义映射文件
?
?
select * from class c, teacher t,student s where c.teacher_id=t.t_id and c.C_id=s.class_id and c.c_id=#{id}
select * from class where c_id=#{id}
SELECT t_id id, t_name name FROM teacher WHERE t_id=#{id}
SELECT s_id id, s_name name FROM student WHERE class_id=#{id}
?
四、动态SQL与模糊查询
4.1 准备数据表
?
create table d_user(
id int primary key auto_increment,
name varchar(10),
age int(3)
);
insert into d_user(name,age) values('Tom',12);
insert into d_user(name,age) values('Bob',13);
insert into d_user(name,age) values('Jack',18);
?
4.2 查询条件实体类ConditionUser
?
private String name;
private int minAge;
private int maxAge;
4.3 表实体类User
?
?
private int id;
private String name;
privat 
