解决属性名和字段名不一致的问题
1.问题
数据库中的字段
新建一个项目,测试实体类字段不一致的情况
测试出现问题
2.解决方式(resultMap)
起别名
<!--select * from mybatis.user where id = #{id};-->
<!--select id,name,password from mybatis.user where id = #{id};-->
<!--select id,name,password as psw from mybatis.user where id = #{id};-->
resultMap
<mapper namespace="com.taiyuan.dao.UserMapper">
<!--resultMap : 结果集映射-->
<resultMap id="UserMap" type="User">
<!--column:数据库中的字段,property:实体类中的属性-->
<result column="id" property="id"/>
<result column="name" property="name"/>
<result column="password" property="psw"/>
</resultMap>
<select id="getUserByid" parameterType="int" resultMap="UserMap">
select * from mybatis.user where id = #{id};
</select>
</mapper>
Mybatis日志工厂
一、日志
1、日志工厂
如果一个数据库操作,出现了异常,我们需要排错,日志就是最好的助手。
曾经:sout, debug
现在:日志工厂
- SLF4J
- LOG4J【掌握】
- LOG4J2
- JDK_LOGGING
- COMMONS_LOGGING
- STDOUT_LOGGING 【掌握】
- NO_LOGGING
在Mybatis中具体使用那个日志实现,在设置中设定!
STDOUT_LOGGING 标准日志输出
<settings>
<setting name="logImpl" value="STDOUT_LOGGING"/>
</settings>
Created connection 388357135.
Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@1725dc0f]
==> Preparing: select s.id sid,s.name sname,t.id tid,t.name tname from mysqllearn.student s,mysqllearn.teacher t where s.tid = t.id
==> Parameters:
<== Columns: sid, sname, tid, tname
<== Row: 1, 小明, 1, 秦老师
<== Row: 2, 小红, 1, 秦老师
<== Row: 3, 小张, 1, 秦老师
<== Row: 4, 小李, 1, 秦老师
<== Row: 5, 小王, 1, 秦老师
<== Total: 5
Student(id=1, name=小明, teacher=Teacher(id=1, name=秦老师))
Student(id=2, name=小红, teacher=Teacher(id=1, name=秦老师))
Student(id=3, name=小张, teacher=Teacher(id=1, name=秦老师))
Student(id=4, name=小李, teacher=Teacher(id=1, name=秦老师))
Student(id=5, name=小王, teacher=Teacher(id=1, name=秦老师))
Resetting autocommit to true on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@1725dc0f]
Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@1725dc0f]
Returned connection 388357135 to pool.
2、Log4j
什么是Log4j?
- Log4j是Apache的一个开源项目,通过使用Log4j,我们可以控制日志信息输送的目的地是控制台、文件、GUI组件
- 我们也可以控制每一条日志的输出格式
- 通过定义每一条日志信息的级别,我们能够更加细致地控制日志的生成过程
- 可以通过一个配置文件来灵活地进行配置,而不需要修改应用的代码。
先导入Log4j的包
<!-- https://mvnrepository.com/artifact/log4j/log4j -->
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
log4j.properties
#将等级为DEBUG的日志信息输出到console和file这两个目的地,console和file的定义在下面的代码
log4j.rootLogger=DEBUG,console,file#控制台输出的相关设置
log4j.appender.console = org.apache.log4j.ConsoleAppender
log4j.appender.console.Target = System.out
log4j.appender.console.Threshold=DEBUG
log4j.appender.console.layout = org.apache.log4j.PatternLayout
log4j.appender.console.layout.ConversionPattern=[%c]-%m%n#文件输出的相关设置
log4j.appender.file = org.apache.log4j.RollingFileAppender
log4j.appender.file.File=./log/shun.log
log4j.appender.file.MaxFileSize=10mb
log4j.appender.file.Threshold=DEBUG
log4j.appender.file.layout=org.apache.log4j.PatternLayout
log4j.appender.file.layout.ConversionPattern=[%p][%d{yy-MM-dd}][%c]%m%n#日志输出级别
log4j.logger.org.mybatis=DEBUG
log4j.logger.java.sql=DEBUG
log4j.logger.java.sql.Statement=DEBUG
log4j.logger.java.sql.ResultSet=DEBUG
log4j.logger.java.sql.PreparedStatement=DEBUG
配置日志的实现
<settings>
<setting name="logImpl" value="LOG4J"/>
</settings>
简单使用
- 在要使用log4j的类中,导入包import org.apache.log4j.Logger;
- 日志对象,参数为当前类的class
static Logger logger = Logger.getLogger(UserDaoTest.class);
- 日志级别
logger.info("info:进入了testlog4j");
logger.debug("debug:进入了testlog4j");
logger.error("error:进入了testlog4j");
Mybatis分页
一、使用Limit分页
select * from user limit startIndex,pageSize;
使用Mybatis实现分页,核心SQL
- 接口
List<User> getUserByLimit(Map<Object,Object> map);
- Mapper.xml
<select id="getUserByLimit" parameterType="map" resultMap="UserMap">
select * from mybatis.user limit #{startIndex},#{pageSize}
</select>
- 测试
@Test
public void testLimit(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
Map<Object, Object> map = new HashMap<Object, Object>();
map.put("startIndex",0);
map.put("pageSize",2);
List<User> user = mapper.getUserByLimit(map);
for(User userlimit :user){
System.out.println(userlimit);
}
sqlSession.close();
}
二、RowBounds分页
不在使用SQL实现分页
1.接口
//分页2
List<User> getUserByRowBounds();
2.mapper.xml
<!--分页查询2-->
<select id="getUserByRowBounds">
select * from user limit #{startIndex},#{pageSize}
</select>
3.测试
public void getUserByRowBounds(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
//RowBounds实现
RowBounds rowBounds = new RowBounds(1, 2);
//通过Java代码层面实现分页
List<User> userList = sqlSession.selectList("com.kaung.dao.UserMapper.getUserByRowBounds", null, rowBounds);
for (User user : userList) {
System.out.println(user);
}
sqlSession.close();
}
Mybatis注解开发
一、注解开发
- 注解在接口上实现
public interface UserMapper {
@Select("select * from user")List<User> getUserList();
}
- 需要在核心配置文件上绑定
<!--绑定接口 -->
<mappers>
<mapper class="com.taiyuan.dao.UserMapper"/>
</mappers>
- 测试
@Test
public void testUserList(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List<User> userList = mapper.getUserList();
for(User user : userList){
System.out.println(user);
}
sqlSession.close();
}
本质:反射机制实现
底层:动态代理
二、CRUD
查询用户
// 方法存在多个基本数据类型参数,每个参数必须加上@param
@Select("select * from user where id = #{id} and name = #{name}")
User getUserByid(@Param("id")int id,@Param("name") String name);
添加用户
public static SqlSession getSqlSession(){
return sqlSessionFactory.openSession(true);//在工具类中可以自动提交事务
}
@Insert("insert into user(id,name,password) values(#{id},#{name},#{psw})")
int addUser(User user);
修改用户
@Update("update user set name = #{name},password = #{psw} where id = #{id}")
int updateUser(User user);
删除用户
@Delete("delete from user where id = #{id}")
int deleteUser(@Param("id") int id);
三、@Param()注解
- 基本数据类型或者String类型,需要加上。
- 引用类型不需要加
- 如果只有一个基本类型的话,可以忽略,但是建议加上
- 我们在SQL中引用的就是我们这里的@Param()中设定的属性名!
四、mybatis详细执行过程
MybatisLombok
一、使用步骤
- 在IDEA中安装Lombok插件
- 导入Lombok的jar包
<!-- https://mvnrepository.com/artifact/org.projectlombok/lombok -->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.20</version>
</dependency>
- 在实体类中加入注解
@ToString
@EqualsAndHashCode
@AllArgsConstructor, @RequiredArgsConstructor and @NoArgsConstructor
@Log, @Log4j, @Log4j2, @Slf4j, @XSlf4j, @CommonsLog, @JBossLog, @Flogger, @CustomLog
@Data
@Builder
@SuperBuilder
@Singular
@Delegate
@Value
@Accessors
@Wither
@With
@SneakyThrows
@val
@var
experimental @var
@UtilityClass
Lombok config system
@Data:无参构造,get、set、tostring、hashcoode、equals
@AllArgsConstructor
@NoArgsConstructor
Mybatis多对一
一、多对一
- 多个学生——对应一个老师
- 关联… 多个学生——关联一个老师【多对一】
- 集合… 一个老师——教许多学生【一对多】
CREATE TABLE `teacher`(`id` INT(10) NOT NULL,`name` VARCHAR(30) NOT NULL,PRIMARY KEY(`id`)
) ENGINE = INNODB DEFAULT CHARSET=utf8;INSERT INTO teacher(`id`,`name`) VALUES(1,'曹老师');CREATE TABLE `student`(`id` INT(10) NOT NULL,`name` VARCHAR(30) DEFAULT NULL,`tid` INT(10) DEFAULT NULL,PRIMARY KEY(`id`)
-- KEY`fktid`(`tid`),
-- CONSTRAINT `fktid` FOREIGN KEY(`tid`) REFERENCES`teacher`(`id`)
) ENGINE = INNODB DEFAULT CHARSET=utf8;INSERT INTO student(`id`,`name`,`tid`) VALUES(1,'小魂',1);
INSERT INTO student(`id`,`name`,`tid`) VALUES(2,'小吕',1);
INSERT INTO student(`id`,`name`,`tid`) VALUES(3,'小蓝',1);
INSERT INTO student(`id`,`name`,`tid`) VALUES(4,'小名',1);
INSERT INTO student(`id`,`name`,`tid`) VALUES(5,'小天',1);
pojo
public class Student {
private int id;
private String name;
private Teacher teacher;
}
public class Teacher {
private int id;
private String name;
}
二、按照查询嵌套处理
- 接口
//获取所有人
public List<Student> getStudent();
- 编写Mapper.xml
<mapper namespace="com.taiyuan.dao.StudentMapper">
<select id="getStudent" resultMap="StudentTeacher">
select * from student</select>
<resultMap id="StudentTeacher" type="Student">
<result property="id" column="id" />
<result property="name" column="name"/>
<!--对于复杂的属性,单独处理对象使用:association集合使用:collection-->
<association property="teacher" column="tid" javaType="Teacher" select="getTeacher"/>
</resultMap>
<select id="getTeacher" resultType="Teacher">
select * from teacher where id = #{id}
</select>
</mapper>
- 测试
public void testgetstudent(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
List<Student> student = mapper.getStudent();
for(Student student1:student){
System.out.println(student1);
}
sqlSession.close();
}
三、按照结果嵌套处理
<mapper namespace="com.taiyuan.dao.StudentMapper">
<select id="getStudent2" resultMap="StudentTeacher2">
select s.id sid,s.name sname,t.name tnamefrom student s,teacher twhere s.tid = t.id
</select>
<resultMap id="StudentTeacher2" type="Student">
<result property="id" column="sid"/>
<result property="name" column="sname"/>
<association property="teacher" javaType="Teacher">
<result property="name" column="tname"/>
</association>
</resultMap>
</mapper>
Mybatis一对多
一、一对多
pojo
public class Student {
private int id;
private String name;
private int tid;
}
public class Teacher {
private int id;
private String name;
private List<Student> students;
}
二、按照结果嵌套处理
- 接口
//获取老师及其学生
List<Teacher> getTeacher2(@Param("tid") int id);
- 编写Mapper.xml
<mapper namespace="com.taiyuan.dao.TeacherMapper">
<select id="getTeacher2" resultMap="TeacherStudent">
select s.id sid,s.name sname,t.name tname,t.id tidfrom teacher t ,student swhere t.id = s.tid and t.id = #{tid}
</select><!--集合中的泛型信息,我们通过ofType获取-->
<resultMap id="TeacherStudent" type="Teacher">
<result property="id" column="tid"/>
<result property="name" column="tname"/>
<collection property="students" ofType="Student">
<result property="id" column="sid"/>
<result property="name" column="sname"/>
<result property="tid" column="tid"/>
</collection>
</resultMap>
</mapper>
三、按照查询嵌套处理
<select id="getTeacher2" resultMap="TeacherStudent">
select * from teacher where id = #{tid}
</select>
<resultMap id="TeacherStudent" type="Teacher">
<collection property="students" javaType="ArrayList" ofType="Studnet" select="getStudent" column="id"/>
</resultMap>
<select id="getStudent" resultType="Student">
select * from student where tid = #{tid}
</select>
小结
- 关联:association 【多对一】
- 集合:collection【一对多】
- javaType & ofType
- javaType:用来指定实体类中属性的类型
- ofType:用来指定映射到List或者集合中的pojo类型,泛型中的约束类型