Qt notes: Database Summary (1)

# Include <qtsql>
Qt + = SQL
Qsqldatabase class implements database connection operations
Qsqlquery SQL statement execution
Qsqlrecord class encapsulates all database records

Qsqldatabase class

Qsqldatabase DB = qsqldatabase: adddatabase ("qoci"); dB. sethostname ("localhost"); // Database Host Name dB. setdatabasename ("Scott"); // database name dB. setusername ("Stott"); // database username dB. setpassword ("tiger"); // Database Password dB. open (); // open the database to connect to the database. close (); // release the database connection

Create Database Files

Qsqldatabase DB = qsqldatabase: adddatabase ("qsqlite"); DB. setdatabasename ("database. DB"); If (! DB. open () {qdebug ("database cannot be opened");} return false;

Create a table and insert two data entries after creating a database file

Qsqlquery queryaskquery.exe C ("create table student (ID integer primary key autoincrement, name nvarchar (20), age INT)"); // id query.exe C ("insert into student values (1, 'xiaoming ', 14) "inserting into query.exe C (" insert into student values (2, 'wang', 15 )");

Qsqlquery

Insert value to database

1. Insert directly using SQL statements (refer to the above)

Ii. Pre-processing (Oracle syntax and ODBC syntax)

It is suitable for inserting multiple records, or to avoid converting the value to a string (that is, to define it correctly). Call the prepare () function to specify a query containing placeholders, and then bind the value to be inserted.

Oracle syntax

Qsqlquery query; query. prepare ("insert into t_student (name, age) values (: name,: Age)"); // prepare to execute SQL query. bindvalue (": Name", "John"); // query the value to be inserted in the bind. bindvalue (": Age", 11); query.exe C ();

ODBC syntax

Qsqlquery query; query. Prepare ("insert into t_student (name, age) values (?,?) "); // Prepare to execute SQL query. addbindvalue (" Xiao Wang "); // the value to be inserted in query.bindvalue(111_1_query.exe C ();

3. Batch insert to database

Qsqlquery query; query. Prepare ("insert into student values (?, ?)"); Qvariantlist names; names <"Xiao Wang" <"Xiao Ming" <"Xiao Zhang" <"Xiao Xin"; // if you want to submit an empty string, use qvariant (qvariant: string) replace name query. addbindvalue (names); qvariantlist ages; ages <11 <13 <12 <11; query. addbindvalue (AGEs); If (! Q.exe cbatch () // batch processing. If an error occurs, the error qdebug () <q. lasterror () is output ();

Query database operations

Qsqlquery queryquery.exe C ("select * From t_student"); // the query result may contain more than one record, so we call it the result set while (query. next () {qstring name = query. value (0 ). tostring (); // retrieves the result int age = query for the 1st fields in the I-th record (counted from 0. value (0 ). toint (); // obtain the result of the first field in record I //... processing name and age variable data}

Seek (int n): the nth record in the result set that the query points. Specify the current location

First (): the query points to the first record of the result set.

Last (): the query points to the last record of the result set.

Next (): Query points to the next record. Every time this function is executed, it points to the next adjacent record.

Previous (): the query points to the previous record. Each time this function is executed, it points to the adjacent previous record.

Record (): Obtain the record pointed to now.

Value (int n): obtains the attribute value. N indicates the nth attribute you query.

Int rownum = query. At (); // gets the number of records pointed to by the query in the result set.

Int fieldno = query. record (). indexof ("name"); // return the column number of "name"

Int columnnum = query. record (). Count (); // gets the number of attributes (that is, columns) in each record.

 

Transaction operations

Operation Function: transaction (), commit () Commit, rollback () rollback
Before performing a transaction operation, determine whether the database supports transaction operations. Hasfeature is a qsqldriver class function

if (QSqlDatabase::database().driver()->hasFeature(QSqlDriver::Transactions)){ ... } // 

Insert a record and submit the transaction

Qsqldatabase: Database (). transaction (); qsqlquery queryquery.exe C ("select ID from t_student where class = 1"); If (query. next () {query.exe C ("insert into t_student (ID, name, age) values (3, 'lil', 13)");} qsqldatabase: Database (). commit ();