Springboot项目使用JPA和Mybatis进行数据操作

• JPA的优缺点大概就是有基本的CURD接口提供使用，实现简单，但是多表查询的时候要返回自定义的实体去接收就有点麻烦。mybatis的优点就是可以定制自己的sql语句，自由度大，多表查询的时候多复杂的语句只要在数据库里验证好了往上一贴，用自己定义的返回实体可以接收美滋滋,就是用List<Map<String,Object>>返回的时候如果值的没有的那么key也不返回。
• pom.xml依赖

       <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.3.2</version>
        </dependency>
         <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis-spring</artifactId>
            <version>1.3.2</version>
        </dependency>
         <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
            <version>2.1.3.RELEASE</version>
        </dependency>

• 使用JPA实现增删改查

public interface UserRepository extends JpaRepository<UserEntity, Long> {
    UserEntity findByAccountAndPassword(String account, String password);


    @Query(value = "select * from user u where u.account like ?1", nativeQuery = true)
    List<UserEntity> userlist(String account);

    List<UserEntity> findByAccountContaining(String account);

//多表查询可以不用实体返回
    @Query(value = "select t.*,u.family,u.frirends,u.job,u.school from mydb.user t left join mydb.userinfo u on t.id=u.id", nativeQuery = true)
    List<Map<String,Object>> selecttwo();
}

@Service("UserService")
public class UserService implements UserInterface {
    @Autowired
    private UserRepository userRepository;
//查
    public UserEntity GetEntityByPassword(String account, String password) {
        return userRepository.findByAccountAndPassword(account, password);
    }
//增
    //@Transactional(rollbackOn = Exception.class)//开启事务，报错回滚
    public boolean Register(UserEntity userEntity) {
        boolean res = false;
        if (!userEntity.getAccount().isEmpty() && !userEntity.getPassword().isEmpty()) {
            String pass = DigestUtils.md5DigestAsHex(userEntity.getPassword().getBytes());
            userEntity.setPassword(pass);
            userEntity.setId(BaseController.getUUID());
            userRepository.save(userEntity);
            res = true;
        }

        return res;
    }
//查
public List<UserEntity> userlist(String account){
        System.out.println("这里在读数据库");
        return  userRepository.userlist(account);
    }
//增
   public UserEntity Adduser(UserEntity userEntity){
       userRepository.save(userEntity);
        return userEntity;
   }

//分页查
    public List<UserEntity> pagelist(Integer page, Integer size){
        Sort sort=new Sort(new Sort.Order(Sort.Direction.ASC,"account"));
        //构造分页对象
        Pageable pageable =new PageRequest(page,size,sort);
        Page<UserEntity> pageusers= userRepository.findAll(pageable);
        List<UserEntity>  pagelist=pageusers.getContent();
        long total=pageusers.getTotalElements();

        return pagelist;
    }
//多表查
    public List<Map<String,Object>> selecttwo(){
        return userRepository.selecttwo();
    }
}

public interface UserInterface {
    UserEntity GetEntityByPassword(String account, String password);

    //用户注册
    boolean  Register(UserEntity userEntity);

    List<UserEntity> userlist(String account);
    List<UserEntity> pagelist(Integer page, Integer size);

    UserEntity Adduser(UserEntity userEntity);

    List<Map<String,Object>> selecttwo();
}

• JPA多表查询返回结果
• 
• 使用mybatis增改查

@Mapper
public interface UserMaper {
   public List<UserEntity> Sel();

   public UserEntity Getbyid(String id);

   public List<UserEntity> Getbyword(String word);

   public  void  insertdata(UserEntity userEntity);

   public  void  updatedata(UserEntity userEntity);

   public List<Viewentity> selecttwo();

    public List<Map<String,Object>> selecttwobymap();
}

