Spring Boot数据库操作

1、pom.xml

<!--引入Spring Boot依赖-->
    <parent>
        <artifactId>spring-boot-starter-parent</artifactId>
        <groupId>org.springframework.boot</groupId>
        <version>2.7.1</version>
    </parent>

    <dependencies>
        <!--引入web应用程序开发的依赖启动器-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <!--导入配置文件处理器-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-configuration-processor</artifactId>
            <optional>true</optional>
        </dependency>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.10</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
            <version>5.1.24</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>

    </dependencies>

2、application.properties

spring.datasource.druid.url=jdbc:mysql://localhost:3306/springboot
spring.datasource.druid.username=root
spring.datasource.druid.password=root
spring.datasource.druid.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.druid.stat-view-servlet.login-username=root
spring.datasource.druid.stat-view-servlet.login-password=root

3、启动类AssetsManagerApplication

@SpringBootApplication//标记该类为主程序启动类
public class AssetsManagerApplication {
    public static void main(String[] args) {
        SpringApplication.run(AssetsManagerApplication.class,args);
    }
}

4、实体类Customer

package com.hbgc.pojo;

import lombok.Data;

@Data
public class Customer {
    private Integer id;
    private String jobNo;
    private String name;
    private String department;
}

5、创建Dao层实现类接口CustomerDao

public interface CustomerDao {
    public int saveCusomer(Customer customer);
    public List<Customer> getAllCustomer();
    public Customer getCustomerById(Integer id);
    public int updateCustomer(Customer customer);
    public int deleteCustomer(Integer id);
}

6、创建Dao层实现类CustomerDaoImpl

@Repository
public class CustomerDaoImpl implements CustomerDao{
    @Autowired
    private JdbcTemplate jdbcTemplate;

    //添加用户信息
    @Override
    public int saveCusomer(Customer customer) {
        String sql = "insert into student(jobNo,name,department) values(?,?,?)";
        int result = jdbcTemplate.update(sql, new Object[]{customer.getJobNo(), customer.getName(), customer.getDepartment()});
        return result;
    }

    //获取所有用户信息
    @Override
    public List<Customer> getAllCustomer() {
        String sql="select * from student";
        return jdbcTemplate.query(sql,new BeanPropertyRowMapper<>(Customer.class));
    }

    //根据id查询用户信息
    @Override
    public Customer getCustomerById(Integer id) {
        String sql="select * from student where id=?";
        return jdbcTemplate.queryForObject(sql,new BeanPropertyRowMapper<>(Customer.class),id);
    }

    //修改客户信息
    @Override
    public int updateCustomer(Customer customer) {
        String sql = "update student set jobNo=?,name=?,department=? where id = ?";
        return jdbcTemplate.update(sql,customer.getJobNo(),customer.getName(),customer.getDepartment(),customer.getId());
    }

    //删除客户信息
    @Override
    public int deleteCustomer(Integer id) {
        String sql = "delete from student where id=?";
        return jdbcTemplate.update(sql,id);
    }
}

7、Service层接口CustomerService

public interface CustomerService {
    public int saveCusomer(Customer customer);
    public List<Customer> getAllCustomer();
    public Customer getCustomerById(Integer id);
    public int updateCustomer(Customer customer);
    public int deleteCustomer(Integer id);
}

8、CustomerServiceImpl

@Service
public class CustomerServiceImpl implements CustomerService{
    @Autowired
    CustomerDao customerDao;

    @Override
    public int saveCusomer(Customer customer) {
        return customerDao.saveCusomer(customer);
    }

    @Override
    public List<Customer> getAllCustomer() {
        return customerDao.getAllCustomer();
    }

    @Override
    public Customer getCustomerById(Integer id) {
        return customerDao.getCustomerById(id);
    }

    @Override
    public int updateCustomer(Customer customer) {
        return customerDao.updateCustomer(customer);
    }

    @Override
    public int deleteCustomer(Integer id) {
        return customerDao.deleteCustomer(id);
    }
}

9、CustomerController

@RestController
public class CustomerController {
    @Autowired
    CustomerService customerService;

    //添加客户信息
    @GetMapping("/save")
    public String saveCustomer(){
        Customer customer = new Customer();
        customer.setJobNo("J00103");
        customer.setName("张三峰");
        customer.setDepartment("软件学院");
        int result = customerService.saveCusomer(customer);
        if (result > 0) {
            return "添加用户信息成功";
        }else {
            return "添加用户信息失败";
        }
    }
    //获取所有用户信息
    @GetMapping("/getAll")
    public String getAllCustomer() {
        return customerService.getAllCustomer().toString();
    }

    //根据id查询所有用户信息
    @GetMapping("/getCustomer/{id}")
    public Customer getCustomerById1(@PathVariable Integer id) {
        return customerService.getCustomerById(id);
    }

    //修改用户信息
    @GetMapping("/update")
    public String updateCustomer(Customer customer) {
        customer.setId(1);
        customer.setName("孙鹤翔");
        customer.setJobNo("J00106");
        customer.setDepartment("音乐学院");
        int result = customerService.updateCustomer(customer);
        if (result > 0) {
            return "修改用户信息成功";
        }else {
            return "修改用户信息失败";
        }
    }

    //删除用户信息
    @GetMapping("/delete/{id}")
    public String deleteCustomer(@PathVariable Integer id) {
        int result=customerService.deleteCustomer(id);
        if (result > 0) {
            return "删除用户信息成功";
        }else {
            return "删除用户信息失败";
        }
    }

}