Spring Boot（三）:JDBCTemplate对数据表的增删改查

数据表的增删改查是基本操作，springboot中第一次接触，操作原理都是一样的。就是spring对jdbc的封装模版

 

1、实现类

@Service
public class UserServiceImpl {


    @Autowired
    private JdbcTemplate jdbcTemplate;


    //select
    public List<TUser> getList() {

        //sql
        String sql = "select * from T_user";

        //结果
        List<TUser> list =  jdbcTemplate.query(sql, new RowMapper<TUser>() {

            //映射每行数据
            @Override
            public TUser mapRow(ResultSet rs, int rowNum) throws SQLException {
                TUser tUser = new TUser();
                tUser.setId(rs.getInt("id"));
                tUser.setName(rs.getString("name"));
                return tUser;
            }
        });
        //返回结果
        return list;
    }


    //add
    public int addUser(String name) {

        int row = jdbcTemplate.update("insert into T_user (name) values (?)", new PreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps) throws SQLException {
                ps.setString(1, name);
            }
        });
        return row;
    }

    //delete
    public int delUser(Integer id) {

        int row = jdbcTemplate.update("delete from T_user where id = ?", new PreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps) throws SQLException {
                ps.setInt(1, id);
            }
        });
        return row;
    }

    //update
    public int updateUser(TUser tUser) {

        String sql = "update T_user set name = ? where id = ?";
        int row = jdbcTemplate.update(sql, new PreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps) throws SQLException {
                //映射数据
                ps.setString(1, tUser.getName());
                ps.setInt(2, tUser.getId());
            }
        });
        return row;
    }
}

 

2、Controller

@Controller
public class HelloWorldApplication {

    @RequestMapping("/index")
    public String index() {

        System.out.println("返回index页面");

        //跳转到 templates 目录下的 index.html
        return "index";
    }


    @Autowired
    private UserServiceImpl userService;

    @RequestMapping("/list")
    @ResponseBody
    public List<TUser> list() {

        System.out.println("从数据库中读取user集合");
        return userService.getList();
    }




    //模版返回user.html（开启thymeleaf依赖）
    @RequestMapping("/user_template")
    public String user_template() {
        System.out.println("返回模版user.html");
        return "user";  //templaers
    }

    //静态返回Html（关闭thymeleaf依赖）
    @RequestMapping("/user_static")
    public String user_static() {
        System.out.println("返回静态user.html");
        return "user";  //static
    }


    //add
    @RequestMapping("/add")
    @ResponseBody
    public int add(String name) {
        System.out.println("name:" + name);

        int row = userService.addUser(name);

        System.out.println("add row:" + row);
        return row;
    }

    //del
    @RequestMapping("/del")
    @ResponseBody
    public int del(Integer id) {
        int row = userService.delUser(id);

        System.out.println("del row:" + row);
        return row;
    }

    //update
    @RequestMapping("/update")
    @ResponseBody
    public int update() {

        //新建对象传递数据
        TUser tUser = new TUser();
        tUser.setId(1);
        tUser.setName("王灵生");

        int row = userService.updateUser(tUser);
        return row;
    }
}