在填充ObservableList时，是否必须从我的数据库加载所有记录？ [英] In populating an ObservableList, do I have to load ALL the records from my database?

问题描述

所以我将我的Swing Java数据库应用程序移植到Java FX（这里仍然是初学者，我最近刚刚学习了FXML和MVC模式的基础知识，所以请耐心等待。）

我打算将现有数据库中的数据加载到学生ObservableList，这样我就可以在TableView上显示它，但是在我原来的Swing代码上，我有一个搜索TextField，当用户点击时在按钮上或按Enter键，程序：

1. 执行搜索特定记录的SQLite命令，并检索RecordSet。


2. 根据RecordSet内容创建DefaultTableModel


3. 并将TableModel抛出到JTable。

然而，Java FX是一个完全不同的野兽（或者至少对我来说似乎是这样 - 不要误会我的意思，我喜欢Java FX：D）所以我不知道该怎么做那么，我的问题是，我是否必须加载数据库中的所有学生，然后使用一些Java代码来过滤o那些不符合搜索条件的学生（并在搜索文本为空白时显示所有学生），或者我仍然使用SQLite过滤和检索记录（这意味着我需要清除列表，然后每次搜索时添加学生执行，也许它也会搞乱绑定？也许这种方法会有速度惩罚吗？除此之外，它还将重置当前选择的记录，因为我清除了列表 - 基本上，糟糕的UI设计并将对可用性产生负面影响）

取决于正确的方法，还有一个后续问题（对不起，即使在谷歌搜索后我也找不到这些答案）：

• 如果我从数据库中获取所有学生并在Java中实现搜索功能，不会占用更多的RAM，因为我将所有数据库数据存储在RAM中，而不仅仅是搜索到的数据库？我的意思是，当然，即使是我的低端笔记本电脑也有4GB内存，但使用更多内存的感觉应该让我感到内疚LOL


• 如果我选择只更新内容ObservableList每次执行新的搜索时，它会搞乱绑定吗？我是否必须再次设置绑定？如何在添加新内容之前清除ObservableList的内容？

我还想到只设置所选的表项到匹配搜索字符串的第一条记录，但我认为它很难使用，因为每次搜索只能突出显示一条记录。即使我们突出显示多行，也很难浏览所有选定的项目。

请给我正确的方法，而不是简单的方式。这是我第一次实现模式（MVC或我实际上在做MVP，我不知道），我意识到我之前的程序是多么难以维护和丑陋，因为我使用了自己的风格。这是一个相对较大的项目，我需要支持和改进好几年，所以拥有干净的代码并以正确的方式做事情应该有助于维护这个程序的功能。

非常感谢您的帮助，我希望在提出这些问题时，我不会成为一个甚至不能谷歌的愚蠢的人。请在这里忍受。

解决方案

基本设计权衡

你当然，你可以用你所描述的方式来做这件事。基本权衡是：

1. 如果从数据库加载所有内容，并用Java过滤表，则使用更多内存（尽管不是正如您所想的那样，如下所述）


2. 如果您从数据库中过滤并在每次用户更改过滤器时重新加载，则显示数据时会有更大的延迟（延迟） ，因为新的查询将在数据库上执行，数据库与应用程序之间的网络通信（通常是最大的瓶颈）（尽管还有其他）。

数据库访问和并发

通常，您应该在后台线程上执行数据库查询（请参阅使用线程发出数据库请求）;如果您经常进行数据库查询（即通过数据库进行过滤），这会变得很复杂，并且在后台任务运行时经常禁用UI中的控件。

TableView 设计和内存管理

JavaFX TableView 是虚拟化的控制。这意味着可视组件（单元格）仅为可见元素创建（加上可能还有少量缓存）。然后在用户滚动时重复使用这些单元格，根据需要显示不同的项目。可视组件通常具有相当大的内存消耗（它们具有数百种属性 - 颜色，字体属性，尺寸，布局属性等等 - 其中大多数具有CSS表示），因此限制创建的数量可以节省大量内存，并且表视图的可见部分的内存消耗基本上是不变的，无论表的后备列表中有多少项。

一般内存消耗计算

