I am using the Jackcess API with an Access database. I open the database and get a specific table. How can I get the data (rows) from this table which matches a list of ids?
我正在使用带有Access数据库的Jackcess API。我打开数据库并获得一个特定的表。如何从此表中获取与id列表匹配的数据(行)?
For example get all the rows from the table where id is in List.
例如,获取表中id为List的所有行。
private List<Component> disabledComponentsIds;
private Database db = null;
db = Database.open(new File(target), false, false);
Table table = db.getTable("t_object");
Table packages = db.getTable("t_package");
for(Map<String, Object> r : table){
if(disabledComponentsIds.contains(r.get("ea_guid"))){
r.get("package_id");
//Delete row from t_package table where id = r.get("package_id")
}
}
In this particular case I want to delete the rows.
在这种特殊情况下,我想删除行。
2 个解决方案
#1
Given a table named "t_object" ...
给定一个名为“t_object”的表...
object_id object_name
--------- -----------
1 alpha
2 bravo
3 charlie
4 delta
5 echo
... where "object_id" is the primary key, you could delete specific rows like so:
...“object_id”是主键,您可以删除特定的行,如下所示:
// test data
ArrayList<Integer> enabledComponentsIds = new ArrayList<>();
enabledComponentsIds.add(2);
enabledComponentsIds.add(3);
String dbFileSpec = "C:/Users/Public/jackcessTest.mdb";
try (Database db = DatabaseBuilder.open(new File(dbFileSpec))) {
Table t = db.getTable("t_object");
for (int id : enabledComponentsIds) {
Row r = CursorBuilder.findRowByPrimaryKey(t, id);
if (r != null) {
t.deleteRow(r);
}
}
} catch (Exception e) {
e.printStackTrace(System.err);
}
That will delete the rows where "object_id" is 2 or 3.
这将删除“object_id”为2或3的行。
Edit:
If the column is not indexed then you'll have to iterate through each row (as Kayaman suggested) and see if its column value is contained in the list:
如果该列未编入索引,那么您将必须遍历每一行(如Kayaman所建议的那样)并查看其列值是否包含在列表中:
// test data
ArrayList<Integer> enabledComponentsIds = new ArrayList<>();
enabledComponentsIds.add(2);
enabledComponentsIds.add(3);
String dbFileSpec = "C:/Users/Public/jackcessTest.mdb";
try (Database db = DatabaseBuilder.open(new File(dbFileSpec))) {
Table t = db.getTable("t_object");
for (Row r : t) {
if (enabledComponentsIds.contains(r.getInt("object_id"))) {
t.deleteRow(r);
}
}
} catch (Exception e) {
e.printStackTrace(System.err);
}
#2
Jackcess provides very limited functionality for querying the data, so your best option is to go through the table with an iterator (or streams).
Jackcess为查询数据提供了非常有限的功能,因此最好的选择是使用迭代器(或流)来遍历表。
for(Row r : myTable)
; // perform any filtering based on rows here
#1
Given a table named "t_object" ...
给定一个名为“t_object”的表...
object_id object_name
--------- -----------
1 alpha
2 bravo
3 charlie
4 delta
5 echo
... where "object_id" is the primary key, you could delete specific rows like so:
...“object_id”是主键,您可以删除特定的行,如下所示:
// test data
ArrayList<Integer> enabledComponentsIds = new ArrayList<>();
enabledComponentsIds.add(2);
enabledComponentsIds.add(3);
String dbFileSpec = "C:/Users/Public/jackcessTest.mdb";
try (Database db = DatabaseBuilder.open(new File(dbFileSpec))) {
Table t = db.getTable("t_object");
for (int id : enabledComponentsIds) {
Row r = CursorBuilder.findRowByPrimaryKey(t, id);
if (r != null) {
t.deleteRow(r);
}
}
} catch (Exception e) {
e.printStackTrace(System.err);
}
That will delete the rows where "object_id" is 2 or 3.
这将删除“object_id”为2或3的行。
Edit:
If the column is not indexed then you'll have to iterate through each row (as Kayaman suggested) and see if its column value is contained in the list:
如果该列未编入索引,那么您将必须遍历每一行(如Kayaman所建议的那样)并查看其列值是否包含在列表中:
// test data
ArrayList<Integer> enabledComponentsIds = new ArrayList<>();
enabledComponentsIds.add(2);
enabledComponentsIds.add(3);
String dbFileSpec = "C:/Users/Public/jackcessTest.mdb";
try (Database db = DatabaseBuilder.open(new File(dbFileSpec))) {
Table t = db.getTable("t_object");
for (Row r : t) {
if (enabledComponentsIds.contains(r.getInt("object_id"))) {
t.deleteRow(r);
}
}
} catch (Exception e) {
e.printStackTrace(System.err);
}
#2
Jackcess provides very limited functionality for querying the data, so your best option is to go through the table with an iterator (or streams).
Jackcess为查询数据提供了非常有限的功能,因此最好的选择是使用迭代器(或流)来遍历表。
for(Row r : myTable)
; // perform any filtering based on rows here