简单入门了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.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端口号,数据库链接相关信息:
可以参照上一篇博文,参考参考如何建立一个spring boot项目,至于在选择依赖的配置时候,可以参考我下面贴出的pom.xml:
###### 设置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进行访问的)