@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