SpringBoot + JdbcTemplate 实现增删改查

最新推荐文章于 2024-06-29 19:07:17 发布

小杨同学~

最新推荐文章于 2024-06-29 19:07:17 发布

阅读量285

点赞数 1

分类专栏：微服务文章标签： mysql

本文链接：https://blog.csdn.net/weixin_38937840/article/details/113766364

版权

微服务专栏收录该内容

32 篇文章 2 订阅

订阅专栏

⭐ 微信社区交流群

⭐ 添加微信 372787553 备注csdn

源码地址在文末

⭐ 创建项目

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <parent>
        <artifactId>advanced-demo</artifactId>
        <groupId>com.javayh.advanced</groupId>
        <version>1.0.0.RELEASE</version>
    </parent>
    <modelVersion>4.0.0</modelVersion>

    <artifactId>source-jdbctemplate-code</artifactId>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
    </dependencies>

</project>

⭐ 配置数据库链接

server:
  port: 9008

spring:
  application:
    name: jdbc-template
  mvc:
    throw-exception-if-no-handler-found: true
  main:
    allow-bean-definition-overriding: true
  datasource:
    driverClassName: com.mysql.cj.jdbc.Driver
    url:  jdbc:mysql://localhost:3306/boke?serverTimezone=CTT&characterEncoding=utf8&autoReconnect=true&useUnicode=true&useSSL=true
    username: root
    password: root
    hikari:
      minimum-idle: 10
      maximum-pool-size: 30
      auto-commit: true
      idle-timeout: 30000
      pool-name: DemoHikari
      max-lifetime: 60000
      connection-timeout: 60000
      validation-timeout: 5000
      read-only: false
      login-timeout: 5

⭐ JdbcTemplate 演示

package com.javayh.jdbc.service;

import com.javayh.jdbc.entity.Demo;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * <p>
 *
 * </p>
 *
 * @author Dylan
 * @version 1.0.0
 * @since 2021-02-08
 */
@Service
public class JdbcDemoService {

    @Resource
    private JdbcTemplate jdbcTemplate;

    String INSERT_DEMO = "INSERT INTO demo (name ,pwd) VALUES (?,?);";
    String UPDATE_DEMO = "UPDATE demo SET name =? ,pwd =?  WHERE id = ?;";
    String DELETE_DEMO = "DELETE FROM demo WHERE id = ?;";
    String SELECT_COUNT = "SELECT COUNT(*) FROM demo;";
    String SELECT = "SELECT * FROM demo WHERE id =?;";
    String SELECT_ALL = "SELECT * FROM demo;";

    /**
     * 保存
     * @param name  名字
     * @param pwd   密码
     * @return int
     */
    public int save(String name, String pwd){
        return jdbcTemplate.update(INSERT_DEMO, name, pwd);
    }

    /**
     * 根据id 进行更新
     * @param name
     * @param pwd
     * @param id
     * @return
     */
    public int update(String name, String pwd,Integer id){
        return jdbcTemplate.update(UPDATE_DEMO, name, pwd,id);
    }

    /**
     * 删除
     * @param id
     * @return
     */
    public int delete(Integer id){
        return jdbcTemplate.update(DELETE_DEMO, id);
    }

    /**
     * 查询总条数
     * @param id
     * @return
     */
    public Integer query(Integer id){
        return jdbcTemplate.queryForObject(SELECT_COUNT,Integer.class);
    }

    /**
     * 查询实体
     * @param id
     * @return
     */
    public Demo queryDemo(Integer id){
        return jdbcTemplate.queryForObject(SELECT, (resultSet, i) -> {
            int id1 = resultSet.getInt("id");
            String name = resultSet.getString("name");
            String pwd = resultSet.getString("pwd");
            Demo demo = new Demo();
            demo.setId(id1);
            demo.setName(name);
            demo.setPwd(pwd);
            return demo;
        },id);
    }

    /**
     * 查询实体集合
     * @return
     */
    public List<Demo> queryAll(){
        //List<Demo> demoList = jdbcTemplate.queryForList(SELECT_ALL, Demo.class);
        return jdbcTemplate.query(SELECT_ALL,new BeanPropertyRowMapper<>(Demo.class));
    }

}