Java RawSql类代码示例

import com.avaje.ebean.RawSql; //导入依赖的package包/类
/**
 * Get the total budget of initiatives for a budget bucket.
 * 
 * @param budgetBucketId
 *            the budget bucket id
 * @param isOpex
 *            set to true for OPEX budget, else CAPEX
 */
public static Double getBudgetAsAmountByBucketAndOpex(Long budgetBucketId, boolean isOpex) {

    String sql = "SELECT SUM(pebl.amount * pebl.currency_rate) as totalAmount FROM portfolio_entry_budget_line pebl"
            + " JOIN portfolio_entry_budget peb ON pebl.portfolio_entry_budget_id = peb.id"
            + " JOIN life_cycle_instance_planning lcip ON peb.id = lcip.portfolio_entry_budget_id"
            + " JOIN life_cycle_instance lci ON lcip.life_cycle_instance_id = lci.id" + " JOIN portfolio_entry pe ON lci.portfolio_entry_id = pe.id"
            + " WHERE pebl.deleted = 0 AND pebl.is_opex = " + isOpex + " AND pebl.budget_bucket_id = " + budgetBucketId
            + " AND peb.deleted = 0 AND pe.deleted = 0 AND lci.deleted = 0 AND lci.is_active=1 AND lcip.deleted = 0 AND lcip.is_frozen = 0";

    RawSql rawSql = RawSqlBuilder.parse(sql).create();

    Query<TotalAmount> query = Ebean.find(TotalAmount.class);

    Double totalAmount = query.setRawSql(rawSql).findUnique().totalAmount;

    if (totalAmount == null) {
        return 0.0;
    }

    return totalAmount;
}
 

import com.avaje.ebean.RawSql; //导入依赖的package包/类
/**
 * Get the total timesheeted hours of a planning package.
 * 
 * @param planningPackage
 *            the planning package
 */
public static BigDecimal getTimesheetLogAsTotalHoursByPEPlanningPackage(PortfolioEntryPlanningPackage planningPackage) {

    String sql = "SELECT SUM(tl.hours) AS totalHours FROM timesheet_log tl " + "JOIN timesheet_entry te ON tl.timesheet_entry_id = te.id "
            + "JOIN timesheet_report tr ON te.timesheet_report_id = tr.id "
            + "WHERE tl.deleted = false AND te.deleted = false AND tr.deleted = false AND te.portfolio_entry_planning_package_id = '" + planningPackage.id
            + "'";

    RawSql rawSql = RawSqlBuilder.parse(sql).create();

    Query<TotalHours> query = Ebean.find(TotalHours.class);

    BigDecimal totalHours = query.setRawSql(rawSql).findUnique().totalHours;

    if (totalHours == null) {
        return BigDecimal.ZERO;
    }

    return totalHours;
}
 

import com.avaje.ebean.RawSql; //导入依赖的package包/类
/**
 * Get the total timesheeted hours of a portfolio entry.
 * 
 * @param portfolioEntryId
 *            the portfolio entry
 */
public static BigDecimal getTimesheetLogAsTotalHoursByPE(Long portfolioEntryId) {

    String sql = "SELECT SUM(tl.hours) AS totalHours FROM timesheet_log tl " + "JOIN timesheet_entry te ON tl.timesheet_entry_id = te.id "
            + "JOIN timesheet_report tr ON te.timesheet_report_id = tr.id "
            + "WHERE tl.deleted = false AND te.deleted = false AND tr.deleted = false AND te.portfolio_entry_id = '" + portfolioEntryId
            + "' AND (tr.status = '" + Status.APPROVED.name() + "' OR tr.status = '" + Status.LOCKED.name() + "' )";

    RawSql rawSql = RawSqlBuilder.parse(sql).create();

    Query<TotalHours> query = Ebean.find(TotalHours.class);

    BigDecimal totalHours = query.setRawSql(rawSql).findUnique().totalHours;

    if (totalHours == null) {
        return BigDecimal.ZERO;
    }

    return totalHours;
}
 

