MySQL "LOAD DATA IN File" using JDBC Connector/J

Question

I am trying to use JDBC to load CSV file into mysql 5.8. I am using the Connector/J API for JDBC. Code snippet is shown below. After the execution of this code I want to get the count of records inserted/updated. How can I get that count? I have to get the details in the java program

import com.mysql.cj.jdbc.*;

import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

public class PricingSynch {

    String host = "localhost";
    String port = "3310";
    String userid = "pricing";
    String password = "pricing";
    String uploadDirectory = "C:\\\\ProgramData\\\\MySQL\\\\MySQL Server 5.7\\\\Uploads\\\\";
    //String uploadDirectory = null;
    String uploadFileExtension = ".csv";

    String baseQuery = "LOAD DATA  INFILE '"+ uploadDirectory +"{fileName}' REPLACE INTO TABLE prc.{dbTable} FIELDS TERMINATED BY ',' LINES TERMINATED BY \'\\n' IGNORE 1 LINES;" ;

        JdbcConnection conn = null;
        JdbcStatement stmt = null;
        ResultSet rs = null;

        public PricingSynch(String host, String port, String userid, String password, String folder) throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException {

            this.host = host;
            this.port = port;
            this.userid = userid;
            this.password = password;
            this.uploadDirectory = folder;
            dbConnection();
            runQueries(); 

        }

        private void runQueries() throws SQLException {
            Utils util = new Utils(uploadDirectory);
            String fileNames [] = util.getFiles();
            String dbTables [] = util.getTableNames();

            for (int i=0;i<fileNames.length;i++) {

                String sql = baseQuery.replace("{fileName}", fileNames[i]);
                sql = sql.replace("{dbTable}", dbTables[i]);
                System.out.println(sql);
                stmt.executeUpdate(sql);

                /*
                while(rs.next()) {

                System.out.println(rs.getString(1));
                System.out.println(rs.getString(2));
                }
                */
                //System.out.println(stmt.getUpdateCount());
                //System.out.println(stmt.);
                //System.out.println(rs.);
                //System.out.println(stmt.);
            }
        }

    public static void main(String[] args) throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException {
        // TODO Auto-generated method stub

        if (args.length == 0) {

            new PricingSynch("localhost", "3310", "root", "pricing", "C:\\ProgramData\\MySQL\\MySQL Server 5.7\\Uploads");
        }

        if (args.length == 4) {

            new PricingSynch(args[0], args[1], args[2], args[3], args[4]);
        }
    }

    private void dbConnection() throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException {

        Class.forName("com.mysql.cj.jdbc.Driver").newInstance();
        conn =(JdbcConnection) DriverManager.getConnection("jdbc:mysql://"+host+"/world?" +"user="+userid+"&password="+password);
        stmt = (JdbcStatement)conn.createStatement();
        //stmt.getGeneratedKeys().getMetaData();
    }
}

Possible duplicate of [Return number of rows affected by SQL UPDATE statement in Java](https://stackoverflow.com/questions/2571915/return-number-of-rows-affected-by-sql-update-statement-in-java) — Kaii, Jan 28 '19 at 17:55

score 0 · Answer 1 · answered Jan 28 '19 at 17:57

0

use int affectedRows = stmt.executeUpdate();

answered Jan 28 '19 at 17:57

Derviş Kayımbaşıoğlu

28,492
4
50
72

SQL command will either do an insert or update? Is there any mechanism by which i can get two different count i.e. number of records inserted and number of records updated.I do not want to execute any additional queries – Shailender Jain Jan 29 '19 at 02:31
AFAIK No. I also have checked the official documentation and there isn't – Derviş Kayımbaşıoğlu Jan 29 '19 at 06:37

MySQL "LOAD DATA IN File" using JDBC Connector/J

1 Answers1