构成表格支持列表的 items 可观察列表仅包含数据：不难估计内存量由给定大小的列表消耗。 String s每个字符使用2个字节，加上一个小的固定开销， double s使用8个字节， int s使用4个字节等。如果在JavaFX属性中包装字段（建议使用），则每个字节都会有几个字节的开销;每个对象的开销约为16字节，引用本身通常最多使用8个字节。因此，存储一些字符串字段的典型 Student 对象通常会占用内存中几百字节的数量级。 （当然，如果每个都有一个与之关联的图像，例如，它可能会更多。）因此，如果您加载，比如说来自数据库的100,000名学生，则会耗尽10-100MB的RAM，这在大多数个人计算机系统上都是可以管理的。

粗略的一般指导方针

通常，对于那种你描述的应用程序，我建议加载数据库中的内容并在内存中过滤它。在我通常的工作领域（基因组学）中，我们有时需要10或100万个实体，这是不可能完成的。 （例如，如果您的数据库包含所有在美国公立学校注册的学生，你可能会遇到类似的问题。）

但是，作为一般的经验法则，对于一个正常的对象（即一个没有的对象）大型数据对象，例如与之关联的图像），在您严重拉伸用户机器的内存容量之前，您的桌面大小将非常大，以便用户可以轻松管理（即使使用过滤）。

用Java过滤表（内存中的所有对象）

过滤代码非常简单。简而言之，您将所有内容加载到 ObservableList 中，并将 ObservableList 包装在 FilteredList 。 FilteredList 包装源列表和 Predicate ，返回 true 是一个项目应该通过过滤器（包含在内）还是false如果它被排除在外。

所以您使用的代码片段可能如下所示：

  ObservableList< Student> allStudents = loadStudentsFromDatabase（）; 
 FilteredList< Student> filteredStudents = new FilteredList<>（allStudents）; 
 studentTable.setItems（filteredStudents）; 
  

然后你可以根据文本字段修改谓词，代码如下：

  filterTextField.textProperty（）。addListener（（obs，oldText，newText） - > {
 if（newText.isEmpty（）） {
 //无过滤：
 filteredStudents.setPredicate（student  - > true）; 
} else {
 filteredStudents.setPredicate（student  - > 
 //你需要什么逻辑：
 student.getFirstName（）。contains（newText）|| student.getLastName（）。contains（newText））; 
} 
}）; 
  

本教程对过滤（和排序）表进行了更彻底的处理。

评论实现通过查询过滤

如果您不想从数据库加载所有内容，则完全跳过过滤后的列表。查询数据库几乎肯定不能足够快地过滤（使用新的数据库查询）作为用户输入，因此您需要一个更新按钮（或文本字段上的动作侦听器），它重新计算新的过滤数据。您可能也需要在后台线程中执行此操作。您不需要在表的列上设置新的 cellValueFactory s（或 cellFactory s），或重新加载列;您只需在数据库查询完成时调用 studentTable.setItems（newListOfStudents）; 。

So I'm porting my Swing Java database application to Java FX (still a beginner here, I recently just learned the basics of FXML and the MVC pattern, so please bear with me).

I intend to load the data from my existing database to the "students" ObservableList so I can show it on a TableView, but on my original Swing code, I have a search TextField, and when the user clicks on a button or presses Enter, the program:

1. Executes an SQLite command that searches for specific records, and retrieves the RecordSet.
2. Creates a DefaultTableModel based on the RecordSet contents
3. And throws that TableModel to the JTable.

