com.almworks.sqlite4java.SQLiteStatement Java Examples

The following examples show how to use com.almworks.sqlite4java.SQLiteStatement. You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example #1
Source File: SQLiteWrapperUtils.java    From sync-android with Apache License 2.0 6 votes vote down vote up
public static SQLiteCursor buildSQLiteCursor(SQLiteConnection conn, String sql, Object[] bindArgs)
        throws SQLiteException {
    SQLiteStatement stmt = null;
    try {
        stmt = bindArguments(conn.prepare(sql), bindArgs);
        List<String> columnNames = null;
        List<Tuple> resultSet = new ArrayList<Tuple>();
        while (!stmt.hasStepped() || stmt.hasRow()) {
            if (!stmt.step()) {
                break;
            }
            if (columnNames == null) {
                columnNames = getColumnNames(stmt);
            }

            Tuple t = getDataRow(stmt);
            logger.finest("Tuple: "+ t.toString());
            resultSet.add(t);
        }
        return new SQLiteCursor(columnNames, resultSet);
    } finally {
        SQLiteWrapperUtils.disposeQuietly(stmt);
    }
}
 
Example #2
Source File: Extractors.java    From arcusplatform with Apache License 2.0 6 votes vote down vote up
@Override
public ZWNode extract(SQLiteConnection conn, SQLiteStatement stmt) throws Exception {
   int node_id = stmt.columnInt(NODE_ID_COL);
   ZWNodeBuilder builder = ZWNode.builder(node_id);
   ZWNode node = builder.setBasicType(stmt.columnInt(NODE_BASIC_TYPE_COL))
         .setGenericType(stmt.columnInt(NODE_GENERIC_TYPE_COL))
         .setSpecificType(stmt.columnInt(NODE_SPECIFIC_TYPE_COL))
         .setManufacturerId(stmt.columnInt(NODE_MANUFACTURER_ID_COL))
         .setProductTypeId(stmt.columnInt(NODE_PRODUCT_TYPE_COL))
         .setProductId(stmt.columnInt(NODE_PRODUCT_ID))
         .setHomeId(ZWServices.INSTANCE.getNetwork().getHomeId())
         .addCmdClasses(ByteUtils.string2bytes(stmt.columnString(NODE_CMD_CLASSES)))
         .setOnline(stmt.columnInt(NODE_ONLINE))
         .setOfflineTimeout(stmt.columnInt(NODE_OFFLINE_TIMEOUT))
         .build();
   return node;
}
 
Example #3
Source File: DbUtils.java    From arcusplatform with Apache License 2.0 6 votes vote down vote up
public static void bindOrString(SQLiteStatement stmt, @Nullable Object arg, int idx) throws Exception {
   Class<?> type = arg == null ? null : arg.getClass();

   boolean bound = doBind(type, stmt, arg, idx);
   if (bound) {
      return;
   }

   if (arg == null || type == null) {
      stmt.bindNull(idx);
   } else if (type.isArray()) {
      stmt.bind(idx, Arrays.toString((Object[]) arg));
   } else {
      stmt.bind(idx, arg.toString());
   }
}
 
Example #4
Source File: Binders.java    From arcusplatform with Apache License 2.0 6 votes vote down vote up
@Override
public void bind(SQLiteConnection conn, SQLiteStatement stmt, ZWNode node) throws Exception {
   int idx = 1;
   stmt.bind(idx++, node.getNodeId());
   stmt.bind(idx++, node.getBasicDeviceType());
   stmt.bind(idx++, node.getGenericDeviceType());
   stmt.bind(idx++, node.getSpecificDeviceType());
   stmt.bind(idx++, node.getManufacturerId());
   stmt.bind(idx++, node.getProductTypeId());
   stmt.bind(idx++, node.getProductId());
   stmt.bind(idx++, ByteUtils.byteArray2SpacedString(node.getCmdClassBytes()));
   stmt.bind(idx++, node.isOnline() ? 1 : 0);
   stmt.bind(idx++, node.getOfflineTimeout());
   
   // The where clause
   stmt.bind(idx++, node.getNodeId());
}
 
