Java MySQL – Bad syntax

Question

I have to create project in Java to school. It types me I have wrong syntax but when I copy the command from error message to MySQL server, it's all alright and information are inserted into table.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Scanner;
import com.mysql.jdbc.StatementInterceptor;


public class main {

    public static void main(String[] args) {
        String dbHost="localhost";
        String dbDatabase="cars";
        String dbUser = "root";
        String dbPassword = "";
        int Select;

        Scanner input = new Scanner(System.in);

        Cars cars = new Cars();
        CarDAO carDAO = new CarDAO();


        System.out.println("Choose option: ");
        System.out.println("1. Create a new car");
        System.out.println("2. Update entry of the car");
        System.out.println("3. Mark the car as sold");
        System.out.println("4. View all cars that are for sale ");
        System.out.println("5. Search for cars");

        Select = input.nextInt();

        switch (Select){
            case 1: {
                carDAO.createCar(cars);
                break;
            }
            case 2:{
                carDAO.changeEntry(cars);
                break;
            }
            case 3:{
                carDAO.soldCar(cars);
                break;
            }
            case 4:{
                carDAO.showCars(cars);
                break;
            }
            case 5:{
                carDAO.search(cars);
                break;
            }
        }

        try {
            // register driver
            Class.forName("com.mysql.jdbc.Driver");
            // Make Connection Url
            String connectionUrl = "jdbc:mysql://" + dbHost
                        + "/" + dbDatabase
                        + "?user=" + dbUser
                        + "&password=" + dbPassword;
            //open Connection
            Connection conn = DriverManager.getConnection(connectionUrl);
            // Code to create sql and run it will go here
            // create SQL
            String sql = "use cars; " + carDAO.sql;
            // prepare Statement
            PreparedStatement ps = conn.prepareStatement(sql);
            // execute SQL
            ps.executeUpdate();

            // close connection
            conn.close();
        }catch (ClassNotFoundException cnfe){
            throw new RuntimeException(cnfe);
        }catch (SQLException sqle) {
        throw new RuntimeException(sqle);
        }
        }
}

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Scanner;

public class CarDAO {
    String type;
    int price;
    String date;
    int ID = 0;
    String sql;
    String year;
    String month;
    String day;
    Scanner input = new Scanner(System.in);
    Cars cars = new Cars();
    main Main = new main();
    public void update (Cars cars){

    }
    public void delete (Cars cars){

    }
    public void createCar (Cars cars){
        System.out.println("Type: ");
        cars.setType(input.nextLine());
        System.out.println("Price: ");
        cars.setPrice(input.nextInt());
        input.nextLine();
        System.out.println("Made in year: ");
        year = input.nextLine();
        System.out.println("month: ");
        month = input.nextLine();
        System.out.println("day: ");
        day = input.nextLine();
        date = year + month + day;
        cars.setDate(date);
        ID = ID + 1;
        cars.setId(ID);
        sql = "INSERT INTO cars (Type, Price, Date) VALUES (" + "'" + cars.getType() + "'" + ", " + cars.getPrice() + ", " + cars.getDate() + ");";
    }
    public void changeEntry (Cars cars){

    }
    public void soldCar (Cars cars){

    }
    public void showCars (Cars cars){

    }
    public void search (Cars cars){

    }


}


public class Cars {
    int id;
    String type;
    String date;
    int price;
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getType() {
        return type;
    }
    public void setType(String type) {
        this.type = type;
    }
    public int getPrice() {
        return price;
    }
    public void setPrice(int price) {
        this.price = price;
    }
    public String getDate() {
        return date;
    }
    public void setDate(String date) {
        this.date = date;
    }
    @Override
    public String toString() {
        return "Car [id=" + getId() + ", type=" + getType() + ", price=" + getPrice() + " date =" + getDate() + "]";
    }

}

error message:

Exception in thread "main" java.lang.RuntimeException: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INSERT INTO cars (Type, Price, Date) VALUES ('nwm', 50, 1991827)' at line 1
    at main.main(main.java:80)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INSERT INTO cars (Type, Price, Date) VALUES ('nwm', 50, 1991827)' at line 1
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
    at java.lang.reflect.Constructor.newInstance(Unknown Source)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
    at com.mysql.jdbc.Util.getInstance(Util.java:386)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1053)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4096)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4028)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2490)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2651)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2734)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2155)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2458)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2375)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2359)
    at main.main(main.java:73)

Try this: sql = "INSERT INTO cars (Type, Price, Date) VALUES ("+"'"+ cars.getType() + "'" + ", " + cars.getPrice() + ", " + cars.getDate()+")"; — suraj_fale
– suraj_fale, Commented Mar 15, 2016 at 20:49

Marc B · Accepted Answer · 2016-03-15 19:43:39Z

2

        String sql = "use cars; " + carDAO.sql;
                                ^^^^^^^^^^^^^^

Standard MySQL connections don't allow multiple statements in a single query, as a basic defense against one form of sql injection attack. Split things up into two query:

query("use cars;");
query(carDAO.sql);

And note that the use query isn't really necessary. You can specify a default DB in your connection string. You already have dbDatabase, but aren't using it.

answered Mar 15, 2016 at 19:43

Marc B

362k44 gold badges433 silver badges508 bronze badges

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

Collectives™ on Stack Overflow

Java MySQL – Bad syntax

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related