Retrieve specific data from SQLIte database in Java

Question

I have a 300GB sqlite database with over billions of records. It is stored locally on a machine.

I want to search for data in the sqlite database and return the row if it is found. I want to retrieve data from this sqlite database based on values from the csv file.

    String csvfile = "C:/documents/parsed - Copy.csv";
    String line = "";
    String csvSplitBy = ",";
    BufferedReader br = new BufferedReader(new FileReader(csvfile));

    String sql = "select substr(hex(column1),5,12),column2,colum3 from table1 where substr(hex(column1),5,12) = ?";
    try (Connection conn = this.connect();
            PreparedStatement stmt = conn.prepareStatement(sql)){
        while ((line = br.readLine()) != null) {
            String[] cols = line.split(csvSplitBy);
            stmt.setString(1, cols[1]);
            ResultSet rs = stmt.executeQuery()
            if (rs.next()) {
                System.out.println("found");}
            else
               System.out.println("not found");

How can I search the database based on values from a separate smaller csv file and not take forever?

have a look at https://stackoverflow.com/questions/13514509/search-sqlite-database-all-tables-and-columns — guleryuz, Aug 30 '18 at 12:56
Have you investigated so-called [function based indexes](https://www.sqlite.org/expridx.html)? — Jeff Holt, Aug 30 '18 at 18:47

score 0 · Accepted Answer · answered Aug 30 '18 at 18:42

if it is not a one time operation you may choose to add one more column to table1 say column1_1

alter table table1 add column column1_1;

then populate column1_1 with values substr(hex(column1),5,12)

update table1 set column1_1 = substr(hex(column1),5,12);

then change your lookups sql as:

String sql = "select column1_1,column2,colum3 from table1 where column1_1 = ?";

this way your lookup will be much more faster

Retrieve specific data from SQLIte database in Java

1 Answers1