Example #5
Source File: SQLite3StatementBindInvoker.java    From arcusplatform with Apache License 2.0 6 votes vote down vote up
@Nullable
public static Object bind(SQLiteStatement stmt, Class<?> type, int index) {
   try {
      if (type.equals(String.class)) {
         return stmt.columnString(index);
      } else if (type.equals(Integer.class) || type.equals(int.class) ) {
         return stmt.columnInt(index);
      } else if (type.equals(Long.class) || type.equals(long.class)) {
         return stmt.columnLong(index);
      } else if (type.equals(Byte.class) || type.equals(byte.class) ) {
         return (byte)stmt.columnInt(index);
      } else if ( type.equals(Boolean.class) || type.equals(boolean.class) ) {
         return stmt.columnInt(index) != 0;
      } else if (type.equals(Double.class) || type.equals(double.class)) {
         return stmt.columnDouble(index);
      } else if (type.equals(float.class) || type.equals(Float.class)) {
         return (float)stmt.columnDouble(index);
      } else if (type.equals(Serializable.class)) {
         return stmt.columnBlob(index);
      }
   } catch (SQLiteException e) {
      log.error("sql bind invoker could not invoke:" , e);
   }

   return null;
}
 
Example #6
Source File: DbUtils.java    From arcusplatform with Apache License 2.0 6 votes vote down vote up
@SuppressWarnings("unchecked")
public static <T> T extract(SQLiteStatement stmt, Class<T> type, int idx) throws Exception {
   if (type == String.class) {
      return (T) stmt.columnString(idx);
   } else if (type == int.class || type == Integer.class) {
      return (T) (Integer) stmt.columnInt(idx);
   } else if (type == long.class || type == Long.class) {
      return (T) (Long) stmt.columnLong(idx);
   } else if (type == double.class || type == Double.class) {
      return (T) (Double) stmt.columnDouble(idx);
   } else if (type == byte[].class) {
      return (T) stmt.columnBlob(idx);
   } else if (type == Object.class) {
      return (T) stmt.columnValue(idx);
   } else {
      throw new DbException("cannot get column type: " + type);
   }
}
 
Example #7
Source File: DbUtils.java    From arcusplatform with Apache License 2.0 6 votes vote down vote up
@Nullable
@Override
public O execute(SQLiteConnection conn) throws Exception {
   I val = value;
   DbBinder<I> bnd = binder;
   if (bnd == null || val == null) {
      conn.exec(sql);
      return results(conn);
   }

   SQLiteStatement stmt = conn.prepare(sql, true);
   try {
      bnd.bind(conn, stmt, val);
      while (stmt.step()) {
         row(conn, stmt);
      }

      return results(conn);
   } finally {
      stmt.dispose();
   }
}
 
Example #8
Source File: AttestationServer.java    From AttestationServer with MIT License 6 votes vote down vote up
@Override
public void handlePost(final HttpExchange exchange) throws IOException, SQLiteException {
    final Account account = verifySession(exchange, false, null);
    if (account == null) {
        return;
    }
    final SQLiteConnection conn = new SQLiteConnection(AttestationProtocol.ATTESTATION_DATABASE);
    try {
        open(conn, false);

        final SQLiteStatement select = conn.prepare("DELETE from Sessions where userId = ?");
        select.bind(1, account.userId);
        select.step();
        select.dispose();
    } finally {
        conn.dispose();
    }
    clearCookie(exchange);
    exchange.sendResponseHeaders(200, -1);
}
 
