ConditionBean at a Glance

• ConditionBean Functions
  • Retrieving Data
  • Filtering Data
  • Sorting Data
  • Search types
  • Other options
• Special Thanks

ConditionBean Functions

Retrieving Data

Retrieving Data @Java
MemberCB cb = new MemberCB();

// <<< SetupSelect(Relation), SpecifyColumn >>>
cb.setupSelect_MemberStatus(); // retrieving tables via one-to-many relationship
cb.specify().specifyMemberStatus().columnMemberStatusName(); // retrieving a specific column
cb.setupSelect_MemberSecurityAsOne(); // retrieving tables via one-to-one
cb.setupSelect_MemberServiceAsOne().withServiceRank(); // retrieving nested tables
cb.setupSelect_MemberAddress(currentDate); // retrieving tables that are practically one-to-one

// <<< (Specify)DerivedReferrer >>>
// Retrieving derived columns from child tables (correlated subqueries)
//  e.g. latest login time via PC
cb.specify().derivedMemberLoginList().max(new SubQuery<MemberLoginCB>() {
    public void query(MemberLoginCB subCB) {
        subCB.specify().columnLoginDatetime();
        subCB.query().setMobileLoginFlg_Equal_False();
    }
}, Member.ALIAS_latestLoginDatetime); // press ctrl+1 to autocomplete if you have EMecha installed
// you can make adjustments using SQL functions when necessary
//}, Member.ALI..., new DerivedReferrerOption().coalesce("1192-01-01"));

// <<< LoadReferrer >>> ※after a SELECT clause
// Retrieving data from child tables (one-to-many)
//  e.g. Retrieving the purchase data, in descending order, of each member (Load)
ListResultBean<Member> memberList = memberBhv.selectList(cb); // List Search
memberBhv.loadPurchaseList(memberList, new ConditionBeanSetupper<Pu...CB>() {
    public void setup(PurchaseCB cb) {
        cb.query().addOrderBy_PurchaseDatetime_Desc();
    }
});
// Use LoadReferrerOption to retrieve a child table of a child table
//LoadReferrerOption<MemberCB, Member> option
//        = new LoadReferrerOption<MemberCB, Member>();
//option.setConditionBeanSetupper(...);
//option.setEntityListSetupper(...);
//memberBhv.loadPurchaseList(memberList, option);

...

Filtering Data

Filtering Data @Java
...

// Equalities
cb.query().setMemberId_Equal(1); // MEMBER_ID = 1
cb.query().setMemberStatusCode_Equal_FormalizedMember(); // using category tables

// Inequalities
cb.query().setMemberId_NotEqual(1); // MEMBER_ID <> 1
cb.query().setMemberStatusCode_NotEqual_FormalizedMember(); // using category tables
cb.query().setMemberId_GreaterThan(3); // MEMBER_ID > 3
cb.query().setMemberId_LessThan(3); // MEMBER_ID < 3
cb.query().setMemberId_GreaterEqual(3); // MEMBER_ID >= 3
cb.query().setMemberId_LessEqual(3); // MEMBER_ID <= 3

// IN lists (in ('a', 'b'))
cb.query().setMemberId_InScope(memberIdList);
cb.query().setMemberStatusCode_InScope_AsMemberStatus(cdefList); // using category tables
// NOT IN (...)
// cb.query().setMemberId_NotInScope(...)

// Fuzzy searches (with escape key)
cb.query().setMemberName_PrefixSearch("S"); // begins-with search (LIKE 'S%' ESCAPE '|')
cb.query().setMemberName_LikeSearch("vi"
    , new LikeSearchOption().likeContain()); // this-and-that options
cb.query().setMemberName_LikeSearch("to vi"
    , new LikeSearchOption().likeContain().splitByBlank());// contains "to" and "vi"
cb.query().setMemberName_LikeSearch("to vi"
    , new LikeSearchOption().likeContain().splitByBlank().asOrSplit());// contains "to" or "vi"
// NOT LIKE '...'
cb.query().setMemberName_NotLikeSearch(...)

// Null checks, etc.
cb.query().setBirthdate_IsNull();
cb.query().setBirthdate_IsNotNull();
cb.query().setMemo_IsNullOrEmpty();

