从java对象更新/合并SQLite DB表

Here a minimal example to describe the issue:

Suppose a table is read from a SQLiteDB and store it in a Java Collection object

DB table--->Java Object


idRecord | Data   (table stored at DB)
1         One
2         Two
3         Three
4         Four

And through an sqlite jdbc library :

Map objTable = new HashMap (); //...adding some jdbc stuff, we get a copy of DBTable in objTable

Then if object is modified, thus being.

idRecord | Data   (modified table stored at objTable)    
2         Two
4         FourModified
5         Five

(id 1 and 3 were deleted, 2 remain the same, 4 modified, and 5 added)

Java Object-->DB table (Here is the question...)


How to Update/Merge the object table with the DB ?

Why I want to merge and not to simple save the object to the table ?

I think that if the table is large enough then it has no sense in writing all the records if only some of them were modified.

  • Delete the whole DBtable and in a (dangerous meanwhile) loop, walk the object to write the new table.

  • Read the DBtable in a second java obj and then compare both (with some kind of merge alghoritm) and apply the actions (ADD, DELETE, MODIFY) directly to DB. (I would acept recomendation for that comparison alghorithm)

  • EDIT: Do not create the Collection in the first place, reading and writing directly from DB, passing queries all the time through JDBC

  • Other better approach

Thanks4Reading

#0

Two statement in the same transaction. (unfortunately SQLite doesn't support ON DUPLICATE KEY UPDATE ish functionality, but hey, it is "Lite" :P)

First, INSERT OR IGNORE then UPDATE yourtable SET data=hashTableData WHERE id=hashTableId AND data != hashTableData

You might be able to identify which have been ignored. I would try and do two prepared statements, im guessing execute will return false if the ignore clause was triggered. Try it.

If thats the case, then do the update on false.

Otherwise, loop though the data twice once for each statement and commit the transition when done :)

#1

Track the records that have been modified in an ArrayList of something similar. Then when times comes to update the DB, update only the records that have been modified.

You can create a base class that does the above, then extend it for every table you have in your schema.

#2

You can implement behavior similar to the (simplified) behavior of Hibernate session cache. Your memory representation can keep a "dirty" flag that would indicate which objects need to be updated in the database and which - deleted. Depending on the algorithm you use to allocate ids (application-centric vs. database-centric) you can identify objects that were added by a special value (say, 0) or by keeping this information in the dirty flag.

One more potential enhancement, is to consider eager (read all records at once) vs. lazy (read records as needed) loading of your cache.

推荐文章

获取多个MEF目录/容器

获取多个MEF目录/容器

推荐文章

尝试使用JQuery将每个表项添加到数组中

尝试使用JQuery将每个表项添加到数组中

推荐文章

Android/Java:使用字符串作为变量

Android/Java:使用字符串作为变量

推荐文章

MVC 3 Webapp-模型绑定器

MVC 3 Webapp-模型绑定器

推荐文章

如何使用这个例子在Linq中动态地旋转

如何使用这个例子在Linq中动态地旋转

推荐文章

实体框架创建逻辑关系

实体框架创建逻辑关系

推荐文章

泛型可以改进这个设计吗?

泛型可以改进这个设计吗?

推荐文章

非泛型类型'System.Collections.IEnumerable系统'不能与类型参数一起使用

非泛型类型'System.Collections.IEnumerable系统'不能与类型参数一起使用

推荐文章

使用ajax的.val()和.attr(“value”)在clone()d但未插入的元素上返回数据的不同行为

使用ajax的.val()和.attr(“value”)在clone()d但未插入的元素上返回数据的不同行为

推荐文章

Jquery切换类和展开/折叠图像

Jquery切换类和展开/折叠图像

推荐文章

将Javascript对象转换为dom元素

将Javascript对象转换为dom元素

推荐文章

从TcpClient序列化的对象流中强制转换不同的对象?

从TcpClient序列化的对象流中强制转换不同的对象?

推荐文章

jtable上的复选框并获取java swing中相应行的值

jtable上的复选框并获取java swing中相应行的值

推荐文章

在Matlab用户界面上限制编辑框中的字符串长度

在Matlab用户界面上限制编辑框中的字符串长度

推荐文章

字符串PHP数组的匹配和拆分

字符串PHP数组的匹配和拆分

推荐文章

如何在JAXB封送期间添加XML处理指令

如何在JAXB封送期间添加XML处理指令