springboot & JPA 连接SqlServer 增删改查

加载maven依赖：

<!-- web项目初始依赖 -->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
</dependency>

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-test</artifactId>
    <scope>test</scope>
</dependency>

<!-- get set -->
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <version>1.18.2</version>
</dependency>

<!-- JPA依赖 -->
<dependency>
    <groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>

<!-- sqlserver依赖 -->
<dependency>
    <groupId>com.microsoft.sqlserver</groupId>
    <artifactId>mssql-jdbc</artifactId>
    <scope>runtime</scope>
</dependency>

配置文件：application.properties

server.port=8001
server.tomcat.uri-encoding=utf-8

spring.datasource.driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver
spring.datasource.url=jdbc:sqlserver://127.0.0.1:1433;DatabaseName=bom_accounting
spring.datasource.username=sa
spring.datasource.password=

#显示sql语句
spring.jpa.show-sql=true

 

新建实体类

/**
 * 公告表
 * Created by wmm on 2019/4/17.
 */
@Entity
@Table(name = "notice")
@Data
public class Notice {

    @Id
    private String id;

    private String title;

    private String information;

    /**
     * 状态：1保存；2发布
     */
    private int status;

    private String goodsId;

    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone="GMT+8")
    private Date createTime;

    private String createUser;

    private Boolean delFlag;

    private Boolean factoryView = false;
}

@Table(name = "notice") 代表数据库表名

@Data 相当于 get set 方法

DAO

/**
 * 公告 DAO
 * Created by wmm on 2019/4/24.
 */
public interface NoticeRepository extends JpaRepository<Notice,Integer>, JpaSpecificationExecutor<Notice> {

    Notice findByIdAndDelFlag(String id,Boolean delFlag);

    Notice findByTitleAndDelFlag(String title,Boolean delFlag);

    Integer countAllByFactoryViewAndStatusAndDelFlag(Boolean factoryView,int status,Boolean delFlag);

}

JpaRepository 实现基本查询
JpaSpecificationExecutor 主要分页查询用到

Service

/**
 * 公告Service
 * Created by wmm on 2019/4/24.
 */
@Service
public class NoticeService {
    
    @Autowired
    NoticeRepository noticeRepository;

    /**
     * 分页查询
     * @param nameParam 查询条件
     * @param pageNum 页数
     * @param pageSize 每页显示条数
     * @return
     */
    public Page findList(String nameParam,
                         int pageNum, int pageSize){
        
        Pageable pageable = PageRequest.of(pageNum,pageSize, Sort.Direction.DESC,"createTime");
        Page<Notice> bookPage = noticeRepository.findAll(new Specification<Notice>(){
            @Override
            public Predicate toPredicate(Root<Notice> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) {
                List<Predicate> list = new ArrayList();
                if(!StringUtils.isEmpty(nameParam)){
                    list.add(criteriaBuilder.like(root.get("title").as(String.class), "%"+nameParam+"%"));
                }
                list.add(criteriaBuilder.equal(root.get("delFlag").as(Boolean.class),false));
                
                Predicate[] p = new Predicate[list.size()];
                return criteriaBuilder.and(list.toArray(p));
            }
        },pageable);
        return bookPage;
    }

    /**
     * 根据ID查询对象
     * @param id
     * @return
     */
    public Notice findById(String id){
        Notice notice = noticeRepository.findByIdAndDelFlag(id,false);
        //查询操作人是否是工厂人员
        Boolean bool = goodsComponent.ifFactory();
        if (bool){
            notice.setFactoryView(true);
            noticeRepository.save(notice);
        }
        return notice;
    }

