Common operation __QT of Qt database Database

The previous section describes how to use QT connections to Access database SQLite and MySQL, and in this section you will see the common operational details of accessing the database, primarily on the use of qsqldatabase,qsqlquery, and SQL injection attacks related to database access security.

Small Tips
1. Now the comparison of the recommended database design, each table has a meaningless primary key, such as ID.
2. As far as possible without the use of foreign keys, the logical relationship of the data with the above mentioned meaningless ID to associate, the advantage is that data migration does not need to consider the foreign key factors caused a lot of trouble. The logical relationship of the data is controlled by the program.
3. Because there is no use of foreign keys and can not be linked to delete, if you are worried that the database will leave some garbage data, you can use timed tasks in the system load relatively light time to delete them, such as 3 o'clock in the evening.
4. If time needs to be displayed according to different time zones, it is best to use timestamp instead of datetime for time related fields. For example, the development of a conference-related software, at the same time with China, the United States, the Germans attend the meeting, if you see the meeting time is 2015-03-03 10:00:00, everyone will naturally think it is their local time, then the meeting is only you alone. Preparing Data

Before we start, we'll get ready to use the data we need. Create the database QT, and then create 3 tables in this database user,blog, comment, and insert some data in each table, details below. Create user Table

CREATE TABLE ' user ' (
    ' id ' int (one) not null auto_increment,
    ' username ' varchar (256) NOT NULL,
    ' password ' Varc Har (256) NOT NULL,
    ' email ' varchar (256) default NULL,
    ' mobile ' varchar (#) default NULL,
    PRIMARY KEY (' id ') c6/>)

INSERT into ' user ' (' id ', ' username ', ' password ', ' email ', ' mobile ') VALUES
(1, ' Alice ', ' passw0rd ', NULL, N ull),
(2, ' Bob ', ' passw0rd ', NULL, NULL),
(3, ' Josh ', ' pa88w0rd ', null, NULL);

ID	username	Password	Email	Mobile
1	Alice	Passw0rd	Null	Null
2	Bob	Passw0rd	Null	Null
3	Josh	Pa88w0rd	Null	Null

Create a blog table

CREATE TABLE ' blog ' (
    ' id ' int ') NOT NULL auto_increment,
    ' user_id ' int (one) not null,
    ' content ' text not NUL L,
    ' created_time ' datetime NOT NULL,
    ' last_updated_time ' datetime NOT NULL,
    PRIMARY KEY (' id ')

INSERT into ' blog ' (' id ', ' user_id ', ' content ', ' created_time ', ' last_updated_time ') VALUES
(1, 1, ' content of blog 1. ', ' 2015-01-01 10:10:10 ', ' 2015-01-01 10:10:10 '),
(2, 2, ' Content of blog 2. ', ' 2015-02-02 20:20:20 ', ' 2015-02-02 20: 20:20 ');

ID	user_id	content	Created_time	Last_updated_time
1	1	Content of blog 1.	2015-01-01 10:10:10	2015-01-01 10:10:10
2	2	Content of Blog 2.	2015-02-02 20:20:20	2015-02-02 20:20:20

Create a comment table

CREATE TABLE ' comment ' (
    ' id ' int () NOT NULL auto_increment,
    ' user_id ' int (one) not null,
    ' blog_id ' int (11) Not NULL,
    ' content ' text isn't null,
    ' created_time ' datetime NOT NULL,
    PRIMARY KEY (' id ')

INSERT into ' comment ' (' id ', ' user_id ', ' blog_id ', ' content ', ' created_time ') VALUES
(1, 1, 1, ' Very useful. ', ' 2015- 01-02 11:11:11 '),
(2, 3, 2, ' Super ', ' 2015-03-03 23:33:33 ');

ID	user_id	blog_id	content	Created_time
1	1	1	Very useful.	2015-01-02 11:11:11
2	3	2	Super	2015-03-03 23:33:33

Qsqldatabase

Access to the database must first establish a connection with the database, Qt used qsqldatabase to represent a database connection (a bit not accustomed, since the expression is connected, there is no feeling if the call qsqlconnection will be better.) Unfortunately, Qt is not designed by us. Each connection has its own name ConnectionName, and the Qsqldatabase object with the same connectionname represents the same connection. It is important to note that if you want to access the database in multi-line Chengri, each thread will use a different database connection, that is, each thread is using Qsqldatabase connectionname, or you may encounter a lot of unexpected things.

Create Qsqldatabase objects with static functions Qsqldatabase Qsqldatabase:: adddatabase (const QString &type, const QString & Connectionname=qlatin1string (defaultconnection)). The first parameter type is the type of the specified database, such as "Qsqlite", "Qmysql", "Qpsql". The second argument is ConnectionName, which can be any string, and the default is "Qt_sql_default_connection" instead of an empty string.

Gets the Qsqldatabase object with a static function Qsqldatabase qsqldatabase:: Database (const QString &connectionname=qlatin1string ( defaultconnection), bool open=true). Create and get connections using the default connectionname:

void Createdefaultconnection () {
    Qsqldatabase db = Qsqldatabase::adddatabase ("Qmysql");
    Db.sethostname ("127.0.0.1");
    Db.setdatabasename ("Qt"); In the case of SQLite, the database filename
    db.setusername ("root");   If it is SQLite do not need
    Db.setpassword ("root");   If it is SQLite do not need if

    (!db.open ()) {
        qdebug () << "Connect to MYSQL Error:" << db.lasterror (). text (); 
  return
    }
}

Qsqldatabase getdefaultconnection () {return
    qsqldatabase::d atabase ();
}

We do not provide connectname, then Qt uses the default Connectname "Qt_sql_default_connection" to invoke Createdefaultconnection () to create a database connection call Getconnectionbyname () Gets a database connection, and multiple calls to this statement results in the same database connection To create and get a connection using a custom connectionname:

void Createconnectionbyname (const QString &connectionname) {
    Qsqldatabase db = Qsqldatabase::adddatabase (" Qmysql ", connectionname);
    Db.sethostname ("127.0.0.1");
    Db.setdatabasename ("Qt"); In the case of SQLite, the database filename
    db.setusername ("root");   If it is SQLite do not need
    Db.setpassword ("root");   If it is SQLite do not need if

    (!db.open ()) {
        qdebug () << "Connect to MYSQL Error:" << db.lasterror (). text (); 
  return
    }
}

Qsqldatabase getconnectionbyname (const QString &connectionname) {return
    qsqldatabase::d atabase ( connectionname);
}

For example, Connectname creates a database connection call for MyConnection call Createconnectionbyname (myconnection) getconnectionbyname (MyConnection) To get a database connection, multiple calls to this statement results in the same database connection

Reusing the same connectionname creating a database connection does not create multiple connections with the same name, and no errors occur, except that existing connections are deleted, and then the connection is recreated with this connectionname. This is very useful, for example, the database is installed on other computers, the program began to run when the database connection is good, but after a network problem caused the database connection disconnected, after a network recovery, with this database connection can not access the database, at this time or use the same ConnectionName you create a database connection again, you can access the database. But don't create a database connection by using the same connectionname without causing the program to fail, and to prevent the above, recreate one every time you use a database connection. Creating a database connection is a very resource-intensive and time consuming operation, with Socket connections at the bottom, which, while guaranteeing the correctness of the program, is returned at the cost of efficiency and is not an ideal solution.

It has always been feared that passing a qsqldatabase stack object between functions, rather than a pointer or a reference, would take up a lot of stack space, and that it might not cause problems by generating another Qsqldatabase object by calling its copy constructor. First sizeof (qsqldatabase) output 8, indicating that the Qsqldatabase occupies 8 bytes, open the Qsqldatabase source can also see that it has only 2 pointers to the member variable char *defaultconnection and QSQLD Atabaseprivate *d, so qsqldatabase occupy a little space. Second, its copy constructor creates a new object that shares the Char *defaultconnection and Qsqldatabaseprivate *d with the original object, so you can safely pass Qsqldatabase objects between functions without worrying about the problem. Qsqlquery

Qsqlquery has two important constructors, we usually use these two forms to construct Qsqlquery objects.

Qsqlquery:: qsqlquery (const QString &query = QString (), Qsqldatabase db = Qsqldatabase ()): If no incoming or incoming an invalid Qsqldatabase object, the default qsqldatabase is used, and if query is not an empty string, the database operation of this query is performed. For example, qsqlquery query (SELECT * from user) performs a query operation using the default database connection, and Qsqlquery query creates a Qsqlquery object using the default database connection, but does nothing.

Qsqlquery:: Qsqlquery (Qsqldatabase db): Creates a Qsqlquery object using the specified database connection, and uses the default database connection if the database connection is invalid, such as Qsqlquery query ( Getconnectionbyname ("MyConnection")) query Operation

There are already databases and related data, understanding qsqldatabase and Qsqlquery, and then give examples of the problems and solutions that you might encounter using Qsqlquery. For simplicity, use the default database connection, and think about how to replace the default database connection with our own designated ConnectionName connection in the following example. 1. Output all IDs in the user table, username, password

/**
 * Output All IDs in the user table, username,
 password
/void Outputidusernamepassword () {
    qsqlquery query ("Select ID, username, password from user ");

    while (Query.next ()) {
        qdebug () << QString ("ID:%1, Username:%2, Password:%3")
                    . Arg (Query.value ("id"). ToInt ())
                    . Arg (Query.value ("username"). ToString ())
                    . Arg (query.value ("password"). ToString ());

Output:

Id:1, Username:alice, password:passw0rd
Id:2, Username:bob, password:passw0rd
Id:3, Username:josh, password:pa88w0rd 2. Output Username as Alice's user

1. function definition/** * Output user its username equals incoming parameter username * @param username/void Outputuser (const
    QString &username) {QString sql = "SELECT * from user WHERE username= '" + username + "'";

    qsqlquery query (SQL); while (Query.next ()) {qdebug () << QString ("Id:%1, Username:%2, Password:%3"). Arg (Qu Ery.value ("id"). toint ()). Arg (Query.value ("username"). toString ())