if (vecInfo.empty()) {
return ; //数据为空，不上传，不上传标志设置为1，只有0表示上传成功
}
std::string strUserName = userName;
std::string strPasswd = passWd;
std::string strDbName;
std::string strIp = ip;
std::string strPort = port;
if (strPort.empty()) {
strPort = ""; //如果输入的数据为空，设置默认值
}
if (dbName.empty()) {
strDbName = "orcl";//如果输入的数据为空，设置默认值
}
else {
strDbName = dbName;
}
std::string strSrvName = strIp + ":" + strPort + "/" + strDbName;//"10.194.98.34:1521/orcl";
std::cout << "数据库连接信息为：" << strSrvName << std::endl;
Environment *env = Environment::createEnvironment("ZHS16GBK", "UTF8");
Connection * conn = NULL;
Statement * stmt = NULL;
if (NULL == env) {
return -;
}
try
{
conn = env->createConnection(strUserName, strPasswd, strSrvName);
if (conn == NULL) {
return -;
}
stmt = conn->createStatement();
std::string strSQL = "INSERT INTO VDS.LOG_OPERATION (USER_ID, OPERATOR, IP,MAC,OPERATOR_ORG_ID,OPERATOR_ORG_NAME,APP_CODE,OBJECT_ORG_IDS,OBJECT_ORG_NAMES,OPERATOR_OBJECT_TYPE,OPERATOR_OBJECT_KEYS,OPERATOR_OBJECT_VALUES,BUSINESS,ACT,OPERATE_RESULT,SEARCH_INFO,CONTENT,CREATOR,CREATE_TIME,UPDATE_TIME) VALUES (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,current_timestamp,current_timestamp)";
stmt->setSQL(strSQL);
int count = vecInfo.size();
stmt->setMaxIterations(count); //最多一次性批量插入的最大数据
stmt->setMaxParamSize(, );
stmt->setMaxParamSize(, );
stmt->setMaxParamSize(, );
stmt->setMaxParamSize(, );
stmt->setMaxParamSize(, );
stmt->setMaxParamSize(, );
stmt->setMaxParamSize(, );
stmt->setMaxParamSize(, );
stmt->setMaxParamSize(, );
stmt->setMaxParamSize(, );
stmt->setMaxParamSize(, );
stmt->setMaxParamSize(, );
stmt->setMaxParamSize(, );
stmt->setMaxParamSize(, );
stmt->setMaxParamSize(, );
stmt->setMaxParamSize(, );
stmt->setMaxParamSize(, );
//std::string strSQL = "INSERT INTO VDS.LOG_OPERATION (USER_ID, OPERATOR, IP,MAC,OPERATOR_ORG_ID,OPERATOR_ORG_NAME,APP_CODE,OBJECT_ORG_IDS,OBJECT_ORG_NAMES,OPERATOR_OBJECT_TYPE,OPERATOR_OBJECT_KEYS,OPERATOR_OBJECT_VALUES,BUSINESS,ACT,OPERATE_RESULT,SEARCH_INFO,CONTENT,CREATOR,CREATE_TIME,UPDATE_TIME) VALUES ";
for (int j = ; j< count ; j++){
int i = ;
stmt->setString(i++, *(vecInfo.at(j)._user_id));
stmt->setString(i++, *(vecInfo.at(j)._operator));
stmt->setString(i++, *(vecInfo.at(j)._ip));
stmt->setString(i++, *(vecInfo.at(j)._mac));
stmt->setString(i++, *(vecInfo.at(j)._operator_org_id));
stmt->setString(i++, *(vecInfo.at(j)._operator_org_name));
stmt->setString(i++, *(vecInfo.at(j)._app_code));
stmt->setString(i++, *(vecInfo.at(j)._object_org_ids));
stmt->setString(i++, *(vecInfo.at(j)._object_org_names));
stmt->setString(i++, *(vecInfo.at(j)._operator_object_type));
stmt->setString(i++, *(vecInfo.at(j)._operator_object_keys));
stmt->setString(i++, *(vecInfo.at(j)._operator_object_values));
stmt->setString(i++, *(vecInfo.at(j)._business));
stmt->setString(i++, *(vecInfo.at(j)._act));
stmt->setInt(i++, vecInfo.at(j)._operate_result);
stmt->setString(i++, *(vecInfo.at(j)._search_info));
stmt->setString(i++, *(vecInfo.at(j)._content));
stmt->setString(i++, *(vecInfo.at(j)._creator));
if (j != count - ) //count至少为1
{
stmt->addIteration();
}
}
//
//for (int i = 0; i < vecInfo.size(); i++) {
// std::string strVal;
// strVal += "(";
// strVal += "'" + *(vecInfo.at(i)._user_id)+"',";
// strVal += "'" + *(vecInfo.at(i)._operator) + "',";
// strVal += "'" + *(vecInfo.at(i)._ip) + "',";
// strVal += "'" + *(vecInfo.at(i)._mac) + "',";
// strVal += "'" + *(vecInfo.at(i)._operator_org_id) + "',";
// strVal += "'" + *(vecInfo.at(i)._operator_org_name) + "',";
// strVal += "'" + *(vecInfo.at(i)._app_code) + "',";
// strVal += "'" + *(vecInfo.at(i)._object_org_ids) + "',";
// strVal += "'" + *(vecInfo.at(i)._object_org_names) + "',";
// strVal += "'" + *(vecInfo.at(i)._operator_object_type) + "',";
// strVal += "'" + *(vecInfo.at(i)._operator_object_keys) + "',";
// strVal += "'" + *(vecInfo.at(i)._operator_object_values) + "',";
// strVal += "'" + *(vecInfo.at(i)._business) + "',";
// strVal += "'" + *(vecInfo.at(i)._act) + "',";
// std::stringstream iToStr;
// std::string strResult;
// iToStr << vecInfo.at(i)._operate_result;
// iToStr >> strResult;
// strVal += "" + strResult + ",";
// strVal += "'" + *(vecInfo.at(i)._search_info) + "',";
// strVal += "'" + *(vecInfo.at(i)._content) + "',";
// strVal += "'" + *(vecInfo.at(i)._creator) + "',";
// strVal += "current_timestamp,current_timestamp";
// strVal += ")";
// if (i != vecInfo.size() - 1) {
// strVal += ", ";
// }
// //else {
// // strVal += ";";
// //}
// strSQL += strVal;
//}
//std::cout << "sql为: " << std::endl;
//std::cout << strSQL << std::endl;
int iRet = stmt->executeUpdate();
}
catch (SQLException ex) {
std::cout << ex.getMessage() << std::endl;
return -;
}
catch (...) {
return -;
}
//释放连接
conn->terminateStatement(stmt);
env->terminateConnection(conn);
env->terminateEnvironment(env);
return ;
批量插入一定要注意的事项：
stmt->setMaxIterations(count); count值必须同要插入的数据条数一样。
setMaxParamSize需要对string类型的设置最大参数的大小。 遇到的错误有：
ORA-32109: invalid column or parameter position
ORA-32132: maximum iterations cannot be changed ->setMaxIterations和setMaxParamSize引起的问题