Java Ebean.find方法代码示例

import com.avaje.ebean.Ebean; //导入方法依赖的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.Ebean; //导入方法依赖的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.Ebean; //导入方法依赖的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.Ebean; //导入方法依赖的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.Ebean; //导入方法依赖的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"));
	}
}
 

import com.avaje.ebean.Ebean; //导入方法依赖的package包/类
public Result readAll() {
	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",
			    session("appId"));

		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"));
	}
}
 

import com.avaje.ebean.Ebean; //导入方法依赖的package包/类
public Result queryResult(String contentId) {
	
	try {
		String sql = String.format(
				//"select t1.description description, count(*) number from activity_result t0 inner join activity_choice t1 on t0.choice_id = t1.id where t0.activity_content_id = '%s' group by t0.choice_id order by number desc;", contentId);
		        "select t1.description description, count(t0.choice_id) number from activity_choice t1 left join activity_result t0 on t0.choice_id = t1.id where t1.activity_content_id = '%s' group by t1.id order by number desc;", contentId);


		RawSql rawSql = 
				RawSqlBuilder.parse(sql)
				.columnMapping("t1.description", "name")
				.columnMapping("count(t0.choice_id)", "number")
				.create();
		
		Query<ResultGroup> query = Ebean.find(ResultGroup.class);
		
		query.setRawSql(rawSql);
		
		List<ResultGroup> list = query.findList();
		
		return ok(Json.toJson(list));			
	}
	catch (Throwable e) {
		return status(ErrDefinition.E_ACTIVITY_RESULT_READ_FAILED,
				Messages.get("activityresult.failure"));
	}
	
}
 

import com.avaje.ebean.Ebean; //导入方法依赖的package包/类
public Result getDetail(String couponId) {
    CouponDetail detail = null;
    
    if (null == couponId) {
        return ok();
    }
    
    String sql = String.format("select coupon.id, coupon.name, coupon.type, "
            + "coupon.available_time, coupon.expired_in_days, "
            + "coupon.code, shop.name, coupon_record.is_used from coupon_record "
            + "join shake_record on shake_record.user_id = coupon_record.user_id "
            + "join coupon on coupon.id = coupon_record.coupon_id "
            + "join shop on shake_record.shop_id = shop.id "
            + "where shake_record.create_time <= coupon_record.create_time "
            + "and coupon.id = '%s' "
            + "order by shake_record.create_time desc limit 1;", couponId);
    
    RawSql rawSql = 
            RawSqlBuilder.parse(sql)
            .columnMapping("coupon.id", "id") 
            .columnMapping("coupon.name", "value")
            .columnMapping("coupon.type", "type")
            .columnMapping("coupon.available_time", "available_time")
            .columnMapping("coupon.expired_in_days", "expired_in_days")
            .columnMapping("coupon.code", "code")
            .columnMapping("shop.name", "shop_name")
            .columnMapping("coupon_record.is_used", "is_used")                
            .create();
    
    Query<CouponDetail> query = Ebean.find(CouponDetail.class);
    query.setRawSql(rawSql);
    
    detail = query.findUnique();  
    
    if (null != detail) {
        detail.expired_date = new Date(detail.available_time.getTime() + (detail.expired_in_days - 1) * 1440L * 60000L);
        return ok(Json.toJson(detail));
    }
    
    return ok();        
}
 

import com.avaje.ebean.Ebean; //导入方法依赖的package包/类
public Result getDrawList() {
    DynamicForm form = Form.form().bindFromRequest();
    
    String userId = form.get("userId");
    String appId = form.get("appId");
    
    Logger.info("userId1: " + userId);
    Logger.info("appId1:" + appId);
    
    List<CouponMark> couponList = new ArrayList<CouponMark>();
    
    if (userId == null || appId == null) {
        return ok(Json.toJson(couponList));
    }
    
    String sql = String.format(
            "select coupon.id, coupon.name, coupon.type, coupon.available_time, "
            + "coupon.expired_in_days, coupon.code, coupon_record.is_used from coupon "                
            + "join coupon_record on coupon_record.coupon_id = coupon.id "
            + "where coupon.app_id = '%s' and user_id = '%s';",
            appId, userId);
    
    Logger.info("sql:" + sql);
    RawSql rawSql = 
            RawSqlBuilder.parse(sql)
            .columnMapping("coupon.id", "id") 
            .columnMapping("coupon.name", "value")
            .columnMapping("coupon.type", "type")
            .columnMapping("coupon.available_time", "available_time")
            .columnMapping("coupon.expired_in_days", "expired_in_days")
            .columnMapping("coupon.code", "code")
            .columnMapping("coupon_record.is_used", "is_used")
            .create();
    
    
    Logger.info("15");
    Query<CouponMark> query = Ebean.find(CouponMark.class);
    query.setRawSql(rawSql);
    
    couponList = query.findList();     
    
    if (null != couponList) {
        for (CouponMark coupon : couponList) {
            coupon.expired_time = new Date(coupon.available_time.getTime() + (coupon.expired_in_days - 1) * 1440L * 60000L);
        }
    }
    return ok(Json.toJson(couponList));
}
 

