Springboot JDBC 对数据库的 CRUD操作

package com.example.demo.controller;

import com.example.demo.dao.JDBCBean;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;
import java.util.Map;

@RestController
public class JDBCController {
    @Value("${book.uuid}")  //Value 可直接取值
    private String uuid;
    @Autowired
    private JdbcTemplate jdbcTemplate;
    @GetMapping("/createTable")
    public String createTable(){
        String sql=" create table user (\n" +
                "id int(11),\n" +
                "user_name varchar(255),\n" +
                "user_password varchar(255) " +
                ");";
        jdbcTemplate.execute(sql);
        return "Create table Success!";
    }

    @GetMapping("/insert01")
    public String insertintoTable(){
        String sql="insert into user values (1,'hello','world');";
        String sql01="insert into user values (3,'"+uuid+"','world');";

        int rows= jdbcTemplate.update(sql);
        jdbcTemplate.execute(sql01);
        //也会执行
        return "insert into ____" + rows;
    }

    //查找所有数据
    @GetMapping("/select01")
    public List selectTable(){
        String sql="select * from user;";


         List a= jdbcTemplate.queryForList(sql);
//        jdbcTemplate.execute(sql01);
        //也会执行
        return  a ;
    }

    //查找ID 唯一的记录
    @GetMapping("/selectbyid")
    public Map selectTablebyid(Integer id){
        String sql="select * from user where id= ? ;";
        Map map=jdbcTemplate.queryForMap(sql,id);
        return  map ;
    }
//Map集合不能返回多Key唯一数据，故使用List集合
    @GetMapping("/selectbyid02")
    public  List getUserByUserColumn(String id){
        String sql="select * from user where id = ?";
        Object s=jdbcTemplate.query(sql,new Object[]{id},
                new BeanPropertyRowMapper<>(JDBCBean.class));
        List<JDBCBean> list=jdbcTemplate.query(sql,new Object[]{id},
                new BeanPropertyRowMapper<>(JDBCBean.class));
        return list;
    }


}

 


为了防止值乱入，这里做下处理

//查找ID 唯一的记录
@GetMapping("/selectbyid")
public Map selectTablebyid(Integer id){
    String sql="select * from user where id= ? ;";
    Map map=null;
    try {
        map = jdbcTemplate.queryForMap(sql, id);
    }catch (EmptyResultDataAccessException e){
        return null;
    }
    return  map ;
}

@GetMapping("/selectbyid02")
public  List getUserByUserColumn(String id){
    String sql="select * from user where id = ?";
    Object s=null;
    try {
        s = jdbcTemplate.query(sql, new Object[]{id},
                new BeanPropertyRowMapper<>(JDBCBean.class));
    }catch (EmptyResultDataAccessException e){
        return null;
    }
        List<JDBCBean> list = jdbcTemplate.query(sql, new Object[]{id},
                new BeanPropertyRowMapper<>(JDBCBean.class));

    return list;
}

实体类

package com.example.demo.dao;

public class JDBCBean {
    private int id;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getUser_name() {
        return user_name;
    }

    public void setUser_name(String user_name) {
        this.user_name = user_name;
    }

    public String getUser_password() {
        return user_password;
    }

    public void setUser_password(String user_password) {
        this.user_password = user_password;
    }

    private String user_name;
    private String user_password;
    public JDBCBean(int id,String user_name,String user_password){
        this.id=id;
        this.user_name=user_name;
        this.user_password=user_password;
    }

    public JDBCBean(){

    }
}

源码地址

链接：https://pan.baidu.com/s/1ge4BpWjoxaop6J2Ak2uFTA 
提取码：pplc