参考资料
源码
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);
}