    /**
     * 新增或编辑点击保存
     * @param bean
     * @return
     */
    @Transactional
    public Map edit(Notice bean){
        Map map = new HashMap();
        SysUser sysUser = (SysUser) SecurityUtils.getSubject().getPrincipal();
        if (null==bean.getId() || "".equals(bean.getId())){//新增
            bean.setId(IdUtil.getId());
        }
        Notice idBean = noticeRepository.findByIdAndDelFlag(bean.getId(),false);
        if (null==idBean){//新增
            idBean = noticeRepository.findByTitleAndDelFlag(bean.getTitle(),false);
            if (null!=idBean){
                map.put("code",0);
                map.put("msg","标题不能重复");
                return map;
            }
        }else{//修改
            Notice nameBean = noticeRepository.findByTitleAndDelFlag(bean.getTitle(),false);
            if (null!=nameBean && !nameBean.getId().equals(bean.getId())){
                map.put("code",0);
                map.put("msg","标题不能重复");
                return map;
            }
        }
        bean.setCreateUser(sysUser.getUserId());
        bean.setCreateTime(DateUtil.currentDateTime());
        bean.setDelFlag(false);
        bean = noticeRepository.save(bean);
        map.put("bean",bean);
        map.put("code",1);
        map.put("msg","成功");
        return map;
    }

    /**
     * 点击删除
     * @param id
     * @return
     */
    @Transactional
    public Map deleteById(String id){
        Map map = new HashMap();
        try {
            Notice bean = noticeRepository.findByIdAndDelFlag(id,false);
            bean.setDelFlag(true);
            noticeRepository.save(bean);
            map.put("code",1);
            map.put("msg","成功");
        }catch (Exception e){
            map.put("code",0);
            map.put("msg","失败");
            e.printStackTrace();
        }
        return map;
    }

    /**
     * 查询工厂人员未查看公告数量
     * @return
     */
    public Integer listCount(){
        return noticeRepository.countAllByFactoryViewAndStatusAndDelFlag(false,NoticeStatusEnum.RELEASE.getValue(),false);
    }
}

Controller 

/**
 * 公告Controller
 * Created by wmm on 2019/4/24.
 */
@Controller
@RequestMapping("notice")
public class NoticeController {
    
    @Autowired
    NoticeService noticeService;
    @Autowired
    GoodsService goodsService;

    @RequestMapping("list")
    @RequiresPermissions("notice:view")//权限管理;
    public String list(HashMap map,String nameParam,
                       @RequestParam(value = "page", defaultValue = "1") Integer page,
                       @RequestParam(value = "pageSize", defaultValue = "10") Integer pageSize){
        try {
            JSONObject jsonObject = (JSONObject) JSON.toJSON(noticeService.findList(nameParam,page-1,pageSize));
            System.out.println(jsonObject);
            map.put("datas",jsonObject);
            map.put("nameParam",nameParam);
            map.put("goodsList",goodsService.findAll());
            map.put("code",1);
            map.put("msg","成功");
        }catch (Exception e){
            map.put("code",0);
            map.put("msg","失败");
            e.printStackTrace();
        }
        return "notice_list";
    }

    @RequestMapping("toEdit")
    @ResponseBody
    public Map toEdit(String id){
        Map map = new HashMap();
        try {
            map.put("bean",noticeService.findById(id));
            map.put("code",1);
            map.put("msg","成功");
        }catch (Exception e){
            map.put("code",0);
            map.put("msg","失败");
            e.printStackTrace();
        }
        return map;
    }

    @RequestMapping("edit")
    @ResponseBody
    @RequiresPermissions("notice:edit")//权限管理;
    public Map editMenu(Notice bean){
        Map map = new HashMap();
        try {
            return noticeService.edit(bean);
        }catch (Exception e){
            map.put("code",0);
            map.put("msg","失败");
            e.printStackTrace();
            return map;
        }
    }

    @RequestMapping("deleteById")
    @ResponseBody
    @RequiresPermissions("notice:delete")//权限管理;
    public Map deleteById(String id){
        return noticeService.deleteById(id);
    }

    @RequestMapping("listCount")
    @ResponseBody
    public Map listCount(){
        Map map = new HashMap();
        try {
            map.put("count",noticeService.listCount());
            map.put("code",1);
            map.put("msg","成功");
        }catch (Exception e){
            map.put("code",0);
            map.put("msg","失败");
            e.printStackTrace();
        }
        return map;
    }