Java Insert multi row data from file.txt to table of database

Question

I have a text file consisting of several lines. I want to add the whole lines to the table of database. Before it is inserted to table, it should be substring to get fields value of database table. I think my code (Query) is not good for big data. I know there is other way to do that condition.

public class ReaderFilesData {
    LinkedList<String> listFiles = new LinkedList<String>();
    private Path path = Paths.get("src/FilesDownloaded/");
    DataTRX dataTRX = new DataTRX();

    public void readFiles() {
        File[] listFile = new File(path.toString()).listFiles();
        for (File file : listFile) {
            if (file.isFile()) {
                listFiles.add(file.getName());
            }
        }
        System.out.println("Total Files : " +listFiles.size());
    }

    public void readData() {
        Path pathsourceFile;

        String line;
        BufferedReader reader;
        for (int i=0; i<listFiles.size(); i++) {
            try {
                String fileName = listFiles.get(i);
                System.out.println("FileName : " +fileName);
                pathsourceFile = Paths.get("src/FilesDownloaded/"+fileName+"");
                reader = new BufferedReader(new FileReader(pathsourceFile.toString());
                while ((line = reader.readLine())!=null) {
                    int startPoint = line.lastIndexOf(';')+1;
                    String valueLine = new String(line.substring(startPoint));
                    System.out.println("Transaction data : " +valueLine);
                    dataTRX.setId(valueLine.substring(0,2));
                    dataTRX.setAmount(Integer.parseInt(valueLine.substring(2, 10)));
                    dataTRX.setDesc(valueLine.substring(10, 18));

                    System.out.println("getId      : " + dataTRX.getId());
                    System.out.println("getAmount       : " + dataTRX.getAmount());
                    System.out.println("getDesc   : " + dataTRX.getDesc());

                    importData(dataTRX.getId(), 
                                dataTRX.getAmount(),
                                dataTRX.getDesc(),

                }
                reader.close();
            } catch (Exception e) {
                e.getMessage();
            }
        }
    }

    public void importData(String id, int amount, String discount ) {

    String insertData = "INSERT INTO tbl_trx (id, amount, desc) "
        + "VALUES (?,?,?)";

    try {
        try (PreparedStatement ps = GeneralRules.conn.prepareStatement(insertData)) {

            ps.setString(1, id);
            ps.setInt(2, amount);
            ps.setString(4, desc);
            ps.executeUpdate();
            System.out.println("Data successfully update to database!!!\n");
            ps.close();
        }

    } catch (Exception e) {
        e.getMessage();
    }
}

This is example data of file.txt

• 320000000200000001
• 2G0000000500000002
• AB0000001500000001

I do substring data base on line above :

• substring id,amount,discount (32,00000002,00000001)
• substring id,amount,discount (2G,00000005,00000002)
• substring id,amount,discount (AB,00000015,00000001)

Answer 1

Your code seems good to me. But If I would have written it, below optimization/replacement, I would have done

1) Use List instead of LinkedList in variable declaration and remove generic String from reference point. Something like

List<String> listFiles = new LinkedList<>();

Link for more explanation on this

2) Similar to using try with resource you did for PreparedStatement, I would do the same for BufferedReader. This would remove the need to close the `BufferedReader' in the end

try (BufferedReader reader = new BufferedReader(new FileReader(pathsourceFile.toString())))

Link for more explanation on this

3) Because you have used try with resource for PreparedStatement, there is no need to have ps.close(), because preparedstatement implements AutoCloseable. So try with resouce will take care of it

4) Instead of e.getMessage(), I would have used e.printStackTrace() because it would give me more information about the error

As far as your use of sub-string is concerned, I would have used it, or would have use regex to split the string.

If number of rows to be inserted are more, which I think is your case, instead of calling executeUpdate() everytime, go with Batch mode. i.e add statements to PreparedStatement batch using addBatch() and execute in one go with executeBatch()