Springboot对数据库增删改查_springboot连接数据库后怎么进行增删改查

有拦截器，读取文件配置，无mapper的xml文件，多数据源

 虽然不难，但是文件多啊，可以用来唬人。

首先是两个实体类

（个人见解：用来保存数据的就叫实体类，这里是和数据库中对应的类就叫实体类）

@Data
public class Test {
     private Integer cid;
     @Nullable
     private String addr;
     @Nullable
     private String email;
}

@Data

public class User {
    @NotNull
    //    @Min( value=0 )
    private int id;
    @Nullable
    private String name;

    @Nullable
    private Integer age;
}

@Data的作用就是省略手动写set和get方法。

接下来是配置文件：

server.port=30999
logging.pattern.console=%d{yyyy/MM/dd-HH:mm} [%thread] %-5level %logger- %msg%n
logging.level.root=info
logging.level.com.example.crud=info
logging.file.name=logs/curd.log
logging.pattern.file=%d{yyyy/MM/dd-HH:mm} [%thread] %-5level %logger -%msg%n
spring.profiles.active=test

设置端口和日志输出以及用哪个子配置文件，这里选择的是test，所以我还有另外一个test的配置文件：

 

 

#aban
aban.padd.default-name=xiaohuang
aban.padd.default-password=1234567
aban.padd.default-token=W2s4d8J9*Gs3@

aban.padd.default-id=id
aban.padd.default-URL=http://localhost:30999/test/getAll
spring.datasource.user.driver-class-name=com.mysql.cj.jdbc.Driver

# ?????
spring.datasource.user.name=defaultDataSource
# ???????
spring.datasource.user.jdbc-url=jdbc:mysql://192.168.10.101:3306/db1?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT\
  %2B8
# ??????&???
spring.datasource.user.username=root
spring.datasource.user.password=123456asd



spring.datasource.test.driver-class-name=com.mysql.cj.jdbc.Driver
# ?????
spring.datasource.test.name=defaultDataSource
# ???????
spring.datasource.test.jdbc-url=jdbc:mysql://192.168.10.101:3306/db2?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8
# ??????&???
spring.datasource.test.username=root
spring.datasource.test.password=123456asd

spring.datasource.three.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.three.name=defaultDataSource
# ???????
spring.datasource.three.jdbc-url=jdbc:mysql://192.168.10.101:3306/db2?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8
# ??????&???
spring.datasource.three.username=root
spring.datasource.three.password=123456asd

看着很多其实很多都是重复的和没用的，三次设置数据源是为了给设置不同数据源。

接下来配置不同数据源

（虽然配置了两个数据源但是我是用来学习的，真正用的只有一个，所以只需要用一个表的可以省略此步骤）：

定义一个DataSourceConfig类

@Configuration

public class DatasourceConfig {
    @Bean
    @Primary
    @ConfigurationProperties(prefix="spring.datasource.user")
    public DataSource first(){
        return DataSourceBuilder.create().build();
    }
        @Bean
        @ConfigurationProperties(prefix="spring.datasource.test")
        public DataSource two(){
            return DataSourceBuilder.create().build();

    }
    @Bean
    @ConfigurationProperties(prefix = "spring.datasource.three")
    public DataSource druidSource(){
        DruidDataSource druidDataSource=new DruidDataSource();
        return druidDataSource;
    }
}

因为我用的是JDBCTemplate，所以在这里又定义了一个类给JDBCTemplate给不同的数据源进行匹配，不匹配直接@Autowired的话他会出错，因为数据源太多了，没有指定。

@Configuration
public class JdbcTemplateConfig {
    @Bean
    public JdbcTemplate oneTemplateForUser( @Qualifier("first")DataSource dataSource ){
        return new JdbcTemplate(dataSource);
    }

    @Bean
    public JdbcTemplate twoTemplate(@Qualifier("two") DataSource dataSource){
        return new JdbcTemplate(dataSource);
    }
//    @Bean
//    public JdbcTemplate druidSource(@Qualifier("druidSource")DataSource dataSource){
//        return  new JdbcTemplate(dataSource);
//    }
}

