ORM用於複雜CRUD，SQL用於大規模讀取

banq發表於2016-07-06
ORMSQL
現在人們已經認識到Hibernate等ORM有一定的侷限性，在CQRS讀寫分離的架構中，ORM主要用於命令寫操作，進行復雜的增刪改查CRUD；而SQL用於查詢讀操作。

Hibernate ORM, jOOQ, HikariCP, Transactions, and S一文分享了他們在自己的NeighborLink專案中如何實踐這個原則。

經驗如下：
1.使用明確的讀寫分離. 實體用於CRUD, 簡單DTO用於查詢結果.
2.讀寫兩邊使用同樣物件，而不是維護多個模型，經常會在SQL結果中複用實體物件。
2.透過繼承實現分層目標，物件的基礎層包含物件的基本必要欄位，然後分別有實體和DTO繼承它。或者使用DTO作為基礎，實體繼承DTO。

在具體技術上，CRUD採取Hibernate，查詢是JOOQ，JOOQ能夠以Java方式編寫SQL(DSL)，而且可以根據資料庫中資料表結構自動產生元模型，如果資料表結構變化，產生的元模型會隨著改變，這樣編譯時會出錯。元模型和DSL都會以資料庫本地語言最佳化地和資料庫打交道。

具體程式碼如下，前端使用的Spring。 將DataSource 和 HibernateTransactionManager 分享給所有DB互動, 包括ORM, jOOQ, 和直接的JDBC!

Maven配置的庫包如下：

 <build>
  <plugins>
    ...
    <plugin>
      <groupId>org.jooq</groupId>
      <artifactId>jooq-codegen-maven</artifactId>
      <version>${jooq.version}</version>
      <executions>
        <execution>
          <goals>
            <goal>generate</goal>
          </goals>
        </execution>
      </executions>
      <dependencies>
        <dependency>
          <groupId>org.jooq</groupId>
          <artifactId>jooq-codegen</artifactId>
          <version>${jooq.version}</version>
        </dependency>
      </dependencies>
      <configuration>
        <jdbc>
          <driver>com.mysql.jdbc.Driver</driver>
          <url>jdbc:mysql://[HOST]:[PORT]/[DB NAME]</url>
          <user>[USER]</user>
          <password>[PASSWORD]</password>
        </jdbc>
        <generator>
          <name>org.jooq.util.DefaultGenerator</name>
          <database>
            <name>org.jooq.util.mysql.MySQLDatabase</name>
            <includes>.*</includes>
            <excludes></excludes>
            <inputSchema>[DB NAME]</inputSchema>
          </database>
          <target>
            <packageName>org.threeriverdev.neighborlink.query</packageName>
            <directory>target/generated-sources/jooq</directory>
          </target>
        </generator>
      </configuration>
    </plugin>
    ...
</build>

<dependencies>
  <!-- Spring framework -->
  ...
  <dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-orm</artifactId>
    <version>${spring.version}</version>
  </dependency>
  ...

  <!-- Hibernate -->
  <dependency>
    <groupId>org.hibernate</groupId>
    <artifactId>hibernate-core</artifactId>
    <version>${hibernate.version}</version>
  </dependency>

  <!-- DataSource -->
  <dependency>
    <groupId>com.zaxxer</groupId>
    <artifactId>HikariCP</artifactId>
    <version>2.4.6</version>
  </dependency>
  <dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.39</version>
  </dependency>

  <!-- jOOQ -->
  <dependency>
    <groupId>org.jooq</groupId>
    <artifactId>jooq</artifactId>
    <version>${jooq.version}</version>
  </dependency>
  <dependency>
    <groupId>org.jooq</groupId>
    <artifactId>jooq-meta</artifactId>
    <version>${jooq.version}</version>
  </dependency>

  ...
</dependencies>
<p class="indent">


下面是Spring程式碼部分：

@Configuration
@EnableWebMvc
@EnableTransactionManagement
@ComponentScan
public class AppConfig {

    @Autowired
    private Environment env;

    @Autowired
    private ConfigService configService;

    ...

    @Bean(destroyMethod = "close")
    public DataSource dataSource() {
        String dbHost;
        String dbPort;
        String dbName;
        String dbUsername;
        String dbPassword;
        if (configService.isProduction()) {
            // We're in a production environment.
            dbHost = "localhost";
            dbPort = "3306";
            dbName = configService.getConfig().getDatabase().getName();
            dbUsername = configService.getConfig().getDatabase().getUsername();
            dbPassword = configService.getConfig().getDatabase().getPassword();
        } else {
            // Local dev or unit test.
            dbHost = "[HOST]";
            dbPort = "[PORT]";
            dbName = "[DB NAME]";
            dbUsername = "[USER]";
            dbPassword = "[PASSWORD]";
        }

        final String url = new StringBuilder()
                .append("jdbc:mysql://")
                .append(dbHost)
                .append(":")
                .append(dbPort)
                .append("/")
                .append(dbName)
                .toString();

        HikariDataSource dataSource = new HikariDataSource();

        ...[HikariCP options]

        MysqlDataSource mysqlDataSource = new MysqlDataSource();
        mysqlDataSource.setURL(url);
        mysqlDataSource.setUser(dbUsername);
        mysqlDataSource.setPassword(dbPassword);
        ...[MySQL-specific options]
        dataSource.setDataSource(mysqlDataSource);

        return dataSource;
    }

    // HIBERNATE

    @Bean
    public LocalSessionFactoryBean sessionFactory() {
        LocalSessionFactoryBean sessionFactory = new LocalSessionFactoryBean();
        sessionFactory.setDataSource(dataSource());
        sessionFactory.setHibernateProperties(hibernateProperties());
        
        List<String> packages = new ArrayList<String>();
        packages.add("org.threeriverdev.neighborlink.entity.core");
        ...
        sessionFactory.setPackagesToScan(packages.toArray(new String[packages.size()]));

        return sessionFactory;
    }

    @Bean
    public HibernateTransactionManager transactionManager() {
        HibernateTransactionManager txManager = new HibernateTransactionManager();
        txManager.setSessionFactory(sessionFactory().getObject());
        txManager.setDataSource(dataSource());
        return txManager;
    }

    private Properties hibernateProperties() {
        final Properties properties = new Properties();
        ...[Add all "hibernate.*" specific properties -- dialect, etc.]
        return properties;
    }

    // JOOQ

    @Bean
    public TransactionAwareDataSourceProxy transactionAwareDataSource() {
        return new TransactionAwareDataSourceProxy(dataSource());
    }

    @Bean
    public DataSourceConnectionProvider connectionProvider() {
        return new DataSourceConnectionProvider(transactionAwareDataSource());
    }

    @Bean()
    public DSLContext jooq() {
        // Generated source assumes the development DB.  Dynamically change it to the production DB name.
        String dbName;
        if (configService.isProduction()) {
            // We're in a production environment.
            dbName = configService.getConfig().getDatabase().getName();
        } else {
            // Local dev or unit test.
            dbName = "[SANDBOX DB NAME]";
        }
        // The DB name used in the generated DSL (see the Maven plugin use) will not be the same as
        // the production DB, unless running in a local dev env!  withSchemata allows us
        // to override that during runtime.
        Settings settings = new Settings().withRenderMapping(
                new RenderMapping().withSchemata(new MappedSchema().withInput("[SANDBOX DB NAME]").withOutput(dbName)));
        return DSL.using(connectionProvider(), SQLDialect.MYSQL, settings);
    }
}
<p class="indent">

相關文章