Example #9
Source File: AttestationServer.java    From AttestationServer with MIT License 6 votes vote down vote up
@Override
public void handlePost(final HttpExchange exchange) throws IOException, SQLiteException {
    final Account account = verifySession(exchange, false, null);
    if (account == null) {
        return;
    }
    final SQLiteConnection conn = new SQLiteConnection(AttestationProtocol.ATTESTATION_DATABASE);
    try {
        open(conn, false);

        final byte[] subscribeKey = generateRandomToken();

        final SQLiteStatement select = conn.prepare("UPDATE Accounts SET " +
                "subscribeKey = ? WHERE userId = ?");
        select.bind(1, subscribeKey);
        select.bind(2, account.userId);
        select.step();
        select.dispose();
    } finally {
        conn.dispose();
    }
    exchange.sendResponseHeaders(200, -1);
}
 
Example #10
Source File: SqliteStreamOperator.java    From attic-apex-malhar with Apache License 2.0 6 votes vote down vote up
@Override
public void processTuple(int tableNum, HashMap<String, Object> tuple)
{
  InputSchema inputSchema = inputSchemas.get(tableNum);

  SQLiteStatement insertStatement = insertStatements.get(tableNum);
  try {
    for (Map.Entry<String, Object> entry : tuple.entrySet()) {
      ColumnInfo t = inputSchema.columnInfoMap.get(entry.getKey());
      if (t != null && t.bindIndex != 0) {
        insertStatement.bind(t.bindIndex, entry.getValue().toString());
      }
    }

    insertStatement.step();
    insertStatement.reset();
  } catch (SQLiteException ex) {
    throw new RuntimeException(ex);
  }
}
 
Example #11
Source File: SQLiteWrapperUtils.java    From sync-android with Apache License 2.0 6 votes vote down vote up
/**
 * Utility method to run the query on the db and return the value in the
 * first column of the first row.
 */
public static Long longForQuery(SQLiteConnection conn, String query, Object[] bindArgs)
        throws SQLiteException {
    SQLiteStatement stmt = null;
    try {
        stmt = conn.prepare(query);
        if (bindArgs != null && bindArgs.length > 0) {
            stmt = SQLiteWrapperUtils.bindArguments(stmt, bindArgs);
        }
        if (stmt.step()) {
            return stmt.columnLong(0);
        } else {
            throw new IllegalStateException("query failed to return any result: " + query);
        }
    } finally {
        SQLiteWrapperUtils.disposeQuietly(stmt);
    }
}
 
Example #12
Source File: SQLiteWrapperUtils.java    From sync-android with Apache License 2.0 5 votes vote down vote up
/**
 * Utility method to run the query on the db and return the value in the
 * first column of the first row.
 */
public static int intForQuery(SQLiteConnection conn, String query, Object[] bindArgs) throws SQLiteException {
    SQLiteStatement stmt = null;
    try {
        stmt = bindArguments(conn.prepare(query), bindArgs);
        if (stmt.step()) {
            return stmt.columnInt(0);
        } else {
            throw new IllegalStateException("query failed to return any result: " + query);
        }
    } finally {
        SQLiteWrapperUtils.disposeQuietly(stmt);
    }
}
 
Example #13
Source File: SQLiteWrapperUtils.java    From sync-android with Apache License 2.0 5 votes vote down vote up
static void disposeQuietly(SQLiteStatement stmt) {
    if (stmt != null && !stmt.isDisposed()) {
        try {
            stmt.dispose();
        } catch (Throwable e) {}
    }
}
 
Example #14
Source File: SQLiteWrapper.java    From sync-android with Apache License 2.0 5 votes vote down vote up
@Override
public void execSQL(String sql, Object[] bindArgs) throws SQLException {
    Misc.checkNotNullOrEmpty(sql.trim(), "Input SQL");
    SQLiteStatement stmt = null;
    try {
        stmt = this.getConnection().prepare(sql);
        stmt = SQLiteWrapperUtils.bindArguments(stmt, bindArgs);
        while (stmt.step()) {
        }
    } catch (SQLiteException e) {
        throw new SQLException(e);
    } finally {
        SQLiteWrapperUtils.disposeQuietly(stmt);
    }
}
 
