How to import CSV file data into a PostgreSQL table via java code?

Question

I have a csv file which I want to import to table in postgres

The table contains 3 fields (id text, name text, geo geometry). The csv file is in the same format (3 values separate with comma).

I want to use java code in order to import the file (input.csv) into the table (tbl)

How can I do it ? Is there a query which I pass the file path to the DB ?

"Is there a query which I pass the file path to the DB" - will the file be accessible from the database server (by the PostgreSQL user; the user ID the server runs as); If so you could use COPY. — Brits
– Brits, Commented Aug 27, 2020 at 5:23

Alexander Makarov · Accepted Answer · 2020-08-27 05:28:11Z

1

You can use OpenCSV to read csv file into java object (here is an example - https://www.geeksforgeeks.org/mapping-csv-to-javabeans-using-opencsv/), and then use Java JPA Repository to insert data into database (here is an example - https://www.baeldung.com/spring-data-crud-repository-save)

answered Aug 27, 2020 at 5:28

Alexander Makarov

8865 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Nilanchala · Accepted Answer · 2024-05-28 10:40:50Z

You can create a simple Spring boot app to import the CSV data into PostgreSQL. There are two choices

Simple Implementation: Use OpenCSV reader to read the CSV content and JPA/JDBC for storing the data in the DB. This will be fine as long as the CSV files are small.
Using Spring Batch: Spring batch offers robust architecture to efficiently import content from files to DB. It comes in handy with different Reader and Writer implementations to work with different input/output sources.

It is very efficient for processing large data files and offers chunk-based processing, transaction management, etc.

I have written a detailed blog post about this here

Also, there is a video tutorial available on this topic here https://youtu.be/XBW3D1f6CAA