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 DataSources
spring.datasource.url = jdbc:mysql://localhost:3306/springboot
spring.datasource.username = root
spring.datasource.password = 123456
spring.datasource.driverClassName = com.mysql.jdbc.Driver
spring.datasource.max-active=20
spring.datasource.max-idle=8
spring.datasource.min-idle=8
spring.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;

@Repository
public class UserDao {
	
	@Resource
	private 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;

@Service
public 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 {

	@Resource
	private 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里，主要是查询结果集的方法封装和数据保存返回主键的那个插入方法，关于这点如果不明白就百度一下