配置好了，可以写主要的代码了：

定义一个类，里面写你的主要代码：

package com.example.crud.demos.controller;

import com.example.crud.demos.pojo.User;
import com.example.crud.demos.profiles.UserProfiles;
import com.example.crud.demos.utils.Operator;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestAttribute;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.bind.annotation.RestController;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

/**
 * @author ShiGuoLi
 */
@RestController
@RequestMapping ("/user")
public class UserJdbcTemplate {
    @Resource(name = "oneTemplateForUser")
    private JdbcTemplate jdbcTemplateUser;

        Logger logger= LoggerFactory.getLogger( this.getClass() );
    @GetMapping("/getAll")
    public List< Map<String,Object> > getAll(){
       Operator operator=Operator.SEARCHALL;
        List< Map< String, Object > > list = ( List< Map< String, Object > > ) userOperator( operator, null, null );
        return list;
    }
    @GetMapping("/getUserById")
    public List<Map<String,Object>> getUserById( HttpServletRequest request ){

        Operator operator=Operator.SEARCH;
        List< Map< String, Object > > list = ( List< Map< String, Object > > ) userOperator( operator, request, null );
        return list;
    }
    @PostMapping("/deleteById")
    public String deleteById(HttpServletRequest request){
        Operator operator=Operator.DELETE;
        String deleteOK = ( String ) userOperator( operator, request, null );
        System.out.println("输出成功");
        return deleteOK;
    }
    @PostMapping("/insert")

    public String insert(  @RequestAttribute("user")  User user ){
        Operator operator=Operator.INSERT;

        String userOperator = ( String ) userOperator( operator, null, user );
        return  userOperator;
    }
    @PostMapping("/create")
   
    public String create(  @RequestAttribute("user")  User user ){
        Operator operator=Operator.CREATE;
       
        String userOperator = ( String ) userOperator( operator, null, user );
        return  userOperator;
    }
    @PostMapping("/update")
    public String update(  @RequestAttribute("user") User user){
        Operator operator=Operator.UPDATE;
        String userOperator = ( String ) userOperator( operator, null, user );
        return  userOperator;
    }
    public Object userOperator( Operator operator, HttpServletRequest request, User user ) {
        int id = 0;
        String name = null;
        Integer age = null;
        if ( operator == Operator.CREATE || operator == Operator.UPDATE || operator == Operator.INSERT ) {
            id = user.getId();
            age = user.getAge();
            name = user.getName();
        } else if ( operator == Operator.SEARCH || operator == Operator.DELETE ) {
            id = Integer.parseInt( request.getParameter( "id" ));

        }

        String sql1 = "select * from user where id =?";
        List< Map< String, Object > > list = jdbcTemplateUser.queryForList( sql1, id );
        if ( list.size() == 0 ) {
            if ( operator == Operator.DELETE  || operator == Operator.UPDATE ) {
                return "您的输入有误，没有该id的用户哦";
            }else if(operator == Operator.SEARCH){
                Map<String,Object> map = new HashMap<>();
                map.put("err","no  this id");
                List<Map<String,Object>> listForSearch =new ArrayList<>();
                listForSearch.add( map );
                return listForSearch;
            }else if ( operator == Operator.CREATE || operator == Operator.INSERT ) {
                String sql2 = "insert into user value (?,?,?)";
                jdbcTemplateUser.update( sql2, id, name, age );
                return "输入成功";
            } else {
                String sqlForSearchAll = "select * from user";
                List< Map< String, Object > > list1 = jdbcTemplateUser.queryForList( sqlForSearchAll );
                return list1;
            }
        } else {
            if ( operator == Operator.CREATE || operator == Operator.INSERT ) {
                return "您的输入有误，已经存在该id的用户";
            } else if ( operator == Operator.DELETE ) {
                String sqlForDelete = "delete from user where id=?";
                jdbcTemplateUser.update( sqlForDelete, id );
                return "delete_ok";
            } else if ( operator == Operator.UPDATE ) {
                String sqlForUpdate = "update user set ";
                String sqlName = "name=? ";
                String sqlAge1 = ",age=? ";
                String sqlAge2 = "age=? ";
                String sqlId = "where id=?";
                String sql = null;
                if ( name!=null ) {
                    if ( age !=null ) {
                        sql = sqlForUpdate + sqlName + sqlAge1 + sqlId;
                        System.out.println( sql );
                        jdbcTemplateUser.update( sql, name, age, id );
                        return "update_ok";
                    } else {
                        sql = sqlForUpdate + sqlName + sqlId;
                        jdbcTemplateUser.update( sql, name, id );
                        return "update_ok";
                    }
                } else {
                    if ( age !=null ) {
                        sql = sqlForUpdate + sqlAge2 + sqlId;
                        logger.info("sql:{}",sql);
                        jdbcTemplateUser.update( sql, age, id );
                        return "update_ok";
                    } else {

                        return "有没名字，又没年龄，你更新个什么？";
                    }
                }
            } else if ( operator == Operator.SEARCH ) {
                return list;
            } else {
                String sqlForSearchAll = "select * from user";
                List< Map< String, Object > > list1 = jdbcTemplateUser.queryForList( sqlForSearchAll );
                return list1;
                

            }
        }
    }

}

