com.j256.ormlite.stmt.SelectArg Java Examples

@Test
public void testWrappedIterableInvalidPreparedQueryFor() throws Exception {
	Dao<Foo, Integer> dao = createDao(Foo.class, true);

	Foo foo1 = new Foo();
	assertEquals(1, dao.create(foo1));

	Foo foo2 = new Foo();
	assertEquals(1, dao.create(foo2));

	CloseableWrappedIterable<Foo> wrapped =
			dao.getWrappedIterable(dao.queryBuilder().where().eq(Foo.ID_COLUMN_NAME, new SelectArg()).prepare());
	try {
		wrapped.iterator();
		fail("Should have thrown");
	} catch (IllegalStateException e) {
		// expected
	} finally {
		wrapped.close();
	}
}
 

protected PreparedQuery<T> getPreparedQuery() throws SQLException {
	if (dao == null) {
		return null;
	}
	if (preparedQuery == null) {
		SelectArg fieldArg = new SelectArg();
		fieldArg.setValue(parentId);
		QueryBuilder<T, ID> qb = dao.queryBuilder();
		if (orderColumn != null) {
			qb.orderBy(orderColumn, orderAscending);
		}
		preparedQuery = qb.where().eq(foreignFieldType.getColumnName(), fieldArg).prepare();
		if (preparedQuery instanceof MappedPreparedStmt) {
			@SuppressWarnings("unchecked")
			MappedPreparedStmt<T, Object> mappedStmt = ((MappedPreparedStmt<T, Object>) preparedQuery);
			mappedStmt.setParentInformation(parent, parentId);
		}
	}
	return preparedQuery;
}
 

/**
 * Returns the object if the query failed or null otherwise.
 */
private boolean checkQueryResult(Dao<AllTypes, Integer> allDao, QueryBuilder<AllTypes, Integer> qb,
		AllTypes allTypes, String fieldName, Object value, boolean required) throws SQLException {
	qb.where().eq(fieldName, value);
	List<AllTypes> results = allDao.query(qb.prepare());
	if (required) {
		assertEquals(1, results.size());
		assertTrue(allDao.objectsEqual(allTypes, results.get(0)));
	} else if (results.size() == 1) {
		assertTrue(allDao.objectsEqual(allTypes, results.get(0)));
	} else {
		return false;
	}

	SelectArg selectArg = new SelectArg();
	qb.where().eq(fieldName, selectArg);
	selectArg.setValue(value);
	results = allDao.query(qb.prepare());
	assertEquals(1, results.size());
	assertTrue(allDao.objectsEqual(allTypes, results.get(0)));
	return true;
}
 

/**
 * Build our query for Post objects that match a User.
 */
private PreparedQuery<Post> makePostsForUserQuery() throws SQLException {
	// build our inner query for UserPost objects
	QueryBuilder<UserPost, Integer> userPostQb = userPostDao.queryBuilder();
	// just select the post-id field
	userPostQb.selectColumns(UserPost.POST_ID_FIELD_NAME);
	SelectArg userSelectArg = new SelectArg();
	// you could also just pass in user1 here
	userPostQb.where().eq(UserPost.USER_ID_FIELD_NAME, userSelectArg);

	// build our outer query for Post objects
	QueryBuilder<Post, Integer> postQb = postDao.queryBuilder();
	// where the id matches in the post-id from the inner query
	postQb.where().in(Post.ID_FIELD_NAME, userPostQb);
	return postQb.prepare();
}
 

/**
 * creates and inserts into the DB processed audit logs for a specific transaction ids.
 * 
 * Additionally, this enqueues alerts to be sent out in the future.
 * 
 * NB: This function commits the transaction in the manager that is provided.
 * 
 * @return the number of rows we added to the processed audit log table.
 */
public static final int putActionsForTransactionId(Manager manager, String transactionId) throws SQLException {
  Collection<DBProcessedAudit> actions = getActionsForTransactionId(manager, transactionId);
  for (DBProcessedAudit pa : actions) {
    manager.processedAuditDao.create(pa);
  }
  // some processed audit logs are added directly in the transaction.
  actions = manager.processedAuditDao.queryForEq(DBProcessedAudit.TRANSACTION_ID_FIELD_NAME, new SelectArg(transactionId));
  long minTimestampMs = Long.MAX_VALUE;
  for (DBProcessedAudit action : actions) {
    minTimestampMs = Math.min(action.getTimestampMs(), minTimestampMs);
  }
  EmailAlertManager.getInstance().createFutureAlertsFromAudits(manager, actions, minTimestampMs);
  manager.commitTransaction();
  
  return actions.size();
}
 

