170623、springboot编程之JdbcTemplate操作数据库

 

使用JdbcTemplate操作mysql数据库！

1、在pom中引入jpa包

<dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>

2、编写UserDao.java

package com.rick.apps.dao;


import com.rick.apps.entity.User;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.sql.*;
import java.util.List;

/**
 * Desc :  JdbcTemplate操作数据库
 * User : RICK
 * Time : 2017/8/21 16:52
  */

@Repository
public class UserDao {

    @Resource
    private JdbcTemplate jdbcTemplate;

    /**
     * Desc :  添加用户
     * User : RICK
     * Time : 2017/8/21 17:09
      */

    public void addUser(User user){
        String sql = "insert into user(id,user_name,pass_word) values(null,?,?)";

        jdbcTemplate.update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
                ps.setString(1, user.getUserName());
                ps.setString(2, user.getPassWord());
                return ps;
            }
        });
    }

    /**
     * Desc :  删除用户
     * User : RICK
     * Time : 2017/8/21 17:10
      */

    public void delUser(int id){
        final String sql = "delete from user where id=?";
        jdbcTemplate.update(sql,new Object[]{id});
    }

    /**
     * Desc :  更新用户
     * User : RICK
     * Time : 2017/8/21 17:10
     */
    public void updateUser(User user){
        final String sql = "update user set user_name=?,pass_word=? where id=?";
        jdbcTemplate.update(sql,new Object[]{user.getUserName(),user.getPassWord(),user.getId()});
    }

    /**
     * Desc :  查询所有用户信息
     * User : RICK
     * Time : 2017/8/21 17:16
      */

    public List<User> findAll() {
        return jdbcTemplate.query("select * from user", new UserRowMapper());
    }

    /**
     * Desc :  查询单个用户
     * User : RICK
     * Time : 2017/8/21 17:16
      */

    public User findUserById(int id) {
        return jdbcTemplate.queryForObject("select * from user where id=?", new Object[]{id}, new UserRowMapper());
    }

    /**
     * Desc :  封装用户获取数据类
     * User : RICK
     * Time : 2017/8/21 17:15
      */

    class UserRowMapper implements RowMapper<User> {
        @Override
        public User mapRow(ResultSet rs, int rowNum) throws SQLException {
            User user = new User();
            user.setId(rs.getInt("id"));
            user.setUserName(rs.getString("user_name"));
            user.setPassWord(rs.getString("pass_word"));
            return user;
        }

    }

}

3、编写UserService.java 引入UserDao，直接调用底层的增删改查方法（此处我只写了一个保存方法）

package com.rick.apps.service;

import com.rick.apps.dao.UserDao;
import com.rick.apps.entity.User;
import com.rick.apps.repository.UserRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

@Transactional
@Service
public class UserService {

    @Autowired
    private UserRepository userRepository;

    @Autowired
    private UserDao userDao;


    public void save(User user){
        userRepository.save(user);
    }

    public void addUser(User user){
        userDao.addUser(user);
    }
}

4、编写HelloController.java,引入UserService，写保存方法add

package com.rick.apps.controller;

import com.rick.apps.entity.User;
import com.rick.apps.service.UserService;
import com.rick.common.ResultJson;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.annotation.Resource;


@RestController
public class HelloController {

    @Resource
    private UserService userService;

    @RequestMapping("/hello")
    public String hello(){
        System.out.println(1/0);
        return "Hello World!";
    }

    @GetMapping(value = "/save")
    public ResultJson saveDemo(){
        User user = new User();
        user.setUserName("rick");
        user.setPassWord("123456");
        userService.save(user);
        ResultJson resultJson = ResultJson.buildSuccessInstance();
        return resultJson;
    }

    @GetMapping(value = "/add")
    public ResultJson addUser(){
        User user = new User();
        user.setUserName("anna");
        user.setPassWord("123123");
        userService.addUser(user);
        ResultJson resultJson = ResultJson.buildSuccessInstance();
        return resultJson;
    }
}

5、启动项目测试

查看数据库

项目清单：