Multi Schemas in Seasar

Config multi-database in an Web Application using Seasar framework

  1. Switch data source

    a. Create jdbc-foo.dicon

    in resources dir for each schema
    <?xml version="1.0" encoding="UTF-8"?>
    <!DOCTYPE components PUBLIC "-//SEASAR//DTD S2Container 2.4//EN" "http://www.seasar.org/dtd/components24.dtd">
      <components namespace="jdbc">
        <include path="jta.dicon"/>
        <component name="xaDataSource" class="org.seasar.extension.dbcp.impl.XADataSourceImpl">
        <property name="driverClassName">"com.mysql.jdbc.Driver"</property>
        <property name="URL">"jdbc:mysql://localhost:3306/foo?characterEncoding=utf8"</property>
        <property name="user">"root"</property>
        <property name="password">"root"</property>
      </component>
      <component name="connectionPool" class="org.seasar.extension.dbcp.impl.ConnectionPoolImpl">
        <property name="timeout">600</property>
        <property name="maxPoolSize">10</property>
        <property name="allowLocalTx">true</property>
        <destroyMethod name="close"/>
      </component>
      <component name="fooDataSource" class="org.seasar.extension.dbcp.impl.DataSourceImpl"/>
    </components>
    

    Notes:

    • A schema name (ex:foo) may be different from component name(ex:fooDataSource).
    • A component name must be ... + "DataSource".

    b. Loading all dicon files "jdbc-foo.dicon" in "jdbc-all.dicon"

    <?xml version="1.0" encoding="UTF-8"?>
    <!DOCTYPE components PUBLIC "-//SEASAR//DTD S2Container 2.4//EN" "http://www.seasar.org/dtd/components24.dtd">
      <components>
        <include path="jdbc-foo.dicon"/>
        <include path="jdbc-baz1.dicon"/>
        <include path="jdbc-baz2.dicon"/>
        <component name="dataSource" class="org.seasar.extension.datasource.impl.SelectableDataSourceProxy"/>
        <component name="dataSourceFactory" class="org.seasar.extension.datasource.impl.DataSourceFactoryImpl"/>
      </components>
    

    c. Load "jdbc-all.dicon" in "s2jdbc.dicon"

    <?xml version="1.0" encoding="UTF-8"?>
    <!DOCTYPE components PUBLIC "-//SEASAR//DTD S2Container 2.4//EN" "http://www.seasar.org/dtd/components24.dtd">
      <components>
        <include path="jdbc-all.dicon"/>
        <include path="s2jdbc-internal.dicon"/>
        <component name="jdbcManager" class="org.seasar.extension.jdbc.manager.JdbcManagerImpl">
          <property name="maxRows">0</property>
          <property name="fetchSize">0</property>
          <property name="queryTimeout">0</property>
          <property name="dialect">mysqlDialect</property>
        </component>
      </components>
    

    d. Switching datasource - Use injected "DataSourceFactory"

    • Ready for injection
      @Resource
      DataSourceFactory dataSourceFactory;
      
    • Switch!
      dataSourceFactory.setSelectableDataSourceName("foo");
      

      Notes: Switching name is "foo" not "fooDataSource".

  2. Create DDL(SQL) from entities for multi-schemas

    a. Create entities

    • Split entity package follow schema type. Ex: common and client1, 2, 3....
    • Create entity.common and entity.client packages.
    ├── src
    │   ├── main
    │   │   ├── java
    │   │   │   └── vn
    │   │   │       └── com
    │   │   │           ├── coloza
    │   │   │           └── colozasample
    │   │   │               └── multischema
    │   │   │                   ├── action
    │   │   │                   ├── annotation
    │   │   │                   ├── catalog
    │   │   │                   ├── dao
    │   │   │                   │   ├── AbstractDao.java
    │   │   │                   │   ├── ClientSampleDao.java
    │   │   │                   │   └── CommonSampleDao.java
    │   │   │                   ├── dto
    │   │   │                   ├── entity
    │   │   │                   │   ├── AbstractEntity.java
    │   │   │                   │   ├── ClientSampleNames.java
    │   │   │                   │   ├── CommonSampleNames.java
    │   │   │                   │   ├── Names.java
    │   │   │                   │   ├── client
    │   │   │                   │   │   ├── AbstractClientSample.java
    │   │   │                   │   │   └── ClientSample.java             <---------- Here
    │   │   │                   │   └── common
    │   │   │                   │       ├── AbstractCommonSample.java
    │   │   │                   │       └── CommonSample.java             <---------- Here
    

    Notes: Even different package, the entities can't be same name.

    b. Make dir for save DDL

    Make 2 dir for DDL stored

    ├── db
    │   ├── client
    │   │   ├── ddl-info-0001.txt
    │   │   ├── ddl-info.txt
    │   │   └── migrate
    │   │       └── 0001
    │   │           ├── create
    │   │           │   ├── 010-table
    │   │           │   ├── 039-basicdata
    │   │           │   └── 062-view
    │   │           └── drop
    │   │               └── 040-table
    │   └── common
    │       ├── ddl-info-0001.txt
    │       ├── ddl-info.txt
    │       └── migrate
    │           └── 0001
    │               ├── create
    │               │   ├── 010-table
    │               │   ├── 039-basicdata
    │               │   └── 062-view
    │               └── drop
    │                   └── 040-table
    

    c. Create dicon file for gen-ddl

    ├── src
    │   ├── main
    │   │   ├── java
    │   │   ├── resources
    │   │   │   ├── app.dicon
    │   │   │   ├── application.properties
    │   │   │   ├── config.dicon
    │   │   │   ├── convention.dicon
    │   │   │   ├── creator.dicon
    │   │   │   ├── customizer.dicon
    │   │   │   ├── env.txt
    │   │   │   ├── env_ut.txt
    │   │   │   ├── gen
    │   │   │   │   ├── s2jdbc-client1.dicon   <---------- Here
    │   │   │   │   ├── s2jdbc-client2.dicon
    │   │   │   │   └── s2jdbc-common.dicon
    

    s2jdbc-client1.dicon

    <?xml version="1.0" encoding="UTF-8"?>
    <!DOCTYPE components PUBLIC "-//SEASAR//DTD S2Container 2.4//EN" "http://www.seasar.org/dtd/components24.dtd">
      <components>
        <include path="jdbc-client1.dicon"/>
        <include path="s2jdbc-internal.dicon"/>
        <component name="jdbcManager" class="org.seasar.extension.jdbc.manager.JdbcManagerImpl">
          <property name="maxRows">0</property>
          <property name="fetchSize">0</property>
          <property name="queryTimeout">0</property>
          <property name="dialect">mysqlDialect</property>
        </component>
    </components>
    

    d. Edit s2jdbc-gen-build.xml

    <target name="gen-ddl">
      <gen-ddl
        classpathdir="${classpathdir}"
        rootpackagename="${rootpackagename}"
        entitypackagename="${entitypackagename}.common"
        env="${env}"
        jdbcmanagername="${jdbcmanagername}"
        classpathref="classpath"
        autogenerateforeignkey="false"
        dump="false"
        migrateDir="db/common/migrate"
        ddlinfofile="db/common/ddl-info.txt"
        ignoreEntityClassNamePattern=".*View$"
        templatefileprimarydir="templates"
        configpath="gen/s2jdbc-common.dicon">
        <jvmarg value="${vmarg.encoding}" />
      </gen-ddl>
      <delete dir="db/common/migrate/0001" />
      <move file="db/common/migrate/0002" tofile="db/common/migrate/0001" />
      <copy file="db/common/ddl-info-0001.txt" tofile="db/common/ddl-info.txt" overwrite="true" />
      <gen-ddl
        classpathdir="${classpathdir}"
        rootpackagename="${rootpackagename}"
        entitypackagename="${entitypackagename}.client"
        env="${env}"
        jdbcmanagername="${jdbcmanagername}"
        classpathref="classpath"
        autogenerateforeignkey="false"
        dump="false"
        migrateDir="db/client/migrate"
        ddlinfofile="db/client/ddl-info.txt"
        ignoreEntityClassNamePattern=".*View$"
        templatefileprimarydir="templates"
        configpath="gen/s2jdbc-client1.dicon">
        <jvmarg value="${vmarg.encoding}" />
      </gen-ddl>
      <delete dir="db/client/migrate/0001" />
      <move file="db/client/migrate/0002" tofile="db/client/migrate/0001" />
      <copy file="db/client/ddl-info-0001.txt" tofile="db/client/ddl-info.txt" overwrite="true" />
    

    Notes: There are 2 gen-ddl tasks: for "common" package entity and for "client" package entity.

    e. Gen! Run gen-ddl and save ddl for each stored dir.

  3. Migrate

    a. Edit migrate target

    <target name="migrate">
      <migrate
        classpathdir="${classpathdir}"
        rootpackagename="${rootpackagename}"
        entitypackagename="${entitypackagename}.common"
        applyenvtoversion="${applyenvtoversion}"
        version="${version}"
        env="${env}"
        migrateDir="db/common/migrate"
        ddlinfofile="db/common/ddl-info.txt"
        configpath="gen/s2jdbc-common.dicon"
        jdbcmanagername="${jdbcmanagername}"
        classpathref="classpath">
        <jvmarg value="${vmarg.encoding}" />
      </migrate>
      <for list="1,2,3,4,5" param="clientNo">
        <sequential>
          <migrate
            classpathdir="${classpathdir}"
            rootpackagename="${rootpackagename}"
            entitypackagename="${entitypackagename}.client"
            applyenvtoversion="${applyenvtoversion}"
            version="${version}"
            env="${env}"
            migrateDir="db/client/migrate"
            ddlinfofile="db/client/ddl-info.txt"
            configpath="gen/s2jdbc-client@{clientNo}.dicon"
            jdbcmanagername="${jdbcmanagername}"
            classpathref="classpath">
            <jvmarg value="${vmarg.encoding}" />
          </migrate>
        </sequential>
      </for>
    

    Notes:

    • The ant task depend on "lib/ant-contrib-1.0b3.jar"
    • db/common -> common schema
    • db/client -> client1 schema
    • db/client -> client2 schema

    b. Migrate! Run

  4. Test data load

    a. Use test package for multi schema

    It's different and no compatible.

    b. Write table-ordering.txt

    client1 client_sample ../../common/sample.csv
    client1 client_sample sample1.csv
    client2 client_sample ../../common/sample.csv
    client2 client_sample sample2.csv
    
    schema_name table_name test_data_relative_path
    

    c. Run the test. Run!

Comments

Popular posts from this blog

Reduce TIME_WAIT Socket Connections