随笔- 247 文章- 0 评论- 22 阅读- 39万

SpringBoot--使用JDBC连接mysql

1、导入包

导入mysql和springJDBC的关系依赖包

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
 
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
</dependency>

2、添加数据库配置

3、创建表

CREATE TABLE `t_user` (
  `id` int(8) NOT NULL AUTO_INCREMENT COMMENT '主键自增',
  `username` varchar(50) NOT NULL COMMENT '用户名',
  `password` varchar(50) NOT NULL COMMENT '密码',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户表';

4、创建对象实体及操作数据库对象

　　（1）实体对象

package com.example.demo.entity;
 
import lombok.Data;
 
@Data
public class User {
    private Long id;
    private String username;
    private String password;
 
 
}

　　（2）操作数据库对象

package com.example.demo.dto;
 
 
import com.example.demo.entity.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
 
@Repository
public class UserDto {
    @Autowired
    private final JdbcTemplate jdbcTemplate;
 
 
    public UserDto(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }
 
 
    public int AddUser(User user){
        String sql = "insert into t_user(username, password) values(?, ?)";
        return jdbcTemplate.update(sql, user.getUsername(), user.getPassword());
    }
}

　5、添加Controller和Service

package com.example.demo.controller;

import com.example.demo.entity.User;
import com.example.demo.service.UserService;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiImplicitParam;
import io.swagger.annotations.ApiImplicitParams;
import io.swagger.annotations.ApiOperation;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.util.StringUtils;
import org.springframework.web.bind.annotation.*;

@Controller
@RequestMapping("/test")
@Api(value = "SpringBoot测试接口")
public class UserTestController {

    @Autowired
    private UserService userService;

    @ResponseBody
    @PostMapping(value ="/jdbc")
    @ApiOperation(value="整合jdbc测试")
    @ApiImplicitParams({
            @ApiImplicitParam(paramType="query", name = "username", value = "用户ID", required = true, dataType = "String"),
            @ApiImplicitParam(paramType="query", name = "password", value = "旧密码", required = true, dataType = "String")
    })
    public String test1(String username, String password){
        User u = new User();
        u.setUsername(username);
        u.setPassword(password);
        return userService.AddUser(u) + "";

    }

}

package com.example.demo.service;

import com.example.demo.entity.User;

public interface UserService {
    int AddUser(User user);
}

package com.example.demo.dto;


import com.example.demo.entity.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

@Repository
public class UserDto {
    @Autowired
    private final JdbcTemplate jdbcTemplate;


    public UserDto(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }


    public int AddUser(User user){
        String sql = "insert into t_user(username, password) values(?, ?)";
        return jdbcTemplate.update(sql, user.getUsername(), user.getPassword());
    }
}

6、测试