public static void addNew(Manager manager,
    AlertPredicate alertType, DBProcessedAudit audit, DBIdentity userToAlert, long minTimestampMs) throws SQLException {
  // These must be unique on (secretId, userId, alertType)  
  // e.g. If I share, then unshare, then re-share something 
  // with a user, only one of these events should ever be added.
  
  QueryBuilder<DBFutureAlert,Integer> builder = manager.futureAlertDao.queryBuilder();
  builder.where().eq(INACTIVE, false)
      .and().eq(ALERT_TYPE, new SelectArg(alertType))
      .and().eq(USER_ID_TO_ALERT, userToAlert.getId());
  
  builder.setCountOf(true);
  if (manager.futureAlertDao.countOf(builder.prepare()) > 0) {
    logger.info("Trying to add new alert for {}, {}. Existing alert already exists. Ignoring",
        alertType, userToAlert.getId());
  } else {
    logger.info("Inserting future alert for {}, {}.",
        alertType, userToAlert.getId());
    DBFutureAlert a = new DBFutureAlert();
    a.userIdToAlert = userToAlert.getId();
    a.transactionId = audit.getTransactionId();
    a.enqueueTimestampMs = minTimestampMs;
    a.alertType = alertType;
    manager.futureAlertDao.create(a);
  }
}
 

public static Stream getByName(ZulipApp app, String name) {
    Stream stream = null;
    try {
        RuntimeExceptionDao<Stream, Object> streams = app.getDao(Stream.class);
        stream = streams.queryBuilder().where()
                .eq(Stream.NAME_FIELD, new SelectArg(name)).queryForFirst();

        if (stream == null) {
            Log.w("Stream.getByName",
                    "We received a stream message for a stream we don't have data for. Fake it until you make it.");
            stream = new Stream(name);
            app.getDao(Stream.class).createIfNotExists(stream);
        }
    } catch (SQLException e) {
        throw new RuntimeException(e);
    }

    return stream;
}
 

@Override
protected MitroRPC processCommand(MitroRequestContext context) throws IOException, SQLException, MitroServletException {
  RPC.RemoveAllPendingGroupApprovalsForScopeRequest in = gson.fromJson(context.jsonRequest, RPC.RemoveAllPendingGroupApprovalsForScopeRequest.class);
  
  // TODO: verify auth
  
  DeleteBuilder<DBPendingGroup, Integer> deleter = context.manager.pendingGroupDao.deleteBuilder();
  deleter.where().eq(DBPendingGroup.SCOPE_NAME, new SelectArg(in.scope));
  deleter.delete();
  
  context.manager.addAuditLog(DBAudit.ACTION.REMOVE_PENDING_GROUP_SYNC, context.requestor, null, /*group*/null, null, in.scope);
  return new MitroRPC();
}
 

@Test
public void testAppendArgOrValueSelectArg() throws SQLException {
	SelectArg value = new SelectArg();
	StringBuilder sb = new StringBuilder();
	List<ArgumentHolder> argList = new ArrayList<ArgumentHolder>();
	cmpInt.appendArgOrValue(null, numberFieldType, sb, argList, value);
	assertEquals(1, argList.size());
	assertEquals(INT_COLUMN_NAME, value.getColumnName());
}
 

@Test
public void testSerializableWhere() throws Exception {
	Dao<AllTypes, Object> allDao = createDao(AllTypes.class, true);
	try {
		// can't query for a serial field
		allDao.queryBuilder().where().eq(AllTypes.SERIAL_FIELD_NAME, new SelectArg());
		fail("expected exception");
	} catch (SQLException e) {
		// expected
	}
}
 

/**
 * Example of created a query with a ? argument using the {@link SelectArg} object. You then can set the value of
 * this object at a later time.
 */
