lazy-cats-code-warehouse
/
dbassist
/
教程.txt

使用方法
0.搭建mysql環境，參照文檔 <<devc++使用mysql.docx>>,解壓縮dbassist.rar,將文件夾導入項目,libdbassist.a的使用方法 和mysql庫文件一樣，參考文檔即可。
1.創建表及字段，并在項目中創建對應的結構體文件，結構體和表，成員變量和字段一一對應。參考文件t.sql
2.在結構體文件中引入表結構
以emp.c為例
typedef struct emp{
	TableValue tValue;
	int empno;
	char ename[10];
	char job[10];
	int mgr;
	struct tm  *hiredate;
	double sal;
	double commm;
	int deptno;
}emp;
TableValue描述
typedef struct TableValue{
	TableBasic* pbValue;
	HashMap map;
	void (*initValue)(void* data);
	void (*fillBasic)(void* data,char* name);
	void (*fillMap)(void* data);
	void (*print)(void* data);
	void (*initMembers)(void* data);
}TableValue;
tablevalue裏邊是包含表的名稱信息的tableBasic,以及存放表字段信息的map.
3.在結構體文件中引入兩個函數，我們以emp.c舉例
fillMap和initMembers是必不可少的。其中fillMap涉及到預處理的字段綁定尤為重要。initMembers是用來作一些簡單的初始化。
struct_set第一個參數是存放結構體字段的hashmap,第二個參數是類型，第三個參數是長度，第四個參數是字段路徑名稱，第五個參數是字段的指針，第六個參數是該字段是否在插入時必需。
static void fillMap(void* pdata)
{
         emp* pvalue = (emp*)pdata;

	 struct_set(pvalue->tValue.map,C_INT,4,pvalue->empno,&pvalue->empno,FILLED);

	 struct_set(pvalue->tValue.map,C_CHAR,10,pvalue->ename,pvalue->ename,FILLED);

	 struct_set(pvalue->tValue.map,C_CHAR,10,pvalue->job,pvalue->job,FILLED);

	 struct_set(pvalue->tValue.map,C_INT,4,pvalue->mgr,&pvalue->mgr,FILLED);

	 struct_set(pvalue->tValue.map,C_DATETIME,64,pvalue->hiredate,pvalue->hiredate,FILLED);

	 struct_set(pvalue->tValue.map,C_DOUBLE,9,pvalue->sal,&pvalue->sal,FILLED);

	 struct_set(pvalue->tValue.map,C_DOUBLE,9,pvalue->commm,&pvalue->commm,FILLED);

	 struct_set(pvalue->tValue.map,C_INT,2,pvalue->deptno,&pvalue->deptno,FILLED);
}

static void initMembers(void* pdata)
{
	emp* pvalue = (emp*)pdata;
	pvalue->empno = 0;
	pvalue->mgr = 0;
	pvalue->deptno = 0;
        memset(pvalue->job,'\0',10);
        memset(pvalue->ename,'\0',10);
        pvalue->commm = 0.00;
        pvalue->sal = 0.00;
	pvalue->hiredate = (struct tm*)malloc(sizeof(struct tm));
	memset((char*)pvalue->hiredate,0,sizeof(struct tm));
        pvalue->hiredate->tm_year = 1980-1900;
        pvalue->hiredate->tm_mon = 12;
        pvalue->hiredate->tm_mday = 17;
        pvalue->hiredate->tm_hour = 14;
        pvalue->hiredate->tm_min = 0;
        pvalue->hiredate->tm_sec = 1;
        pvalue->hiredate->tm_isdst = 0;
}
4.在主函數中
   1.引入#include "preExecuteSQL.h"和要用到的結構體
   2.連接數據庫
          MYSQL* conn_prt = init_mysql();
     conn_prt = connect_db(conn_prt,_HOST_,_USER_,_PASSWD_,_DBNAME_);
   3.創建數據庫表對應的對象和預處理框架結構體。
          emp a = createEmp();
     dept b = createDept();
     salgrade c = createSalgrade();
     SQLCmd* pCmd = createSQLCmd();
   4.SQL語句拼接
          pCmd->createStr(pCmd,pCmd->pObj,GET,NULL)
            ->from(pCmd,pCmd->pObj,pCmd->forTable(pCmd,&a,VNAME(a)),"")
            ->selectAll(pCmd,&a,pCmd->pObj,TABLE,TYPEO)
            ->onCondition(pCmd,pCmd->pObj,WHERE,pCmd->fillvalue(pCmd,&a,DATE_FORMAT(a.hiredate,"'%Y-%m-%                   d'"),">","1980-12-17"),"")
            ->limit(pCmd,pCmd->pObj,1,3);
   5.預處理請求
        JsonNode* Jnode2 = prepare_executeSQL(conn_prt,pCmd->pObj->sqlstr,pCmd->parModelList,pCmd->retModelList);

 如有不明白的請參考main中的例子或者參考下文的10個例子。
 聯係郵箱  solider12@163.com

