QT Learning Database (supports 10 types of databases)

QT provides a qtsql module to provide platform-independent SQL-based database operations. What we call "platform independence" here includes both the operating system platform and the various database platforms. In addition, we emphasize "SQL-based" because NoSQL databases do not have a common query method so far, so it is impossible to provide a generic NoSQL database operation. Qt database operations can also be easily integrated with the Model/view architecture. In general, our operations on the database are more about working with database tables, which is the forte of the Model/view architecture.

Qt uses Qsqldatabase to represent a database connection. On the lower level, Qt uses drivers (drivers) to interact with different database APIs. The Qt Desktop version provides the following drivers:

Drive	Database
QDB2	IBM DB2 (7.1 or later)
Qibase	Borland InterBase
Qmysql	Mysql
Qoci	Oracle Call Interface Driver
Qodbc	Open database Connectivity (ODBC) –microsoft SQL Server and other ODBC-compliant databases
Qpsql	PostgreSQL (7.3 or later)
QSQLITE2	SQLite 2
Qsqlite	SQLite 3
Qsymsql	SQLite 3 for Symbian platform
Qtds	Sybase Adaptive Server (abolished since Qt 4.7)

However, due to the limitations of the agreement, the Qt Open source version does not provide a binary version of all of the above drivers, but only in the form of source code. In general, Qt only comes with the qsqlite driver (this driver actually includes the SQLite database, which means that if you need to use SQLite, you only need the driver). We can choose to compile these drivers as part of Qt, or as plugins.

If we are accustomed to using SQL statements, we can choose the Qsqlquery class, and if we only need to use a high-level database interface (not concerned with SQL syntax), we can choose Qsqltablemodel and Qsqlrelationaltablemodel. We only introduce the use of the Qsqlquery class.

When in use, we can

QSqlDatabase::drivers();

• 1

Find a list of all database-driven names available in the system. We can only use the drivers that appear in the list. Because by default, Qtsql is provided as a module of Qt. In order to use the classes related to the database, we have to add such a sentence earlier in the. Pro file:

QT += sql

• 1

This means that our program requires the use of Qt's core, GUI, and SQL three modules. Note that if you need to use both QT4 and QT5 to compile the program, our. Pro files are usually like this:

QT += core gui sql
greaterThan(QT_MAJOR_VERSION, 4): QT += widgets

• 1
• 2

The two sentences are also clear: Qt needs to load the core, GUI and SQL three modules, if the motherboard is larger than 4, then add the Widgets module.

Let's look at a simple function:

