Answers to questions about Jive's use of JDBC, transactions, and other database tools.
We use Spring JDBC templates for direct database access. We almost always do that in a DAO (Data Access Object).
Here is a simple example using Spring JdbcTemplate:
private static final String REMOVE_THING = "DELETE FROM jiveThing WHERE thingID = ?";
public void removeThing(long id) throws DAOException {
try {
getSimpleJdbcTemplate().update(REMOVE_THING, id);
}
catch (DataAccessException e) {
Log.error(e.getMessage(), e);
throw new DAOException(e);
}
}
Here is an example using Spring RowMapper:
private static final String GET_THING = "SELECT t.thingID, t.name FROM jiveThing t WHERE t.thingID = ? ";
public Thing getThing(long thingID) {
Thing thing = null;
try {
thing = getSimpleJdbcTemplate()
.queryForObject(GET_THING, new ThingRowMapper(), 0, thingID);
}
catch (EmptyResultDataAccessException e) {
// ignore, thing may not exist
}
catch (DataAccessException e) {
Log.error(e.getMessage(), e);
throw new DAOException(e);
}
return descriptor;
}
private static final class ThingRowMapper implements ParameterizedRowMapper<Thing> {
public Thing mapRow(ResultSet rs, int rowNum) throws SQLException {
long thingID = rs.getLong(1);
String name = rs.getString(2);
return new Thing(thingID, name);
}
}
Transactions are currently disabled in 2.5.0 and earlier. The JDBC connection is set with auto-commit to true.
This is planned for the next major release.
This will be through Spring transactions using annotations. You can plan for this support already by adding an @Transactional annotation on your method on one of your manager methods. Do not add this at the DAO layer. Most of these annotations are already in place in our code and inspecting the managers can give insight into what methods will be transactional moving forward.
The best base reference is the jive.xml file from which .sql scripts are created. This represents the logical schema that our sqlgen tool uses to generate physical schemas for each database platform.
Date column type support varies widely across databases. Therefore, Jive SBS specially encodes dates as 64-bit numbers. The long value is the internal representation of Java Date objects, which can be obtained with code such as the following:
long currentDate = new Date().getTime();
Boolean values are always represented a numeric values: 0 for false and 1 for true.
To report dates out of PostgreSQL you can use the following function:
select (timestamp 'epoch' + u.creationDate/1000 * interval '1 second') as joinDate from jiveUser u
This is an internal tool for generating database schemas from an abstract platform-agnostic specification. sqlgen scripts are XML files that define the schemas and/or upgrade actions. sqlgen generates the appropriate DDL or DML for the target platform. The best reference is jive.xml, the file sqlgen uses to generate its output. Here is an example table creation snippet from the jive.xml file:
<table name="jiveContainerAprvr" description="Container approvers.">
<column name="containerType" type="int" nullable="false" description="Container Type."/>
<column name="containerID" type="bigint" nullable="false" description="Container ID."/>
<column name="userID" type="bigint" nullable="false" description="Approver User ID."/>
<index type="primary" name="jiveCAprvr_pk" column="containerType,containerID,userID"/>
</table>
<schema name="AddDocumentStatusColumn">
<alter table="jiveDocVersion" type="add" description="Add a status column">
<column name="status" type="smallint" nullable="false" default="2"/>
</alter>
</schema>
If you create a string or binary type, sqlgen will automaticly convert it to a TEXT/BLOB if it exceeds the varchar limit on that DB. So you should be able to specify a vachar(65000) and have it work for what you need.
XML would look like this:
<column name="body" type="text" nullable="true" index_none="true" unicode="true"
description="body of content"/>
If you don't use Spring JDBCTemplate, you have to use the ConnectionManager to open and close connections.
// Always grab your connections in a try/(catch)/finally
try {
con = ConnectionManager.getConnection();
. . .
} finally {
ConnectionManager.close(pstmt, con);
}
Use the SequenceManager to generate IDs to get a new unique value for your primary key.
SequenceManager.nextID(JiveConstants.ATTACHMENT)
When using methods that return column data typed as Object, such as
ResultSet.getObject()mysql>drop database clearspace;
mysql>create database clearspace;
mysql>use clearspace; (very important)
mysql>source \path\to\database\scripts\included\in\the\source\distro\database\jive_clearspace_community_mysql.sql (no semi-colon for this command)
, or when using the Spring JDBC template methods queryForList and queryForMap without a row mapper, always cast numeric columns to Number then use the appropriate method to convert the value into the expected data type. This is because some database drivers do not correctly map column types to the expected Java type; for example, Oracle will map all numeric values to BigDecimal.
int intValue = ((Number) theMap.get("int_column")).intValue();