一些規則
類型輸出(select字段)                       TYPEO
類型輸入(add,delete,update那些?號字段)      TYPEI
其他                                       TYPEN

下邊是例子

eg0.插入操作
mysql語句   insert into salgrade (lowsal,highsal) VALUES(?,?)
C語言用例    salgrade c = createSalgrade();
	    c.lowsal = 10000;
	    c.highsal = 15000;
	    SQLCmd* pCmd = createSQLCmd();
	    pCmd->createStr(pCmd,pCmd->pObj,ADD,NULL)
		->on(pCmd,pCmd->pObj,pCmd->forTable(pCmd,&c,NULL),"")
		->selectFull(pCmd,&c,pCmd->pObj,TABLE,TYPEI)
	        ->values(pCmd,pCmd->pObj);

eg1.更新操作
mysql語句       update salgrade set lowsal=?,highsal=? where grade=?
C語言用例        salgrade c = createSalgrade();
	    c.lowsal = 10;
	    c.highsal = 15;
	    c.grade = 9;
	    SQLCmd* pCmd = createSQLCmd();
	    pCmd->createStr(pCmd,pCmd->pObj,PUT,NULL)
		    ->on(pCmd,pCmd->pObj,pCmd->forTable(pCmd,&c,NULL),"")
		    ->multi_selected(pCmd,&c,pCmd->pObj,TYPEI,VNAME(c.lowsal),VNAME(c.highsal),"")
            ->onCondition(pCmd,pCmd->pObj,WHERE,pCmd->fillvalue(pCmd,&c,VNAME(c.grade),"=",NULL),"");

eg2.刪除操作
mysql語句    delete  from salgrade where grade=?
C語言用例    salgrade c = createSalgrade();
	    c.grade = 14;
	    SQLCmd* pCmd = createSQLCmd();
	    pCmd->createStr(pCmd,pCmd->pObj,DEL,NULL)
		    ->from(pCmd,pCmd->pObj,pCmd->forTable(pCmd,&c,NULL),"")
		    ->onCondition(pCmd,pCmd->pObj,WHERE,pCmd->fillvalue(pCmd,&c,VNAME(c.grade),"=",NULL),"");
注意: fillvalue是給預處理的參數填充值的，c.grade就是默認的值,"="是與值的關係對應grade=?,最後一個參數一般是NULL，除非遇到count,sum等内置函數.


eg3.聯合查詢操作
mysql語句      select a.ename,a.empno,b.dname,b.deptno  from emp a,dept b where a.deptno = b.deptno AND a.deptno=?
C語言用例       emp a = createEmp();
            dept b = createDept();
            a.deptno = 20;
            SQLCmd* pCmd = createSQLCmd();
	    pCmd->createStr(pCmd,pCmd->pObj,GET,NULL)
		    ->from(pCmd,pCmd->pObj,pCmd->forTable(pCmd,&a,VNAME(a)),pCmd->forTable(pCmd,&b,VNAME(b)),"")
		    ->multi_selected(pCmd,&a,pCmd->pObj,TYPEO,VNAME(a.ename),VNAME(a.empno),"")
		    ->multi_selected(pCmd,&b,pCmd->pObj,TYPEO,VNAME(b.dname),VNAME(b.deptno),"")
			->onCondition(pCmd,pCmd->pObj,WHERE,pCmd->compare(pCmd,VNAME(a.deptno),"=",VNAME(b.deptno)),
                          pCmd->jointWords(pCmd,"AND"),pCmd->fillvalue(pCmd,&a,VNAME(a.deptno),"=",NULL),"");
注意: multi_select表示多選,jointwords顧名思義就是連接詞.

eg4.連接查詢操作
mysql語句    select a.ename,a.empno,b.dname,b.deptno  from emp a LEFT JOIN dept b on a.deptno = b.deptno
C語言用例     pCmd->createStr(pCmd,pCmd->pObj,GET,NULL)
		    ->fromJoin(pCmd,pCmd->pObj,LEFT_JOIN,pCmd->forTable(pCmd,&a,VNAME(a)),pCmd->forTable(pCmd,&b,VNAME(b)))
			->multi_selected(pCmd,&a,pCmd->pObj,TYPEO,VNAME(a.ename),VNAME(a.empno),"")
		    ->multi_selected(pCmd,&b,pCmd->pObj,TYPEO,VNAME(b.dname),VNAME(b.deptno),"")
			->onCondition(pCmd,pCmd->pObj,ON,pCmd->compare(pCmd,VNAME(a.deptno),"=",VNAME(b.deptno)),"");
