阅读全文，约 14 分钟
这是江帅帅的第014篇原创
Spring Boot 使用 JdbcTemplate
JdbcTemplate 主要为数据访问提供了模板方案，将数据库存取的工作进行简化。
案例：访问数据
1）编辑 pom.xml 文件
<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.nx</groupId> <artifactId>springbootdata</artifactId> <version>1.0-SNAPSHOT</version> <parent> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-parent</artifactId> <version>2.2.6.RELEASE</version> <relativePath/> </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> <!-- 添加spring-boot-starter-web模块依赖 --> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <!-- 添加spring-boot-starter-thymeleaf模块依赖 --> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-thymeleaf</artifactId> </dependency> <!-- 添加MySQL依赖 --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> </dependency> <!-- 添加JDBC依赖 --> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-jdbc</artifactId> </dependency> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <scope>test</scope> </dependency> </dependencies> </project>
2）编辑 application.properties 文件
#################### ### 数据源信息配置 ### #################### # 数据库地址 spring.datasource.url=jdbc:mysql://localhost:3306/springbootdata?characterEncoding=utf8&useSSL=false&serverTimezone=UTC&rewriteBatchedStatements=true # 用户名 spring.datasource.username=root # 密码 spring.datasource.password=1234 # 数据库驱动 spring.datasource.driverClassName=com.mysql.cj.jdbc.Driver # 指定连接池中最大的活跃连接数. spring.datasource.max-active=20 # 指定连接池最大的空闲连接数量. spring.datasource.max-idle=8 # 指定必须保持连接的最小值 spring.datasource.min-idle=8 # 指定启动连接池时，初始建立的连接数量 spring.datasource.initial-size=10
3）创建 User 持久类
public class User implements Serializable{ private static final long serialVersionUID = 1L; private int id ; private String loginName ; private String username ; private String password; // setXxx 和 getXxx 方法 }
4）创建 UserRepository 数据访问接口
@Repository public class UserRepository { // 注入JdbcTemplate模板对象 @Resource private JdbcTemplate jdbcTemplate; // 插入数据 public int insertUser(){ String sql = "insert into tb_user(login_name ,username ,passWord) " + "values (?,?,?),(?,?,?),(?,?,?)"; Object[] args = new Object[]{"s1","小黄","123456","s2","小绿","123456" ,"s3","小红","123456"}; // 参数1：插入数据的sql语句 // 参数2: 对应sql语句中占位符?的参数 return jdbcTemplate.update(sql, args); } // 根据userName查询数据 public User selectByUsername(String username) { String sql = "select * from tb_user where username = ?"; RowMapper<User> rowMapper = new BeanPropertyRowMapper<>(User.class); User user = jdbcTemplate.queryForObject(sql, new Object[] { username }, rowMapper); return user; } // 根据id查询数据 public User findUserById(int id) { String sql = "select * from tb_user where id=?"; RowMapper<User> rowMapper = new BeanPropertyRowMapper<>(User.class); return jdbcTemplate.queryForObject(sql, new Object[] { id }, rowMapper); } // 查询所有数据 public List<User> findAll() { String sql = "select * from tb_user"; // 申明结果集的映射rowMapper，将结果集的数据映射成User对象数据 RowMapper<User> rowMapper = new BeanPropertyRowMapper<>(User.class); return jdbcTemplate.query(sql, rowMapper); } // 根据id删除数据 public void delete(final Integer id) { String sql = "delete from tb_user where id=?"; jdbcTemplate.update(sql, new Object[] { id }); } // 修改数据 public void update(final User user) { String sql = "update tb_user set username=?, login_name=? where id=?"; jdbcTemplate.update(sql, new Object[] { user.getUsername(), user.getLoginName(), user.getId()}); } // 插入数据获取被插入数据的主键 public User insertGetKey(User user) { String sql = "insert into tb_user(username,login_name,password) values(?,?,?)"; // 定义插入数据后获取主键的对象 KeyHolder holder = new GeneratedKeyHolder(); jdbcTemplate.update(new PreparedStatementCreator() { @Override public PreparedStatement createPreparedStatement(Connection connection) throws SQLException { // 插入数据后，将被插入数据的主键返回回来 PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS); ps.setString(1, user.getUsername()); ps.setString(2, user.getLoginName()); ps.setString(3, user.getPassword()); return ps; } }, holder); // 获取被插入数据库的主键 然后注入到user对象中去 int newUserId = holder.getKey().intValue(); user.setId(newUserId); return user; } }
5）创建 UserService 业务层类
@Service public class UserService { // 注入UserRepository @Resource private UserRepository userRepository; public int insertUser(){ return userRepository.insertUser(); } public User selectByUsername(String username){ return userRepository.selectByUsername(username); } public List<User> findAll(){ return userRepository.findAll(); } public User insertGetKey(User user) { return userRepository.insertGetKey(user); } public void update(User user) { userRepository.update(user); } public void delete(Integer id) { userRepository.delete(id); } }
6）定义 UserController 控制器类
@RestController @RequestMapping("/user") public class UserController { @Resource private UserService userService; @RequestMapping("/insertUser") public String insertUser(){ return "插入数据["+userService.insertUser()+"]条"; } @RequestMapping("/insertGetKey") public User insertGetKey(User user) { return userService.insertGetKey(user); } @RequestMapping("/selectByUsername") public User selectByUserNm(String username){ return userService.selectByUsername(username); } @RequestMapping("/findAll") public List<User> findAll(){ return userService.findAll(); } @RequestMapping("/update") public void update(User user) { userService.update(user); } @RequestMapping("/delete") public void delete(Integer id) { userService.delete(id); } }
7）测试
http://localhost:8080/user/insertUser
