SpringBoot2 学习4 JPA 查询

最新推荐文章于 2026-04-18 03:33:06 发布

原创最新推荐文章于 2026-04-18 03:33:06 发布 · 866 阅读

1 ·

本内容遵循CC 4.0 BY-SA版权协议

SpringBoot 专栏收录该内容

47 篇文章

订阅专栏

本文详细介绍SpringBoot框架下如何运用JPA进行数据库操作，包括基本的增删查改、复杂查询、批量修改等，同时提供了详细的代码示例。

参考资料

在这里插入图片描述

源码

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);
	}

}

测试

在这里插入图片描述

复杂查询

使用 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);
	 }

}

测试结果：

在这里插入图片描述

复杂查询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);


}