SpringBoot（四）：SpringBoot整合JdbcTemplate

r囧r小猫 2022-05-29 13:40 165阅读 0赞

> 个人其实偏向于使用类似于JdbcTemplate这种的框架，返回数据也习惯于接受Map/List形式，而不是转化成对象，一是前后台分离转成json方便，另外是返回数据格式，数据字段可以通过SQL控制，而不是返回整个对象字段数据，或者通过VO方式。当然更多人习惯于采用Bean形式，所以这里也同样使用Bean.

### 一、数据准备 ###

CREATE TABLE `tb_user` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID', `username` varchar(50) NOT NULL COMMENT '用户名', `age` int(11) NOT NULL COMMENT '年龄', `ctm` datetime NOT NULL COMMENT '创建时间', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8

INSERT INTO `db_test`.`tb_user` (`username`, `age`, `ctm`) VALUES('张三', '18', NOW()) ;
    INSERT INTO `db_test`.`tb_user` (`username`, `age`, `ctm`) VALUES('李四', '20', NOW()) ;
    INSERT INTO `db_test`.`tb_user` (`username`, `age`, `ctm`) VALUES('王五', '19', NOW()) ;

### 二、引入依赖 ###

<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>

另外web依赖也需要，因为我们采用MVC模式。

<dependency>
    	<groupId>org.springframework.boot</groupId>
    	<artifactId>spring-boot-starter-web</artifactId>
    </dependency>

### 三、数据库配置文件 ###

一如既往，我们采用yaml文件配置，当然properties文件也是一样。

> 注意点，SpringBoot默认采用tomcat-jdbc连接池，如果需要C3P0，DBCP，Druid等作为连接池，需要加入相关依赖以及配置，这里不作说明，采用默认配置即可。

spring:
      datasource:
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://localhost:3306/db_user
        username: root
        password: root

### 四、代码 ###

项目结构如下：

![这里写图片描述][SouthEast]

实体类User.class

package cn.saytime.bean;
    
    import java.util.Date;
    
    /** * @ClassName cn.saytime.bean.User * @Description * @date 2017-07-04 22:47:28 */
    public class User {
    
    	private int id;
    	private String username;
    	private int age;
    	private Date ctm;
    
    	public User() {
    	}
    
    	public User(String username, int age) {
    		this.username = username;
    		this.age = age;
    		this.ctm = new Date();
    	}
    
    	// Getter、Setter
    }

UserDao.class

package cn.saytime.dao;
    
    import cn.saytime.bean.User;
    
    import java.util.List;
    
    /** * @ClassName cn.saytime.dao.UserDao * @Description * @date 2017-07-04 22:48:45 */
    public interface UserDao {
    
    	User getUserById(Integer id);
    
    	public List<User> getUserList();
    
    	public int add(User user);
    
    	public int update(Integer id, User user);
    
    	public int delete(Integer id);
    }

UserDaoImpl.class

package cn.saytime.dao.impl;
    
    import cn.saytime.bean.User;
    import cn.saytime.dao.UserDao;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.jdbc.core.BeanPropertyRowMapper;
    import org.springframework.jdbc.core.JdbcTemplate;
    import org.springframework.stereotype.Repository;
    
    import java.util.Date;
    import java.util.List;
    
    /** * @ClassName cn.saytime.dao.impl.UserDaoImpl * @Description * @date 2017-07-04 22:50:07 */
    @Repository
    public class UserDaoImpl implements UserDao {
    
    	@Autowired
    	private JdbcTemplate jdbcTemplate;
    
    	@Override
    	public User getUserById(Integer id) {
    		List<User> list = jdbcTemplate.query("select * from tb_user where id = ?", new Object[]{id}, new BeanPropertyRowMapper(User.class));
    		if(list!=null && list.size()>0){
    			return list.get(0);
    		}else{
    			return null;
    		}
    	}
    
    	@Override
    	public List<User> getUserList() {
    		List<User> list = jdbcTemplate.query("select * from tb_user", new Object[]{}, new BeanPropertyRowMapper(User.class));
    		if(list!=null && list.size()>0){
    			return list;
    		}else{
    			return null;
    		}
    	}
    
    	@Override
    	public int add(User user) {
    		return jdbcTemplate.update("insert into tb_user(username, age, ctm) values(?, ?, ?)",
    				user.getUsername(),user.getAge(), new Date());
    
    	}
    
    	@Override
    	public int update(Integer id, User user) {
    		return jdbcTemplate.update("UPDATE tb_user SET username = ? , age = ? WHERE id=?",
    				user.getUsername(),user.getAge(), id);
    	}
    
    	@Override
    	public int delete(Integer id) {
    		return jdbcTemplate.update("DELETE from tb_user where id = ? ",id);
    	}
    
    }

