spring boot开发restful API入门（二）简单的操作mysql，实现增删改查

一、工程整体结构（文件的结构大概如下图，多余的文件请忽略）：

二、pom.xml

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>

  <groupId>com.gyd</groupId>
  <artifactId>moneyCom</artifactId>
  <version>0.0.1-SNAPSHOT</version>
  <packaging>jar</packaging>

  <name>moneyCom</name>
  <url>http://maven.apache.org</url>

  <properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
  <jackson.version>2.8.11</jackson.version>
  </properties>
<parent>  
        <groupId>org.springframework.boot</groupId>  
        <artifactId>spring-boot-starter-parent</artifactId>  
        <version>1.5.3.RELEASE</version>  
    </parent>
  
  <dependencies> 
	    
	    
        <!-- Compile -->  
        <dependency>  
            <groupId>org.springframework.boot</groupId>  
            <artifactId>spring-boot-starter-web</artifactId>  
        </dependency>  
  
  		<!-- 使用的数据库是MySql -->
        <dependency>  
            <groupId>mysql</groupId>  
            <artifactId>mysql-connector-java</artifactId>  
        </dependency>  
  
  		<!-- 使用JDBC访问数据库 -->
        <dependency>    
            <groupId>org.springframework.boot</groupId>  
            <artifactId>spring-boot-starter-jdbc</artifactId>  
        </dependency>  
  
  		<!-- 使用jpa处理数据 -->
        <dependency>  
            <groupId>org.springframework.boot</groupId>  
            <artifactId>spring-boot-starter-data-jpa</artifactId>  
        </dependency>  
  
         <dependency>  
            <groupId>com.h2database</groupId>  
            <artifactId>h2</artifactId>  
            <scope>runtime</scope>  
        </dependency>  
    <dependency>
      <groupId>com.fasterxml.jackson.core</groupId>
      <artifactId>jackson-core</artifactId>
      <version>${jackson.version}</version>
    </dependency>
    <dependency>
      <groupId>com.fasterxml.jackson.core</groupId>
      <artifactId>jackson-databind</artifactId>
      <version>${jackson.version}</version>
    </dependency>
        <!-- Test -->   
        <dependency>   
            <groupId>org.springframework.boot</groupId>   
            <artifactId>spring-boot-starter-test</artifactId>   
            <scope>test</scope>   
        </dependency>   
      <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>3.8.1</version>
      <scope>test</scope>
    </dependency>
  </dependencies>  
  
  
  <build>  
        <plugins>   
            <plugin>  
            	<!-- jar打包用 -->  
                <groupId>org.springframework.boot</groupId>   
                <artifactId>spring-boot-maven-plugin</artifactId>   
            </plugin>   
            <plugin>   
                <groupId>org.apache.maven.plugins</groupId>   
                <artifactId>maven-surefire-plugin</artifactId>   
            </plugin>   
        </plugins>   
    	<finalName>mono.localhost</finalName>  
  </build>


</project>

三、application.properties

spring.datasource.url=jdbc:mysql://localhost:3306/springdatajpa
spring.datasource.username=root
spring.datasource.password=
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.max-idle=10
spring.datasource.max-wait=10000
spring.datasource.min-idle=5
spring.datasource.initial-size=5

########################################################

### JPA持久化配置

########################################################

# 指定数据库的类型

spring.jpa.database = MYSQL

# 指定是否需要在日志中显示sql语句

spring.jpa.show-sql = true

# 指定自动创建|更新|验证数据库表结构等配置，配置成update

# 表示如果数据库中存在持久化类对应的表就不创建，不存在就创建对应的表

spring.jpa.hibernate.ddl-auto = update

# Naming strategy

# 指定命名策略

spring.jpa.hibernate.naming-strategy = org.hibernate.cfg.ImprovedNamingStrategy

# 指定数据库方言

spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL5Dialect

四、数据模型 UserInfo.java

package com.gyd.moneyCom.bean;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;
import javax.validation.constraints.NotNull;
 
@Entity
@Table(name="t_user")

public class UserInfo {
	
	@Id
	@GeneratedValue(strategy = GenerationType.AUTO)
	private int id;
	@NotNull
	private String name;
	
	private String password;
	
	private String salt;
	
	private String role;
 
	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 getPassword() {
		return password;
	}
 
	public void setPassword(String password) {
		this.password = password;
	}
 
	public String getSalt() {
		return salt;
	}
 
	public void setSalt(String salt) {
		this.salt = salt;
	}
 
	public String getRole() {
		return role;
	}
 
	public void setRole(String role) {
		this.role = role;
	}

}

五、数据访问接口UserInfoRepository.java

package com.gyd.moneyCom.repository;

import java.util.List;

import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;

import com.gyd.moneyCom.bean.UserInfo;

public interface UserInfoRepository extends CrudRepository<UserInfo, Integer>{
	
	UserInfo findUserInfoById(int id);
	List<UserInfo> findUserInfoByRole(String role);
	
