×
Namespaces

Variants
Actions

Symbian DBMS中的检索

From Nokia Developer Wiki
Jump to: navigation, search
Article Metadata

文章
beover1984 在 27 Jul 2007 创建
最后由 hamishwillee 在 15 Dec 2011 编辑

DBMS方面的应用中检索是必不可少的操作,通常我们习惯使用SQL来进行检索,就像下面这样:

//SQL keywords definitions
 
_LIT(KSelect, "SELECT ");
 
_LIT(KDelete, "DELETE " );
 
_LIT(KFrom, " FROM ");
 
_LIT(KWhere, " WHERE ");
 
_LIT(KEqual, " = ");
 
_LIT(KNotEqual, " <> ");
 
_LIT(KOrderBy, " ORDER BY ");
 
_LIT(KDot, ", ");
 
_LIT(KAnd, " AND " );
 
_LIT(KLike, " LIKE ");
 
_LIT(KQuotation, "'");
 
_LIT( KAsterisk, "*" );
 
 
 
// Implementation specific constants
 
const int KCustomSqlMaxLength = 256;
 
/**
 
* Function: GetColumnDataByKeyL
 
*
 
* Description: Get a value from appointed table and column by key.
 
*
 
* Returns: KErrNone or one of the system wide error codes.
 
*/
 
TInt COurDb::GetColumnDataByKeyL( const TDesC &aTable, const TDesC &aCol, const TDesC &aSortCol,
 
const TDesC &aKeyCol, const TInt aKey, TDes &aVal )
 
{
 
TBuf<KCustomSqlMaxLength> sqlStr;
 
sqlStr.Append( KSelect );
 
sqlStr.Append( aCol );
 
sqlStr.Append( KFrom );
 
sqlStr.Append( aTable );
 
sqlStr.Append( KWhere );
 
sqlStr.Append( aKeyCol );
 
sqlStr.Append( KEqual );
 
sqlStr.AppendNum( aKey );
 
sqlStr.Append( KOrderBy );
 
sqlStr.Append( aSortCol );
 
 
 
// Create a view on the database
 
RDbView view;
 
TInt ret( 0 );
 
ret = view.Prepare( iDb, TDbQuery( sqlStr ), view.EReadOnly );
 
if( ret != KErrNone )
 
{
 
view.Close();
 
return ret;
 
}
 
User::LeaveIfError( view.EvaluateAll() );
 
 
 
CDbColSet* colSet = view.ColSetL();
 
CleanupStack::PushL(colSet);
 
 
 
if( view.FirstL() )
 
{
 
ret = KErrNone;
 
view.GetL();
 
aVal.Copy( view.ColDes( colSet->ColNo( aCol ) ) );
 
}
 
else
 
{
 
ret = KErrNotFound;
 
}
 
 
 
CleanupStack::PopAndDestroy(colSet);
 
view.Close();
 
 
 
return ret;
 
}

上面的方法对程序员来说是最得心应手的,但由于需要组织一长串的SQL语句所以略显冗长,其实Symbian DBMS API提供了检索的功能,可以通过RDbTable:: SeekL()进行单一和复合的查询功能,应用RDbTable:: SeekL()上面的代码就可以变成:

/**
 
* Function: GetColumnDataByKeyL
 
*
 
* Description: Get a value from appointed table and column by key.
 
*
 
* Returns: KErrNone or one of the system wide error codes.
 
*/
 
TInt COurDb::GetColumnDataByKeyL( const TDesC &aTable, const TDesC &aCol, const TDesC &aIndex,
 
const TInt aKey, TDes &aVal )
 
{
 
// Create an read-only database table object
 
RDbTable table;
 
TInt ret = table.Open( iDb, aTable, table.EReadOnly );
 
 
 
if( ret != KErrNone )
 
{
 
return ret;
 
}
 
 
 
CDbColSet* colSet = table.ColSetL();
 
CleanupStack::PushL(colSet);
 
 
 
table.SetIndex( aIndex ); //
 
if( table.SeekL( TDbSeekKey( aKey ) ) )
 
{
 
table.GetL();
 
aVal .Copy( table.ColDes( colSet->ColNo( aCol ) ) );
 
ret = KErrNone;
 
}
 
else
 
{
 
ret = KErrNotFound;
 
}
 
 
 
CleanupStack::PopAndDestroy( colSet );
 
table.Close();
 
 
 
return ret;
 
}

使用RDbTable:: SeekL()我们需要为数据表设置当前使用的索引,然后通过构造一个TDbSeekKey查询对象传给RDbTable:: SeekL()进行实际的检索,这么简单就完成了检索,去掉了繁杂字符串组合操作.下面接着介绍一下通过RDbTable:: SeekL()实现复合查询的方法,可能有人看过TDbSeekKey的定义后会被TDbSeekKey::Add()这个方法迷惑,认为通过它为查询对象TDbSeekKey添加键值就行了,可不幸的是我们会在运行时得到一个panic.正确的方法是使用Symbian DBMS提供的另一个查询类TDbSeekMultiKey<TInt S>,它是一个瘦模板类,构造时传入复合查询时key的个数,然后通过Add方法把每个key添加进行就可以了:


    TDbSeekMultiKey<2> key;
 
key.Add( aKey1 );
 
key.Add( aKey2 );
 
table.SetIndex( aIndex ); //
 
if( table.SeekL( key ) )
 
{
 
table.GetL();
 
aVal .Copy( table.ColDes( colSet->ColNo( aCol ) ) );
 
ret = KErrNone;
 
}
 
else
 
{
 
ret = KErrNotFound;
 
}

有一点需要注意一下:就是在使用RDbTable:: SeekL()进行检索时,当前使用的索引必需包含每个key所在的列.

This page was last modified on 15 December 2011, at 10:09.
141 page views in the last 30 days.

Was this page helpful?

Your feedback about this content is important. Let us know what you think.

 

Thank you!

We appreciate your feedback.

×