ORM用于复杂CRUD,SQL用于大规模读取

16-07-06 banq
                   

现在人们已经认识到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>

下面是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>

                   

1
banq
2016-07-06 08:47

下面关键来了,一个控制器和一个服务以及两个DAO,CRUD使用Hibernate操作,而查询用JOOQ:

@Controller
...
public class ProjectController {
	
    @Autowired
    protected ProjectService projectService;

    ...
    
    // COMPLEX READ
    @RequestMapping("/project/{projectId}")
    public String project(@PathVariable("projectId") int projectId, ModelMap model) {
        ...
        
        Project project = projectService.getProject(projectId);
        
        ...
        
        model.addAttribute("project", project);
        
        ...
        
        return "/view/project.jsp";
    }

    ...
    
    // COMPLEX CREATE
    @RequestMapping(value = "/addProject}", method = RequestMethod.POST)
    public String addProject(@ModelAttribute("addProjectWizard") AddProjectWizard wizard, ModelMap model) {
        ...
        
        Project project = new Project();
        ...[Build up the object.]
        
        projectService.insert(project);
        
        ...
        
        return "redirect:/project/" + project.getId();
    }

    ...
    
    // COMPLEX UPDATE
    @RequestMapping(value = "/editProject/{projectId}", method = RequestMethod.POST)
    public String editProject(@PathVariable("projectId") int projectId, @ModelAttribute("project") Project projectEdit, ModelMap model) {
        ...
        
        Project project = projectService.getProject(projectId));
        ...[Edit 'project' with fields from 'projectEdit'.  Ex: project.setDescription(projectEdit.getDescription);]
        
        projectService.update(project);
        
        ...
        
        return "redirect:/project/" + project.getId();
    }

    // LARGE-SCALE FETCH
    // NOTE: AdvancedSearchForm is a bean that was bound to a JSP/JSTL form, containing free-text search, checkboxes, etc.
    @RequestMapping(value = "/advancedSearch")
    public String projects(@ModelAttribute("advancedSearch") AdvancedSearchForm advancedSearch, ModelMap model) {
	...
        
        List<Project> projects = projectService.search(advancedSearch);
        
        ...
        
        model.addAttribute("projects", projects);
        
        ...
        
        return "/view/projects.jsp";
    }

@Service
@Transactional
...
public class ProjectService {
	
    @Autowired
    private ProjectDao projectDao;
	
    @Autowired
    private ProjectJooqDao projectJooqDao;

    ...
    
    // COMPLEX READ
    public Project getProject(int id) {
        return projectDao.getProject(id);
    }

    ...
    
    // COMPLEX CREATE
    public void insert(Project project) {
        projectDao.insert(project);
    }

    ...
    
    // COMPLEX UPDATE
    public void update(Project project) {
        project.setLastUpdated(Calendar.getInstance());
        projectDao.update(project);
    }

    ...
    
    // LARGE-SCALE FETCH
    public List<Project> search(AdvancedSearchForm advancedSearch) {
        return projectJooqDao.search(advancedSearch);
    }

@Repository
...
public class ProjectDao {
	
    @Autowired
    private SessionFactory sessionFactory;

    ...
    
    // COMPLEX READ
    public Project getProject(int id) {
        // Note that the simple #get is likely not enough, since there are likely associations that are LAZY.
        // This likely implies HQL + joins, a fetch profile, explicit use of Hibernate#initialize, or a combo.
        return (Project) sessionFactory.getCurrentSession().get(Project.class, id);
    }

    ...
    
    // COMPLEX CREATE
    public void insert(Project project) {
        sessionFactory.getCurrentSession().save(project);
    }

    ...
    
    // COMPLEX UPDATE
    public void update(Project project) {
        sessionFactory.getCurrentSession().update(project);
    }

@Repository
...
public class ProjectJooqDao {
	
    @Autowired
    private DSLContext jooq;

    ...
    
    // LARGE-SCALE FETCH
    public List<Project> search(AdvancedSearchForm advancedSearch) {
        Result<Record> results = jooq.selectDistinct()
                .from(Projects.PROJECTS)
                .join(...)
                ...
                .where(...)
                .orderBy(...)
                .fetch();

      // NOTE: 映射 jOOQ结果有很多方式
        // jOOQ DSL 包括fetchInto(Class) 对象能自动映射结果
        //到POJO, 包括支持JPA原始类型元注解。
        // 但是项目包括enums, collection和其他非原始类型
        // 易于手工构建
        // jOOQ最新版本包含了和ModelMapper 集成
        //能帮助自动化和清晰化映射
        List<Project> projects = new ArrayList<>();
        ...[Build up each Project using the jOOQ Records.]

        return projects;
    }
<p>