sqlite3的C语言API操作数据库的实例分析
发布时间：2020-12-12 19:59:41 所属栏目：百科 来源：网络整理
导读：每一种数据库提供给程序员使用的API都有其特点，根据其设计的机制不同有很大的差别，对于SQLITE3，其API的使用还是很方便很简单的。 一.sqlite3提供的一些主要的API 在前一篇博文sqlite3的c/c++接口中对其原理有了介绍，主要使用的API一般有： （1）打开数据
|
每一种数据库提供给程序员使用的API都有其特点，根据其设计的机制不同有很大的差别，对于SQLITE3，其API的使用还是很方便很简单的。 一.sqlite3提供的一些主要的API 在前一篇博文sqlite3的c/c++接口中对其原理有了介绍，主要使用的API一般有： （1）打开数据库 如果此文件不存在，那么就直接创建它，存在的话就直接打开。 （2）关闭数据库 int sqlite3_close( sqlite3 * ) ;
因为在前一篇中已经提到了关于所有可能用到的API，那么下面我们直接用一个实例来进行分析。
二.实例 这个实例包括：插入，删除，查找，修改。 （1）插入 //======================================================================
//
// 函数功能：向表中插入数据
//
// 函数参数：
//
// 返回值：插入失败返回0，插入成功返回1
//
//
//====================================================================
int Insert( char * szFilePath,char * szTableName,void * pData,int nOpMode )
{
sqlite3 * conn = NULL ;
int nRet ;
const char * beginSQL = "BEGIN TRANSACTION" ;
const char * commitSQL = "COMMIT" ;
char insertSqlCommand[100] ;
// 打开数据库文件
nRet = sqlite3_open( szFilePath,&conn ) ;
if( nRet != SQLITE_OK )
{
printf("加载数据库文件失败!n") ;
sqlite3_close( conn ) ;
return 0 ;
}
// 显式开启一个事物
sqlite3_stmt * stmt = NULL ;
if( SQLITE_OK != sqlite3_prepare_v2( conn,beginSQL,strlen( beginSQL ),&stmt,NULL ) )
{
if( stmt )
{
sqlite3_finalize( stmt ) ;
}
sqlite3_close( conn ) ;
return 0 ;
}
if( sqlite3_step( stmt ) != SQLITE_DONE )
{
sqlite3_finalize( stmt ) ;
sqlite3_close( conn ) ;
return 0 ;
}
// 构建基于绑定变量的插入数据
if( INSERT_BOOK == nOpMode )
{
strcpy( insertSqlCommand,"insert into Book values ( ?,?,? )" ) ;
}
else if( INSERT_CUSTOMER == nOpMode )
{
strcpy( insertSqlCommand,"insert into Customer values( ?,?)" ) ;
}
else if( INSERT_ORDER == nOpMode )
{
strcpy( insertSqlCommand,"insert into OrderInfo values( ?,? )" ) ;
}
else
{
return 0 ;
}
sqlite3_stmt * stmt2 = NULL ;
if( SQLITE_OK != sqlite3_prepare_v2( conn,insertSqlCommand,strlen( insertSqlCommand ),&stmt2,NULL ) )
{
if( stmt2 )
{
sqlite3_finalize(stmt2 );
sqlite3_close( conn ) ;
return 0 ;
}
}
if( INSERT_BOOK == nOpMode )
{
sqlite3_bind_text( stmt2,1,((Book*)pData)->id,strlen( ((Book*)pData)->id),SQLITE_TRANSIENT ) ;
sqlite3_bind_text( stmt2,2,((Book*)pData)->name,strlen( ((Book*)pData)->name ),3,((Book*)pData)->type,strlen( ((Book*)pData)->type ),SQLITE_TRANSIENT ) ;
sqlite3_bind_double( stmt2,4,((Book*)pData)->price ) ;
sqlite3_bind_text( stmt2,5,((Book*)pData)->content,strlen( (( Book*)pData)->content ),SQLITE_TRANSIENT ) ;
}
else if( INSERT_CUSTOMER == nOpMode )
{
sqlite3_bind_text( stmt2,(( Customer*)pData)->id,strlen( (( Customer*)pData)->id ),(( Customer*)pData)->name,strlen( (( Customer*)pData)->name ),(( Customer*)pData)->sex,strlen( (( Customer*)pData)->sex ),SQLITE_TRANSIENT ) ;
sqlite3_bind_int( stmt2,(( Customer*)pData)->age ) ;
sqlite3_bind_text( stmt2,(( Customer*)pData)->addr,strlen( (( Customer*)pData)->addr ),SQLITE_TRANSIENT ) ;
}
else if( INSERT_ORDER == nOpMode )
{
sqlite3_bind_text( stmt2,( ( OrderInfo*)pData)->orderId,strlen( ( (OrderInfo*)pData)->orderId ),( ( OrderInfo*)pData)->buyerId,strlen( ( (OrderInfo*)pData)->buyerId ),( ( OrderInfo*)pData)->bookId,strlen( ( (OrderInfo*)pData)->bookId ),( ( OrderInfo*)pData)->buyType,strlen( ( (OrderInfo*)pData)->buyType ),SQLITE_TRANSIENT ) ;
}
else
{
return 0 ;
}
if( SQLITE_DONE != sqlite3_step( stmt2 ) )
{
sqlite3_finalize( stmt2 ) ;
sqlite3_close( conn ) ;
return 0 ;
}
// 重新初始化该sqlite3_stmt绑定的对象
sqlite3_reset( stmt2 ) ;
sqlite3_finalize( stmt2 ) ;
// 提交之前的事物
sqlite3_stmt * stmt3 = NULL ;
if( SQLITE_OK != sqlite3_prepare_v2( conn,commitSQL,strlen( commitSQL ),&stmt3,NULL ) )
{
sqlite3_finalize( stmt3 ) ;
sqlite3_close( conn ) ;
return 0 ;
}
if( sqlite3_step( stmt3 ) != SQLITE_DONE )
{
if( stmt3 )
{
sqlite3_finalize( stmt3 ) ;
}
sqlite3_close( conn ) ;
return 0 ;
}
// 关闭数据库
sqlite3_close( conn ) ;
return 1 ;
}
（2）删除 //=================================================================
//
// 函数说明：
//
// 返回：删除成功返回1，删除失败返回0
//
//
//
//================================================================
int Delete( char * szFileName,char * szKeyword,int nOpMode )
{
sqlite3 * conn = NULL ;
sqlite3_stmt * stmt = NULL ;
int nRet ;
char szSqlCommand[100] ;
nRet = sqlite3_open( szFileName,&conn ) ;
if( nRet != SQLITE_OK )
{
sqlite3_close( conn ) ;
return 0 ;
}
if( nOpMode == DELETE_BOOK_BY_BOOKID )
{
sprintf( szSqlCommand,"delete from Book where id = %s ;",szKeyword ) ;
}
else if( nOpMode == DELETE_BOOK_BY_BOOKNAME )
{
sprintf( szSqlCommand,"delete from Book where name = %s ;",szKeyword ) ;
}
else if( nOpMode == DELETE_BOOK_ALL_ITEMS )
{
sprintf( szSqlCommand,"delete from Book ; " ) ;
}
else if( nOpMode == DELETE_CUSTOMER_BY_ID )
{
sprintf( szSqlCommand,"delete from Customer where id = %s ;",szKeyword ) ;
}
else if( nOpMode == DELETE_CUSTOMER_BY_NAME )
{
sprintf( szSqlCommand,"delete from Customer where name = %s ;",szKeyword ) ;
}
else if( nOpMode == DELETE_CUSTOMER_ALL_ITEMS )
{
sprintf( szSqlCommand,"delete from Customer ;") ;
}
else if( nOpMode == DELETE_ORDER_BY_ORDERID )
{
sprintf( szSqlCommand,"delete from OrderInfo where orderid = %s ;",szKeyword ) ;
}
else if( nOpMode == DELETE_ORDER_BY_BOOKID )
{
sprintf( szSqlCommand,"delete from OrderInfo where bookid = %s ;",szKeyword ) ;
}
else if( nOpMode == DELETE_ORDER_BY_CUSTOMERID )
{
sprintf( szSqlCommand,"delete from OrderInfo where customerid = %s ;",szKeyword ) ;
}
else if( nOpMode == DELETE_ORDER_ALL_ITEMS )
{
sprintf( szSqlCommand,"delete from orderInfo ;" ) ;
}
else
{
return 0 ;
}
if( SQLITE_OK != sqlite3_prepare_v2( conn,szSqlCommand,strlen( szSqlCommand ),& stmt,NULL ) )
{
if( stmt )
{
sqlite3_finalize( stmt ) ;
}
sqlite3_close( conn ) ;
return 0 ;
}
if( sqlite3_step( stmt ) != SQLITE_DONE )
{
if( stmt )
{
sqlite3_finalize( stmt ) ;
}
sqlite3_close( conn ) ;
return 0 ;
}
// 关闭数据库
sqlite3_close( conn ) ;
return 1 ;
}
（3）查找 //=============================================================
//
// 函数说明：
//
// 返回值:返回1说明查找成功，返回0说明查找失败
//
//
//
//============================================================
int Query( char * szFilePath,int nOpMode,int & nCount )
{
sqlite3 * conn = NULL ;
int nRet = 0 ;
sqlite3_stmt * stmt1 = NULL ;
char szQueryCommand[100] ;
if( QUERY_BOOK_BY_BOOKID == nOpMode )
{
sprintf( szQueryCommand,"select * from Book where id = %s ;",szKeyword ) ;
}
else if( QUERY_BOOK_BY_BOOKNAME == nOpMode )
{
sprintf( szQueryCommand,"select * from Book where name = %s ;",szKeyword ) ;
}
else if( QUERY_CUSTOMER_BY_ID == nOpMode )
{
sprintf( szQueryCommand,"select * from Customer where id = %s ;",szKeyword ) ;
}
else if( QUERY_CUSTOMER_BY_NAME == nOpMode )
{
sprintf( szQueryCommand,"select * from Customer where name = %s ;",szKeyword ) ;
}
else if( QUERY_ORDER_ORDERID == nOpMode )
{
sprintf( szQueryCommand,"select * from OrderInfo where orderid = %s ;",szKeyword ) ;
}
else if( QUERY_ORDER_CUSTOMERID == nOpMode )
{
sprintf( szQueryCommand,"select * from OrderInfo where customerid = %s ;",szKeyword ) ;
}
else if( QUERY_ORDER_BOOKID == nOpMode )
{
sprintf( szQueryCommand,"select * from OrderInfo where bookid = %s ;",szKeyword ) ;
}
else if( QUERY_BOOK_ALL_INFO == nOpMode )
{
sprintf( szQueryCommand,"select * from Book ;") ;
}
else if( QUERY_CUSTOMER_ALL_INFO == nOpMode )
{
sprintf( szQueryCommand,"select * from Customer ;") ;
}
else if( QUERY_ORDER_ALL_INFO == nOpMode )
{
sprintf( szQueryCommand,"select * from OrderInfo ;") ;
}
else if( QUERY_USERS_BY_USERNAME == nOpMode )
{
sprintf( szQueryCommand,"select * from UserStatus where name = %s ;",szKeyword ) ;
}
else
{
return 0 ;
}
nRet = sqlite3_open( szFilePath,&conn ) ;
if( nRet != SQLITE_OK )
{
sqlite3_close( conn ) ;
return 0 ;
}
char **dbResult ;
int nRow ;
int nColumn ;
char *errorMsg = NULL ;
int index ;
nCount = 0 ;
nRet = sqlite3_get_table( conn,szQueryCommand,&dbResult,&nRow,&nColumn,&errorMsg ) ;
if( nRet == SQLITE_OK )
{
index = nColumn ;
if( nRow == 0 )
{
return 1 ;
}
if( QUERY_BOOK_ALL_INFO == nOpMode || QUERY_BOOK_BY_BOOKID == nOpMode|| QUERY_BOOK_BY_BOOKNAME == nOpMode )
{
for ( int j = 0 ; j < nRow ; j++ )
{
for( int i = 0 ; i < nColumn ; i++ )
{
if( 0 == ( i % 5 ) )
{
strcpy( ((Book*)pData)[nCount].id,dbResult[index++] ) ;
}
if( 1 == ( i % 5 ) )
{
strcpy( ((Book*)pData)[nCount].name,dbResult[index++] ) ;
}
if( 2 == ( i % 5 ) )
{
strcpy( ((Book*)pData)[nCount].type,dbResult[index++] ) ;
}
if( 3 == ( i % 5 ) )
{
double tmp = atof( dbResult[index++] ) ;
((Book*)pData)[nCount].price = tmp ;
}
if( 4 == ( i % 5 ) )
{
strcpy( ((Book*)pData)[nCount].content,dbResult[index++] ) ;
nCount++ ;
}
}
}
}
else if( QUERY_CUSTOMER_ALL_INFO == nOpMode || QUERY_CUSTOMER_BY_ID == nOpMode || QUERY_CUSTOMER_BY_NAME == nOpMode )
{
for( int j = 0 ; j < nRow ; j++ )
{
for( int i = 0 ; i < nColumn ; i++ )
{
if( 0 == ( i % 5 ) )
{
strcpy( ( ( Customer*)pData)[nCount].id,dbResult[index++] ) ;
}
if( 1 == ( i % 5 ) )
{
strcpy( ( ( Customer*)pData)[nCount].name,dbResult[index++] ) ;
}
if( 2 == ( i % 5 ) )
{
strcpy( ( ( Customer*)pData)[nCount].sex,dbResult[index++] ) ;
}
if( 3 == ( i % 5 ) )
{
int tmp = atoi( dbResult[index++] ) ;
(( Customer*)pData)[nCount].age = tmp ;
}
if( 4 == ( i % 5 ) )
{
strcpy( ( ( Customer*)pData)[nCount].addr,dbResult[index++] ) ;
nCount++ ;
}
}
}
}
else if( QUERY_ORDER_ALL_INFO == nOpMode || QUERY_ORDER_ORDERID == nOpMode || QUERY_ORDER_CUSTOMERID == nOpMode
|| QUERY_ORDER_BOOKID == nOpMode )
{
for( int j = 0 ; j < nRow ; j++ )
{
for( int i = 0 ; i < nColumn ; i++ )
{
if( 0 == ( i % 5 ) )
{
strcpy( (( OrderInfo * )pData)[nCount].orderId,dbResult[index++] ) ;
}
if( 1 == ( i % 5 ) )
{
strcpy( (( OrderInfo *)pData )[nCount].buyerId,dbResult[index++] ) ;
}
if( 2 == ( i % 5 ) )
{
strcpy( (( OrderInfo *)pData)[nCount].bookId,dbResult[index++] ) ;
}
if( 3 == ( i % 5 ) )
{
strcpy( (( OrderInfo *)pData)[nCount].buyType,dbResult[index++] ) ;
nCount++ ;
}
}
}
}
else if( QUERY_USERS_BY_USERNAME == nOpMode )
{
for( int j = 0 ; j < nRow ; j++ )
{
for( int i = 0 ; i < nColumn ; i++ )
{
if( 0 == ( i % 5 ) )
{
strcpy( (( UserStatus*)pData)[nCount].userId,dbResult[index++] ) ;
}
if( 1 == ( i % 5 ) )
{
strcpy( (( UserStatus*)pData)[nCount].userName,dbResult[index++] ) ;
}
if( 2 == ( i % 5 ) )
{
strcpy( (( UserStatus*)pData)[nCount].password,dbResult[index++] ) ;
}
if( 3 == ( i % 5 ) )
{
strcpy( (( UserStatus*)pData)[nCount].loginType,dbResult[index++] ) ;
nCount ++ ;
}
}
}
}
else
{
return 0 ;
}
}
// 释放数据
sqlite3_free_table( dbResult ) ;
// 关闭数据库
sqlite3_close( conn ) ;
return 1 ;
}
（4）修改 //=======================================================
//
// 函数说明：
//
// 返回值：修改成功返回1，修改失败返回0
//
//
//
//======================================================
int Modify( char * szFileName,int nOpMode )
{
sqlite3 * conn = NULL ;
sqlite3_stmt * stmt = NULL ;
int nRet = 0 ;
char szSqlCommnad[200] ;
nRet = sqlite3_open( szFileName,&conn ) ;
if( SQLITE_OK != nRet )
{
sqlite3_close( conn ) ;
return 0 ;
}
if( MODIFY_BOOK == nOpMode )
{
sprintf( szSqlCommnad,"update Book set id = %s,name = %s,type = %s,price = %lf,content = %s
where id = %s ;",( (Book*)pData)->id,( (Book*)pData)->name,( (Book*)pData)->type,( (Book*)pData)->price,
( (Book*)pData)->content,szKeyword ) ;
}
else if( MODIFY_CUSTOMER == nOpMode )
{
sprintf( szSqlCommnad,"update Customer set id = %s,sex = %s,age = %d,address= %s
where id = %s ; ",(( Customer *)pData )->id,(( Customer *)pData )->name,(( Customer *)pData )->sex,
(( Customer *)pData )->age,(( Customer *)pData )->addr,szKeyword ) ;
}
else if( MODIFY_ORDER == nOpMode )
{
sprintf( szSqlCommnad,"update OrderInfo set orderid = %s,customerid = %s,bookid = %s,buytype = %s
where orderid = %s ;",(( OrderInfo*)pData)->orderId,(( OrderInfo *)pData )->buyerId,(( OrderInfo *)pData )->bookId,
(( OrderInfo *)pData )->buyType,szKeyword ) ;
}
else
{
return 0 ;
}
if( SQLITE_OK != sqlite3_prepare_v2( conn,szSqlCommnad,strlen( szSqlCommnad ),NULL ) )
{
if( stmt )
{
sqlite3_finalize( stmt ) ;
}
sqlite3_close( conn ) ;
return 0 ;
}
if( SQLITE_DONE != sqlite3_step( stmt) )
{
if( stmt )
{
sqlite3_finalize( stmt ) ;
}
sqlite3_close( conn ) ;
return 0 ;
}
// 关闭数据库
sqlite3_close( conn ) ;
return 1 ;
}
（编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |