Getting Java mysql SQL Syntax error but my query seems normal

Question

I am developing a simple java mysql based application and during data insertion into the database I'm getting an SQL error mentioned below.

Here is my code:

public DBConnection() {
    try {
        Class.forName("com.mysql.jdbc.Driver");
        con = DriverManager.getConnection("jdbc:mysql://localhost:3306/myDatabase?useUnicode=true&useLegacyDatetimeCode=false&serverTimezone=Turkey", "root", "");

        st = con.createStatement();

        System.out.println("CONNECTED!");

    } catch (Exception e) {
        System.out.println("Error  : " + e);
    }
}


public void addCustomer(String name, String surname, String company, String adress, String adressTwo){

  String addQuery = "insert into musteri (name,surname,company,adress,adressTwo) values (?,?,?,?,?)" ;

  try {
      st.executeUpdate(addQuery);
  System.out.println("Data Added");
  } catch (Exception e) {
      System.out.println("Error occured when adding value to database : " + e );
  }
}

Here is my java main method that add's the data:

public static void main(String[] args) {
    // TODO code application logic here


    Customers c1 = new Customers();

    c1.setIsim("test");
    c1.setSoyisim("test");
    c1.setSirket("test");
    c1.setAdres("test");
    c1.setIletisim("test");

    DBConnection db = new DBConnection();

    db.addCustomer(c1.isim, c1.soyisim, c1.sirket, c1.adres, c1.iletisim);
}

The error I'm getting is:

Error occured when adding value to database : java.sql.SQLSyntaxErrorException: 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 ''insert into musteri (ad,soyad,sirket,adres,iletisim) values (?,?,?,?,?)'' at line 1

Also I would recommend to create the statement as a local variable in the method rather than re-using some class member or global variable — Joakim Danielson
– Joakim Danielson, Commented Mar 3, 2019 at 17:31
Yes i set prepared st. Btw when i add normal values instead of ? its working properly. — krky
– krky, Commented Mar 3, 2019 at 17:33
Show the complete code for addCustomer including where you set the parameters — Joakim Danielson
– Joakim Danielson, Commented Mar 3, 2019 at 17:33
posted error "insert into musteri (ad,soyad..." does not match posted code "insert into musteri (name,surname..." - also missed setting of parameters (like addQuery.setString(...) — user85421
– user85421, Commented Mar 3, 2019 at 17:34

Mureinik · Accepted Answer · 2019-03-03 17:43:57Z

1

You are mixing statements with prepared statements. You should use a prepared statement and set the values to it:

public void addCustomer(String name, String surname, String company, String address, String adressTwo) {
    String addQuery = "insert into musteri (name, surname, company, adress, adressTwo) values (?,?,?,?,?)" ;

    // Shown here for simplicitly.
    // The query could be prepared once and stored in a data member
    try (PreparedStatement ps = con.prepareStatement(addQuery)) {
        ps.setString(1, name);
        ps.setString(2, surname);
        ps.setString(3, company);
        ps.setString(4, address);
        ps.setString(5, addressTwo);
        ps.executeUpdate();
        System.out.println("Data Added");
    } catch (Exception e) {
        System.out.println("Error occured when adding value to database : " + e );
    }
}

answered Mar 3, 2019 at 17:43

Mureinik

316k54 gold badges399 silver badges405 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

krky Over a year ago

It works. Many thanks! Now i understood my mistake. Thanks for attention.

Joakim Danielson · Accepted Answer · 2019-03-03 17:48:32Z

May I suggest you implement addCustomer like this. Use a local Statement and create it by using try-with-resource style and then set your parameters for the query

public void addCustomer(String name, String surname, String company, String adress, String adressTwo){
    String addQuery = "insert into musteri (name,surname,company,adress,adressTwo) values (?,?,?,?,?)" ;

    try (PreparedStatement stmt = con.prepareStatement(addQuery)) {
        stmt.setString(1, name);
        stmt.setString(2, surname);
        stmt.setString(3, company);
        stmt.setString(4, adress);
        stmt.setString(5, adressTwo);
        stmt.executeUpdate();
        System.out.println("Data Added");
    } catch (Exception e) {
        System.out.println("Error occured when adding value to database : " + e );
    }
}

Collectives™ on Stack Overflow

Getting Java mysql SQL Syntax error but my query seems normal

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related