文章转载于 http://blog.youkuaiyun.com/u013871100/article/details/68925050
JDBC事务处理 https://www.oschina.net/code/snippet_574870_33828
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进行访问的):