通用mapper的学习与使用

小鱼儿 2021-09-21 07:18 894阅读 0赞

# 通用mapper的学习与使用 #

本文是基于SpringBoot 2.0.4.release环境，学习和使用通用mapper：

--------------------

## 一、通用mapper的注解使用 ##

### （1）@Table ###

*  **建立实体类与数据库表之间的映射关系**

@Data
    @NoArgsConstructor
    @AllArgsConstructor
    @Table(name = "table_emp")
    public class Employee { 
        private Integer empId;
        private String empName;
        private Double empSalary;
        private Integer empAge;
    }

### （2）@Column ###

*  **建立实体类字段与数据库表字段之间的映射关系**
 *  **实体类字段使用驼峰式命名**
 *  **数据库表字段使用\_下划线分割**

@Data
    @NoArgsConstructor
    @AllArgsConstructor
    @Table(name = "table_emp")
    public class Employee { 
        private Integer empId;
        @Column(name = "emp_name_value")
        private String empName;
        private Double empSalary;
        private Integer empAge;
    }

### （3）@Id ###

*  **标记数据库表中的主键**

@Data
    @NoArgsConstructor
    @AllArgsConstructor
    @Table(name = "table_emp")
    public class Employee { 
    	@Id
        private Integer empId;
        @Column(name = "emp_name_value")
        private String empName;
        private Double empSalary;
        private Integer empAge;
    }

### （4）@GeneratedValue ###

*  **让通用mapper执行insert操作之后将数据库自动生成的主键值回写到实体类对象中**
 *  **基于自增主键用法：**

@Data
    @NoArgsConstructor
    @AllArgsConstructor
    @Table(name = "table_emp")
    public class Employee { 
    	@Id
    	@GeneratedValue(strategy = GenerationType.IDENTITY)
        private Integer empId;
        @Column(name = "emp_name_value")
        private String empName;
        private Double empSalary;
        private Integer empAge;
    }

*  **基于序列主键用法：**

@Data
    @NoArgsConstructor
    @AllArgsConstructor
    @Table(name = "table_emp")
    public class Employee { 
    	@Id
    	@GeneratedValue(strategy = GenerationType.IDENTITY, generator="select SEQ_ID.nextval from dual")
        private Integer empId;
        @Column(name = "emp_name_value")
        private String empName;
        private Double empSalary;
        private Integer empAge;
    }

### （5）@Transient ###

*  **标记实体类中的额外的属性，用来告诉通用mapper这不是表中的字段**

## 二、通用mapper的常用方法 ##

### （0）接口继承关系 ###

*  **Mapper的继承关系**

public interface Mapper<T> extends 
    	BaseMapper<T>, 
    	ExampleMapper<T>, 
    	RowBoundsMapper<T>, 
    	Marker

*  **BaseMapper的继承关系**

public interface BaseMapper<T> extends 
    	BaseSelectMapper<T>, 
    	BaseInsertMapper<T>, 
    	BaseUpdateMapper<T>, 
    	BaseDeleteMapper<T>

*  **BaseSelectMapper的继承关系**

public interface BaseSelectMapper<T> extends 
    	SelectOneMapper<T>,
    	SelectMapper<T>, 
    	SelectAllMapper<T>, 
    	SelectCountMapper<T>, 
    	SelectByPrimaryKeyMapper<T>, 
    	ExistsWithPrimaryKeyMapper<T>

*  **BaseInsertMapper的继承关系**

public interface BaseInsertMapper<T> extends 
    	InsertMapper<T>, 
    	InsertSelectiveMapper<T>

*  **BaseUpdateMapper的继承关系**

public interface BaseUpdateMapper<T> extends
    	UpdateByPrimaryKeyMapper<T>,
    	UpdateByPrimaryKeySelectiveMapper<T>

*  **BaseDeleteMapper的继承关系**

public interface BaseDeleteMapper<T> extends 
    	DeleteMapper<T>, 
    	DeleteByPrimaryKeyMapper<T>

*  **ExampleMapper的继承关系**

