jooq spring mysql_【jOOQ中文】2. jOOQ与Spring和Druid整合

jOOQ和Spring很容易整合。 在这个例子中，我们将整合：

Alibaba Druid(但您也可以使用其他连接池，如BoneCP，C3P0，DBCP等)。

Spring TX作为事物管理library。

jOOQ作为SQL构建和执行library。

一、准备数据库

DROP TABLE IF EXISTS `author`;

CREATE TABLE `author` (

`id` int(11) NOT NULL,

`first_name` varchar(50) DEFAULT NULL,

`last_name` varchar(50) NOT NULL,

`date_of_birth` date DEFAULT NULL,

`year_of_birth` int(11) DEFAULT NULL,

`distinguished` int(1) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `book`;

CREATE TABLE `book` (

`id` int(11) NOT NULL,

`author_id` int(11) NOT NULL,

`title` varchar(400) NOT NULL,

`published_in` int(11) NOT NULL,

`language_id` int(11) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `book_store`;

CREATE TABLE `book_store` (

`name` varchar(400) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `book_to_book_store`;

CREATE TABLE `book_to_book_store` (

`name` varchar(400) NOT NULL,

`book_id` int(11) NOT NULL,

`stock` int(11) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `language`;

CREATE TABLE `language` (

`id` int(11) NOT NULL,

`cd` char(2) NOT NULL,

`description` varchar(50) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `author`

ADD PRIMARY KEY (`id`);

ALTER TABLE `book`

ADD PRIMARY KEY (`id`),

ADD KEY `fk_book_author` (`author_id`),

ADD KEY `fk_book_language` (`language_id`);

ALTER TABLE `book_store`

ADD UNIQUE KEY `name` (`name`);

ALTER TABLE `book_to_book_store`

ADD PRIMARY KEY (`name`,`book_id`),

ADD KEY `fk_b2bs_book` (`book_id`);

ALTER TABLE `language`

ADD PRIMARY KEY (`id`);

二、添加所需的Maven依赖项

在这个例子中，我们将创建以下Maven依赖项：

xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">

4.0.0

com.jsyso

jooq-tutorials-2

jar

jooq-tutorials-2

1.0.0

4.1.9.RELEASE

1.8

UTF-8

true

1.7.7

5.1.30

1.0.18

3.9.5

aliyun-repos

Aliyun Repository

http://maven.aliyun.com/nexus/content/groups/public

aliyun-repos

Aliyun Repository

http://maven.aliyun.com/nexus/content/groups/public

mysql

mysql-connector-java

${mysql.driver.version}

junit

junit

4.11

test

org.jooq

jooq

${jooq.version}

org.jooq

jooq-meta

${jooq.version}

org.jooq

jooq-codegen

${jooq.version}

com.alibaba

druid

${druid.version}

org.springframework

spring-core

${spring.version}

commons-logging

commons-logging

org.springframework

spring-beans

${spring.version}

org.springframework

spring-context

${spring.version}

org.springframework

spring-context-support

${spring.version}

org.springframework

spring-aop

${spring.version}

commons-logging

commons-logging

org.springframework

spring-tx

${spring.version}

org.springframework

spring-orm

${spring.version}

org.springframework

spring-jdbc

${spring.version}

junit

junit

4.11

test

org.springframework

spring-test

${spring.version}

test

org.slf4j

slf4j-api

${slf4j.version}

org.slf4j

slf4j-log4j12

${slf4j.version}

org.slf4j

jcl-over-slf4j

${slf4j.version}

org.slf4j

jul-to-slf4j

${slf4j.version}

org.apache.maven.plugins

maven-compiler-plugin

3.5.1

${jdk.version}

${jdk.version}

true

org.apache.maven.plugins

maven-jar-plugin

2.4

org.apache.maven.plugins

maven-resources-plugin

2.7

org.apache.maven.plugins

maven-install-plugin

2.5.2

org.apache.maven.plugins

maven-clean-plugin

2.6.1

org.apache.maven.plugins

maven-dependency-plugin

2.10

org.apache.maven.plugins

maven-surefire-plugin

2.5

true

com.jsyso

Jan

[e-mail]

+8

三、Spring配置文件

xmlns:context="http://www.springframework.org/schema/context" xmlns:jdbc="http://www.springframework.org/schema/jdbc"

xmlns:jee="http://www.springframework.org/schema/jee" xmlns:tx="http://www.springframework.org/schema/tx"

xmlns:util="http://www.springframework.org/schema/util" xmlns:task="http://www.springframework.org/schema/task" xsi:schemaLocation="

http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.1.xsd

http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.1.xsd

http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-4.1.xsd

http://www.springframework.org/schema/jee http://www.springframework.org/schema/jee/spring-jee-4.1.xsd

http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.1.xsd

http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-4.1.xsd

http://www.springframework.org/schema/task http://www.springframework.org/schema/task/spring-task-4.1.xsd"

default-lazy-init="true">

Spring Configuration

class="org.springframework.jdbc.datasource.TransactionAwareDataSourceProxy">

MYSQL

四、Spring Test + JUnit集成测试

查询测试：

package test.generated.service;

import static java.util.Arrays.asList;

import static org.jooq.impl.DSL.countDistinct;

import static org.junit.Assert.assertEquals;

import static test.generated.Tables.*;

import org.jooq.DSLContext;

import org.jooq.Record3;

import org.jooq.Result;

import org.junit.Test;

import org.junit.runner.RunWith;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.test.context.ContextConfiguration;

import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import test.generated.tables.Author;

import test.generated.tables.Book;

import test.generated.tables.BookStore;

import test.generated.tables.BookToBookStore;

import test.generated.tables.records.BookRecord;

/**

* @author Lukas Eder

*/

@RunWith(SpringJUnit4ClassRunner.class)

@ContextConfiguration(locations = {"/jooq-spring.xml"})

public class QueryTest {

@Autowired

DSLContext create;

@Test

public void testJoin() throws Exception {

// All of these tables were generated by jOOQ's Maven plugin

Book b = BOOK.as("b");

Author a = AUTHOR.as("a");

BookStore s = BOOK_STORE.as("s");

BookToBookStore t = BOOK_TO_BOOK_STORE.as("t");

Result> result =

create.select(a.FIRST_NAME, a.LAST_NAME, countDistinct(s.NAME))

.from(a)

.join(b).on(b.AUTHOR_ID.eq(a.ID))

.join(t).on(t.BOOK_ID.eq(b.ID))

.join(s).on(t.NAME.eq(s.NAME))

.groupBy(a.FIRST_NAME, a.LAST_NAME)

.orderBy(countDistinct(s.NAME).desc())

.fetch();

assertEquals(2, result.size());

assertEquals("Paulo", result.getValue(0, a.FIRST_NAME));

assertEquals("George", result.getValue(1, a.FIRST_NAME));

assertEquals("Coelho", result.getValue(0, a.LAST_NAME));

assertEquals("Orwell", result.getValue(1, a.LAST_NAME));

assertEquals(Integer.valueOf(3), result.getValue(0, countDistinct(s.NAME)));

assertEquals(Integer.valueOf(2), result.getValue(1, countDistinct(s.NAME)));

}

}

数据插入，使用Spring的TransactionManager来显式处理事务：

package test.generated.service;

import static org.junit.Assert.assertEquals;

import static org.junit.Assert.assertTrue;

import static test.generated.Tables.BOOK;

import java.util.concurrent.atomic.AtomicBoolean;

import org.jooq.DSLContext;

import org.junit.After;

import org.junit.Assert;

import org.junit.Test;

import org.junit.runner.RunWith;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.dao.DataAccessException;

import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import org.springframework.test.context.ContextConfiguration;

import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import org.springframework.test.context.transaction.TransactionConfiguration;

import org.springframework.transaction.TransactionStatus;

import org.springframework.transaction.support.DefaultTransactionDefinition;

/**

* @author Petri Kainulainen

* @author Lukas Eder

*

* @see

* href="http://www.petrikainulainen.net/programming/jooq/using-jooq-with-spring-configuration/">http://www.petrikainulainen.net/programming/jooq/using-jooq-with-spring-configuration/

*/

@RunWith(SpringJUnit4ClassRunner.class)

@ContextConfiguration(locations = {"/jooq-spring.xml"})

@TransactionConfiguration(transactionManager="transactionManager")

public class TransactionTest {

@Autowired DSLContext dsl;

@Autowired DataSourceTransactionManager txMgr;

@Test

public void testDelBooks() {

// Delete all books that were created in any test

dsl.delete(BOOK).where(BOOK.ID.gt(4)).execute();

}

@Test

public void testAddBooks() {

TransactionStatus tx = txMgr.getTransaction(new DefaultTransactionDefinition());

for (int i = 1; i <= 6; i++)

dsl.insertInto(BOOK)

.set(BOOK.ID, i)

.set(BOOK.PUBLISHED_IN, 1)

.set(BOOK.LANGUAGE_ID, 1)

.set(BOOK.AUTHOR_ID, 1)

.set(BOOK.TITLE, "Book " + i)

.execute();

txMgr.commit(tx);

}

@Test

public void testExplicitTransactions() {

boolean rollback = false;

TransactionStatus tx = txMgr.getTransaction(new DefaultTransactionDefinition());

try {

// This is a "bug". The same book is created twice, resulting in a

// constraint violation exception

for (int i = 7; i <=9; i++)

dsl.insertInto(BOOK)

.set(BOOK.ID, i)

.set(BOOK.AUTHOR_ID, 1)

.set(BOOK.TITLE, "Book " + i)

.execute();

Assert.fail();

}

// Upon the constraint violation, we explicitly roll back the transaction.

catch (DataAccessException e) {

txMgr.rollback(tx);

rollback = true;

}

assertEquals(4, dsl.fetchCount(BOOK));

assertTrue(rollback);

}

}

【jOOQ中文】教程代码都会放在码云，希望多多宣传给Star(^_−)☆。