private void useSelectArgFeature() throws Exception {

	String name1 = "foo";
	String name2 = "bar";
	String name3 = "baz";
	assertEquals(1, accountDao.create(new Account(name1)));
	assertEquals(1, accountDao.create(new Account(name2)));
	assertEquals(1, accountDao.create(new Account(name3)));

	QueryBuilder<Account, Integer> statementBuilder = accountDao.queryBuilder();
	SelectArg selectArg = new SelectArg();
	// build a query with the WHERE clause set to 'name = ?'
	statementBuilder.where().like(Account.NAME_FIELD_NAME, selectArg);
	PreparedQuery<Account> preparedQuery = statementBuilder.prepare();

	// now we can set the select arg (?) and run the query
	selectArg.setValue(name1);
	List<Account> results = accountDao.query(preparedQuery);
	assertEquals("Should have found 1 account matching our query", 1, results.size());
	assertEquals(name1, results.get(0).getName());

	selectArg.setValue(name2);
	results = accountDao.query(preparedQuery);
	assertEquals("Should have found 1 account matching our query", 1, results.size());
	assertEquals(name2, results.get(0).getName());

	selectArg.setValue(name3);
	results = accountDao.query(preparedQuery);
	assertEquals("Should have found 1 account matching our query", 1, results.size());
	assertEquals(name3, results.get(0).getName());
}
 

/**
 * Build our query for User objects that match a Post
 */
private PreparedQuery<User> makeUsersForPostQuery() throws SQLException {
	QueryBuilder<UserPost, Integer> userPostQb = userPostDao.queryBuilder();
	// this time selecting for the user-id field
	userPostQb.selectColumns(UserPost.USER_ID_FIELD_NAME);
	SelectArg postSelectArg = new SelectArg();
	userPostQb.where().eq(UserPost.POST_ID_FIELD_NAME, postSelectArg);

	// build our outer query
	QueryBuilder<User, Integer> userQb = userDao.queryBuilder();
	// where the user-id matches the inner query's user-id field
	userQb.where().in(Post.ID_FIELD_NAME, userPostQb);
	return userQb.prepare();
}
 

private void prepareSearchByTitleQuery() {
    try {
        QueryBuilder<PhotoEntity, Integer> queryBuilder = mPhotosDao.queryBuilder();
        mSearchByTitleQuerySelectArg = new SelectArg();
        queryBuilder.where().like(PhotoEntity.Fields.TITLE, mSearchByTitleQuerySelectArg);
        mSearchByTitleQuery = queryBuilder.prepare();
    } catch (SQLException e) {
        Log.wtf(LOG_TAG, "Preparing of SearchByTitleQuery failed", e);
    }
}
 

@Override
protected void handleRequest(HttpServletRequest request,
    HttpServletResponse response) throws Exception {
  String customerToken = request.getParameter("customer_token");

  if (Strings.isNullOrEmpty(customerToken)) {
    response.sendError(HttpServletResponse.SC_BAD_REQUEST, "Missing customer_token parameter");
    return;
  }

  try (Manager mgr = ManagerFactory.getInstance().newManager()) {
    List<DBStripeCustomer> resultSet = mgr.stripeCustomerDao.queryForEq("token",  new SelectArg(customerToken));
    assert(resultSet.size() == 1);
    DBStripeCustomer stripeCustomer = resultSet.get(0);

    DBGroup org = mgr.groupDao.queryForId(stripeCustomer.getOrgId());
    assert(null != org);

    Plan plan = Plan.retrieve(SubmitPayment.MITRO_PRO_PLAN_ID, SubmitPayment.STRIPE_API_KEY);

    GetCustomerResponse out = new GetCustomerResponse();
    out.orgName = org.getName();
    out.numUsers = stripeCustomer.getNumUsers();
    out.planName = plan.getName();
    out.planUnitCost = plan.getAmount();

    response.getWriter().write(gson.toJson(out));
  }
}
 