Example #15
Source File: SqliteStreamOperator.java    From attic-apex-malhar with Apache License 2.0 5 votes vote down vote up
@Override
public void endWindow()
{
  try {
    commitStatement.step();
    commitStatement.reset();
    if (bindings != null) {
      for (int i = 0; i < bindings.size(); i++) {
        execStatement.bind(i, bindings.get(i).toString());
      }
    }
    int columnCount = execStatement.columnCount();
    while (execStatement.step()) {
      HashMap<String, Object> resultRow = new HashMap<String, Object>();
      for (int i = 0; i < columnCount; i++) {
        resultRow.put(execStatement.getColumnName(i), execStatement.columnValue(i));
      }
      this.result.emit(resultRow);
    }
    execStatement.reset();

    for (SQLiteStatement st : deleteStatements) {
      st.step();
      st.reset();
    }
  } catch (SQLiteException ex) {
    throw new RuntimeException(ex);
  }
  bindings = null;
}
 
Example #16
Source File: AttestationServer.java    From AttestationServer with MIT License 5 votes vote down vote up
@Override
public void handlePost(final HttpExchange exchange) throws IOException, SQLiteException {
    final InputStream input = exchange.getRequestBody();

    final ByteArrayOutputStream sample = new ByteArrayOutputStream();
    final byte[] buffer = new byte[4096];
    for (int read = input.read(buffer); read != -1; read = input.read(buffer)) {
        sample.write(buffer, 0, read);

        if (sample.size() > 64 * 1024) {
            exchange.sendResponseHeaders(413, -1);
            return;
        }
    }

    final SQLiteConnection conn = new SQLiteConnection(SAMPLES_DATABASE);
    try {
        open(conn, false);
        final SQLiteStatement insert = conn.prepare("INSERT INTO Samples " +
               "(sample, time) VALUES (?, ?)");
        insert.bind(1, sample.toByteArray());
        insert.bind(2, System.currentTimeMillis());
        insert.step();
        insert.dispose();
    } finally {
        conn.dispose();
    }

    exchange.sendResponseHeaders(200, -1);
}
 
Example #17
Source File: AttestationServer.java    From AttestationServer with MIT License 5 votes vote down vote up
@Override
public void handlePost(final HttpExchange exchange) throws IOException, SQLiteException {
    final Account account = verifySession(exchange, false, null);
    if (account == null) {
        return;
    }
    final JsonObjectBuilder accountJson = Json.createObjectBuilder();
    accountJson.add("username", account.username);
    accountJson.add("verifyInterval", account.verifyInterval);
    accountJson.add("alertDelay", account.alertDelay);

    final SQLiteConnection conn = new SQLiteConnection(AttestationProtocol.ATTESTATION_DATABASE);
    try {
        open(conn, true);
        final SQLiteStatement select = conn.prepare("SELECT address FROM EmailAddresses " +
                "WHERE userId = ?");
        select.bind(1, account.userId);
        if (select.step()) {
            accountJson.add("email", select.columnString(0));
        }
        select.dispose();
    } finally {
        conn.dispose();
    }

    exchange.getResponseHeaders().set("Content-Type", "application/json");
    exchange.sendResponseHeaders(200, 0);
    try (final OutputStream output = exchange.getResponseBody();
            final JsonWriter writer = Json.createWriter(output)) {
        writer.write(accountJson.build());
    }
}
 
Example #18
Source File: AttestationServer.java    From AttestationServer with MIT License 5 votes vote down vote up
@Override
public void handlePost(final HttpExchange exchange) throws IOException, SQLiteException {
    final String requestToken;
    final String fingerprint;
    try (final JsonReader reader = Json.createReader(exchange.getRequestBody())) {
        final JsonObject object = reader.readObject();
        requestToken = object.getString("requestToken");
        fingerprint = object.getString("fingerprint");
    } catch (final ClassCastException | JsonException | NullPointerException e) {
        e.printStackTrace();
        exchange.sendResponseHeaders(400, -1);
        return;
    }

    final Account account = verifySession(exchange, false, requestToken.getBytes(StandardCharsets.UTF_8));
    if (account == null) {
        return;
    }

    final SQLiteConnection conn = new SQLiteConnection(AttestationProtocol.ATTESTATION_DATABASE);
    try {
        open(conn, false);

        final SQLiteStatement update = conn.prepare("UPDATE Devices SET " +
                "deletionTime = ? WHERE userId = ? AND hex(fingerprint) = ?");
        update.bind(1, System.currentTimeMillis());
        update.bind(2, account.userId);
        update.bind(3, fingerprint);
        update.step();
        update.dispose();

        if (conn.getChanges() == 0) {
            exchange.sendResponseHeaders(400, -1);
            return;
        }
    } finally {
        conn.dispose();
    }
    exchange.sendResponseHeaders(200, -1);
}
 
