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