protected void saveMitroAccessEmail(Manager mgr, String email) throws SQLException {
  Preconditions.checkArgument(!Strings.isNullOrEmpty(email));
  PreparedQuery<DBMitroAccessEmail> query = mgr.mitroAccessEmailDao.queryBuilder().setCountOf(true)
      .where().eq(DBMitroAccessEmail.EMAIL_FIELD_NAME, new SelectArg(email)).prepare();
  if (0 == mgr.mitroAccessEmailDao.countOf(query)) {
    DBMitroAccessEmail mitroAccessEmail = new DBMitroAccessEmail();
    mitroAccessEmail.setEmail(email);
    mgr.mitroAccessEmailDao.create(mitroAccessEmail);
  }
}
 

@Override
public Where<Message, Object> modWhere(Where<Message, Object> where)
        throws SQLException {

    where.eq(Message.RECIPIENTS_FIELD, new SelectArg(recipientString));
    return where;
}
 

@Override
public Where<Message, Object> modWhere(Where<Message, Object> where)
        throws SQLException {
    where.raw(
            Message.CONTENT_FIELD + " LIKE ? COLLATE NOCASE",
            new SelectArg(Message.CONTENT_FIELD, "%"
                    + DatabaseHelper.likeEscape(query) + "%"));
    return where;
}
 

@Override
public Where<Message, Object> modWhere(Where<Message, Object> where)
        throws SQLException {
    where.eq(Message.STREAM_FIELD, new SelectArg(stream));
    if (subject != null) {
        where.and().eq(Message.SUBJECT_FIELD, new SelectArg(subject));
    }
    return where;
}
 

@SuppressWarnings("WeakerAccess")
public static Person getByEmail(Dao<Person, ?> dao, String email) {
    try {
        return dao.queryBuilder().where()
                .eq(Person.EMAIL_FIELD, new SelectArg(email))
                .queryForFirst();
    } catch (SQLException e) {
        throw new RuntimeException(e);
    }

}
 

/**
 * This function returns the last message read in {@param streamName} stream.
 *
 * @param app        {@link ZulipApp}
 * @param streamName name of stream
 * @return last message {@link Message} read
 */
public static Message getLastMessageRead(ZulipApp app, String streamName) {
    try {
        Dao<Message, Integer> messageDao = app.getDatabaseHelper().getDao(Message.class);

        // query for message in given stream and orderby timestamp decreasingly
        return messageDao.queryBuilder().orderBy(Message.TIMESTAMP_FIELD, false)
                .where().eq(Message.RECIPIENTS_FIELD, new SelectArg(Message.RECIPIENTS_FIELD, streamName))
                .queryForFirst();
    } catch (SQLException e) {
        ZLog.logException(e);
    }
    return null;
}
 

/**
 * Checks stream name is valid or not
 *
 * @param app        ZulipApp
 * @param streamName Checks this stream name is valid or not
 * @return null if stream does not exist else cursor
 */
public static Stream streamCheckBeforeMessageSend(ZulipApp app, CharSequence streamName) {
    if (streamName == null) {
        return null;
    }
    try {
        return app.getDao(Stream.class)
                .queryBuilder().where()
                .eq(Stream.NAME_FIELD, new SelectArg(Stream.NAME_FIELD, streamName)).queryForFirst();
    } catch (SQLException e) {
        ZLog.logException(e);
    }
    return null;
}
 