Example #19
Source File: BinaryLogger.java    From arcusplatform with Apache License 2.0 5 votes vote down vote up
@Override
public void bind(SQLiteConnection conn, SQLiteStatement stmt, BinaryLogDbData value) throws Exception {
   stmt.bind(1, value.key);
   stmt.bind(2, value.timestamp);
   stmt.bind(3, value.direction);
   stmt.bind(4, value.data);
}
 
Example #20
Source File: Db.java    From arcusplatform with Apache License 2.0 5 votes vote down vote up
@Override
public void row(SQLiteConnection conn, SQLiteStatement stmt) {
   if (result != null) {
      return;
   }

   try {
      result = extractor.extract(conn, stmt);
   } catch (Exception ex) {
      log.debug("sql extractor failed: {}", ex.getMessage(), ex);
   }
}
 
Example #21
Source File: SQLiteWrapperUtils.java    From sync-android with Apache License 2.0 5 votes vote down vote up
static List<Integer> getColumnTypes(SQLiteStatement stmt) throws SQLiteException {
//        Log.v(LOG_TAG, "getColumnTypes()");
        List<Integer> columnTypes = new ArrayList<Integer>();
        int columnCount = stmt.columnCount();
        for (int i = 0; i < columnCount; i++) {
            columnTypes.add(i, mapColumnType(stmt.columnType(i)));
        }
//        Log.v(LOG_TAG, "columnTypes:" + columnTypes);
        return columnTypes;
    }
 
Example #22
Source File: MbVectorTilesInputFormat.java    From mrgeo with Apache License 2.0 5 votes vote down vote up
protected long getRecordCount(Configuration conf) throws IOException
{
  String countQuery = "SELECT COUNT(*) FROM tiles WHERE zoom_level=?";
  // Run the count query and grab the result.
  SQLiteConnection conn = null;
  try {
    conn = MbVectorTilesDataProvider.getDbConnection(dbSettings, conf);
    SQLiteStatement stmt = null;
    try {
      stmt = conn.prepare(countQuery, false);
      stmt.bind(1, zoomLevel);
      if (stmt.step()) {
        return stmt.columnLong(0);
      }
      else {
        throw new IOException("Unable to count tiles for zoom " + zoomLevel + " in " + dbSettings.getFilename());
      }
    }
    finally {
      if (stmt != null) {
        stmt.dispose();
      }
    }
  }
  catch (SQLiteException e)
  {
    String msg = "Unable to get the count of records using query: " + countQuery;
    log.error(msg, e);
    throw new IOException(msg, e);
  }
  finally {
    if (conn != null) {
      conn.dispose();
    }
  }
}
 
Example #23
Source File: ConfigService.java    From arcusplatform with Apache License 2.0 5 votes vote down vote up
@Override
public void bind(SQLiteConnection conn, SQLiteStatement stmt, KeyValuePair pair) throws Exception {
   ValueWithTime<String> val = pair.value;

   stmt.bind(1, pair.key);
   DbUtils.bind(stmt, val == null ? null : val.value, 2);
   stmt.bind(3, val == null ? Long.MIN_VALUE : val.lastUpdateTime);
   stmt.bind(4, val == null ? Long.MIN_VALUE : val.lastReportTime);
}
 
