Springboot+JdbcTemplate实现增删改查及分页

实现对user表的crud操作

• 创建数据库和表

• 新建springboot项目

package com.ah.entity;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@NoArgsConstructor
@AllArgsConstructor
public class User {
    private Integer id;
    private String name;
    private Integer age;
    private String addr;
}

package com.ah.common;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.io.Serializable;
import java.util.List;

@Data
@NoArgsConstructor
@AllArgsConstructor
public class Page<T> implements Serializable {

    private static final long serialVersionUID = -7102129155309986923L;

    private List<T> list;				// list result of this page
    private int pageNumber;				// page number
    private int pageSize;				// result amount of this page
    private int totalPage;				// total page
    private int totalRow;				// total row

    public boolean isFirstPage() {
        return pageNumber == 1;
    }

    public boolean isLastPage() {
        return pageNumber >= totalPage;
    }
}

package com.ah.controller;

import com.ah.common.Page;
import com.ah.entity.User;
import com.ah.service.IUserService;
import org.springframework.web.bind.annotation.*;

import javax.annotation.Resource;
import java.util.List;

@RestController
@RequestMapping("/user")
public class UserController {
    @Resource
    private IUserService userService;
    @GetMapping("/queryAll")
    public List<User> queryAll(){
        return userService.queryAll();
    }

    @PostMapping("/add")
    public void add(@RequestParam String name, @RequestParam Integer age, @RequestParam String addr){
        userService.add(name,age,addr);
    }

    @PostMapping("/del")
    public void del(@RequestParam Integer id){
        userService.del(id);
    }

    @PostMapping("/edit")
    public void edit(@RequestParam Integer id, @RequestParam String name, @RequestParam Integer age, @RequestParam String addr){
        userService.edit(id,name,age,addr);
    }

    @PostMapping("/page")
    public Page<User> page(@RequestParam Integer pageNumber, @RequestParam Integer pageSize){
        return userService.page(pageNumber,pageSize);
    }
}

package com.ah.service;

import com.ah.common.Page;
import com.ah.entity.User;

import java.util.List;

public interface IUserService {
    List<User> queryAll();
    void add(String name,Integer age,String addr);
    void del(Integer id);
    void edit(Integer id,String name,Integer age,String addr);
    Page<User> page(Integer pageNumber,Integer pageSize);
}

package com.ah.service.impl;

import com.ah.common.Page;
import com.ah.entity.User;
import com.ah.service.IUserService;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.util.List;

@Service
public class UserServiceImpl implements IUserService {
    @Resource
    private JdbcTemplate jdbcTemplate;
    @Override
    public List<User> queryAll() {
        String sql = "select * from sys_user";
//        List<User> userList = jdbcTemplate.query(sql, new RowMapper<User>() {
//            @Override
//            public User mapRow(ResultSet rs, int i) throws SQLException {
//                User user = new User();
//                user.setId(rs.getInt("id"));
//                user.setName(rs.getString("name"));
//                user.setAge(rs.getInt("age"));
//                user.setAddr(rs.getString("addr"));
//                return user;
//            }
//        });
        //        List<Map<String, Object>> userList = jdbcTemplate.queryForList(sql);
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(User.class));
    }

    @Override
    public void add(String name,Integer age,String addr) {
        jdbcTemplate.update("insert into sys_user(name,age,addr) values(?,?,?)", name, age, addr);
    }

    @Override
    public void del(Integer id) {
        jdbcTemplate.update("delete from sys_user where id=?",id);
    }

    @Override
    public void edit(Integer id,String name,Integer age,String addr) {
        jdbcTemplate.update("update sys_user set name=?,age=?,addr=? where id=?", name, age, addr, id);
    }

    @Override
    public Page<User> page(Integer pageNumber,Integer pageSize) {
        String countSql = "select count(1) from sys_user";
        Integer totalRow = jdbcTemplate.queryForObject(countSql, Integer.class);
        String sql = "select * from sys_user limit ?,?";
        List<User> userList = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(User.class), (pageNumber-1)*pageSize, pageSize);
        int totalPage = totalRow%pageSize==0?totalRow/pageSize:totalRow/pageSize+1;
        return new Page<>(userList,pageNumber,pageSize,totalPage,totalRow);
    }
}

spring:
    datasource:
        driver-class-name: com.mysql.cj.jdbc.Driver
        hikari:
            connection-timeout: 60000
            idle-timeout: 30000
            login-timeout: 5
            max-lifetime: 60000
            maximum-pool-size: 60
            minimum-idle: 10
            validation-timeout: 3000
        password: fucksb1314
        url: jdbc:mysql://localhost:3307/springboot
        username: root

• UserServiceImpl

• postman测试

赏

谢谢你请我喝咖啡

支付宝

微信