1.配置依赖及jooq类生成脚本(配置build.gradle)
脚本依赖
buildscript { ext { springBootVersion = '1.5.4.RELEASE' } repositories { mavenCentral() // ... // ... } dependencies { classpath('org.jooq:jooq-codegen:3.10.2') // ... // ... } }
项目依赖
dependencies { compile('org.springframework.boot:spring-boot-starter-jooq') compile('com.zaxxer:HikariCP:2.7.6') compile('org.scala-lang:scala-library:2.12.4') compile('org.scala-lang.modules:scala-xml_2.12:1.0.5') compile('mysql:mysql-connector-java:5.1.39') compileOnly('org.jooq:jooq-codegen:3.9.1') runtime('org.springframework.boot:spring-boot-devtools') testCompile('org.springframework.boot:spring-boot-starter-test') }
根据数据库表生成jooq类的脚本
import groovy.xml.MarkupBuilder import org.jooq.util.GenerationTool import javax.xml.bind.JAXB task jooq_code_generate << { def writer = new StringWriter() new MarkupBuilder(writer) .configuration('xmlns': 'http://www.jooq.org/xsd/jooq-codegen-3.9.0.xsd') { jdbc() { driver('com.mysql.jdbc.Driver')//mysql驱动,需要和项目依赖的mysql-connector版本对应 url('jdbc:mysql://192.168.31.97:3306?characterEncoding=UTF-8&allowMultiQueries=true')//url所连接数据库表需要和真实使用环境的库表保持一致 user('test')//数据库用户名 password('123456')//密码 } generator() { name('org.jooq.util.ScalaGenerator') database() { name('org.jooq.util.mysql.MySQLDatabase') inputSchema('testdb')//数据库名 includes("t_profile")//包含的表,这里是用正则规则匹配的,例如,如果要包含所有"t_"开头的表,则写成includes("t_\\w+"),多张表用"|"符号隔开即可 excludes("schema_version")//不包含的表 } generate([:]) { pojos true//是否生成数据表对应的javabean类 //daos true//是否生成数据表对应的dao层,这里生成的dao能执行基本的增删改查sql操作,复杂一点的操作需要自己写dao } //生成类输出目录 target() { packageName('com.study.jooq.data')//输出具体目录,生成的jooq类会在这个目录底下 directory('src/main/scala')//输出根目录 encoding("UTF-8")//编码格式 } } } // 执行脚本 GenerationTool.generate( JAXB.unmarshal(new StringReader(writer.toString()), org.jooq.util.jaxb.Configuration.class) ) }
配置好后可以在gradle的可执行任务脚本中找到jooq_code_generate,执行即可生成数据库表所对应jooq类。(脚本任务在Tasks的other下)
2.配置数据库连接和jooq的会话实例
application.properties配置
datasource.driverClass=com.mysql.jdbc.Driver datasource.jdbcUrl=jdbc:mysql://192.168.31.97:3306?characterEncoding=UTF-8&allowMultiQueries=true datasource.user=test datasource.password=123456
实例类配置
@Configuration @EnableTransactionManagement class JooqConf { @Value("${datasource.driverClass}") private val driverClass: String = null @Value("${datasource.jdbcUrl}") private val jdbcUrl: String = null @Value("${datasource.user}") private val user: String = null @Value("${datasource.password}") private val password: String = null @Bean(name = Array("TestDataSource")) def dataSource: DataSource = { val dataSource = new HikariDataSource dataSource.setPoolName("testDS") dataSource.setDriverClassName(driverClass) dataSource.setJdbcUrl(jdbcUrl) dataSource.setUsername(user) dataSource.setPassword(password) dataSource } @Bean def dsl(jooqConfig: org.jooq.Configuration): DSLContext = { new DefaultDSLContext(jooqConfig) } @Bean def jooqConfig(connectionProvider: ConnectionProvider): org.jooq.Configuration = { new DefaultConfiguration().derive(connectionProvider) // .derive(SQLDialect.MYSQL) } @Bean def connectionProvider = new DataSourceConnectionProvider(new TransactionAwareDataSourceProxy(this.dataSource)) }
3.使用
@Repository class ProfileDao(@Autowired val dSLContext: DSLContext) { val tProfile: TProfile = Tables.t_profile /** * 根据id获取profile数据 * @param pid pid * @return profile */ def getProfileByPid(pid:Long): Profile = { dSLContext select (tProfile.fields():_*) from tProfile where tProfile.id.eq(pid) fetchOneInto classOf[Profile] } /** * 根据id删除profile数据 * @param pid pid */ def delProfileByPid(pid:Long):Int={ dSLContext delete tProfile where tProfile.id.eq(pid) execute() } }
"select (tProfile.fields():_*)"和"select ()"都能表示select * ,这里如果直接写"select (tProfile.fields())"会报错,说找不到这种入参的select方法,但是这种写法在java里却是可以的,原因在于,scala不识别“select(SelectField<?>... fields)”中这种三个点的java数组,所以要把fields中的元素依次传进去。
insert和update操作和上面的差不多,就不做赘述了。
4.测试
@RunWith(classOf[SpringRunner]) @SpringBootTest(classes = Array(classOf[HappydogApplication])) class ProfileDaoTest { @Autowired val profileDao: ProfileDao = null new TestContextManager(this.getClass).prepareTestInstance(this) @Test def testProfileByPid() = { val profile = profileDao getProfileByPid 1001 println("profile is " + profile) } }
执行打印
profile is Profile ("haha")