saiku由H2改入Mysql

最新推荐文章于 2021-02-11 09:04:17 发布

拱最好的白菜

最新推荐文章于 2021-02-11 09:04:17 发布

阅读量2.3k

点赞数

分类专栏： saiku项目经验文章标签： saiku 多维分析

本文链接：https://blog.csdn.net/zstao168/article/details/52224308

版权

saiku项目经验专栏收录该内容

6 篇文章 0 订阅

订阅专栏

saiku社区版的zip包中自带有H2的数据库，该库中主要维护着saiku账户信息（用户名、密码、邮箱、角色等等），而角色通过配置schema可以控制用户可访问到的cube的权限；由于H2数据库维护起来不如mysql方便，而且后期还要集成公司单点系统，所以就要mysql替换H2，当然网上也有很多的解决方法，笔者这一方案与之也有类似之处，在这里记录供网友参考，本方案是经过笔者反复测试验证的，本方案的大致步骤如下：

修改web.xml、saiku-bean.properties、applicationContext-spring-security-jdbc.properties等配置文件中的数据库连接信息

修改Database().initDB() (可以把foodmart、earthquake等无关代码注释)

修改database-queries.properties中sql为mysql语法格式的

修改数据源配置

首先，将此web.xml、 saiku-bean.properties、 applicationContext-spring-security-jdbc.properties三个文件中的数据库连接信息修改为mysql的， web.xml中的配置在Database.java中用到，在此类中，系统会连接mysql，并主动在mysql中创建3张表log、users、user_roles详情参见Database. loadUsers()方法；applicationContext-spring-security-jdbc.properties中连接信息在applicationContext-spring-security-jdbc.xml中用于创建 org.springframework.security.core.userdetails.jdbc.JdbcDaoImpl，该类用于用户登陆认证时；saiku-bean.properties中连接信息在saiku-bean.xml中创建h2database，可以在此文件中看到用于构建licenseUtil；由于此部分修改信息比较简单，笔者就不再上图了；

修改Database.java

package org.saiku.database;

import java.io.IOException;
import java.nio.charset.Charset;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

import javax.servlet.ServletContext;

import org.saiku.service.datasource.IDatasourceManager;
import org.saiku.service.importer.LegacyImporter;
import org.saiku.service.importer.LegacyImporterImpl;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.security.crypto.bcrypt.BCryptPasswordEncoder;

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



public class Database2 {

    @Autowired
    ServletContext servletContext;

    //由DataSource改为MysqlDataSource
    private MysqlDataSource ds;
    private static final Logger log = LoggerFactory.getLogger(Database2.class);
    private final BCryptPasswordEncoder passwordEncoder = new BCryptPasswordEncoder();
    private IDatasourceManager dsm;
    
    
    public Database2() {

    }

    public void setDatasourceManager(IDatasourceManager dsm) {
        this.dsm = dsm;
    }

    public ServletContext getServletContext() {
        return servletContext;
    }

    public void setServletContext(ServletContext servletContext) {
        this.servletContext = servletContext;
    }

    public void init() throws SQLException {
        initDB();
        loadUsers();
        //笔者将这些日后没用的都注释了
//        loadFoodmart();
//        loadEarthquakes();
//        loadLegacyDatasources();
    }

    private void initDB() {
    	//获取web.xml中的mysql配置信息
    	String url = servletContext.getInitParameter("db.url");
        String user = servletContext.getInitParameter("db.user");
        String pword = servletContext.getInitParameter("db.password");
    	ds = new MysqlDataSource();
        ds.setUrl(url);
        ds.setUser(user);
        ds.setPassword(pword);	
    	
    }

    private void loadFoodmart() throws SQLException {

   }

    private void loadEarthquakes() throws SQLException {

   }

    private static String readFile(String path, Charset encoding)
            throws IOException
    {
        byte[] encoded = Files.readAllBytes(Paths.get(path));
        return new String(encoded, encoding);
    }
    
    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');");
    }
    
    
    private void loadLegacyDatasources() throws SQLException {
        Connection c = ds.getConnection();

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

        result.next();
        if (result.getInt("c") == 0) {
            LegacyImporter l = new LegacyImporterImpl(dsm);
            l.importSchema();
            l.importDatasources();
            statement.execute("INSERT INTO LOG(log) VALUES('insert datasources');");

        }
    }


    public List<String> getUsers() throws java.sql.SQLException
    {
        //Stub for EE.
        return null;
    }

    public void addUsers(List<String> l) throws java.sql.SQLException
    {
        //Stub for EE.
    }
}

修改sql语句

将database-queries.properties、applicationContext-spring-security-jdbc.properties中的sql都修改为mysql中的语法，不然会报错；

以上便是修改整个过程，下面是一网友的解决方案，供参考：

http://blog.csdn.net/gsying1474/article/details/51675235