Lighting@刘迎光
将saiku3自带的H2嵌入式用户角色数据库迁移到mysql数据库
saiku H2 mysql OpenBI    2017-07-19 21:09:26    930   
lightingfire   saiku H2 mysql OpenBI

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

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

修改web.xml，将原有的与用户相关的h2数据库配置注释

<code class="hljs"><context-<span class="hljs-built_in">param</span>>
        <<span class="hljs-built_in">param</span>-name>db.url</<span class="hljs-built_in">param</span>-name>
        <<span class="hljs-built_in">param</span>-<span class="hljs-built_in">value</span>>jdbc:h2:../../data/saiku;MODE=MySQL</<span class="hljs-built_in">param</span>-<span class="hljs-built_in">value</span>>
    </context-<span class="hljs-built_in">param</span>>
    <context-<span class="hljs-built_in">param</span>>
        <<span class="hljs-built_in">param</span>-name>db.user</<span class="hljs-built_in">param</span>-name>
        <<span class="hljs-built_in">param</span>-<span class="hljs-built_in">value</span>>sa</<span class="hljs-built_in">param</span>-<span class="hljs-built_in">value</span>>
    </context-<span class="hljs-built_in">param</span>>
    <context-<span class="hljs-built_in">param</span>>
        <<span class="hljs-built_in">param</span>-name>db.password</<span class="hljs-built_in">param</span>-name>
        <<span class="hljs-built_in">param</span>-<span class="hljs-built_in">value</span>></<span class="hljs-built_in">param</span>-<span class="hljs-built_in">value</span>>
    </context-<span class="hljs-built_in">param</span>></code>

修改saiku-beans.xml中与用户相关的h2数据库配置

<code class="hljs"><bean id=<span class="hljs-string">"h2database"</span> class=<span class="hljs-string">"org.saiku.database.Database"</span> init-<span class="hljs-keyword">method</span>=<span class="hljs-string">"init"</span>>
        <property name=<span class="hljs-string">"datasourceManager"</span> <span class="hljs-keyword">ref</span>=<span class="hljs-string">"repositoryDsManager"</span>/>
        <property name=<span class="hljs-string">"datasourcetype"</span> value=<span class="hljs-string">"mysql"</span> /><!-- add by liuyg -->
        <property name=<span class="hljs-string">"url"</span> value=<span class="hljs-string">"${userdao.url}"</span> />
        <property name=<span class="hljs-string">"user"</span> value=<span class="hljs-string">"${userdao.username}"</span> />
        <property name=<span class="hljs-string">"pword"</span> value=<span class="hljs-string">"${userdao.password}"</span> />
    </bean></code>

修改Database中的数据源为mysql并添加代码

<code class="hljs"><span class="hljs-keyword">private</span> <span class="hljs-keyword">String</span> url ;
    <span class="hljs-keyword">private</span> <span class="hljs-keyword">String</span> user ;
    <span class="hljs-keyword">private</span> <span class="hljs-keyword">String</span> pword ;
    <span class="hljs-keyword">private</span> <span class="hljs-keyword">String</span> datasourcetype ;
    <span class="hljs-keyword">public</span> <span class="hljs-keyword">String</span> getDatasourcetype() {
        <span class="hljs-keyword">return</span> datasourcetype;
    }

    <span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> setDatasourcetype(<span class="hljs-keyword">String</span> datasourcetype) {
        <span class="hljs-keyword">this</span>.datasourcetype = datasourcetype;
    }
    <span class="hljs-keyword">public</span> <span class="hljs-keyword">String</span> getUrl() {
        <span class="hljs-keyword">return</span> url;
    }

    <span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> setUrl(<span class="hljs-keyword">String</span> url) {
        <span class="hljs-keyword">this</span>.url = url;
    }

    <span class="hljs-keyword">public</span> <span class="hljs-keyword">String</span> getUser() {
        <span class="hljs-keyword">return</span> user;
    }

    <span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> setUser(<span class="hljs-keyword">String</span> user) {
        <span class="hljs-keyword">this</span>.user = user;
    }

    <span class="hljs-keyword">public</span> <span class="hljs-keyword">String</span> getPword() {
        <span class="hljs-keyword">return</span> pword;
    }

    <span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> setPword(<span class="hljs-keyword">String</span> pword) {
        <span class="hljs-keyword">this</span>.pword = pword;
    }</code>

修改initDB方法代码

<code class="hljs"><span class="hljs-keyword">private</span> <span class="hljs-function"><span class="hljs-keyword">void</span> <span class="hljs-title">initDB</span><span class="hljs-params">()</span> </span>{
        ds = <span class="hljs-keyword">new</span> MysqlDataSource();
        ((MysqlDataSource) ds).setUrl(url);
        ((MysqlDataSource) ds).setUser(user);
        ((MysqlDataSource) ds).setPassword(pword);
    }</code>

修改loadUsers方法、checkUpdatedEncyption方法、updateForEncyption方法

