private void initFromDatabase() throws SQLException, BlockStoreException {
Statement s = conn.get().createStatement();
ResultSet rs;
rs = s.executeQuery("SELECT value FROM settings WHERE name = '" + CHAIN_HEAD_SETTING + "'");
if (!rs.next()) {
throw new BlockStoreException("corrupt Postgres block store - no chain head pointer");
}
Sha256Hash hash = new Sha256Hash(rs.getBytes(1));
rs.close();
this.chainHeadBlock = get(hash);
this.chainHeadHash = hash;
if (this.chainHeadBlock == null) {
throw new BlockStoreException("corrupt Postgres block store - head block not found");
}
rs =
s.executeQuery(
"SELECT value FROM settings WHERE name = '" + VERIFIED_CHAIN_HEAD_SETTING + "'");
if (!rs.next()) {
throw new BlockStoreException(
"corrupt Postgres block store - no verified chain head pointer");
}
hash = new Sha256Hash(rs.getBytes(1));
rs.close();
s.close();
this.verifiedChainHeadBlock = get(hash);
this.verifiedChainHeadHash = hash;
if (this.verifiedChainHeadBlock == null) {
throw new BlockStoreException("corrupt Postgres block store - verified head block not found");
}
}
public boolean delete(ByteArray key, Version maxVersion) throws PersistenceFailureException {
StoreUtils.assertValidKey(key);
Connection conn = null;
PreparedStatement selectStmt = null;
ResultSet rs = null;
String select = "select key_, version_ from " + name + " where key_ = ? for update";
try {
conn = datasource.getConnection();
selectStmt = conn.prepareStatement(select);
selectStmt.setBytes(1, key.get());
rs = selectStmt.executeQuery();
boolean deletedSomething = false;
while (rs.next()) {
byte[] theKey = rs.getBytes("key_");
byte[] version = rs.getBytes("version_");
if (new VectorClock(version).compare(maxVersion) == Occured.BEFORE) {
delete(conn, theKey, version);
deletedSomething = true;
}
}
return deletedSomething;
} catch (SQLException e) {
throw new PersistenceFailureException("Fix me!", e);
} finally {
tryClose(rs);
tryClose(selectStmt);
tryClose(conn);
}
}
@Override
public WordForm next() {
try {
if (rs.next()) {
// int nofcolumns = rs.getMetaData().getColumnCount();
WordForm w = new WordForm();
try {
w.lemmaID = new String(rs.getBytes(1), "UTF-8");
w.lemma = new String(rs.getBytes(2), "UTF-8");
w.wordform = new String(rs.getBytes(3), "UTF-8");
w.lemmaPoS = new String(rs.getBytes(4), "UTF-8");
w.tag = w.lemmaPoS;
if (this.lexiconDatabase.dumpWithFrequenciesAndDerivations) {
String wf = new String(rs.getBytes(5), "UTF-8");
String lf = new String(rs.getBytes(6), "UTF-8");
String normalized = new String(rs.getBytes(7), "UTF-8");
w.wordformFrequency = new Integer(wf);
w.lemmaFrequency = new Integer(lf);
w.modernWordform = normalized;
}
} catch (Exception e) {
e.printStackTrace();
}
return w;
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return null;
}
public Map<ByteArray, List<Versioned<byte[]>>> getAll(Iterable<ByteArray> keys)
throws VoldemortException {
StoreUtils.assertValidKeys(keys);
Connection conn = null;
PreparedStatement stmt = null;
ResultSet rs = null;
String select = "select version_, value_ from " + name + " where key_ = ?";
try {
conn = datasource.getConnection();
stmt = conn.prepareStatement(select);
Map<ByteArray, List<Versioned<byte[]>>> result = StoreUtils.newEmptyHashMap(keys);
for (ByteArray key : keys) {
stmt.setBytes(1, key.get());
rs = stmt.executeQuery();
List<Versioned<byte[]>> found = Lists.newArrayList();
while (rs.next()) {
byte[] version = rs.getBytes("version_");
byte[] value = rs.getBytes("value_");
found.add(new Versioned<byte[]>(value, new VectorClock(version)));
}
if (found.size() > 0) result.put(key, found);
}
return result;
} catch (SQLException e) {
throw new PersistenceFailureException("Fix me!", e);
} finally {
tryClose(rs);
tryClose(stmt);
tryClose(conn);
}
}
public StoredTransactionOutput getTransactionOutput(Sha256Hash hash, long index)
throws BlockStoreException {
maybeConnect();
PreparedStatement s = null;
try {
s =
conn.get()
.prepareStatement(
"SELECT height, value, scriptBytes FROM openOutputs "
+ "WHERE hash = ? AND index = ?");
s.setBytes(1, hash.getBytes());
// index is actually an unsigned int
s.setInt(2, (int) index);
ResultSet results = s.executeQuery();
if (!results.next()) {
return null;
}
// Parse it.
int height = results.getInt(1);
BigInteger value = new BigInteger(results.getBytes(2));
// Tell the StoredTransactionOutput that we are a coinbase, as that is encoded in height
StoredTransactionOutput txout =
new StoredTransactionOutput(hash, index, value, height, true, results.getBytes(3));
return txout;
} catch (SQLException ex) {
throw new BlockStoreException(ex);
} finally {
if (s != null)
try {
s.close();
} catch (SQLException e) {
throw new BlockStoreException("Failed to close PreparedStatement");
}
}
}
public void put(ByteArray key, Versioned<byte[]> value) throws PersistenceFailureException {
StoreUtils.assertValidKey(key);
boolean doCommit = false;
Connection conn = null;
PreparedStatement insert = null;
PreparedStatement select = null;
ResultSet results = null;
String insertSql = "insert into " + name + " (key_, version_, value_) values (?, ?, ?)";
String selectSql = "select key_, version_ from " + name + " where key_ = ?";
try {
conn = datasource.getConnection();
conn.setAutoCommit(false);
// check for superior versions
select = conn.prepareStatement(selectSql);
select.setBytes(1, key.get());
results = select.executeQuery();
while (results.next()) {
byte[] thisKey = results.getBytes("key_");
VectorClock version = new VectorClock(results.getBytes("version_"));
Occured occured = value.getVersion().compare(version);
if (occured == Occured.BEFORE)
throw new ObsoleteVersionException(
"Attempt to put version "
+ value.getVersion()
+ " which is superceeded by "
+ version
+ ".");
else if (occured == Occured.AFTER) delete(conn, thisKey, version.toBytes());
}
// Okay, cool, now put the value
insert = conn.prepareStatement(insertSql);
insert.setBytes(1, key.get());
VectorClock clock = (VectorClock) value.getVersion();
insert.setBytes(2, clock.toBytes());
insert.setBytes(3, value.getValue());
insert.executeUpdate();
doCommit = true;
} catch (SQLException e) {
if (e.getErrorCode() == MYSQL_ERR_DUP_KEY || e.getErrorCode() == MYSQL_ERR_DUP_ENTRY) {
throw new ObsoleteVersionException("Key or value already used.");
} else {
throw new PersistenceFailureException("Fix me!", e);
}
} finally {
if (conn != null) {
try {
if (doCommit) conn.commit();
else conn.rollback();
} catch (SQLException e) {
}
}
tryClose(results);
tryClose(insert);
tryClose(select);
tryClose(conn);
}
}
protected List<Message> exportMessages() {
if (!this.running) return null;
// get user configured column names and sql
String colId = (String) this.properties.get(PROPKEY_MESSAGE_ID);
String colTopic = (String) this.properties.get(PROPKEY_MESSAGE_TOPIC);
String colBoard = (String) this.properties.get(PROPKEY_MESSAGE_BOARD);
String colUserid = (String) this.properties.get(PROPKEY_MESSAGE_USERID);
String colUsername = (String) this.properties.get(PROPKEY_MESSAGE_USERNAME);
String colUseremail = (String) this.properties.get(PROPKEY_MESSAGE_USEREMAIL);
String colTime = (String) this.properties.get(PROPKEY_MESSAGE_TIME);
String colTitle = (String) this.properties.get(PROPKEY_MESSAGE_TITLE);
String colContent = (String) this.properties.get(PROPKEY_MESSAGE_CONTENT);
String colIsfirst = (String) this.properties.get(PROPKEY_MESSAGE_ISFIRST);
String colFirstid = (String) this.properties.get(PROPKEY_MESSAGE_FIRSTID);
String table = (String) this.properties.get(PROPKEY_MESSAGE_TABLE);
String rawSql = (String) this.properties.get(PROPKEY_MESSAGE_SQL);
// update sql to reflect user configured column names
String sql = rawSql.replaceAll("\\Q{" + PROPKEY_MESSAGE_ID + "}\\E", colId);
sql = sql.replaceAll("\\Q{" + PROPKEY_MESSAGE_TOPIC + "}\\E", colTopic);
sql = sql.replaceAll("\\Q{" + PROPKEY_MESSAGE_BOARD + "}\\E", colBoard);
sql = sql.replaceAll("\\Q{" + PROPKEY_MESSAGE_USERID + "}\\E", colUserid);
sql = sql.replaceAll("\\Q{" + PROPKEY_MESSAGE_USERNAME + "}\\E", colUsername);
sql = sql.replaceAll("\\Q{" + PROPKEY_MESSAGE_USEREMAIL + "}\\E", colUseremail);
sql = sql.replaceAll("\\Q{" + PROPKEY_MESSAGE_TIME + "}\\E", colTime);
sql = sql.replaceAll("\\Q{" + PROPKEY_MESSAGE_TITLE + "}\\E", colTitle);
sql = sql.replaceAll("\\Q{" + PROPKEY_MESSAGE_CONTENT + "}\\E", colContent);
sql = sql.replaceAll("\\Q{" + PROPKEY_MESSAGE_ISFIRST + "}\\E", colIsfirst);
sql = sql.replaceAll("\\Q{" + PROPKEY_MESSAGE_FIRSTID + "}\\E", colFirstid);
sql = sql.replaceAll("\\Q{" + PROPKEY_MESSAGE_TABLE + "}\\E", table);
Vector<Message> messages = new Vector<Message>();
ResultSet data = null;
try {
data = sql(sql);
while (data.next()) { // get each category
Message message = new Message();
message.id = data.getString(colId);
message.topic = data.getString(colTopic);
message.board = data.getString(colBoard);
message.userid = data.getString(colUserid);
message.username = data.getString(colUsername);
message.useremail = data.getString(colUseremail);
message.time = data.getString(colTime);
message.title = encode(data.getBytes(colTitle));
message.content = encode(data.getBytes(colContent));
message.isfirst = data.getBoolean(colIsfirst);
message.firstid = data.getString(colFirstid);
messages.add(message);
}
} catch (SQLException e) {
log.error("Could not export Messages with sql: " + sql);
e.printStackTrace();
return null;
}
return messages;
}
@Override
public ArrayList<OrderPO> findWithdCondition(String nameOfCourier, DocumentCondition dCondition)
throws RemoteException {
// TODO Auto-generated method stub
String sql =
"select* from orderpo where documentcondition='"
+ dCondition
+ "' and nameofcourier='"
+ nameOfCourier
+ "';";
ResultSet result = null;
OrderPO po = null;
ArrayList<OrderPO> pos = new ArrayList<OrderPO>();
ReceiverPO receiver = null;
SenderPO sender = null;
BillPO bill = null;
GoodsPO goods = null;
try {
result = Helper.find(sql);
while (result.next()) {
try {
// ObjectInputStream oips = new ObjectInputStream(result.getBinaryStream("receiver"));
receiver = (ReceiverPO) IOObject.getArray(result.getBytes("receiver"));
// oips = new ObjectInputStream(result.getBinaryStream("sender"));
sender = (SenderPO) IOObject.getArray(result.getBytes("sender"));
// oips = new ObjectInputStream(result.getBinaryStream("bill"));
bill = (BillPO) IOObject.getArray(result.getBytes("bill"));
// oips = new ObjectInputStream(result.getBinaryStream("goods"));
goods = (GoodsPO) IOObject.getArray(result.getBytes("goods"));
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}
po =
new OrderPO(
receiver,
sender,
bill,
goods,
result.getString(5),
result.getString(6),
result.getString(7),
result.getString(8),
result.getString(9),
DocumentCondition.valueOf(result.getString(10)));
pos.add(po);
}
} catch (Exception e) {
e.printStackTrace();
}
return pos;
}
@Test
public void set() throws SQLException {
ResultSet rs;
PreparedStatement prep = conn.prepareStatement("select ?, ?, ?;");
// integers
prep.setInt(1, Integer.MIN_VALUE);
prep.setInt(2, Integer.MAX_VALUE);
prep.setInt(3, 0);
rs = prep.executeQuery();
assertTrue(rs.next());
assertEquals(Integer.MIN_VALUE, rs.getInt(1));
assertEquals(Integer.MAX_VALUE, rs.getInt(2));
assertEquals(0, rs.getInt(3));
// strings
String name = "Winston Leonard Churchill";
String fn = name.substring(0, 7), mn = name.substring(8, 15), sn = name.substring(16, 25);
prep.clearParameters();
prep.setString(1, fn);
prep.setString(2, mn);
prep.setString(3, sn);
prep.executeQuery();
assertTrue(rs.next());
assertEquals(fn, rs.getString(1));
assertEquals(mn, rs.getString(2));
assertEquals(sn, rs.getString(3));
// mixed
prep.setString(1, name);
prep.setString(2, null);
prep.setLong(3, Long.MAX_VALUE);
prep.executeQuery();
assertTrue(rs.next());
assertEquals(name, rs.getString(1));
assertNull(rs.getString(2));
assertTrue(rs.wasNull());
assertEquals(Long.MAX_VALUE, rs.getLong(3));
// bytes
prep.setBytes(1, b1);
prep.setBytes(2, b2);
prep.setBytes(3, b3);
prep.executeQuery();
assertTrue(rs.next());
assertArrayEq(rs.getBytes(1), b1);
assertArrayEq(rs.getBytes(2), b2);
assertArrayEq(rs.getBytes(3), b3);
assertFalse(rs.next());
rs.close();
}
// this function should only be used while the sql resultset is still valid,
// i.e. the connection related to the resultset is still valid (not closed)
public static String getSqlResult(ResultSet resultSet) throws SQLException {
StringBuffer stringBuffer = new StringBuffer();
List columnLabels = new ArrayList<String>();
try {
int columnCount = resultSet.getMetaData().getColumnCount();
for (int i = 1; i <= columnCount; i++) {
columnLabels.add(resultSet.getMetaData().getColumnLabel(i));
}
List<Integer> types = Lists.newArrayList();
for (int i = 1; i <= columnCount; i++) {
types.add(resultSet.getMetaData().getColumnType(i));
}
LOG.debug("Result set data types:");
LOG.debug(Utils.getTypesInStrings(types));
stringBuffer.append(new ColumnList(types, columnLabels).toString() + "\n");
while (resultSet.next()) {
List<Object> values = Lists.newArrayList();
for (int i = 1; i <= columnCount; i++) {
try {
if (resultSet.getObject(i) == null) {
values.add(null);
continue;
}
if (resultSet.getMetaData().getColumnType(i) == Types.NVARCHAR) {
values.add(new String(resultSet.getBytes(i), "UTF-16"));
} else {
values.add(new String(resultSet.getBytes(i), "UTF-8"));
}
} catch (Exception e) {
if (resultSet.getMetaData().getColumnType(i) == Types.DATE) {
values.add(resultSet.getDate(i));
} else {
values.add(resultSet.getObject(i));
}
}
}
stringBuffer.append(new ColumnList(types, values).toString() + "\n");
}
} catch (IllegalArgumentException | IllegalAccessException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
} finally {
if (resultSet != null) {
resultSet.close();
}
}
return stringBuffer.toString();
}
public Pair<ByteArray, Versioned<byte[]>> next() {
try {
if (!this.hasMore)
throw new PersistenceFailureException(
"Next called on iterator, but no more items available!");
ByteArray key = new ByteArray(rs.getBytes("key_"));
byte[] value = rs.getBytes("value_");
VectorClock clock = new VectorClock(rs.getBytes("version_"));
this.hasMore = rs.next();
return Pair.create(key, new Versioned<byte[]>(value, clock));
} catch (SQLException e) {
throw new PersistenceFailureException(e);
}
}
public HashMap<Integer, CraftingRecipe> loadCraftingRecipes() {
final HashMap<Integer, CraftingRecipe> list = new HashMap<Integer, CraftingRecipe>();
try {
final PreparedStatement ps =
ItemDatabase.queries.prepare("SELECT * FROM crafting_recipes where isactive = 1");
final ResultSet rs = ItemDatabase.queries.executeSelect(ps);
if (rs != null) {
while (rs.next()) {
Log.debug("CRAFTING: loading recipe:" + rs.getInt("id"));
final CraftingRecipe recipe =
new CraftingRecipe(
rs.getInt("id"), HelperFunctions.readEncodedString(rs.getBytes("name")));
recipe.setResultItemId(rs.getInt("resultItemID"));
recipe.setResultItemCount(rs.getInt("resultItemCount"));
recipe.setSkillID(rs.getInt("skillID"));
recipe.setRequiredSkillLevel(rs.getInt("skillLevelReq"));
recipe.setRecipeItemId(rs.getInt("recipeItemID"));
recipe.setQualityChangeable(rs.getBoolean("qualityChangeable"));
recipe.setAllowDyes(rs.getBoolean("allowDyes"));
recipe.setAllowEssences(rs.getBoolean("allowEssences"));
recipe.setStationReq(HelperFunctions.readEncodedString(rs.getBytes("stationReq")));
recipe.setMustMatchLayout(rs.getBoolean("layoutReq"));
for (int i = 0; i < CraftingPlugin.GRID_SIZE; ++i) {
final LinkedList<CraftingComponent> componentRow = new LinkedList<CraftingComponent>();
for (int j = 0; j < CraftingPlugin.GRID_SIZE; ++j) {
final CraftingComponent component =
new CraftingComponent(
"",
rs.getInt("component" + (i * CraftingPlugin.GRID_SIZE + j + 1) + "Count"),
rs.getInt("component" + (i * CraftingPlugin.GRID_SIZE + j + 1)));
componentRow.add(component);
Log.debug(
"CRAFTING: adding item: "
+ component.getItemId()
+ " to row: "
+ i
+ " in column: "
+ j);
}
recipe.addCraftingComponentRow(componentRow);
}
list.put(recipe.getID(), recipe);
Log.debug("CRAFTING: put recipe:" + recipe.getID());
}
}
} catch (SQLException e) {
e.printStackTrace();
}
return list;
}
@Deprecated
@Override
public List<Conflict> getConflicts(final @Nullable String ns, final @Nullable String pathFilter) {
final String namespace = namespace(ns);
final String sql;
if (pathFilter == null) {
sql =
format(
"SELECT path,ancestor,ours,theirs FROM %s WHERE repository = ? AND namespace = ?",
conflictsTable);
} else {
sql =
format(
"SELECT path,ancestor,ours,theirs FROM %s WHERE repository = ? AND namespace = ? AND path LIKE ?",
conflictsTable);
}
List<Conflict> conflicts = new ArrayList<>();
try (Connection cx = dataSource.getConnection()) {
try (PreparedStatement ps = cx.prepareStatement(sql)) {
ps.setInt(1, repositoryId);
ps.setString(2, namespace);
if (pathFilter != null) {
ps.setString(3, pathFilter + "%");
}
log(sql, LOG, repositoryId, namespace);
try (ResultSet rs = ps.executeQuery()) {
while (rs.next()) {
String path = rs.getString(1);
@Nullable byte[] ancestorb = rs.getBytes(2);
ObjectId ancestor;
if (ancestorb == null) {
ancestor = ObjectId.NULL;
} else {
ancestor = ObjectId.createNoClone(ancestorb);
}
ObjectId ours = ObjectId.createNoClone(rs.getBytes(3));
ObjectId theirs = ObjectId.createNoClone(rs.getBytes(4));
conflicts.add(new Conflict(path, ancestor, ours, theirs));
}
}
}
} catch (SQLException e) {
throw propagate(e);
}
return conflicts;
}
List<Conflict> getBatch(
@Nullable String namespace, @Nullable String treePath, int offset, int limit)
throws SQLException {
checkArgument(offset >= 0);
checkArgument(limit > 0);
final String sql;
{
StringBuilder sb =
new StringBuilder("SELECT path,ancestor,ours,theirs FROM ")
.append(conflictsTable)
.append(" WHERE repository = ? AND namespace = ?");
if (treePath != null) {
sb.append(" AND (path = ? OR path LIKE ?)");
}
sb.append(" ORDER BY repository, namespace, path OFFSET ")
.append(offset)
.append(" LIMIT ")
.append(limit);
sql = sb.toString();
}
List<Conflict> batch = new ArrayList<>();
try (Connection cx = dataSource.getConnection()) {
try (PreparedStatement ps = cx.prepareStatement(sql)) {
ps.setInt(1, repositoryId);
ps.setString(2, namespace(namespace));
if (treePath != null) {
ps.setString(3, treePath);
ps.setString(4, treePath + "/%");
}
log(sql, LOG, repositoryId, namespace);
try (ResultSet rs = ps.executeQuery()) {
while (rs.next()) {
String path = rs.getString(1);
@Nullable byte[] ancestorb = rs.getBytes(2);
ObjectId ancestor =
ancestorb == null ? ObjectId.NULL : ObjectId.createNoClone(ancestorb);
ObjectId ours = ObjectId.createNoClone(rs.getBytes(3));
ObjectId theirs = ObjectId.createNoClone(rs.getBytes(4));
batch.add(new Conflict(path, ancestor, ours, theirs));
}
}
}
}
return batch;
}
public StoredBlock get(Sha256Hash hash, boolean wasUndoableOnly) throws BlockStoreException {
// Optimize for chain head
if (chainHeadHash != null && chainHeadHash.equals(hash)) return chainHeadBlock;
if (verifiedChainHeadHash != null && verifiedChainHeadHash.equals(hash))
return verifiedChainHeadBlock;
maybeConnect();
PreparedStatement s = null;
try {
s =
conn.get()
.prepareStatement(
"SELECT chainWork, height, header, wasUndoable FROM headers WHERE hash = ?");
// We skip the first 4 bytes because (on prodnet) the minimum target has 4 0-bytes
byte[] hashBytes = new byte[28];
System.arraycopy(hash.getBytes(), 3, hashBytes, 0, 28);
s.setBytes(1, hashBytes);
ResultSet results = s.executeQuery();
if (!results.next()) {
return null;
}
// Parse it.
if (wasUndoableOnly && !results.getBoolean(4)) return null;
BigInteger chainWork = new BigInteger(results.getBytes(1));
int height = results.getInt(2);
Block b = new Block(params, results.getBytes(3));
b.verifyHeader();
StoredBlock stored = new StoredBlock(b, chainWork, height);
return stored;
} catch (SQLException ex) {
throw new BlockStoreException(ex);
} catch (ProtocolException e) {
// Corrupted database.
throw new BlockStoreException(e);
} catch (VerificationException e) {
// Should not be able to happen unless the database contains bad
// blocks.
throw new BlockStoreException(e);
} finally {
if (s != null)
try {
s.close();
} catch (SQLException e) {
throw new BlockStoreException("Failed to close PreparedStatement");
}
}
}
/**
* Generates physical or logical plan files.
*
* @param statement statement used
* @param planFileName name of plan file
* @param queryString query for which plan is being generated
* @param type physical or logical
* @param timeout time allowed for generation of plan file
* @throws Exception
*/
public void generatePlan(
Statement statement, String planFileName, String queryString, String type, long timeout)
throws Exception {
String query = "explain plan ";
if (type.equals("logical")) {
query += "without implementation ";
}
query += "for " + queryString;
ResultSet resultSet = null;
String planString = "";
try {
LOG.debug("Submitting query:\n" + query.trim());
RunThread runThread = new RunThread(statement, query);
processThread(runThread, timeout);
resultSet = runThread.getResultSet();
resultSet.next();
resultSet.next();
planString = new String(resultSet.getBytes(2));
} finally {
if (resultSet != null) {
resultSet.close();
}
}
BufferedWriter writer = new BufferedWriter(new FileWriter(new File(planFileName)));
writer.write(planString);
writer.close();
}
protected List<Category> exportCategories() {
if (!this.running) return null;
// get user configured column names and sql
String colId = (String) this.properties.get(PROPKEY_CAT_ID);
String colName = (String) this.properties.get(PROPKEY_CAT_NAME);
String table = (String) this.properties.get(PROPKEY_CAT_TABLE);
String rawSql = (String) this.properties.get(PROPKEY_CAT_SQL);
// update sql to reflect user configured column names
String sql = rawSql.replaceAll("\\Q{" + PROPKEY_CAT_ID + "}\\E", colId);
sql = sql.replaceAll("\\Q{" + PROPKEY_CAT_NAME + "}\\E", colName);
sql = sql.replaceAll("\\Q{" + PROPKEY_CAT_TABLE + "}\\E", table);
Vector<Category> categories = new Vector<Category>();
ResultSet data = null;
try {
data = sql(sql);
while (data.next()) { // get each category
Category cat = new Category();
cat.id = data.getString(colId);
cat.name = encode(data.getBytes(colName));
categories.add(cat);
}
} catch (SQLException e) {
log.error("Could not export Categories with sql: " + sql);
e.printStackTrace();
return null;
}
return categories;
}
/**
* Blob 데이타의 한 필드를 가져 온다.
*
* @param sql String
* @return byte[]
* @throws SQLException
*/
public byte[] slectBlob(String sql) throws SQLException {
KJFLog.sql(sql);
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
byte[] lb = null;
try {
conn = this.getConnection();
ps = conn.prepareStatement(sql);
rs = ps.executeQuery();
;
if (rs.next()) {
lb = rs.getBytes(1);
}
} catch (SQLException e) {
System.out.println(e);
throw e;
} finally {
if (rs != null) rs.close();
if (ps != null) ps.close();
this.release(conn);
}
return lb;
}
public static SummaryCollection getSummaryCollections() throws Exception {
PreparedStatement pstmt = conn.prepareStatement(READ_OBJECT_SQL_ALL);
ResultSet rs = pstmt.executeQuery();
SummaryCollection sc = new SummaryCollection();
while (rs.next()) {
byte[] buf = rs.getBytes(1);
ObjectInputStream objectIn = null;
if (buf != null) {
objectIn = new ObjectInputStream(new ByteArrayInputStream(buf));
}
Object sumObject = objectIn.readObject();
Summary sum = (Summary) sumObject;
sc.appendSummary(sum);
}
try {
if (rs != null) {
rs.close();
}
if (pstmt != null) {
pstmt.close();
}
} catch (Exception E) {
metrixLogger.log.log(
Level.SEVERE, "Error in closing resource sets of SQL Connection. {0}", E.toString());
}
return sc;
}
@Override
public byte[] packLog(long index, int itemsToPack) {
if (index < this.startIndex.get() || index >= this.nextIndex.get()) {
throw new IllegalArgumentException("index out of range");
}
try {
ByteArrayOutputStream memoryStream = new ByteArrayOutputStream();
GZIPOutputStream gzipStream = new GZIPOutputStream(memoryStream);
PreparedStatement ps = this.connection.prepareStatement(SELECT_RANGE_SQL);
ps.setLong(1, index);
ps.setLong(2, index + itemsToPack);
ResultSet rs = ps.executeQuery();
while (rs.next()) {
byte[] value = rs.getBytes(4);
int size = value.length + Long.BYTES + 1 + Integer.BYTES;
ByteBuffer buffer = ByteBuffer.allocate(size);
buffer.putInt(size);
buffer.putLong(rs.getLong(2));
buffer.put(rs.getByte(3));
buffer.put(value);
gzipStream.write(buffer.array());
}
rs.close();
gzipStream.flush();
memoryStream.flush();
gzipStream.close();
return memoryStream.toByteArray();
} catch (Throwable error) {
this.logger.error("failed to pack log entries", error);
throw new RuntimeException("log store error", error);
}
}
/**
* Submits query via JDBC
*
* @param query SQL query string
* @param statement sql statement to execute the query with
* @param outputFilename name of file result set is to be written to
* @param timeout time allowed for query execution
* @throws Exception
*/
public void submitQueryJDBC(
String query, Statement statement, String outputFilename, long timeout) throws Exception {
BufferedWriter writer = null;
if (outputFilename != null) {
writer = new BufferedWriter(new FileWriter(new File(outputFilename)));
}
ResultSet resultSet = null;
try {
RunThread runThread = new RunThread(statement, query);
processThread(runThread, timeout);
resultSet = runThread.getResultSet();
if (resultSet == null) {
throw runThread.getException();
}
if (outputFilename == null) {
return;
}
int columnCount = resultSet.getMetaData().getColumnCount();
columnLabels = new ArrayList<String>();
for (int i = 1; i <= columnCount; i++) {
columnLabels.add(resultSet.getMetaData().getColumnLabel(i));
}
Object[] types = new Object[columnCount];
for (int i = 1; i <= columnCount; i++) {
types[i - 1] = resultSet.getMetaData().getColumnType(i);
}
ColumnList.setTypes(types);
LOG.debug("Result set data types:");
LOG.debug(Utils.getTypesInStrings(ColumnList.getTypes()));
while (resultSet.next()) {
Object[] values = new Object[columnCount];
for (int i = 1; i <= columnCount; i++) {
try {
if (resultSet.getObject(i) == null) {
values[i - 1] = null;
continue;
}
values[i - 1] = new String(resultSet.getBytes(i));
} catch (Exception e) {
if (resultSet.getMetaData().getColumnType(i) == Types.DATE) {
values[i - 1] = resultSet.getDate(i);
} else {
values[i - 1] = resultSet.getObject(i);
}
}
}
ColumnList columnList = new ColumnList(values);
if (writer != null) {
writer.write(columnList + "\n");
}
}
if (writer != null) {
writer.close();
}
} finally {
if (resultSet != null) {
resultSet.close();
}
}
}
private ElementClassifier loadClassifierModel(Extractor extractor) {
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
con = Application.getDataSource().getConnection();
pstmt = con.prepareStatement("SELECT cmodel FROM extractors WHERE id = ?");
pstmt.setInt(1, extractor.getId());
rs = pstmt.executeQuery();
if (rs.next()) {
byte[] buf = rs.getBytes(1);
if (buf != null) {
ElementClassifier c =
ElementClassifier.readElementClassifier(new ByteArrayInputStream(buf));
System.out.println(c);
return c;
}
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
rs.close();
pstmt.close();
con.close();
} catch (Exception e) {
}
}
return null;
}
/* (non-Javadoc)
* @see org.hibernate.UserType#nullSafeGet(java.sql.ResultSet, java.lang.String[], java.lang.Object)
*/
public Object nullSafeGet(ResultSet rs, String[] names, Object owner)
throws HibernateException, SQLException {
byte[] bytes = rs.getBytes(names[0]);
if (rs.wasNull()) {
return null;
}
// TODO figure out how to inject this
HomeFactory homeFactory = (HomeFactory) ComponentManager.getInstance().get("xmlHomeFactory");
WritableObjectHome home = (WritableObjectHome) homeFactory.getHome("agent");
StructuredArtifact artifact = (StructuredArtifact) home.createInstance();
ByteArrayInputStream in = new ByteArrayInputStream(bytes);
SAXBuilder saxBuilder = new SAXBuilder();
saxBuilder.setFeature(
"http://apache.org/xml/features/disallow-doctype-decl", true); // SAK-23245
try {
Document doc = saxBuilder.build(in);
artifact.setBaseElement(doc.getRootElement());
} catch (JDOMException e) {
throw new HibernateException(e);
} catch (IOException e) {
throw new HibernateException(e);
}
return artifact;
}
protected List<PingData> readAll(Connection connection, String clustername) throws SQLException {
PreparedStatement ps = connection.prepareStatement(select_all_pingdata_sql);
try {
ps.setString(1, clustername);
ResultSet resultSet = ps.executeQuery();
ArrayList<PingData> results = new ArrayList<PingData>();
while (resultSet.next()) {
byte[] bytes = resultSet.getBytes(1);
PingData pingData = null;
try {
pingData = deserialize(bytes);
results.add(pingData);
} catch (Exception e) {
int row = resultSet.getRow();
log.error(
"%s: failed deserializing row %d: %s; removing it from the table",
local_addr, row, e);
try {
resultSet.deleteRow();
} catch (Throwable t) {
log.error(
"%s: failed removing row %d: %s; please delete it manually", local_addr, row, e);
}
}
}
return results;
} finally {
ps.close();
}
}
public ArrayList<Product2> BindTable() {
ArrayList<Product2> list = new ArrayList<Product2>();
Connection cox = getConnection();
Statement st;
ResultSet rs;
try {
st = cox.createStatement();
rs = st.executeQuery("SELECT images FROM javacv.jimages");
Product2 p;
while (rs.next()) {
p = new Product2(rs.getBytes("images"));
// rs.getString("ID_PRO"),
// rs.getString("PRO_NAME"),
// rs.getInt("QTE_IN_STOCK"),
// rs.getString("PRICE"),
// rs.getInt("ID_CAT")
list.add(p);
}
} catch (SQLException ex) {
// Logger.getLogger(MyQuery.class.getName()).log(Level.SEVERE, null, ex);
}
return list;
}
private Vote(ResultSet rs) throws SQLException {
this.id = rs.getLong("id");
this.dbKey = voteDbKeyFactory.newKey(this.id);
this.pollId = rs.getLong("poll_id");
this.voterId = rs.getLong("voter_id");
this.voteBytes = rs.getBytes("vote_bytes");
}
/**
* See if there are array elements.
*
* @param dbl0 Value of the first (maybe only) array element
* @param result ResultSet for the sample table with blob
* @return Array with given element and maybe more.
* @throws Exception on error, including 'cancel'
*/
private double[] readBlobArrayElements(final double dbl0, final ResultSet result)
throws Exception {
final String datatype;
if (reader.isOracle()) datatype = result.getString(7);
else datatype = result.getString(8);
// ' ' or NULL indicate: Scalar, not an array
if (datatype == null || " ".equals(datatype) || result.wasNull()) return new double[] {dbl0};
// Decode BLOB
final byte[] bytes = result.getBytes(reader.isOracle() ? 8 : 9);
final ByteArrayInputStream stream = new ByteArrayInputStream(bytes);
final DataInputStream data = new DataInputStream(stream);
if ("d".equals(datatype)) { // Read Double typed array elements
final int nelm = data.readInt();
final double[] array = new double[nelm];
for (int i = 0; i < nelm; i++) array[i] = data.readDouble();
data.close();
return array;
}
// TODO Decode 'l' Long and 'i' Integer?
else {
throw new Exception("Sample BLOBs of type '" + datatype + "' are not decoded");
}
}
public List<Detetive> Listar() {
List<Detetive> lista = new ArrayList<Detetive>();
String sql = "SELECT * FROM detetive";
PreparedStatement psm = Conexao.getPreparedStatement(sql);
try {
ResultSet resultado = psm.executeQuery();
while (resultado.next()) {
Detetive obj = new Detetive();
obj.setDetetiveid(resultado.getInt("detetiveid"));
obj.setNome(resultado.getString("nome"));
obj.setEmail(resultado.getString("email"));
obj.setNcasos(resultado.getInt("numerocasos"));
obj.setEquipe(resultado.getString("nomeequipe"));
obj.setImagem(resultado.getBytes("imagem"));
lista.add(obj);
}
} catch (SQLException ex) {
System.out.println("Erro ao acessar o banco: " + ex.getMessage().toString());
lista = null;
}
return lista;
}
/*
* (non-Javadoc)
* @see org.datanucleus.store.rdbms.mapping.AbstractLargeBinaryRDBMSMapping#getObject(java.lang.Object,
* int)
*/
@Override
public Object getObject(ResultSet rs, int param) {
byte[] bytes = null;
try {
// Retrieve the bytes of the object directly
bytes = rs.getBytes(param);
if (bytes == null) {
return null;
}
} catch (SQLException sqle) {
try {
// Retrieve the bytes using the Blob (if getBytes not supported e.g HSQLDB 2.0)
Blob blob = rs.getBlob(param);
if (blob == null) {
return null;
}
bytes = blob.getBytes(1, (int) blob.length());
if (bytes == null) {
return null;
}
} catch (SQLException sqle2) {
throw new NucleusDataStoreException(
Localiser.msg("055002", "Object", "" + param, column, sqle2.getMessage()), sqle2);
}
}
return getObjectForBytes(bytes, param);
}
final void initHuman() {
String sql = "Select * from tblHuman where id = ?";
try (Connection cn = Tools.getConn();
PreparedStatement pst = cn.prepareStatement(sql); ) {
pst.setInt(1, Integer.parseInt(vRow.get(0).toString()));
ResultSet rs = pst.executeQuery();
if (rs.next()) {
txtUserName.setText(rs.getString(2));
txtBirthDay.setDate(rs.getDate(3));
txtGender.setText(rs.getString(4));
txtBirthPlace.setText(rs.getString(5));
txtNativeCountry.setText(rs.getString(6));
txtNation.setText(rs.getString(7));
txtReligion.setText(rs.getString(8));
txtOccupation.setText(rs.getString(9));
txtWorkPlace.setText(rs.getString(10));
txtIDCard.setText(rs.getString(11));
txtArrivalDate.setDate(rs.getDate(12));
byte[] b = rs.getBytes(13);
ImageIcon icon = new ImageIcon(b);
Image img = icon.getImage();
img = img.getScaledInstance(lblImage.getWidth(), lblImage.getHeight(), Image.SCALE_SMOOTH);
lblImage.setIcon(new ImageIcon(img));
txtRoomID.setText(rs.getString(14));
txtEmail.setText(rs.getString(15));
}
} catch (SQLException ex) {
Logger.getLogger(EditHumanDialog.class.getName()).log(Level.SEVERE, null, ex);
}
}