BOOL Connect (constQString &dbname) {Qsqldatabase db =qsqldatabase::adddatabase ( "Qsqlite"); //db.sethostname ( "host"); //db.setdatabasename ( "dbname"); //db.setusername ( "username"); //Db.setpassword (if (!db.open ()) {qmessagebox::critical ( 0, qobject::tr ( "Database Error") , Db.lasterror (). text ()); return FALSE;} return true;}        

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15

We use the Connect () function to create a database connection. We use the qsqldatabase::adddatabase () static function to complete this request, which is to create a qsqldatabase instance. Note that the database connection is distinguished by its own name, not the name of the database. For example, we can use the following statement:

QSqlDatabase db=QSqlDatabase::addDatabase("QSQLITE", QString("con%1").arg(dbName));

• 1

At this point, we use the second parameter of the Adddatabase () function to connect a name to the database. In this example, the name used to differentiate the database connection is qstring ("conn%1"). Arg (dbName) instead of "Qsqlite". This parameter is optional, if not specified, the system will give a default name qsqldatabase::d efaultconnection, Qt will create a default connection at this time. If you give the same name as the name already exists, the new connection will replace the existing one. With this design, we can establish multiple connections for a single database.

We use the SQLite database here, only need to specify the database name. In the case of a database server, such as MySQL, we also need to specify the host name, port number, user name, and password, which are simply described using annotations.

Next we call the Qsqldatabase::open () function to open the database connection. By checking the return value of the open () function, we can tell if the database is open correctly.

Most classes in the Qtsql module have the LastError () function, which is used to check for recent errors. If you find any problems with the database operation, you should use this function for error checking. This is also reflected in the code above. Of course, this is the simplest implementation, and generally, a better design is to not clutter the interface code in database operations (and put the Connect () function in a specialized database operation Class).

Next we can use the Connect () function in the main () function:

int main (int argc, Char*argv[]) {qapplication A (argc, argv);if (connect ( "demo.db")) { Qsqlquery query; if (!query. EXEC ( "CREATE TABLE student ("  "id INTEGER PRIMARY KEY AutoIncrement, "" name VARCHAR, "" Age INT) ") {Qmessagebox:: Critical (0, Qobject::tr ( " Database Error "), Query.lasterror (). text ()); return 1;} else {return 1;} return a.exec ();}         

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18

In the main () function, we call this connect () function to open the database. If the open succeeds, we execute the SQL statement through a Qsqlquery instance, that is query.exec(); . Again, we use its lasterror () function to check if the execution result is correct.

Note the creation of the Qsqlquery instance here. We did not specify which database connection to create the query object for, at this point, the system will use the default connection, that is, using the adddatabase () function without the second parameter to create the connection (in fact, the name is Qsqldatabase:: Default connection for defaultconnection). If there is not such a connection, the system will error. In other words, if there is no default connection, we must specify which Qsqldatabase object, the return value of Adddatabase (), when creating the Qsqlquery object.

We can also check that the statement is executed correctly by using the qsqlquery::isactive () function. If the Qsqlquery object is active, the function returns True. The so-called "activity" means that the object successfully executes the EXEC () function, but it has not yet completed. If you need to set it to inactive, you can use the finish () or clear () function, or release the Qsqlquery object directly. It is important to note that some database systems cannot successfully complete the call to connect () or the rollback () function if there is an active SELECT statement. At this point, we must first set the active SELECT statement to inactive.

After we have created the database table student, we begin to insert the data and then extract it individually:

if (Connect ("Demo.db")) {qsqlquery query; Query. Prepare ("INSERT into student (name, age) VALUES (?,?)"); Qvariantlist names; Names <<"Tom" <<"Jack" <<"Jane" <<"Jerry"; Query. Addbindvalue (names); Qvariantlist ages; Ages <<<<<<<<25; Query. Addbindvalue (Ages); if (!query. Execbatch ()) {qmessagebox::critical (0, Qobject::tr ("Database Error"), query. LastError ().text ()) .finish () ; query .exec ( "select name, age from student") ; while (Query.next ()) {QString name = Query.value ( Span class= "Hljs-number" >0) .tostring () ; int age = query< Span class= "Hljs-preprocessor" >.value (1) .toInt ()  ":" << age1       

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21st
• 22
• 23

Still connected to the DEMO.DB database we created. We need to insert multiple data, we can use the qsqlquery::exec () function to insert data in one line, but here we choose another method: Batch execution. First, we use the Qsqlquery::p repare () function to preprocess This SQL statement, question mark? equals placeholders, indicating that we can replace these locations with actual data later. Briefly, preprocessing is a feature of the database that compiles SQL statements that are superior to normal SQL processing for performance and security. In the above code, we use a string list names to replace the position of the first question mark, an integer list ages replace the position of the second question mark, and using Qsqlquery::addbindvalue () We bind the actual data to the preprocessed SQL statement. It is important to note that the data in both lists of names and ages need to correspond to one by one. We then call Qsqlquery::execbatch () to execute the SQL in bulk and then end the object. In this way, the insert operation is complete.

Also, we use the ODBC style here. Placeholders, we can also use Oracle-style placeholders:

query.prepare("INSERT INTO student (name, age) VALUES (:name, :age)");

• 1

At this point, we need to use

query.bindValue(":name", names);
query.bindValue(":age", ages);

• 1
• 2

To bind. The greatest benefit of Oracle-style binding is that the binding's name and value are clear, regardless of order. However, it is important to note that the Bindvalue () function can only bind one position. Like what

query.prepare("INSERT INTO test (name1, name2) VALUES (:name, :name)");
// ...
query.bindValue(":name", name);

• 1
• 2
• 3

Only the first: name placeholder can be bound, and cannot be bound to the second one.

Next we still use the same query object to execute a SELECT statement. If there is a query result, Qsqlquery::next () returns true until the end of the result is reached and false to indicate that the traversal is complete. We use this to iterate through the results of the query using the while loop. Use the Qsqlquery::value () function to get the data stored in the corresponding database in the order of the fields in the SELECT statement.

For the operation of the database transaction, we can use Qsqldatabase::transaction () to open the transaction, Qsqldatabase::commit () or qsqldatabase::rollback () to end the transaction. Use the Qsqldatabase::d atabase () function to obtain the required database connection by name.

http://blog.csdn.net/u013007900/article/details/50094131

QT Learning Database (supports 10 types of databases)