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 ( " +
"id int(11), " +
"user_name varchar(255), " +
"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