/**
* Builds a sequence of elements from a query's result set.
*
* @param rs result set
* @return sequence of elements <tuple/> each of which represents a row from the result set
* @throws QueryException query exception
*/
private NodeCache buildResult(final ResultSet rs) throws QueryException {
try {
final ResultSetMetaData metadata = rs.getMetaData();
final int columnCount = metadata.getColumnCount();
final NodeCache rows = new NodeCache();
while (rs.next()) {
final NodeCache columns = new NodeCache();
for (int k = 1; k <= columnCount; k++) {
// For each row add column values as children
final String label = metadata.getColumnLabel(k);
final Object value = rs.getObject(label);
// Null values are ignored
if (value != null) {
// Column name
final FAttr columnName = new FAttr(Q_NAME, token(label));
final NodeCache attr = new NodeCache();
attr.add(columnName);
// Column value
final FTxt columnValue = new FTxt(token(value.toString()));
final NodeCache ch = new NodeCache();
ch.add(columnValue);
// Element <sql:column name='...'>...</sql:column>
columns.add(new FElem(Q_COLUMN, ch, attr, NS_SQL, null));
}
}
rows.add(new FElem(Q_ROW, columns, null, NS_SQL, null));
}
return rows;
} catch (final SQLException ex) {
throw SQLEXC.thrw(input, ex.getMessage());
}
}
/**
* Establishes a connection to a relational database.
*
* @param ctx query context
* @return connection id
* @throws QueryException query exception
*/
private Itr connect(final QueryContext ctx) throws QueryException {
// URL to relational database
final String url = string(checkStr(expr[0], ctx));
try {
if (expr.length > 2) {
// Credentials
final String user = string(checkStr(expr[1], ctx));
final String pass = string(checkStr(expr[2], ctx));
if (expr.length == 4) {
// Connection options
final TokenObjMap<Object> options = options(3, E_OPS, ctx);
boolean autoCommit = true;
final Object commit = options.get(AUTO_COMM);
if (commit != null) {
// Extract auto-commit mode from options
autoCommit = Boolean.parseBoolean(commit.toString());
options.delete(AUTO_COMM);
}
// Connection properties
final Properties props = connProps(options(3, E_OPS, ctx));
props.setProperty(USER, user);
props.setProperty(PASS, pass);
// Open connection
final Connection conn = getConnection(url, props);
// Set auto/commit mode
conn.setAutoCommit(autoCommit);
return Itr.get(ctx.jdbc.add(conn));
}
return Itr.get(ctx.jdbc.add(getConnection(url, user, pass)));
}
return Itr.get(ctx.jdbc.add(getConnection(url)));
} catch (final SQLException ex) {
throw SQLEXC.thrw(input, ex.getMessage());
}
}
/**
* Rollbacks all changes made during last transaction.
*
* @param ctx query context
* @return {@code null}
* @throws QueryException query exception
*/
private Item rollback(final QueryContext ctx) throws QueryException {
try {
connection(ctx, false).rollback();
return null;
} catch (final SQLException ex) {
throw SQLEXC.thrw(input, ex.getMessage());
}
}
/**
* Closes a connection to a relational database.
*
* @param ctx query context
* @return {@code null}
* @throws QueryException query exception
*/
private Item close(final QueryContext ctx) throws QueryException {
try {
connection(ctx, true).close();
return null;
} catch (final SQLException ex) {
throw SQLEXC.thrw(input, ex.getMessage());
}
}
/**
* Sets the parameter with the given index in a prepared statement.
*
* @param index parameter index
* @param stmt prepared statement
* @param paramType parameter type
* @param value parameter value
* @param isNull indicator if the parameter is null or not
* @throws QueryException query exception
*/
private void setParam(
final int index,
final PreparedStatement stmt,
final byte[] paramType,
final String value,
final boolean isNull)
throws QueryException {
try {
if (eq(BOOL, paramType)) {
if (isNull) stmt.setNull(index, Types.BOOLEAN);
else stmt.setBoolean(index, Boolean.parseBoolean(value));
} else if (eq(DATE, paramType)) {
if (isNull) stmt.setNull(index, Types.DATE);
else stmt.setDate(index, Date.valueOf(value));
} else if (eq(DOUBLE, paramType)) {
if (isNull) stmt.setNull(index, Types.DOUBLE);
else stmt.setDouble(index, Double.parseDouble(value));
} else if (eq(FLOAT, paramType)) {
if (isNull) stmt.setNull(index, Types.FLOAT);
else stmt.setFloat(index, Float.parseFloat(value));
} else if (eq(INT, paramType)) {
if (isNull) stmt.setNull(index, Types.INTEGER);
else stmt.setInt(index, Integer.parseInt(value));
} else if (eq(SHORT, paramType)) {
if (isNull) stmt.setNull(index, Types.SMALLINT);
else stmt.setShort(index, Short.parseShort(value));
} else if (eq(STRING, paramType)) {
if (isNull) stmt.setNull(index, Types.VARCHAR);
else stmt.setString(index, value);
} else if (eq(TIME, paramType)) {
if (isNull) stmt.setNull(index, Types.TIME);
else stmt.setTime(index, Time.valueOf(value));
} else if (eq(TIMESTAMP, paramType)) {
if (isNull) stmt.setNull(index, Types.TIMESTAMP);
else stmt.setTimestamp(index, Timestamp.valueOf(value));
} else {
throw SQLEXC.thrw(input, "unsupported type: " + string(paramType));
}
} catch (final SQLException ex) {
throw SQLEXC.thrw(input, ex.getMessage());
} catch (final IllegalArgumentException ex) {
throw ILLFORMAT.thrw(input, string(paramType));
}
}
/**
* Executes a query or an update statement on a relational database.
*
* @param conn connection
* @param ctx query context
* @return result
* @throws QueryException query exception
*/
private NodeCache executeQuery(final Connection conn, final QueryContext ctx)
throws QueryException {
final String query = string(checkStr(ctx.iter(expr[1]).next(), ctx));
try {
final Statement stmt = conn.createStatement();
final boolean result = stmt.execute(query);
return result ? buildResult(stmt.getResultSet()) : new NodeCache();
} catch (final SQLException ex) {
throw SQLEXC.thrw(input, ex.getMessage());
}
}
/**
* Prepares a statement and returns its id.
*
* @param ctx query context
* @return prepared statement id
* @throws QueryException query exception
*/
private Itr prepare(final QueryContext ctx) throws QueryException {
final Connection conn = connection(ctx, false);
// Prepared statement
final byte[] prepStmt = checkStr(expr[1], ctx);
try {
// Keep prepared statement
final PreparedStatement prep = conn.prepareStatement(string(prepStmt));
return Itr.get(ctx.jdbc.add(prep));
} catch (final SQLException ex) {
throw SQLEXC.thrw(input, ex.getMessage());
}
}
/**
* Executes a prepared statement.
*
* @param stmt prepared statement
* @param ctx query context
* @return result
* @throws QueryException query exception
*/
private NodeCache executePrepStmt(final PreparedStatement stmt, final QueryContext ctx)
throws QueryException {
// Get parameters for prepared statement
final ANode params = (ANode) checkType(expr[1].item(ctx, input), NodeType.ELM);
if (!params.qname().eq(E_PARAMS)) PARWHICH.thrw(input, params.qname());
try {
final int placeCount = stmt.getParameterMetaData().getParameterCount();
// Check if number of parameters equals number of place holders
if (placeCount != countParams(params)) PARAMS.thrw(input);
else setParameters(params.children(), stmt);
final boolean result = stmt.execute();
return result ? buildResult(stmt.getResultSet()) : new NodeCache();
} catch (final SQLException ex) {
throw SQLEXC.thrw(input, ex.getMessage());
}
}
