#create users table
CREATE TABLE `users` (
 `username` varchar(100) NOT NULL,
 `password` varchar(100) NOT NULL,
 `enabled` tinyint(1) NOT NULL DEFAULT '1',
 UNIQUE KEY `account` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#create authorities table
CREATE TABLE `authorities` (
 `username` varchar(50) NOT NULL,
 `authority` varchar(50) DEFAULT NULL,
 PRIMARY KEY (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#create persistent_logins table
CREATE TABLE `persistent_logins` (
 `username` varchar(64) NOT NULL,
 `series` varchar(64) NOT NULL,
 `token` varchar(64) NOT NULL,
 `last_used` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY (`series`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

<?xml version="1.0" encoding="UTF-8"?>
<beans:beans xmlns="http://www.springframework.org/schema/security"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
  xmlns:beans="http://www.springframework.org/schema/beans"
  xsi:schemaLocation="http://www.springframework.org/schema/beans
    http://www.springframework.org/schema/beans/spring-beans-4.0.xsd
    http://www.springframework.org/schema/security
    http://www.springframework.org/schema/security/spring-security-4.0.xsd">

  <!-- 配置为none的不经过任何spring的过滤器 -->
  <http pattern="/resources/**" security="none" />
  <http pattern="/sitemap.xml" security="none" />
  <http pattern="/favicon.ico" security="none" />

  <!-- 配置为permitAll允许用户访问，但依然经过过滤器处理 -->
  <http auto-config="true" use-expressions="true">

    <intercept-url pattern="/" access="permitAll" />
    <intercept-url pattern="/index*" access="permitAll" />
    <intercept-url pattern="/signin*" access="permitAll" />
    <intercept-url pattern="/login*" access="permitAll" />
    <intercept-url pattern="/register*" access="permitAll" />
    <intercept-url pattern="/invalidsession*" access="permitAll" />
    <intercept-url pattern="/404*" access="none" />

    <form-login login-page="/signin" authentication-failure-url="/signin?login_error" default-target-url="/query"/>
    <logout logout-success-url="/query" delete-cookies="JSESSIONID" />

    <intercept-url pattern="/admin" access="hasRole('ROLE_ADMIN')" />
    <intercept-url pattern="/**" access="hasAnyRole('ROLE_ADMIN','ROLE_USER')" />

    <csrf disabled="true" />
    <access-denied-handler error-page="/403" />

    <remember-me data-source-ref="dataSource" token-validity-seconds="1209600" remember-me-parameter="remember-me" />

    <session-management invalid-session-url="/"> 
      <concurrency-control max-sessions="1"/> 
    </session-management>

  </http>

  <authentication-manager erase-credentials="false">
    <authentication-provider>
      <password-encoder ref="bcryptEncoder" />
      <jdbc-user-service data-source-ref="dataSource" />
    </authentication-provider>
  </authentication-manager>

  <beans:bean id="messageSource"
    class="org.springframework.context.support.ReloadableResourceBundleMessageSource">
    <beans:property name="basenames">
      <beans:list>
        <beans:value>classpath:myMessages</beans:value>
      </beans:list>
    </beans:property>
  </beans:bean>

  <beans:bean name="bcryptEncoder" class="org.springframework.security.crypto.bcrypt.BCryptPasswordEncoder" />

</beans:beans>

<div class="container">

  <!-- 页面切换代码 -->
  <ul class="nav nav-tabs" id="loginTab" role="tablist">
    <li class="nav-item">
      <a class="nav-link active" id="home-tab" data-toggle="tab" href="#login" rel="external nofollow" 
        role="tab" aria-controls="home" aria-expanded="true">登录</a>
    </li>
    <li class="nav-item">
      <a class="nav-link" id="home-tab" data-toggle="tab" href="#register" rel="external nofollow" 
        role="tab" aria-controls="home" aria-expanded="true">注册</a>
    </li>
  </ul>

  <div class="tab-content" id="myTabContent">
    <!-- 登录页面 -->
    <div id="login" class="tab-pane fade show active" role="tabpanel" aria-labelledby="login-tab">
      <form class="form-signin" action="login" method="post">
        <label for="username" class="sr-only">Email address</label>
        <input type="email" name="username" id="username" class="form-control" placeholder="邮件地址">
        <label for="password" class="sr-only">Password</label>
        <input type="password" name="password" id="password" class="form-control" placeholder="密码">
        <button class="btn btn-lg btn-primary btn-block" type="submit">点击登录</button>
      </form>

    </div>

    <!-- 注册页面 -->
    <div id="register" class="tab-pane fade" role="tabpanel" aria-labelledby="register-tab">
      <div id="register_attention_alert_reg"></div>
      <form class="form-signin" onsubmit="return register()" method="post">
        <label for="registerEmail" class="sr-only">Email address</label>
        <input type="email" id="registerEmail" name="registerEmail" class="form-control" placeholder="邮件地址">
        <label for="registerPassword" class="sr-only">Password</label>
        <input type="password" name="password" id="registerPassword" class="form-control" placeholder="密码">
        <label for="inputPassword2" class="sr-only">Password</label>
        <input type="password" id="inputPasswordForRegister2" class="form-control" placeholder="请再次输入密码">
        <button class="btn btn-lg btn-primary btn-block" onclick="submit">点击注册</button>
      </form>
    </div>
  </div>

</div>

//插入users表的语句
String addUser = "insert into users(username,password) values(?,?)";

//对密码参数进行加密
String pwd = SpringSecurityUtil.encode(password);
Object[] param = {email, pwd};

//插入authorities表的语句
String addAuthority = "insert into authorities(username,authority) values(?,'ROLE_USER')";
Object[] authorityParam = {email};

int rows = 0;
try {
  rows = MyQueryRunnerUtil.getQueryRunner().update(addUser, param);
  rows += MyQueryRunnerUtil.getQueryRunner().update(addAuthority, authorityParam);
} catch (SQLException e) {
e.printStackTrace();
}

/**
  * BCrypt加密(适用于注册时密码加密)
  * 
  * @param rawPassword 明文密码
  * @return encoderPassword 密文密码，长度为60
  */
public static String encode(String rawPassword) {

  // 调用spring security的BCrypt加密
  BCryptPasswordEncoder encoder = new BCryptPasswordEncoder();
  String encoderPassword = encoder.encode(rawPassword);
  return encoderPassword;
}