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

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

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

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

  1. <code class="hljs"><context-<span class="hljs-built_in">param</span>>
  2.         <<span class="hljs-built_in">param</span>-name>db.url</<span class="hljs-built_in">param</span>-name>
  3.         <<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>>
  4.     </context-<span class="hljs-built_in">param</span>>
  5.     <context-<span class="hljs-built_in">param</span>>
  6.         <<span class="hljs-built_in">param</span>-name>db.user</<span class="hljs-built_in">param</span>-name>
  7.         <<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>>
  8.     </context-<span class="hljs-built_in">param</span>>
  9.     <context-<span class="hljs-built_in">param</span>>
  10.         <<span class="hljs-built_in">param</span>-name>db.password</<span class="hljs-built_in">param</span>-name>
  11.         <<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>>
  12.     </context-<span class="hljs-built_in">param</span>></code>

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

  1. <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>>
  2.         <property name=<span class="hljs-string">"datasourceManager"</span> <span class="hljs-keyword">ref</span>=<span class="hljs-string">"repositoryDsManager"</span>/>
  3.         <property name=<span class="hljs-string">"datasourcetype"</span> value=<span class="hljs-string">"mysql"</span> /><!-- add by liuyg -->
  4.         <property name=<span class="hljs-string">"url"</span> value=<span class="hljs-string">"${userdao.url}"</span> />
  5.         <property name=<span class="hljs-string">"user"</span> value=<span class="hljs-string">"${userdao.username}"</span> />
  6.         <property name=<span class="hljs-string">"pword"</span> value=<span class="hljs-string">"${userdao.password}"</span> />
  7.     </bean></code>

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

  1. <code class="hljs"><span class="hljs-keyword">private</span> <span class="hljs-keyword">String</span> url ;
  2.     <span class="hljs-keyword">private</span> <span class="hljs-keyword">String</span> user ;
  3.     <span class="hljs-keyword">private</span> <span class="hljs-keyword">String</span> pword ;
  4.     <span class="hljs-keyword">private</span> <span class="hljs-keyword">String</span> datasourcetype ;
  5.     <span class="hljs-keyword">public</span> <span class="hljs-keyword">String</span> getDatasourcetype() {
  6.         <span class="hljs-keyword">return</span> datasourcetype;
  7.     }
  8.  
  9.     <span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> setDatasourcetype(<span class="hljs-keyword">String</span> datasourcetype) {
  10.         <span class="hljs-keyword">this</span>.datasourcetype = datasourcetype;
  11.     }
  12.     <span class="hljs-keyword">public</span> <span class="hljs-keyword">String</span> getUrl() {
  13.         <span class="hljs-keyword">return</span> url;
  14.     }
  15.  
  16.     <span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> setUrl(<span class="hljs-keyword">String</span> url) {
  17.         <span class="hljs-keyword">this</span>.url = url;
  18.     }
  19.  
  20.     <span class="hljs-keyword">public</span> <span class="hljs-keyword">String</span> getUser() {
  21.         <span class="hljs-keyword">return</span> user;
  22.     }
  23.  
  24.     <span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> setUser(<span class="hljs-keyword">String</span> user) {
  25.         <span class="hljs-keyword">this</span>.user = user;
  26.     }
  27.  
  28.     <span class="hljs-keyword">public</span> <span class="hljs-keyword">String</span> getPword() {
  29.         <span class="hljs-keyword">return</span> pword;
  30.     }
  31.  
  32.     <span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> setPword(<span class="hljs-keyword">String</span> pword) {
  33.         <span class="hljs-keyword">this</span>.pword = pword;
  34.     }</code>

