MyBatis补充lombok与多表查询

1 lombook

以前的Java项目中，充斥着太多不友好的代码：POJO的getter/setter/toString；异常处理；I/O流的关闭操作等等，这些样板代码既没有技术含量，又影响着代码的美观，Lombok应运而生。

Lombok能以简单的注解形式来简化java代码，提高开发人员的开发效率。例如开发中经常需要写的javabean，都需要花时间去添加相应的getter/setter，也许还要去写构造器、equals等方法，而且需要维护，当属性多时会出现大量的getter/setter方法，这些显得很冗长也没有太多技术含量，一旦修改属性，就容易出现忘记修改对应方法的失误。

项目中导入jar包

lombok-1.18.20.jar

eclipse中安装lombok的插件

参考：https://www.cnblogs.com/boonya/p/10691466.html
* 下载lombok.jar
* 点击jar：选择安装到eclipse的安装目录下

重启eclipse
创建实体类：添加lombok的注解

package com.zhiyou100.demo03;

import java.io.Serializable;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
@Data
@NoArgsConstructor
@AllArgsConstructor
//@Getter
//@Setter
//@ToString
//@EqualsAndHashCode
//@NoArgsConstructor
//@AllArgsConstructor
//@Data=@ToString @EqualsAndHashCode @Getter @Setter @RequiredArgsConstructo
public class Student   implements Serializable{
	private Integer sid;
	private String sname;
	private String sex;
	private Float score;
	private Integer stid;
	private Integer sage;
	private Boolean sdy;
}

2 多个参数的解决方案

select * from student where sex=? and sdy=? and score>?

2.1 参数定义为对象

  <!-- 解决多个占位符 -->
  <!-- 方式1：参数是对象：对象的属性名和占位符的名字一致 -->
  <select id="getAll1" resultType="Student" parameterType="Student">
      select * from student where sex = #{sex} and score &gt; #{score} and sdy = #{sdy}
  </select>

2.2 参数定义为map

 <!-- List<Student>  getAll2(Map<String, Object> map); -->
  <!-- 方式2：参数是map：map的键的名字和占位符的名字一致 -->
  <!--  
  <select id="getAll2" resultType="Student" parameterType="java.util.Map">
      select * from student where sex = #{sex} and score &gt; #{score} and sdy = #{sdy}
  </select>
  -->
  <select id="getAll2" resultType="Student" parameterType="map">
      select * from student where sex = #{sex} and score &gt; #{score} and sdy = #{sdy}
  </select>

2.3 定义多个参数：在占位符中使用索引

  <!--  List<Student>  getAll3(String sex,float score,boolean sdy); -->
  <!-- 方式3 多个参数   直接使用索引：从0开始 -->
  <select id="getAll3" resultType="Student">
      select * from student where sex = #{0} and score &gt; #{1} and sdy = #{2}
  </select>

2.4 使用注解@Param（name）：注解的属性值就是占位符的名字

<!--  List<Student>  getAll4(@Param("se")  String sex,@Param("sc") float score,@Param("sd") boolean sdy); -->
<!-- 方式4 使用注解@Param(name)   注解的值就是占位符的名字-->
<select id="getAll4" resultType="Student">
    select * from student where sex = #{se} and score &gt; #{sc} and sdy = #{sd}
</select>

3 结果集的列和对象的属性名不一致时

创建表：

CREATE TABLE tab_stu AS SELECT sid id,sname NAME,sex sex,sage age ,score score,stid tid,sdy dy FROM student;

3.0 结果集的列名和属性名不一致：给对象属性赋值不成功

实体类

@Data
@NoArgsConstructor
@AllArgsConstructor
public class Student   implements Serializable{
	private Integer sid;
	private String sname;
	private String sex;
	private Float score;
	private Integer stid;
	private Integer sage;
	private Boolean sdy;
}

sql标签

<!-- List<Student> getAll1(); -->
<select id="getAll1" resultType="Student">
    select * from tab_stu
</select>

结果:同名的属性有值名字不一致的没有值

3.1 给结果集的列起别名：别名与属性名一致即可

<!-- 结果集列明与对象属性名不一致：解决方案1：给结果集起别名： -->
<!-- List<Student> getAll2(); -->
<select id="getAll2" resultType="Student">
    select id sid,name sname,score,sex,dy sdy,tid stid,age sage from tab_stu
</select>

3.2 定义resultMap指定结果集列与对象属性的对应关系