However, Java FX is a completely different beast (or at least it seems so to me--don't get me wrong, I love Java FX :D ) so I'm not sure what to do.

So, my question is, do I have to load ALL the students in the database, then use some Java code to filter out students that don't fit the search criteria (and display all students when the search text is blank), or do I still use SQLite in filtering and retrieving records (which means I need to clear the list then add students every time a search is performed, and maybe it will also mess up with the bindings? Maybe there will be a speed penalty on this method also? Besides that, it will also reset the currently selected record because I clear the list--basically, bad UI design and will negatively impact the usability)

Depending on the right approach, there is also a follow-up question (sorry, I really can't find the answer to these even after Googling):

• If I get ALL students from database and implement a search feature in Java, won't it use up more RAM than it should, because I am storing ALL the database data in RAM, instead of just the ones searched for? I mean, sure, even my lowly laptop has 4GB RAM, but the feeling of using more memory than I should makes me feel somewhat guilty LOL
• If I choose to just update the contents of the ObservableList every time a new search has been performed, will it mess up with the bindings? Do I have to set up bindings again? How do I clear the contents of the ObservableList before adding the new contents?

I also have the idea of just setting the selected table item to the first record that matches the search string but I think it will be difficult to use, since only one record can be highlighted per search. Even if we highlight multiple rows, it'd be difficult to browse all selected items.

Please give me the proper way, not the "easy" way. This is my first time implementing a pattern (MVC or am I actually doing MVP, I don't know) and I realized how unmaintainable and ugly my previous programs are because I used my own style. This is a relatively big project that I need to support and improve for several years so having clean code and doing stuff the right way should help in maintaining the functionality of this program.

Thank you very much in advance for your help, and I hope I don't come off as a "dumb person who can't even Google" in asking these questions. Please bear with me here.

解决方案

Basic design tradeoffs

You can, of course, do this either of the ways you describe. The basic tradeoffs are:

1. If you load everything from the database, and filter the table in Java, you use more memory (though not as much as you might think, as explained below)
2. If you filter from the database and reload every time the user changes the filter, there will be a bigger latency (delay) in displaying the data, as a new query will be executed on the database, with (usually) network communication between the database and the application being the biggest bottleneck (though there are others).

Database access and concurrency

In general, you should perform database queries on a background thread (see Using threads to make database requests); if you are frequently making database queries (i.e. filtering via the database), this gets complex and involves frequently disabling controls in the UI while a background task is running.

TableView design and memory management

The JavaFX TableView is a virtualized control. This means that the visual components (cells) are created only for visible elements (plus, perhaps, a small amount of caching). These cells are then reused as the user scrolls around, displaying different "items" as required. The visual components are typically quite memory-consumptive (they have hundreds of properties - colors, font properties, dimensions, layout properties, etc etc - most of which have CSS representations), so limiting the number created saves a lot of memory, and the memory consumption of the visible part of the table view is essentially constant, no matter how many items are in the table's backing list.

General memory consumption computations

The items observable list that forms the table's backing list contains only the data: it is not hard to ballpark-estimate the amount of memory consumed by a list of a given size. Strings use 2 bytes per character, plus a small fixed overhead, doubles use 8 bytes, ints use 4 bytes, etc. If you wrap the fields in JavaFX properties (which is recommended), there will be a few bytes overhead for each; each object has an overhead of ~16 bytes, and references themselves typically use up to 8 bytes. So a typical Student object that stores a few string fields will usually consume of the order of a few hundred bytes in memory. (Of course, if each has an image associated with it, for example, it could be a lot more.) Thus if you load, say 100,000 students from a database, you would use up of the order of 10-100MB of RAM, which is pretty manageable on most personal computer systems.

Rough general guidelines

So normally, for the kind of application you describe, I would recommend loading what's in your database and filtering it in memory. In my usual field of work (genomics), where we sometimes need 10s or 100s of millions of entities, this can't be done. (If your database contains, say, all registered students in public schools in the USA, you may run into similar issues.)

As a general rule of thumb, though, for a "normal" object (i.e. one that doesn't have large data objects such as images associated with it), your table size will be prohibitively large for the user to comfortably manage (even with filtering) before you seriously stretch the memory capacity of the user's machine.

Filtering a table in Java (all objects in memory)

Filtering in code is pretty straightforward. In brief, you load everything into an ObservableList, and wrap the ObservableList in a FilteredList. A FilteredList wraps a source list and a Predicate, which returns true is an item should pass the filter (be included) or false if it is excluded.

So the code snippets you would use might look like:

ObservableList<Student> allStudents = loadStudentsFromDatabase();
FilteredList<Student> filteredStudents = new FilteredList<>(allStudents);
studentTable.setItems(filteredStudents);

And then you can modify the predicate based on a text field with code like:

filterTextField.textProperty().addListener((obs, oldText, newText) -> {
    if (newText.isEmpty()) {
        // no filtering:
        filteredStudents.setPredicate(student -> true);
    } else {
        filteredStudents.setPredicate(student -> 
            // whatever logic you need:
            student.getFirstName().contains(newText) || student.getLastName().contains(newText));
    }
});

This tutorial has a more thorough treatment of filtering (and sorting) tables.

Comments on implementing "filtering via queries"

If you don't want to load everything from the database, then you skip the filtered list entirely. Querying the database will almost certainly not work fast enough to filter (using a new database query) as the user types, so you would need an "Update" button (or action listener on the text field) which recomputed the new filtered data. You would probably need to do this in a background thread too. You would not need to set new cellValueFactorys (or cellFactorys) on the table's columns, or reload the columns; you would just call studentTable.setItems(newListOfStudents); when the database query finished.

这篇关于在填充ObservableList时，是否必须从我的数据库加载所有记录？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！