import com.avaje.ebean.RawSql; //导入依赖的package包/类
public static List<OrgUnit> getOrgUnitAsListByKeywordsAndFilter2(String key, boolean mustBeActive, boolean mustBeSponsor, boolean mustBeDelivery) {
    key = key.replace("\"", "\\\"");
    String sql = "SELECT ou.id FROM `org_unit` ou LEFT OUTER JOIN `i18n_messages` im ON im.key = ou.name WHERE ou.deleted = 0";
    if (mustBeActive) {
        sql += " AND ou.is_active = 1";
    }
    if (mustBeSponsor) {
        sql += " AND ou.can_sponsor = 1";
    }
    if (mustBeDelivery) {
        sql += " AND ou.can_deliver = 1";
    }
    sql += " AND (im.language = '" + Http.Context.current().lang().code() + "' OR im.language IS NULL)";
    sql += " AND (ou.name LIKE \"" + key + "\" OR ou.ref_id LIKE \"" + key + "\" OR im.value LIKE \"" + key + "\") ";
    RawSql rawSql = RawSqlBuilder.parse(sql).columnMapping("ou.id", "id").create();
    return findOrgUnit.query().setRawSql(rawSql).findList();
}
 

import com.avaje.ebean.RawSql; //导入依赖的package包/类
/**
 * Get the total portfolio entry budget of a portfolio.
 * 
 * @param id
 *            the portfolio id
 * @param isOpex
 *            set to true for OPEX budget, else CAPEX
 */
public static Double getPortfolioAsBudgetAmountByOpex(Long id, boolean isOpex) {

    String sql = "SELECT SUM(pebl.amount * pebl.currency_rate) as totalAmount FROM portfolio_entry_budget_line pebl "
            + "JOIN portfolio_entry_budget peb ON pebl.portfolio_entry_budget_id=peb.id "
            + "JOIN life_cycle_instance_planning lcip ON peb.id = lcip.portfolio_entry_budget_id "
            + "JOIN portfolio_entry pe ON lcip.life_cycle_instance_id = pe.active_life_cycle_instance_id "
            + "JOIN portfolio_has_portfolio_entry phpe ON pe.id = phpe.portfolio_entry_id " + "WHERE pebl.deleted=0 AND pebl.is_opex=" + isOpex
            + " AND peb.deleted=0 AND lcip.deleted=0 AND lcip.is_frozen=0 " + "AND pe.deleted=0 AND pe.archived=0 AND phpe.portfolio_id=" + id;

    RawSql rawSql = RawSqlBuilder.parse(sql).create();

    Query<TotalAmount> query = Ebean.find(TotalAmount.class);

    Double totalAmount = query.setRawSql(rawSql).findUnique().totalAmount;

    if (totalAmount == null) {
        return 0.0;
    }

    return totalAmount;
}
 

import com.avaje.ebean.RawSql; //导入依赖的package包/类
@Override
public List<Meal> filterByDateTime(
    final User user,
    Integer fromDate,
    Integer toDate,
    Integer fromTime,
    Integer toTime) {
  String sql = String.format(
      "SELECT id, date, description, calories FROM meal WHERE EXTRACT(HOUR_MINUTE FROM date) " +
          "BETWEEN %d AND %d AND DATE(date) BETWEEN %d AND %d AND user_id=%d",
      fromTime, toTime, fromDate, toDate, user.id);
  RawSql rawSq = RawSqlBuilder
      .parse(sql)
      .create();
  return find
      .setRawSql(rawSq)
      .orderBy("date desc")
      .findList();
}
 

