Java SqlQuery类代码示例

import com.avaje.ebean.SqlQuery; //导入依赖的package包/类
/**
 * Return the list of values for a
 * {@link DynamicMultiItemCustomAttributeValue}.
 * 
 * @param i18nMessagesPlugin
 *            the i18n messages service
 * @param uid
 *            the uid of the current user
 */
public ISelectableValueHolderCollection<Long> getValueHoldersCollectionFromNameForDynamicMultiItemCustomAttribute(II18nMessagesPlugin i18nMessagesPlugin,
        String uid) {
    String sql = getSelectionQuery();
    if (log.isDebugEnabled()) {
        log.debug("SQL Query : " + sql);
    }
    SqlQuery sqlQuery = Ebean.createSqlQuery(sql);
    sqlQuery.setParameter("uid", uid);
    sqlQuery.setParameter("lang", i18nMessagesPlugin.getCurrentLanguage().getCode());
    if (log.isDebugEnabled()) {
        log.debug("uid passed as a parameter : " + uid);
    }
    List<SqlRow> rows = sqlQuery.findList();
    DefaultSelectableValueHolderCollection<Long> valueHolders = new DefaultSelectableValueHolderCollection<Long>();
    if (rows != null) {
        for (SqlRow row : rows) {
            valueHolders.add(new DefaultSelectableValueHolder<Long>(row.getLong("value"), row.getString("name")));
        }
    }
    return valueHolders;
}
 

import com.avaje.ebean.SqlQuery; //导入依赖的package包/类
public String getUsernameOrIP(final Context ctx, boolean allow_ip) {
     Logger.debug("Authenticator::getUsername " + ctx + ", " + allow_ip);
     final AuthUser u = mAuth.getUser(ctx.session());

     if (u != null) {
         User the_user = User.findByAuthUserIdentity(u);
         if (the_user != null) {
	// If a user is logged in already, check the session timeout.
	// If there is no timeout, or we can't parse it, or it's too old,
	// don't count the user as being logged in.
	Session sess = ctx.session();
	if (sess.get("timeout") != null) {
		try
		{
			long timeout = Long.parseLong(sess.get("timeout"));
			if (System.currentTimeMillis() - timeout < 1000 * 60 * 30) {
				sess.put("timeout", "" + System.currentTimeMillis());
				return the_user.email;
			}
		}
		catch (NumberFormatException e) {
		}
	}
}
     }

     // If we don't have a logged-in user, try going by IP address.
     if (allow_ip && Organization.getByHost() != null) {
         String sql = "select ip from allowed_ips where ip like :ip and organization_id=:org_id";
         SqlQuery sqlQuery = Ebean.createSqlQuery(sql);
         String address = Application.getRemoteIp();
         sqlQuery.setParameter("ip", address);
         sqlQuery.setParameter("org_id", Organization.getByHost().id);

         // execute the query returning a List of MapBean objects
         SqlRow result = sqlQuery.findUnique();

         if (result != null) {
             return address;
         }
     }

     return null;
 }
 

import com.avaje.ebean.SqlQuery; //导入依赖的package包/类
public Result viewAccess() {
    Organization org = OrgConfig.get().org;
    List<User> users =
        User.find.where().eq("organization", org)
        .order("name ASC")
        .findList();

    String allowed_ip = "";
    String sql = "select ip from allowed_ips where organization_id=:org_id";
    SqlQuery sqlQuery = Ebean.createSqlQuery(sql);
    sqlQuery.setParameter("org_id", org.id);
    List<SqlRow> result = sqlQuery.findList();
    if (result.size() > 0) {
        allowed_ip = result.get(0).getString("ip");
    }

    Form<User> user_form = Form.form(User.class);
    return ok(views.html.view_access.render(users, allowed_ip, user_form));
}
 

import com.avaje.ebean.SqlQuery; //导入依赖的package包/类
/**
 * Return the list of values for a
 * {@link DynamicSingleItemCustomAttributeValue}.
 * 
 * @param i18nMessagesPlugin
 *            the i18n messages service
 * @param searchstring
 *            the string entered by the user in the autocomplete field
 * @param uid
 *            the uid of the current user
 */
