工具:QT sqlserver2008
步骤：
1.使用QT连接sqlserver数据库，并插入一些数据（main.cpp）
2.在myqtsql.h中添加我所需要的控件
3.实现qt的界面，添加查询功能，并实现模糊查询。
主要代码：myqtsql.h myqtsql.cpp main.cpp
界面如下：
1.myqtsql.h
#ifndef MYQTSQL_H
#define MYQTSQL_H
#include "ui_myqtsql.h"
#include <QtWidgets/QMainWindow>
#include <QPushButton>
#include <QListWidget>
#include <QLineEdit>
#include <QComboBox>
#include <QTableWidget>
#include <QLabel>
#include <QDateEdit>
#include <QSqlQuery>
#include <QCheckBox>
#include <QGroupBox>
class myqtsql : public QMainWindow
{
Q_OBJECT
public:
myqtsql(QWidget *parent = 0);
~myqtsql();
void setTableWidget();
void showtable(QSqlQuery query);
private:
Ui::myqtsqlClass ui;
QGroupBox *groupBox;
QLabel *label1;
QLabel *label2;
QLabel *label3;
QLabel *label4;
QLabel *label5;
QPushButton *button;
QDateEdit *dateEdit1;
QDateEdit *dateEdit2;
QLineEdit *lineEdit;
QComboBox *combobox;
QTableWidget *tablewidget;
private slots:
void on_button_clicked();
};
#endif // MYQTSQL_H
2.myqtsql.cpp
#include "myqtsql.h"
#include <QHBoxLayout>
#include <QDebug>
#include <QVBoxLayout>
#include <QGridLayout>
#include <QStringList>
#include <QTableWidgetItem>
#include <QSpacerItem>
myqtsql::myqtsql(QWidget *parent)
: QMainWindow(parent)
{
ui.setupUi(this);
this->setFixedWidth(400);
groupBox = new QGroupBox;
groupBox->setTitle("query");
label1 = new QLabel("sex: ");
label3 = new QLabel("keyword: ");
label4 = new QLabel("start date :");
label5 = new QLabel("end date :");
button = new QPushButton("query");
button->setStyleSheet("background: rgb(0,255,0)");
//button->setStyleSheet("color: white");
tablewidget = new QTableWidget();
setTableWidget();
lineEdit = new QLineEdit;
lineEdit->setFixedWidth(100);
combobox = new QComboBox;
//combobox->setFixedWidth(60);
combobox->addItem(QString::fromLocal8Bit("全部"));
combobox->addItem(QString::fromLocal8Bit("男"));
combobox->addItem(QString::fromLocal8Bit("女"));
dateEdit1 = new QDateEdit;
dateEdit2 = new QDateEdit;
QVBoxLayout *mainLayout = new QVBoxLayout(this);
QGridLayout *topLayout = new QGridLayout(this);
QHBoxLayout *rightLayout = new QHBoxLayout(this);
topLayout->addWidget(label4,0,0);
topLayout->addWidget(dateEdit1,0,1);
//topLayout->addItem(new QSpacerItem());
topLayout->addWidget(label1,0,2);
topLayout->addWidget(combobox,0,3);
topLayout->addWidget(label5,1,0);
topLayout->addWidget(dateEdit2,1,1);
topLayout->addWidget(label3,1,2);
topLayout->addWidget(lineEdit,1,3);
topLayout->addWidget(button,2,3);
groupBox->setLayout(topLayout);
mainLayout->addWidget(groupBox);
mainLayout->addWidget(tablewidget);
this->centralWidget()->setLayout(mainLayout);
connect(button,SIGNAL(clicked()),this,SLOT(on_button_clicked()));
}
myqtsql::~myqtsql()
{
}
void myqtsql::setTableWidget()
{
tablewidget->setColumnCount(3);
QStringList rowLabels;
rowLabels << "name" << "sex" << "data";
tablewidget->setHorizontalHeaderLabels(rowLabels);
tablewidget->verticalHeader()->setVisible(false);
}
void myqtsql::showtable(QSqlQuery query)
{
int RowCont = 0;
while(query.next())
{
tablewidget->insertRow(RowCont);
tablewidget->setItem(RowCont,0,new QTableWidgetItem(query.value(1).toString()));
tablewidget->setItem(RowCont,1,new QTableWidgetItem(query.value(2).toString()));
tablewidget->setItem(RowCont,2,new QTableWidgetItem(query.value(3).toString()));
RowCont++;
}
RowCont = RowCont;
}
void myqtsql::on_button_clicked()
{
QSqlQuery query;
QString s = lineEdit->text();
QString start = dateEdit1->text();
QString end = dateEdit2->text();
if(s.isEmpty())
{
if(combobox->currentText() == QString::fromLocal8Bit("男"))
{
query.exec(QString::fromLocal8Bit("select * from aaa where sex = '男' and data between '")
+start+QString::fromLocal8Bit("' and '")+end+QString::fromLocal8Bit("'"));
tablewidget->model()->removeRows(0,tablewidget->rowCount());
showtable(query);
}
else if(combobox->currentText() == QString::fromLocal8Bit("女"))
{
query.exec(QString::fromLocal8Bit("select * from aaa where sex = '女' and data between '")
+start+QString::fromLocal8Bit("' and '")+end+QString::fromLocal8Bit("'"));
tablewidget->model()->removeRows(0,tablewidget->rowCount());
showtable(query);
}
else if(combobox->currentText() == QString::fromLocal8Bit("全部"))
{
query.exec("select * from aaa where data between '"+start+"' and '"+end+"'");
tablewidget->model()->removeRows(0,tablewidget->rowCount());
showtable(query);
}
}
else
{
if(combobox->currentText() == QString::fromLocal8Bit("男"))
{
query.exec(QString::fromLocal8Bit("select * from aaa where sex = '男' and name like '%")
+s+QString::fromLocal8Bit("%' and data between '")+start+QString::fromLocal8Bit("'and'")+end+QString::fromLocal8Bit("'"));
tablewidget->model()->removeRows(0,tablewidget->rowCount());
showtable(query);
}
else if(combobox->currentText() == QString::fromLocal8Bit("女"))
{
query.exec(QString::fromLocal8Bit("select * from aaa where sex = '女'and name like '%")
+s+QString::fromLocal8Bit("%' and data between '")+start+QString::fromLocal8Bit("'and'")+end+QString::fromLocal8Bit("'"));
tablewidget->model()->removeRows(0,tablewidget->rowCount());
showtable(query);
}
else if(combobox->currentText() == QString::fromLocal8Bit("全部"))
{
query.exec("select * from aaa where name like '%"+s+"%'and data between '"+start+"' and '"+end+"'");
tablewidget->model()->removeRows(0,tablewidget->rowCount());
showtable(query);
}
}
}
3.main.cpp
#include "myqtsql.h"
#include <QtWidgets/QApplication>
#include <QSqlDatabase> //头文件
#include <QDebug>
#include <QMessageBox>
#include <QSqlError>
#include <QDialog>
#include <QSqlQuery>
#include <QSqlDriver>
#include <QSqlRecord>
#include <QTime>
#include <QObject>
bool OpenDatabase()
{
QSqlDatabase db = QSqlDatabase::addDatabase("QODBC"); //数据库驱动类型为SQL Server
qDebug()<<"ODBC driver?"<<db.isValid();
QString dsn = QString::fromLocal8Bit("dsn"); //数据源名称
db.setHostName("localhost"); //选择本地主机，127.0.1.1
db.setDatabaseName(dsn); //设置数据源名称
db.setUserName("sa"); //登录用户
db.setPassword("123456"); //密码
if(!db.open()) //打开数据库
{
qDebug()<<db.lastError().text();
QMessageBox::critical(0, QObject::tr("Database error"), db.lastError().text());
return false; //打开失败
}
else
qDebug()<<"database open success!";
return true;
}
void createtable()
{
if(!OpenDatabase())
return;
QSqlQuery query;
bool success = query.exec("create table aaa(id int,name varchar(10),sex varchar(4),data date,other varchar(10))");
if(success)
qDebug()<<QObject::tr("数据库表创建成功\n");
else
qDebug()<<QObject::tr("数据库表创建失败\n");
query.exec("select *from aaa");
QSqlRecord rec = query.record();
qDebug() << QObject::tr("num:")<<rec.count();
QTime t;
t.start();
/*
long records = 10;
for(int i = 0;i < records;i++)
{
query.bindValue(0,i);
query.bindValue(1,"张三");
query.bindValue(2,"nan");
query.bindValue(3,"2018/7/25");
query.bindValue(4,NULL);
success = query.exec();
if(!success)
{
QSqlError lastError = query.lastError();
qDebug()<<lastError.driverText()<<QString(QObject::tr("插入失败"));
}
}
for(int i = 0;i < records;i++)
{
query.bindValue(0,i+10);
query.bindValue(1,"lisi");
query.bindValue(2,"nv");
query.bindValue(3,"2018/7/26");
query.bindValue(4,NULL);
success = query.exec();
if(!success)
{
QSqlError lastError = query.lastError();
qDebug()<<lastError.driverText()<<QString(QObject::tr("插入失败"));
}
}
*/
query.prepare("insert into aaa values(?,?,?,?,?)");
int records = 10;
for(int i = 0;i < records;i++)
{
//success = query.exec(QObject::tr("insert into aaa values(i,'zhang'，'nan'，'2018-7-30',' ')"));
query.bindValue(0,i);
query.bindValue(1,QString::fromLocal8Bit("张三"));
query.bindValue(2,QString::fromLocal8Bit("男"));
query.bindValue(3,"2018/7/25");
query.bindValue(4,NULL);
success = query.exec();
if(!success)
{
QSqlError lastError = query.lastError();
qDebug()<<lastError.driverText()<<QString(QObject::tr("插入失败"));
}
}
//success = query.exec(QObject::tr("insert into aaa values(i,'zhang'，'nan'，'2018-7-30',' ')"));
query.bindValue(0,10);
query.bindValue(1,QString::fromLocal8Bit("李四"));
query.bindValue(2,QString::fromLocal8Bit("女"));
query.bindValue(3,"2018/7/26");
query.bindValue(4,NULL);
success = query.exec();
if(!success)
{
QSqlError lastError = query.lastError();
qDebug()<<lastError.driverText()<<QString(QObject::tr("插入失败"));
}
for(int i = 0;i < records;i++)
{
//success = query.exec(QObject::tr("insert into aaa values(i,'zhang'，'nan'，'2018-7-30',' ')"));
query.bindValue(0,i+20);
query.bindValue(1,QString::fromLocal8Bit("钱三"));
query.bindValue(2,QString::fromLocal8Bit("男"));
query.bindValue(3,"2018/7/27");
query.bindValue(4,NULL);
success = query.exec();
if(!success)
{
QSqlError lastError = query.lastError();
qDebug()<<lastError.driverText()<<QString(QObject::tr("插入失败"));
}
}
query.bindValue(0,30);
query.bindValue(1,QString::fromLocal8Bit("李子昂"));
query.bindValue(2,QString::fromLocal8Bit("男"));
query.bindValue(3,"2018/7/30");
query.bindValue(4,NULL);
success = query.exec();
if(!success)
{
QSqlError lastError = query.lastError();
qDebug()<<lastError.driverText()<<QString(QObject::tr("插入失败"));
}
for(int i = 0;i < records;i++)
{
//success = query.exec(QObject::tr("insert into aaa values(i,'zhang'，'nan'，'2018-7-30',' ')"));
query.bindValue(0,i+40);
query.bindValue(1,QString::fromLocal8Bit("王莉"));
query.bindValue(2,QString::fromLocal8Bit("女"));
query.bindValue(3,"2018/7/25");
query.bindValue(4,NULL);
success = query.exec();
if(!success)
{
QSqlError lastError = query.lastError();
qDebug()<<lastError.driverText()<<QString(QObject::tr("插入失败"));
}
}
qDebug()<< QObject::tr("count:")<<query.size();
}
int main(int argc, char *argv[])
{
QApplication a(argc, argv);
createtable();
//OpenDatabase();
myqtsql w;
w.show();
return a.exec();
}