首先将匹配好数据源的jdbcTemplate导入，这里我导入的是匹配user的那个。

从上到下分别是：查询所有，根据ID查用户，根据id删除用户，插入用户，创建用户，更新用户和总方法。

但是数据并非都是正确的，比如你的数据没有输入Id，那么就查不了，可以在主代码中编写判断条件，但是这里我使用的是拦截器；

拦截器：

package com.example.crud.demos.interceptor;

import com.alibaba.druid.sql.ast.statement.SQLForeignKeyImpl;
import com.example.crud.demos.pojo.User;
import com.example.crud.demos.proxyprofiles.AbanProfiles;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.fasterxml.jackson.databind.util.JSONPObject;
import com.sun.org.apache.bcel.internal.classfile.Code;
import jdk.internal.util.xml.impl.Input;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.method.HandlerMethod;
import org.springframework.web.servlet.HandlerInterceptor;
import org.springframework.web.servlet.ModelAndView;

import javax.servlet.ServletInputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.util.Arrays;
import java.util.HashMap;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import static com.alibaba.druid.util.FnvHash.Constants.JSON;

/**
 * @author ShiGuoLi
 */
public class LoginInterceptor implements HandlerInterceptor {
    @Autowired
    AbanProfiles abanProfiles;
    private Logger logger= LoggerFactory.getLogger( this.getClass() );
    private  static final String REGEX1="[//insert]{0,}[ById]{0,1}[//update]{0,}[//create]{0,}";
    private  static final String REGEX2="[//delete]{0,}[//get^A]{0,}";

    /**
     * 目标方法执行前
     *
     * @param request
     * @param response
     * @param handler
     * @return
     * @throws Exception
     */
    @Override
    public boolean preHandle( HttpServletRequest request, HttpServletResponse response, Object handler )
    throws Exception {
        Integer id=null;
        Pattern idFromBody=Pattern.compile( REGEX1 );
        Pattern idFromParam = Pattern.compile( REGEX2 );

        String url = request.getRequestURL().toString();
        Matcher bodyMatcher = idFromBody.matcher( url );
        Matcher paramMatcher = idFromParam.matcher( url );
//        Object loginUser = request.getSession().getAttribute( "loginUser" );
//        String token = request.getHeader( "Token" );
        String s = handler.toString();
        logger.info("this is handler:{}",s);

        if(bodyMatcher.lookingAt()){
            BufferedReader reader = request.getReader();
            StringBuilder builder = new StringBuilder();
            String line = reader.readLine();
            while(line != null){
                builder.append(line);
                line = reader.readLine();
            }

            String s1 = builder.toString();
            ObjectMapper objectMapper=new ObjectMapper();
            User user = objectMapper.readValue( s1, User.class );
            logger.info("user:{}",user);
            id=user.getId();
            System.out.println(id);
            request.setAttribute( "user",user );
        }else if(paramMatcher.lookingAt()){
         id = Integer.parseInt( request.getParameter( abanProfiles.getPadd().getDefaultId() ) );
        }
        if(id!=null) {
            //        HandlerMethod handlerMethod = ( HandlerMethod ) handler;
            //        StringBuffer requestURL = request.getRequestURL();
            //        logger.info("requestURL{}",requestURL);
            //        System.out.println(requestURL);
            //        if(requestURL.equals( abanProfiles.getPadd().getDefaultURL() )){
            //        return true;
            //        }else {
            if ( id <= 0 ) {
                request.setAttribute( "msg", "您没有权限操作，请先输入id" );
                logger.info( "您的id输入有误  --来自拦截器" );
                //            request.getRequestDispatcher( "/index.html" ).forward(request,response);
                                handleFalseResponse(response);
                return false;
                //        }else if(token.equals( "" )){
                //           request.setAttribute( "msg","token不匹配，您无法登录" );
                           request.getRequestDispatcher( "index/html" ).forward( request,response );
                //           return false;
            } else {
                return true;
            }
        }
        else{
            return true;
        }
    }


