SpringBoot2 学习4 JPA 查询

女爷i 2021-09-30 16:20 293阅读 0赞

# 参考资料 #

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2gzNTYzNjM_size_16_color_FFFFFF_t_70]

# 源码 #

https://gitee.com/bseaworkspace/springboot2all/tree/master

# 例子 #

\-ProductRepository

package com.zz.repository;
    import java.util.List;
    
    import org.springframework.data.repository.CrudRepository;
    import com.zz.entity.Product;
    public interface ProductRepository extends  CrudRepository<Product,String>{
    	
    	public List<Product> findByProductName(String name);
    	public List<Product> findByProductNameLike(String name);
    	public List<Product> findByProductNameAndProductPrice(String name,String price);
    
    }

*  ProductService

package com.zz.service;
    
    import java.util.List;
    
    import javax.annotation.Resource;
    
    import org.springframework.stereotype.Service;
    
    import com.zz.entity.OrderMaster;
    import com.zz.entity.Product;
    import com.zz.repository.OrderMasterRepository;
    import com.zz.repository.ProductRepository;
    
    @Service
    public class ProductService {
    	@Resource
    	ProductRepository productRepository;
    	
    	public List<Product> getAll(){
    		
    		return (List<Product>) productRepository.findAll();
    	}
    	
    	
    public List<Product> getByName(String name){
    		
    		return productRepository.findByProductName(name);
    	}
    
    public List<Product> getByLikeName(String name){
    	
    	return productRepository.findByProductNameLike(name);
    }
    
    
    public List<Product> getByNameAndPrice(String name,String price){
    	
    	return productRepository.findByProductNameAndProductPrice(name,price);
    }
    
    
    }

*  ProductController

package com.zz.controller;
    
    import java.util.List;
    
    import javax.annotation.Resource;
    import javax.servlet.http.HttpServletRequest;
    
    import org.springframework.web.bind.annotation.RequestMapping;
    import org.springframework.web.bind.annotation.RestController;
    
    import com.zz.entity.OrderMaster;
    import com.zz.entity.Product;
    import com.zz.service.OrderService;
    import com.zz.service.ProductService;
    import com.zz.util.KeyUtil;
    
    @RestController
    @RequestMapping("product")
    public class ProductController {
    	@Resource
    	ProductService productService;
    	//测试地址：http://localhost:9081/b/ordermaster/add?address=ddd&name=jacky
    	@RequestMapping("all")
    	public List<Product> showAll(){
    		return productService.getAll();
    	}
    	
    	
    	@RequestMapping("byname")
    	public List<Product> showByName(HttpServletRequest request){
    		String name=request.getParameter("name");
    		
    		return productService.getByName(name);
    	}
    	
    	@RequestMapping("bynamelike")
    	public List<Product> showByNameLike(HttpServletRequest request){
    		String name=request.getParameter("name");
    		
    		return productService.getByLikeName(name+"%");
    	}
    	
    	@RequestMapping("bynameandprice")
    	public List<Product> showByNamePrice(HttpServletRequest request){
    		String name=request.getParameter("name");
    		String price=request.getParameter("p");
    		
    		return productService.getByNameAndPrice(name,price);
    	}
    
    }

# 测试 #

![在这里插入图片描述][20190807213802538.png]

![在这里插入图片描述][20190807213817617.png]

![在这里插入图片描述][20190807213835211.png]

![在这里插入图片描述][20190807213852157.png]

# 复杂查询 #

*  使用 JdbcTemplate

**Repository**

