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

来源：互联网发布：怎么投诉淘宝网平台编辑：程序博客网时间：2024/06/06 06:37

简单入门了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-8spring.datasource.username=rootspring.datasource.password=123456spring.datasource.driver-class-name=com.mysql.jdbc.Driver#spring.datasource.driverClassName = com.mysql.jdbc.Driver

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

/*Navicat MySQL Data TransferSource Server         : localhostSource Server Version : 50625Source Host           : localhost:3306Source Database       : db_springbootTarget Server Type    : MYSQLTarget Server Version : 50625File Encoding         : 65001Date: 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 */@Repositorypublic 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服务器: