spring boot与jdbcTemplate的整合案例2

2022-12-29 22:48:04

简单入门了spring boot后，接下来写写跟数据库打交道的案例。博文采用spring的jdbcTemplate工具类与数据库打交道。

下面是搭建的springbootJDBC的项目的总体架构图：

<?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">

    <modelVersion>4.0.</modelVersion>  

    <groupId>com.example</groupId>

    <artifactId>demo</artifactId>

    <version>0.0.-SNAPSHOT</version>

    <packaging>jar</packaging>  

    <name>demo</name>

    <description>Demo project for Spring Boot</description>  

    <parent>

        <groupId>org.springframework.boot</groupId>

        <artifactId>spring-boot-starter-parent</artifactId>

        <version>1.5..RELEASE</version>

        <relativePath/> <!-- lookup parent from repository -->

    </parent>  

    <properties>

        <project.build.sourceEncoding>UTF-</project.build.sourceEncoding>

        <project.reporting.outputEncoding>UTF-</project.reporting.outputEncoding>

        <java.version>1.8</java.version>

    </properties>  

    <dependencies>

        <dependency>

            <groupId>org.springframework.boot</groupId>

            <artifactId>spring-boot-starter-web</artifactId>

        </dependency>

        <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>

        <dependency>

            <groupId>org.springframework.boot</groupId>

            <artifactId>spring-boot-starter-test</artifactId>

            <scope>test</scope>

        </dependency>

        <dependency>

            <groupId>org.springframework.boot</groupId>

            <artifactId>spring-boot-test</artifactId>

        </dependency>

        <dependency>

            <groupId>com.google.guava</groupId>

            <artifactId>guava</artifactId>

            <version>18.0</version>

        </dependency>

    </dependencies>  

    <!--spring boot maven插件-->

    <build>

        <plugins>

            <plugin>

                <groupId>org.springframework.boot</groupId>

                <artifactId>spring-boot-maven-plugin</artifactId>

            </plugin>

        </plugins>

    </build>  

</project>

接下来，贴出application.properties，设置tomcat端口号，数据库链接相关信息：

可以参照上一篇博文，参考参考如何建立一个spring boot项目，至于在选择依赖的配置时候，可以参考我下面贴出的pom.xml：

###### 设置tomcat访问端口号 ######

server.port=  

###### 设置数据源 ######

spring.datasource.url=jdbc:mysql://localhost:3306/db_springboot?autoReconnect=true&useUnicode=true&characterEncoding=utf-8

spring.datasource.username=root

spring.datasource.password=

spring.datasource.driver-class-name=com.mysql.jdbc.Driver

#spring.datasource.driverClassName = com.mysql.jdbc.Driver

建立数据库tb_springboot，然后执行下面的sql脚本，生成users表：

/*

Navicat MySQL Data Transfer  

Source Server         : localhost

Source Server Version : 50625

Source Host           : localhost:3306

Source Database       : db_springboot  

Target Server Type    : MYSQL

Target Server Version : 50625

File Encoding         : 65001  

Date: 2017-03-31 15:01:08

*/  

SET FOREIGN_KEY_CHECKS=;  

-- ----------------------------

-- Table structure for users

-- ----------------------------

DROP TABLE IF EXISTS `users`;