<!-- 结果集列明与对象属性名不一致：解决方案2：使用resultMap指定对象属性和结果集列明的对应关系 -->
<!-- List<Student> getAll3(); -->
<resultMap type="Student" id="stuMap3">
    <id column="id"  property="sid" javaType="int"/>
    <result column="name" property="sname"  javaType="String"/>
    <result column="age" property="sage" javaType="int"/>
    <result column="dy" property="sdy" javaType="boolean"/>
    <!-- 本来属性名和列名一致的可以不用设置 
            <result column="sex" property="sex"/>
            <result column="score" property="score"/>
           -->
    <result column="tid" property="stid"/>
</resultMap>
<select id="getAll3" resultMap="stuMap3">
    select * from tab_stu
</select>

4 实现表关系：n对1

数据库

CREATE DATABASE db_37 CHARSET='utf8';
USE db_37;
CREATE TABLE tab_stu(
   sid INT PRIMARY KEY AUTO_INCREMENT,
   sname VARCHAR(11),
   sex CHAR(1),
   sage INT,
   cid INT,
   CONSTRAINT fk_2 FOREIGN KEY(cid) REFERENCES tab_class(cid)
);
ALTER TABLE tab_stu DROP FOREIGN KEY fk_2;
ALTER TABLE tab_stu CHANGE cid scid INT;
ALTER TABLE tab_stu ADD CONSTRAINT fk_22 FOREIGN KEY(scid) REFERENCES tab_class(cid);
INSERT INTO tab_stu VALUES(NULL,"李四",IF(RAND()>0.5,"男","女"),TRUNCATE(RAND()*10+15,0),TRUNCATE(RAND()*3+1,0));
INSERT INTO tab_stu VALUES(NULL,"王五",IF(RAND()>0.5,"男","女"),TRUNCATE(RAND()*10+15,0),TRUNCATE(RAND()*3+1,0));
INSERT INTO tab_stu VALUES(NULL,"赵六",IF(RAND()>0.5,"男","女"),TRUNCATE(RAND()*10+15,0),TRUNCATE(RAND()*3+1,0));
INSERT INTO tab_stu VALUES(NULL,"田七",IF(RAND()>0.5,"男","女"),TRUNCATE(RAND()*10+15,0),TRUNCATE(RAND()*3+1,0));
INSERT INTO tab_stu VALUES(NULL,"赵宝",IF(RAND()>0.5,"男","女"),TRUNCATE(RAND()*10+15,0),TRUNCATE(RAND()*3+1,0));
INSERT INTO tab_stu VALUES(NULL,"呵呵",IF(RAND()>0.5,"男","女"),TRUNCATE(RAND()*10+15,0),TRUNCATE(RAND()*3+1,0));
INSERT INTO tab_stu VALUES(NULL,"换行",IF(RAND()>0.5,"男","女"),TRUNCATE(RAND()*10+15,0),TRUNCATE(RAND()*3+1,0));
INSERT INTO tab_stu VALUES(NULL,"韩非子",IF(RAND()>0.5,"男","女"),TRUNCATE(RAND()*10+15,0),TRUNCATE(RAND()*3+1,0));
INSERT INTO tab_stu VALUES(NULL,"韩寒",IF(RAND()>0.5,"男","女"),TRUNCATE(RAND()*10+15,0),TRUNCATE(RAND()*3+1,0));
SELECT * FROM tab_stu;
CREATE TABLE tab_class(
   cid INT PRIMARY KEY AUTO_INCREMENT,
   cname VARCHAR(11),
   cban_xun VARCHAR(20)
);
ALTER TABLE tab_class CHANGE cban_xun cbanXun VARCHAR(20);

INSERT INTO tab_class VALUES(NULL,"java37","天道酬勤，不忘初衷!");
INSERT INTO tab_class VALUES(NULL,"java36","好好学习!");
INSERT INTO tab_class VALUES(NULL,"java35","天天向上!");

实体类定义

public class MyClass  implements Serializable{
	private Integer cid;
	private String cname;
	private String cbanXun;
    ...
}
public class Student   implements Serializable{
    private Integer sid;
    private String sname;
    private String sex;
    private Integer sage;
    private Integer scid;
    
    //n对1：定义成员变量记录1方的对象
    private MyClass myClass;
    ...
}

实现方式1：子查询

实现方式2：连接查询

5 实现表关系：1对n

实体类