Example #24
Source File: ConfigService.java    From arcusplatform with Apache License 2.0 5 votes vote down vote up
@SuppressWarnings("null")
private static boolean configTableExists(Db db) {
   String name = db.query(CHECK_CONFIG_TABLE, new DbExtractor<String>() {
      @Override
      @Nullable
      public String extract(SQLiteConnection conn, SQLiteStatement stmt) throws Exception {
         return stmt.columnString(0);
      }
   });
   return name != null;
}
 
Example #25
Source File: SQLiteWrapperUtils.java    From sync-android with Apache License 2.0 5 votes vote down vote up
static Tuple getDataRow(SQLiteStatement stmt) throws SQLiteException {
        logger.entering("com.cloudant.sync.internal.sqlite.sqlite4java.SQLiteWrapperUtils","getDataRow",stmt);
        Tuple result = new Tuple(getColumnTypes(stmt));
        for (int i = 0; i < stmt.columnCount(); i++) {
            Integer type = stmt.columnType(i);
//            Log.v(LOG_TAG, "i: " + i + ", type: " + mapColumnType(type) + ", expected type: " + result.getType(i));
            switch (type) {
                case SQLiteConstants.SQLITE_NULL:
                    result.put(i);
                    break;
                case SQLiteConstants.SQLITE_TEXT:
                    result.put(i, stmt.columnString(i));
                    break;
                case SQLiteConstants.SQLITE_INTEGER:
                    result.put(i, stmt.columnLong(i));
                    break;
                case SQLiteConstants.SQLITE_FLOAT:
                    result.put(i, Double.valueOf(stmt.columnDouble(i)).floatValue());
                    break;
                case SQLiteConstants.SQLITE_BLOB:
                    result.put(i, stmt.columnBlob(i));
                    break;
                default:
                    throw new IllegalArgumentException("Unsupported data type: " + type);
            }
        }
        return result;
    }
 
Example #26
Source File: BackupUtils.java    From arcusplatform with Apache License 2.0 5 votes vote down vote up
@SuppressWarnings("null")
public static boolean configTableExists(Db db, String tableName) {
   String name = db.query(
         "SELECT name FROM sqlite_master WHERE type='table' AND name='" + tableName + "'",
      new DbExtractor<String>() {
         @Override
         @Nullable
         public String extract(SQLiteConnection conn, SQLiteStatement stmt) throws Exception {
            return stmt.columnString(0);
         }
      }
   );

   return name != null;
}
 
Example #27
Source File: Db.java    From arcusplatform with Apache License 2.0 5 votes vote down vote up
@Override
public List<?> extract(SQLiteConnection conn, SQLiteStatement stmt) throws Exception {
   int count = stmt.columnCount();
   List<Object> results = new ArrayList<>(count);
   for (int i = 0; i < count; ++i) {
      results.add(stmt.columnValue(i));
   }

   return results;
}
 
Example #28
Source File: Db.java    From arcusplatform with Apache License 2.0 5 votes vote down vote up
@Override
public void bind(SQLiteConnection conn, SQLiteStatement stmt, List<?> args) throws Exception {
   int idx = 0;
   for (Object arg : args) {
      DbUtils.bind(stmt, arg, ++idx);
   }
}
 
Example #29
Source File: Db.java    From arcusplatform with Apache License 2.0 5 votes vote down vote up
@Override
public void row(SQLiteConnection conn, SQLiteStatement stmt) {
   try {
      O result = extractor.extract(conn, stmt);
      if (result != null) {
         results.add(result);
      }
   } catch (Exception ex) {
      log.debug("sql extractor failed: {}", ex.getMessage(), ex);
   }
}
 
Example #30
Source File: TestDbService.java    From arcusplatform with Apache License 2.0 5 votes vote down vote up
@Override
public void bind(SQLiteConnection conn, SQLiteStatement stmt, TestPojo value) throws Exception {
   stmt.bind(1, value.key);
   stmt.bind(2, value.value1);
   stmt.bind(3, value.value2);
   stmt.bind(4, value.value3);
}