public interface ExampleMapper<T> extends 
    	SelectByExampleMapper<T>, 
    	SelectOneByExampleMapper<T>, 
    	SelectCountByExampleMapper<T>, 
    	DeleteByExampleMapper<T>, 
    	UpdateByExampleMapper<T>, 
    	UpdateByExampleSelectiveMapper<T>

## 三、通用BaseSelectMapper的常用方法 ##

### （1）selectOne(T record) ###

*  **只能返回一条记录**
 *  **使用非空的值生成where子句**
 *  **在条件表达式中使用=进行比较**

public Employee getOne(){ 
    		Employee employeeQueryCondition = new Employee(null, "bob", 5560.11, null);
            return empMapper.selectOne(employeeQueryCondition);
        }

### （2）selectAll() ###

*  **返回所有记录**

public List<Employee> getAll(){ 
    	
            return empMapper.selectAll();
        }

### （3）selectCount(T record) ###

*  **返回记录的个数**

public Integer getCount(){ 
    		Employee employeeQueryCondition = new Employee(null, "bob", 5560.11, null);
            return empMapper.selectCount(employeeQueryCondition);
        }

### （4）selectByPrimaryKey(T id) ###

public Employee getById(T id){ 
    	
            return empMapper.selectByPrimaryKey(id);
        }

### （5）isExists(T id) ###

public Boolean isExists(Integer id){ 
    
            return empMapper.existsWithPrimaryKey(id);
        }

## 四、通用BaseInsertMapper的常用方法 ##

### （1）insert(T record) ###

public Integer insert(){ 
    		Employee employee= new Employee(null, "bob", 5560.11, null);
            return empMapper.insert(employee);
        }

### （2）insertSelective(T record) ###

*  **效率比insert方法高，为null的字段不会出现在SQL语句中**

public Integer insert(){ 
    		Employee employee= new Employee(null, "bob", 5560.11, null);
            return empMapper.insertSelective(employee);
        }

## 五、通用BaseUpdateMapper的常用方法 ##

### （1）updateByPrimaryKey(T record) ###

public Integer updateByPrimaryKey(){ 
    		Employee employee= new Employee(1, "bob", 5560.11, 23);
            return empMapper.updateByPrimaryKey(employee);
        }

### （2）updateByPrimaryKeySelective(T record) ###

*  **为null的字段不会出现在SQL语句中**

public Integer updateByPrimaryKeySelective(){ 
    		Employee employee= new Employee(1, "bob", 5560.11, null);
            return empMapper.updateByPrimaryKeySelective(employee);
        }

## 六、通用BaseDeleteMapper的常用方法 ##

### （1）delete(T record) ###

*  **尽量不要用，会删除多个数据记录**

public Integer delete(){ 
    		Employee employee= new Employee(1, "bob", 5560.11, 23);
            return empMapper.delete(employee);
        }

### （2）deleteByPrimaryKey(T id) ###

*  **为null的字段不会出现在SQL语句中**

public Integer deleteByPrimaryKey(){ 
    		Integer empId = 5;
            return empMapper.deleteByPrimaryKey(empId);
        }

## 七、通用ExampleMapper的常用方法 ##

### （1）selectByExample(Example example) ###

@Test
        public void testSelectByExample(){ 
            // 目标：where (emp_salary > 3000 and emp_age < 25) or (emp_salary < 5000 and emp_age > 30)
            Example example = new Example(Employee.class);
    
            // 设置排序信息
            example.orderBy("empSalary").asc().orderBy("empAge").desc();
            // 设置去重
            example.setDistinct(true);
            // 设置select字段
            example.selectProperties("empName", "empSalary");
    
            Criteria criteria01 = example.createCriteria();
            Criteria criteria02 = example.createCriteria();
    
            criteria01.andGreaterThan("empSalary",3000).andLessThan("empAge",25);
            criteria02.andLessThan("empSalary",5000).andGreaterThan("empAge",30);
    
            example.or(criteria02);
    
            List<Employee> employeeList = empMapper.selectByExample(example);
            employeeList.forEach(employee -> { 
                System.out.println(employee);
            });
        }

## 八、自定义mapper接口 ##

根据开发的实际需要对Mapper接口进行定制

*  **自定义通用mapper接口，可以继承任意组合的mapper**
 *  **放在与mapper不同的包下，否则会出错**

public interface MyMapper<T> extends
            SelectAllMapper<T>,
            SelectByExampleMapper<T> { 
    }

public interface EmpMapper extends MyMapper<Employee> { 
    }

@Test
    public void testSelectAll() { 
    	List<Employee> list = empMapper.selectAll();
    	list.forEach(employee -> { 
    		System.out.println(employee);
    	});
    }

## 九、开启二级缓存 ##

*  **添加CacheNamespace注解**

@CacheNamespace
    public interface EmpMapper extends MyMapper<Employee> { 
    }

*  **缺点：**  
    分布式环境下必然会出现脏数据；  
    多表联合查询的情况下极大可能会出现脏数据；

## 十、复杂类型数据处理 ##

*  **复杂类型User**

@Data
    @NoArgsConstructor
    @AllArgsConstructor
    @Table(name="table_user")
    public class User { 
        @Id
        private Integer userId;
        private String userName;
        @ColumnType(typeHandler = AddressTypeHandler.class)
        private Address address;
        @ColumnType(typeHandler = SeasonTypeHandler.class)
        private SeasonEnum season;
    }

*  **AddressTypeHandler**

public class AddressTypeHandler extends BaseTypeHandler<Address> { 
    
        @Override
        public void setNonNullParameter(PreparedStatement ps, int i, Address address, JdbcType jdbcType) throws SQLException { 
            if (address == null)
                return;
            String provice = address.getProvince();
            String city = address.getCity();
            String street = address.getStreet();
            StringBuilder builder = new StringBuilder();
    
            builder.append(provice).append(",").append(city).append(",").append(street);
    
            String addressValue = builder.toString();
            ps.setString(i, addressValue);
        }
    
        @Override
        public Address getNullableResult(ResultSet rs, String columnName) throws SQLException { 
    
            return stringToAddress(rs.getString(columnName));
        }
    
        @Override
        public Address getNullableResult(ResultSet rs, int columnIndex) throws SQLException { 
    
            return stringToAddress(rs.getString(columnIndex));
        }
    
        @Override
        public Address getNullableResult(CallableStatement cs, int columnIndex) throws SQLException { 
    
            return stringToAddress(cs.getString(columnIndex));
        }
    
        private Address stringToAddress(String columnValue){ 
            if(StringUtils.isBlank(columnValue) || !columnValue.contains(","))
                return null;
            String[] strings = columnValue.split(",");
            String province = strings[0];
            String city = strings[1];
            String street = strings[2];
            return new Address(province,city,street);
        }
    }

*  **SeasonTypeHandler**

public class SeasonTypeHandler extends BaseTypeHandler<SeasonEnum> { 
    
        @Override
        public void setNonNullParameter(PreparedStatement ps, int i, SeasonEnum season, JdbcType jdbcType) throws SQLException { 
            if (season == null)
                return;
            String s = season.getSeason();
            ps.setString(i, s);
        }
    
        @Override
        public SeasonEnum getNullableResult(ResultSet rs, String columnName) throws SQLException { 
    
            return stringToSeason(rs.getString(columnName));
        }
    
        @Override
        public SeasonEnum getNullableResult(ResultSet rs, int columnIndex) throws SQLException { 
    
            return stringToSeason(rs.getString(columnIndex));
        }
    
        @Override
        public SeasonEnum getNullableResult(CallableStatement cs, int columnIndex) throws SQLException { 
    
            return stringToSeason(cs.getString(columnIndex));
        }
    
        private SeasonEnum stringToSeason(String columnValue){ 
            if(StringUtils.isBlank(columnValue))
                return null;
            return SeasonEnum.valueOf(columnValue);
        }
    }

--------------------