SpringBoot整合Jdbctemplate-java黑洞网

本站消息

站长简介/公众号

出租广告位,需要合作请联系站长

我长得真不赖

550

文章

267028

访问

+关注

分类

暂无分类

日期归档

2024-11(1)

SpringBoot整合Jdbctemplate

发布于2021-05-29 21:43 阅读(1288) 评论(0) 点赞(6) 收藏(4)

本文介绍SpringBoot整合Jdbctemplate，使用Idea，jdk为1.8版本

本文项目已上传GitHub：SpringBoot整合Jdbctemplate

1.创建项目

首先创建一个Springboot项目，以idea为例：
在这里插入图片描述
然后选择自己需要使用到的其他依赖

如若未选择，也可以进入之后修改pom.xml，内容为：

<?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 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.5.0</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.example</groupId>
    <artifactId>Jdbctemplate</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>Jdbctemplate</name>
    <description>Demo project for Spring Boot</description>
    <properties>
        <java.version>1.8</java.version>
        <druid.version>1.0.29</druid.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
            <scope>runtime</scope>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.41</version>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>${druid.version}</version>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <configuration>
                    <excludes>
                        <exclude>
                            <groupId>org.projectlombok</groupId>
                            <artifactId>lombok</artifactId>
                        </exclude>
                    </excludes>
                </configuration>
            </plugin>
        </plugins>
    </build>

</project>

修改配置文件，application.yml

#修改启动端口
server:
  port: 8088
spring:
  #数据库配置
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    driver-class-name: com.mysql.jdbc.Driver
    url: jdbc:mysql://localhost:3306/springbootlx?useUnicode=true&characterEncoding=UTF-8
    username: root
    password: root
    # 配置初始化大小、最小、最大
    initialSize: 5
    minIdle: 5
    maxActive: 20
    # 配置获取连接等待超时的时间
    maxWait: 60000
    # 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
    timeBetweenEvictionRunsMillis: 60000
    # 配置一个连接在池中最小生存的时间，单位是毫秒
    minEvictableIdleTimeMillis: 30000
    validationQuery: SELECT 'x'
    testWhileIdle: true
    testOnBorrow: false
    testOnReturn: false
    # 打开PSCache，并且指定每个连接上PSCache的大小。如果用Oracle，则把poolPreparedStatements配置为true，mysql可以配置为false。分库分表较多的数据库，建议配置为false。
    poolPreparedStatements: false
    maxPoolPreparedStatementPerConnectionSize: 20
    # 配置监控统计拦截的filters
    filters: stat

创建本次需要的数据表（把yml中的url中数据库名称改成你自己的），如若换成自己的数据表，则修改yml中的url，实体类与代码逻辑

DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `u_id` int(11) NOT NULL AUTO_INCREMENT,
  `u_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`u_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 9 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

SET FOREIGN_KEY_CHECKS = 1;

2.开始编写代码

项目结构如下：
在这里插入图片描述

UserController

package bd.user.controller;

import bd.user.entity.User;
import bd.user.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author lolly1023
 * @desc
 * @time 2021/05/26/10:04
 */
@RestController
@RequestMapping("/user")
public class UserController {
    @Autowired
    UserService userService;
    @RequestMapping("query")
    public Map query(){
        Map<String, Object> result = new HashMap<>();
        List<Map<String, Object>> list = null;
        try {
            list = userService.query();
            result.put("method", "jdbctemplate");
            result.put("data", list);
            result.put("errorCode", 0);
            result.put("msg", "获取数据成功");
        } catch (Exception e) {
            result.put("errorCode", 1);
            result.put("msg", "后端服务器异常");
        }
        return result;
    }
    @RequestMapping(value = "add",method = RequestMethod.POST)
    public Map add(@RequestBody User user) {
        Map<String, Object> result = new HashMap<>();
        try {
            if (userService.add(user)) {
                result.put("msg","添加成功");
            } else {
                result.put("msg","添加失败");
            }
            result.put("method", "jdbctemplate");
            result.put("errorCode", 0);
        } catch (Exception e) {
            result.put("errorCode", 1);
            result.put("msg", "后端服务器异常");
        }
        return result;
    }
    @RequestMapping(value = "update",method = RequestMethod.POST)
    public Map update(@RequestBody User user) {
        Map<String, Object> result = new HashMap<>();
        try {
            if (userService.update(user)) {
                result.put("msg","修改成功");
            } else {
                result.put("msg","修改失败");
            }
            result.put("method", "jdbctemplate");
            result.put("errorCode", 0);
        } catch (Exception e) {
            result.put("errorCode", 1);
            result.put("msg", "后端服务器异常");
        }
        return result;
    }
    @RequestMapping(value = "delete",method = RequestMethod.POST)
    public Map delete(@RequestBody User user) {
        Map<String, Object> result = new HashMap<>();
        try {
            if (userService.delete(user)) {
                result.put("msg","删除成功");
            } else {
                result.put("msg","删除失败");
            }
            result.put("method", "jdbctemplate");
            result.put("errorCode", 0);
        } catch (Exception e) {
            result.put("errorCode", 1);
            result.put("msg", "后端服务器异常");
        }
        return result;
    }
}

UserService

package bd.user.service;

import bd.user.entity.User;

import java.util.List;
import java.util.Map;

/**
 * @author lolly1023
 * @desc
 * @time 2021/05/26/10:13
 */
public interface UserService {
    List<Map<String, Object>> query();

    boolean add(User user);

    boolean update(User user);

    boolean delete(User user);
}

UserServiceImpl

package bd.user.service;

import bd.user.dao.UserDao;
import bd.user.entity.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;
import java.util.Map;

/**
 * @author lolly1023
 * @desc
 * @time 2021/05/26/10:13
 */
@Service("userService")
public class UserServiceImpl implements UserService{
    @Autowired
    UserDao userDao;

    @Override
    public List<Map<String, Object>> query() {
        return userDao.query();
    }

    @Override
    public boolean add(User user) {
        return  userDao.add(user);
    }

    @Override
    public boolean update(User user) {
        return userDao.update(user);
    }

    @Override
    public boolean delete(User user) {
        return userDao.delete(user);
    }
}

UserDao

package bd.user.dao;

import bd.user.entity.User;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

/**
 * @author lolly1023
 * @desc
 * @time 2021/05/26/10:11
 */
public interface UserDao {
    List<Map<String, Object>> query();

    boolean add(User user);

    boolean update(User user);

    boolean delete(User user);
}

UserDaoImpl

package bd.user.dao;

import bd.user.entity.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * @author lolly1023
 * @desc
 * @time 2021/05/26/10:11
 */
@Repository("userDao")
public class UserDaoImpl implements UserDao{
    @Autowired
    JdbcTemplate jdbcTemplate;

    @Override
    public List<Map<String, Object>> query() {
        return jdbcTemplate.queryForList("select * from user");
    }

    @Override
    public boolean add(User user) {
       return jdbcTemplate.update("insert  into user values (0,?)",user.getU_name()) > 0;
    }

    @Override
    public boolean update(User user) {
        return jdbcTemplate.update("update user set  u_name = ? where u_id = ?",user.getU_name(),user.getU_id())>0;
    }

    @Override
    public boolean delete(User user) {
        return jdbcTemplate.update("delete from user where u_id = ?",user.getU_id())>0;
    }
}

User

package bd.user.entity;

import lombok.Data;
import lombok.ToString;

/**
 * @author lolly1023
 * @desc
 * @time 2021/05/26/10:08
 */
@Data
@ToString
public class User {
    Integer u_id;
    String u_name;
}