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

阅读前可先参考

https://blog.csdn.net/MinggeQingchun/article/details/126521908

https://blog.csdn.net/MinggeQingchun/article/details/126533536

一、表和列 

注解 | 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;

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

测试

@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 {
}

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 {
    //自定义方法
    public int insertStudent(Student student);
    public Student selectStudentById(Integer id);
    public List selectByName(String name);
}

4、创建SQL映射xml文件

"1.0" encoding="UTF-8"?>
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);
    }