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

                   
banq
16-07-06 1 653 1

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


下面是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);
}
}

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;
}