Java--MybatisPlus表和列；自定义SQL（三）

「爱情、让人受尽委屈。」 2023-09-28 21:58 22阅读 0赞

#### 阅读前可先参考 ####

[https://blog.csdn.net/MinggeQingchun/article/details/126521908][https_blog.csdn.net_MinggeQingchun_article_details_126521908]

[https://blog.csdn.net/MinggeQingchun/article/details/126533536][https_blog.csdn.net_MinggeQingchun_article_details_126533536]

## 一、表和列 ##

[注解 | MyBatis-Plus][_ MyBatis-Plus]

#### 1、表名 ####

**@TableName** **注解**

定义实体类时，默认需要和数据库中的表名保持一致；如果不一致可以使用 @TableName注解来进行说明

@TableName(value = "数据库表名")

创建表 user\_address

DROP TABLE IF EXISTS user_address;
    
    CREATE TABLE user_address(
    		address_id int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    		address_provice varchar(50) NULL DEFAULT NULL COMMENT '省份',
    		address_city varchar(50) NULL DEFAULT NULL COMMENT '城市',
    		address_street varchar(50) NULL DEFAULT NULL COMMENT '街道',
    		address_code int(11) NULL DEFAULT NULL COMMENT '行政编码',
    		PRIMARY KEY (address_id)
    )ENGINE=InnoDB DEFAULT CHARSET=utf8;

编写entity实体类

/**
     * @TableName(value="表名")
     * 位置：在类定义的上面
     */
    @TableName(value = "user_address")
    public class Address {
    
        //指定主键
        @TableId(value="address_id",type  = IdType.AUTO)
        private Integer id;
        private String provice;
        private String city;
        private String street;
        private String code;
    
    }

#### 2、列名 ####

**@TableField 注解**

/**
         * @TableField : 指定属性和列名的对应关系。
         *    属性： value 指定列名
         */
        @TableField(value = "address_provice")
        private String provice;
        @TableField(value = "address_city")
        private String city;
        @TableField(value = "address_street")
        private String street;
        @TableField(value = "address_code")
        private String code;

<table> 
 <thead> 
  <tr> 
   <th>属性</th> 
   <th>类型</th> 
   <th>必须指定</th> 
   <th>默认值</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>value</td> 
   <td>String</td> 
   <td>否</td> 
   <td>""</td> 
   <td>数据库字段名</td> 
  </tr> 
  <tr> 
   <td>exist</td> 
   <td>boolean</td> 
   <td>否</td> 
   <td>true</td> 
   <td>是否为数据库表字段</td> 
  </tr> 
  <tr> 
   <td>condition</td> 
   <td>String</td> 
   <td>否</td> 
   <td>""</td> 
   <td>字段&nbsp;<code>where</code>&nbsp;实体查询比较条件，有值设置则按设置的值为准，没有则为默认全局的&nbsp;<code>%s=#{%s}</code>，<a href="https://github.com/baomidou/mybatis-plus/blob/3.0/mybatis-plus-annotation/src/main/java/com/baomidou/mybatisplus/annotation/SqlCondition.java" title="参考(opens new window)" rel="nofollow">参考(opens new window)</a></td> 
  </tr> 
  <tr> 
   <td>update</td> 
   <td>String</td> 
   <td>否</td> 
   <td>""</td> 
   <td>字段&nbsp;<code>update set</code>&nbsp;部分注入，例如：当在version字段上注解<code>update="%s+1"</code>&nbsp;表示更新时会&nbsp;<code>set version=version+1</code>&nbsp;（该属性优先级高于&nbsp;<code>el</code>&nbsp;属性）</td> 
  </tr> 
  <tr> 
   <td>insertStrategy</td> 
   <td>Enum</td> 
   <td>否</td> 
   <td>FieldStrategy.DEFAULT</td> 
   <td>举例：NOT_NULL<br><code>insert into table_a(&lt;if test="columnProperty != null"&gt;column&lt;/if&gt;) values (&lt;if test="columnProperty != null"&gt;#{columnProperty}&lt;/if&gt;)</code></td> 
  </tr> 
  <tr> 
   <td>updateStrategy</td> 
   <td>Enum</td> 
   <td>否</td> 
   <td>FieldStrategy.DEFAULT</td> 
   <td>举例：IGNORED<br><code>update table_a set column=#{columnProperty}</code></td> 
  </tr> 
  <tr> 
   <td>whereStrategy</td> 
   <td>Enum</td> 
   <td>否</td> 
   <td>FieldStrategy.DEFAULT</td> 
   <td>举例：NOT_EMPTY<br><code>where &lt;if test="columnProperty != null and columnProperty!=''"&gt;column=#{columnProperty}&lt;/if&gt;</code></td> 
  </tr> 
  <tr> 
   <td>fill</td> 
   <td>Enum</td> 
   <td>否</td> 
   <td>FieldFill.DEFAULT</td> 
   <td>字段自动填充策略</td> 
  </tr> 
  <tr> 
   <td>select</td> 
   <td>boolean</td> 
   <td>否</td> 
   <td>true</td> 
   <td>是否进行 select 查询</td> 
  </tr> 
  <tr> 
   <td>keepGlobalFormat</td> 
   <td>boolean</td> 
   <td>否</td> 
   <td>false</td> 
   <td>是否保持使用全局的 format 进行处理</td> 
  </tr> 
  <tr> 
   <td>jdbcType</td> 
   <td>JdbcType</td> 
   <td>否</td> 
   <td>JdbcType.UNDEFINED</td> 
   <td>JDBC 类型 (该默认值不代表会按照该值生效)</td> 
  </tr> 
  <tr> 
   <td>typeHandler</td> 
   <td>Class&lt;? extends TypeHandler&gt;</td> 
   <td>否</td> 
   <td>UnknownTypeHandler.class</td> 
   <td>类型处理器 (该默认值不代表会按照该值生效)</td> 
  </tr> 
  <tr> 
   <td>numericScale</td> 
   <td>String</td> 
   <td>否</td> 
   <td>""</td> 
   <td>指定小数点后保留的位数</td> 
  </tr> 
 </tbody> 
</table>

#### 3、主键 ####

#### @TableId 注解 ####

//指定主键
        @TableId(value="address_id",type  = IdType.AUTO)
        private Integer id;

**IdType** 类型

public enum IdType {
        AUTO(0),
        NONE(1),
        INPUT(2),
        ASSIGN_ID(3),
        ASSIGN_UUID(4);
    }

application.yml配置文件设置

mybatis-plus:
      global-config:
        db-config:
          # id生成策略 auto为数据库自增
          id-type: auto

<table> 
 <thead> 
  <tr> 
   <th>值</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>AUTO</td> 
   <td>数据库 ID 自增</td> 
  </tr> 
  <tr> 
   <td>NONE</td> 
   <td>无状态，该类型为未设置主键类型（注解里等于跟随全局，全局里约等于 INPUT）</td> 
  </tr> 
  <tr> 
   <td>INPUT</td> 
   <td>insert 前自行 set 主键值</td> 
  </tr> 
  <tr> 
   <td>ASSIGN_ID</td> 
   <td>分配 ID(主键类型为 Number(Long 和 Integer)或 String)(since 3.3.0),使用接口<code>IdentifierGenerator</code>的方法<code>nextId</code>(默认实现类为<code>DefaultIdentifierGenerator</code>雪花算法)</td> 
  </tr> 
  <tr> 
   <td>ASSIGN_UUID</td> 
   <td>分配 UUID,主键类型为 String(since 3.3.0),使用接口<code>IdentifierGenerator</code>的方法<code>nextUUID</code>(默认 default 方法)</td> 
  </tr> 
  <tr> 
   <td>ID_WORKER</td> 
   <td>分布式全局唯一 ID 长整型类型(please use&nbsp;<code>ASSIGN_ID</code>)</td> 
  </tr> 
  <tr> 
   <td>UUID</td> 
   <td>32 位 UUID 字符串(please use&nbsp;<code>ASSIGN_UUID</code>)</td> 
  </tr> 
  <tr> 
   <td>ID_WORKER_STR</td> 
   <td>分布式全局唯一 ID 字符串类型(please use&nbsp;<code>ASSIGN_ID</code>)</td> 
  </tr> 
 </tbody> 
</table>

测试

@Test
        public void testInsert(){
            Address address  = new Address();
            address.setCity("上海");
            address.setStreet("南京路");
            address.setCode("020");
    
            //INSERT INTO user_address ( address_city, address_street, address_code ) VALUES ( ?, ?, ? )
            int rows = addressMapper.insert(address);
            System.out.println("insert address结果:"+rows);
        }

#### 4、驼峰命名 ####

默认情况下MP会开启字段名列名的驼峰映射， 即从经典数据库列名 A\_COLUMN（下划线命名） 到经典 Java 属性名 aColumn（驼峰命名） 的类似映射

application.yml配置文件设置

mybatis-plus:
      configuration:
    	#是否开启自动驼峰命名规则（camel case）映射，即从经典数据库列名 A_COLUMN（下划线命名） 到经典 Java 属性名 aColumn（驼峰命名） 的类似映射
        map-underscore-to-camel-case: false

1、建表goods

DROP TABLE IF EXISTS goods;
    
    CREATE TABLE goods(
    		good_id int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    		good_name varchar(50) NULL DEFAULT NULL COMMENT '商品名字',
    		good_cate varchar(50) NULL DEFAULT NULL COMMENT '商品类别',
    		PRIMARY KEY (good_id)
    )ENGINE=InnoDB DEFAULT CHARSET=utf8;

2、创建实体类entity

@TableName(value = "goods")
    @Data
    public class Goods {
        //定义属性
        @TableId(value="good_id",type = IdType.AUTO)
        private Integer goodId;
        private String goodName;
        private String goodCate;
    
        @Override
        public String toString() {
            return "Goods{" +
                    "goodId=" + goodId +
                    ", goodName='" + goodName + '\'' +
                    ", goodCate='" + goodCate + '\'' +
                    '}';
        }
    }

3、创建mapper

/**
     * 自定义Mapper，就是Dao接口
     * 1、要实现BaseMapper
     * 2、指定实体类
     *
     * BaseMapper是MP框架中的对象，定义19个操作方法（CRUD）
     */
    public interface GoodMapper extends BaseMapper<Goods> {
    }

4、测试

@Test
        public void testInsert(){
            Goods goods = new Goods();
            goods.setGoodName("iPhone 12");
            goods.setGoodCate("手机");
    
            //INSERT INTO goods ( good_name, good_cate ) VALUES ( ?, ? )
            int rows = goodMapper.insert(goods);
            System.out.println("insert good结果:"+rows);
        }

## 二、自定义SQL ##

1、建表

DROP TABLE IF EXISTS student;
    
    CREATE TABLE student(
    		id int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    		name varchar(50) NULL DEFAULT NULL COMMENT '学生名字',
    		age int(11) NULL DEFAULT NULL COMMENT '年龄',
    		email varchar(50) NULL DEFAULT NULL COMMENT '邮箱',
    		status int(11) NULL DEFAULT NULL COMMENT '状态',
    		PRIMARY KEY (id)
    )ENGINE=InnoDB DEFAULT CHARSET=utf8;

2、创建实体entity

@Data
    public class Student {
        //定义属性
        @TableId(value="id",type = IdType.AUTO)
        private Integer id;
        private String name;
        private Integer age;
        private String email;
        private Integer status;
    
        @Override
        public String toString() {
            return "Student{" +
                    "id=" + id +
                    ", name='" + name + '\'' +
                    ", age=" + age +
                    ", email='" + email + '\'' +
                    ", status=" + status +
                    '}';
        }
    }

3、创建mapper

/**
     * 自定义Sql
     */
    public interface StudentMapper extends BaseMapper<Student> {
        //自定义方法
        public int insertStudent(Student student);
        public Student selectStudentById(Integer id);
        public List<Student> selectByName(String name);
    }

4、创建SQL映射xml文件

<?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.company.mapper.StudentMapper">
        <insert id="insertStudent">
            insert into student(name,age,email,status) values(#{name},#{age},#{email},#{status})
        </insert>
    
        <select id="selectStudentById" resultType="com.company.entity.Student">
            select id,name,age,email,status from student where id=#{studentId}
        </select>
    
        <select id="selectByName" resultType="com.company.entity.Student">
            select id,name,age,email,status from student where name=#{name}
        </select>
    </mapper>

5、配置xml文件

mybatis-plus:
      #配置xml文件位置
      mapper-locations: classpath*:mapper/*Mapper.xml

6、测试

@Test
        public void testInsertStudent(){
            Student student  = new Student();
            student.setName("zhangsan");
            student.setAge(18);
            student.setEmail("zhangsan@163.com");
            student.setStatus(2);
            int rows  = studentMapper.insert(student);
            System.out.println("insert Student rows:"+rows);
        }
    
        @Test
        public void testSelect(){
            Student student = studentMapper.selectById(1);
            System.out.println("testSelect:"+student);
        }

[https_blog.csdn.net_MinggeQingchun_article_details_126521908]: https://blog.csdn.net/MinggeQingchun/article/details/126521908
[https_blog.csdn.net_MinggeQingchun_article_details_126533536]: https://blog.csdn.net/MinggeQingchun/article/details/126533536
[_ MyBatis-Plus]: https://baomidou.com/pages/223848/#tablename