import com.avaje.ebean.RawSql; //导入依赖的package包/类
public List<RecipientModel> getRecipientsForCustomer(Long id) {
  String recipientsSql =
      "select distinct r.id, r.email " +
          " from pos_recipient r, pos_order o , POS_ORDER_RECIPIENTS pr, pos_customer c " +
          " where r.id = pr.recipient_id" +
          " and pr.order_id = o.id" +
          " and o.customer_id = c.id ";

  RawSql rawSql = RawSqlBuilder.parse(recipientsSql).
      columnMapping("r.id", "id")
      .columnMapping("r.email", "email")
      .create();

  Query<RecipientModel> query = Ebean.find(RecipientModel.class);
  query.setRawSql(rawSql)
      .where().eq("c.id", id);

  List<RecipientModel> recipients = query.findList();

  return recipients;

}
 

import com.avaje.ebean.RawSql; //导入依赖的package包/类
/**
 * Find best sale for amount.
 * 
 * @param shop
 *            the shop
 * @param amount
 *            the amount
 * @param stackSizes
 *            the stack sizes
 * @return the auction sale
 */
public AuctionSale findBestSaleForAmount(AuctionShop shop, int amount, List<Integer> stackSizes) {
	String amountCondition;

	if (amount > 0) {
		amountCondition = String.format("= %d", amount);
	} else {
		amountCondition = String.format("not in (%s)", StringUtils.join(stackSizes, ","));
	}

	String bestPriceReq = String
			.format("select bs.amount, min(unit_price) bestPrice from well_auction_sale bs inner join well_auction_sellerData bsd on bsd.id = bs.seller_data_id where bsd.shop_id=%d and bs.amount %s",
					shop.getId(), amountCondition);

	String sql = "from well_auction_sale s inner join (" + bestPriceReq + ") bpr on s.amount=bpr.amount and s.unit_price = bpr.bestPrice "
			+ "left outer join well_auction_sellerData sd on sd.id = s.seller_data_id  ";

	RawSql rawSql = AuctionSale.mapRawSql("s").mapJoin("sellerData", AuctionSellerData.mapRawSql("sd")).create(sql);

	return refreshSale(db.find(AuctionSale.class).setRawSql(rawSql).where(String.format("sd.shop_id=:shop and amount %s", amountCondition))
			.setParameter("shop", shop.getId()).order().asc("created").setMaxRows(1).findUnique());
}
 

import com.avaje.ebean.RawSql; //导入依赖的package包/类
private StockRecommend ExistNewest() {

		String str = String
				.format("select id, create_time, "
						+ " stock, open, close,"
						+ " income, current, average_income, "
						+ " num, total, up, down,"
						+ " is_closed, account_id, app_id "
						+ " from stock_recommend where (stock_recommend.open !=0 or (stock_recommend.open=0 and stock_recommend.is_closed = 1))"
						+ "	and stock_recommend.account_id = '%s' and stock_recommend.app_id = '%s'"
						+ " order by stock_recommend.create_time desc ",
						session("userId"), session("appId"));
		RawSql rawSql = RawSqlBuilder.parse(str).columnMapping("id", "id")
				.columnMapping("create_time", "createTime")
				.columnMapping("stock", "stock.id")
				.columnMapping("open", "open").columnMapping("close", "close")
				.columnMapping("income", "income")
				.columnMapping("current", "current")
				.columnMapping("average_income", "averageIncome")
				.columnMapping("num", "num").columnMapping("total", "total")
				.columnMapping("up", "up").columnMapping("down", "down")
				.columnMapping("is_closed", "isClosed")
				.columnMapping("account_id", "accountId.id")
				.columnMapping("app_id", "application.id").create();
		StockRecommend theOne = Ebean.find(StockRecommend.class)
				.setRawSql(rawSql).setFirstRow(0).setMaxRows(1).findUnique();

		return theOne;
	}
 