UserService.class

package cn.saytime.service;
    
    import cn.saytime.bean.User;
    import org.springframework.stereotype.Service;
    
    import java.util.List;
    
    /** * @ClassName cn.saytime.service.UserService * @Description * @date 2017-07-04 22:49:05 */
    public interface UserService {
    
    	User getUserById(Integer id);
    
    	public List<User> getUserList();
    
    	public int add(User user);
    
    	public int update(Integer id, User user);
    
    	public int delete(Integer id);
    }

UserServiceimpl.class

package cn.saytime.service.impl;
    
    import cn.saytime.bean.User;
    import cn.saytime.dao.UserDao;
    import cn.saytime.service.UserService;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Service;
    
    import java.util.List;
    
    /** * @ClassName cn.saytime.service.impl.UserServiceImpl * @Description * @date 2017-07-04 22:49:27 */
    @Service public class UserServiceImpl implements UserService {
    
    	@Autowired private UserDao userDao;
    
    	@Override public User getUserById(Integer id) {
    		return userDao.getUserById(id);
    	}
    
    	@Override public List<User> getUserList() {
    		return userDao.getUserList();
    	}
    
    	@Override public int add(User user) {
    		return userDao.add(user);
    	}
    
    	@Override public int update(Integer id, User user) {
    		return userDao.update(id, user);
    	}
    
    	@Override public int delete(Integer id) {
    		return userDao.delete(id);
    	}
    }

JsonResult.class 通用json返回类

package cn.saytime.bean;
    
    public class JsonResult {
    
    	private String status = null;
    
    	private Object result = null;
    
    	public JsonResult status(String status) {
    		this.status = status;
    		return this;
    	}
    
    	// Getter Setter
    }

UserController.class（Restful风格）

