前两章节讲述了如何通过APi接口实现对数据库的查询操作
这章主要讲述,Stringboot对数据库数据的查询、新增、修改、删除操作
第一节:数据库查询
首先编写UserMapper.xml。定义SQl语句
文件结构:demo\src\main\java\com\example\demo\mapper\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.example.demo.mapper.UserMapper">
<select id="UserList" resultType="com.example.demo.entity.User">
Select *
from user;
</select>
</mapper>
编写UserMapper.java。编写interface接口
文件结构:demo\src\main\java\com\example\demo\mapper\usermapper.java
package com.example.demo.mapper;
import com.example.demo.entity.User;
import java.util.List;
public interface UserMapper {
List<User> UserList();
}
编写UserApi.java,定义接口并编写逻辑处理
文件结构:demo\src\main\java\com\example\demo\controller\UserApi.java
package com.example.demo.controller;
import com.example.demo.entity.User;
import com.example.demo.mapper.UserMapper;
import com.example.demo.util.APIResultUtil;
import com.example.demo.util.MybatisUtl;
import org.apache.ibatis.session.SqlSession;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import java.util.List;
@RestController
@RequestMapping("/api")
public class UserAPi {
// 查询全部用户数据 测试成功
@RequestMapping("/setUserList")
public Object setUserList() {
try {
SqlSession sqlSession = MybatisUtl.sqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List<User> users = mapper.UserList();
// 这里判断是否有数据(注意是倒过来的)
if (users.isEmpty()) {
// 没有数据
return APIResultUtil.APIResultUtil(false, null, "无用户数据");
} else {
// 有数据
return APIResultUtil.APIResultUtil(true, users, "读取成功");
}
} catch (Exception e) {
System.out.println("程序错误!错误原因:" + e);
return APIResultUtil.APIResultUtil(false, null, "程序错误");
}
}
}
最后启动主类
文件结构:demo\src\main\java\com\example\demo\DemoApplication.java
第二节:数据库新增
首先编写UserMapper.xml。定义SQl语句
文件结构:demo\src\main\java\com\example\demo\mapper\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.example.demo.mapper.UserMapper">
<insert id="addUser" parameterType="com.example.demo.entity.User">
insert into user(UserEmail, UserName, UserPwd)
values (#{UserEmail}, #{UserName}, #{UserPwd});
</insert>
</mapper>
编写UserMapper.java。编写interface接口
文件结构:demo\src\main\java\com\example\demo\mapper\usermapper.java
package com.example.demo.mapper;
import org.apache.ibatis.annotations.Param;
public interface UserMapper {
int addUser(@Param("UserEmail") String UserEmail, @Param("UserName") String UserName, @Param("UserPwd") String UserPwd);
}
编写UserApi.java,定义接口并编写逻辑处理
文件结构:demo\src\main\java\com\example\demo\controller\UserApi.java
package com.example.demo.controller;
import com.example.demo.mapper.UserMapper;
import com.example.demo.util.APIResultUtil;
import com.example.demo.util.SqlSessionUtil;
import org.apache.ibatis.session.SqlSession;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
@RestController
@RequestMapping("/api")
public class UserApi {
@RequestMapping("addUser")
public Object GetUserList(@RequestParam(value = "UserEmail") String UserEmail, @RequestParam(value = "UserName") String UserName, @RequestParam(value = "UserPwd") String UserPwd) {
try {
SqlSession sqlSession = SqlSessionUtil.sqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
int i = mapper.addUser(UserEmail, UserName, UserPwd);
sqlSession.commit();
if (i == 1) {
return APIResultUtil.apiResult(true, null, "添加成功");
} else {
return APIResultUtil.apiResult(false, null, "添加失败");
}
} catch (Exception e) {
return APIResultUtil.apiResult(false, null, "程序错误,错误原因:" + e.getCause());
}
}
}
最后启动主类
文件结构:demo\src\main\java\com\example\demo\DemoApplication.java
第三节:数据库修改
首先编写UserMapper.xml。定义SQl语句
文件结构:demo\src\main\java\com\example\demo\mapper\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.example.demo.mapper.UserMapper">
<update id="updateUser" parameterType="com.example.demo.entity.User">
update user set UserName = #{UserName} where UserEmail = #{UserEmail};
</update>
</mapper>
编写UserMapper.java。编写interface接口
文件结构:demo\src\main\java\com\example\demo\mapper\usermapper.java
package com.example.demo.mapper;
import org.apache.ibatis.annotations.Param;
public interface UserMapper {
int updateUser(@Param("UserName") String UserName, @Param("UserEmail") String UserEmail);
}
编写UserApi.java,定义接口并编写逻辑处理
文件结构:demo\src\main\java\com\example\demo\controller\UserApi.java
package com.example.demo.controller;
import com.example.demo.mapper.UserMapper;
import com.example.demo.util.APIResultUtil;
import com.example.demo.util.SqlSessionUtil;
import org.apache.ibatis.session.SqlSession;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
@RestController
@RequestMapping("/api")
public class UserApi {
@RequestMapping("updateUser")
public Object updateUser(@RequestParam(value = "UserName") String UserName, @RequestParam(value = "UserEmail") String UserEmail) {
try {
SqlSession sqlSession = SqlSessionUtil.sqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
int i = mapper.updateUser(UserName, UserEmail);
sqlSession.commit();
if (i == 1) {
return APIResultUtil.apiResult(true, null, "修改成功");
} else {
return APIResultUtil.apiResult(false, null, "修改失败");
}
} catch (Exception e) {
return APIResultUtil.apiResult(false, null, "程序错误,错误原因:" + e.getCause());
}
}
}
最后启动主类
文件结构:demo\src\main\java\com\example\demo\DemoApplication.java
第四节:数据库删除
首先编写UserMapper.xml。定义SQl语句
文件结构:demo\src\main\java\com\example\demo\mapper\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.example.demo.mapper.UserMapper">
<delete id="DelUser" parameterType="com.example.demo.entity.User">
delete from user where UserEmail= #{UserEmail};
</delete>
</mapper>
编写UserMapper.java。编写interface接口
文件结构:demo\src\main\java\com\example\demo\mapper\usermapper.java
package com.example.demo.mapper;
import org.apache.ibatis.annotations.Param;
public interface UserMapper {
int DelUser(@Param("UserEmail") String UserEmail);
}
编写UserApi.java,定义接口并编写逻辑处理
文件结构:demo\src\main\java\com\example\demo\controller\UserApi.java
package com.example.demo.controller;
import com.example.demo.mapper.UserMapper;
import com.example.demo.util.APIResultUtil;
import com.example.demo.util.SqlSessionUtil;
import org.apache.ibatis.session.SqlSession;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
@RestController
@RequestMapping("/api")
public class UserApi {
@RequestMapping("DelUser")
public Object DelUser(@RequestParam(value = "UserEmail") String UserEmail) {
try {
SqlSession sqlSession = SqlSessionUtil.sqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
int i = mapper.DelUser(UserEmail);
sqlSession.commit();
if (i == 1) {
return APIResultUtil.apiResult(true, null, "删除成功");
} else {
return APIResultUtil.apiResult(false, null, "删除失败");
}
} catch (Exception e) {
return APIResultUtil.apiResult(false, null, "删除失败,错误原因:" + e.getCause());
}
}
}
最后启动主类
文件结构:demo\src\main\java\com\example\demo\DemoApplication.java