package com.zz.repository;
    
    import java.sql.Date;
    import java.sql.Types;
    import java.util.List;
    import java.util.Map;
    
    import javax.annotation.Resource;
    
    import org.springframework.jdbc.core.JdbcTemplate;
    import org.springframework.stereotype.Repository;
    
    @Repository
    public class DTODao {
    	@Resource
        private JdbcTemplate jdbcTemplate;
    	
    	 public List<Map<String, Object>> queryHomeWorkDTOListMap(String homework_id) {
    
    	        String sql="select u.name,uh.homework_id from user u left join user_homework uh on (u.id=uh.user_id and uh.homework_id=?)";
    	       // 参数数组，按照顺序放入sql中的？占位符
    	        Object[] args = { homework_id };
    	     // 参数数组数据类型，跟上面数组的参数顺序对应，用来设置参数的数据类型
    	        int[] argTypes = { Types.VARCHAR };
    
    	        return jdbcTemplate.queryForList(sql, args, argTypes);
    
    	    }
    	 
    	 
    	 public List<Map<String, Object>> getHomewokmasterByDateAndType(long sreachDate,String type){
    			String sql="select * from homeworkmaster where open_date=? and type=?";
    	       Date date=new Date(sreachDate);
    	       // 参数数组，按照顺序放入sql中的？占位符
    	        Object[] args = { date,type };
    	     // 参数数组数据类型，跟上面数组的参数顺序对应，用来设置参数的数据类型
    	        int[] argTypes = {Types.DATE, Types.VARCHAR };
    
    	        return jdbcTemplate.queryForList(sql, args, argTypes);
    
    	    }
    
    
    }

**Service**

package com.zz.service;
    
    import java.sql.Date;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.text.ParseException;
    import java.text.SimpleDateFormat;
    import java.util.ArrayList;
    import java.util.List;
    import java.util.Map;
    
    import javax.annotation.Resource;
    
    import org.springframework.stereotype.Service;
    
    import com.zz.dto.HomeWorkDTO;
    import com.zz.repository.DTODao;
    import com.zz.util.DateUtil;
    
    @Service
    public class HomeWorkService {
    	
    	@Resource
    	DTODao dtodao;
    	
    	/**
    	 * 根据主表id 查询提交情况
    	 * @return
    	 */
    	public ArrayList<HomeWorkDTO> getHomeworkdetailByMasterId2(String mid){
    		ArrayList<HomeWorkDTO> ls=new ArrayList<HomeWorkDTO>();
    		List<Map<String, Object>> listmap=dtodao.queryHomeWorkDTOListMap(mid);
    		for(int i=0;i<listmap.size();i++){
    			String name=(String) listmap.get(i).get("name");
    			String homid=(String) listmap.get(i).get("homework_id");
    			HomeWorkDTO dto=new HomeWorkDTO();
    			dto.setUserName(name);
    			
    			
    			if(homid==null||"".equals(homid)){
    				dto.setIsSubmit("未提交");
    			}else{
    				dto.setIsSubmit("已提交");
    			}
    			ls.add(dto);
    			
    		}
    		return ls;
    		
    	}
    	
    	
    	 public ArrayList<HomeWorkDTO>  getHomewokmasterByDateAndType(long sreachDate,String type){
    		 ArrayList<HomeWorkDTO> ls=new ArrayList<HomeWorkDTO>();
    		 List<Map<String, Object>> listmap=dtodao.getHomewokmasterByDateAndType(sreachDate, type);
    			for(int i=0;i<listmap.size();i++){
    				String id=(String) listmap.get(i).get("id");
    				String title=(String) listmap.get(i).get("title");
    				String type1=(String) listmap.get(i).get("type");
    				String content=(String) listmap.get(i).get("content");
    				System.out.println( listmap.get(i).get("update_time"));
    				SimpleDateFormat s=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    				String update_timestr= listmap.get(i).get("update_time").toString();
    				String open_datestr=listmap.get(i).get("open_date").toString();
    				HomeWorkDTO hk=new HomeWorkDTO();
    				try {
    					java.util.Date udate=s.parse(update_timestr);
    					hk.setUpdate_time(DateUtil.tranceToSqlDate(udate));
    					
    					java.util.Date udate2=s.parse(open_datestr);
    					hk.setOpen_date(DateUtil.tranceToSqlDate(udate2));
    				} catch (ParseException e) {
    					// TODO Auto-generated catch block
    					e.printStackTrace();
    				}
    				System.out.println("***********"+hk.getUpdate_time());
    				hk.setId(id);
    				hk.setType(type1);
    				hk.setContent(content);
    				ls.add(hk);
    				
    			}
    		 
    		 return ls;
    	 }
    
    }

