Trouble Updating a Table Row in Java

Question

Connection conn = SqlConnection.getConnection("jdbc:mysql://localhost:3306/stocks");
Statement statement = conn.createStatement();

    File path = new File("/Users/Zack/Desktop/JavaDB/BALANCESHEETS");
    for(File file: path.listFiles()) {
        if (file.isFile()) {
            String fileName = file.getName();
            String ticker = fileName.split("\\_")[0];
            if (ticker.equals("ASB") || ticker.equals("FRC")) {
                if (ticker.equals("ASB")) {
                    ticker = ticker + "PRD";
                }
                if (ticker.equals("FRC")) {
                    ticker = ticker + "PRD";
                }
            }

            //CSVReader reader = new CSVReader(new FileReader(file));
            //List entries = reader.readAll();
            //ArrayList<String> entry = new ArrayList<String>();

            Reader reader = new BufferedReader(new FileReader(file));
            StringBuilder builder = new StringBuilder();

            int c;
            while ((c = reader.read()) != -1) {
            builder.append((char) c);
            }

            String string = builder.toString();

            ArrayList<String> stringResult = new ArrayList<String>();

            if (string != null) {
                String[] splitData = string.split("\\s*,\\s*|\\n");
                for (int i = 0; i <splitData.length; i++) {
                    if (!(splitData[i] == null) || !(splitData[i].length() ==0)) {
                        stringResult.add(splitData[i].trim());
                    }
                }
            }

            String columnName = null;
            int yearCount = 0;
            for (int i = 0; i < stringResult.size(); i++) {
                int sL = stringResult.get(i).length();


                for (int x = 0; x < sL; x++) {
                    if (Character.isLetter(stringResult.get(i).charAt(x))) {
                        yearCount = 0;
                        System.out.println("index: " + i);
                        columnName = stringResult.get(i);
                        columnName = columnName.replace(" ", "_");
                        System.out.println(columnName);

                        i++;
                        break;
                    }
                }
                yearCount++;
                String value = stringResult.get(i);
                System.out.println("Year: " + stringResult.get(yearCount) + " Value: " + value + " Stock: " + ticker + " Column: " + columnName );
                if (!(columnName == null)) {
                String writeValues = "INSERT INTO BalanceSheet (ticker, Year, " + columnName + ") "
                                    + "VALUE ('" + ticker + "','" + stringResult.get(yearCount) + "','" + value + "')";
                String writeValues2 = "UPDATE BalanceSheet "
                                    + "SET ticker = '" + ticker + "', " 
                                        + "Year = '" + stringResult.get(yearCount) + "', "
                                        + columnName + " = '" + value + "' "
                                        + "WHERE ticker = '" + ticker + "'";
                statement.executeUpdate(writeValues2);
                }
            }

Towards the bottom of the code are two queries I tried, I'm trying to get all data organized by ticker and year into a table, "writeColumns" works but it's making a new row for every new "value" put into "columnName". My second attempt "writeColumns2" doesn't do anything.

I want to update the same row with a certain year for all values and then move onto the next year, then next ticker.

Answer 1

If I have understood your question correctly, you want to insert a row if it doesn't exists but update the values if it already does. You need to use ON DUPLICATE KEY UPDATE

String writeValues = "INSERT INTO BalanceSheet (ticker, Year, " + columnName + ") "
                                    + "VALUES (?,?,?) "
        +"ON DUPLICATE KEY UPDATE " + columnName +"=?";

Statement statement = conn.prepareStatement(writeValues);
statement.setString(1,ticker);
statement.setString(2,stringResult.get(yearCount));
statement.setString(3, value);

This will solve your immidiate problem provided you create a UNIQUE index on ticker,year

However there are lot's of other issues here.

An update for each column - Currently you are doing an insert/update for each column on the table. What you are supposed to do is to insert update all the columns at one.

You are not using prepared statements addressed in my code sample

You shouldn't be doing this at all the best way to batch process data is to use MYSQL's built in LOAD DATA INFILE command. If your data is not in a format that can be easily imported into mysql, what your Java code can do is to transform it into a format that can be. Such a code will be a lot simpler and neater than what you have now