sqlite-insert

一次插入多行

sql = "INSERT INTO t_outlet (id,parentId) VALUES (null,?),";
for (int i = 0; i < 7; ++i)
{
    sql += " (null,?),";
}
sql.remove(sql.length() - 1, 1);
for (int i = 0; i < 8; ++i)
{
    m_pDb->setSqlParam(dbID);
}
if (m_pDb->exec(sql.toStdWString().c_str()) || m_pDb->changes() != 8)
{
    m_lastErrorString = m_pDb->getLastError();
    return -1;
}

替换

先删除，后插入，key 会有增长

The idea of the REPLACE statement is that when a UNIQUE or PRIMARY KEY constraint violation occurs, it does the following:

First, delete the existing row that causes a constraint violation.
Second, insert a new row.
In the second step, if any constraint violation e.g., NOT NULL constraint occurs, the REPLACE statement will abort the action and roll back the transaction.

REPLACE INTO table(column_list)
VALUES(value_list);

upsert

wstring sql = L"INSERT INTO t_geometric_coeff (f_id,f_name,f_notes,f_value) VALUES  (?,?,?,?) ON CONFLICT (f_id) DO UPDATE SET f_name=excluded.f_name,f_notes=excluded.f_notes,f_value=excluded.f_value where f_id=excluded.f_id;";
int itemCount = m_ctrl_list_geometric_coeff.GetItemCount();
// 用来保存新插入数据的id，如果没抛异常就设置
vector<pair<int64_t,int64_t>> vSetDataIdxs;
try {
    m_spSqlite->beinTransaction();
    vector<CString> vStr;
    add_count = 0;
    
    for (size_t i = 0; i < itemCount; ++i)
    {
        size_t bindIndex = 1;
        dwID = m_ctrl_list_geometric_coeff.GetItemData(i);
        m_spSqlite->statement(sql);
        if (dwID != 0)
        {
            m_spSqlite->bind((add_count * 4 + bindIndex), dwID);
        }
        else
        {
            m_spSqlite->bind((add_count * 4 + bindIndex));
        }
        // 3 param need bind
        for (size_t j = 1; j < m_ctrl_list_geometric_coeff.GetHeaderCtrl()->GetItemCount(); ++j)
        {
            ++bindIndex;
            m_spSqlite->bind((add_count * 4) + bindIndex, (m_ctrl_list_geometric_coeff.GetItemText(i, j).GetBuffer()));
        }
        int iRet = m_spSqlite->exec();
        if (iRet != 1)
        {
            string strErr = fmt::format("insert to database failed. iRet={},add_count={}", iRet, add_count);
            g_logger->error(strErr);
            AfxMessageBox(utils::s2w(strErr).c_str());
        }
        vSetDataIdxs.push_back(std::make_pair(i,m_spSqlite->getLastRowId()));
        
    }
    m_spSqlite->endTransaction();
    for (auto&it:vSetDataIdxs)
    {
        m_ctrl_list_geometric_coeff.SetItemData(it.first, it.second);
    }
}
catch (std::exception& e)
{
    m_spSqlite->rollbackTransaction();
    string strErr = fmt::format("save geometric coeff failed. err={}", e.what());
    g_logger->error(strErr);
    AfxMessageBox(utils::s2w(strErr).c_str());
}