修改initDB方法代码

  1. <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>{
  2.         ds = <span class="hljs-keyword">new</span> MysqlDataSource();
  3.         ((MysqlDataSource) ds).setUrl(url);
  4.         ((MysqlDataSource) ds).setUser(user);
  5.         ((MysqlDataSource) ds).setPassword(pword);
  6.     }</code>

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

  1. <code class="hljs">private <span class="hljs-type">void</span> loadUsers() throws <span class="hljs-type">SQLException</span> {
  2.  
  3.         <span class="hljs-type">Connection</span> c = ds.getConnection();
  4.  
  5.         <span class="hljs-type">Statement</span> statement = c.createStatement();
  6.  
  7.         statement.execute(<span class="hljs-string">" CREATE TABLE IF NOT EXISTS log ( time  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, log  TEXT); "</span>);
  8.         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>);
  9.         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>);
  10.  
  11.         <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>);
  12.  
  13.         <span class="hljs-literal">result</span>.next();
  14.  
  15.         <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>) {
  16.  
  17.             statement.execute(<span class="hljs-string">"INSERT INTO users (username,password,email, enabled) VALUES ('admin','admin', 'test@admin.com',TRUE);"</span>);
  18.             statement.execute(<span class="hljs-string">"INSERT INTO users (username,password,enabled) VALUES ('smith','smith', TRUE);"</span>);
  19.             statement.execute(<span class="hljs-string">"INSERT INTO user_roles (user_id, username, ROLE) VALUES (1, 'admin', 'ROLE_USER');"</span>);
  20.             statement.execute(<span class="hljs-string">"INSERT INTO user_roles (user_id, username, ROLE) VALUES (1, 'admin', 'ROLE_ADMIN');"</span>);
  21.             statement.execute(<span class="hljs-string">"INSERT INTO user_roles (user_id, username, ROLE) VALUES (2, 'smith', 'ROLE_USER');"</span>);
  22.             statement.execute(<span class="hljs-string">"INSERT INTO log (log) VALUES('insert users');"</span>);
  23.         }
  24.  
  25.         <span class="hljs-type">String</span> encrypt = servletContext.getInitParameter(<span class="hljs-string">"db.encryptpassword"</span>);
  26.         <span class="hljs-keyword">if</span> (encrypt.equals(<span class="hljs-string">"true"</span>) && !checkUpdatedEncyption()) {
  27.             updateForEncyption();
  28.         }
  29.     }
  30.  
  31.  
  32.     public boolean checkUpdatedEncyption() throws <span class="hljs-type">SQLException</span>{
  33.         <span class="hljs-type">Connection</span> c = ds.getConnection();
  34.         <span class="hljs-type">Statement</span> statement = c.createStatement();
  35.         <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>);
  36.         <span class="hljs-literal">result</span>.next();
  37.         <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>
  38.     }
  39.  
  40.     public <span class="hljs-type">void</span> updateForEncyption() throws <span class="hljs-type">SQLException</span> {
  41.  
  42.         <span class="hljs-type">Connection</span> c = ds.getConnection();
  43.         <span class="hljs-type">Statement</span> statement = c.createStatement();
  44.         statement.execute(<span class="hljs-string">"ALTER TABLE users MODIFY COLUMN PASSWORD VARCHAR(100) DEFAULT NULL"</span>);
  45.         <span class="hljs-type">ResultSet</span> <span class="hljs-literal">result</span> = statement.executeQuery(<span class="hljs-string">"select username, password from users"</span>);
  46.         <span class="hljs-keyword">while</span> (<span class="hljs-literal">result</span>.next()) {
  47.             statement = c.createStatement();
  48.             <span class="hljs-type">String</span> pword = <span class="hljs-literal">result</span>.getString(<span class="hljs-string">"password"</span>);
  49.             <span class="hljs-type">String</span> hashedPassword = passwordEncoder.encode(pword);
  50.             <span class="hljs-type">String</span> sql = <span class="hljs-string">"UPDATE users "</span> + <span class="hljs-string">"SET password = '"</span> + hashedPassword
  51.                     + <span class="hljs-string">"' WHERE username = '"</span> + <span class="hljs-literal">result</span>.getString(<span class="hljs-string">"username"</span>)
  52.                     + <span class="hljs-string">"'"</span>
  53.             statement.executeUpdate(sql);
  54.         }
  55.         statement = c.createStatement();
  56.         statement.execute(<span class="hljs-string">"INSERT INTO log (log) VALUES('update passwords');"</span>);
  57.     }</code>

修改数据源配置

文件：saiku-beans.properties

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

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

  1. <code class="hljs">jdbcauth.driver=com<span class="hljs-class">.mysql</span><span class="hljs-class">.jdbc</span><span class="hljs-class">.Driver</span>
  2. jdbcauth.url=jdbc:mysql:<span class="hljs-comment">//192.168.11.140:3306/saiku</span>
  3. jdbcauth.username=root
  4. 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