首页 > 代码库 > SQLITE和QT
SQLITE和QT
sqlite3数据库支持事务
例如：
1 BEGIN DEFERRED TRANSACTION;2 INSERT INTO main.test_transaction (test_unique) VALUES (‘test_6‘);3 INSERT INTO main.test_transaction (test_unique) VALUES (‘test_6‘);4 COMMIT TRANSACTION;
第二个插入sql语句是不符合约束的，所以事务提交失败。
对比mysql，例如：
1 SET autocommit = 0;2 START TRANSACTION;3 INSERT INTO `他天天天天`.test_2 (test_column,test_column2) VALUES (‘12‘, ‘test2‘);4 INSERT INTO `他天天天天`.test_2 (test_column,test_column2) VALUES (‘12‘, ‘test3‘);5 COMMIT;
同样，第二个插入sql语句是不符合约束的，所以事务提交失败。
但是到了qt里面使用sqlite3数据库时却发现了个问题：提交事务时，sql语句有正确的、错误的，但是提交都会成功，commit函数返回true。
请看测试代码：
1 bool test__Qsql_transaction5() 2 { 3 bool is_success = false; 4 5 do 6 { 7 QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE"); 8 db.setDatabaseName("dbName_test_transaction"); 9 db.setHostName("AK-PC");10 db.setUserName("Asterisk");11 db.setPassword("0");12 //db.setPort(12345);13 if (db.open("Asterisk", "0"))14 {15 if (db.transaction())16 {17 QSqlQuery query;18 19 QString sql;20 21 sql = "CREATE TABLE IF NOT EXISTS test_transaction (tid INTEGER NOT NULL PRIMARY KEY ASC AUTOINCREMENT, param_1 VARCHAR(200));";22 query.exec(sql);23 qDebug() << query.lastError();24 25 sql = "INSERT INTO test_transaction (tid,param_2) VALUES (‘7‘,‘test2‘);";26 bool b1 = query.exec(sql);27 qDebug() << query.lastError();28 29 sql = "INSERT INTO test_transaction (tid,param_2) VALUES (‘8‘,‘test2‘);";30 bool b2 = query.exec(sql);31 qDebug() << query.lastError();32 33 34 if ( !db.commit() )35 {36 qDebug() << db.lastError();37 if ( !db.rollback() )38 qDebug() << db.lastError();39 }40 }41 }42 db.close();43 44 } while ( false );45 46 return is_success;47 }
同样，第二个插入sql语句是不符合约束的，所以事务提交失败。但是“db.commit()”返回true，不知道问题出在哪里。
完成测试代码：
1 #include <QtCore/QCoreApplication> 2 #include <QSqlDatabase> 3 #include <QSqlQuery> 4 #include <QString> 5 #include <QDebug> 6 #include <QSqlError> 7 #include <QSqlDriver> 8 9 #include <iostream> 10 11 bool test__Qsql_transaction() 12 { 13 bool is_success = false; 14 15 do 16 { 17 QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE", "connectionName_test_transaction"); 18 db.setDatabaseName("dbName_test_transaction"); 19 db.setHostName("AK-PC"); 20 db.setUserName("Asterisk"); 21 db.setPassword("0"); 22 //db.setPort(12345); 23 if (db.open("Asterisk", "0")) 24 { 25 if (db.transaction()) 26 { 27 QSqlQuery query(db); 28 29 QString sql; 30 31 sql = "CREATE TABLE IF NOT EXISTS test_transaction (tid INTEGER NOT NULL PRIMARY KEY ASC AUTOINCREMENT, param_1 VARCHAR(200));"; 32 query.exec(sql); 33 qDebug() << query.lastError(); 34 35 sql = "INSERT INTO test_transaction (tid,param_2) VALUES (‘7‘,‘test2‘);"; 36 bool b1 = query.exec(sql); 37 qDebug() << query.lastError(); 38 39 sql = "INSERT INTO test_transaction (tid,param_2) VALUES (‘8‘,‘test3‘);"; 40 bool b2 = query.exec(sql); 41 qDebug() << query.lastError(); 42 43 44 if ( !db.commit() ) 45 { 46 qDebug() << db.lastError(); 47 if ( !db.rollback() ) 48 qDebug() << db.lastError(); 49 } 50 } 51 } 52 db.close(); 53 54 } while ( false ); 55 56 return is_success; 57 } 58 bool test__Qsql_transaction2() 59 { 60 bool is_success = false; 61 62 do 63 { 64 QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE", "connectionName_test_transaction"); 65 db.setDatabaseName("dbName_test_transaction"); 66 db.setHostName("AK-PC"); 67 db.setUserName("Asterisk"); 68 db.setPassword("0"); 69 //db.setPort(12345); 70 if (db.open("Asterisk", "0")) 71 { 72 QSqlQuery query0(db); 73 74 QString sql; 75 bool b; 76 77 sql = "CREATE TABLE IF NOT EXISTS test_transaction1 (tid INTEGER NOT NULL PRIMARY KEY ASC AUTOINCREMENT, param_1 VARCHAR(200), param_2 VARCHAR(80) NOT NULL UNIQUE);"; 78 b = query0.exec(sql); 79 qDebug() << query0.lastError(); 80 81 sql = "CREATE TABLE IF NOT EXISTS test_transaction2 (tid INTEGER NOT NULL PRIMARY KEY ASC AUTOINCREMENT, param_1 VARCHAR(200), param_2 VARCHAR(80) NOT NULL UNIQUE);"; 82 b = query0.exec(sql); 83 qDebug() << query0.lastError(); 84 85 bool bSupportTransaction = db.driver()->hasFeature(QSqlDriver::Transactions); 86 if (db.transaction()) 87 { 88 QSqlQuery query(db); 89 90 QString sql1, sql2, sql3, sql4, sql5; 91 bool b1, b2, b3, b4, b5; 92 QString qstrid = "1"; 93 94 sql2 = "INSERT OR REPLACE INTO test_transaction2 (tid, param_3, param_2) VALUES (‘"; 95 sql2 += qstrid; 96 sql2 += "‘,‘test_1‘,‘test_2‘);"; 97 b2 = query.exec(sql2); 98 qDebug() << query.lastError(); 99 100 sql5 = "DELETE FROM test_transaction1 WHERE tid = ‘";101 sql5 += qstrid;102 sql5 += "‘;";103 b5 = query.exec(sql5);104 qDebug() << query.lastError();105 106 if ( !db.commit() )107 {108 db.rollback();109 }110 }111 }112 113 } while ( false );114 115 return is_success;116 }117 bool test__Qsql_transaction3()118 {119 bool is_success = false;120 121 do 122 {123 bool bUse = QSqlDatabase::isDriverAvailable("QOCI");124 QSqlDatabase db = QSqlDatabase::addDatabase("QOCI", "connectionName_test_transaction3");125 db.setDatabaseName("dbName_test_transaction_by_oracle");126 db.setHostName("AK-PC");127 db.setUserName("Asterisk");128 db.setPassword("0");129 //db.setPort(12345);130 if (db.open("Asterisk", "0"))131 {132 QSqlQuery query0(db);133 134 QString sql;135 bool b;136 137 sql = "CREATE TABLE IF NOT EXISTS test_transaction1 (tid INTEGER NOT NULL PRIMARY KEY ASC AUTOINCREMENT, param_1 VARCHAR(200), param_2 VARCHAR(80) NOT NULL UNIQUE);";138 b = query0.exec(sql);139 qDebug() << query0.lastError();140 141 sql = "CREATE TABLE IF NOT EXISTS test_transaction2 (tid INTEGER NOT NULL PRIMARY KEY ASC AUTOINCREMENT, param_1 VARCHAR(200), param_2 VARCHAR(80) NOT NULL UNIQUE);";142 b = query0.exec(sql);143 qDebug() << query0.lastError();144 145 bool bSupportTransaction = db.driver()->hasFeature(QSqlDriver::Transactions);146 if (db.transaction())147 {148 QSqlQuery query(db);149 150 QString sql1, sql2, sql3, sql4, sql5;151 bool b1, b2, b3, b4, b5;152 QString qstrid = "1";153 154 sql2 = "INSERT OR REPLACE INTO test_transaction2 (tid, param_3, param_2) VALUES (‘";155 sql2 += qstrid;156 sql2 += "‘,‘test_1‘,‘test_2‘);";157 b2 = query.exec(sql2);158 qDebug() << query.lastError();159 160 sql5 = "DELETE FROM test_transaction1 WHERE tid = ‘";161 sql5 += qstrid;162 sql5 += "‘;";163 b5 = query.exec(sql5);164 qDebug() << query.lastError();165 166 if ( !db.commit() )167 {168 db.rollback();169 }170 }171 }172 173 } while ( false );174 175 return is_success;176 }177 bool test__Qsql_transaction4()178 {179 bool is_success = false;180 181 do 182 {183 bool bUse = QSqlDatabase::isDriverAvailable("QMYSQL");184 QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL");185 qDebug() << db.lastError();186 187 db.setDatabaseName("dbName_test_transaction_by_mysql");188 db.setHostName("AK-PC");189 db.setUserName("Asterisk");190 db.setPassword("0");191 //db.setPort(12345);192 if (db.open("Asterisk", "0"))193 {194 QSqlQuery query0(db);195 196 QString sql;197 bool b;198 199 sql = "CREATE TABLE IF NOT EXISTS test_transaction1 (tid INTEGER NOT NULL PRIMARY KEY ASC AUTOINCREMENT, param_1 VARCHAR(200), param_2 VARCHAR(80) NOT NULL UNIQUE);";200 b = query0.exec(sql);201 qDebug() << query0.lastError();202 203 sql = "CREATE TABLE IF NOT EXISTS test_transaction2 (tid INTEGER NOT NULL PRIMARY KEY ASC AUTOINCREMENT, param_1 VARCHAR(200), param_2 VARCHAR(80) NOT NULL UNIQUE);";204 b = query0.exec(sql);205 qDebug() << query0.lastError();206 207 bool bSupportTransaction = db.driver()->hasFeature(QSqlDriver::Transactions);208 if (db.transaction())209 {210 QSqlQuery query(db);211 212 QString sql1, sql2, sql3, sql4, sql5;213 bool b1, b2, b3, b4, b5;214 QString qstrid = "1";215 216 sql2 = "INSERT OR REPLACE INTO test_transaction2 (tid, param_3, param_2) VALUES (‘";217 sql2 += qstrid;218 sql2 += "‘,‘test_1‘,‘test_2‘);";219 b2 = query.exec(sql2);220 qDebug() << query.lastError();221 222 sql5 = "DELETE FROM test_transaction1 WHERE tid = ‘";223 sql5 += qstrid;224 sql5 += "‘;";225 b5 = query.exec(sql5);226 qDebug() << query.lastError();227 228 if ( !db.commit() )229 {230 db.rollback();231 }232 }233 }234 235 } while ( false );236 237 return is_success;238 }239 bool test__Qsql_transaction5()240 {241 bool is_success = false;242 243 do 244 {245 QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");246 db.setDatabaseName("dbName_test_transaction");247 db.setHostName("AK-PC");248 db.setUserName("Asterisk");249 db.setPassword("0");250 //db.setPort(12345);251 if (db.open("Asterisk", "0"))252 {253 if (db.transaction())254 {255 QSqlQuery query;256 257 QString sql;258 259 sql = "CREATE TABLE IF NOT EXISTS test_transaction (tid INTEGER NOT NULL PRIMARY KEY ASC AUTOINCREMENT, param_1 VARCHAR(200));";260 query.exec(sql);261 qDebug() << query.lastError();262 263 sql = "INSERT INTO test_transaction (tid,param_2) VALUES (‘7‘,‘test2‘);";264 bool b1 = query.exec(sql);265 qDebug() << query.lastError();266 267 sql = "INSERT INTO test_transaction (tid,param_2) VALUES (‘8‘,‘test2‘);";268 bool b2 = query.exec(sql);269 qDebug() << query.lastError();270 271 272 if ( !db.commit() )273 {274 qDebug() << db.lastError();275 if ( !db.rollback() )276 qDebug() << db.lastError();277 }278 }279 }280 db.close();281 282 } while ( false );283 284 return is_success;285 }286 287 int main(int argc, char *argv[])288 {289 QCoreApplication a(argc, argv);290 291 //std::cout << std::boolalpha << test__Qsql_transaction() << std::endl;292 //std::cout << std::boolalpha << test__Qsql_transaction2() << std::endl;293 //std::cout << std::boolalpha << test__Qsql_transaction3() << std::endl;294 //std::cout << std::boolalpha << test__Qsql_transaction4() << std::endl;295 std::cout << std::boolalpha << test__Qsql_transaction5() << std::endl;296 297 return a.exec();298 }
*注：希望哪个高手能够帮我指出是我对qt操作sqlite有问题，还是qt对sqlite的操作就是这样的。万分感谢。
SQLITE和QT
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。