项目开启
首先设计数据库以及存储表、表的联系 —需要存贮的信息
基本表的性质
基本表与中间表、临时表不同,因为它具有如下四个特性:
(1) 原子性。基本表中的字段是不可再分解的。
(2) 原始性。基本表中的记录是原始数据(基础数据)的记录。
(3) 演绎性。由基本表与代码表中的数据,可以派生出所有的输出数据。
(4) 稳定性。基本表的结构是相对稳定的,表中的记录是要长期保存的。
理解基本表的性质后,在设计数据库时,就能将基本表与中间表、临时表区分开来
例如商城的话:
表的设计基本思想:
一对多 多对一 多对多 最终还是要化为 一对多
基本数据表就是:基本用户 管理员用户 三元用户 商品信息 商品分类 商品的中间表 订单 订单具体信息
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="org.sang.mapper.UserMapper">
<select id="loadUserByUsername" resultType="org.sang.bean.User">
SELECT * FROM user WHERE username=#{username}
</select>
<insert id="reg" useGeneratedKeys="true" keyProperty="id">
INSERT INTO user set username=#{username},password=#{password},nickname=#{nickname}
</insert>
<update id="updateUserEmail">
UPDATE user set email=#{email} WHERE id=#{id}
</update>
<select id="getUserByNickname" resultMap="BaseResultMap">
SELECT u.*,r.`id` AS rid,r.`name` AS rname FROM user u,roles r,roles_user ru WHERE u.`id`=ru.`uid` AND
r.`id`=ru.`rid` AND u.`id` NOT IN(SELECT u.`id` FROM user u,roles_user ru WHERE u.`id`=ru.`uid` AND ru.`rid`=1)
<choose>
<when test="nickname!=null and nickname!=''">
and u.nickname like concat('%',#{nickname},'%') ORDER BY u.`id`
</when>
<otherwise>
ORDER BY u.`id` limit 20
</otherwise>
</choose>
</select>
<select id="getUserById" resultMap="BaseResultMap">
SELECT u.*,r.`id` AS rid,r.`name` AS rname FROM user u,roles r,roles_user ru WHERE u.`id`=ru.`uid` AND
r.`id`=ru.`rid` AND u.`id`=#{id}
</select>
<resultMap id="BaseResultMap" type="org.sang.bean.User">
<id column="id" property="id"/>
<result column="username" property="username"/>
<result column="nickname" property="nickname"/>
<result column="password" property="password"/>
<result column="enabled" property="enabled"/>
<result column="email" property="email"/>
<result column="userface" property="userface"/>
<result column="regTime" property="regTime"/>
<collection property="roles" ofType="org.sang.bean.Role">
<id column="rid" property="id"/>
<result column="rname" property="name"/>
</collection>
</resultMap>
<select id="getAllRole" resultType="org.sang.bean.Role">
select * from roles;
</select>
<update id="updateUserEnabled">
UPDATE user set enabled=#{enabled} WHERE id=#{uid}
</update>
<delete id="deleteUserById">
DELETE FROM user WHERE id=#{id}
</delete>
<delete id="deleteUserRolesByUid">
DELETE FROM roles_user WHERE uid=#{id}
</delete>
<insert id="setUserRoles">
INSERT INTO roles_user(rid,uid) VALUES
<foreach collection="rids" separator="," item="rid">
(#{rid},#{id})
</foreach>
</insert>
</mapper>
接口user封装
package org.sang.mapper;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.sang.bean.Role;
import org.sang.bean.User;
import java.util.List;
/**
* Created by sang on 2017/12/17.
*/
@Mapper
public interface UserMapper {
User loadUserByUsername(@Param("username") String username);
long reg(User user);
int updateUserEmail(@Param("email") String email, @Param("id") Long id);
List<User> getUserByNickname(@Param("nickname") String nickname);
List<Role> getAllRole();
int updateUserEnabled(@Param("enabled") Boolean enabled, @Param("uid") Long uid);
int deleteUserById(Long uid);
int deleteUserRolesByUid(Long id);
int setUserRoles(@Param("rids") Long[] rids, @Param("id") Long id);
User getUserById(@Param("id") Long id);
}
提供其他类操作数据库
提供给service层再进一层封装 对接口进行实现
public int reg(User user) {
User loadUserByUsername = userMapper.loadUserByUsername(user.getUsername());
if (loadUserByUsername != null) {
return 1;
}
//插入用户,插入之前先对密码进行加密
user.setPassword(passwordEncoder.encode(user.getPassword()));
user.setEnabled(true);//用户可用
long result = userMapper.reg(user);
//配置用户的角色,默认都是普通用户
String[] roles = new String[]{"2"};
int i = rolesMapper.addRoles(roles, user.getId());
boolean b = i == roles.length && result == 1;
if (b) {
return 0;
} else {
return 2;
}
}
然后传给控制层
@PostMapping("/reg")
public RespBean reg(User user) {
int result = userService.reg(user);
if (result == 0) {
//成功
return new RespBean("success", "注册成功!");
} else if (result == 1) {
return new RespBean("error", "用户名重复,注册失败!");
} else {
//失败
return new RespBean("error", "注册失败!");
}
}
}
显示到视图层–前端