#include "occiemp.h"

#include

//构造函数

occiemp::occiemp(string user, string passwd, string db)

{

try

{

this->env = Environment::createEnvironment();//创建环境

this->conn = env->createConnection(user, passwd, db);//创建连接

}

catch(SQLException ex)

{

cout << "Error number: " << ex.getErrorCode() << endl;

cout << ex.getMessage() << endl;

}

}

//析构函数

occiemp::~occiemp()

{

if (this->env != nullptr)

{

this->env->terminateConnection(this->conn);//释放连接

}

if (this->env)

{

Environment::terminateEnvironment(this->env);//释放环境

}

}

//显示所有数据

void occiemp::displayAllRows()

{

string sqlStmt = "select empno,ename,job, mgr,hiredate,sal,comm, deptno from emp order by empno ";

this->stmt = this->conn->createStatement(sqlStmt);

//执行查询语句

ResultSet* rset = this->stmt->executeQuery();//ResultSet提供对通过执行生成的数据表的访问Statement。表行按顺序检索。在一行中，可以按任何顺序访问列值。ResultSet保持光标指向其当前数据行。最初，光标位于第一行之前。next() 方法将光标移动到下一行。

try

{

cout << "empno" << setw(10) << "ename" << setw(10) << "job" << setw(20) << "mgr" << setw(30) << "hiredate" << setw(20) << "sal" << setw(10) << "comm" << setw(10) << "deptno" << endl;

while (rset->next())

{

cout << rset->getInt(1) << setw(10) << rset->getString(2) << setw(10) << rset->getString(3) << setw(20) << rset->getInt(4) << setw(30) << (rset->getDate(5).toText()) << setw(20)<< rset->getDouble(6) << setw(10) << rset->getDouble(7) << setw(10) << rset->getInt(8) << endl;

}

}

catch (SQLException ex)

{

cout << "Exception thrown for displayAllRows" << endl;

cout << "Error number: " << ex.getErrorCode() << endl;

cout << ex.getMessage() << endl;

}

this->stmt->closeResultSet(rset);//释放集合数据

this->conn->terminateStatement(this->stmt);//释放SQL语句

}

//更新数据

void occiemp::updateRow(int empno, double sal)

{

string sqlStmt ="UPDATE emp SET sal = :x WHERE empno= :y";

try

{

this->stmt = this->conn->createStatement(sqlStmt);

stmt->setInt(1, empno);

stmt->setDouble(2, sal);

//执行非查询语句

unsigned int res = stmt->executeUpdate();

if (res > 0)

{

cout << "update - Success "<

}

}

catch (SQLException ex)

{

cout << "Exception thrown for updateRow" << endl;

cout << "Error number: " << ex.getErrorCode() << endl;

cout << ex.getMessage() << endl;

}

this->conn->terminateStatement(this->stmt);

}

//根据条件删除数据

void occiemp::deleteRow(int empno, string ename)

{

string sqlStmt ="DELETE FROM emp WHERE empno= :x AND ename = :y";

try

{

this->stmt = this->conn->createStatement(sqlStmt);

this->stmt->setInt(1, empno);

this->stmt->setString(2, ename);

unsigned int res = this->stmt->executeUpdate();

if (res > 0)

{

cout << "delete - Success" << res << " 行受影响。" << endl;

}

}

catch (SQLException ex)

{

cout << "Exception thrown for deleteRow" << endl;

cout << "Error number: " << ex.getErrorCode() << endl;

cout << ex.getMessage() << endl;

}

this->conn->terminateStatement(this->stmt);

}

//插入一行数据

void occiemp::insertRow(EMP emp)

{

string sqlStmt = "INSERT INTO EMP VALUES (:x1, :x2, :x3, :x4 ,:x5, :x6, :x7,:x8)";

this->stmt = this->conn->createStatement(sqlStmt);

try

{

this->stmt->setInt(1, emp.empno);

this->stmt->setString(2, emp.ename);

this->stmt->setString(3, emp.job);

this->stmt->setDouble(4, emp.mgr);

this->stmt->setDate(5, Todate(emp.hiredate));

this->stmt->setDouble(6, emp.sal);

this->stmt->setDouble(7, emp.comm);

this->stmt->setInt(8, emp.deptno);

unsigned int res=this->stmt->executeUpdate();

if (res >0)

{

cout << "Data saved successfully ," << res << " 行数据！" << endl;

}

}

catch (SQLException ex)

{

cout << "Exception thrown for insertRow of emp" << endl;

cout << "Error number: " << ex.getErrorCode() << endl;

cout << ex.getMessage() << endl;

}

this->conn->terminateStatement(this->stmt);//释放SQL语句

}

Date occiemp::Todate(string strtime)

{

try

{

int year = stoi((strtime.substr(0, 4)));

unsigned int month= stoi((strtime.substr(4,2)));

unsigned int day= stoi((strtime.substr(6, 2)));

unsigned int hour = stoi((strtime.substr(8, 2)));

unsigned int minute = stoi((strtime.substr(10, 2)));

unsigned int seconds= stoi((strtime.substr(12, 2)));

Date date(this->env, year, month, day, hour, minute, seconds);

return date;

}

catch (const std::exception& e)

{

cout << e.what() << endl;

return nullptr;

}

}