package cn.saytime.web;
    
    import cn.saytime.bean.JsonResult;
    import cn.saytime.bean.User;
    import cn.saytime.service.UserService;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.http.HttpStatus;
    import org.springframework.http.ResponseEntity;
    import org.springframework.web.bind.annotation.PathVariable;
    import org.springframework.web.bind.annotation.RequestBody;
    import org.springframework.web.bind.annotation.RequestMapping;
    import org.springframework.web.bind.annotation.RequestMethod;
    import org.springframework.web.bind.annotation.RequestParam;
    import org.springframework.web.bind.annotation.RestController;
    
    import java.util.List;
    
    /** * @ClassName cn.saytime.web.UserController * @Description * @date 2017-07-04 22:46:14 */
    @RestController
    public class UserController {
    
    	@Autowired
    	private UserService userService;
    
    	/** * 根据ID查询用户 * @param id * @return */
    	@RequestMapping(value = "user/{id}", method = RequestMethod.GET)
    	public ResponseEntity<JsonResult> getUserById (@PathVariable(value = "id") Integer id){
    		JsonResult r = new JsonResult();
    		try {
    			User user = userService.getUserById(id);
    			r.setResult(user);
    			r.setStatus("ok");
    		} catch (Exception e) {
    			r.setResult(e.getClass().getName() + ":" + e.getMessage());
    			r.setStatus("error");
    			e.printStackTrace();
    		}
    		return ResponseEntity.ok(r);
    	}
    
    	/** * 查询用户列表 * @return */
    	@RequestMapping(value = "users", method = RequestMethod.GET)
    	public ResponseEntity<JsonResult> getUserList (){
    		JsonResult r = new JsonResult();
    		try {
    			List<User> users = userService.getUserList();
    			r.setResult(users);
    			r.setStatus("ok");
    		} catch (Exception e) {
    			r.setResult(e.getClass().getName() + ":" + e.getMessage());
    			r.setStatus("error");
    			e.printStackTrace();
    		}
    		return ResponseEntity.ok(r);
    	}
    
    	/** * 添加用户 * @param user * @return */
    	@RequestMapping(value = "user", method = RequestMethod.POST)
    	public ResponseEntity<JsonResult> add (@RequestBody User user){
    		JsonResult r = new JsonResult();
    		try {
    			int orderId = userService.add(user);
    			if (orderId < 0) {
    				r.setResult(orderId);
    				r.setStatus("fail");
    			} else {
    				r.setResult(orderId);
    				r.setStatus("ok");
    			}
    		} catch (Exception e) {
    			r.setResult(e.getClass().getName() + ":" + e.getMessage());
    			r.setStatus("error");
    
    			e.printStackTrace();
    		}
    		return ResponseEntity.ok(r);
    	}
    
    	/** * 根据id删除用户 * @param id * @return */
    	@RequestMapping(value = "user/{id}", method = RequestMethod.DELETE)
    	public ResponseEntity<JsonResult> delete (@PathVariable(value = "id") Integer id){
    		JsonResult r = new JsonResult();
    		try {
    			int ret = userService.delete(id);
    			if (ret < 0) {
    				r.setResult(ret);
    				r.setStatus("fail");
    			} else {
    				r.setResult(ret);
    				r.setStatus("ok");
    			}
    		} catch (Exception e) {
    			r.setResult(e.getClass().getName() + ":" + e.getMessage());
    			r.setStatus("error");
    
    			e.printStackTrace();
    		}
    		return ResponseEntity.ok(r);
    	}
    
    	/** * 根据id修改用户信息 * @param user * @return */
    	@RequestMapping(value = "user/{id}", method = RequestMethod.PUT)
    	public ResponseEntity<JsonResult> update (@PathVariable("id") Integer id, @RequestBody User user){
    		JsonResult r = new JsonResult();
    		try {
    			int ret = userService.update(id, user);
    			if (ret < 0) {
    				r.setResult(ret);
    				r.setStatus("fail");
    			} else {
    				r.setResult(ret);
    				r.setStatus("ok");
    			}
    		} catch (Exception e) {
    			r.setResult(e.getClass().getName() + ":" + e.getMessage());
    			r.setStatus("error");
    
    			e.printStackTrace();
    		}
    		return ResponseEntity.ok(r);
    	}
    
    }

### 五、测试 ###

GET [http://localhost:8080/users][http_localhost_8080_users] 获取用户列表 ![这里写图片描述][SouthEast 1]

GET [http://localhost:8080/user/\{id\}][http_localhost_8080_user_id] 根据ID获取用户信息 ![这里写图片描述][SouthEast 2]

POST [http://localhost:8080/user][http_localhost_8080_user] 添加用户（注意提交格式以及内容） ![这里写图片描述][SouthEast 3]

PUT [http://localhost:8080/user/\{id\}][http_localhost_8080_user_id] 根据ID修改用户信息 ![这里写图片描述][SouthEast 4]

再次查询所有用户信息  
![这里写图片描述][SouthEast 5]

DELETE [http://localhost:8080/user/\{id\}][http_localhost_8080_user_id] 根据ID删除用户

![这里写图片描述][SouthEast 6]

最终用户数据

![这里写图片描述][SouthEast 7]

测试结果通过，ok

[SouthEast]: /images/20220529/27fb8f52293b4b30bebd15b905e24adf.png
[http_localhost_8080_users]: http://localhost:8080/users
[SouthEast 1]: /images/20220529/2433dba150b64c42bbd454ace706394e.png
[http_localhost_8080_user_id]: http://localhost:8080/user/%7Bid%7D
[SouthEast 2]: /images/20220529/2ba5ee821044481e914af0d35ce1a5f8.png
[http_localhost_8080_user]: http://localhost:8080/user
[SouthEast 3]: /images/20220529/1bff16309cad48faa365c4c1c7cc9222.png
[SouthEast 4]: /images/20220529/08f055294cd447ab867b883fe6ab10b2.png
[SouthEast 5]: /images/20220529/c2b357f3f8ac45a6a626f3c6646c24d7.png
[SouthEast 6]: /images/20220529/e6658629163948f293de041551f0f2a1.png
[SouthEast 7]: /images/20220529/e6f70a25af0e4acdacb10c11b66c20f1.png