public class Student   implements Serializable{
    private Integer sid;
    private String sname;
    private String sex;
    private Integer sage;
    private Integer scid;
    ...
}
public class MyClass  implements Serializable{
	private Integer cid;
	private String cname;
	private String cbanXun;
	//1个班级 对应n个学生：在1的方定义一个集合装n
	private List<Student> stuList;
    ...
}

实现方式1：子查询

实现方式2：连接查询

6 实现1对1

略：两个n对1

7 实现n对n

表结构

CREATE DATABASE db_37 CHARSET='utf8';
USE db_37;
CREATE TABLE tab_stu(
   sid INT PRIMARY KEY AUTO_INCREMENT,
   sname VARCHAR(11),
   sex CHAR(1),
   sage INT,
   cid INT,
   CONSTRAINT fk_2 FOREIGN KEY(cid) REFERENCES tab_class(cid)
);
ALTER TABLE tab_stu DROP FOREIGN KEY fk_2;
ALTER TABLE tab_stu CHANGE cid scid INT;
ALTER TABLE tab_stu ADD CONSTRAINT fk_22 FOREIGN KEY(scid) REFERENCES tab_class(cid);
INSERT INTO tab_stu VALUES(NULL,"李四",IF(RAND()>0.5,"男","女"),TRUNCATE(RAND()*10+15,0),TRUNCATE(RAND()*3+1,0));
INSERT INTO tab_stu VALUES(NULL,"王五",IF(RAND()>0.5,"男","女"),TRUNCATE(RAND()*10+15,0),TRUNCATE(RAND()*3+1,0));
INSERT INTO tab_stu VALUES(NULL,"赵六",IF(RAND()>0.5,"男","女"),TRUNCATE(RAND()*10+15,0),TRUNCATE(RAND()*3+1,0));
INSERT INTO tab_stu VALUES(NULL,"田七",IF(RAND()>0.5,"男","女"),TRUNCATE(RAND()*10+15,0),TRUNCATE(RAND()*3+1,0));
INSERT INTO tab_stu VALUES(NULL,"赵宝",IF(RAND()>0.5,"男","女"),TRUNCATE(RAND()*10+15,0),TRUNCATE(RAND()*3+1,0));
INSERT INTO tab_stu VALUES(NULL,"呵呵",IF(RAND()>0.5,"男","女"),TRUNCATE(RAND()*10+15,0),TRUNCATE(RAND()*3+1,0));
INSERT INTO tab_stu VALUES(NULL,"换行",IF(RAND()>0.5,"男","女"),TRUNCATE(RAND()*10+15,0),TRUNCATE(RAND()*3+1,0));
INSERT INTO tab_stu VALUES(NULL,"韩非子",IF(RAND()>0.5,"男","女"),TRUNCATE(RAND()*10+15,0),TRUNCATE(RAND()*3+1,0));
INSERT INTO tab_stu VALUES(NULL,"韩寒",IF(RAND()>0.5,"男","女"),TRUNCATE(RAND()*10+15,0),TRUNCATE(RAND()*3+1,0));
SELECT * FROM tab_stu;
CREATE TABLE tab_ke_cheng(
    kid INT PRIMARY KEY AUTO_INCREMENT,
    kname VARCHAR(10),
    kcontent VARCHAR(100)
)
INSERT INTO tab_ke_cheng VALUES(NULL,"java","java面向对象的编程语言");
INSERT INTO tab_ke_cheng VALUES(NULL,"c","java面向过程的编程语言");
INSERT INTO tab_ke_cheng VALUES(NULL,"sql","关系型数据库的数据库语言");
CREATE TABLE tab_score(
    kid INT,
    sid INT,
    fen_shu FLOAT(4,1),
    PRIMARY KEY(kid,sid),
    CONSTRAINT fk_3 FOREIGN KEY(kid) REFERENCES tab_ke_cheng(kid),
    CONSTRAINT fk_4 FOREIGN KEY(sid) REFERENCES tab_stu(sid)
)
INSERT INTO tab_score VALUES(TRUNCATE(RAND()*3+1,0),TRUNCATE(RAND()*10+1,0),TRUNCATE(RAND()*100,0));
SELECT * FROM tab_stu;
SELECT * FROM tab_teacher;
SELECT * FROM tab_class;
SELECT * FROM tab_ke_cheng;
SELECT * FROM tab_score;

实体类

public class Student   implements Serializable{
    private Integer sid;
    private String sname;
    private String sex;
    private Integer sage;
    
