如何使用ORMLite同时在表中写入多个项目 [英] How to write several item in table at the same time using ORMLite

问题描述

我在服务器和客户端制作的解决方案上使用 ORMLite.

I use ORMLite on a solution made by server and clients.

在服务器端我使用 PostgreSql，在客户端我使用 SQLite.在代码中，我使用相同的 ORMLite 方法，而不关心管理的 DB(PostgreSql 或 SQLite).

On server side I use PostgreSql, on client side I use SQLite. In code, I use the same ORMLite methods, without taking care of the DB that is managed (PostgreSql or SQLite).

让我们说:

• 表A对应A类
• 我有一个对象 A Arraylist
• 我想在数据库中插入 ArrayList 的所有项目.

今天我使用 for() 循环，我将它们一个一个插入(在事务管理器中执行).项目少的时候没问题，但现在项目越来越多，这可能不是最好的方法，也是因为我长时间锁定数据库.

Today I use a for() cycle, and I insert them one by one (doing it in Transaction Manager). When the items are few, no problem, but now the items are becaming more and this is not probably the best way, also because I lock the DB for long time.

我正在寻找一种方法来一步插入所有项目，所以要快点，不要长时间锁定数据库.我知道它应该是一种存储过程(我不是专家......).

I'm searching a way to insert all the items in one step, so to go quickly, to not lock the DB for long time. I understood that it should be a sort of Stored Procedures (I'm not expert...).

需要注意的是，有些项目可能是新的(即不存在具有相同主键id的项目)，则必须执行并插入；其他项目可能存在，因此应执行更新.

To be noted that some items could be new (that is it not exist already an item with the same primary key id), then must be performed and INSERT; other items could be existing, so it should be performed an UPDATE.

谢谢

推荐答案

我正在寻找一种方法来一步插入所有项目，所以要快点，不要长时间锁定数据库.

I'm searching a way to insert all the items in one step, so to go quickly, to not lock the DB for long time.

所以我知道有两种方法可以做到这一点:事务和禁用自动提交.如果您要插入到数据库中并且需要同时"进行所有操作，请执行以下操作:从一致性的角度来看，事务是唯一的出路.如果您只想插入和更新大量具有更高性能的记录，那么您可以禁用自动提交，执行操作，然后提交.根据数据库实现，这就是 TransactionManager 真正在做的事情.

So there are two ways to do this that I know of: transactions and disabling auto-commit. If you are inserting into the database and it needs to all happen "at once" from a consistency standpoint, transactions are the only way to go. If you just want to insert and update a large number of records with higher performance then you can disable auto-commit, do the operations, and then commit. Depending on the database implementation, this is what the TransactionManager is really doing.

我明白它应该是一种存储过程...

I understood that it should be a sort of Stored Procedures...

我根本看不出存储过程对您有什么帮助.它们不是魔法.

I don't see how stored procedures helps you at all. They aren't magic.

但现在项目越来越多，这可能不是最好的方法，也是因为我长时间锁定数据库.

but now the items are becoming more and this is not probably the best way, also because I lock the DB for long time.

我不认为有什么神奇的解决方案.如果您将大量对象推送到数据库并且您需要数据是事务性的，那么在更新期间将不得不持有锁.要意识到的一件事是 postgres 应该比 Sqlite 更好地处理这个问题.Sqlite 没有(我认为没有)行级锁定意味着整个数据库在事务期间暂停.Postgres 有一个 更成熟的锁定系统，并且在这种情况下应该具有更高的性能.这也是 Sqlite 在许多其他操作中如此之快的原因，因为它不必负担锁的复杂性.

I don't think there is a magic solution to this. If you are pushing a large number of objects to the database and you need the data to be transactional, then locks are going to be have to be held during the updates. One thing to realize is that postgres should handle this a ton better than Sqlite. Sqlite does not (I don't think) have row level locking meaning that the whole DB is paused during transactions. Postgres has a much more mature locking system and should be more performant in this situation. This is also why Sqlite is so fast in many other operations because it doesn't have to burdened with the lock complexity.

需要考虑的一件事是重新构建您的架构.尝试找出需要以事务方式插入的最小数据量.例如，也许只是对象关系需要以事务方式更改，但所有数据都可以稍后存储.例如，您可以有一个 AccountOwner 对象，它只有 2 个 id，而有关 Account 的所有信息都可以存储在交易之外.这会使您的架构更加复杂，但可能会更快.

One thing to consider is to rearchitect your schema. Try to figure out the minimal amount of data that needs to be transactionally inserted. For example, maybe just the object relationships needs to be changed transactionally but all of the data can be stored later. For example, you could have an AccountOwner object which just has 2 ids while all of information about the Account can be stored outside of the transaction. This makes your schema more complicated but maybe much faster.

希望这里有帮助.

这篇关于如何使用ORMLite同时在表中写入多个项目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！