import com.avaje.ebean.RawSql; //导入依赖的package包/类
private List<StockRecommend> ExistList(String accountId) {

		String str = String
				.format("select id, create_time, "
						+ " stock, open, close,"
						+ " income, current, average_income, "
						+ " num, total, up, down,"
						+ " is_closed, account_id, app_id "
						+ " from stock_recommend where (stock_recommend.open !=0 or (stock_recommend.open=0 and stock_recommend.is_closed = 1))"
						+ "	and stock_recommend.account_id = '%s' and stock_recommend.app_id = '%s'"
						+ " order by stock_recommend.create_time desc ",
						accountId, session("appId"));
		RawSql rawSql = RawSqlBuilder.parse(str).columnMapping("id", "id")
				.columnMapping("create_time", "createTime")
				.columnMapping("stock", "stock.id")
				.columnMapping("open", "open").columnMapping("close", "close")
				.columnMapping("income", "income")
				.columnMapping("current", "current")
				.columnMapping("average_income", "averageIncome")
				.columnMapping("num", "num").columnMapping("total", "total")
				.columnMapping("up", "up").columnMapping("down", "down")
				.columnMapping("is_closed", "isClosed")
				.columnMapping("account_id", "accountId.id")
				.columnMapping("app_id", "application.id").create();
		List<StockRecommend> theOne = Ebean.find(StockRecommend.class)
				.setRawSql(rawSql).findList();

		return theOne;
	}
 

import com.avaje.ebean.RawSql; //导入依赖的package包/类
private List<CharmSortResult> getManList(PERIOD_TYPE periodType, int pageNumber, int sizePerPage){
	
	Date nowDate = new Date();		
	Date startDate = getStartDate(nowDate, periodType);
	
	
	SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
	String appId = session("appId");
	
	String sql = String.format(
			"select charm_value.receiver_id, name, head_image, search_id, sum(charm_value)sumValue, is_verified from charm_value"
					+ " join app_profile on app_profile.account_id = charm_value.receiver_id"
					+ " where charm_value.app_id = '%s' and app_profile.sex = 0 and charm_value.create_time >= '%s' and charm_value.create_time <= '%s'"
					+ " group by charm_value.receiver_id  order by if (isnull(head_image) or head_image = '', 1, 0), sumValue desc",
					appId, sdf.format(startDate), sdf.format(nowDate));

	RawSql rawSql = 
			RawSqlBuilder.parse(sql)
			.columnMapping("charm_value.receiver_id", "account_id")	
			.columnMapping("name", "name")
			.columnMapping("head_image", "head_image")
   			.columnMapping("search_id", "search_id")
			.columnMapping("sum(charm_value)sumValue", "sum_charm")
			.create();
	
	Query<CharmSortResult> query = Ebean.find(CharmSortResult.class);
	query.setRawSql(rawSql);
	
	List<CharmSortResult> list = 
			query
			.setFirstRow(pageNumber*sizePerPage)
			.setMaxRows(sizePerPage)				
			.findList();
	
	return list;
}
 

import com.avaje.ebean.RawSql; //导入依赖的package包/类
private List<CharmSortResult> getWomanList(PERIOD_TYPE periodType, int pageNumber, int sizePerPage){

  Date nowDate = new Date();		
  Date startDate = getStartDate(nowDate, periodType);


  SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
  String appId = session("appId");

  String sql = String.format(
		  "select charm_value.receiver_id, name, head_image, search_id, sum(charm_value)sumValue, is_verified from charm_value"
					+ " join app_profile on app_profile.account_id = charm_value.receiver_id"
					+ " where charm_value.app_id = '%s' and app_profile.sex = 1 and charm_value.create_time >= '%s' and charm_value.create_time <= '%s'"
					+ " group by charm_value.receiver_id  order by if (isnull(head_image) or head_image = '', 1, 0), sumValue desc",
					appId, sdf.format(startDate), sdf.format(nowDate));

  RawSql rawSql = 
		RawSqlBuilder.parse(sql)
		.columnMapping("charm_value.receiver_id", "account_id")	
		.columnMapping("name", "name")
		.columnMapping("head_image", "head_image")
		.columnMapping("search_id", "search_id")
		.columnMapping("sum(charm_value)sumValue", "sum_charm")
		.create();

  Query<CharmSortResult> query = Ebean.find(CharmSortResult.class);
  query.setRawSql(rawSql);

  List<CharmSortResult> list = 
		query
		.setFirstRow(pageNumber*sizePerPage)
		.setMaxRows(sizePerPage)				
		.findList();

  return list;
 }
 

