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 |