每一种数据库提供给程序员使用的API都有其特点,根据其设计的机制不同有很大的差别,对于sqlITE3,其API的使用还是很方便很简单的。
一.sqlite3提供的一些主要的API
在前一篇博文sqlite3的c/c++接口中对其原理有了介绍,主要使用的API一般有:
(1)打开数据库
int sqlite3_open( 文件名,sqlite3 ** ) ;
如果此文件不存在,那么就直接创建它,存在的话就直接打开。
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 ; }