There are two types of database connection:
- 外部连接 – 可以连接到任何的数据库。
- 本地 – 可以连接到JIRA正在使用的数据库
使用
当我们增加数据库连接的时候,我们可以在我们的ScriptRunner的脚本中来使用这些获得,以便获得数据库中的数据操作。
我们可以在会用中使用以下格式,来对数据库进行相应操作,
import com.onresolve.scriptrunner.db.DatabaseUtil DatabaseUtil.withSql('localdatabase') { sql -> sql.rows('select * from project') } ##获得结果 [ [ID: 10000, PNAME: DEMO, URL: null, LEAD: admin, DESCRIPTION: null, PKEY: DEMO, PCOUNTER: 23, ASSIGNEETYPE: 3, AVATAR: 10324, ORIGINALKEY: DEMO, PROJECTTYPE: software], [ID: 10100, PNAME: 需求空间, URL: , LEAD: user02, DESCRIPTION: , PKEY: REQ, PCOUNTER: 14, ASSIGNEETYPE: 3, AVATAR: 10324, ORIGINALKEY: REQ, PROJECTTYPE: software], [ID: 10101, PNAME: 团队1空间, URL: , LEAD: user02, DESCRIPTION: , PKEY: TEAM1, PCOUNTER: 9, ASSIGNEETYPE: 3, AVATAR: 10324, ORIGINALKEY: TEAM1, PROJECTTYPE: software], [ID: 10102, PNAME: ITSM, URL: , LEAD: user02, DESCRIPTION: , PKEY: ITSM, PCOUNTER: 3, ASSIGNEETYPE: 3, AVATAR: 10324, ORIGINALKEY: ITSM, PROJECTTYPE: software], [ID: 10103, PNAME: 项目1空间, URL: , LEAD: user02, DESCRIPTION: , PKEY: PROJ1, PCOUNTER: 0, ASSIGNEETYPE: 3, AVATAR: 10324, ORIGINALKEY: PROJ1, PROJECTTYPE: software], [ID: 10200, PNAME: SD, URL: null, LEAD: admin, DESCRIPTION: null, PKEY: SD, PCOUNTER: 135, ASSIGNEETYPE: 3, AVATAR: 10324, ORIGINALKEY: SD, PROJECTTYPE: service_desk] ]
DatabaseUtil.withSql有两个参数:
在Resource中定义的数据库连接名称
- 闭包。闭包接收一个初始化的groovy.lang.Sql对象作为参数。使用闭包的好处是,它在执行后返回到池的连接。
withSql方法返回闭包返回的任何内容,因此作为另一个示例,您可以使用以下方法获取项目的数量:
import com.onresolve.scriptrunner.db.DatabaseUtil DatabaseUtil.withSql('localdatabase') { sql -> sql.rows('select * from project')[0] } ##获得结果 [ID: 10000, PNAME: DEMO, URL: null, LEAD: admin, DESCRIPTION: null, PKEY: DEMO, PCOUNTER: 23, ASSIGNEETYPE: 3, AVATAR: 10324, ORIGINALKEY: DEMO, PROJECTTYPE: software]
数据库连接URL
Oracle
JDBC Driver | oracle.jdbc.OracleDriver |
JDBC URL | jdbc:oracle:thin:@localhost:1521:orclpdb1 |
Hibernate Dialect | org.hibernate.dialect.Oracle12cDialect |
mysql
JDBC Driver | com.mysql.jdbc.Driver |
JDBC URL | jdbc:mysql://localhost/high_performance_java_persistence |
Hibernate Dialect | org.hibernate.dialect.MySQL8Dialect |
PostgreSQL
JDBC Driver | org.postgresql.Driver |
JDBC URL | jdbc:postgresql://localhost/high_performance_java_persistence |
Hibernate Dialect | org.hibernate.dialect.PostgreSQL95Dialect |
SQLServer
JDBC Driver | com.microsoft.sqlserver.jdbc.SQLServerDriver |
JDBC URL | jdbc:sqlserver://localhost;instance=SQLEXPRESS;databaseName=high_performance_java_persistence |
Hibernate Dialect | org.hibernate.dialect.SQLServer2012Dialect |
mariadb
JDBC Driver | org.mariadb.jdbc.Driver |
JDBC URL | jdbc:mariadb://localhost/high_performance_java_persistence |
Hibernate Dialect | org.hibernate.dialect.MariaDB53Dialect |
Db2 Express-C
JDBC Driver | com.ibm.db2.jcc.DB2Driver |
JDBC URL | jdbc:db2://localhost/high_performance_java_persistence |
Hibernate Dialect | org.hibernate.dialect.DB2Dialect |
HSQLDB
JDBC Driver | org.hsqldb.jdbc.JDBCDriver |
JDBC URL | jdbc:hsqldb:mem:high_performance_java_persistence |
Hibernate Dialect | org.hibernate.dialect.HSQLDialect |
H2
JDBC Driver | org.h2.Driver |
JDBC URL | jdbc:h2:mem:high_performance_java_persistence |
Hibernate Dialect | org.hibernate.dialect.H2Dialect |
Derby
JDBC Driver | org.apache.derby.jdbc.EmbeddedDriver |
JDBC URL | jdbc:derby:target/tmp/derby/hpjp;databaseName=high_performance_java_persistence;create=true |
Hibernate Dialect | org.hibernate.dialect.DerbyTenSevenDialect |