    /***
     *
     * 目标方法执行后
     *
     * @param request
     * @param response
     * @param handler
     * @param modelAndView
     * @throws Exception
     */
    @Override
    public void postHandle( HttpServletRequest request, HttpServletResponse response, Object handler,
                            ModelAndView modelAndView ) throws Exception {
        logger.info("postHandle执行{}",modelAndView);
    }

    @Override
    public void afterCompletion( HttpServletRequest request, HttpServletResponse response, Object handler,
                                 Exception ex ) throws Exception {
        HandlerInterceptor.super.afterCompletion( request, response, handler, ex );
        logger.info("afterCompletion执行异常{}",ex);
    }
    private void handleFalseResponse(HttpServletResponse response) throws IOException {
        response.setStatus( 400 );
        response.getWriter().write( "{\"err:\":\"id is not ok\"}" );
        response.setContentType( "application/json" );
        response.setCharacterEncoding( "UTF-8" );
        response.getWriter().flush();


    }
}

这里就是用了正则对路径进行选择，然后实现了对ID的判断，springboot的拦截器还是比较简单的，首先定义一个类实现HandlerInterceptor接口的三个方法，接下来还要将拦截器注册一下：

package com.example.crud.demos.utils;

import com.example.crud.demos.interceptor.LoginInterceptor;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.context.annotation.Configuration;
import org.springframework.stereotype.Controller;
import org.springframework.web.servlet.config.annotation.InterceptorRegistry;
import org.springframework.web.servlet.config.annotation.WebMvcConfigurer;

/**
 * @author ShiGuoLi
 */
@Configuration
public class MyMvcConfig implements WebMvcConfigurer {
    private Logger logger= LoggerFactory.getLogger( this.getClass() );
    @Override
    public void addInterceptors( InterceptorRegistry registry ) {
        logger.info("注册拦截器");
        //.addPathPatterns( "/*" ).excludePathPatterns( "/*" )
        registry.addInterceptor( new LoginInterceptor() ).addPathPatterns( "/test/*","/user/*" ).excludePathPatterns(
                "/user/getAll" );
    }
}

最后的那个.addxxxx的方法是对括号的路径与资源进行拦截，.excxxxxxx那个方法是对某个资源或者路径放行。

好了以上拦截器就注册成功。

遇到的错误：

有很多，都忘了不是啥大问题，有一个印象比较深：

如果在主代码中使用@RequestBody后，在使用拦截器，会产生一个关于getReader（）的错误，错误的大概意思是拦截器已经将数据读取过一遍了，@RequestBody将接受不到你从postman里面传入的数据

这里使用的方法是将数据再传回去，在拦截器中使用：

request.setAttribute("user",user);

在主代码中使用@RequestAttribute（“user”）代替@RequestBody

这里的两个参数：拦截器中的“user”与@RequestAttribute的“user”对应，

第二个参数是自己读取的参数，也是返回去的参数，要注意类型。