注意:連接查詢用fromJoin  onconditioni跟 ON語句.

eg5.分組查詢操作
mysql語句     select a.deptno,sum(a.sal)  from emp a where a.sal>? group by a.deptno having sum(a.sal)>? order by a.deptno DESC
C語言用例      emp a = createEmp();
           SQLCmd* pCmd = createSQLCmd();
	   a.sal = 1200.00;
	   double salK = 8500.00;
	   pCmd->createStr(pCmd,pCmd->pObj,GET,NULL)
	        ->from(pCmd,pCmd->pObj,pCmd->forTable(pCmd,&a,VNAME(a)),"")
	        ->multi_selected(pCmd,&a,pCmd->pObj,TYPEO,VNAME(a.deptno),sum(a.sal),"")
	        ->onCondition(pCmd,pCmd->pObj,WHERE,pCmd->fillvalue(pCmd,&a,VNAME(a.sal),">",NULL),"")
	        ->group_By(pCmd,pCmd->pObj,VNAME(a.deptno))
	        ->onCondition(pCmd,pCmd->pObj,HAVING,pCmd->fillvalue(pCmd,&a,sum(a.sal),">",&salK),"")
	        ->order_By(pCmd,pCmd->pObj,createOrderObj("a.deptno",DESC),NULL);
注意:可能有多個排序比如按照年齡升序，按照學號降序。所以order_by采用了orderObj類型的不定個數的參數，以NULL作爲結尾.

eg6.分頁查詢操作
mysql語句       select a.*  from emp a where DATE_FORMAT(a.hiredate,'%Y-%m-%d')>? limit 0,3
C語言用例        emp a = createEmp();
            SQLCmd* pCmd = createSQLCmd();
                         pCmd->createStr(pCmd,pCmd->pObj,GET,NULL)
            ->from(pCmd,pCmd->pObj,pCmd->forTable(pCmd,&a,VNAME(a)),"")
            ->selectAll(pCmd,&a,pCmd->pObj,TABLE,TYPEO)
            ->onCondition(pCmd,pCmd->pObj,WHERE,pCmd->fillvalue(pCmd,&a,DATE_FORMAT(a.hiredate,"'%Y-%m-%d'"),">","1980-12-17"),"")
            ->limit(pCmd,pCmd->pObj,1,3);
注意: fillvalue通常情況下最後一個參數為NULL,本例中引入了mysql内置函數date_format，所以在末尾追加字符串"1980-12-17",相當於給DATE_FORMAT(a.hiredate,"'%Y-%m-%d'")賦值。


eg7.where嵌套子查詢
mysql語句   select a.empno,a.ename  from emp a where a.deptno in (select b.deptno  from dept b) AND a.mgr=? (a.mgr=7968)
C語言用例    emp a = createEmp();
	  a.mgr = 7968;
	  dept b = createDept();
                    SQLCmd* pCmd = createSQLCmd();
          SQLObject* obj = createSQLObject(100,COLUMN);
          pCmd->createStr(pCmd,obj,GET,NULL)
              ->from(pCmd,obj,pCmd->forTable(pCmd,&b,VNAME(b)),"")
              ->multi_selected(pCmd,&b,obj,TYPEN,VNAME(b.deptno),"");
	      printf("\nsubStr = %s",obj->sqlstr);
	      pCmd->createStr(pCmd,pCmd->pObj,GET,NULL)
		  ->from(pCmd,pCmd->pObj,pCmd->forTable(pCmd,&a,VNAME(a)),"")
		  ->multi_selected(pCmd,&a,pCmd->pObj,TYPEO,VNAME(a.empno),VNAME(a.ename),"")
		  ->onCondition(pCmd,pCmd->pObj,WHERE,pCmd->compare(pCmd,VNAME(a.deptno),"in",pCmd->subQuery(obj)),
			        pCmd->jointWords(pCmd,"AND"),pCmd->fillvalue(pCmd,&a,VNAME(a.mgr),"=",NULL),"");