**controller**

package com.zz.controller;
    
    import java.text.ParseException;
    import java.text.SimpleDateFormat;
    import java.util.ArrayList;
    import java.util.Date;
    
    import javax.annotation.Resource;
    
    import org.springframework.web.bind.annotation.PathVariable;
    import org.springframework.web.bind.annotation.RequestMapping;
    import org.springframework.web.bind.annotation.RestController;
    
    import com.zz.dto.HomeWorkDTO;
    import com.zz.service.HomeWorkService;
    
    @RestController
    @RequestMapping("hc")
    public class HomeWorkController {
    	@Resource
    	HomeWorkService hservice;
    	
    	//springboot controller地址传值：
    	//1, 在拦截路径上用{参数名字}  2，使用@PathVariable(参数名字) 获取参数值
    	@RequestMapping("submitdetail/{id}")
    	public ArrayList<HomeWorkDTO> getHomeworkdetailByMasterId2(@PathVariable("id") String mid){
    		return hservice.getHomeworkdetailByMasterId2(mid);
    	}
    	
    	@RequestMapping("getbydatetype/{date}/{type}")
    	 public ArrayList<HomeWorkDTO>  getHomewokmasterByDateAndType(@PathVariable("date") long sreachDate,@PathVariable("type") String type){
    		SimpleDateFormat sformat=new SimpleDateFormat("yyyy-MM-dd");
    		Date d=null;
    		try {
    			d=sformat.parse("2019-07-27");
    		} catch (ParseException e) {
    			// TODO Auto-generated catch block
    			e.printStackTrace();
    		}
    		 return hservice.getHomewokmasterByDateAndType(d.getTime(), type);
    	 }
    
    }

测试结果：

![在这里插入图片描述][20190811194645163.png]

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2gzNTYzNjM_size_16_color_FFFFFF_t_70 1]

## 复杂查询2 ##

package com.zz.repository;
    
    import java.util.List;
    
    import org.springframework.data.domain.Page;
    import org.springframework.data.domain.Pageable;
    import org.springframework.data.jpa.repository.JpaRepository;
    import org.springframework.data.jpa.repository.Query;
    import org.springframework.data.repository.CrudRepository;
    
    import com.zz.entity.Student;
    import com.zz.entity.User;
    
    public interface StudentRepository extends  JpaRepository<Student,Integer>{
    	//根据科目查询学生信息，按照学生成绩，降序排列
    	public List<Student> findBySubjectOrderByScoreDesc(String sub);
    	
    	
    	public Page<Student> findBySubjectOrderByScoreDesc(String sub,Pageable pageable);
    	//默认使用JPQL，操作的是对象，所以里面的列名字必须和entity的属性名字一样
    	@Query("select s from Student s where s.teamId=?1 and s.name=?2")
    	public Student getStudentsssdfds(int tid,String name);
    	//ativeQuery=true 表示使用SQL，
    	//操作的是数据库表，所以里面的列名字必须和数据库里面列的名字一样
    	@Query(value="select * from student  where team_id=?1 and name=?2",nativeQuery=true)
    	public Student getStudentsdfsdfnative(int tid,String name);
    	
    	
    	
    	@Query(value="select subject,count(*) from student  group by subject",nativeQuery=true)
    	public List<Object[]> getSubGroup();
    	
    	@Query(value="select u.name,uh.homework_id from user u left join user_homework uh on (u.id=uh.user_id and uh.homework_id=?1)",nativeQuery=true)
    	public List<Object[]> selectHomeWork(String hid);
    	
    	
    	
    }

*  测试

