This is a cache of http://dbflute.seasar.org/manual/function/ormapper/conditionbean/hitomecb.html. It is a snapshot of the page at 2024-11-13T00:49:23.410+0000.
ConditionBean at a Glance | DBFlute

ConditionBean at a Glance

to Japanese page

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.