一、准备工作
1.1 创建Spring工程
在IntelliJ IDEA中新建Spring工程，选择Web、MyBatis、MySQL等依赖。
配置文件application.properties
spring.datasource.url=jdbc:mysql://localhost:3306/springbootdb?useUnicode=true&characterEncoding=utf8
spring.datasource.username=root
spring.datasource.password=123
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
mybatis.mapper-locations=classpath:mapper/*xml
server.port=8084
server.servlet.context-path=/singlecandy
1.2 创建数据库和表
在SQLyog中，执行以下SQL语句，创建DataBase和Table：
/*
SQLyog Ultimate v12.08 (64 bit)
MySQL - 5.5.56 : Database - springbootdb
*********************************************************************
*/
/*!40101 SET NAMES utf8 */;
/*!40101 SET SQL_MODE=''*/;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`springbootdb` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `springbootdb`;
/*Table structure for table `address` */
DROP TABLE IF EXISTS `address`;
CREATE TABLE `address` (
`aid` int(11) NOT NULL,
`city` varchar(16) DEFAULT NULL,
PRIMARY KEY (`aid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*Data for the table `address` */
insert into `address`(`aid`,`city`) values (1,'北京'),(2,'上海'),(3,'广州'),(4,'深圳'),(5,'杭州');
/*Table structure for table `company` */
DROP TABLE IF EXISTS `company`;
CREATE TABLE `company` (
`cid` int(11) NOT NULL,
`cname` varchar(16) DEFAULT NULL,
`address_id` int(11) DEFAULT NULL,
PRIMARY KEY (`cid`),
KEY `address_id` (`address_id`),
CONSTRAINT `company_ibfk_1` FOREIGN KEY (`address_id`) REFERENCES `address` (`aid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*Data for the table `company` */
insert into `company`(`cid`,`cname`,`address_id`) values (0,'admin',4),(1,'阿里巴巴',5),(2,'腾讯',4),(3,'百度',1),(4,'京东',1),(5,'华为',4),(6,'网易',3),(7,'斗鱼',NULL),(8,'大疆',4);
/*Table structure for table `user` */
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`uid` int(11) NOT NULL,
`username` varchar(16) DEFAULT NULL,
`password` varchar(16) DEFAULT NULL,
`company_id` int(11) DEFAULT NULL,
PRIMARY KEY (`uid`),
KEY `company_id` (`company_id`),
CONSTRAINT `user_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `company` (`cid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*Data for the table `user` */
insert into `user`(`uid`,`username`,`password`,`company_id`) values (0,'admin','123',0),(1,'马云','123',1),(2,'马化腾','123',2),(3,'李彦宏','123',3),(4,'刘强东','123',4),(5,'任正非','123',5),(6,'丁磊','123',6),(7,'张小龙','123',2),(8,'张三','123',8),(9,'李四',NULL,NULL);
/*!40101 SET [email protected]_SQL_MODE */;
/*!40014 SET [email protected]_FOREIGN_KEY_CHECKS */;
/*!40014 SET [email protected]_UNIQUE_CHECKS */;
/*!40111 SET [email protected]_SQL_NOTES */;
表结构和表关系如下图：
二、代码编写
2.1 实体类
User.java
package com.jake.singlecandy.pojo;
import lombok.Data;
@Data
public class User {
private Integer uid;
private String username;
private String password;
}
UserInfo.java
package com.jake.singlecandy.pojo;
import lombok.Data;
import java.util.List;
@Data
public class UserInfo {
private List<User> users;
private String cname;
private String city;
}
2.2 持久层
UserDao.java
package com.jake.singlecandy.dao;
import com.jake.singlecandy.pojo.User;
import com.jake.singlecandy.pojo.UserInfo;
import org.apache.ibatis.annotations.Param;
import java.util.List;
public interface UserDao {
List<User> findAllUsers();
List<User> findAllUserInfo();
User findUserById(Integer uid);
UserInfo findUserInfoById(Integer uid);
List<UserInfo> findUserInfoByUsernameAndCname(@Param("username") String username, @Param("cname") String cname);
User findUserByName(@Param("username") String username);
void insertUser(User user);
void updateUser(User user);
void deleteUserById(Integer uid);
}
映射文件：UserMapper.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.jake.singlecandy.dao.UserDao">
<resultMap id="UserBaseResultMap" type="com.jake.singlecandy.pojo.User">
<id property="uid" column="uid"></id>
<result property="username" column="username"></result>
<result property="password" column="password"></result>
</resultMap>
<resultMap id="UserInfoBaseResultMap" type="com.jake.singlecandy.pojo.UserInfo">
<result property="city" column="city"></result>
<result property="cname" column="cname"></result>
<collection property="users" resultMap="UserBaseResultMap"></collection>
</resultMap>
<parameterMap id="User" type="com.jake.singlecandy.pojo.User"></parameterMap>
<select id="findAllUsers" resultMap="UserBaseResultMap">
select *
from user
</select>
<select id="findAllUserInfo" resultMap="UserInfoBaseResultMap">
SELECT *
FROM
USER AS u
LEFT JOIN company AS c
ON u.`company_id` = c.`cid`
LEFT JOIN address AS a
ON c.`address_id` = a.`aid`
</select>
<select id="findUserById" parameterType="Integer" resultMap="UserBaseResultMap">
SELECT *
FROM
USER
WHERE uid = #{uid}
</select>
<select id="findUserInfoById" parameterType="Integer" resultMap="UserInfoBaseResultMap">
SELECT *
FROM
USER AS u
LEFT JOIN company AS c
ON u.`company_id` = c.`cid`
LEFT JOIN address AS a
ON c.`address_id` = a.`aid`
WHERE u.`uid` = #{uid}
</select>
<select id="findUserInfoByUsernameAndCname" resultMap="UserInfoBaseResultMap">
SELECT
*
FROM
USER AS u
LEFT JOIN company AS c
ON u.`company_id` = c.`cid`
LEFT JOIN address AS a
ON c.`address_id` = a.`aid`
<where>
<if test="username != null">
u.username = #{username}
</if>
<if test="cname != null">
AND c.cname = #{cname}
</if>
</where>
</select>
<select id="findUserByName" resultMap="UserBaseResultMap">
SELECT uid, username, password
FROM
user
WHERE username = #{username}
</select>
<insert id="insertUser" parameterMap="User" useGeneratedKeys="true" keyProperty="id">
insert into
user (uid, username)
values
(#{uid}, #{username})
</insert>
<update id="updateUser" parameterMap="User">
update
user
set
username = #{username}
where
uid = #{uid}
</update>
<delete id="deleteUserById" parameterType="Integer">
delete from
user
where
uid = #{uid}
</delete>
</mapper>
2.3 业务层
接口定义方法 UserService.java
package com.jake.singlecandy.service;
import com.jake.singlecandy.pojo.User;
import com.jake.singlecandy.pojo.UserInfo;
import java.util.List;
public interface UserService {
List<User> findAllUsers();
List<User> findAllUserInfo();
User findUserById(Integer uid);
UserInfo findUserInfoById(Integer uid);
List<UserInfo> findUserInfoByUsernameAndCname(String username, String cname);
void createUser(User user);
void updateUser(User user);
void removeUserById(Integer uid);
}
实现类UserServiceImpl.java
package com.jake.singlecandy.service.impl;
import com.jake.singlecandy.dao.UserDao;
import com.jake.singlecandy.pojo.User;
import com.jake.singlecandy.pojo.UserInfo;
import com.jake.singlecandy.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
@Service("userService")
public class UserServiceImpl implements UserService {
@Autowired
private UserDao userDao;
@Override
public List<User> findAllUsers() {
return userDao.findAllUsers();
}
@Override
public List<User> findAllUserInfo() {
return userDao.findAllUserInfo();
}
@Override
public User findUserById(Integer uid) {
return userDao.findUserById(uid);
}
@Override
public UserInfo findUserInfoById(Integer uid) {
return userDao.findUserInfoById(uid);
}
@Override
public List<UserInfo> findUserInfoByUsernameAndCname(String username, String cname) {
return userDao.findUserInfoByUsernameAndCname(username, cname);
}
@Override
public void createUser(User user) {
userDao.insertUser(user);
}
@Override
public void updateUser(User user) {
userDao.updateUser(user);
}
@Override
public void removeUserById(Integer uid) {
userDao.deleteUserById(uid);
}
}
2.4 控制层
UserController.java
package com.jake.singlecandy.controller;
import com.jake.singlecandy.pojo.User;
import com.jake.singlecandy.pojo.UserInfo;
import com.jake.singlecandy.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import java.util.List;
@RestController
@RequestMapping("users")
public class UserController {
@Autowired
private UserService userService;
/**
* 查找所有用户
* @return
*/
@GetMapping
public List<User> findAllUsers() {
return userService.findAllUsers();
}
/**
* 查找所有用户信息
* @return
*/
@GetMapping("info")
public List<User> findAllUserInfo() {
return userService.findAllUserInfo();
}
/**
* 通过id查找用户
* @param uid
* @return
*/
@GetMapping("{uid}")
public User findUserById(@PathVariable Integer uid) {
return userService.findUserById(uid);
}
/**
* 通过id查找用户信息
* @param uid
* @return
*/
@GetMapping("info/{uid}")
public UserInfo findUserInfoById(@PathVariable Integer uid) {
return userService.findUserInfoById(uid);
}
/**
* 通过用户名和公司名查找用户
* @param username
* @param cname
* @return
*/
@GetMapping("info/{cname}/{username}")
public List<UserInfo> findUserInfoByUsernameAndCname(@PathVariable String username, @PathVariable String cname) {
return userService.findUserInfoByUsernameAndCname(username, cname);
}
/**
* 新增用户
* @param user
* @return
*/
@PostMapping
public String createUser(@RequestBody User user) {
if (user != null) {
userService.createUser(user);
return "添加用户成功";
} else {
return "添加用户失败";
}
}
/**
* 修改用户
* @param user
* @return
*/
@PutMapping
public String updateUser(@RequestBody User user) {
User userInDb = userService.findUserById(user.getUid());
if (userInDb != null) {
userService.updateUser(user);
return "更新用户成功";
} else {
return "更新用户失败";
}
}
/**
* 删除用户
* @param uid
* @return
*/
@DeleteMapping("{uid}")
public String removeUserById(@PathVariable Integer uid) {
User user = userService.findUserById(uid);
if (user == null) {
return "数据库中不存在id为" + uid + "的用户";
} else {
userService.removeUserById(uid);
return "删除成功";
}
}
}
三、前端测试
3.1 查找用户
HTTP请求方式选为GET
localhost:8084/singlecandy/users
localhost:8084/singlecandy/users/info
localhost:8084/singlecandy/users/1
localhost:8084/singlecandy/users/info/admin.com/admin
3.2 新增用户
HTTP请求方式选为POST，请求头选为Content-Type：application/json
localhost:8084/singlecandy/users/
{
"username": "Jason Tang",
"uid": 10
}
3.3 更新用户
HTTP请求方式选为PUT，请求头选为Content-Type：application/json
localhost:8084/singlecandy/users/
{
"username": "Jason.Tang",
"uid": 10
}
3.4 删除用户
HTTP请求方式选为DELETE
localhost:8084/singlecandy/users/10
原创文章，作者：ItWorker，如若转载，请注明出处：https://blog.ytso.com/tech/pnotes/19605.html