dbj is an R package implementing DBI 0.4 using JDBC via rJava.
- Java 6+
dbj can be installed most easily using the devtools package. After you have installed devtools, just run devtools::install_github("hoesler/dbj").
The source compilation requires that you have maven installed.
library(DBI)
library(dbj)
# Initially, register the JDBC drivers you need
jdbc_register_driver(
'org.h2.Driver',
resolve(
module('com.h2database:h2:1.3.176'),
repositories = list(maven_local, maven_central) )
)
con <- dbConnect(dbj::driver(), 'jdbc:h2:mem:')
dbWriteTable(con, "iris", iris)
# dbj supports prepared statements
sql <- paste0(
"SELECT * FROM ", dbQuoteIdentifier(con, "iris"),
" WHERE ", dbQuoteIdentifier(con, "Species"), " = ?"
)
dbGetQuery(con, sql, parameters = list("setosa"))No version has been releaded yet. dbj is under active development and tested agains different JDBC drivers using DBItest:
- H2
- Apache Derby
- MySQL Connector/J
- more to come...
The default type mapping between R and SQL is (roughly) as follows:
| R classes | SQL types |
|---|---|
| logical | BIT, BOOLEAN |
| numeric | FLOAT, REAL, DOUBLE, NUMERIC, DECIMAL, BIGINT |
| integer | TINYINT, SMALLINT, INTEGER, NULL |
| character, factor | CHAR, VARCHAR, LONGVARCHAR, NCHAR, NVARCHAR, LONGNVARCHAR |
| Date | DATE |
| difftime | TIME |
| POSIXct | TIMESTAMP |
| list(raw) | BINARY, BLOB |
dbj is a rewrite of Simon Urbanek's RJDBC packge. Development started with only minor code modifications to meet the requirements of the devtools package development tools and the design guidelines for good R packages (See R packages by Hadley Wickham). In the end, the code diverged too far to merge it back into RJDBC.