<code class="hljs">private <span class="hljs-type">void</span> loadUsers() throws <span class="hljs-type">SQLException</span> {

        <span class="hljs-type">Connection</span> c = ds.getConnection();

        <span class="hljs-type">Statement</span> statement = c.createStatement();

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

        <span class="hljs-type">ResultSet</span> <span class="hljs-literal">result</span> = statement.executeQuery(<span class="hljs-string">"select count(*) as c from log where log = 'insert users'"</span>);

        <span class="hljs-literal">result</span>.next();

        <span class="hljs-keyword">if</span> (<span class="hljs-literal">result</span>.getInt(<span class="hljs-string">"c"</span>) == <span class="hljs-number">0</span>) {

            statement.execute(<span class="hljs-string">"INSERT INTO users (username,password,email, enabled) VALUES ('admin','admin', 'test@admin.com',TRUE);"</span>);
            statement.execute(<span class="hljs-string">"INSERT INTO users (username,password,enabled) VALUES ('smith','smith', TRUE);"</span>);
            statement.execute(<span class="hljs-string">"INSERT INTO user_roles (user_id, username, ROLE) VALUES (1, 'admin', 'ROLE_USER');"</span>);
            statement.execute(<span class="hljs-string">"INSERT INTO user_roles (user_id, username, ROLE) VALUES (1, 'admin', 'ROLE_ADMIN');"</span>);
            statement.execute(<span class="hljs-string">"INSERT INTO user_roles (user_id, username, ROLE) VALUES (2, 'smith', 'ROLE_USER');"</span>);
            statement.execute(<span class="hljs-string">"INSERT INTO log (log) VALUES('insert users');"</span>);
        }

        <span class="hljs-type">String</span> encrypt = servletContext.getInitParameter(<span class="hljs-string">"db.encryptpassword"</span>);
        <span class="hljs-keyword">if</span> (encrypt.equals(<span class="hljs-string">"true"</span>) && !checkUpdatedEncyption()) {
            updateForEncyption();
        }
    }


    public boolean checkUpdatedEncyption() throws <span class="hljs-type">SQLException</span>{
        <span class="hljs-type">Connection</span> c = ds.getConnection();
        <span class="hljs-type">Statement</span> statement = c.createStatement();
        <span class="hljs-type">ResultSet</span> <span class="hljs-literal">result</span> = statement.executeQuery(<span class="hljs-string">"select count(*) as c from log where log = 'update passwords'"</span>);
        <span class="hljs-literal">result</span>.next();
        <span class="hljs-keyword">return</span> <span class="hljs-literal">result</span>.getInt(<span class="hljs-string">"c"</span>) != <span class="hljs-number">0</span>
    }

    public <span class="hljs-type">void</span> updateForEncyption() throws <span class="hljs-type">SQLException</span> {

        <span class="hljs-type">Connection</span> c = ds.getConnection();
        <span class="hljs-type">Statement</span> statement = c.createStatement();
        statement.execute(<span class="hljs-string">"ALTER TABLE users MODIFY COLUMN PASSWORD VARCHAR(100) DEFAULT NULL"</span>);
        <span class="hljs-type">ResultSet</span> <span class="hljs-literal">result</span> = statement.executeQuery(<span class="hljs-string">"select username, password from users"</span>);
        <span class="hljs-keyword">while</span> (<span class="hljs-literal">result</span>.next()) {
            statement = c.createStatement();
            <span class="hljs-type">String</span> pword = <span class="hljs-literal">result</span>.getString(<span class="hljs-string">"password"</span>);
            <span class="hljs-type">String</span> hashedPassword = passwordEncoder.encode(pword);
            <span class="hljs-type">String</span> sql = <span class="hljs-string">"UPDATE users "</span> + <span class="hljs-string">"SET password = '"</span> + hashedPassword
                    + <span class="hljs-string">"' WHERE username = '"</span> + <span class="hljs-literal">result</span>.getString(<span class="hljs-string">"username"</span>)
                    + <span class="hljs-string">"'"</span>
            statement.executeUpdate(sql);
        }
        statement = c.createStatement();
        statement.execute(<span class="hljs-string">"INSERT INTO log (log) VALUES('update passwords');"</span>);
    }</code>

修改数据源配置

文件：saiku-beans.properties

<code class="hljs">userdao.driverclass=com<span class="hljs-class">.mysql</span><span class="hljs-class">.jdbc</span><span class="hljs-class">.Driver</span>
userdao.url=jdbc:mysql:<span class="hljs-comment">//192.168.11.140:3306/saiku</span>
userdao.username=root
userdao.password=root</code>

文件：applicationContext-spring-security-jdbc.properties

<code class="hljs">jdbcauth.driver=com<span class="hljs-class">.mysql</span><span class="hljs-class">.jdbc</span><span class="hljs-class">.Driver</span>
jdbcauth.url=jdbc:mysql:<span class="hljs-comment">//192.168.11.140:3306/saiku</span>
jdbcauth.username=root
jdbcauth.password=root</code>

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

Pre: saiku3二次开发pom文件

Next: saiku （branch 3.8-release）构建步骤

Copyright©2016 - 2021   沪ICP备17020857号-1    
Proudly powered by LightingLYG
Table of content