Delete specific rows from an Access table using Jackcess

Question

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?

For example get all the rows from the table where id is in 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.

score 1 · Answer 1 · answered May 04 '15 at 15:30

1

Jackcess provides very limited functionality for querying the data, so your best option is to go through the table with an iterator (or streams).

for(Row r : myTable)
    ; // perform any filtering based on rows here

answered May 04 '15 at 15:30

Kayaman

72,141
5
83
121

yeah but what after this how can I write the query ? like for each row if id = component_id return it – Ragnar May 04 '15 at 15:31
You don't know how to write an if clause and add to a list based on that? – Kayaman May 04 '15 at 15:33
My aim is to delete the data from the table and for that I need to write a delete query – Ragnar May 04 '15 at 15:34
Why didn't you say so. You can't do that with Jackcess. You'll need to create a new table and leave the missing values out. – Kayaman May 04 '15 at 15:36
if we can do something like this ResultSet rows = source.createStatement().executeQuery("select * from " + table.getName() + " ;"); why not delete? – Ragnar May 04 '15 at 15:37
Mostly because what you described is not Jackcess, but JDBC. – Kayaman May 04 '15 at 15:40
You have no idea what you're doing, do you? I recommend reading the Jackcess documentation. Then maybe the JDBC documentation. – Kayaman May 04 '15 at 15:42
No I dont have, Thanks for the reply – Ragnar May 04 '15 at 15:42
Well, my answer is still accurate. You can't use JDBC with Jackcess and Jackcess doesn't provide delete queries. So read-filter-write is the way to go. – Kayaman May 04 '15 at 15:46
@wearybands If you want to use SQL then you might want to give [UCanAccess](http://ucanaccess.sourceforge.net/site.html) a try. For more information, see the question [here](http://stackoverflow.com/q/21955256/2144390). – Gord Thompson May 05 '15 at 08:39
1

*"You can't [delete rows] with Jackcess."* - Actually, you can delete rows using Jackcess. (Ref: [here](http://jackcess.sourceforge.net/apidocs/com/healthmarketscience/jackcess/Table.html#deleteRow(com.healthmarketscience.jackcess.Row))) – Gord Thompson May 05 '15 at 08:43
@GordThompson You're right! I was actually looking for that, but the documentation is poor at best. Can't believe I missed that from the javadoc... – Kayaman May 05 '15 at 09:00
I have an access database and i want to delete rows from a table where the id is in a list – Ragnar May 05 '15 at 09:13
@wearybands Well you're in luck. I showed you how to iterate the table, and `Gord Thompson` managed to find the delete method from the javadocs. Your task is to compare the row to your list of ids, and perform the deletion. (Your list contains `Component`s, why is it named `enabledComponentIds`?) – Kayaman May 05 '15 at 09:21
My list contains components but for each component I have its id , and this id is also stored in t_objects table, how can I select the rows from the table where id IN component_id. – Ragnar May 05 '15 at 09:29

Gord Thompson · Accepted Answer · 2015-05-05T10:14:42.130

1

Given a table named "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:

// 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.

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:

// 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);
}

edited May 05 '15 at 10:14

answered May 05 '15 at 09:34

Gord Thompson

116,920
32
215
418

What if the object_id is not the primary key? – Ragnar May 05 '15 at 09:35
Is the column indexed? – Gord Thompson May 05 '15 at 09:37
How can I get all these rows where id is in componetsIds list instead of deleting them – Ragnar May 05 '15 at 12:50
I'm not entirely sure what you mean by "get all these rows". Certainly you could create an `ArrayList` and then add the Row objects to the list as you find them. – Gord Thompson May 05 '15 at 13:01
Ok let me explain , let suppose I got a few rows from t_object table where the id was in componentsIds , now each of these rows also have a package_id and I want to delete the rows from t_package table for the rows I got from t_object... I will update my code – Ragnar May 05 '15 at 13:08
1

Same idea. Look for `t_object` rows with those specific id values in your `componentsIds` List. When you find one, add the `package_id` from that row to a `packageIDs` List. Then when you're finished processing the `t_object` table you just do the same thing using the `t_package` table and your `packageIds` List. – Gord Thompson May 05 '15 at 14:17
exactly this is what I did :) Thanks – Ragnar May 05 '15 at 14:33

Delete specific rows from an Access table using Jackcess

2 Answers2