// The standard FromTo method (for dates)
//  e.g. from October to December (all-inclusive)
Date fromMonth = new HandyDate("2005/10/01").getDate();
Date toMonth = new HandyDate("2005/12/01").getDate();
cb.query().setFormalizedDatetime_FromTo(fromMonth, toMonth
        , new FromToOption().compareAsMonth()); // this-and-that options

// Using the OrIsNull option
//  e.g. until 2005 (all-inclusive)
Date toYear = new HandyDate("2005/01/01").getDate();
cb.query().setFormalizedDatetime_FromTo(null, toYear
        , new FromToOption().compareAsYear().orIsNull());

// The frequently-used DateFromTo method
//  e.g. from 10/1 to 10/3 (all-inclusive) ※equivalent to the compareAsDate() option
Date fromDate = new HandyDate("2005/10/01").getDate();
Date toDate = new HandyDate("2005/10/03").getDate();
cb.query().setFormalizedDatetime_DateFromTo(fromDate, toDate);

// The standard RangeOf method (for numbers)
//  e.g. from 30 to 70 (or null)
cb.query().setMemberAge_RangeOf(30, 70, new RangeOfOption().orIsNull());

// <<< ExistsReferrer >>>
// Filtering using child tables (EXISTS (SELECT ...))
//  e.g. members with purchases of ¥2000 and above
cb.query().existsPurchaseList(new SubQuery<PurchaseCB>() {
    public void query(PurchaseCB subCB) {
        subCB.query().setPurchasePrice_GreaterEqual(2000);
    }
});
// NOT EXISTS (SELECT...))
// cb.query().notExistsPurchaseList(...)

// <<< InScopeRelation >>>
// Filtering using IN lists (IN (SELECT ...))
//  e.g. members with purchases of ¥2000 and above
cb.query().inScopePurchaseList(new SubQuery<PurchaseCB>() {
    public void query(PurchaseCB subCB) {
        subCB.query().setPurchasePrice_GreaterEqual(2000);
    }
});
// NOT IN (SELECT ...)
// cb.query().notInScopePurchaseList(...)

// <<< (Query)DerivedReferrer >>>
// Filtering using derived columns of child tables (correlated subqueries)
//  e.g. members with paid purchases, the maximum purchase price of which is ¥2000 and above
cb.query().derivedPurchaseList().max(new SubQuery<PurchaseCB>() {
    public void query(PurchaseCB subCB) {
        subCB.specify().columnPurchasePrice();
        subCB.query().setPaymentCompleteFlg_Equal_True();
    }
}).greaterEqual(2000);
// you can make adjustments to derived columns using SQL functions when necessary
//}, new DerivedReferrerOption().coalesce(0)).greaterEqual(...);

// <<< ScalarCondition >>>
// Filtering using derived columns (normal subqueries)
//  e.g. members with the same birthdate as the youngest formalized member
cb.query().scalar_Equal().max(new SubQuery<MemberCB>() {
    public void query(MemberCB subCB) {
        subCB.specify().columnBirthdate();
        subCB.query().setMemberStatusCode_Equal_Formalized();
    }
});

// <<< ScalarConditionPartitionBy >>>
// Filtering using derived columns and categories (correlated subqueries)
//  e.g. the youngest member per member status
cb.query().scalar_Equal().max(new SubQuery<MemberCB>() {
    public void query(MemberCB subCB) {
        subCB.specify().columnBirthdate();
    }
}).partitionBy(new SpecifyQuery<MemberCB>() {
    public void specify(MemberCB cb) {
        cb.specify().columnMemberStatusCode();
    }
});

// <<< MyselfInScope >>>
// Filtering using IN lists with values from the same table (used only under special conditions)
cb.query().myselfInScope(new SubQuery<MemberCB>() {
    public void query(MemberCB subCB) {
        //subCB.specify().column...() // defaults to the PK column when not specified
        subCB.query().setMemberStatusCode_Equal_Formalized(); // the filter condition
    }
});

// <<< ColumnQuery >>>
// Filtering using the values of two columns from the same table
//  e.g. members whose birthdates are earlier than their formalization dates
cb.columnQuery(new SpecifyQuery<MemberCB>() {
    public void specify(MemberCB cb) {
        cb.specify().columnBirthdate();
    }
}).lessThan(new SpecifyQuery<MemberCB>() {
    public void specify(MemberCB cb) {
        cb.specify().columnFormalizedDatetime();
    }
});
// you can make adjustments to the values using SQL functions when necesary
//}).convert(new ColumnConversionOption().truncTime().addDay(7));

