1.MyBaits简介
MyBatis 是一款优秀的持久层框架,它支持自定义 SQL、存储过程以及高级映射。MyBatis 免除了几乎所有的 JDBC 代码以及设置参数和获取结果集的工作。MyBatis 可以通过简单的 XML 或注解来配置和映射原始类型、接口和 Java POJO(Plain Old Java Objects,普通老式 Java 对象)为数据库中的记录。
来自官网 -https://mybatis.org/mybatis-3/zh/index.html
2.传统的JDBC操作数据库
导入依赖
<!-- 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>
</dependency>
2.1 创建实体类
/**
* @author xxs
* @create 2021/5/18 11:07 下午
*/
@Data
@ToString
@AllArgsConstructor
@NoArgsConstructor
public class User {
private int id;
private String username;
private String password;
}
2.2创建连接工具JdbcUtils
package com.example.util;
import com.sun.org.apache.regexp.internal.RE;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
/**
* @author xxs
* @create 2021/5/18 11:08 下午
* jdbc 工具类
*/
public class JdbcUtils {
private static String driver = null;
private static String url = null;
private static String username = null;
private static String password;
/**
* 初始化 配置参数
*/
static {
try {
InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
Properties prop = new Properties();
prop.load(in);
driver = prop.getProperty("driver");
url = prop.getProperty("url");
username = prop.getProperty("username");
password = prop.getProperty("password");
Class.forName(driver);
}catch (Exception e) {
throw new ExceptionInInitializerError(e);
}
}
/**
* 获取连接
* @return
* @throws SQLException
*/
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url,username,password);
}
/**
* 释放资源
* @param connection
* @param st
* @param rs
*/
public static void release(Connection connection, Statement st, ResultSet rs) {
if (rs!=null) {
try {
rs.close();
}catch (Exception e) {
e.printStackTrace();
}
rs = null;
}
if (st!=null) {
try {
st.close();;
}catch (Exception e) {
e.printStackTrace();
}
}
if (connection!=null) {
try {
connection.close();
}catch (Exception e) {
e.printStackTrace();
}
}
}
}
2.3编写配置文件db.properties
driver=com.mysql.jdbc.Driver
password=123
url=jdbc:mysql://localhost:3306/jdbc
username=root
2.4编写测试类来测试CRUD
package com.example.test;
import com.example.util.JdbcUtils;
import com.sun.deploy.uitoolkit.impl.fx.AppletStageManager;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
/**
* @author xxs
* @create 2021/5/18 11:20 下午
* crud
*/
public class Demo {
public static void main(String[] args) {
//insert();
//delete();
//update();
find();
}
/**
* 查找数据
*/
private static void find() {
Connection connection = null;
Statement st = null;
ResultSet rs = null;
try {
connection = JdbcUtils.getConnection();
st = connection.createStatement();
String sql = "select * from user where id = 1";
rs = st.executeQuery(sql);
if (rs.next()) {
System.out.println(rs.getString("username"));
System.out.println(rs.getString("password"));
}
}catch (Exception e) {
e.printStackTrace();
}finally {
JdbcUtils.release(connection,st,rs);
}
}
/**
* 更新数据
*/
private static void update() {
Connection connection = null;
Statement st = null;
ResultSet rs = null;
try {
connection = JdbcUtils.getConnection();
st = connection.createStatement();
String sql = "update user set username = '李四' where id = 1";
int i = st.executeUpdate(sql);
if (i>0) {
System.out.println("更新成功!");
}
}catch (Exception e) {
e.printStackTrace();
}finally {
JdbcUtils.release(connection,st,rs);
}
}
/**
* 删除数据
*/
private static void delete() {
Connection connection = null;
Statement st = null;
ResultSet rs = null;
try {
connection = JdbcUtils.getConnection();
st = connection.createStatement();
String sql = "delete from user where id = 2";
int i = st.executeUpdate(sql);
if (i>0) {
System.out.println("删除成功!");
}
}catch (Exception e) {
e.printStackTrace();
}finally {
JdbcUtils.release(connection,st,rs);
}
}
/**
* 插入数据
*/
private static void insert() {
Connection connection = null;
Statement st = null;
ResultSet rs = null;
try {
connection = JdbcUtils.getConnection();
st = connection.createStatement();
String sql = "insert into user values (2,'李四','123456')";
int num = st.executeUpdate(sql);
if (num>0) {
System.out.println("插入成功");
}
}catch (Exception e) {
e.printStackTrace();
}finally {
JdbcUtils.release(connection,st,rs);
}
}
}
2.5存在的问题
- 数据库创建连接,释放资源造成系统资源浪费影响系统性能,可以使用数据库连接池来解决
- Sql语句存在硬编码,不利于后期的维护
- 对结果集的解析也存在硬编码,sql变化导致代码变化,系统不容易维护
2.6使用连接池
连接池:连接池就是将应用所需的连接对象放在池中,每次访问时从池中获取,使用完毕再放回池中,以达到连接复用的目的。连接池和线程池很像,都是为了减少连接对象在创建、销毁连接过程中不必要消耗的资源
线程池的常用种类
- c3p0
- druid
- HikariCP
c3p0
导入依赖
<dependency>
<groupId>com.mchange</groupId>
<artifactId>c3p0</artifactId>
<version>0.9.5.5</version>
</dependency>
package com.example.connpool;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/**
* @author xxs
* @create 2021/5/19 8:41 上午
* c3p0连接池
*/
public class JdbcUtils_C3P0 {
private static ComboPooledDataSource ds = null;
static {
try {
ds = new ComboPooledDataSource();
ds.setDriverClass("com.mysql.jdbc.Driver");
ds.setJdbcUrl("jdbc://localhost:3306/jdbc");
ds.setUser("root");
ds.setPassword("123");
ds.setInitialPoolSize(10);
ds.setMinPoolSize(5);
ds.setMaxPoolSize(20);
}catch (Exception e) {
throw new ExceptionInInitializerError(e);
}
}
/**
* 获取连接
* @return
* @throws SQLException
*/
public static Connection getConnection() throws SQLException {
return ds.getConnection();
}
/**
* 关闭资源
* @param connection
* @param st
* @param rs
*/
public static void release(Connection connection, Statement st, ResultSet rs) {
if (rs!=null) {
try {
rs.close();
}catch (Exception e) {
e.printStackTrace();
}
}
rs = null;
if (st!=null) {
try {
st.close();
}catch (Exception e) {
e.printStackTrace();
}
}
if (connection!=null) {
try {
connection.close();
}catch (Exception e) {
e.printStackTrace();
}
}
}
}
也可以使用xml文件来配置连接的相关信息
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
<default-config>
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbc</property>
<property name="user">root</property>
<property name="password">123</property>
<property name="acquireIncrement">5</property>
<property name="initialPoolSize">10</property>
<property name="minPoolSize">5</property>
<property name="maxPoolSize">20</property>
</default-config>
<named-config name="mysql">
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbc</property>
<property name="user">root</property>
<property name="password">123</property>
<property name="acquireIncrement">5</property>
<property name="initialPoolSize">10</property>
<property name="minPoolSize">5</property>
<property name="maxPoolSize">20</property>
</named-config>
</c3p0-config>
druid
导入依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.2.5</version>
</dependency>
package com.example.connpool;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import com.example.util.JdbcUtils;
import com.sun.org.apache.regexp.internal.RE;
import sun.tools.asm.CatchData;
import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import java.util.ResourceBundle;
/**
* @author xxs
* @create 2021/5/19 9:31 上午
* druid 连接池
*/
public class JdbcUtils_Druid {
private static DataSource ds = null;
/**
* 初始化配置
*/
static {
try {
Properties properties = new Properties();
InputStream is = JdbcUtils.class.getClassLoader().getResourceAsStream("druid.properties");
properties.load(is);
ds = DruidDataSourceFactory.createDataSource(properties);
}catch (Exception e) {
e.printStackTrace();
}
}
/**
* 获取连接
* @return
* @throws SQLException
*/
public static Connection getConnection() throws SQLException {
return ds.getConnection();
}
/**
* 关闭资源
* @param connection
* @param st
* @param rs
*/
public static void release(Connection connection, Statement st, ResultSet rs) {
if (rs!=null) {
try {
rs.close();
}catch (Exception e) {
e.printStackTrace();
}
}
rs = null;
if (st!=null) {
try {
st.close();
}catch (Exception e) {
e.printStackTrace();
}
}
if (connection!=null) {
try {
connection.close();
}catch (Exception e) {
e.printStackTrace();
}
}
}
}
driver=com.mysql.jdbc.Driver
##初始连接数,默认0
initialSize=10
#最大连接数,默认8
maxActive=30
#缓存PreparedStatement的最大数量,默认-1(不缓存)。大于0时会自动开启缓存PreparedStatement,所以可以省略上一句设置
maxOpenPreparedStatements=20
#获取连接的最大等待时间,单位毫秒
maxWait=2000
#最小闲置数
minIdle=10
password=123
#缓存PreparedStatement,默认false
poolPreparedStatements=true
url=jdbc:mysql://localhost:3306/jdbc
username=root
HikariCP
导入依赖
<dependency>
<groupId>com.zaxxer</groupId>
<artifactId>HikariCP</artifactId>
<version>2.6.1</version>
</dependency>
package com.example.demo.connpool;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import com.zaxxer.hikari.HikariJNDIFactory;
import com.zaxxer.hikari.pool.HikariPool;
import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
/**
* @author xxs
* @create 2021/5/19 1:56 下午
* HikariCP 连接池
*/
public class JdbcUtils_HikariCP {
private static HikariDataSource dataSource;
/**
* 初始化配置
*/
static {
Properties properties = new Properties();
try {
InputStream is = JdbcUtils_HikariCP.class.getClassLoader().getResourceAsStream("hikari.properties");
properties.load(is);
}catch (Exception e) {
e.printStackTrace();
}
HikariConfig config = new HikariConfig();
config.setJdbcUrl(properties.getProperty("url"));
config.setUsername(properties.getProperty("username"));
config.setPassword(properties.getProperty("password"));
config.setDriverClassName(properties.getProperty("driver"));
dataSource = new HikariDataSource(config);
}
/**
* 获取连接
* @return
* @throws SQLException
*/
public static Connection getConnection() throws SQLException {
return dataSource.getConnection();
}
/**
* 关闭资源
* @param connection
* @param st
* @param rs
*/
public static void release(Connection connection, Statement st, ResultSet rs) {
if (rs!=null) {
try {
rs.close();
}catch (Exception e) {
e.printStackTrace();
}
}
rs = null;
if (st!=null) {
try {
st.close();
}catch (Exception e) {
e.printStackTrace();
}
}
if (connection!=null) {
try {
connection.close();
}catch (Exception e) {
e.printStackTrace();
}
}
}
}
driver=com.mysql.jdbc.Driver
password=123
url=jdbc:mysql://localhost:3306/jdbc
username=root
3.MyBatis入门
3.1创建MyBatis的主配置文件
<?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>
<!--环境 操作是哪个数据库-->
<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/jdbc"/>
<property name="username" value="root"/>
<property name="password" value="123"/>
</dataSource>
</environment>
</environments>
<!-- dao 和 xml 一一对应 -->
<mappers>
<mapper resource="mapper/UserDao.xml"/>
</mappers>
</configuration>
这个配置文件是用来创建SqlSessionFactory对象
3.2获取SqlSession
package com.example.demo.test;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.IOException;
import java.io.InputStream;
/**
* @author xxs
* @create 2021/5/19 2:22 下午
*/
public class TestMyBatis {
public static void main(String[] args) throws IOException {
// 读取mybatis-config 配置文件
InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
// 创建mybatis核心对象 sqlSessionFactory
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
// 获取SqlSession 对象
SqlSession sqlSession = sqlSessionFactory.openSession();
System.out.println(sqlSession);
}
}
3.3编写dao接口
package com.example.demo.dao;
import com.example.demo.pojo.User;
/**
* @author xxs
* @create 2021/5/19 2:31 下午
* user的持久层
*/
public interface UserDao {
/**
* 保存用户
* @param user
* @return
*/
int save(User user);
}
3.4编写对应的配置文件
<?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">
<mapper namespace="com.example.demo.dao.UserDao">
<insert id="save" parameterType="com.example.demo.pojo.User">
insert into user values (#{id},#{username},#{password})
</insert>
</mapper>
3.5测试类
package com.example.demo.test;
import com.example.demo.dao.UserDao;
import com.example.demo.pojo.User;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.IOException;
import java.io.InputStream;
/**
* @author xxs
* @create 2021/5/19 2:22 下午
*/
public class TestMyBatis {
public static void main(String[] args) throws IOException {
// 读取mybatis-config 配置文件
InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
// 创建mybatis核心对象 sqlSessionFactory
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
// 获取SqlSession 对象
SqlSession sqlSession = sqlSessionFactory.openSession();
try {
UserDao mapper = sqlSession.getMapper(UserDao.class);
int i = mapper.save(new User(3, "王五", "12345678"));
System.out.println(i);
System.out.println(sqlSession);
sqlSession.commit();
}catch (Exception e) {
e.printStackTrace();
sqlSession.rollback();
}finally {
sqlSession.close();
}
}
}
其它类似的增删改查都是相同的
3.6关系映射
数据库的关联关系
- 一对一
- 一对多
- 多对多
一对一的关联关系
我们有两张表 一张是用户表,一张是用户的信息表
person表里面有 id、name、age、cardno
info表里面有 id、cardno、address
create table person(
id int(6) PRIMARY KEY auto_increment,
name VARCHAR(40),
age int(3),
cardno VARCHAR(18) REFERENCES info(cradno)
);
create table info(
id int(6) PRIMARY KEY auto_increment,
cradno VARCHAR(18),
address VARCHAR(100)
)
编写实体类
package com.example.demo.pojo;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;
/**
* @author xxs
* @create 2021/5/19 8:06 下午
* 用户的实体类
*/
@Data
@AllArgsConstructor
@NoArgsConstructor
@ToString
public class Person {
private int id;
private String name;
private String age;
private String cardNo;
private Info info;
}
package com.example.demo.pojo;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;
/**
* @author xxs
* @create 2021/5/19 8:07 下午
* 用户的信息类
*/
@ToString
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Info {
private int id;
private String cardNo;
private String address;
}
package com.example.demo.dao;
import com.example.demo.pojo.Person;
import java.util.List;
/**
* @author xxs
* @create 2021/5/19 9:15 下午
*/
public interface PersonDao {
/**
* 保存用户
* @param person
* @return
*/
int save(Person person);
/**
* 查询用户的信息
* @return
*/
List<Person> findAll();
}
package com.example.demo.dao;
import com.example.demo.pojo.Info;
/**
* @author xxs
* @create 2021/5/19 8:12 下午
*/
public interface InfoDao {
/**
* 保存用户信息
*/
int save(Info info);
}
<?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">
<mapper namespace="com.example.demo.dao.PersonDao">
<resultMap id="findAll" type="com.example.demo.pojo.Person">
<id property="id" column="id"/>
<result property="name" column="name"/>
<result property="age" column="age"/>
<result property="cardNo" column="cardno"/>
<!-- 处理一对一关系的标签 -->
<association property="info" javaType="com.example.demo.pojo.Info">
<id column="iid" property="id"/>
<result column="icardno" property="cardNo"/>
<result column="address" property="address"/>
</association>
</resultMap>
<insert id="save" parameterType="com.example.demo.pojo.Person" useGeneratedKeys="true" keyProperty="id">
insert into person values (#{id},#{name},#{age},#{cardNo})
</insert>
<select id="findAll" resultMap="findAll">
SELECT
p.id,p.name,p.age,p.cardno,i.id as iid,i.cardno as icardno,i.address
FROM person as p
left JOIN info as i
on p.cardno = i.cardno
</select>
</mapper>
<?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">
<mapper namespace="com.example.demo.dao.InfoDao">
<resultMap id="saveResult" type="com.example.demo.pojo.Info">
<id property="id" column="id"/>
<result property="cardNo" column="cardno"/>
<result property="address" column="address"/>
</resultMap>
<insert id="save" parameterType="com.example.demo.pojo.Info">
insert into info values (#{id},#{cardNo},#{address})
</insert>
</mapper>
package com.example.demo.test;
import com.example.demo.dao.InfoDao;
import com.example.demo.dao.PersonDao;
import com.example.demo.pojo.Info;
import com.example.demo.pojo.Person;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
/**
* @author xxs
* @create 2021/5/19 9:19 下午
*/
public class TestMyBatis6 {
public static void main(String[] args) throws IOException {
// 读取mybatis-config 配置文件
InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
// 创建mybatis核心对象 sqlSessionFactory
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
// 获取SqlSession 对象
SqlSession sqlSession = sqlSessionFactory.openSession();
try {
PersonDao personDao = sqlSession.getMapper(PersonDao.class);
List<Person> all = personDao.findAll();
all.forEach(person -> System.out.println(person));
sqlSession.commit();
}catch (Exception e) {
e.printStackTrace();
sqlSession.rollback();
}finally {
sqlSession.close();
}
}
}
一对多的映射关系
我们现在有两张表 一张是部门表 一张是员工表
部门表 dept 有 id name
员工表有 id username age bir deptid
package com.example.demo.pojo;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;
import java.util.List;
/**
* @author xxs
* @create 2021/5/19 10:08 下午
* 部门实体类
*/
@Data
@AllArgsConstructor
@NoArgsConstructor
@ToString
public class Dept {
private int id;
private String name;
private List<Emp> emp;
}
package com.example.demo.pojo;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;
import java.util.Date;
/**
* @author xxs
* @create 2021/5/19 10:08 下午
* 员工表
*/
@AllArgsConstructor
@NoArgsConstructor
@Data
@ToString
public class Emp {
private int id;
private String username;
private int age;
private Date bir;
private int deptId;
}
package com.example.demo.dao;
import com.example.demo.pojo.Dept;
import java.util.List;
/**
* @author xxs
* @create 2021/5/19 10:10 下午
*/
public interface DeptDao {
/**
* 查询部门 并且所有的员工
* @return
*/
List<Dept> findAll();
}
<?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">
<mapper namespace="com.example.demo.dao.DeptDao">
<resultMap id="deptAll" type="com.example.demo.pojo.Dept">
<id property="id" column="id"/>
<result property="name" column="name"/>
<!-- collection 是用来映射一对多的 -->
<collection property="emp" javaType="java.util.List" ofType="com.example.demo.pojo.Emp">
<id column="eid" property="id"/>
<result column="username" property="username"/>
<result column="age" property="age"/>
<result column="bir" property="bir"/>
<result column="deptid" property="deptId"/>
</collection>
</resultMap>
<select id="findAll" resultMap="deptAll">
SELECT
d.id,d.name,e.id as eid,e.username,e.age,e.bir,e.deptid
from dept as d
left JOIN emp as e
on d.id = e.deptid
</select>
</mapper>
package com.example.demo.test;
import com.example.demo.dao.DeptDao;
import com.example.demo.dao.PersonDao;
import com.example.demo.pojo.Dept;
import com.example.demo.pojo.Person;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
/**
* @author xxs
* @create 2021/5/19 10:13 下午
*/
public class TestMyBatis7 {
public static void main(String[] args) throws IOException {
// 读取mybatis-config 配置文件
InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
// 创建mybatis核心对象 sqlSessionFactory
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
// 获取SqlSession 对象
SqlSession sqlSession = sqlSessionFactory.openSession();
try {
DeptDao deptDao = sqlSession.getMapper(DeptDao.class);
List<Dept> depts = deptDao.findAll();
depts.forEach(dept -> System.out.println(dept));
sqlSession.commit();
}catch (Exception e) {
e.printStackTrace();
sqlSession.rollback();
}finally {
sqlSession.close();
}
}
}
多对多映射的关系
两张表 一张是学生表 一张是老师表
学生表 id name
老师表 id name
中间表 id cid sid
package com.example.demo.pojo;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;
/**
* @author xxs
* @create 2021/5/19 10:45 下午
*/
@AllArgsConstructor
@NoArgsConstructor
@Data
@ToString
public class Course {
private int id;
private String name;
}
package com.example.demo.pojo;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;
import java.util.List;
/**
* @author xxs
* @create 2021/5/19 10:44 下午
*/
@AllArgsConstructor
@NoArgsConstructor
@Data
@ToString
public class Student {
private int id;
private String name;
private List<Course> course;
}
package com.example.demo.dao;
import com.example.demo.pojo.Student;
/**
* @author xxs
* @create 2021/5/19 10:45 下午
*/
public interface StudentDao {
/**
* 根据id 查询学生
* @param id
* @return
*/
Student findStudentById(int id);
}
<?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">
<mapper namespace="com.example.demo.dao.StudentDao">
<resultMap id="studentMap" type="com.example.demo.pojo.Student">
<id column="id" property="id"/>
<result column="name" property="name"/>
<collection property="course" javaType="java.util.List" ofType="com.example.demo.pojo.Course">
<id column="cid" property="id"/>
<result column="cname" property="name"/>
</collection>
</resultMap>
<select id="findStudentById" parameterType="java.lang.Integer" resultMap="studentMap">
SELECT s.id,s.name,c.id as cid,c.name as cname
FROM student as s
left join student_course as sc
on s.id = sc.sid
left join course as c
on sc.cid = c.id
where s.id = #{id}
</select>
</mapper>
3.7动态sql
- if
- choose when otherwise
- trim where set
- foreach
- script
- bind