    private List<KeCheng> keChengList; 
}
public class KeCheng  implements Serializable{
    private Integer kid;
    private String kname;
    private String kcontent;
    //多定义一个fenShu属性  指定当前学生对象的当前课程的分数
    private Float fenShu;
}

n对n实现方式1：子查询

<!-- List<Student> getAllBySex1(String sex); -->
<!-- 实现n对n方式1：子查询 -->
<resultMap type="Student" id="stuMap1">
    <id column="sid" property="sid"/>
    <result column="sname" property="sname"/>
    <result column="sage" property="sage"/>
    <result column="sex" property="sex"/>
    <!-- 给List<KeCheng> keChengList赋值 -->
    <collection property="keChengList" ofType="KeCheng" select="getAllKeChengBySid" column="sid"/>
</resultMap>
<select id="getAllBySex1" parameterType="String" resultMap="stuMap1">
    select * from tab_stu where sex=#{sex}
</select>
<select id="getAllKeChengBySid" parameterType="int" resultType="KeCheng">
    select k.*,s.*,s.fen_shu fenShu from tab_ke_cheng k,tab_score s where k.kid=s.kid and s.sid=#{sid}
</select>

n对n实现方式2：连接查询

<!-- 实现n对n方式2：连接查询-->
      <resultMap type="Student" id="stuMap2">
             <id column="sid" property="sid"/>
             <result column="sname" property="sname"/>
             <result column="sage" property="sage"/>
             <result column="sex" property="sex"/>
             <!-- 给List<KeCheng> keChengList赋值 -->
             <collection property="keChengList" ofType="KeCheng" >
                    <id column="kid" property="kid"/>
                    <result column="kname"  property="kname"/>
                    <result column="kcontent"  property="kcontent"/>
                    <result column="fen_shu"  property="fenShu"/>
             </collection>
      </resultMap>
      <select id="getAllBySex2" parameterType="String" resultMap="stuMap2">
           select * from tab_stu s,tab_ke_cheng k,tab_score c where s.sex=#{sex} and s.sid=c.sid and k.kid=c.kid
      </select>

8 注解形式

注意事项：注解和配置形式可以共存

实体类

public class KeCheng  implements Serializable{
    private Integer kid;
    private String kname;
    private String kcontent;
}

mapper接口

public interface KeChengMapper {
      List<KeCheng> getAll1();
      
      @Select("select * from tab_ke_cheng")
      List<KeCheng> getAll2();
      
      @Select("select * from tab_ke_cheng where kid >= #{0} and kcontent like #{1}")
      List<KeCheng> getAll3(int kid,String kcontent);
      
      @Select("select * from tab_ke_cheng where kid >= #{kid} and kcontent like #{kcontent}")
      List<KeCheng> getAll4(@Param("kid") int kid,@Param("kcontent")String kcontent);
      
      @Update("update tab_ke_cheng set kname=#{kname},kcontent=#{kcontent} where kid=#{kid}")
      int  updateOne(KeCheng k);
      
      @Insert("insert into tab_ke_cheng(kname,kcontent) values(#{kname},#{kcontent})")
      int addOne(KeCheng k);
      
      
      @Delete("delete from tab_ke_cheng where kid=#{kid}")
      int deleteOne(int kid);
}

sql映射文件

<mapper namespace="com.zhiyou100.test04.KeChengMapper">
     <select id="getAll1" resultType="KeCheng">
           select * from tab_ke_cheng
     </select>
</mapper>

posted @ 2021-11-05 19:11 RenVei 阅读(57) 评论(0) 编辑收藏举报

刷新页面返回顶部

RenVei

MyBatis补充lombok与多表查询

1 lombook

2 多个参数的解决方案

2.1 参数定义为对象

2.2 参数定义为map

2.3 定义多个参数：在占位符中使用索引

2.4 使用注解@Param（name）：注解的属性值 就是占位符的名字

3 结果集的列和对象的属性名不一致时

3.0 结果集的列名和属性名不一致：给对象属性赋值不成功

3.1 给结果集的列起别名：别名与属性名一致即可

3.2 定义resultMap指定结果集列与对象属性的对应关系

4 实现表关系：n对1

数据库

实体类定义

实现方式1：子查询

实现方式2：连接查询

5 实现表关系：1对n

实体类

实现方式1：子查询

实现方式2：连接查询

6 实现1对1

7 实现n对n

表结构

实体类

n对n实现方式1：子查询

n对n实现方式2：连接查询

8 注解形式

公告

2.4 使用注解@Param（name）：注解的属性值就是占位符的名字