import com.avaje.ebean.Ebean; //导入方法依赖的package包/类
public Result readMe(String id) {
	try {
		if (id==null) {
			id=session("userId");
		}			
		Vote vote=Vote.find.where()
				.eq("account_id", id)
				.findUnique();
		
		String sql = String.format("SELECT account_id,count(*) from (SELECT * FROM vote ORDER BY vote_total DESC) "
				+ "as a where vote_total > (SELECT vote_total from vote where account_id = '%s');",id);
		
		RawSql rawSql=
				RawSqlBuilder.parse(sql)
				.columnMapping("account_id", "account_id")
				.columnMapping("count(*)", "value")
				.create();
		Query<Vote> query = Ebean.find(Vote.class);
		query.setRawSql(rawSql);
		Vote voteme = query.findUnique();
		if (voteme==null) {
			selfstatus.put("idx", 1);
		}else {
			selfstatus.put("idx", voteme.value+1);
		}
		if (vote==null) {
			selfstatus.put("status", 5);
			return ok(Json.toJson(selfstatus));
		}
		else {
			selfstatus.put("status", 2);
			selfstatus.put("vote", Json.toJson(vote));
			
			return ok(Json.toJson(selfstatus));
		}
		
	} catch (Throwable e) {
		return status(ErrDefinition.E_VOTEREAD_ERROR, 
				Messages.get("readerror.failure"));
	}
}
 

import com.avaje.ebean.Ebean; //导入方法依赖的package包/类
private List<CharmSortResult> getNewbieList(PERIOD_TYPE periodType, int pageNumber, int sizePerPage){
	
	Calendar newbieCalendar = Calendar.getInstance();
	newbieCalendar.setTime(new Date());
	 newbieCalendar.add(Calendar.DATE, -30);
	 Date newbieDate = newbieCalendar.getTime();
	
	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"
					+ " join account on account.id = charm_value.receiver_id"
					+ " where charm_value.app_id = '%s' and charm_value.create_time >= '%s' and charm_value.create_time <= '%s' and  account.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), sdf.format(newbieDate));

	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.Ebean; //导入方法依赖的package包/类
public Result isVoted(String contentId) {
	String userId = session("userId");
	
	try {
		//find the repeatable in activity
		String sql = String.format(
				"select t0.id from activity t0 join activity_content t1 on t0.id = t1.activity_id  where t1.id = '%s'", contentId, userId);
		RawSql rawSql = 
				RawSqlBuilder.parse(sql)
				.columnMapping("t0.id", "id")
				.create();
		Query<Activity> query = Ebean.find(Activity.class);
		query.setRawSql(rawSql);
		
		Activity activity = query.findUnique();
					
		int count = 0;
		
		if (!activity.repeatable) {
			count = ActivityResult.find.where()
				.eq("account_id", userId)
				.eq("activity_content_id", contentId)
				.findRowCount();				
		}
		else {
			Calendar today = Calendar.getInstance();
			today.set(Calendar.HOUR_OF_DAY, 0);
			today.set(Calendar.MINUTE, 0);
			today.set(Calendar.SECOND, 0);
			today.set(Calendar.MILLISECOND, 0);
			
			Calendar tomorrow = (Calendar) today.clone();
			tomorrow.add(Calendar.DATE, 1);
			count = ActivityResult.find.where()
				.eq("account_id", userId)
				.eq("activity_content_id", contentId)
				.between("create_time", today.getTime(), tomorrow.getTime())
				.findRowCount();
		}
		
		ObjectNode node = Json.newObject();
		node.put("contentId", contentId);
		node.put("result", count != 0 ? "true" : "false");
		
		return ok(node);
	}
	catch (Throwable e) {
		return status(ErrDefinition.E_ACTIVITY_RESULT_READ_FAILED,
				Messages.get("activityresult.failure"));
	}		
}