public ISelectableValueHolderCollection<Long> getValueHoldersCollectionFromNameForDynamicSingleItemCustomAttribute(II18nMessagesPlugin i18nMessagesPlugin,
        String searchstring, String uid) {
    String sql = getSelectionQuery() + " " + getFilterWhereClause();
    if (getDynamicSingleMaxRecords() != 0 && isAutoComplete()) {
        sql = sql + " LIMIT " + getDynamicSingleMaxRecords();
    }
    if (log.isDebugEnabled()) {
        log.debug("SQL Query : " + sql);
    }
    SqlQuery sqlQuery = Ebean.createSqlQuery(sql);
    sqlQuery.setParameter("searchstring", searchstring + "%");
    sqlQuery.setParameter("uid", uid);
    sqlQuery.setParameter("lang", i18nMessagesPlugin.getCurrentLanguage().getCode());
    if (log.isDebugEnabled()) {
        log.debug("uid passed as a parameter : " + uid);
    }
    List<SqlRow> rows = sqlQuery.findList();
    DefaultSelectableValueHolderCollection<Long> valueHolders = new DefaultSelectableValueHolderCollection<Long>();
    if (rows != null) {
        for (SqlRow row : rows) {
            valueHolders.add(new DefaultSelectableValueHolder<Long>(row.getLong("value"), row.getString("name")));
        }
    }
    return valueHolders;
}
 

import com.avaje.ebean.SqlQuery; //导入依赖的package包/类
/**
 * Return a value from the specified name using the configured SQL queries.
 * 
 * @param nameOfValueHolder
 *            the name of the value holder
 * @return a long value
 */
public Long getValueFromName(String nameOfValueHolder) {
    try {
        String sql = getSelectionQuery() + " " + getValueFromNameWhereClause();
        if (log.isDebugEnabled()) {
            log.debug("SQL Query : " + sql);
        }
        SqlQuery sqlQuery = Ebean.createSqlQuery(sql);
        sqlQuery.setParameter("nametofind", nameOfValueHolder);
        SqlRow row = sqlQuery.findUnique();
        return row != null ? row.getLong("value") : null;
    } catch (RuntimeException e) {
        log.error(String.format("Error with custom attribute %s with name=%s", uuid, nameOfValueHolder));
        throw e;
    }
}
 

import com.avaje.ebean.SqlQuery; //导入依赖的package包/类
/**
 * Return a value from the specified name using the configured SQL queries.
 * 
 * @param i18nMessagesPlugin
 *            the i18n messages service
 * @param valueOfValueHolder
 *            the value of the value holder
 */
public String getNameFromValue(II18nMessagesPlugin i18nMessagesPlugin, Long valueOfValueHolder) {
    try {
        String sql = getSelectionQuery() + " " + getNameFromValueWhereClause();
        if (log.isDebugEnabled()) {
            log.debug("SQL Query : " + sql);
        }
        SqlQuery sqlQuery = Ebean.createSqlQuery(sql);
        sqlQuery.setParameter("lang", i18nMessagesPlugin.getCurrentLanguage().getCode());
        sqlQuery.setParameter("valuetofind", valueOfValueHolder);
        SqlRow row = sqlQuery.findUnique();
        return row != null ? row.getString("name") : null;
    } catch (RuntimeException e) {
        log.error(String.format("Error with custom attribute %s with value=%s", uuid, String.valueOf(valueOfValueHolder)));
        throw e;
    }
}
 

import com.avaje.ebean.SqlQuery; //导入依赖的package包/类
/**
 * Load the keys stored into the database
 */
private void loadFromDb() {
    try {
        SqlQuery query = Ebean.createSqlQuery("select * from i18n_messages");
        List<SqlRow> rows = query.findList();
        if (rows != null) {
            for (SqlRow row : rows) {
                String language = row.getString("language");
                Hashtable<Object, Object> messages = getI18nMessagesStore().get(language);
                if (messages == null) {
                    messages = new Hashtable<Object, Object>();
                    getI18nMessagesStore().put(language, messages);
                }
                messages.put(row.getString("key"), row.getString("value"));
            }
        }
    } catch (Exception e) {
        throw new IllegalArgumentException("Error while loading the resources form the database", e);
    }
}
 