package com.zz;
    
    import java.util.List;
    
    import javax.annotation.Resource;
    
    import org.junit.Test;
    import org.junit.runner.RunWith;
    import org.springframework.boot.test.context.SpringBootTest;
    import org.springframework.test.context.junit4.SpringRunner;
    
    import com.zz.entity.Student;
    import com.zz.repository.StudentRepository;
    
    @RunWith(SpringRunner.class)
    @SpringBootTest
    public class StudentTest {
    	
    	@Resource
    	StudentRepository studentRepository;
    	@Test
    	public void t1(){
    		Student s=studentRepository.getStudentsdfsdfnative(1, "JDBC");
    		System.out.println(s.getScore());
    	}
    	
    	
    	@Test
    	public void t2(){
    		List<Object[]> s=studentRepository.getSubGroup();
    		System.out.println(s.get(0)[0]);
    		System.out.println(s.get(0)[1]);
    		
    		System.out.println(s.get(1)[0]);
    		System.out.println(s.get(1)[1]);
    	}
    
    }

*  测试结果  
    Hibernate: select subject,count(\*) from student group by subject  
    数学  
    6  
    英文  
    3

# JPA 批量修改 #

// 执行update, delete,insert必须加@Transactional  @Modifying
    	//默认是JPQL,使用默认操作的是entity类
    	@Transactional
    	@Modifying
    	@Query("update Student set subject='语文' where id>?1")
    	public int updateStu(int id);

# JPA 修改 实现只修改有值部分 #

package com.zz.repository;
    
    import com.zz.entity.Member;
    import org.springframework.data.jpa.repository.JpaRepository;
    import org.springframework.data.jpa.repository.Modifying;
    import org.springframework.data.jpa.repository.Query;
    import org.springframework.data.repository.query.Param;
    import org.springframework.transaction.annotation.Transactional;
    
    /**
     * @Description: 会员
     * @Author: Bsea
     * @CreateDate: 2019/9/25$ 20:16$
     */
    public interface MemberRepository extends JpaRepository<Member, String> {
       
        /**
         *复杂JPA操作  使用@Query()自定义sql语句  根据业务id UId去更新整个实体
         * 删除和更新操作，需要@Modifying和@Transactional注解的支持
         *
         * 更新操作中 如果某个字段为null则不更新，否则更新【注意符号和空格位置】
         * @param member 对象
         * @return int 被修改数据 条数
         */
        @Modifying
    
        @Query("update tb_member tm set " +
                "tm.name = CASE WHEN :#{#m.name} IS NULL THEN tm.name ELSE :#{#m.name} END ," +
                "tm.phone =  CASE WHEN :#{#m.phone} IS NULL THEN tm.phone ELSE :#{#m.phone} END, " +
                "tm.sex =  CASE WHEN :#{#m.sex} IS NULL THEN tm.sex ELSE :#{#m.sex} END, " +
                "tm.age =  CASE WHEN :#{#m.age} <=0 THEN tm.age ELSE :#{#m.age} END " +
                "where tm.id = :#{#m.id}")
        int update(@Param("m") Member member);
    
    
    }

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2gzNTYzNjM_size_16_color_FFFFFF_t_70]: /images/20210726/dbd8ac20e85d49d9ad94dfaea971463b.png
[20190807213802538.png]: /images/20210726/6b4b5d999ece4d0dad5e58e6e258fd4e.png
[20190807213817617.png]: /images/20210726/8adad68b2f4f4c0687ed8c67641afddc.png
[20190807213835211.png]: /images/20210726/fb44f90b52d44899877e15e222a87c44.png
[20190807213852157.png]: /images/20210726/8b374cd88de046a7ba84f6b56a3a8b5e.png
[20190811194645163.png]: /images/20210726/9d4cfe2e19724a93816cbec347ed2cd0.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2gzNTYzNjM_size_16_color_FFFFFF_t_70 1]: /images/20210726/c6a21d3a9ad040b0b78678a96db78888.png