JDBC 个人笔记

@RestController
public class UserController {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    
	//创建表
    @GetMapping("createTable")
    public String createTable(){
        String sql =
                "CREATE table `user`( \n" +
                "`id` INT(11) not null auto_increment,\n"+
                "`user_name` VARCHAR(255) not NULL,\n"+
                "`user_password` VARCHAR(255) DEFAULT null,\n"+
                "PRIMARY KEY(`id`)"+
                ")";
        jdbcTemplate.execute(sql);
        return "创建成功";
    }

    //直接http://localhost:8080/saveUser?userName=zhangsan&&passWord=123
    //方法的参数就是GET方法传过来的值
    @GetMapping("saveUser")
    public String saveUser(String userName,String passWord){
        int rows = jdbcTemplate.update("INSERT INTO USER(user_name,user_password) values (?,?)",userName,passWord);
        return "插入成功，影响"+ rows +"行";
    }

	//更新
    @GetMapping("update")
    public String update(int id,String passWord){
        int rows = jdbcTemplate.update("UPDATE USER SET user_password=? where ID = ?",passWord,id);
        return "更新成功，影响" + rows +"行";
    }

	//删除
    @GetMapping("delete")
    public String delete(int id){
        int rows = jdbcTemplate.update("DELETE FROM USER WHERE ID = ?",id);
        return "删除成功，影响" + rows +"行";
    }

//批量添加
    @GetMapping("batchSaveUserSql")
    public String batchSaveUserSql(){
        String sql = "INSERT INTO USER(user_name,user_password) VALUES (?,?)";
        List<Object[]> paramList = new ArrayList<>();
        for (int i=0;i<10;i++){
            String [] arr= new String[2];
            arr[0] = "zhangsan"+i;
            arr[1] = "password"+i;
            paramList.add(arr);
        }
        jdbcTemplate.batchUpdate(sql,paramList);
        return "执行成功";
    }

    //query方法
    @GetMapping("getUserByName")
    public List getUserByName(String userName){
        String sql = "SELECT * FROM USER WHERE USER_NAME = ?";
        List<User> list = jdbcTemplate.query(sql,new Object[]{userName},new BeanPropertyRowMapper<>(User.class));
        return list;
    }

	//execute方法
    @GetMapping("getMapById")
    public Map getByMapById(Integer id){
        String sql = "SELECT * FROM USER WHERE ID= ?";
        Map map = jdbcTemplate.queryForMap(sql,id);
        return map;
    }

}

1、

2、

3、

4

5

6

7