saiku+kettle整合(十三)替换h2数据库

本文介绍如何将Saiku的数据分析平台与Kettle的数据集成工具进行整合,并详细讲解了从使用H2数据库到更换为MySQL数据库的过程。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >


title: saiku+kettle整合(十三)替换h2数据库 tags:

  • OLAP categories: saiku date: 2016-08-25 18:18:54

替换h2数据库

saiku默认使用h2数据库作为用户的储存。到了生产上替换成mysql吧。

    Index: saiku-core/saiku-service/src/main/java/org/saiku/database/Database.java
    IDEA additional info:
    Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
    <+>UTF-8
    ===================================================================
    --- saiku-core/saiku-service/src/main/java/org/saiku/database/Database.java	(revision 783e78ca132d62452435514a5d76662aec9db8af)
    +++ saiku-core/saiku-service/src/main/java/org/saiku/database/Database.java	(revision 3c1304562ddce613858d89d856e1045aa1cd7975)
    @@ -1,36 +1,30 @@
     package org.saiku.database;
     
     import org.apache.commons.io.FileUtils;
    -
    +import org.h2.jdbcx.JdbcDataSource;
     import org.saiku.datasources.datasource.SaikuDatasource;
     import org.saiku.service.datasource.IDatasourceManager;
     import org.saiku.service.importer.LegacyImporter;
     import org.saiku.service.importer.LegacyImporterImpl;
    -
    -import org.h2.jdbcx.JdbcDataSource;
     import org.slf4j.Logger;
     import org.slf4j.LoggerFactory;
     import org.springframework.beans.factory.annotation.Autowired;
     import org.springframework.security.crypto.bcrypt.BCryptPasswordEncoder;
     
    +import javax.servlet.ServletContext;
    +import javax.sql.DataSource;
     import java.io.File;
     import java.io.IOException;
     import java.nio.charset.Charset;
     import java.nio.charset.StandardCharsets;
     import java.nio.file.Files;
     import java.nio.file.Paths;
    -import java.sql.Connection;
    -import java.sql.DatabaseMetaData;
    -import java.sql.ResultSet;
    -import java.sql.SQLException;
    -import java.sql.Statement;
    +import java.sql.*;
     import java.util.Iterator;
     import java.util.List;
     import java.util.Properties;
     
    -import javax.servlet.ServletContext;
     
    -
     /**
      * Created by bugg on 01/05/14.
      */
    @@ -39,12 +33,12 @@
         @Autowired
         ServletContext servletContext;
     
    -    private JdbcDataSource ds;
    +    private DataSource ds;
         private static final Logger log = LoggerFactory.getLogger(Database.class);
         private final BCryptPasswordEncoder passwordEncoder = new BCryptPasswordEncoder();
         private IDatasourceManager dsm;
    -    public Database() {
    -
    +    public Database(DataSource ds) {
    +      this.ds=ds;
         }
     
         public void setDatasourceManager(IDatasourceManager dsm) {
    @@ -60,22 +54,12 @@
         }
     
         public void init() throws SQLException {
    -        initDB();
             loadUsers();
     //        loadFoodmart();
     //        loadEarthquakes();
             loadLegacyDatasources();
         }
     
    -    private void initDB() {
    -        String url = servletContext.getInitParameter("db.url");
    -        String user = servletContext.getInitParameter("db.user");
    -        String pword = servletContext.getInitParameter("db.password");
    -        ds = new JdbcDataSource();
    -        ds.setURL(url);
    -        ds.setUser(user);
    -        ds.setPassword(pword);
    -    }
     
         private void loadFoodmart() throws SQLException {
             String url = servletContext.getInitParameter("foodmart.url");
    @@ -224,7 +208,7 @@
             Connection c = ds.getConnection();
     
             Statement statement = c.createStatement();
    -        statement.execute("CREATE TABLE IF NOT EXISTS LOG(time TIMESTAMP AS CURRENT_TIMESTAMP NOT NULL, log CLOB);");
    +        statement.execute("CREATE TABLE IF NOT EXISTS LOG(time TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, 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), " +
    @@ -242,16 +226,16 @@
                 dsm.createUser("admin");
                 dsm.createUser("smith");
                 statement.execute("INSERT INTO users(username,password,email, enabled)\n"
    -                    + "VALUES ('admin','admin', 'test@admin.com',TRUE);" +
    -                    "INSERT INTO users(username,password,enabled)\n"
    +                    + "VALUES ('admin','admin', 'test@admin.com',TRUE);");
    +            statement.execute("INSERT INTO users(username,password,enabled)\n"
                         + "VALUES ('smith','smith', TRUE);");
                 statement.execute(
                         "INSERT INTO user_roles (user_id, username, ROLE)\n"
    -                            + "VALUES (1, 'admin', 'ROLE_USER');" +
    -                            "INSERT INTO user_roles (user_id, username, ROLE)\n"
    -                            + "VALUES (1, 'admin', 'ROLE_ADMIN');" +
    -                            "INSERT INTO user_roles (user_id, username, ROLE)\n"
    +                            + "VALUES (1, 'admin', 'ROLE_USER');");
    +            statement.execute("INSERT INTO user_roles (user_id, username, ROLE)\n"
    +                    + "VALUES (1, 'admin', 'ROLE_ADMIN');");
    +            statement.execute("INSERT INTO user_roles (user_id, username, ROLE)\n"
    -                            + "VALUES (2, 'smith', 'ROLE_USER');");
    +                    + "VALUES (2, 'smith', 'ROLE_USER');");
     
                 statement.execute("INSERT INTO LOG(log) VALUES('insert users');");
             }
    @@ -278,7 +262,7 @@
             Connection c = ds.getConnection();
     
             Statement statement = c.createStatement();
    -        statement.execute("ALTER TABLE users ALTER COLUMN password VARCHAR(100) DEFAULT NULL");
    +        statement.execute("ALTER TABLE users MODIFY COLUMN password VARCHAR(100) DEFAULT NULL");
     
             ResultSet result = statement.executeQuery("select username, password from users");
     
    Index: saiku-webapp/src/main/webapp/WEB-INF/applicationContext-spring-security-jdbc.properties
    IDEA additional info:
    Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
    <+>UTF-8
    ===================================================================
    --- saiku-webapp/src/main/webapp/WEB-INF/applicationContext-spring-security-jdbc.properties	(revision 783e78ca132d62452435514a5d76662aec9db8af)
    +++ saiku-webapp/src/main/webapp/WEB-INF/applicationContext-spring-security-jdbc.properties	(revision 3c1304562ddce613858d89d856e1045aa1cd7975)
    @@ -3,7 +3,7 @@
     jdbcauth.usernamequery=select u.username,u.password, u.enabled from users u inner join ( \
                        select MAX(USERS.USER_ID) ID, USERS.USERNAME from USERS group by USERS.USERNAME) tm on u.USER_ID =\
                        tm.ID where u.username = ? GROUP BY u.USER_ID
    -jdbcauth.driver=org.h2.Driver
    -jdbcauth.url=jdbc:h2:/Users/qixiaobo/work/saiku/saiku-webapp/target/saiku-webapp-3.8-RC5/data//saiku;MODE=MySQL
    -jdbcauth.username=sa
    +jdbcauth.driver=com.mysql.jdbc.Driver
    +jdbcauth.url=jdbc:mysql://localhost:3306/saiku
    +jdbcauth.username=root
     jdbcauth.password=
    \ No newline at end of file
    Index: saiku-webapp/src/main/webapp/WEB-INF/saiku-beans.properties
    IDEA additional info:
    Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
    <+>UTF-8
    ===================================================================
    --- saiku-webapp/src/main/webapp/WEB-INF/saiku-beans.properties	(revision 783e78ca132d62452435514a5d76662aec9db8af)
    +++ saiku-webapp/src/main/webapp/WEB-INF/saiku-beans.properties	(revision 3c1304562ddce613858d89d856e1045aa1cd7975)
    @@ -1,9 +1,9 @@
     default.role=ROLE_USER
     external.properties.file=${catalina.base}/conf/Catalina/localhost/datasources.properties
     webdav.password=sa!kuanalyt!cs
    -userdao.driverclass=org.h2.Driver
    -userdao.url=jdbc:h2:/Users/qixiaobo/work/saiku/saiku-webapp/target/saiku-webapp-3.8-RC5/data/saiku;MODE=MySQL
    -userdao.username=sa
    +userdao.driverclass=com.mysql.jdbc.Driver
    +userdao.url=jdbc:mysql://localhost:3306/saiku
    +userdao.username=root
     userdao.password=
     logdir=../logs
     repoconfig=/Users/qixiaobo/work/saiku/saiku-webapp/target/saiku-webapp-3.8-RC5/repository/configuration.xml
    @@ -15,3 +15,4 @@
     earthquakeschema=/Users/qixiaobo/work/saiku/saiku-webapp/target/saiku-webapp-3.8-RC5/data/Earthquakes.xml
     earthquakeurl=jdbc:h2:/Users/qixiaobo/work/saiku/saiku-webapp/target/saiku-webapp-3.8-RC5/data/earthquakes;MODE=MySQL
     pluginpath=/Users/qixiaobo/work/saiku/saiku-webapp/target/saiku-webapp-3.8-RC5/js/saiku/plugins/
    +
    Index: saiku-webapp/src/main/webapp/WEB-INF/saiku-beans.xml
    IDEA additional info:
    Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
    <+>UTF-8
    ===================================================================
    --- saiku-webapp/src/main/webapp/WEB-INF/saiku-beans.xml	(revision 783e78ca132d62452435514a5d76662aec9db8af)
    +++ saiku-webapp/src/main/webapp/WEB-INF/saiku-beans.xml	(revision 3c1304562ddce613858d89d856e1045aa1cd7975)
    @@ -41,8 +41,14 @@
         <bean id="repoPasswordProviderBean" class="org.saiku.service.util.security.authentication.SimplePasswordProvider">
             <constructor-arg index="0" value="${webdav.password}"/>
         </bean>
    -
    -    <bean id="h2database" class="org.saiku.database.Database" init-method="init">
    +   <bean id="dataSource" class="com.mysql.jdbc.jdbc2.optional.MysqlDataSource">
    +       <property name="password" value="${userdao.password}"/>
    +       <property name="user" value="${userdao.username}"/>
    +       <property name="url" value="${userdao.url}"/>
    +       <property name="encoding" value="UTF-8"/>
    +   </bean>
    +    <bean id="database" class="org.saiku.database.Database" init-method="init">
    +        <constructor-arg ref="dataSource"/>
             <property name="datasourceManager" ref="repositoryDsManager"/>
         </bean>
     
    @@ -176,7 +182,7 @@
     
         <bean id="licenseUtils" class="org.saiku.LicenseUtils"/>
         <bean id="licenseBean" class="org.saiku.web.rest.resources.License">
    -        <property name="databaseManager" ref="h2database"/>
    +        <property name="databaseManager" ref="database"/>
             <property name="licenseUtils" ref="licenseUtils"/>
             <property name="userService" ref="userServiceBean"/>
         </bean>
    Index: saiku-webapp/src/main/webapp/WEB-INF/web.xml
    IDEA additional info:
    Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
    <+>UTF-8
    ===================================================================
    --- saiku-webapp/src/main/webapp/WEB-INF/web.xml	(revision 783e78ca132d62452435514a5d76662aec9db8af)
    +++ saiku-webapp/src/main/webapp/WEB-INF/web.xml	(revision 3c1304562ddce613858d89d856e1045aa1cd7975)
    @@ -11,46 +11,10 @@
     		<param-value>UTF-8</param-value>
     	</context-param>
         <context-param>
    -        <param-name>db.url</param-name>
    -        <param-value>jdbc:h2:jdbc:h2:/Users/qixiaobo/work/saiku/saiku-webapp/target/saiku-webapp-3.8-RC5/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>
    -    <context-param>
             <param-name>db.encryptpassword</param-name>
             <param-value>true</param-value>
         </context-param>
         <context-param>
    -        <param-name>foodmart.url</param-name>
    -        <param-value>jdbc:h2:/Users/qixiaobo/work/saiku/saiku-webapp/target/saiku-webapp-3.8-RC5/data//foodmart;MODE=MySQL</param-value>
    -    </context-param>
    -    <context-param>
    -        <param-name>foodmart.user</param-name>
    -        <param-value>sa</param-value>
    -    </context-param>
    -    <context-param>
    -        <param-name>foodmart.password</param-name>
    -        <param-value></param-value>
    -    </context-param>
    -    <context-param>
    -        <param-name>earthquakes.url</param-name>
    -        <param-value>jdbc:h2:/Users/qixiaobo/work/saiku/saiku-webapp/target/saiku-webapp-3.8-RC5/data//earthquakes;MODE=MySQL</param-value>
    -    </context-param>
    -    <context-param>
    -        <param-name>earthquakes.user</param-name>
    -        <param-value>sa</param-value>
    -    </context-param>
    -    <context-param>
    -        <param-name>earthquakes.password</param-name>
    -        <param-value></param-value>
    -    </context-param>
    -    <context-param>
             <param-name>db.tcpServer</param-name>
             <param-value>-tcpAllowOthers</param-value>
         </context-param>
    @@ -160,25 +124,6 @@
             <url-pattern>/rmi</url-pattern>
         </servlet-mapping>-->
     
    -    <servlet>
    -        <servlet-name>H2Console</servlet-name>
    -        <servlet-class>org.h2.server.web.WebServlet</servlet-class>
    -        <!--
    -        <init-param>
    -            <param-name>webAllowOthers</param-name>
    -            <param-value></param-value>
    -        </init-param>
    -        <init-param>
    -            <param-name>trace</param-name>
    -            <param-value></param-value>
    -        </init-param>
    -        -->
    -        <load-on-startup>1</load-on-startup>
    -    </servlet>
    -    <servlet-mapping>
    -        <servlet-name>H2Console</servlet-name>
    -        <url-pattern>/console/*</url-pattern>
    -    </servlet-mapping>
     
     	<servlet>
         	<servlet-name>jersey2</servlet-name>
复制代码

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值