将saiku3.8 自带的H2嵌入式用户角色数据库迁移到mysql数据库

参考原文，略作修改：http://www.cnblogs.com/avivaye/p/4881106.html

> 以上链接文章的思路是正确的，有略微的遗漏，saiku-beans.properties中也存在与用户相关的H2数据库配置，并且在id为“userDaoDataSource”的bean中有用到，为了避免出现问题，将其一并替换，并修改原文中部分配置到配置文件中

# 修改web.xml，将原有的与用户相关的h2数据库配置注释
```
    <context-param>
        <param-name>db.url</param-name>
        <param-value>jdbc:h2:../../data/saiku;MODE=MySQL</param-value>
    </context-param>
    <context-param>
        <param-name>db.user</param-name>
        <param-value>sa</param-value>
    </context-param>
    <context-param>
        <param-name>db.password</param-name>
        <param-value></param-value>
    </context-param>
```
# 修改saiku-beans.xml中与用户相关的h2数据库配置
```
    <bean id="h2database" class="org.saiku.database.Database" init-method="init">
        <property name="datasourceManager" ref="repositoryDsManager"/>
        <property name="datasourcetype" value="mysql" />
        <property name="url" value="${userdao.url}" />
        <property name="user" value="${userdao.username}" />
        <property name="pword" value="${userdao.password}" />
    </bean>
```
# 修改Database中的数据源为mysql并添加代码
```
    private String url ;
    private String user ;
    private String pword ;
    private String datasourcetype ;
    public String getDatasourcetype() {
        return datasourcetype;
    }

public void setDatasourcetype(String datasourcetype) {
        this.datasourcetype = datasourcetype;
    }
    public String getUrl() {
        return url;
    }

public void setUrl(String url) {
        this.url = url;
    }

public String getUser() {
        return user;
    }

public void setUser(String user) {
        this.user = user;
    }

public String getPword() {
        return pword;
    }

public void setPword(String pword) {
        this.pword = pword;
    }
```
## 修改initDB方法代码
```
    private void initDB() {
        ds = new MysqlDataSource();
        ((MysqlDataSource) ds).setUrl(url);
        ((MysqlDataSource) ds).setUser(user);
        ((MysqlDataSource) ds).setPassword(pword);
    }
```
## 修改loadUsers方法、checkUpdatedEncyption方法、updateForEncyption方法
```
    private void loadUsers() throws SQLException {

Connection c = ds.getConnection();

Statement statement = c.createStatement();

statement.execute(" CREATE TABLE IF NOT EXISTS log ( time  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, log  TEXT); ");
        statement.execute(" CREATE TABLE IF NOT EXISTS users(user_id INT(11) NOT NULL AUTO_INCREMENT, " + " username VARCHAR(45) NOT NULL UNIQUE, password VARCHAR(100) NOT NULL, email VARCHAR(100), " + " enabled TINYINT NOT NULL DEFAULT 1, PRIMARY KEY(user_id)); ");
        statement.execute(" CREATE TABLE IF NOT EXISTS user_roles ( " + " user_role_id INT(11) NOT NULL AUTO_INCREMENT,username VARCHAR(45), "  + " user_id INT(11) NOT NULL REFERENCES users(user_id), " + " ROLE VARCHAR(45) NOT NULL, " + " PRIMARY KEY (user_role_id)); ");

ResultSet result = statement.executeQuery("select count(*) as c from log where log = 'insert users'");

result.next();

if (result.getInt("c") == 0) {

statement.execute("INSERT INTO users (username,password,email, enabled) VALUES ('admin','admin', 'test@admin.com',TRUE);");
            statement.execute("INSERT INTO users (username,password,enabled) VALUES ('smith','smith', TRUE);");
            statement.execute("INSERT INTO user_roles (user_id, username, ROLE) VALUES (1, 'admin', 'ROLE_USER');");
            statement.execute("INSERT INTO user_roles (user_id, username, ROLE) VALUES (1, 'admin', 'ROLE_ADMIN');");
            statement.execute("INSERT INTO user_roles (user_id, username, ROLE) VALUES (2, 'smith', 'ROLE_USER');");
            statement.execute("INSERT INTO log (log) VALUES('insert users');");
        }

String encrypt = servletContext.getInitParameter("db.encryptpassword");
        if (encrypt.equals("true") && !checkUpdatedEncyption()) {
            updateForEncyption();
        }
    }

public boolean checkUpdatedEncyption() throws SQLException{
        Connection c = ds.getConnection();
        Statement statement = c.createStatement();
        ResultSet result = statement.executeQuery("select count(*) as c from log where log = 'update passwords'");
        result.next();
        return result.getInt("c") != 0;
    }

public void updateForEncyption() throws SQLException {

Connection c = ds.getConnection();
        Statement statement = c.createStatement();
        statement.execute("ALTER TABLE users MODIFY COLUMN PASSWORD VARCHAR(100) DEFAULT NULL");
        ResultSet result = statement.executeQuery("select username, password from users");
        while (result.next()) {
            statement = c.createStatement();
            String pword = result.getString("password");
            String hashedPassword = passwordEncoder.encode(pword);
            String sql = "UPDATE users " + "SET password = '" + hashedPassword
                    + "' WHERE username = '" + result.getString("username")
                    + "'";
            statement.executeUpdate(sql);
        }
        statement = c.createStatement();
        statement.execute("INSERT INTO log (log) VALUES('update passwords');");
    }
```
# 修改数据源配置
文件：saiku-beans.properties
```
userdao.driverclass=com.mysql.jdbc.Driver
userdao.url=jdbc:mysql://192.168.11.140:3306/saiku
userdao.username=root
userdao.password=root
```
文件：applicationContext-spring-security-jdbc.properties
```
jdbcauth.driver=com.mysql.jdbc.Driver
jdbcauth.url=jdbc:mysql://192.168.11.140:3306/saiku
jdbcauth.username=root
jdbcauth.password=root
```

> by 刘迎光@萤火虫工作室  
> OpenBI交流群：495266201  
> MicroService 微服务交流群：217722918    
> mail: liuyg#liuyingguang.cn  
> 博主首页（==防止爬虫==）：http://blog.liuyingguang.cn

Lighting@刘迎光

Navigation

Recent Posts

友情链接