import com.avaje.ebean.RawSql; //导入依赖的package包/类
private List<CharmSortResult> getRichList(PERIOD_TYPE periodType, int pageNumber, int sizePerPage){
	
	Date nowDate = new Date();		
	Date startDate = getStartDate(nowDate, periodType);
	
	
	SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
	String appId = session("appId");
	
	//use flower instead of money, please replace count(money_record.account_id) back to sum(money)
	String sql = String.format(
			"select money_record.account_id, name, head_image, search_id, count(money_record.account_id) sumValue, is_verified from money_record"
			+ " join app_profile on app_profile.account_id = money_record.account_id"
			+ " where money_record.app_id = '%s' and money_record.create_time >= '%s' and money_record.create_time <= '%s'"
			+ " group by money_record.account_id  order by if (isnull(head_image) or head_image = '', 1, 0), sumValue desc",
			appId, sdf.format(startDate), sdf.format(nowDate));

	RawSql rawSql = 
			RawSqlBuilder.parse(sql)
			.columnMapping("money_record.account_id", "account_id")	
			.columnMapping("name", "name")
			.columnMapping("head_image", "head_image")
   			.columnMapping("search_id", "search_id")
			.columnMapping("count(money_record.account_id)", "sum_charm")
			.create();
	
	Query<CharmSortResult> query = Ebean.find(CharmSortResult.class);
	query.setRawSql(rawSql);
	
	List<CharmSortResult> list = 
			query
			.setFirstRow(pageNumber*sizePerPage)
			.setMaxRows(sizePerPage)				
			.findList();
	
	return list;
}
 

import com.avaje.ebean.RawSql; //导入依赖的package包/类
public Result readNew(Integer pageNumber, Integer sizePerPage) {
	try {
		
		String sql = String.format(
				"select t1.account_id,t1.app_id from account t0 join app_profile t1 on t0.id = t1.account_id");
		
		RawSql rawSql = 
				RawSqlBuilder.parse(sql)
				.columnMapping("t1.account_id", "id.accountId")
				.columnMapping("t1.app_id","id.appId")
				.create();
		Query<AppProfile> query = Ebean.find(AppProfile.class);
		query.setRawSql(rawSql)
		        .where().eq("is_verified",1)
		        .setFirstRow(pageNumber*sizePerPage)
		        .setMaxRows(sizePerPage)
				.orderBy("create_time desc") 
				.findList();;
		
		List<AppProfile> newList = query.findList();
				
		// dao xu  
		
		return ok(Json.toJson(newList));
	}
	catch (Throwable e) {
		return status(ErrDefinition.E_PROGRAM_CONTENT_READ_FAILED, 
				Messages.get("programcontent.failure"));
	}
}
 

import com.avaje.ebean.RawSql; //导入依赖的package包/类
public Result read() {
	try {
		
		String sql = String.format(
				"select id, shop_id, user_id, create_time from shake_record" 
				+" join shop on shop.id = shop_id"
			    +" where shop.app_id = %s and user_id = %s",
			    session("appId"), session("userId"));

		RawSql rawSql = 
				RawSqlBuilder.parse(sql)
				.columnMapping("id", "id")
				.columnMapping("shop_id", "shop_id")
				.columnMapping("user_id", "user_id")
				.columnMapping("create_time", "create_time")
				.create();
		
		Query<ShakeRecord> query = Ebean.find(ShakeRecord.class);
		query.setRawSql(rawSql);
		
		List<ShakeRecord> shakeList = query.findList();
		
		return ok(Json.toJson(shakeList));			
	}
	catch (Throwable e) {
		return status(ErrDefinition.E_SHAKE_RECORD_READ_ERROR, 
				Messages.get("shakerecord.failure"));
	}
}