SpringBoot(四):SpringBoot整合JdbcTemplate

个人其实偏向于使用类似于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()) ;

二、引入依赖

<!-- jdbcTemplate -->
<dependency>
	<groupId>org.springframework.boot</groupId>
	<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>

<!-- MySQL连接 -->
<dependency>
	<groupId>mysql</groupId>
	<artifactId>mysql-connector-java</artifactId>
	<scope>runtime</scope>
</dependency>

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

<!-- Add typical dependencies for a web application -->
<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

四、代码

项目结构如下:

这里写图片描述

实体类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 获取用户列表 这里写图片描述

GET http://localhost:8080/user/{id} 根据ID获取用户信息 这里写图片描述

POST http://localhost:8080/user 添加用户(注意提交格式以及内容) 这里写图片描述

PUT http://localhost:8080/user/{id} 根据ID修改用户信息 这里写图片描述

再次查询所有用户信息
这里写图片描述

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

 这里写图片描述

最终用户数据

 这里写图片描述

测试结果通过,ok





评论 9
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值