JDBC Driver for JCR Repository
jdbc-jcr provides a JDBC Driver for JCR Repository using either sql (javax.jcr.query.Query#SQL) or JCR2_SQL (javax.jcr.query.Query#JCR2_SQL) query languages.
Add the following dependency:
<dependency>
<groupId>com.github.woonsan</groupId>
<artifactId>jdbc-jcr</artifactId>
<version>${jdbc-jcr.version}</version>
</dependency>JDBC URLs using this drivers must start with jdbc:jcr:.
The following JDBC URLs are supported:
jdbc:jcr:http(s)://...(e.g,jdbc:jcr:http://localhost:8080/server/)jdbc:jcr:file://...jdbc:jcr:jndi:...jdbc:jcr:(e.g,jdbc:jcr:?repository.conf=repository.xml&repository.home=repository)
The first one is to create a remote repository connection using SPI2DAVex with the given URL.
The second one is to create an embedded Jackrabbit repository located in the given directory.
The third one is to lookup JNDI for the named repository. See the org.apache.jackrabbit.commons.JndiRepositoryFactory
for more details.
The fourth one (with an empty location and repository parameters) is to create a TransientRepository.
private Connection getConnection() throws SQLException {
Properties info = new Properties();
info.setProperty("username", "admin");
info.setProperty("password", "admin");
final String jdbcUrl = "jdbc:jcr:http://localhost:8080/server/";
Driver jdbcDriver = new com.github.woonsan.jdbc.jcr.Driver.Driver();
Connection conn = jdbcDriver.connect(jdbcUrl, info);
return conn;
}It assumes there is a JNDI resource (jcr/repository) as javax.jcr.Repository.
<Resource name="jdbc/jcr"
auth="Container"
type="javax.sql.DataSource"
username="liveusername"
password="liveuserpass"
driverClassName="com.github.woonsan.jdbc.jcr.Driver"
url="jdbc:jcr:jndi:java:comp/env/jcr/repository"
maxActive="20"
maxIdle="10"/> //
// Assuming you have nt:unstructure nodes under /testdatafolder node and
// each node contains the following properties:
// - empno (long)
// - ename (string)
// - salary (double)
// - hiredate (date)
//
// SQL statement example:
//final String sql1 =
// "SELECT empno, ename, salary, hiredate, nicknames "
// + "FROM nt:unstructured "
// + "WHERE jcr:path like '/testdatafolder/%' "
// + "ORDER BY empno ASC";
// JCR2_SQL statement example:
final String sql1 =
"SELECT e.[empno] AS empno, e.[ename] AS ename, e.[salary] AS salary, e.[hiredate] AS hiredate, e.[nicknames] AS nicknames "
+ "FROM [nt:unstructured] AS e "
+ "WHERE ISDESCENDANTNODE('/testdatafolder') "
+ "ORDER BY e.[empno] ASC";
public void testStatement() throws SQLException {
Connection conn = getConnection();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql1);
printResult(rs);
rs.close();
stmt.close();
conn.close();
}
private void printResult(ResultSet rs) throws SQLException {
int i = 0;
long empno;
String ename;
double salary;
Date hireDate;
String [] nicknames;
System.out.println();
System.out.println("===================================================================");
System.out.println(" empno ename salary hire_date nicknames");
System.out.println("===================================================================");
final String rowFormat = "%8d\t%s\t%8.2f\t%s\t%s";
while (rs.next()) {
++i;
empno = rs.getLong(1);
ename = rs.getString(2);
salary = rs.getDouble(3);
hireDate = rs.getDate(4);
nicknames = (String []) rs.getArray(5).getArray();
System.out.println(String.format(rowFormat, empno, ename, salary,
new SimpleDateFormat("yyyy-MM-dd").format(hireDate),
StringUtils.join(nicknames, ",")));
}
System.out.println("==================================================");
System.out.println();
} // SQL statement example:
//final String sql2 =
// "SELECT empno, ename, salary, hiredate, nicknames "
// + "FROM nt:unstructured "
// + "WHERE jcr:path like '/testdatafolder/%' "
// + "AND salary > ? "
// + "ORDER BY empno ASC";
// JCR2_SQL statement example:
final String sql2 =
"SELECT e.[empno] AS empno, e.[ename] AS ename, e.[salary] AS salary, e.[hiredate] AS hiredate, e.[nicknames] AS nicknames "
+ "FROM [nt:unstructured] AS e "
+ "WHERE ISDESCENDANTNODE('/testdatafolder') "
+ "AND e.[salary] > ? "
+ "ORDER BY e.[empno] ASC";
public void testPreparedStatement() throws SQLException {
Connection conn = getConnection();
PreparedStatement pstmt = conn.prepareStatement(sql2);
pstmt.setDouble(1, 100010.0);
ResultSet rs = pstmt.executeQuery();
printResult(rs);
rs.close();
pstmt.close();
conn.close();
}ResultSet supports four meta-columns: jcr:path, jcr:name, jcr:uuid and jcr:score.
So, you can read the values of the meta-columns like the following example:
String path = rs.getString("jcr:path");
String name = rs.getString("jcr:name");
String uuid = rs.getString("jcr:uuid");
double score = rs.getDouble("jcr:score"); Connection conn = getConnection();
JcrConnection jconn = conn.unwrap(JcrConnection.class);
Session jcrSession = jconn.getSession(); ResultSet rs = stmt.executeQuery(sql);
JcrResultSet jrs = rs.unwrap(JcrResultSet.class);
Node node = jrs.getCurrentRow().getNode();