Spring Boot整合jdbctemplate

jdbctemplate在之前的开发中用的还是比较多的，它和mybatis有点类似都是需要自己写sql语句，也是对jdbc做了封装，缺点也比较明显就是移植性有些差。但日常的中小型开发也够用，这次就来一个Spring Boot整合jdbctemplate实现增删改查的小例子

一  依赖的引入

<dependency>   <groupId>mysql</groupId>   <artifactId>mysql-connector-java</artifactId></dependency><dependency>  <groupId>org.springframework.boot</groupId>  <artifactId>spring-boot-starter-jdbc</artifactId></dependency>

二  配置文件

#DataBase DataSourcesspring.datasource.url = jdbc:mysql://localhost:3306/springbootspring.datasource.username = rootspring.datasource.password = 123456spring.datasource.driverClassName = com.mysql.jdbc.Driverspring.datasource.max-active=20spring.datasource.max-idle=8spring.datasource.min-idle=8spring.datasource.initial-size=10

三  实体类

package com.debug.pojo;public class UserInfo {private int id;private String name;private String address;public int getId() {return id;}public void setId(int id) {this.id = id;}public String getName() {return name;}public void setName(String name) {this.name = name;}public String getAddress() {return address;}public void setAddress(String address) {this.address = address;}}

四  数据库访问层DAO和service

package com.debug.dao;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.util.List;import javax.annotation.Resource;import org.springframework.jdbc.core.BeanPropertyRowMapper;import org.springframework.jdbc.core.JdbcTemplate;import org.springframework.jdbc.core.PreparedStatementCreator;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 com.debug.pojo.UserInfo;import com.mysql.jdbc.Statement;@Repositorypublic class UserDao {@Resourceprivate JdbcTemplate jdbcTemplate;public UserInfo createUser(UserInfo u) {String sql = "insert into user(name,address) values(?,?)";KeyHolder holder=new GeneratedKeyHolder();jdbcTemplate.update(new PreparedStatementCreator() {public PreparedStatement createPreparedStatement(Connection conn) throws SQLException {   PreparedStatement ps=conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);   ps.setString(1, u.getName());   ps.setString(2, u.getAddress());   return ps;}}, holder);int insertId=holder.getKey().intValue();u.setId(insertId);return u;}public void deleteUser(int id) {String sql="delete from user where id=?";jdbcTemplate.update(sql, new Object[] {id},new int[] {java.sql.Types.INTEGER});}public void updateUser(UserInfo u) {String sql="update user set name=? where id=?";jdbcTemplate.update(sql, new Object[] {u.getName(),u.getId()});}public List<UserInfo> queryUser(int id) {String sql="select * from user where id=?";//RowMapper<UserInfo> rowMapper = new BeanPropertyRowMapper<>(UserInfo.class);return jdbcTemplate.query(sql,new Object[] {id},new UserRowMapper());} class UserRowMapper implements RowMapper<UserInfo>{public UserInfo mapRow(ResultSet res, int arg1) throws SQLException {UserInfo u=new UserInfo();u.setId(res.getInt("id"));u.setName(res.getString("name"));u.setAddress(res.getString("address"));return u;}}}

package com.debug.service;import java.util.List;import javax.annotation.Resource;import org.springframework.stereotype.Service;import com.debug.dao.UserDao;import com.debug.pojo.UserInfo;@Servicepublic class UserService {    @Resource    private UserDao userDao;    public UserInfo createUser(UserInfo u) {return userDao.createUser(u);    }    public void deleteUser(int id) {    userDao.deleteUser(id);    }        public void updateUser(UserInfo u) {    userDao.updateUser(u);    }        public List<UserInfo> queryUser(int id){    return userDao.queryUser(id);    }}

五  控制器controller

package com.debug.controller;import java.util.List;import javax.annotation.Resource;import org.springframework.stereotype.Controller;import org.springframework.web.bind.annotation.RequestMapping;import org.springframework.web.bind.annotation.RestController;import com.debug.pojo.UserInfo;import com.debug.service.UserService;@RestController@RequestMapping("/user")public class UserController {@Resourceprivate UserService userService;@RequestMapping("/createUser")public UserInfo createUser() {UserInfo u=new UserInfo();u.setName("林志玲");u.setAddress("上海浦东");UserInfo u1=userService.createUser(u);//System.out.println(u1.getId());return u1;}@RequestMapping("/deleteUser")public String deleteUser() {userService.deleteUser(15);return "删除成功";}@RequestMapping("/updateUser")public String updateUser() {UserInfo u1=new UserInfo();u1.setId(16);u1.setName("Justin Timberlake");userService.updateUser(u1);return "修改成功";}@RequestMapping("/queryUser")public List<UserInfo> queryUser(){return userService.queryUser(16);}}

上面的几段代码难度比较大的主要在DAO里，主要是查询结果集的方法封装和数据保存返回主键的那个插入方法，关于这点如果不明白就百度一下