Mybatis框架开发(二)
一、开发流程(详见上一篇)
1. maven–>依赖
<!-- 定义项目版本 -->
<properties>
<mysql.version>8.0.25</mysql.version>
<druid.version>1.2.6</druid.version>
<log4j.version>1.2.6</log4j.version>
<junit.version>4.12</junit.version>
<mybatis.version>3.5.7</mybatis.version>
<slf4j.version>1.7.32</slf4j.version>
<lombok.version>1.18.20</lombok.version>
</properties>
<dependencies>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>${mysql.version}</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>${druid.version}</version>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>${mybatis.version}</version>
</dependency>
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>${log4j.version}</version>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-api</artifactId>
<version>${slf4j.version}</version>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-log4j12</artifactId>
<version>${slf4j.version}</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>${junit.version}</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>${lombok.version}</version>
</dependency>
</dependencies>
2.实体类
@Alias("Book")
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Book {
private Integer id;
private String title;
private Double price;
private String author;
}
3. 映射接口
public interface IBookMapper {
void save(Book book);
void deleteById(int id);
void update(Book book);
List<Book> findAll();
List<Book> findByTitileOrAuthor(String title,String author);
List<Book> findAllByPriceOrder();
}
4. 实现自定义数据源
@Slf4j 用来自动生成日志对象—>Logger.getLogger(clazz)
@Slf4j
public class MybatisDataSource extends UnpooledDataSourceFactory {
public MybatisDataSource() {
this.dataSource = new DruidDataSource();
log.debug("创建数据源成功...");
}
}
5 .mybatis日志
-
位置: resources/根目录下
-
配置
log4j.rootLogger=debug,Console,Logfile Console--命令行 Logfile--日志文件 level: debug,info ,warn,fatal
#Console output
log4j.appender.Console=org.apache.log4j.ConsoleAppender
log4j.appender.Console.layout=org.apache.log4j.PatternLayout
log4j.appender.Console.layout.ConversionPattern=%d{yyyy-MM-dd hh:mm:ss} [%t] %rms (%-5p) [%c] -%l-- {%m}%n
log4j.appender.Logfile=org.apache.log4j.FileAppender
log4j.appender.Logfile.File=D:/logs/mybatis.log
log4j.appender.Logfile.layout=org.apache.log4j.PatternLayout
log4j.appender.Logfile.layout.ConversionPattern=%d{yyyy-MM-dd hh:mm:ss} [%t] %rms (%-5p) [%c] -%l-- {%m}%n
myabtis日志
log4j.logger.com.ibatis=DEBUG
log4j.logger.com.ibatis.common.jdbc.SimpleDataSource=DEBUG
log4j.logger.com.ibatis.common.jdbc.ScriptRunner=DEBUG
log4j.logger.com.ibatis.sqlmap.engine.impl.SqlMapClientDelegate=DEBUG
log4j.logger.java.sql.Connection=DEBUG
log4j.logger.java.sql.Statement=DEBUG
log4j.logger.java.sql.PreparedStatement=DEBUG
二、 mybatis-configuration.xml相关内容
2.1配置别名
<!-- 配置别名,配合@Alais,扫描指定包内的锁有@Alais注解的类 -->
<typeAliases>
<package name="com.dyit.mybatis.entity"/>
<!-- <typeAlias type="com.dyit.mybatis.entity.Book" alias="ABC"/> -->
</typeAliases>
2.2 传参问题
- 一个参数
void deleteById(int id);
<delete id="deleteById">
DELETE FROM book_tab WHERE book_id=#{id}
</delete>
- 多于一个参数,使用@Param
List<Book> findByTitileOrAuthor(@Param("title")String a,@Param("author")String b);
- 多参数,封装为对象
void save(Book book);
2.3缓存 : cache
-
SqlSession是支持缓存的(内置一级缓存)
-
案例: findAll -->SELECT * FROM book_tab -->List(缓存到cache)
-
第二次findAll–>SELECT * FROM book_tab—>sql语句一致,从缓存中读取数据,减少和数据库的交互次数。
-
需要一个时间存储更长,空间更大,一般不变化(地址,城市,国家等)
-
有两个缓存
-
一级缓存 SqlSession
-
二级缓存 SqlSessionFactory
@Alias("Book") @Data @NoArgsConstructor @AllArgsConstructor public class Book implements Serializable{//要将对象序列化 private Integer id; private String title; private Double price; private String author; }
-
三、多表的检索
3.1 多对一结构
- 类的映射关系
@Alias("Book")
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Book implements Serializable{
private Integer id;
private String title;
private Double price;
private String author;
private Publisher publisher; //多对一关系
}
@Alias("Publisher")
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Publisher {
private Integer id;
private String name;
private String loc;
}
- mapper.xml映射
<resultMap type="Publisher" id="PublisherMap">
<id property="id" column="publisher_id"/>
<result property="name" column="publisher_name"/>
<result property="loc" column="publisher_loc"/>
</resultMap>
<resultMap type="Book" id="BookMap">
<id property="id" column="book_id"/>
<result property="title" column="book_title"/>
<result property="price" column="book_price"/>
<result property="author" column="book_author"/>
<!-- 多对一关联 -->
<association property="publisher" column="book_publisher"
resultMap="com.dyit.mybatis.mapper.IPublisherMapper.PublisherMap"/>
</resultMap>
property:Book类中的Publisher属性名称
column: 外键
resultMap: Book--->publisher Publisher的映射结果
<select id="findAll" resultMap="BookMap">
SELECT b.*,p.* FROM book_tab b LEFT JOIN publisher_tab p ON b.book_publisher=p.publisher_id
</select>
3.2一对多结构
@Alias("Author")
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Author {
private Integer id;
private String name;
private List<BookAuthor> list;
}
public interface IBookMapper {
void save(Book book);
void deleteById(int id);
Book findById(int id);
void update(Book book);
List<Book> findAll();
List<Book> findByTitileOrAuthor(@Param("x")String a,@Param("y")String b);
List<Book> findAllByOrder(String orderName);
List<Author> findAllAuthors();
}
<resultMap type="Author" id="AuthorMap">
<id property="id" column="author_id" />
<result property="name" column="author_name" />
<collection property="list" column="ba_author"
resultMap="BookAuthorMap" /> <!-- 一对多 关联 -->
</resultMap>
<resultMap type="BookAuthor" id="BookAuthorMap">
<id property="id" column="ba_id" />
<association property="book" column="ba_book"
resultMap="BookMap" /> <!-- 多对一关联 -->
</resultMap>
<resultMap type="Book" id="BookMap">
<id property="id" column="book_id" />
<result property="title" column="book_title" />
<result property="price" column="book_price" />
<!-- 多对一关联 -->
<association property="publisher" column="book_publisher"
resultMap="com.dyit.mybatis.mapper.IPublisherMapper.PublisherMap" />
</resultMap>
<select id="findAllAuthors" resultMap="AuthorMap">
SELECT
a.*,
ba.*,
b.*
FROM
author_tab a
LEFT JOIN book_author_tab ba ON a.author_id = ba.ba_author
LEFT JOIN book_tab b ON ba.ba_book = b.book_id
</select>
@Test
public void testMany2Many() {
SqlSession session = db.openSession();
IBookMapper mapper = session.getMapper(IBookMapper.class);
List<Author> list = mapper.findAllAuthors();
for (Author author : list) {
System.out.print(author.getName()+":");
List<BookAuthor> list2 = author.getList();
for (BookAuthor ba : list2) {
System.out.print(
ba.getBook().getTitle()+","
);
}
System.out.println();
}
session.commit();
session.close();
}
3.3 关于**# { } 和 ${ }的区别**
#{ } | ${ } |
---|---|
底层转换为 ? 号: PreparedStatement | 使用拼接 Statement |
#{title} : 对象.getTile() | ${bookName}: 拼接bookName的值 |
安全 | SQL注入漏洞 |
|