I am working on a project where I am trying to insert values into a MySQL table. Before starting this project I verified that I had the necessary JAR file setup and tested the SQL connection. I thought I was on the right track, but I can't seem to get past these errors popping up in the console. I have a feeling I am not inserting data correctly into MySQL through Eclipse. I was hoping someone here may know of a better process or way of completing this. My source code is posted below. The file "theraven.txt" that is being read from is in the project directory.

public class DatabaseGO {

    //private static Connection connection = null;
    
    public static void main(String[] args) throws IOException {
        // TODO Auto-generated method stub
        
        FileInputStream findIt = new FileInputStream("theraven.txt");
        Scanner fileInput = new Scanner(findIt);    
        
        ArrayList<String> words = new ArrayList<String>();
        ArrayList<Integer> count = new ArrayList<Integer>();

        while (fileInput.hasNext()) {
    
            String nextWord = fileInput.next();
    
            if (words.contains(nextWord)) {
                int index = words.indexOf(nextWord);
                count.set(index,  count.get(index)  1);
            }
            else {
                words.add(nextWord);
                count.add(1);
            }
        }
        
        fileInput.close();
        findIt.close();

        for (int i = 0; i < words.size();   i) {
            Collections.sort(count, Collections.reverseOrder());
        
        //////////////////////////////
        
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        String url = "jdbc:mysql://localhost:3306/word_occurrences";
        String user = "root";
        String password = "kittylitter";
        
        String sql = "INSERT INTO word(countNumber, `countName`) VALUES(?,?)";
        
        try (Connection con = DriverManager.getConnection(url, user, password);
                PreparedStatement pst = con.prepareStatement(sql)) {

                pst.setInt(1, count.get(i));
                pst.setString(2, words.get(i));
                pst.executeUpdate();
            

        } catch (SQLException ex) {
            
            Logger lgr = Logger.getLogger(DatabaseGO.class.getName());
            lgr.log(Level.SEVERE, ex.getMessage(), ex);
        } 
    }
    }
}

This is the current errors I am getting:

SEVERE: Unknown column 'countNumber' in 'field list'
java.sql.SQLSyntaxErrorException: Unknown column 'countNumber' in 'field list'
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
    at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:916)
    at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1061)
    at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1009)
    at com.mysql.cj.jdbc.ClientPreparedStatement.executeLargeUpdate(ClientPreparedStatement.java:1320)
    at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdate(ClientPreparedStatement.java:994)
    at database.DatabaseGO.main(DatabaseGO.java:67)

Nov 09, 2022 12:00:14 AM database.DatabaseGO main

In MySQL I have a table named word and three columns named recordNumber Int (set to auto-increment), wordCount Int, and wordName VARCHAR(45). I was hoping the program would output the count of the name next to the name.

CodePudding user response：

The issue says that the table word doesn't have column countNumber. Kindly recheck your table description.

Alternatively, an insert query can run without column names, try with INSERT INTO word VALUES(?,?).

CodePudding user response：

The issue has been resolved. The problem was with the naming convention in my program. When I inserted the correct column names, it worked. I was up late working on this project, I somehow missed this. Thank you and apologies for the misprint.