eg8.select嵌套子查詢
mysql語句  select a.ename,(select b.dname  from dept b where a.deptno = b.deptno)  from emp a
C語言用例     emp a = createEmp();
           dept b = createDept();
           SQLCmd* pCmd = createSQLCmd();
		SQLObject* obj = createSQLObject(100,COLUMN);
		pCmd->createStr(pCmd,obj,GET,NULL)
			      ->from(pCmd,obj,pCmd->forTable(pCmd,&b,VNAME(b)),"")
			      ->multi_selected(pCmd,&b,obj,TYPEO,VNAME(b.dname),"")
			      ->onCondition(pCmd,obj,WHERE,pCmd->compare(pCmd,VNAME(a.deptno),"=",VNAME(b.deptno)),"");
		printf("\nsubStr = %s",obj->sqlstr);
		pCmd->createStr(pCmd,pCmd->pObj,GET,NULL)
		    ->from(pCmd,pCmd->pObj,pCmd->forTable(pCmd,&a,VNAME(a)),"")
		    ->multi_selected(pCmd,&a,pCmd->pObj,TYPEO,VNAME(a.ename),"")
		    ->multi_selected(pCmd,"",pCmd->pObj,TYPEN,pCmd->subQuery(obj),"");
注意:select嵌套子查詢,子查詢中字段類型是TYPEO,而父查詢代入這個子查詢時用TYPEN.


eg9.from嵌套子查詢
mysql語句    select view_A.*,b.dname,b.deptno  from (select a.deptno,a.ename  from emp a) view_A,dept b where b.deptno = view_A.deptno
C語言用例     emp a = createEmp();
           dept b = createDept();
           SQLCmd* pCmd = createSQLCmd();
		SQLObject* obj = createSQLObject(100,SUBVIEW);
                pCmd->createStr(pCmd,obj,GET,NULL)
		              ->from(pCmd,obj,pCmd->forTable(pCmd,&a,VNAME(a)),"")
			      ->multi_selected(pCmd,&a,obj,TYPEO,VNAME(a.deptno),VNAME(a.ename),"");
                pCmd->createStr(pCmd,pCmd->pObj,GET,NULL)
                    ->from(pCmd,pCmd->pObj,pCmd->forView(pCmd,obj),pCmd->forTable(pCmd,&b,VNAME(b)),"")
                    ->selectAll(pCmd,obj,pCmd->pObj,VIEW,TYPEN)
		    ->multi_selected(pCmd,&b,pCmd->pObj,TYPEO,VNAME(b.dname),VNAME(b.deptno),"")
		    ->onCondition(pCmd,pCmd->pObj,WHERE,pCmd->compare(pCmd,VNAME(b.deptno),"=",pCmd->forViewPath(pCmd,obj,VNAME(a.deptno))),"");
注意:from嵌套子查詢,createSQLObject需要類型SUBVIEW,而父查詢代入這個子查詢時查找路徑用forViewPath.


eg10.拼接完成后如何查詢
C語言用例如下
         pCmd->createStr(pCmd,pCmd->pObj,GET,NULL)
            ->from(pCmd,pCmd->pObj,pCmd->forTable(pCmd,&a,VNAME(a)),"")
            ->selectAll(pCmd,&a,pCmd->pObj,TABLE,TYPEO)
            ->onCondition(pCmd,pCmd->pObj,WHERE,pCmd->fillvalue(pCmd,&a,DATE_FORMAT(a.hiredate,"'%Y-%m-%d'"),">","1980-12-17"),"")
            ->limit(pCmd,pCmd->pObj,1,3);
            free_pArr(pCmd);

	JsonNode* Jnode1 = prepare_executeSQL(conn_prt,pCmd->pObj->sqlstr,pCmd->parModelList,pCmd->retModelList);
        system("pause");
        resetCmd(pCmd);
	a.empno = 7369;
	pCmd->createStr(pCmd,pCmd->pObj,GET,NULL)
	    ->from(pCmd,pCmd->pObj,pCmd->forTable(pCmd,&a,VNAME(a)),"")
	    ->selectFull(pCmd,&a,pCmd->pObj,TABLE,TYPEO)
	    ->onCondition(pCmd,pCmd->pObj,WHERE,pCmd->fillvalue(pCmd,&a,VNAME(a.empno),"=",NULL),"");
	    free_pArr(pCmd);

	JsonNode* Jnode2 = prepare_executeSQL(conn_prt,pCmd->pObj->sqlstr,pCmd->parModelList,pCmd->retModelList);
注意:如果兩個sql語句參數或者返回值有不同，則第一個sql拼接并且在prepare_executeSQL執行完成之後，必須resetCmd(pCmd),否則會出現bug.如果只執行一個相同的sql，則沒必要resetCmd.