jia_tan_diyic/handle/database.cpp

#include "database.h"
#include "QSqlRecord"

Database::Database(QObject *parent) :
    QObject(parent)
{

}

Database::~Database()
{
    _database.close();
}

void Database::SetTableName(QString table)
{
    _tableName = table + ".db";
}

bool Database::openDB()
{
    _database = QSqlDatabase::addDatabase("QSQLITE","db");
//    _database.setDatabaseName(_tableName);
    _database.setDatabaseName(".//qtDb.db");
    return _database.open();
}

bool Database::createDatabase()
{
    QSqlDatabase db = QSqlDatabase::database("db"); //建立数据库连接
    QSqlQuery query(db);

//    query.exec("DROP TABLE dabaodata");

    bool success = query.exec("CREATE TABLE dabaodata ("
                              "goods_id INTEGER NOT NULL,"
                              "code_single INTEGER NOT NULL,"
                              "level_id INTEGER NOT NULL,"
                              "tube_number INTEGER NOT NULL,"
                              "gross_weight DOUBLE NOT NULL,"
                              "net_weight DOUBLE NOT NULL,"
                              "machine_no VARCHAR(5),"
                              "packing_time VARCHAR(15),"
                              "packing_type VARCHAR(10),"
                              "carton_type VARCHAR(5) NOT NULL,"
                              "operator INTEGER NOT NULL,"
                              "case_number INTEGER NOT NULL,"
                              "backet_weight DOUBLE NOT NULL,"
                              "sort VARCHAR NOT NULL,"
                              "tongbu VARCHAR(1) DEFAULT 0,"
                              "remark VARCHAR);");


    if(success)
    {
        qDebug() << QObject::tr("数据库表创建成功！\n");
        return true;
    }
    else
    {
        qDebug() << QObject::tr("数据库表创建失败！\n");
        return false;
    }
}

bool Database::changeState(QString xianghao)
{
    QSqlDatabase db = QSqlDatabase::database("db"); //建立数据库连接
    QSqlQuery query(db);

    bool success = query.exec("update dabaodata set tongbu=1 where case_number="+xianghao);
    if(success)
    {
        return true;
    }
    else
    {
        return false;
    }
}

bool Database::insert(QString &table, QStringList &names, QStringList &values)
{
    if(names.size() != values.size())
    {
        return false;
    }

//    QSqlQuery query(QSqlDatabase::database());

    QSqlDatabase db = QSqlDatabase::database("db"); //建立数据库连接
    QSqlQuery query(db);

    QString sql = QString("insert into ") + table + QString("(");

    int i;
    for(i = 0; i < names.size(); i++)
    {
        sql = sql + names.value(i);
        if(i != names.size()-1)
        {
            sql += QString(",");
        }
        else
        {
            sql += QString(")");
        }
    }

    sql = sql + QString("values (");

    for(i = 0; i < values.size(); i++)
    {
        sql = sql + QString("'") + values.value(i) + QString("'");
        if (i != values.size()-1)
        {
            sql += QString(",");
        }
    }
    sql += QString(")");



    return (query.exec(sql)) ? true : false;
}

bool Database::update(QString &table, QStringList &names, QStringList &values, QString &expression)
{
    if (names.size() != values.size())
        return false;
    //UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值
//    QSqlQuery query(QSqlDatabase::database());

    QSqlDatabase db = QSqlDatabase::database("db"); //建立数据库连接
    QSqlQuery query(db);

    QString sql = QString("update ")+table+QString(" set ");
    for (int i = 0; i < names.size(); i++)
    {
        sql += names.value(i);
        sql += QString(" = '");
        sql = sql + values.value(i);
        sql = sql + QString("'");
        if (i != names.size()-1)
            sql = sql + QString(" ,");
    }
    sql = sql + QString(" where ") + expression;
    return (query.exec(sql)) ? true : false;
}

bool Database::del(QString &table, QString &expression)
{
    //DELETE FROM 表名称 WHERE 列名称 = 值
    QSqlQuery query(QSqlDatabase::database());
    QString sql = QString("delete from ") + table + QString(" where ") + expression;
    return (query.exec(sql)) ? true : false;
}

QList<QStringList> Database::GetValues(QString &table, QStringList &values)
{
    dbmxdata.clear();
    dbmxlist.clear();
    QSqlDatabase db = QSqlDatabase::database("db"); //建立数据库连接
    QSqlQuery query(db);

    QString sql = QString("select * from ") + table;
    query.exec(sql);
    while (query.next())
    {
        for(int i = 0;i <= values.size()-1;i++){
//            qDebug()<<values.at(i) << query.value(values.at(i)).toString();
            dbmxdata.append(query.value(values.at(i)).toString());
        }
        dbmxlist.append(dbmxdata);
        dbmxdata.clear();
    }
    return dbmxlist;
}

//void Database::connectDB(QString dbName, QString username, QString password)
//{
//    if (QSqlDatabase::contains("qt_sql_default_connection"))
//    {
//        database = QSqlDatabase::database("qt_sql_default_connection");
//    }
//    else
//    {
//        database = QSqlDatabase::addDatabase("QSQLITE");
//        database.setDatabaseName(dbName);
//        database.setUserName(username);
//        database.setPassword(password);
//    }

//    if (!database.open())
//    {
//        qDebug() << "Error: Failed to connect database." << database.lastError();
//    }
//    else
//    {
//        QSqlQuery sql_query;
//        QString create_sql;
////        create_sql = "create table login (name varchar(100), logintime DATETIME)";
//        sql_query.prepare(create_sql);
//        if(!sql_query.exec())
//        {
//            qDebug() << "Error: Fail to create table." << sql_query.lastError();
//        }
//        else
//        {
//            qDebug() << "Table created!";
//        }
//    }

//}