// <<< OrScopeQuery >>>
// The OR operator (conditions are joined by AND by default)
//  e.g. members whose names start with "S" or "J", or whose MEMBER_ID is equal to 3
cb.orScopeQuery(new SubQuery<MemberCB>() {
    public void query(MemberCB orCB) {
        orCB.query().setMemberName_PrefixSearch("S");
        orCB.query().setMemberName_PrefixSearch("J");
        orCB.query().setMemberId_Equal(3);
    }
});

// <<< OrScopeQueryAndPart >>>
// Using AND operators within OR operators
//  e.g. members who have withdrawn or whose MEMBER_ID is greater than or equal to 100 and with null formalized dates
cb.orScopeQuery(new SubQuery<MemberCB>() {
    public void query(MemberCB orCB) {
        orCB.query().setMemberStatusCode_Equal_Withdrawal();
        orCB.orScopeQueryAndPart(new AndQuery<MemberCB>() {
            public void query(MemberCB andCB) {
                andCB.query().setMemberId_GreaterEqual(100);
                andCB.query().setFormalizedDatetime_IsNull();
            }
        };
    }
});

// <<< UnionQuery >>>
// Using the UNION clause
//  e.g. searching for members whose birthdates are on or after 2005 using UNION
cb.union(new UnionQuery<MemberCB>() {
    public void query(MemberCB unionCB) {
        Date targetDate = new HandyDate("2005/01/01").getDate();
        unionCB.query().setBirthdate_GreaterEqual(targetDate);
    }
});

...

Sorting Data

Sorting Data @Java
...

cb.query().addOrderBy_MemberId_Asc(); // ascending sort
cb.query().addOrderBy_FormalizedDatetime_Desc(); // descending sort
cb.query().addOrderBy_Birthdate_Desc().withNullsFirst(); // with null values first
cb.query().addOrderBy_Birthdate_Desc().withNullsLast(); // with null values last

// <<< ManualOrder >>>
// Sorting manually using conditions
//  e.g. putting data modified within the last 24 hours first
ManualOrderBean mob = new ManualOrderBean();
Date date24before = new HandyDate(currentDate()).addDay(-1).getDate();
mob.when_GreaterThan(date24before); // date modified > 24 hours ago
cb.query().addOrderBy_UpdateDatetime_Asc().withManualOrder(mob);

// <<< SpecifiedDerivedOrderBy >>>
// Sorting using derived columns
//  e.g. sorting by last login
cb.query().addSpecifiedDerivedOrderBy_Desc(Member.ALIAS_latestLoginDatetime);

...

Search types

Search types @Java
...

// Count
int count = memberBhv.selectCount(cb);

// Item search (returns null if not found)
Member member = memberBhv.selectEntity(cb);

// Item search (throws an Exception if not found)
Member member =  = memberBhv.selectEntityWithDeletedCheck(cb);

// List search
ListResultBean<Member> memberList = memberBhv.selectList(cb);

// Paging
PagingResultBean<Member> page = memberBhv.selectPage(cb);

// <<< ScalarSelect >>>
// Using derived values
//  e.g. the youngest formalized member
Date max = memberBhv.scalarSelect(Date.class).max(new ScalarQuery<Me...CB>() {
    public void query(MemberCB cb) {
        cb.specify().columnBirthdate(); // latest birthdate
        cb.query().setMemberStatusCode_Equal_Formalized(); // search only for formalized members
    }
});

// <<< CursorSelect >>>
// Using cursors (fetch one record at a time)
memberBhv.selectCursor(cb, new EntityRowHandler<Member>() {
    public void handle(Member entity) {
        Integer memberId = entity.getMemberId();
        String memberName = entity.getMemberName();
        ...
    }
});

// <<< QueryDelete >>>
memberBhv.queryDelete(cb);

// <<< QueryUpdate >>>
Member member = new Member();
member.setMemberStatusCode_ProvisionalMember();
memberBhv.queryUpdate(member, cb);

...

Other options

Other options @Java
...

// throw an exception if a condition value is null or an empty String (ignored by default)
cb.checkInvalidQuery();

// acquire a table lock (for updates)
cb.lockForUpdate();

...

Special Thanks

Cruz, thank you for your translation.

SiteMap Author's BlogBTS