CODEWITHSUNDEEP
javamariadb
John McCullough
John McCullough

Reputation: 37

Writing a txt file to mySQL database

I'm currently trying to write a txt file to a mySQL database through a Java program. My database connects correctly through a JDBC driver and I can create tables etc through the program. However when I try to read the text file in I get this error message

java.sql.SQLException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''FName' , 'SName' , 'DOB') VALUES ('John' , 'McCullough' , '270696')' at line 1

I can't find an error in my SQL code. Here is the rest of the code from the class. Any help would be greatly appreciated.

                try (Connection con = DriverManager.getConnection(dbUrl, dbUsername, dbPassword)) {
                FileReader file1 = new FileReader("resources/Test.txt");
                BufferedReader buffer1 = new BufferedReader(file1);
                String read;
                while ((read = buffer1.readLine()) != null) {
                    String[] row = read.split(",");
                    String fName = row[0];
                    String sName = row[1];
                    String DOB = row[2];
                    String insert = "INSERT INTO chessleague.table1 ('FName' , 'SName' , 'DOB') VALUES ('" + fName + "' , '" + sName + "' , '" + DOB + "')";

                    ps = con.prepareStatement(insert);

                    ps.executeUpdate();
                    ps.close();
                }
            } catch (Exception ex) {
                System.out.println(ex);
            }

Upvotes: 0

Views: 953

Answers (1)

markusjm
markusjm

Reputation: 2562

As mentioned in the comments, don't quote the column names.

The code heavily misuses prepared statements to execute simple SQL. The Connection Class has a createStatement() method that creates a simple statement which is meant for text form SQL commands.

Statement stmt = con.createStatement();
stmt.execute("SELECT * from test.t1");

Prepared statements expect a template that is used to create the SQL statements. Here's an example of how the insert could be done with prepared statement commands.

try (PreparedStatement ps = con.prepareStatement("INSERT INTO chessleague.table1 (FName , SName , DOB) VALUES (?, ?, ?)")) {
    ps.setString(0, fName);
    ps.setString(1, sName);
    ps.setString(2, DOB);
    ps.execute();
} catch (SQLException ex) {
    System.out.println("SQLException: " + ex.getMessage());
}

Upvotes: 1

Related Questions