I have been trying to insert some variables into a MySQL database, but it doesn't seem to be working. When I type the value directly into the query it works, but this is obviously not very helpful. I have typed my code below along with the erorr message, I just don't know how I can change my code to make it work. Any help you can provide would be most helpful! Please note the function is called selectCountry instead of something like insertCountry, please ignore this.

import java.sql.*;

public class Database_Interactor {
    public static Connection letConnect()  {
        Connection conn =null;
        String url,username,password = null;
        url="jdbc:mysql://localhost:3306/DatabaseName";
        username = "username";
        password = "password";
        
        try {Class.forName("com.mysql.cj.jdbc.Driver");
            conn = DriverManager.getConnection(url,username,password);
        } catch (SQLException | ClassNotFoundException e) {
            e.printStackTrace();}
        return conn;}
    
    
    void SelectCountry(String country, String color, int X, int Y) {
        Connection conn = letConnect();
        try {
            Statement stmt = conn.createStatement();
            stmt.executeUpdate("INSERT INTO Country (CountryName, Color, XPosition, YPosition)\r\n"
                      "VALUES (country,color,X,Y);");
            System.out.println("Inserted");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

java.sql.SQLSyntaxErrorException: Unknown column 'country' in 'field list'
    at [email protected]/com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
    at [email protected]/com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
    at [email protected]/com.mysql.cj.jdbc.StatementImpl.executeUpdateInternal(StatementImpl.java:1334)
    at [email protected]/com.mysql.cj.jdbc.StatementImpl.executeLargeUpdate(StatementImpl.java:2084)
    at [email protected]/com.mysql.cj.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1245)
    at Database_Interactor.SelectCountry(Database_Interactor.java:22)
    at MainClass.main(MainClass.java:8)

CodePudding user response：

Your method is not performing a select, it's performing an insert. So I would rename it. Java convention has methods start with a lowercase letter. You should be using a PreparedStatement and binding the variables for your query¹. And you aren't closing anything which is likely to leak cursors; I would use a try-with-Resources². Something like,

void insertCountry(String country, String color, int x, int y) {
    String sql = "INSERT INTO Country (CountryName, Color, XPosition, YPosition) "
              "VALUES (?, ?, ?, ?)";
    try (Connection conn = letConnect();
            PreparedStatement ps = conn.prepareStatement(sql)) {
        ps.setString(1, country);
        ps.setString(2, color);
        ps.setInt(3, x);
        ps.setInt(4, y);
        ps.executeUpdate();
        System.out.println("Inserted");
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

_{¹This is also how you avoid SQL Injection attacks. See also, xkcd 327: Exploits of a Mom.
²A connection pool (for example HikariCP or others) would likely improve performance if your application will run for an extended period by amortizing the database connection time.}

CodePudding user response：

After thinking about it for a little while, I edited my code and came up with somewhat a good solution to this problem, the code is below:

void insertCountry(String country, String color, int X, int Y) {
        Connection conn = letConnect();
        try {
            String sql = "INSERT INTO Country (CountryName, Color, XPosition, YPosition) VALUES ('" country "','" color "','" X "','" Y "');";
            System.out.println(sql);
            Statement stmt = conn.createStatement();
            stmt.executeUpdate(sql);
            System.out.println("Inserted");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }