myBatis基础之注解/动态SQL、字段冲突问题

SalaryMapper.java

package com.liu.work.Dao.User;

import com.liu.work.pojo.user.Salary;

import java.util.List;

public interface SalaryMapper {
    //查询
    List getSalaryList();
    //指定查询
    List getPersonMsg(String ID);
    //增加
    int addSalary(Salary salary);
    //修改
    int updateSalary(String EmployeeID);
    //删除
    int delSalary(String EmployeeID);

}

    
    
        select *
        from salary
    
    
    
        insert into salary(EmployeeID, InCome, OutCome)
        values (#{EmployeeID}, #{InCome}, #{OutCome})
    
    
    
        update Salary
        set InCome=InCome + 100
        where EmployeeID = #{EmployeeID};
    
    
    
        delete
        from Salary
        where EmployeeID = #{EmployeeID};
    

 
2、注解编写Sql语句（.xml文件依然要创建） 
//注解
    @Select("select * from departments where DepartmentID=#{DepartmentID}")
    List getID(String id);
     //添加
    @Insert("insert into departments(DepartmentID,DepartmentName,Note) values(#{DepartmentID},#{DepartmentName},#{Note})")
    int insertMsg(Department department);
    //修改
    @Update("update departments set Note=#{Note} where DepartmentID=#{DepartmentID}")
    int updateMeg(@Param("DepartmentID") String id,@Param("Note") String note);
    // 删除
    @Delete("delete from departments where DepartmentID=#{DepartmentID}")
    int DeleteMsg(@Param("DepartmentID") String id);
 
3、字段冲突问题（resultMap属性） 
 
字段不一致在.xml中的处理（简单） 
	
        
        
    
    
    
        select * from salary where EmployeeID=#{ID}
    

针对对象：

针对集合：

 			
           
 

DB：

实体类：
Student.java

package com.liu.pojo;
import lombok.Data;
@Data
public class Student {
    private int id;
    private String name;
    private Teacher teacher;
}

Teacher.java

package com.liu.pojo;
import lombok.Data;
@Data
public class Teacher {
    private int id;
    private String name;
}

StudentMapper.xml

    
    
        select s.id sid, s.name sname, t.id tid,t.name tname
        from student s,
             teacher t
        where s.tid = t.id;
    
    
        
        
        
            
            
        
    
    


 
测试：
 
   @Test
    public void getStuTest(){
        SqlSession sqlSession = MybatisUntil.getSqlSession();
       
        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
        List stuList = mapper.getStuList();
        for ( Student t:
                stuList) {
            System.out.println(t);
        }
        sqlSession.close();
        sqlSession.close();
 
多对一（多个学生对应一个老师） 
实体类
 Student .java
 
package com.liu.pojo;
import lombok.Data;
@Data
public class Student {
    private int id;
    private String name;
    private int tid;
}
 
Teacher .java
 
package com.liu.pojo;
import lombok.Data;
import java.util.List;
@Data
public class Teacher {
    private int id;
    private String name;
    List students;
}
 
TeacherMapper.xml
 



    
    
   
    
     
         select *
         from teacher where id = #{tid};
     
     
         
         
         
     
     
         select *
         from student where tid =#{tid};
     

 
测试：
 
 @Test
    public void getTeacher(){
        SqlSession sqlSession = MybatisUntil.getSqlSession();
        TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
        Teacher teacher=mapper.getTeacherList(1);
        System.out.println(teacher);
        sqlSession.close();
    }
 
4、动态Sql（If、choose、foreach） 



    
    
    
        
            and title=#{title}
        
        
            and author=#{author}
        
    
    
    
        select *
        from blog where 1=1
        
    
    
    
        select *
        from blog
        
            
        
    
    
    
        select *
        from blog
        
            
                
                    title=#{title}
                
                
                    views=#{views}
                
            
        
    
    
    
        update blog
        
            author=#{author},views=#{views}
        
        where id=#{id}
    
    
    
        select *
        from blog
        
            
                id=#{id}
            
        
    

测试：

import com.liu.dao.BlogMapper;
import com.liu.pojo.Bolg;
import com.liu.untils.IDutils;
import com.liu.untils.MybatisUntil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.util.*;
public class TestSql {
    @Test
    public void queryList(){
        SqlSession sqlSession = MybatisUntil.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        HashMap map = new HashMap<>();
        //map.put("title","Java");
        map.put("create_time",new Date("Mar 26 16:55:56 CST 2022"));
        List bolgs = mapper.queryIf(map);
        for (Bolg b: bolgs) {
            System.out.println(b);
        }
        sqlSession.close();
    }
    //2.where
    @Test
    public void whereTest(){
        SqlSession sqlSession = MybatisUntil.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        HashMap map = new HashMap<>();
        map.put("title","Mybatis");
        List bolgs = mapper.queryWhere(map);
        for (Bolg b:bolgs){
            System.out.println(b);
        }
        sqlSession.close();
    }
    //queryChoose
    @Test
    public void chooseTest(){
        SqlSession sqlSession = MybatisUntil.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        HashMap map = new HashMap<>();
        map.put("views",9999);
        List bolgs = mapper.queryChoose(map);
        for (Bolg b:bolgs){
            System.out.println(b);
        }
        sqlSession.close();
    }
    4.set
    @Test
    public void setTest(){
        SqlSession sqlSession = MybatisUntil.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        HashMap map = new HashMap<>();
        map.put("id","647099367c4e4e4fae2c73e4e75b212c");
        map.put("author","李四");
        map.put("views",8000);
        mapper.updateSet(map);
        sqlSession.close();
    }

    //queryFor
    @Test
    public void forTest(){
        SqlSession sqlSession = MybatisUntil.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        HashMap map = new HashMap<>();
        ArrayList ids = new ArrayList<>();
        ids.add(1);
        ids.add(2);
        ids.add(3);
        map.put("ids",ids);
        List bolgs = mapper.queryFor(map);
        for (Bolg b:
             bolgs) {
            System.out.println(b);
        }
        sqlSession.close();
    }
}