// Редактирует заданный ресторан а БД
public static boolean editRestaurant(
int id, String name, String review, Byte cuisine, Byte interior, Byte service) {
boolean result = false;
try {
Connection conn = getDBConnection();
PreparedStatement stmt =
conn.prepareStatement(
"UPDATE restaurants SET name = ?, review = ?, cuisine_rating = ?, interior_rating = ?, service_rating = ? WHERE id = ?");
stmt.setString(1, name);
stmt.setString(2, review);
stmt.setByte(3, cuisine);
stmt.setByte(4, interior);
stmt.setByte(5, service);
stmt.setInt(6, id);
stmt.executeUpdate();
conn.close();
result = true;
} catch (Exception e) {
System.err.println(e.getClass().getName() + ": " + e.getMessage());
// e.printStackTrace();
// System.exit(0);
}
return result;
}
/**
* Save the ledger entry
*
* @param con Database connection
* @throws SQLException Database error occurred
*/
private void save(Connection con) throws SQLException {
try (PreparedStatement stmt =
con.prepareStatement(
"INSERT INTO account_ledger "
+ "(account_id, event_type, event_id, holding_type, holding_id, change, balance, "
+ "block_id, height, timestamp) "
+ "VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
Statement.RETURN_GENERATED_KEYS)) {
int i = 0;
stmt.setLong(++i, accountId);
stmt.setByte(++i, (byte) event.getCode());
stmt.setLong(++i, eventId);
if (holding != null) {
stmt.setByte(++i, (byte) holding.getCode());
} else {
stmt.setByte(++i, (byte) -1);
}
DbUtils.setLong(stmt, ++i, holdingId);
stmt.setLong(++i, change);
stmt.setLong(++i, balance);
stmt.setLong(++i, blockId);
stmt.setInt(++i, height);
stmt.setInt(++i, timestamp);
stmt.executeUpdate();
try (ResultSet rs = stmt.getGeneratedKeys()) {
if (rs.next()) {
ledgerId = rs.getLong(1);
}
}
}
}
private void setParameter(PreparedStatement stmt, List<Object> paramList) throws SQLException {
for (int i = 0; i < paramList.size(); i++) {
Object param = paramList.get(i);
if (param instanceof Arrays) {
List<Object> list = Arrays.asList(param);
for (Object obj : list) {
if (obj instanceof String) {
stmt.setString(i + 1, (String) paramList.get(i));
} else if (obj instanceof Integer) {
stmt.setInt(i + 1, (Integer) paramList.get(i));
} else if (obj instanceof Double) {
stmt.setDouble(i + 1, (Double) paramList.get(i));
} else if (param instanceof Long) {
stmt.setLong(i + 1, (Long) paramList.get(i));
} else if (param instanceof Float) {
stmt.setFloat(i + 1, (Float) paramList.get(i));
} else if (param instanceof Short) {
stmt.setShort(i + 1, (Short) paramList.get(i));
} else if (param instanceof Byte) {
stmt.setByte(i + 1, (Byte) paramList.get(i));
} else if (param instanceof Boolean) {
stmt.setBoolean(i + 1, (Boolean) paramList.get(i));
} else if (param instanceof Date) {
stmt.setDate(i + 1, (Date) paramList.get(i));
} else if (param instanceof Timestamp) {
stmt.setTimestamp(i + 1, (Timestamp) paramList.get(i));
} else if (param instanceof Object) {
stmt.setObject(i + 1, (Object) paramList.get(i));
} else if (param instanceof Arrays) {
stmt.setObject(i + 1, (Object) paramList.get(i));
}
}
}
if (param instanceof String) {
stmt.setString(i + 1, (String) paramList.get(i));
} else if (param instanceof Integer) {
stmt.setInt(i + 1, (Integer) paramList.get(i));
} else if (param instanceof Double) {
stmt.setDouble(i + 1, (Double) paramList.get(i));
} else if (param instanceof Long) {
stmt.setLong(i + 1, (Long) paramList.get(i));
} else if (param instanceof Float) {
stmt.setFloat(i + 1, (Float) paramList.get(i));
} else if (param instanceof Short) {
stmt.setShort(i + 1, (Short) paramList.get(i));
} else if (param instanceof Byte) {
stmt.setByte(i + 1, (Byte) paramList.get(i));
} else if (param instanceof Boolean) {
stmt.setBoolean(i + 1, (Boolean) paramList.get(i));
} else if (param instanceof Date) {
stmt.setDate(i + 1, (Date) paramList.get(i));
} else if (param instanceof Timestamp) {
stmt.setTimestamp(i + 1, (Timestamp) paramList.get(i));
} else if (param instanceof Object) {
stmt.setObject(i + 1, (Object) paramList.get(i));
} else if (param instanceof Arrays) {
stmt.setObject(i + 1, (Object) paramList.get(i));
}
}
}
// Добавляет ресторан в БД
public static int addRestaurant(
String name, String review, Byte cuisine, Byte interior, Byte service) {
int result = 0;
try {
Connection conn = getDBConnection();
PreparedStatement stmt =
conn.prepareStatement(
"INSERT INTO restaurants(name, review, cuisine_rating, interior_rating, service_rating) VALUES (?, ?, ?, ?, ?)");
stmt.setString(1, name);
stmt.setString(2, review);
stmt.setByte(3, cuisine);
stmt.setByte(4, interior);
stmt.setByte(5, service);
stmt.executeUpdate();
// Находим id только что добавленного ресторана с помощью специального MySQL-запроса
Statement stmt2 = conn.createStatement();
ResultSet rslt2 = stmt2.executeQuery("SELECT LAST_INSERT_ID()");
rslt2.next();
result = rslt2.getInt(1);
conn.close();
} catch (Exception e) {
System.err.println(e.getClass().getName() + ": " + e.getMessage());
// e.printStackTrace();
// System.exit(0);
}
return result;
}
public static void addUserTaskToStatement(
PreparedStatement preparedStatement, UserJournalBean userJournalBean, Long idGroupTask)
throws SQLException {
int cursor = 1;
preparedStatement.setLong(cursor++, userJournalBean.getUser().getId());
preparedStatement.setLong(cursor++, idGroupTask);
if (userJournalBean.getNumberOfAttempts() != null) {
preparedStatement.setInt(cursor++, userJournalBean.getNumberOfAttempts());
} else {
preparedStatement.setObject(cursor++, null);
}
preparedStatement.setString(cursor++, userJournalBean.getComment());
preparedStatement.setByte(cursor++, (byte) 1);
List<Double> marks = userJournalBean.getMarks();
int counter = 0;
if (marks != null) {
for (Double mark : marks) {
if (mark == null) {
preparedStatement.setObject(cursor++, null);
} else {
preparedStatement.setDouble(cursor++, mark);
}
++counter;
}
}
while (counter < 3) {
preparedStatement.setObject(cursor++, null);
++counter;
}
preparedStatement.setTimestamp(
cursor++, CalendarHelper.getStamp(userJournalBean.getMarkDate()));
}
/**
* 设置预编译参数
*
* @param ps 预编译
* @param index 序号
* @param t vo模型
* @param f 字段
* @throws IllegalArgumentException
* @throws SQLException
* @throws IllegalAccessException
*/
private void setParamter(PreparedStatement ps, int index, T t, Field f)
throws IllegalArgumentException, SQLException, IllegalAccessException {
if (!f.isAccessible()) {
f.setAccessible(true);
}
if (isBoolean(f)) {
ps.setBoolean(index, f.getBoolean(t));
} else if (isInt(f)) {
ps.setInt(index, f.getInt(t));
} else if (isLong(f)) {
ps.setLong(index, f.getLong(t));
} else if (isString(f)) {
ps.setString(index, (String) f.get(t));
} else if (isDate(f)) {
Object o = f.get(t);
if (o == null) {
ps.setDate(index, null);
} else {
ps.setTimestamp(index, new java.sql.Timestamp(((Date) o).getTime()));
}
} else if (isByte(f)) {
ps.setByte(index, f.getByte(t));
} else if (isChar(f)) {
ps.setInt(index, f.getChar(t));
} else if (isDouble(f)) {
ps.setDouble(index, f.getDouble(t));
} else if (isFloat(f)) {
ps.setFloat(index, f.getFloat(t));
} else {
ps.setObject(index, f.get(t));
}
}
/** 同一个PreparedStatement先查询后更新 */
@Test
public void testExecute2() throws SQLException {
TGroupConnection conn = null;
PreparedStatement stat = null;
try {
conn = tgds.getConnection();
stat = conn.prepareStatement("select * from xxx where id=?");
stat.setByte(1, (byte) 5);
boolean res = stat.execute();
Assert.assertEquals(res, true);
res = stat.execute("update t set name = 'newName'");
Assert.assertEquals(res, false);
MockDataSource.showTrace();
} finally {
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
}
if (stat != null) {
try {
stat.close();
} catch (SQLException e) {
}
}
}
}
}
public void testBigDecimal() throws Throwable {
String crtab = "create table #testBigDecimal (a decimal(28,10) NULL)";
dropTable("#testBigDecimal");
Statement stmt = con.createStatement();
stmt.executeUpdate(crtab);
stmt.close();
PreparedStatement pstmt = con.prepareStatement("insert into #testBigDecimal values (?)");
pstmt.setObject(1, new BigDecimal("10.200"));
pstmt.execute();
// FIXME With Sybase this should probably throw a DataTruncation, not just a plain SQLException
pstmt.setObject(1, new BigDecimal(10.200));
pstmt.execute();
pstmt.setObject(1, null);
pstmt.execute();
pstmt.setObject(1, new Integer(20));
pstmt.execute();
pstmt.setObject(1, new Double(2.10));
pstmt.execute();
pstmt.setObject(1, new BigDecimal(-10.200));
pstmt.execute();
pstmt.setObject(1, new Long(200));
pstmt.execute();
pstmt.setByte(1, (byte) 1);
pstmt.execute();
pstmt.setInt(1, 200);
pstmt.execute();
pstmt.setLong(1, 200L);
pstmt.execute();
pstmt.setFloat(1, (float) 1.1);
pstmt.execute();
pstmt.setDouble(1, 1.1);
pstmt.execute();
pstmt.close();
}
public static void setPreparedParameters(PreparedStatement pst, List<Object> parameters)
throws SQLException {
if (parameters != null && parameters.size() > 0) {
int parameterIndex = 1;
for (Object p : parameters) {
if (p == null) {
pst.setObject(parameterIndex, null);
} else if (p instanceof Integer || p.getClass() == int.class) {
pst.setInt(parameterIndex, (Integer) p);
} else if (p instanceof Long || p.getClass() == long.class) {
pst.setLong(parameterIndex, (Long) p);
} else if (p instanceof Float || p.getClass() == float.class) {
pst.setFloat(parameterIndex, (Float) p);
} else if (p instanceof Double || p.getClass() == double.class) {
pst.setDouble(parameterIndex, (Double) p);
} else if (p instanceof Byte || p.getClass() == byte.class) {
pst.setByte(parameterIndex, (Byte) p);
} else if (p instanceof Boolean || p.getClass() == boolean.class) {
pst.setBoolean(parameterIndex, (Boolean) p);
} else if (p instanceof Date) {
pst.setTimestamp(parameterIndex, new java.sql.Timestamp(((Date) p).getTime()));
} else if (p.getClass().isArray()) {
pst.setBytes(parameterIndex, (byte[]) p);
} else {
pst.setObject(parameterIndex, p);
}
parameterIndex++;
}
}
}
@Test
public void testExecuteQuery() throws SQLException {
TGroupConnection conn = null;
PreparedStatement stat = null;
try {
conn = tgds.getConnection();
stat = conn.prepareStatement("select * from xxx where id=?");
stat.setByte(1, (byte) 5);
MockDataSource.addPreData("id:1,name:2");
ResultSet result = stat.executeQuery();
Assert.assertEquals(result.next(), true);
Assert.assertEquals(result.getLong(1), 1L);
Assert.assertEquals(result.getLong(2), 2L);
MockDataSource.showTrace();
} finally {
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
}
if (stat != null) {
try {
stat.close();
} catch (SQLException e) {
}
}
}
}
}
@Override
public void writeAt(long index, LogEntry logEntry) {
if (index >= this.nextIndex.get() || index < this.startIndex.get()) {
throw new IllegalArgumentException("index out of range");
}
try {
PreparedStatement ps = this.connection.prepareStatement(UPDATE_ENTRY_SQL);
ps.setLong(1, logEntry.getTerm());
ps.setByte(2, logEntry.getValueType().toByte());
ps.setBytes(3, logEntry.getValue());
ps.setLong(4, index);
ps.execute();
ps = this.connection.prepareStatement(TRIM_TABLE_SQL);
ps.setLong(1, index);
ps.execute();
ps = this.connection.prepareStatement(UPDATE_SEQUENCE_SQL);
ps.setLong(1, index + 1);
ps.execute();
this.connection.commit();
this.nextIndex.set(index + 1);
this.lastEntry = logEntry;
} catch (Throwable error) {
this.logger.error("failed to write an entry at a specific index", error);
throw new RuntimeException("log store error", error);
}
}
public void setByte(int idx, byte b) throws SQLException {
try {
addMementoEntry("setByte", new Class[] {Byte.TYPE}, idx, b);
wrapped.setByte(idx, b);
} catch (SQLException e) {
throw new UcanaccessSQLException(e);
}
}
public void setByte(int parameterIndex, byte x) throws SQLException {
checkOpen();
try {
_stmt.setByte(parameterIndex, x);
} catch (SQLException e) {
handleException(e);
}
}
public void setByte(int parameterIndex, byte x) throws SQLException {
Profiler profiler = _profilerPoint.start();
try {
_preparedStatement.setByte(parameterIndex, x);
} finally {
profiler.finish();
}
}
public void run(Connection conn, long s_id, byte ai_type) throws SQLException {
if (LOG.isTraceEnabled()) LOG.trace(String.format("GetAccessData %d %d", s_id, ai_type));
PreparedStatement stmt = this.getPreparedStatement(conn, getAccessInfo);
stmt.setLong(1, s_id);
stmt.setByte(2, ai_type);
ResultSet results = stmt.executeQuery();
assert (results != null);
results.close();
}
@Override
public void setNonNullParameter(
PreparedStatement ps, int i, ScheduleType parameter, JdbcType jdbcType) throws SQLException {
byte typeId = (byte) parameter.getId();
if (jdbcType == null) {
ps.setByte(i, typeId);
} else {
ps.setObject(i, typeId, jdbcType.TYPE_CODE);
}
}
public void setByte(int parameterIndex, byte x) throws SQLException {
String methodCall = "setByte(" + parameterIndex + ", " + x + ")";
argTraceSet(parameterIndex, "(byte)", new Byte(x));
try {
realPreparedStatement.setByte(parameterIndex, x);
} catch (SQLException s) {
reportException(methodCall, s);
throw s;
}
reportReturn(methodCall);
}
private void updateUnsignedTable(Connection conn, double data) throws Exception {
PreparedStatement stmt =
conn.prepareStatement("UPSERT INTO " + unsignedTableName + " VALUES (?, ?, ?, ?, ?, ?, ?)");
stmt.setString(1, KEY);
Double d = Double.valueOf(data);
stmt.setDouble(2, d.doubleValue());
stmt.setFloat(3, d.floatValue());
stmt.setInt(4, d.intValue());
stmt.setLong(5, d.longValue());
stmt.setShort(6, d.shortValue());
stmt.setByte(7, d.byteValue());
stmt.executeUpdate();
conn.commit();
}
@Override
public void append(LogEntry logEntry) {
try {
PreparedStatement ps = this.connection.prepareStatement(INSERT_ENTRY_SQL);
ps.setLong(1, logEntry.getTerm());
ps.setByte(2, logEntry.getValueType().toByte());
ps.setBytes(3, logEntry.getValue());
ps.execute();
this.connection.commit();
this.nextIndex.incrementAndGet();
this.lastEntry = logEntry;
} catch (Throwable error) {
this.logger.error("failed to insert a new entry", error);
throw new RuntimeException("log store error", error);
}
}
private void save(Connection con) throws SQLException {
try (PreparedStatement pstmt =
con.prepareStatement(
"INSERT INTO asset (id, account_id, name, "
+ "description, quantity, decimals, height) VALUES (?, ?, ?, ?, ?, ?, ?)")) {
int i = 0;
pstmt.setLong(++i, this.getId());
pstmt.setLong(++i, this.getAccountId());
pstmt.setString(++i, this.getName());
pstmt.setString(++i, this.getDescription());
pstmt.setLong(++i, this.getQuantityQNT());
pstmt.setByte(++i, this.getDecimals());
pstmt.setInt(++i, Nxt.getBlockchain().getHeight());
pstmt.executeUpdate();
}
}
public static PreparedStatement resolvePreparedStatement(PreparedStatement stm, Object[] set)
throws SQLException {
for (int i = 0; i < set.length; i++) {
if (set[i] instanceof String) {
stm.setString(i + 1, (String) set[i]);
} else if (set[i] instanceof Date) {
stm.setLong(i + 1, DB.dateToUnix((Date) set[i]));
} else if (set[i] instanceof Integer) {
stm.setInt(i + 1, ((Integer) set[i]).intValue());
} else if (set[i] instanceof Byte) {
stm.setByte(i + 1, ((Byte) set[i]).byteValue());
} else {
throw new SQLException("Tipo invalido");
}
}
return stm;
}
@Ignore
@Test
public void getObject() throws SQLException {
stat.executeUpdate(
"create table testobj (" + "c1 integer, c2 float, c3, c4 varchar, c5 bit, c6, c7);");
PreparedStatement prep = conn.prepareStatement("insert into testobj values (?,?,?,?,?,?,?);");
prep.setInt(1, Integer.MAX_VALUE);
prep.setFloat(2, Float.MAX_VALUE);
prep.setDouble(3, Double.MAX_VALUE);
prep.setLong(4, Long.MAX_VALUE);
prep.setBoolean(5, false);
prep.setByte(6, (byte) 7);
prep.setBytes(7, b1);
prep.executeUpdate();
ResultSet rs = stat.executeQuery("select c1,c2,c3,c4,c5,c6,c7 from testobj;");
assertTrue(rs.next());
assertEquals(Integer.MAX_VALUE, rs.getInt(1));
assertEquals(Integer.MAX_VALUE, (int) rs.getLong(1));
assertEquals(Float.MAX_VALUE, rs.getFloat(2));
assertEquals(Double.MAX_VALUE, rs.getDouble(3));
assertEquals(Long.MAX_VALUE, rs.getLong(4));
assertFalse(rs.getBoolean(5));
assertEquals((byte) 7, rs.getByte(6));
assertArrayEq(rs.getBytes(7), b1);
assertNotNull(rs.getObject(1));
assertNotNull(rs.getObject(2));
assertNotNull(rs.getObject(3));
assertNotNull(rs.getObject(4));
assertNotNull(rs.getObject(5));
assertNotNull(rs.getObject(6));
assertNotNull(rs.getObject(7));
assertTrue(rs.getObject(1) instanceof Integer);
assertTrue(rs.getObject(2) instanceof Double);
assertTrue(rs.getObject(3) instanceof Double);
assertTrue(rs.getObject(4) instanceof String);
assertTrue(rs.getObject(5) instanceof Integer);
assertTrue(rs.getObject(6) instanceof Integer);
assertTrue(rs.getObject(7) instanceof byte[]);
rs.close();
}
protected void setParameter(PreparedStatement statement, Object object, int i)
throws SQLException {
if (object == null) {
statement.setNull(i, java.sql.Types.VARCHAR);
} else if (object instanceof NullValue.Binary) {
statement.setNull(i, java.sql.Types.BINARY);
} else if (object instanceof NullValue.Decimal) {
statement.setNull(i, java.sql.Types.DECIMAL);
} else if (object instanceof NullValue.Double) {
statement.setNull(i, java.sql.Types.DOUBLE);
} else if (object instanceof NullValue.Integer) {
statement.setNull(i, java.sql.Types.INTEGER);
} else if (object instanceof NullValue.String) {
statement.setNull(i, java.sql.Types.VARCHAR);
} else if (object instanceof NullValue.Timestamp) {
statement.setNull(i, java.sql.Types.TIMESTAMP);
} else if (object instanceof String) {
statement.setString(i, (String) object);
} else if (object instanceof Byte) {
statement.setByte(i, (Byte) object);
} else if (object instanceof Short) {
statement.setShort(i, (Short) object);
} else if (object instanceof Integer) {
statement.setInt(i, (Integer) object);
} else if (object instanceof Long) {
statement.setLong(i, (Long) object);
} else if (object instanceof BigDecimal) {
statement.setBigDecimal(i, (BigDecimal) object);
} else if (object instanceof Date) {
statement.setDate(i, (Date) object);
} else if (object instanceof Double) {
statement.setDouble(i, (Double) object);
} else if (object instanceof Float) {
statement.setFloat(i, (Float) object);
} else if (object instanceof Boolean) {
statement.setBoolean(i, (Boolean) object);
} else if (object instanceof Timestamp) {
statement.setTimestamp(i, (Timestamp) object);
} else if (object instanceof java.util.Date) {
statement.setTimestamp(i, new Timestamp(((java.util.Date) object).getTime()));
} else {
statement.setObject(i, object);
}
}
@Override
public void applyLogPack(long index, byte[] logPack) {
if (index < this.startIndex.get()) {
throw new IllegalArgumentException("logIndex out of range");
}
try {
ByteArrayInputStream memoryStream = new ByteArrayInputStream(logPack);
GZIPInputStream gzipStream = new GZIPInputStream(memoryStream);
byte[] sizeBuffer = new byte[Integer.BYTES];
PreparedStatement ps = this.connection.prepareStatement(TRIM_TABLE_SQL);
ps.setLong(1, index - 1);
ps.execute();
ps = this.connection.prepareStatement(UPDATE_SEQUENCE_SQL);
ps.setLong(1, index);
ps.execute();
while (this.read(gzipStream, sizeBuffer)) {
int size = BinaryUtils.bytesToInt(sizeBuffer, 0);
byte[] entryData = new byte[size - Integer.BYTES];
if (!this.read(gzipStream, entryData)) {
throw new RuntimeException("bad log pack, no able to read the log entry data");
}
ByteBuffer buffer = ByteBuffer.wrap(entryData);
long term = buffer.getLong();
byte valueType = buffer.get();
byte[] value = new byte[size - Long.BYTES - 1 - Integer.BYTES];
buffer.get(value);
ps = this.connection.prepareStatement(INSERT_ENTRY_SQL);
ps.setLong(1, term);
ps.setByte(2, valueType);
ps.setBytes(3, value);
ps.execute();
this.lastEntry = new LogEntry(term, value, LogValueType.fromByte(valueType));
}
this.connection.commit();
gzipStream.close();
} catch (Throwable error) {
this.logger.error("failed to apply log pack", error);
throw new RuntimeException("log store error", error);
}
}
private void addValue(int index, Value value) throws SQLException {
pactType type;
try {
type = pactType.valueOf(value.getClass().getSimpleName());
} catch (IllegalArgumentException iae) {
throw new IllegalArgumentException("PactType not supported:\t", iae);
}
switch (type) {
case BooleanValue:
upload.setBoolean(index, ((BooleanValue) value).getValue());
break;
case ByteValue:
upload.setByte(index, ((ByteValue) value).getValue());
break;
case CharValue:
upload.setString(index, String.valueOf(((CharValue) value).getValue()));
break;
case DoubleValue:
upload.setDouble(index, ((DoubleValue) value).getValue());
break;
case FloatValue:
upload.setFloat(index, ((FloatValue) value).getValue());
break;
case IntValue:
upload.setInt(index, ((IntValue) value).getValue());
break;
case LongValue:
upload.setLong(index, ((LongValue) value).getValue());
break;
case ShortValue:
upload.setShort(index, ((ShortValue) value).getValue());
break;
case StringValue:
upload.setString(index, ((StringValue) value).getValue());
break;
}
}
public void setParameter(PreparedStatement stmt, Object[] args) throws SQLException {
stmt.setByte((Integer) args[0], (Byte) args[1]);
}
public static void setValue(PreparedStatement ps, int fieldIndex, Object value, DataType dataType)
throws SQLException {
if (value == null) {
ps.setNull(fieldIndex, DataType.getSqlType(dataType));
return;
}
Class<?> valueClass = value.getClass();
switch (dataType) {
case FLOAT:
if (float.class.isAssignableFrom(valueClass) || Float.class.isAssignableFrom(valueClass)) {
ps.setFloat(fieldIndex, (Float) value);
return;
}
break;
case DOUBLE:
if (double.class.isAssignableFrom(valueClass)
|| Double.class.isAssignableFrom(valueClass)) {
ps.setDouble(fieldIndex, (Double) value);
return;
}
break;
case INTEGER:
if (int.class.isAssignableFrom(valueClass) || Integer.class.isAssignableFrom(valueClass)) {
ps.setInt(fieldIndex, (Integer) value);
return;
}
break;
case BYTE:
if (byte.class.isAssignableFrom(valueClass) || Byte.class.isAssignableFrom(valueClass)) {
ps.setByte(fieldIndex, (Byte) value);
return;
}
break;
case LONG:
if (long.class.isAssignableFrom(valueClass) || Long.class.isAssignableFrom(valueClass)) {
ps.setLong(fieldIndex, (Long) value);
return;
}
break;
case STRING:
ps.setString(fieldIndex, (String) value);
return;
case ENUMERATION:
if (valueClass.isEnum()) {
Enum<?> enumValue = (Enum<?>) value;
ps.setInt(fieldIndex, enumValue.ordinal());
return;
}
break;
case DATE:
Date d = (Date) value;
Integer seconds = (int) (d.getTime() / 1000);
ps.setInt(fieldIndex, seconds);
return;
case BOOLEAN:
Boolean flag = (Boolean) value;
int intValue = flag ? 1 : 0;
ps.setInt(fieldIndex, intValue);
return;
}
ps.setObject(fieldIndex, value);
}
@Override
public DbIterator<TransactionImpl> getTransactions(
Account account,
int numberOfConfirmations,
byte type,
byte subtype,
int blockTimestamp,
boolean withMessage,
boolean phasedOnly,
boolean nonPhasedOnly,
int from,
int to) {
if (phasedOnly && nonPhasedOnly) {
throw new IllegalArgumentException(
"At least one of phasedOnly or nonPhasedOnly must be false");
}
int height =
numberOfConfirmations > 0 ? getHeight() - numberOfConfirmations : Integer.MAX_VALUE;
if (height < 0) {
throw new IllegalArgumentException(
"Number of confirmations required "
+ numberOfConfirmations
+ " exceeds current blockchain height "
+ getHeight());
}
Connection con = null;
try {
StringBuilder buf = new StringBuilder();
buf.append("SELECT * FROM transaction WHERE recipient_id = ? AND sender_id <> ? ");
if (blockTimestamp > 0) {
buf.append("AND block_timestamp >= ? ");
}
if (type >= 0) {
buf.append("AND type = ? ");
if (subtype >= 0) {
buf.append("AND subtype = ? ");
}
}
if (height < Integer.MAX_VALUE) {
buf.append("AND height <= ? ");
}
if (withMessage) {
buf.append("AND (has_message = TRUE OR has_encrypted_message = TRUE ");
buf.append(
"OR ((has_prunable_message = TRUE OR has_prunable_encrypted_message = TRUE) AND timestamp > ?)) ");
}
if (phasedOnly) {
buf.append("AND phased = TRUE ");
} else if (nonPhasedOnly) {
buf.append("AND phased = FALSE ");
}
buf.append("UNION ALL SELECT * FROM transaction WHERE sender_id = ? ");
if (blockTimestamp > 0) {
buf.append("AND block_timestamp >= ? ");
}
if (type >= 0) {
buf.append("AND type = ? ");
if (subtype >= 0) {
buf.append("AND subtype = ? ");
}
}
if (height < Integer.MAX_VALUE) {
buf.append("AND height <= ? ");
}
if (withMessage) {
buf.append(
"AND (has_message = TRUE OR has_encrypted_message = TRUE OR has_encrypttoself_message = TRUE ");
buf.append(
"OR ((has_prunable_message = TRUE OR has_prunable_encrypted_message = TRUE) AND timestamp > ?)) ");
}
if (phasedOnly) {
buf.append("AND phased = TRUE ");
} else if (nonPhasedOnly) {
buf.append("AND phased = FALSE ");
}
buf.append("ORDER BY block_timestamp DESC, transaction_index DESC");
buf.append(DbUtils.limitsClause(from, to));
con = Db.db.getConnection();
PreparedStatement pstmt;
int i = 0;
pstmt = con.prepareStatement(buf.toString());
pstmt.setLong(++i, account.getId());
pstmt.setLong(++i, account.getId());
if (blockTimestamp > 0) {
pstmt.setInt(++i, blockTimestamp);
}
if (type >= 0) {
pstmt.setByte(++i, type);
if (subtype >= 0) {
pstmt.setByte(++i, subtype);
}
}
if (height < Integer.MAX_VALUE) {
pstmt.setInt(++i, height);
}
int prunableExpiration =
Constants.INCLUDE_EXPIRED_PRUNABLE
? 0
: Nxt.getEpochTime() - Constants.MAX_PRUNABLE_LIFETIME;
if (withMessage) {
pstmt.setInt(++i, prunableExpiration);
}
pstmt.setLong(++i, account.getId());
if (blockTimestamp > 0) {
pstmt.setInt(++i, blockTimestamp);
}
if (type >= 0) {
pstmt.setByte(++i, type);
if (subtype >= 0) {
pstmt.setByte(++i, subtype);
}
}
if (height < Integer.MAX_VALUE) {
pstmt.setInt(++i, height);
}
if (withMessage) {
pstmt.setInt(++i, prunableExpiration);
}
DbUtils.setLimits(++i, pstmt, from, to);
return getTransactions(con, pstmt);
} catch (SQLException e) {
DbUtils.close(con);
throw new RuntimeException(e.toString(), e);
}
}
public synchronized void insertData(String name, long modified, int type, DLNAMediaInfo media) {
Connection conn = null;
PreparedStatement ps = null;
try {
conn = getConnection();
ps =
conn.prepareStatement(
"INSERT INTO FILES(FILENAME, MODIFIED, TYPE, DURATION, BITRATE, WIDTH, HEIGHT, SIZE, CODECV, "
+ "FRAMERATE, ASPECT, ASPECTRATIOCONTAINER, ASPECTRATIOVIDEOTRACK, REFRAMES, AVCLEVEL, BITSPERPIXEL, "
+ "THUMB, CONTAINER, MODEL, EXPOSURE, ORIENTATION, ISO, MUXINGMODE, FRAMERATEMODE, STEREOSCOPY, "
+ "MATRIXCOEFFICIENTS, TITLECONTAINER, TITLEVIDEOTRACK, VIDEOTRACKCOUNT, IMAGECOUNT, BITDEPTH) VALUES "
+ "(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
ps.setString(1, name);
ps.setTimestamp(2, new Timestamp(modified));
ps.setInt(3, type);
if (media != null) {
if (media.getDuration() != null) {
ps.setDouble(4, media.getDurationInSeconds());
} else {
ps.setNull(4, Types.DOUBLE);
}
int databaseBitrate = 0;
if (type != Format.IMAGE) {
databaseBitrate = media.getBitrate();
if (databaseBitrate == 0) {
LOGGER.debug("Could not parse the bitrate from: " + name);
}
}
ps.setInt(5, databaseBitrate);
ps.setInt(6, media.getWidth());
ps.setInt(7, media.getHeight());
ps.setLong(8, media.getSize());
ps.setString(9, left(media.getCodecV(), SIZE_CODECV));
ps.setString(10, left(media.getFrameRate(), SIZE_FRAMERATE));
ps.setString(11, left(media.getAspectRatioDvdIso(), SIZE_ASPECTRATIO_DVDISO));
ps.setString(12, left(media.getAspectRatioContainer(), SIZE_ASPECTRATIO_CONTAINER));
ps.setString(13, left(media.getAspectRatioVideoTrack(), SIZE_ASPECTRATIO_VIDEOTRACK));
ps.setByte(14, media.getReferenceFrameCount());
ps.setString(15, left(media.getAvcLevel(), SIZE_AVC_LEVEL));
ps.setInt(16, media.getBitsPerPixel());
ps.setBytes(17, media.getThumb());
ps.setString(18, left(media.getContainer(), SIZE_CONTAINER));
if (media.getExtras() != null) {
ps.setString(19, left(media.getExtrasAsString(), SIZE_MODEL));
} else {
ps.setString(19, left(media.getModel(), SIZE_MODEL));
}
ps.setInt(20, media.getExposure());
ps.setInt(21, media.getOrientation());
ps.setInt(22, media.getIso());
ps.setString(23, left(media.getMuxingModeAudio(), SIZE_MUXINGMODE));
ps.setString(24, left(media.getFrameRateMode(), SIZE_FRAMERATE_MODE));
ps.setString(25, left(media.getStereoscopy(), SIZE_STEREOSCOPY));
ps.setString(26, left(media.getMatrixCoefficients(), SIZE_MATRIX_COEFFICIENTS));
ps.setString(27, left(media.getFileTitleFromMetadata(), SIZE_TITLE));
ps.setString(28, left(media.getVideoTrackTitleFromMetadata(), SIZE_TITLE));
ps.setInt(29, media.getVideoTrackCount());
ps.setInt(30, media.getImageCount());
ps.setInt(31, media.getVideoBitDepth());
} else {
ps.setString(4, null);
ps.setInt(5, 0);
ps.setInt(6, 0);
ps.setInt(7, 0);
ps.setLong(8, 0);
ps.setString(9, null);
ps.setString(10, null);
ps.setString(11, null);
ps.setString(12, null);
ps.setString(13, null);
ps.setByte(14, (byte) -1);
ps.setString(15, null);
ps.setInt(16, 0);
ps.setBytes(17, null);
ps.setString(18, null);
ps.setString(19, null);
ps.setInt(20, 0);
ps.setInt(21, 0);
ps.setInt(22, 0);
ps.setString(23, null);
ps.setString(24, null);
ps.setString(25, null);
ps.setString(26, null);
ps.setString(27, null);
ps.setString(28, null);
ps.setInt(29, 0);
ps.setInt(30, 0);
ps.setInt(31, 0);
}
ps.executeUpdate();
int id;
try (ResultSet rs = ps.getGeneratedKeys()) {
id = -1;
while (rs.next()) {
id = rs.getInt(1);
}
}
if (media != null && id > -1) {
PreparedStatement insert = null;
if (media.getAudioTracksList().size() > 0) {
insert =
conn.prepareStatement(
"INSERT INTO AUDIOTRACKS VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
for (DLNAMediaAudio audio : media.getAudioTracksList()) {
insert.clearParameters();
insert.setInt(1, id);
insert.setInt(2, audio.getId());
insert.setString(3, left(audio.getLang(), SIZE_LANG));
insert.setString(4, left(audio.getAudioTrackTitleFromMetadata(), SIZE_TITLE));
insert.setInt(5, audio.getAudioProperties().getNumberOfChannels());
insert.setString(6, left(audio.getSampleFrequency(), SIZE_SAMPLEFREQ));
insert.setString(7, left(audio.getCodecA(), SIZE_CODECA));
insert.setInt(8, audio.getBitsperSample());
insert.setString(9, left(trimToEmpty(audio.getAlbum()), SIZE_ALBUM));
insert.setString(10, left(trimToEmpty(audio.getArtist()), SIZE_ARTIST));
insert.setString(11, left(trimToEmpty(audio.getSongname()), SIZE_SONGNAME));
insert.setString(12, left(trimToEmpty(audio.getGenre()), SIZE_GENRE));
insert.setInt(13, audio.getYear());
insert.setInt(14, audio.getTrack());
insert.setInt(15, audio.getAudioProperties().getAudioDelay());
insert.setString(16, left(trimToEmpty(audio.getMuxingModeAudio()), SIZE_MUXINGMODE));
insert.setInt(17, audio.getBitRate());
try {
insert.executeUpdate();
} catch (SQLException e) {
if (e.getErrorCode() == 23505) {
LOGGER.debug(
"A duplicate key error occurred while trying to store the following file's audio information in the database: "
+ name);
} else {
LOGGER.debug(
"An error occurred while trying to store the following file's audio information in the database: "
+ name);
}
LOGGER.debug("The error given by jdbc was: " + e);
}
}
}
if (media.getSubtitleTracksList().size() > 0) {
insert = conn.prepareStatement("INSERT INTO SUBTRACKS VALUES (?, ?, ?, ?, ?)");
for (DLNAMediaSubtitle sub : media.getSubtitleTracksList()) {
if (sub.getExternalFile() == null) { // no save of external subtitles
insert.clearParameters();
insert.setInt(1, id);
insert.setInt(2, sub.getId());
insert.setString(3, left(sub.getLang(), SIZE_LANG));
insert.setString(4, left(sub.getSubtitlesTrackTitleFromMetadata(), SIZE_TITLE));
insert.setInt(5, sub.getType().getStableIndex());
try {
insert.executeUpdate();
} catch (SQLException e) {
if (e.getErrorCode() == 23505) {
LOGGER.debug(
"A duplicate key error occurred while trying to store the following file's subtitle information in the database: "
+ name);
} else {
LOGGER.debug(
"An error occurred while trying to store the following file's subtitle information in the database: "
+ name);
}
LOGGER.debug("The error given by jdbc was: " + e);
}
}
}
}
close(insert);
}
} catch (SQLException se) {
if (se.getErrorCode() == 23505) {
LOGGER.debug(
"Duplicate key while inserting this entry: "
+ name
+ " into the database: "
+ se.getMessage());
} else {
LOGGER.error(null, se);
}
} finally {
close(ps);
close(conn);
}
}
// this method is a work in progress. additional functionality will be added as needed.
private int setPreparedStatementParameter(Object object, int index) {
if ((preparedStatement_ == null) || (preparedStatementParameters_ == null)) {
logger.warn(
"Can't set preparedStatementParameters - preparedStatementParameters or preparedStatement is null");
return -1;
}
try {
if (object == null) {
preparedStatement_.setObject(index++, null);
} else if (object instanceof BigDecimal) {
preparedStatement_.setBigDecimal(index++, (BigDecimal) object);
} else if (object instanceof Blob) {
preparedStatement_.setBlob(index++, (Blob) object);
} else if (object instanceof Boolean) {
preparedStatement_.setBoolean(index++, (Boolean) object);
} else if (object instanceof Byte) {
preparedStatement_.setByte(index++, (Byte) object);
} else if (object instanceof byte[]) {
preparedStatement_.setBytes(index++, (byte[]) object);
} else if (object instanceof Clob) {
preparedStatement_.setClob(index++, (Clob) object);
} else if (object instanceof Double) {
preparedStatement_.setDouble(index++, (Double) object);
} else if (object instanceof Float) {
preparedStatement_.setFloat(index++, (Float) object);
} else if (object instanceof Integer) {
preparedStatement_.setInt(index++, (Integer) object);
} else if (object instanceof List) {
for (Object listObject : (List) object) {
setPreparedStatementParameter(listObject, index++);
}
} else if (object instanceof Long) {
preparedStatement_.setLong(index++, (Long) object);
} else if (object instanceof Short) {
preparedStatement_.setShort(index++, (Short) object);
} else if (object instanceof String) {
preparedStatement_.setString(index++, (String) object);
} else if (object instanceof java.sql.Timestamp) {
preparedStatement_.setTimestamp(index++, (java.sql.Timestamp) object);
} else if (object instanceof java.sql.Date) {
preparedStatement_.setDate(index++, (java.sql.Date) object);
} else if (object instanceof java.util.Date) {
java.util.Date tempDate = (java.util.Date) object;
java.sql.Date dateSql = new java.sql.Date(tempDate.getTime());
preparedStatement_.setDate(index++, dateSql);
} else {
if (object instanceof Object) {
} else {
logger.warn(
"Setting PreparedStatement parameter to 'object' type when object is not an object type");
}
preparedStatement_.setObject(index++, object);
}
return index;
} catch (Exception e) {
logger.error(e.toString() + System.lineSeparator() + StackTrace.getStringFromStackTrace(e));
return -1;
}
}