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

Post a Comment

Popular posts from this blog

Reduce TIME_WAIT Socket Connections

Sometime, you’ll run across an Apache server that always has tons of TIME_WAIT connections just seeming to hang out. While these don’t take up as many resources as an ESTABLISHED connection, why keep them around so long? This will show you how to identify how many you have, and how to tell your server to reduce them, reuse and recycle them
Read more