Question

Source: https://stackoverflow.com/questions/70380362/how-to-update-database-through-csv-for-some-columns

I have a table containing columns like id, link_name, browser_title, title, content etc., and a CSV file that contains new title and browser_title values for some link _names (the file looks like -- link_name, title, browser_title). Now I need to update values for title and browser_title.

Can anyone suggest what should be the good way to do that.

Answer

The task aims to replace certain data in a database table with corresponding values in a CSV file. To use Java directly for doing this requires loading the CSV file to the database and store it as a temporary table. That’s a hassle.

It is easy to do this using Java’s open-source package SPL. You just need one line of code:

SPL offers JDBC driver to be invoked by Java. Just store the above SPL script as update.splx and invoke it in Java as you call a stored procedure:

…

Class.forName("com.esproc.jdbc.InternalDriver");

con = DriverManager.getConnection("jdbc:esproc:local://");

st = con.prepareCall("call update ()");

st.execute();

…

Or execute the SPL string within a Java program as we execute a SQL statement:

…

st = con.prepareStatement("==RDB.update@u(file(\"update_info.csv\").import@ct(),infotable,link_name,title,browser_title)");

st.execute();

…

View SPL source code.