CREATE TABLE `users` (

  `id` int() NOT NULL AUTO_INCREMENT,

  `name` varchar() DEFAULT NULL,

  `email` varchar() DEFAULT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT= DEFAULT CHARSET=utf8;  

-- ----------------------------

-- Records of users

-- ----------------------------

INSERT INTO `users` VALUES ('', 'linsen', 'linsen@126.com');

INSERT INTO `users` VALUES ('', 'sam', 'sam@qq.com');

INSERT INTO `users` VALUES ('', 'debug', 'debug@sina.com');

INSERT INTO `users` VALUES ('', '杰克', '杰克@sina.com');

INSERT INTO `users` VALUES ('', '张三', '张三@sina.com');

INSERT INTO `users` VALUES ('', '李四', '李四@sina.com');

INSERT INTO `users` VALUES ('', '王五', '王五@sina.com');

INSERT INTO `users` VALUES ('', '王五2', '王五2@sina.com');

本博文我们对spring boot与jdbcTemplate进行整合，主要当然是实现基本的增删改查 user实体操作，首先是开发dao层：

package com.example.repository;  

import com.example.entity.User;

import com.example.exception.UserException;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.jdbc.core.JdbcTemplate;

import org.springframework.jdbc.core.PreparedStatementCreator;

import org.springframework.jdbc.core.PreparedStatementSetter;

import org.springframework.jdbc.core.RowMapper;

import org.springframework.jdbc.support.GeneratedKeyHolder;

import org.springframework.jdbc.support.KeyHolder;

import org.springframework.stereotype.Repository;

import org.springframework.transaction.annotation.Transactional;  

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.ArrayList;

import java.util.List;  

/**

 * Created by steadyjack on 2017/3/22.

 * 充当dao层UserRepository

 */

@Repository

public class UserRepository {  

    @Autowired

    private JdbcTemplate jdbcTemplate;  

    /**

     * 获取用户列表

     * @return

     * @throws Exception

     */

    @Transactional(readOnly = true)

    public List<User> getUserList() throws Exception{

        List<User> userList=jdbcTemplate.query("select id,name,email from users",new UserRowMapper());

        System.out.println(userList);

        return userList;

    }  

    /**

     * 根据用户id获取用户

     * @param id

     * @return

     * @throws Exception

     */

    @Transactional(readOnly = true)

    public User getUserById(Integer id) throws  Exception{

        //queryForObject:找不到会报异常  query:找不到则Null

        //User user=jdbcTemplate.queryForObject("select id,name,email from users where id=?",new Object[]{id},new UserRowMapper());

        List<User> userList=jdbcTemplate.query("select id,name,email from users where id=?",new Object[]{id},new UserRowMapper());

        User user=null;

        if (!userList.isEmpty()){

            user=userList.get();

        }

        System.out.println(user);

        return user;

    }  

    /**

     * 插入用户数据

     * @param user

     * @return

     * @throws Exception

     */

    public int saveUser(final User user) throws  Exception{

        int resRow=jdbcTemplate.update("INSERT INTO users(id,name,email) VALUES(NULL,?,?)",new Object[]{

           user.getName(),user.getEmail()

        });

        System.out.println("操作结果记录数：  "+resRow);

        return resRow;

    }  

    /**

     * 插入用户数据-防止sql注入

     * @param user

     * @return

     * @throws Exception

     */

    public int saveUserWithSafe(final User user) throws  Exception{

        int resRow=jdbcTemplate.update("INSERT INTO users(id,name,email) VALUES(NULL,?,?)", new PreparedStatementSetter() {

            @Override

            public void setValues(PreparedStatement ps) throws SQLException {

                ps.setString(,user.getName());

                ps.setString(,user.getEmail());

            }

        });

        System.out.println("操作结果记录数：  "+resRow);

        return resRow;

    }  

    /**

     * 插入用户数据-防止sql注入-可以返回该条记录的主键（注意需要指定主键）

     * @param user

     * @return

     * @throws Exception

     */

    @Transactional(rollbackFor=UserException.class)

    public int saveUserWithKey(final User user) throws  Exception{

        String sql="INSERT INTO users(id,name,email) VALUES(NULL,?,?)";

        KeyHolder keyHolder=new GeneratedKeyHolder();

        int resRow=jdbcTemplate.update(new PreparedStatementCreator() {

            @Override

            public PreparedStatement createPreparedStatement(Connection conn) throws SQLException {

                PreparedStatement ps=conn.prepareStatement(sql,new String[]{"id"}); //指定 id 为主键

                ps.setString(,user.getName());

                ps.setString(,user.getEmail());

                return ps;

            }

        },keyHolder);

        System.out.println("操作结果记录数：  "+resRow+" 主键: "+keyHolder.getKey());

        return Integer.parseInt(keyHolder.getKey().toString());

    }  

    /**

     * 更新用户信息

     * @param user

     * @return

     */

    public int updateUser(final User user) throws  Exception{

        String sql="update users set name=?,email=? where id=?";

        int resRow=jdbcTemplate.update(sql, new PreparedStatementSetter() {

            @Override

            public void setValues(PreparedStatement preparedStatement) throws SQLException {

                preparedStatement.setString(,user.getName());

                preparedStatement.setString(,user.getEmail());

                preparedStatement.setInt(,user.getId());

            }

        });

        System.out.println("操作结果记录数：  "+resRow);

        return resRow;

    }  

    /**

     * 删除用户

     * @param user

     * @return

     * @throws Exception

     */

    public int deleteUser(final User user) throws  Exception{

        int resRow=jdbcTemplate.update("DELETE FROM users WHERE id=?", new PreparedStatementSetter() {

            @Override

            public void setValues(PreparedStatement ps) throws SQLException {

                ps.setInt(,user.getId());

            }

        });

        System.out.println("操作结果记录数：  "+resRow);

        return resRow;

    }  

    /**

     * 根据用户名查找用户-用于判断用户是否存在

     * @param user

     * @return

     * @throws Exception

     */

    public User getUserByUserName(final User user) throws Exception{

        String sql="select id,name,email from users where name=?";

        List<User> queryList=jdbcTemplate.query(sql,new UserRowMapper(),new Object[]{user.getName()});

        if (queryList!=null && queryList.size()>){

            return queryList.get();

        }else{

            return null;

        }

    }  

    /**

     * 获取记录数

     * @return

     * @throws Exception

     */

    public Integer getCount() throws  Exception{

        String sql="select count(id) from users";

        //jdbcTemplate.getMaxRows();

        Integer total=jdbcTemplate.queryForObject(sql,Integer.class);

        System.out.println("操作结果记录数：  "+total);

        return total;

    }  

    //其他的像模糊查询之类的可以自己尝试查查 jdbcTemplate 的使用文档  

}  

/**

 * 行映射

 */

class UserRowMapper implements RowMapper<User>{  

    @Override

    public User mapRow(ResultSet resultSet, int i) throws SQLException {

        User user=new User();

        user.setId(resultSet.getInt("id"));

        user.setName(resultSet.getString("name"));

        user.setEmail(resultSet.getString("email"));

        return user;

    }  

}

代码以及相关的注释我已经写在里面了，个人觉得很清晰了，如果有啥问题，可以下面留言，或者后面提到的技术交流群交流。

接下来，当然是开发controller层，在这里，我主要开发rest服务接口，结果将以json的格式返回给发起请求的客户端（以postman进行模拟），下面是我的restController：

package com.example.controller;  

import com.example.DemoApplication;

import com.example.entity.User;

import com.example.repository.UserRepository;

import com.google.common.base.Strings;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.boot.test.context.SpringBootTest;

import org.springframework.web.bind.annotation.PathVariable;

import org.springframework.web.bind.annotation.RequestMapping;

import org.springframework.web.bind.annotation.RequestMethod;

import org.springframework.web.bind.annotation.RestController;  

import javax.servlet.http.HttpServletRequest;

import java.util.List;  

/**

 * Created by steadyjack on 2017/3/22.

 */

@SpringBootTest(classes = DemoApplication.class)

@RestController

@RequestMapping("/user")

public class UserController {  

    @Autowired

    private UserRepository userRepository;  

    /**

     * 用户列表

     * @return

     */

    @RequestMapping("/list")

    public List<User> listUser() {

        List<User> userList=null;

        try {

            userList=userRepository.getUserList();

        }catch (Exception e){

            System.out.println("异常信息:  "+e.getMessage());

        }

        return userList;

    }  

    /**

     * 根据id查询User实体

     * @param id

     * @return

     */

    @RequestMapping("/{id}")

    public User getUserById(@PathVariable Integer id){

        User user=null;

        try {

            user=userRepository.getUserById(id);

        }catch (Exception e){

            user=new User(,"admin","admin@sina.com");

            System.out.println("异常信息： "+e.getMessage());

        }

        return user;

    }  

    /**

     * 保存user实体

     * @param user

     * @return

     */

    @RequestMapping(value = "/save",method = RequestMethod.POST)

    public int insertUser(User user){

        int res=;

        try {

            res=userRepository.saveUser(user);

        }catch (Exception e){

            System.out.println("异常信息： "+e.getMessage());

        }

        return res;

    }  

    /**

     * 保存User实体-PreparedStatementSetter

     * @param user

     * @return

     */

    @RequestMapping(value = "/saveWithSafe",method = RequestMethod.POST)

    public int insertUserWithSafe(User user){

        int res=;

        try {

            res=userRepository.saveUserWithSafe(user);

        }catch (Exception e){

            System.out.println("异常信息： "+e.getMessage());

        }

        return res;

    }  

    /**

     * 保存user实体-PreparedStatementCreator、KeyHolder-保存实体后返回实体的主键

     * @param user

     * @return

     */

    @RequestMapping(value = "/saveWithKey",method = RequestMethod.POST)

    public int insertUserWithKey(User user){

        int res=;

        try {

            res=userRepository.saveUserWithKey(user);

        }catch (Exception e){

            System.out.println("异常信息： "+e.getMessage());

        }

        return res;

    }  

    /**

     * 根据id更新user实体

     * @param id

     * @param request

     * @return

     */

    @RequestMapping(value = "/update/{id}",method = RequestMethod.POST)

    public int updateUserWithId(@PathVariable Integer id,HttpServletRequest request){

        int res=;

        try {

            if (id!=null && !id.equals()){

                String name=request.getParameter("name");

                String email=request.getParameter("email");

                User updateUser=new User(id, Strings.isNullOrEmpty(name)?null:name,Strings.isNullOrEmpty(email)?null:email);

                res=userRepository.updateUser(updateUser);

            }

        }catch (Exception e){

            System.out.println("异常信息： "+e.getMessage());

        }

        return res;

    }  

    /**

     * 根据id删除user实体

     * @param id

     * @return

     */

    @RequestMapping("/delete/{id}")

    public int deleteUserById(@PathVariable Integer id){

        int res=;

        try {

            User deleteUser=userRepository.getUserById(id);

            res=userRepository.deleteUser(deleteUser);

        }catch (Exception e){

            System.out.println("异常信息： "+e.getMessage());

        }

        return res;

    }  

    /**

     * 根据name查询是否存在某个user实体

     * @param request

     * @return

     */

    @RequestMapping("/isExistUser")

    public Boolean isExistUser(HttpServletRequest request){

        Boolean res=false;

        try {

            String name=request.getParameter("name");

            User queryUser=new User(null,Strings.isNullOrEmpty(name)?null:name,null);

            User deleteUser=userRepository.getUserByUserName(queryUser);

            if (deleteUser!=null){

                res=true;

            }

        }catch (Exception e){

            System.out.println("异常信息： "+e.getMessage());

        }

        return res;

    }  

    /**

     * 查询user实体的总数

     * @return

     */

    @RequestMapping("/total")

    public Integer getTotal(){

        Integer res=;

        try {

            res=userRepository.getCount();

        }catch (Exception e){

            System.out.println("异常信息： "+e.getMessage());

        }

        return res;

    }  

}