	//传参
	@Query(value = "select * from t_user limit ?1", nativeQuery =true)
	List<UserInfo> findAllUsersByCount(int count);

}

 六、UserInfoController.java 用户信息的增删改查操作

package com.gyd.moneyCom.controller;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
 
import com.gyd.moneyCom.bean.UserInfo;
import com.gyd.moneyCom.repository.UserInfoRepository;
import com.gyd.moneyCom.utils.ResultMsg;
import com.gyd.moneyCom.utils.ResultStatusCode;

@RestController
@RequestMapping("userCtrl")
public class UserInfoController {

	@Autowired
	private UserInfoRepository userRepositoy;
	
	@RequestMapping("getuser")
	public Object getUser(int id)
	{
		UserInfo userEntity = userRepositoy.findUserInfoById(id);
		ResultMsg resultMsg = new ResultMsg(ResultStatusCode.OK.getErrcode(), ResultStatusCode.OK.getErrmsg(), userEntity);
		return resultMsg;
	}
	
	@RequestMapping("getusers")
	public Object getUsers(String role)
	{
		List<UserInfo> userEntities = userRepositoy.findUserInfoByRole(role);
		ResultMsg resultMsg = new ResultMsg(ResultStatusCode.OK.getErrcode(), ResultStatusCode.OK.getErrmsg(), userEntities);
		return resultMsg;
	}
	
	@Modifying
	@RequestMapping("adduser")
	public Object addUser(@RequestBody UserInfo userEntity)
	{
		userRepositoy.save(userEntity);
		ResultMsg resultMsg = new ResultMsg(ResultStatusCode.OK.getErrcode(), ResultStatusCode.OK.getErrmsg(), userEntity);
		return resultMsg;
	}
	
	@Modifying
	@RequestMapping("updateuser")
	public Object updateUser(@RequestBody UserInfo userEntity)
	{
		UserInfo user = userRepositoy.findUserInfoById(userEntity.getId());
		if (user != null)
		{
			user.setName(userEntity.getName());
			userRepositoy.save(user);
		}
		ResultMsg resultMsg = new ResultMsg(ResultStatusCode.OK.getErrcode(), ResultStatusCode.OK.getErrmsg(), null);
		return resultMsg;
	}
	
	@Modifying
	@RequestMapping("deleteuser")
	public Object deleteUser(int id)
	{
		userRepositoy.delete(id);
		ResultMsg resultMsg = new ResultMsg(ResultStatusCode.OK.getErrcode(), ResultStatusCode.OK.getErrmsg(), null);
		return resultMsg;
	}
	
}

七、封装返回的结果

1. ResultMsg.java 结果封装

package com.gyd.moneyCom.utils;

public class ResultMsg {

	private int errcode;
	private String errmsg;
	private Object p2pdata;
	
	public ResultMsg(int ErrCode, String ErrMsg, Object P2pData)
	{
		this.errcode = ErrCode;
		this.errmsg = ErrMsg;
		this.p2pdata = P2pData;
	}
	public int getErrcode() {
		return errcode;
	}
	public void setErrcode(int errcode) {
		this.errcode = errcode;
	}
	public String getErrmsg() {
		return errmsg;
	}
	public void setErrmsg(String errmsg) {
		this.errmsg = errmsg;
	}
	public Object getP2pdata() {
		return p2pdata;
	}
	public void setP2pdata(Object p2pdata) {
		this.p2pdata = p2pdata;
	}
	
}

2. ResultStatusCode.java 结果状态码

package com.gyd.moneyCom.utils;

public enum ResultStatusCode {

	OK(0, "OK"),
	SYSTEM_ERR(30001, "System error");
	
	private int errcode;
	private String errmsg;
	public int getErrcode() {
		return errcode;
	}
 
	public void setErrcode(int errcode) {
		this.errcode = errcode;
	}
 
	public String getErrmsg() {
		return errmsg;
	}
 
	public void setErrmsg(String errmsg) {
		this.errmsg = errmsg;
	}
	private ResultStatusCode(int Errode, String ErrMsg)
	{
		this.errcode = Errode;
		this.errmsg = ErrMsg;
	}
	
}

八、运行测试

1. 增加用户

2. 修改用户信息（上面的代码仅实现了修改用户name，实现逻辑详见代码）

3. 查找用户

a. 根据id查找

b.根据role查找

4. 删除用户（根据id删除）

 

参考：

[1]. https://blog.csdn.net/sun_t89/article/details/51912905（rest接口及数据库基本操作）

[2]. http://www.ruanyifeng.com/blog/2011/09/restful.html（理解restful架构）

[3]. http://www.ruanyifeng.com/blog/2014/05/restful_api.html（restful设计指南）

问题解决：application.properties文件中文显示其编码

右击该properties文件--properties--Resource--Text file encoding，选中other,选择其它编码方式，如UTF-8或GBK,这样就能在properties里面输入中文，而不会自动转成Unicode了。另外，其实如果key value是中文，也不一定读不出来。比如如下的情况，打开文件流时选择了正确的编码方式，这样用properties的load的方法可以加载中文的。（但有的情况下你选择不了编码方式自然就有问题了）