• 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.example.demo.mapper.UserMaper">

    <select id="Sel" resultType="UserEntity">
        select * from user
    </select>

    <select id="Getbyid" resultType="UserEntity">
        select * from user where id=#{id}
    </select>
    <!--传入string类型的参数时用_parameter代替参数名-->
    <select id="Getbyword" resultType="UserEntity">
        select * from user
        <where>/*where放这里可以保证特殊条件如第一个条件不满足第二个条件时将and 自动去掉，不过我们之前会在where后面加1=1这样就不会错了。*/
            /*相当于case when default*/
            <choose>
                <when test="_parameter=='haha'">
                    account like #{_parameter}
                </when>
                <when test="_parameter=='长庚'">
                    and name=#{_parameter}
                </when>
                <otherwise>
                    1=1
                </otherwise>
            </choose>
        </where>

    </select>

    <insert id="insertdata">
        insert user  (id,name,address,phone,account)values (#{id},#{name},#{address},#{phone},#{account})
    </insert>
    <update id="updatedata">
        update user
        <set>
            name=#{name},address=#{address},phone=#{phone},account=#{account}
        </set>
        where id=#{id}
    </update>


    <select id="selecttwo" resultType="Viewentity">
     select t.*,u.family,u.frirends,u.job,u.school from mydb.user t left join mydb.userinfo u on t.id=u.id
   </select>
    <select id="selecttwobymap" resultType="Map">
     select t.*,u.family,u.frirends,u.job,u.school from mydb.user t left join mydb.userinfo u on t.id=u.id
   </select>


</mapper>

• 控制器代码

@RestController
@Slf4j

public class TestController {
    @Autowired
    private UserInterface userInterface;
    @Autowired
    private UserMaper userMaper;



    @RequestMapping("get/testcache")
    public List<UserEntity> findone(String account) {
        if (account == null) {
            account = "haha";
        }

        return userInterface.userlist(account);
    }

    @RequestMapping("get/adduser")
    public UserEntity Adduser() {
        UserEntity userEntity = new UserEntity();
        userEntity.setId("1234");
        userEntity.setAccount("haha");
        userInterface.Adduser(userEntity);
        return userEntity;
    }

    @RequestMapping("get/getcachelist")
    public List<UserEntity> findcachelist(String account) {
        Jedis jedis = new Jedis("localhost", 6379);
        jedis.connect();
        Set<String> value = jedis.keys("users");
        jedis.disconnect();
        return userInterface.userlist(account);
    }

    @RequestMapping("get/savestring")
    @Cacheable("user_string")
    public String savestring() {
        String value = "dfsdfhesrwefsvdsdvivkdfifasndfhfi";
        return value;
    }

    /*测试使用mybatis连接数据
    * mybatis持久化框架
    * */
    @RequestMapping("get/mybatisdata")
    public List<UserEntity> get() {
        return userMaper.Sel();
    }

    @RequestMapping("get/mybatisuser")
    public UserEntity getbyid(String id) {
        return userMaper.Getbyid(id);
    }
    @RequestMapping("get/getbyword")
    public List<UserEntity> getbyword(String word) {
        return userMaper.Getbyword(word);
    }
    @RequestMapping("get/insert")
    public String  insertdata() {
        UserEntity user=new UserEntity();
        user.setId(UUID.randomUUID().toString());
        user.setAccount("ceshibatis");
        user.setAddress("奥尔良");
        user.setName("新宿");
        user.setPhone("13778790989");
        userMaper.insertdata(user);
        return "保存成功！";

    }
    @RequestMapping("get/update")
    public String  updatedata(String id) {
        UserEntity user=new UserEntity();
        user.setId(id);
        user.setAccount("update");
        user.setAddress("update奥尔良");
        user.setName("update新宿");
        user.setPhone("13778790989");
        userMaper.updatedata(user);
        return "更新成功！";

    }
    @RequestMapping("get/gettwo")
    public List<Viewentity>  gettwo() {
        return userMaper.selecttwo();
    }
    @RequestMapping("get/gettwobymap")
    public List<Map<String,Object>>  gettwobymap() {
        return userMaper.selecttwobymap();
    }
    @RequestMapping("get/gettwobyjpa")
    public List<Map<String,Object>>  gettwobyjpa() {
        return userInterface.selecttwo();
    }

}

• mybatis多表查询用实体返回的数据
  
• mybtis多表查询用List<Map<String,Object>>返回数据，看这字段缺胳膊少退的样子
  
  总结：两个可以结合使用，到项目后期mybatis可以放一些需要维护的SQL。这样就不用到代码里面去修改直接改XML文件就行了。这是我目前学习到的经验。