spring boot学习系列:spring boot与jdbcTemplate的整合案例

spring boot学习系列:spring boot与jdbcTemplate的整合案例

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

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

     可以参照上一篇博文,参考参考如何建立一个spring boot项目,至于在选择依赖的配置时候,可以参考我下面贴出的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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>

	<groupId>com.example</groupId>
	<artifactId>demo</artifactId>
	<version>0.0.1-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.2.RELEASE</version>
		<relativePath/> <!-- lookup parent from repository -->
	</parent>

	<properties>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
		<project.reporting.outputEncoding>UTF-8</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端口号,数据库链接相关信息:

 

###### 设置tomcat访问端口号 ######
server.port=8088

###### 设置数据源 ######
spring.datasource.url=jdbc:mysql://localhost:3306/db_springboot?autoReconnect=true&useUnicode=true&characterEncoding=utf-8
spring.datasource.username=root
spring.datasource.password=123456
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=0;

-- ----------------------------
-- Table structure for users
-- ----------------------------
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of users
-- ----------------------------
INSERT INTO `users` VALUES ('1', 'linsen', 'linsen@126.com');
INSERT INTO `users` VALUES ('2', 'sam', 'sam@qq.com');
INSERT INTO `users` VALUES ('3', 'debug', 'debug@sina.com');
INSERT INTO `users` VALUES ('4', '杰克', '杰克@sina.com');
INSERT INTO `users` VALUES ('5', '张三', '张三@sina.com');
INSERT INTO `users` VALUES ('6', '李四', '李四@sina.com');
INSERT INTO `users` VALUES ('7', '王五', '王五@sina.com');
INSERT INTO `users` VALUES ('8', '王五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(0);
        }
        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(1,user.getName());
                ps.setString(2,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(1,user.getName());
                ps.setString(2,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(1,user.getName());
                preparedStatement.setString(2,user.getEmail());
                preparedStatement.setInt(3,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(1,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()>0){
            return queryList.get(0);
        }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(1,"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=1;
        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=1;
        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=1;
        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=1;
        try {
            if (id!=null && !id.equals(0)){
                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=1;
        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=0;
        try {
            res=userRepository.getCount();
        }catch (Exception e){
            System.out.println("异常信息: "+e.getMessage());
        }
        return res;
    }

}


    至此已经开发完毕了,你可以直接run DemoApplication类,然后在浏览器测试访问,也可以在postman发起访问!下面我才用一键式部署到我的本地tomcat服务器:

 

   

 

 

    完了之后,(当然啦,你也可以jar -jar将你的spring boot打包为jar项目,然后$ java –jar E:\IDEA_Workspace\springbootJDBC\target\demo-0.0.1-SNAPSHOT.jar 也可以直接跑起来

    好了,现在默认就是启动了这个sb项目,下面就开始访问测试各个服务(开头都以 127.0.0.1:8088/)

    1,首先是获取用户列表:

    

   2、接着是查询id=3 的user实体:

 

   3、将id=3的实体删除(1:代表操作的记录数-说明已经成功删除)

 

  4、再次查询出来:

 

  5、增加一个user实体:

 

  6、检验一下是否增加该实体成功:

 

    7、更新id=11的实体的相关数据(1:代表更新成功)

 

   8、检验一下是否更新数据成功!

 

    好了,整合完毕!下面提供postman的下载地址(当然了,上面的那些地址其实也可以通过browser进行访问的):

 

    如果有任何问题可以下面留言!如果想交流java方方面面的开源的技术,可以加入群: 583522159  我叫debug,个人QQ:1948831260,我也在学习中,希望可以一同进步!

评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

修罗debug

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值