import com.avaje.ebean.SqlQuery; //导入依赖的package包/类
@Test
public void test() {

  String sql = 
        " SELECT table_name "
      + " FROM information_schema.tables "
      + " WHERE table_schema = 'public'"
      + " AND table_name like :name";

  SqlQuery sqlQuery = Ebean.createSqlQuery(sql);
  sqlQuery.setParameter("name", "o_%");
  
  List<SqlRow> rows = sqlQuery.findList();
  for (SqlRow sqlRow : rows) {
    String tableName = sqlRow.getString("table_name");
    System.out.println(tableName);
  }
  
}
 

import com.avaje.ebean.SqlQuery; //导入依赖的package包/类
public static Organization getByHost() {
     String host = Context.current().request().host();

     String cache_key = "Organization::getByHost::" + host;

     Object cached_val = Cache.get(cache_key);
     if (cached_val!= null) {
         return (Organization)cached_val;
     }

     String sql = "select organization_id from organization_hosts where host like :host";
     SqlQuery sqlQuery = Ebean.createSqlQuery(sql);
     sqlQuery.setParameter("host", host);

     // execute the query returning a List of MapBean objects
     SqlRow result = sqlQuery.findUnique();

     if (result != null) {
         Organization org_result = find.byId(result.getInteger("organization_id"));
         Cache.set(cache_key, org_result, 60); // cache for 1 minute
         return org_result;
     } else {
if (host.matches("[a-z]")) {
	Logger.error("Unknown organization for host: " + host);
}
     }

     return null;
 }
 

import com.avaje.ebean.SqlQuery; //导入依赖的package包/类
public Result searchManual(String searchString) {
    Map<String, Object> scopes = new HashMap<String, Object>();
    scopes.put("searchString", searchString);

    String sql =
        "SELECT ei.id " +
        "FROM entry_index ei " +
        "WHERE ei.document @@ plainto_tsquery(:searchString) " +
        "and ei.organization_id=:orgId " +
        "ORDER BY ts_rank(ei.document, plainto_tsquery(:searchString), 0) DESC";

    OrgConfig orgConfig = OrgConfig.get();
    SqlQuery sqlQuery = Ebean.createSqlQuery(sql);
    sqlQuery.setParameter("orgId", orgConfig.org.id);
    sqlQuery.setParameter("searchString", searchString);

    List<SqlRow> result = sqlQuery.findList();
    List<Integer> entryIds = new ArrayList<Integer>();
    for (int i = 0; i < result.size(); i++) {
        entryIds.add(result.get(i).getInteger("id"));
    }

    Map<Integer, String> entryToHeadline = new HashMap<Integer, String>();
    if (entryIds.size() > 0) {
        sql = "SELECT e.id, ts_headline(e.content, plainto_tsquery(:searchString), 'MaxFragments=5') as headline " +
            "FROM entry e WHERE e.id IN (:entryIds)";
        sqlQuery = Ebean.createSqlQuery(sql);
        sqlQuery.setParameter("searchString", searchString);
        sqlQuery.setParameter("entryIds", entryIds);
        List<SqlRow> headlines = sqlQuery.findList();
        for (int i = 0; i < headlines.size(); i++) {
            entryToHeadline.put(headlines.get(i).getInteger("id"),
                headlines.get(i).getString("headline"));
        }
    }

    Map<Integer, Entry> entries = Entry.find
        .fetch("section", new FetchConfig().query())
        .fetch("section.chapter", new FetchConfig().query())
        .where().in("id", entryIds).findMap();

    ArrayList<Map<String, Object>> entriesList = new ArrayList<Map<String, Object>>();
    for (int i = 0; i < result.size(); i++) {
        Map<String, Object> entryInfo = new HashMap<String, Object>();
        int entryId = result.get(i).getInteger("id");
        entryInfo.put("entry", entries.get(entryId));
        entryInfo.put("headline", entryToHeadline.get(entryId));
        entriesList.add(entryInfo);
    }
    scopes.put("entries", entriesList);

    return ok(views.html.main_with_mustache.render(
        "Manual Search: " + searchString,
        "manual",
        "",
        "manual_search.html",
        scopes));
}