@Override
protected void handleRequest(HttpServletRequest request,
    HttpServletResponse response) throws Exception {
    String customerToken = request.getParameter("customer_token");
    String cardToken = request.getParameter("cc_token");

    if (Strings.isNullOrEmpty(customerToken)) {
      response.sendError(HttpServletResponse.SC_BAD_REQUEST, "Missing customer_token parameter");
      return;
    }
    if (Strings.isNullOrEmpty(cardToken)) {
      response.sendError(HttpServletResponse.SC_BAD_REQUEST, "Missing card_token parameter");
      return;
    }
 
    try (Manager mgr = ManagerFactory.getInstance().newManager()) {
      List<DBStripeCustomer> resultSet = mgr.stripeCustomerDao.queryForEq("token",  new SelectArg(customerToken));
      assert(resultSet.size() == 1);
      DBStripeCustomer stripeCustomer = resultSet.get(0);

      DBGroup org = mgr.groupDao.queryForId(stripeCustomer.getOrgId());
      assert(null != org);

      Stripe.apiKey = STRIPE_API_KEY;
      Customer customer;

      if (Strings.isNullOrEmpty(stripeCustomer.getStripeCustomerId())) {
        Map<String, Object> customerParams = new HashMap<String, Object>();
        customerParams.put("card", cardToken);
        customerParams.put("description", org.getName());

        customer = Customer.create(customerParams);

        // Associate the stripe customer with org id
        stripeCustomer.setStripeCustomerId(customer.getId());
        mgr.stripeCustomerDao.update(stripeCustomer);
        mgr.commitTransaction();
      } else {
        // throws exception if customer not found.
        customer = Customer.retrieve(stripeCustomer.getStripeCustomerId());
      }

      assert(customer != null);

      // TODO: allow updating subscription.
      if (!customer.getSubscriptions().all(Collections.<String,Object>emptyMap()).getData().isEmpty()) {
        response.getWriter().write("Already subscribed to Mitro Pro");
        return;
      }

      // Warning: don't combine with Customer.create()
      // It is possible to subscribe to a plan during customer creation, but at the risk of charging the
      // customer twice if commit the stripe customer id fails.
      Map<String, Object> subscriptionParams = new HashMap<String, Object>();
      subscriptionParams.put("plan", MITRO_PRO_PLAN_ID);
      subscriptionParams.put("quantity", stripeCustomer.getNumUsers());
      // throws exception if subscribe fails
      customer.createSubscription(subscriptionParams);

      logger.info("Payment submitted for {}", org.getName());
      response.getWriter().write("Payment successful!");
    }
}
 

@Test
public void testInSubQuerySelectArgs() throws Exception {
	Dao<Foo, Integer> fooDao = createDao(Foo.class, true);
	Dao<Basic, String> basicDao = createDao(Basic.class, true);

	Basic basic1 = new Basic();
	String string1 = "ewpofjewgprgrg";
	basic1.id = string1;
	assertEquals(1, basicDao.create(basic1));
	Basic basic2 = new Basic();
	String string2 = "e2432423432wpofjewgprgrg";
	basic2.id = string2;
	assertEquals(1, basicDao.create(basic2));

	Foo foo1 = new Foo();
	foo1.stuff = basic1.id;
	Foo foo2 = new Foo();
	foo2.stuff = basic2.id;

	int num1 = 7;
	for (int i = 0; i < num1; i++) {
		assertEquals(1, fooDao.create(foo1));
	}
	int num2 = 17;
	long maxId = 0;
	for (int i = 0; i < num2; i++) {
		assertEquals(1, fooDao.create(foo2));
		if (foo2.id > maxId) {
			maxId = foo2.id;
		}
	}
	// using seletArgs
	SelectArg arg1 = new SelectArg();
	SelectArg arg2 = new SelectArg();
	QueryBuilder<Basic, String> bqb = basicDao.queryBuilder();
	bqb.selectColumns(Basic.ID_FIELD);
	bqb.where().eq(Basic.ID_FIELD, arg1);
	PreparedQuery<Foo> preparedQuery =
			fooDao.queryBuilder().where().in(Foo.STUFF_FIELD_NAME, bqb).and().lt(Foo.ID_FIELD_NAME, arg2).prepare();
	arg1.setValue(string1);
	// this should get none
	arg2.setValue(0);
	List<Foo> results = fooDao.query(preparedQuery);
	assertEquals(0, results.size());
}
 

@Override
public Where<Message, Object> modWhere(Where<Message, Object> where)
        throws SQLException {
    where.eq(Message.MESSAGE_STAR_FIELD, new SelectArg(isStarred));
    return where;
}
 

/**
 * This generates a list of SelectArgs which must be used when 
 * building queries, especially with Strings. Strings are otherwise not escaped, 
 * even when used with .in() or .eq(). This can result in an SQL injection
 * vulnerability.
 *  
 * See: http://ormlite.com/javadoc/ormlite-core/doc-files/ormlite_3.html#Select-Arguments
 * 
 * @param items A Collection of any specific type of Object.
 * @return A list of SelectArgs that can be used in place of the List of Objects.
 */
public static List<SelectArg> makeSelectArgsFromList(
    Collection<? extends Object> items) {
  List<SelectArg> args = new ArrayList<SelectArg>(items.size());
  for (Object item : items) {
    args.add(new SelectArg(item));
  }
  return args;
}