12.21基础验证配置

<dependency>
  <groupId>org.apache.shiro</groupId>
  <artifactId>shiro-web</artifactId>
  <version>1.3.2</version>
</dependency>

<listener>
    <listener-class>org.apache.shiro.web.env.EnvironmentLoaderListener</listener-class>
</listener>

<filter>
    <filter-name>ShiroFilter</filter-name>
    <filter-class>org.apache.shiro.web.servlet.ShiroFilter</filter-class>
</filter>

<filter-mapping>
    <filter-name>ShiroFilter</filter-name>
    <url-pattern>/*</url-pattern>
    <dispatcher>REQUEST</dispatcher>
    <dispatcher>FORWARD</dispatcher>
    <dispatcher>INCLUDE</dispatcher>
    <dispatcher>ERROR</dispatcher>
</filter-mapping>

<filter>
    <filter-name>ShiroFilter</filter-name>
    <filter-class>org.apache.shiro.web.servlet.ShiroFilter</filter-class>
    <init-param>    <!-- 此处明确的表示配置文件的路径 -->
         <param-name>configPath</param-name>
         <param-value>classpath:shiro.ini</param-value>
    </init-param>
</filter>

<filter-mapping>
    <filter-name>ShiroFilter</filter-name>
    <url-pattern>/*</url-pattern>
    <dispatcher>REQUEST</dispatcher>
    <dispatcher>FORWARD</dispatcher>
    <dispatcher>INCLUDE</dispatcher>
    <dispatcher>ERROR</dispatcher>
</filter-mapping>

 

 

 

package cn.mldn.servlet;

import java.io.IOException;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.shiro.SecurityUtils;
import org.apache.shiro.authc.UsernamePasswordToken;
import org.apache.shiro.subject.Subject;

@SuppressWarnings("serial")
@WebServlet("/shiroLogin")
public class LoginServlet extends HttpServlet{
    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        // TODO Auto-generated method stub
        //super.doGet(req, resp);
        this.doPost(request, response);
    }
    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        // TODO Auto-generated method stub
        //super.doPost(req, resp);
        String mid = request.getParameter("mid");
        String password = request.getParameter("password");
        Subject subject = SecurityUtils.getSubject();
        UsernamePasswordToken token = new UsernamePasswordToken(mid,password);
        subject.login(token);
        request.getSession().setAttribute("mid", mid);
        request.getRequestDispatcher("/welcome.jsp").forward(request, response);
    }
}

package cn.mldn.servlet;

import java.io.IOException;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.shiro.SecurityUtils;
import org.apache.shiro.authc.UsernamePasswordToken;
import org.apache.shiro.subject.Subject;

@SuppressWarnings("serial")
@WebServlet("/shiroLogin")
public class LoginServlet extends HttpServlet{
    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        // TODO Auto-generated method stub
        //super.doGet(req, resp);
        this.doPost(request, response);
    }
    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        // TODO Auto-generated method stub
        //super.doPost(req, resp);
        String mid = request.getParameter("mid");
        String password = request.getParameter("password");
        Subject subject = SecurityUtils.getSubject();
        UsernamePasswordToken token = new UsernamePasswordToken(mid,password);
        subject.login(token);
        request.getSession().setAttribute("mid", mid);
        request.getRequestDispatcher("/pages/welcome.jsp").forward(request, response);
    }
}

   <form action="" method="post">
                 用户名：<input type="text" name="mid" id="mid"><br>
                 密$nbsp;码：    <input type="password" name="password" id="password"><br>
       <input type="submit" value="登录">
       <input type="reset" value="重置">
   </form>

   <form action="shiroLogin" method="post">
                 用户名：<input type="text" name="mid" id="mid"><br>
                 密$nbsp;码：    <input type="password" name="password" id="password"><br>
       <input type="submit" value="登录">
       <input type="reset" value="重置">
   </form>

[main]
# 如果现在认证失败，则跳转到loginUrl配置的路径
authc.loginUrl=/login.jsp
jdbcRealm=cn.mldn.realm.MyRealm
securityManager.realms=$jdbcRealm
# 配置所有需要进行路径检测的页面
[urls]
# 登录的页面是不需要进行检测处理的
/shiroLogin=anon
# 指定的页面需要进行登录检测
/pages/welcome.jsp=authc

---

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%
    String path = request.getContextPath();
    String basePath = request.getScheme() + "://" +request.
    getServerName()+":"+request.getServerPort()+path+"/";
    //request.setCharacterEncoding("UTF-8");


%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<base href="<%=basePath%>">
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Shiro Login Demo</title>
</head>
<body>
   <form action="shiroLogin" method="post">
                 用户名：<input type="text" name="mid" id="mid"><br>
                 密$nbsp;码：    <input type="password" name="password" id="password"><br>
       <input type="submit" value="登录">
       <input type="reset" value="重置">
   </form>
</body>
</html>

<?xml version="1.0" encoding="UTF-8"?>  
<web-app version="2.5"  
    xmlns="http://java.sun.com/xml/ns/javaee"  
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"  
    xsi:schemaLocation="http://java.sun.com/xml/ns/javaee  
    http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd">  
      
  <display-name>Archetype Created Web Application</display-name>
  <listener>
    <listener-class>
        org.apache.shiro.web.env.EnvironmentLoaderListener
    </listener-class>
</listener>  
<filter>
    <filter-name>ShiroFilter</filter-name>
    <filter-class>org.apache.shiro.web.servlet.ShiroFilter</filter-class>
    <init-param>    <!-- 此处明确的表示配置文件的路径 -->
         <param-name>configPath</param-name>
         <param-value>classpath:shiro.ini</param-value>
    </init-param>
</filter>

<filter-mapping>
    <filter-name>ShiroFilter</filter-name>
    <url-pattern>/*</url-pattern>
    <dispatcher>REQUEST</dispatcher>
    <dispatcher>FORWARD</dispatcher>
    <dispatcher>INCLUDE</dispatcher>
    <dispatcher>ERROR</dispatcher>
</filter-mapping>
</web-app>

[main]
# 如果现在认证失败，则跳转到loginUrl配置的路径
authc.loginUrl=/login.jsp
jdbcRealm=cn.mldn.realm.MyRealm
securityManager.realms=$jdbcRealm
# 配置所有需要进行路径检测的页面
[urls]
# 登录的页面是不需要进行检测处理的
/shiroLogin=anon
# 指定的页面需要进行登录检测
/pages/welcome.jsp=authc

package cn.mldn.realm;

import org.apache.shiro.authc.AuthenticationException;
import org.apache.shiro.authc.AuthenticationInfo;
import org.apache.shiro.authc.AuthenticationToken;
import org.apache.shiro.authc.IncorrectCredentialsException;
import org.apache.shiro.authc.SimpleAuthenticationInfo;
import org.apache.shiro.authc.UnknownAccountException;
import org.apache.shiro.authz.AuthorizationInfo;
import org.apache.shiro.authz.SimpleAuthorizationInfo;
import org.apache.shiro.realm.AuthorizingRealm;
import org.apache.shiro.subject.PrincipalCollection;

import cn.mldn.service.MemberLoginService;
import cn.mldn.vo.Member;

public class MyRealm extends AuthorizingRealm {
    @Override
    protected AuthenticationInfo doGetAuthenticationInfo(
            AuthenticationToken token) throws AuthenticationException {
        // TODO Auto-generated method stub
        System.out.println("************* 1、用户登陆认证：doGetAuthenticationInfo() *************");
        // 1、 登录认证的方法需要先执行，需要用他来判断登录的用户信息是否合法
        String username = (String) token.getPrincipal();
        // 需要通过用户名取得用户的完整信息，利用业务层操作
        MemberLoginService service = new MemberLoginService();
        //Member vo = new MemberLoginService().get(username);  // 需要取得的是用户的信息
        Member vo = service.get(username);  // 需要取得的是用户的信息
        service.close();
        if(vo == null) {
            throw new UnknownAccountException("该用户名称不存在！");
        } else { // 进行密码的验证处理
            //String password = new String((char [])token.getPrincipal());
            String password = new String((char []) token.getCredentials());
            // 将数据库中的密码与输入的密码进行比较，这样就可以确定当前用户是否可以正常登陆
            if (vo.getPassword().equals(password)){  //  密码正确
                AuthenticationInfo auth = new SimpleAuthenticationInfo(username, password, "memberRealm");
                return auth ;
            } else {
                throw new IncorrectCredentialsException("密码错误！");
            }
        } 
    }
    @Override
    protected AuthorizationInfo doGetAuthorizationInfo(
            PrincipalCollection principals) {
        // TODO Auto-generated method stub
        System.out.println("*************2、用户角色与权限：doGetAuthorizationInfo() *************");
        String username = (String) principals.getPrimaryPrincipal();   // 取得用户登录名
        //AuthorizationInfo auth = new SimpleAuthorizationInfo();      // 定义授权信息的返回数据
        SimpleAuthorizationInfo auth = new SimpleAuthorizationInfo();      // 定义授权信息的返回数据
        MemberLoginService service = new MemberLoginService();// 进行业务层处理
        auth.setRoles(service.listRolesByMember(username));
        auth.setStringPermissions(service.listActionsByMember(username));// 所有的权限必须以Set集合的形式出现
        service.close();
        return auth;
    }



}

package cn.mldn.service;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashSet;
import java.util.Set;

import cn.mldn.vo.Member;

public class MemberLoginService {
    private Connection conn;    // 数据库的连接类对象
    private static final String DBDRIVER = "org.gjt.mm.mysql.Driver";
    //private static final String DBDRIVER = "com.mysql.jdbc.Driver";
    private static final String DBURL = "jdbc:mysql://localhost:3306/shirodb";
    private static final String DBUSER = "root" ;
    private static final String PASSWORD = "" ;
    private PreparedStatement pstmt = null;
    public MemberLoginService() { // 在构造方法里面进行数据库连接对象的实例化
        //super();
        this.connectionDataBase(); // 进行数据库的连接取得
    }
    //shiro登录第一步涉及到用户登录检测，第二步才涉及到角色和权限的
    //public Member login() { // 实现用户登录处理
    public Member get(String mid) {
        Member vo = null;
        try{
        String sql = "SELECT mid,password FROM member WHERE mid=? " ;
        this.pstmt = this.conn.prepareStatement(sql);
        this.pstmt.setString(1, mid);
        ResultSet rs = this.pstmt.executeQuery();
        if (rs.next()) {
            vo = new Member();
            vo.setMid(rs.getString(1));
            vo.setPassword(rs.getString(2));
            
        }
        }catch(Exception e){}
        
        return vo;
    }
    /**
     * 根据用户名称查询用户对应的所有的角色数据
     * @param mid
     * @return
     */
    public Set<String> listRolesByMember(String mid) {
        Set<String> allRoles = new HashSet<String>();
        String sql = "SELECT flag FROM role WHERE rid IN ("
                + " SELECT rid FROM member_role WHERE mid=?)";
        try {
            this.pstmt = this.conn.prepareStatement(sql);
            this.pstmt.setString(1, mid);
            ResultSet rs = this.pstmt.executeQuery();
            while (rs.next()) {
                allRoles.add(rs.getString(1));
            }
        //} catch (SQLException e) {
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        
        return allRoles;
        
    }
    /**
     * 根据用户的名称查询出该用户对应的所有权限数据
     * @param mid
     * @return
     */
    public Set<String> listActionsByMember(String mid) {
        Set<String> allActions = new HashSet<String>();
        String sql = "SELECT flag FROM action WHERE actid IN ("
                + " SELECT actid FROM role_action WHERE rid IN("
                + " SELECT rid FROM member_role WHERE mid=?))"
                ;
        try {
            this.pstmt = this.conn.prepareStatement(sql);
            this.pstmt.setString(1, mid);
            ResultSet rs = this.pstmt.executeQuery();
            while (rs.next()) {
                allActions.add(rs.getString(1));
            }
        //} catch (SQLException e) {
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        
        return allActions;
        
    }
    public void close(){
        if (this.conn != null) {
            try {
                this.conn.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
    }
    private void connectionDataBase(){  // 专门负责数据库连接的创建
        try {
            Class.forName(DBDRIVER);
            this.conn = DriverManager.getConnection(DBURL, DBUSER, PASSWORD);
        //} catch (ClassNotFoundException e) {
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        
    }
    
}

package cn.mldn.vo;

import java.io.Serializable;

@SuppressWarnings("serial")
public class Member implements Serializable{
    private String mid;
    private String password;
    private String name;
    public String getMid() {
        return mid;
    }
    public void setMid(String mid) {
        this.mid = mid;
    }
    public String getPassword() {
        return password;
    }
    public void setPassword(String password) {
        this.password = password;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    
}

package cn.mldn.servlet;

import java.io.IOException;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.shiro.SecurityUtils;
import org.apache.shiro.authc.UsernamePasswordToken;
import org.apache.shiro.subject.Subject;

@SuppressWarnings("serial")
@WebServlet("/shiroLogin")
public class LoginServlet extends HttpServlet{
    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        // TODO Auto-generated method stub
        //super.doGet(req, resp);
        this.doPost(request, response);
    }
    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        // TODO Auto-generated method stub
        //super.doPost(req, resp);
        String mid = request.getParameter("mid");
        String password = request.getParameter("password");
        Subject subject = SecurityUtils.getSubject();
        UsernamePasswordToken token = new UsernamePasswordToken(mid,password);
        subject.login(token);
        request.getSession().setAttribute("mid", mid);
        request.getRequestDispatcher("/pages/welcome.jsp").forward(request, response);
    }
}

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
</head>
<body>
    <h1>Welcome，你懂的。</h1>
</body>
</html>

package cn.mldn.test;

import org.apache.shiro.SecurityUtils;
import org.apache.shiro.authc.UsernamePasswordToken;
import org.apache.shiro.config.IniSecurityManagerFactory;
import org.apache.shiro.mgt.SecurityManager;
//import org.apache.shiro.realm.jdbc.JdbcRealm;
import org.apache.shiro.subject.Subject;
import org.apache.shiro.util.Factory;

//import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;



//import org.apache.shiro.util.Factory;

public class TestLoginDemo {
   public static void main(String[] args) {
       //MysqlDataSource 
       //JdbcRealm
       
       //Factory factory;
       //SecurityManager sm;
       // 取得Factory接口对象，主要的目的是通过配置文件加载文件之中的信息，这些信息暂时不能够成为认证信息
       Factory<SecurityManager> factory = new IniSecurityManagerFactory("classpath:shiro.ini");
       // 取得里面所保存的所有的认证数据信息
       SecurityManager securityManager = factory.getInstance();
       //利用一个专门的认证操作的处理类，实现认证处理的具体的实现
       SecurityUtils.setSecurityManager(securityManager);
       // 获取进行用户名和密码认证的接口对象
       Subject subject = SecurityUtils.getSubject();
       // 定义了一个Token，里面保存要登录的用户名和密码信息
       UsernamePasswordToken token = new UsernamePasswordToken("admin","hello");
       // 实现用户登录处理
       subject.login(token);
       //System.out.println(subject.getPrincipal());//取得用户名
       //subject.checkPermissions("member:add","member:edit","emp:remove");
       //System.out.println(subject.isPermitted("member:add"));
       System.out.println(subject.hasRole("member"));
       System.out.println("++++++++++++++++++++++++++++++++++++++++++++++++");
       //System.out.println(subject.isPermitted("emp:*"));
       System.out.println(subject.isPermitted("member:add"));
   }
}
/*public interface Realm{
    *//**
     * 只是要求返回一个当前使用的Realm名字，这个名字可以任意返回，但是不要重名
     * @return
     *//*
    public String getName();
    *//**
     * 判断你当前使用的Token的类型是否为指定的类型
     * @param token
     * @return
     *//*
    public boolean supports(AuthenticationToken token) ;
    *//**
     * 得到用户的认证信息，根据传入的Token取得
     * @param token 包含了要进行验证的所有数据
     * @return
     * @throws AuthenticationException
     *//*
    public AuthenticationInfo getAuthenticationInfo(AuthenticationToken token)
            throws AuthenticationException
}
*/

# Licensed to the Apache Software Foundation (ASF) under one or more
# contributor license agreements.  See the NOTICE file distributed with
# this work for additional information regarding copyright ownership.
# The ASF licenses this file to You under the Apache License, Version 2.0
# (the "License"); you may not use this file except in compliance with
# the License.  You may obtain a copy of the License at
#
#      http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.

# For the general syntax of property based configuration files see the
# documenation of org.apache.log4j.PropertyConfigurator.

# The root category uses the appender called A1. Since no priority is
# specified, the root category assumes the default priority for root
# which is DEBUG in log4j. The root category is the only category that
# has a default priority. All other categories need not be assigned a
# priority in which case they inherit their priority from the
# hierarchy.

log4j.rootCategory=, A1

# A1 is set to be a LogMonitorAppender which outputs to a swing
# logging console. 
  
log4j.appender.A1=org.apache.log4j.lf5.LF5Appender

-- 一、 删除数据库
DROP DATABASE IF EXISTS shirodb;
-- 二、创建数据库
CREATE DATABASE shirodb CHARACTER SET UTF8 ;
-- 三、使用数据库
USE shirodb ;
-- 四、创建数据表
-- 1、用户数据表
CREATE TABLE member (
    mid                  VARCHAR(50),
    password             VARCHAR(32),
    name                 VARCHAR(20),
    locked               INT,
    CONSTRAINT pk_mid PRIMARY KEY (mid)
) type = innodb;
CREATE TABLE member (
    mid                  VARCHAR(50),
    password             VARCHAR(32),
    name                 VARCHAR(20),
    locked               INT,
    CONSTRAINT pk_mid PRIMARY KEY (mid)
) ENGINE = innodb;
-- 2、角色数据表
CREATE TABLE role (
         rid                 INT          AUTO_INCREMENT,
         title               VARCHAR(50),
         flag                VARCHAR(50),
         CONSTRAINT pk_rid PRIMARY KEY (rid)

) type = innodb;
CREATE TABLE role (
         rid                 INT          AUTO_INCREMENT,
         title               VARCHAR(50),
         flag                VARCHAR(50),
         CONSTRAINT pk_rid PRIMARY KEY (rid)

) ENGINE = innodb;
-- 3、用户角色关系表
CREATE TABLE member_role (
        mid              VARCHAR(50) ,
        rid              INT,
        CONSTRAINT fk_mid1 FOREIGN KEY(mid) REFERENCES member (mid),
        CONSTRAINT fk_rid1 FOREIGN KEY(rid) REFERENCES role (rid)

) type = innodb;                
CREATE TABLE member_role (
        mid              VARCHAR(50) ,
        rid              INT,
        CONSTRAINT fk_mid1 FOREIGN KEY(mid) REFERENCES member (mid),
        CONSTRAINT fk_rid1 FOREIGN KEY(rid) REFERENCES role (rid)

) ENGINE = innodb;   
-- 4、权限表
CREATE TABLE action (
    actid               INT     AUTO_INCREMENT,
    title               VARCHAR(50),
    flag                VARCHAR(50),
    CONSTRAINT pk_actid PRIMARY KEY (actid)
 ) type = innodb;
 CREATE TABLE action (
    actid               INT     AUTO_INCREMENT,
    title               VARCHAR(50),
    flag                VARCHAR(50),
    CONSTRAINT pk_actid PRIMARY KEY (actid)
 ) ENGINE = innodb;
 -- 5、角色-权限关系表
 CREATE TABLE role_action (
     rid                 INT,
     actid               INT,
     CONSTRAINT fk_rid6 FOREIGN KEY(rid) REFERENCES role(rid)  ,
     CONSTRAINT fk_actid6 FOREIGN KEY(actid) REFERENCES action(actid)
) type = innodb;
 CREATE TABLE role_action (
     rid                 INT,
     actid               INT,
     CONSTRAINT fk_rid6 FOREIGN KEY(rid) REFERENCES role(rid)  ,
     CONSTRAINT fk_actid6 FOREIGN KEY(actid) REFERENCES action(actid)
) ENGINE = innodb;
-- 五、测试数据
-- 1、增加角色信息
INSERT INTO role(title,flag) VALUES ('管理员','member');
INSERT INTO role(title,flag) VALUES ('部门管理','dept');
INSERT INTO role(title,flag) VALUES ('雇员管理','emp');
-- 2、增加权限信息
INSERT INTO action(title,flag) VALUES ('增加管理员','member:add');
INSERT INTO action(title,flag) VALUES ('管理员列表','member:list');
INSERT INTO action(title,flag) VALUES ('部门增加','dept:add');
INSERT INTO action(title,flag) VALUES ('部门列表','dept:list');
INSERT INTO action(title,flag) VALUES ('部门编辑','dept:edit');
INSERT INTO action(title,flag) VALUES ('员工入职','emp:add');
INSERT INTO action(title,flag) VALUES ('员工列表','emp:list');
INSERT INTO action(title,flag) VALUES ('员工编辑','emp:edit');
INSERT INTO action(title,flag) VALUES ('员工离职','emp:remove');


-- 3、增加角色与权限关系
INSERT INTO role_action(rid,actid) VALUES (1,1);
INSERT INTO role_action(rid,actid) VALUES (1,2);
INSERT INTO role_action(rid,actid) VALUES (2,3);
INSERT INTO role_action(rid,actid) VALUES (2,4);
INSERT INTO role_action(rid,actid) VALUES (2,5);
INSERT INTO role_action(rid,actid) VALUES (3,6);
INSERT INTO role_action(rid,actid) VALUES (3,7);
INSERT INTO role_action(rid,actid) VALUES (3,8);
INSERT INTO role_action(rid,actid) VALUES (3,9);
INSERT INTO member(mid,password,name,locked) VALUES ('admin','hello','管理员',0);
INSERT INTO member(mid,password,name,locked) VALUES ('mermaid','hello','老李',0);
INSERT INTO member(mid,password,name,locked) VALUES ('mldn','java','隔壁老王',0);
INSERT INTO member_role(mid,rid) VALUES ('admin',1);
INSERT INTO member_role(mid,rid) VALUES ('admin',2);
INSERT INTO member_role(mid,rid) VALUES ('mermaid',3);
INSERT INTO member_role(mid,rid) VALUES ('mldn',2);
INSERT INTO member_role(mid,rid) VALUES ('mldn',3);

-- 一、 删除数据库
DROP DATABASE IF EXISTS shirodb;
-- 二、创建数据库
CREATE DATABASE shirodb CHARACTER SET UTF8 ;
-- 三、使用数据库
USE shirodb ;
CREATE TABLE member (
    mid                  VARCHAR(50),
    password             VARCHAR(32),
    name                 VARCHAR(20),
    locked               INT,
    CONSTRAINT pk_mid PRIMARY KEY (mid)
) type = innodb;
CREATE TABLE member (
    mid                  VARCHAR(50),
    password             VARCHAR(32),
    name                 VARCHAR(20),
    locked               INT,
    CONSTRAINT pk_mid PRIMARY KEY (mid)
) ENGINE = innodb;
INSERT INTO member(mid,password,name,locked) VALUES ('admin','hello','管理员',0);
INSERT INTO member(mid,password,name,locked) VALUES ('mermaid','hello','老李',0);
INSERT INTO member(mid,password,name,locked) VALUES ('mldn','java','隔壁老王',0);