MyBatis_2
目录
使用接口结合xml文件
数据库表与实体类创建
create database mybatis;
use mybatis;
CREATE TABLE users(id INT PRIMARY KEY AUTO_INCREMENT, NAME
VARCHAR(20), age INT);
INSERT INTO users(NAME, age) VALUES('Tom', 12);
INSERT INTO users(NAME, age) VALUES('Jack', 11);
public class User {
private int id;
private String name;
private int age;
//get,set 方法 无参与有参的构造方法创建
}
Users的操作类
public interface UsersDao{
public Users getUser(int id)
}
Users映射文件
新建映射文件UsersMapper
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
//com.huangwei.lb包名
<mapper namespace="com.huangwei.lb.dao.UsersDao">
<select id="getUser" paramterType="int" resultType="com.huangwei.lb.bean.Users">
select * from users where id = #{id}
</select>
</mapper>
测试
新建一个测试类
//com.huangwei.lb包名
class UsersTest{
static SqlSession session = null;
final String str = "com.huangwei.lb.mapper.UsersMapper.";
static UsersDao usesDao = null;
@BeforeAll
static void setUpBeforeClass() throws Exception {
Reader reader = Resources.getResourceAsReader("config.xml");
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader);
session = sessionFactory.openSession();
usersDao = session.getMapper(UsersDao.class);
}
@Test
void testFindAll(){
Users users = usersDao.getUser(1);
}
}
解决字段与属性不匹配（以Order表为例）
数据库表与实体类创建
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);
public class Order {
private int id;
private String orderNo;
private float price;
//get,set 方法 无参与有参的构造方法创建
}
通过给查询字段起别名，要求别名与属性名一致
<select id="selectOrder" parameterType="int" resultType="_Order">
select order_id id, order_no orderNo,order_price price from orders where order_id=#{id}
</select>
使用resultMap标签来定义实体类与字段之间的关系
<select id="selectOrderResultMap" parameterType="int" resultMap="orderResultMap">
select * from orders where order_id=#{id}
</select>
<resultMap type="_Order" id="orderResultMap">
<id property="id" column="order_id"/>
<result property="orderNo" column="order_no"/>
<result property="price" column="order_price"/>
</resultMap>
实现链表查询【根据班级ID查询班级信息(带有老师信息)】
数据库表与实体类创建
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);
public class Teacher {
private int id;
private String name;
//get,set 方法 无参与有参的构造方法创建
}
public class Classes {
private int id;
private String name;
private Teacher teacher;
//get,set 方法 无参与有参的构造方法创建
}
定义 sql 映射文件 ClassMapper.xml
方法一：通过联表查询
<select id="getClass" parameterType="int" resultMap="ClassResultMap">
select * from class c, teacher t where c.teacher_id=t.t_id and c.c_id=#{id}
</select>
<resultMap type="_Classes" id="ClassResultMap">
<id property="id" column="c_id"/>
<result property="name" column="c_name"/>
<association property="teacher" column="teacher_id" javaType="_Teacher">
<id property="id" column="t_id"/>
<result property="name" column="t_name"/>
</association>
</resultMap>
方法二：通过嵌套查询
<select id="getClass2" parameterType="int" resultMap="ClassResultMap2">
select * from class where c_id=#{id}
</select>
<resultMap type="_Classes" id="ClassResultMap2">
<id property="id" column="c_id"/>
<result property="name" column="c_name"/>
<association property="teacher" column="teacher_id" javaType="_Teacher" select="getTeacher"></association>
</resultMap>
<select id="getTeacher" parameterType="int" resultType="_Teacher">
SELECT t_id id, t_name name FROM teacher WHERE t_id=#{id}
</select>
测试
@Test
public void testOO() {
SqlSession sqlSession = factory.openSession();
Classes c = sqlSession.selectOne("com.atguigu.day03_mybatis.test5.OOMapper.getClass",1);
System.out.println(c);
}
@Test
public void testOO2() {
SqlSession sqlSession = factory.openSession();
Classes c =
sqlSession.selectOne("com.atguigu.day03_mybatis.test5.OOMapper.getClass2", 1);
System.out.println(c);
}
实现一对多关联【根据 classId 查询对应的班级信息,包括学生,老师】
数据库表与实体类创建
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;
//get,set 方法 无参与有参的构造方法创建
}
public class Classes {
private int id;
private String name;
private Teacher teacher;
private List<Student> students;
//get,set 方法 无参与有参的构造方法创建
}
定义 sql 映射文件 ClassMapper.xml
**方法一：联表查询
<select id="getClass3" parameterType="int" resultMap="ClassResultMap3">
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>
<resultMap type="_Classes" id="ClassResultMap3">
<id property="id" column="c_id"/>
<result property="name" column="c_name"/>
<association property="teacher" column="teacher_id" javaType="_Teacher">
<id property="id" column="t_id"/>
<result property="name" column="t_name"/>
</association>
<!-- ofType 指定 students 集合中的对象类型 -->
<collection property="students" ofType="_Student">
<id property="id" column="s_id"/>
<result property="name" column="s_name"/>
</collection>
</resultMap>
**方法二：嵌套查询
<select id="getClass4" parameterType="int" resultMap="ClassResultMap4">
select * from class where c_id=#{id}
</select>
<resultMap type="_Classes" id="ClassResultMap4">
<id property="id" column="c_id"/>
<result property="name" column="c_name"/>
<association property="teacher" column="teacher_id" javaType="_Teacher" select="getTeacher2"></association>
<collection property="students" ofType="_Student" column="c_id" select="getStudent"></collection>
</resultMap>
<select id="getTeacher2" parameterType="int" resultType="_Teacher">
SELECT t_id id, t_name name FROM teacher WHERE t_id=#{id}
</select>
<select id="getStudent" parameterType="int" resultType="_Student">
SELECT s_id id, s_name name FROM student WHERE class_id=#{id}
</select>
测试
@Test
public void testOM() {
SqlSession sqlSession = factory.openSession();
Classes c = sqlSession.selectOne("com.atguigu.day03_mybatis.test5.OOMapper.getClass3",1);
System.out.println(c);
}
@Test
public void testOM2() {
SqlSession sqlSession = factory.openSession();
Classes c = sqlSession.selectOne("com.atguigu.day03_mybatis.test5.OOMapper.getClass4",1);
System.out.println(c);
}
$与#的区别
"$"中的变量就是直接替换成值的，实际用于字符串拼接。
一般用于传入数据库对象
"#" 则是根据变量的类型来替换
一般用于传入增删改查的where条件
在同时可用#和$是，建议使用#
