CODEWITHSUNDEEP
javamysqljdbcmysql-connector
WHOATEMYNOODLES
WHOATEMYNOODLES

Reputation: 2731

Why doesn't my PreparedStatement SQL query work? Only works with a hardcoded string

I'm trying to use a PreparedStatement to generate an SQL query. It works perfectly fine when I use a hard coded String or with concatenation, but I am absolutely lost about why it's not working when using a PreparedStatement.

public Customer createCustomer(Customer customer) {

     //Ommitted code

     int result = statement.executeUpdate(createPreparedStatementQuery(customer), Statement.RETURN_GENERATED_KEYS)
);

Creating the query with a PreparedStatement

public static String createPreparedStatementQuery(Customer customer){
    String query = "INSERT INTO customers(Customer_Name, Address, Postal_Code, Phone, Created_By, Last_Updated_By, Division_ID) " +
            "VALUES (?, ?, ?, ?, ?, ?, (SELECT Division_ID FROM first_level_divisions WHERE division = ?));";
    try {
        PreparedStatement statement = ConnectionManager.getConnection().prepareStatement(query);
        statement.setString(1, customer.getName());
        statement.setString(2, customer.getAddress());
        statement.setString(3, customer.getPostalCode());
        statement.setString(4, customer.getPhoneNumber());
        statement.setString(5, customer.getCreatedBy());
        statement.setString(6, customer.getLastUpdatedBy());
        statement.setString(7, customer.getDivision());
        
        System.out.println(statement); //Successfully returns the query
        
        return statement.toString();
    }catch (SQLException e){
        System.out.println(e.getMessage());
        return null;
    }
}

This gives the error:

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 'com.mysql.cj.jdbc.ClientPreparedStatement: INSERT INTO customers(Customer_Name, ' at line 1

However, the query executes perfectly fine with a String created by concatenation or hardcoded.

public static String createConcatQuery(Customer customer) {
    String query =  "INSERT INTO customers(Customer_Name, Address, Postal_Code, Phone, Created_By, Last_Updated_By, Division_ID) " +
            "VALUES ('"
            + customer.getName() + "', '"
            + customer.getAddress() + "', '"
            + customer.getPostalCode() + "', '"
            + customer.getPhoneNumber() + "', '"
            + customer.getCreatedBy() + "', '"
            + customer.getLastUpdatedBy() + "', "
            + "(SELECT Division_ID FROM first_level_divisions WHERE division = '"
            + customer.getDivision() + "'));";
    System.out.println(query);
    return query;
}

String hardCodedQuery = "INSERT INTO customers(Customer_Name, Address, Postal_Code, Phone, Created_By, Last_Updated_By, 

Division_ID) VALUES ('ee', 'ee', 'ee', 'ee', 'test', 'test', (SELECT Division_ID FROM 

first_level_divisions WHERE division = 'Newfoundland and Labrador'));";

I've copied and pasted both the queries outputted from the log. The concat query and preparedstatement query both return the same String:

enter image description here

I am absolutely lost on why it doesn't work at this point. Could someone please help me figure out what I'm doing incorrectly using the PreparedStatement?

Full StackTrace:

java.sql.SQLSyntaxErrorException: 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 'com.mysql.cj.jdbc.ClientPreparedStatement: INSERT INTO customers(Customer_Name, ' at line 1
    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.StatementImpl.executeUpdateInternal(StatementImpl.java:1337)
    at com.mysql.cj.jdbc.StatementImpl.executeLargeUpdate(StatementImpl.java:2117)
    at com.mysql.cj.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1342)
    at sample.data.CustomerRepository.createCustomer(CustomerRepository.java:144)
    at sample.controller.CustomerController.createCustomer(CustomerController.java:165)
    at sample.controller.CustomerController.confirmAlert(CustomerController.java:153)
    at sample.controller.CustomerController.onBtnSaveClick(CustomerController.java:146)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at sun.reflect.misc.Trampoline.invoke(MethodUtil.java:71)
    at sun.reflect.GeneratedMethodAccessor1.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at sun.reflect.misc.MethodUtil.invoke(MethodUtil.java:275)
    at javafx.fxml.FXMLLoader$MethodHandler.invoke(FXMLLoader.java:1771)
    at javafx.fxml.FXMLLoader$ControllerMethodEventHandler.handle(FXMLLoader.java:1657)
    at com.sun.javafx.event.CompositeEventHandler.dispatchBubblingEvent(CompositeEventHandler.java:86)
    at com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(EventHandlerManager.java:238)
    at com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(EventHandlerManager.java:191)
    at com.sun.javafx.event.CompositeEventDispatcher.dispatchBubblingEvent(CompositeEventDispatcher.java:59)
    at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:58)
    at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
    at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:56)
    at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
    at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:56)
    at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
    at com.sun.javafx.event.EventUtil.fireEventImpl(EventUtil.java:74)
    at com.sun.javafx.event.EventUtil.fireEvent(EventUtil.java:49)
    at javafx.event.Event.fireEvent(Event.java:198)
    at javafx.scene.Node.fireEvent(Node.java:8411)
    at javafx.scene.control.Button.fire(Button.java:185)
    at com.sun.javafx.scene.control.behavior.ButtonBehavior.mouseReleased(ButtonBehavior.java:182)
    at com.sun.javafx.scene.control.skin.BehaviorSkinBase$1.handle(BehaviorSkinBase.java:96)
    at com.sun.javafx.scene.control.skin.BehaviorSkinBase$1.handle(BehaviorSkinBase.java:89)
    at com.sun.javafx.event.CompositeEventHandler$NormalEventHandlerRecord.handleBubblingEvent(CompositeEventHandler.java:218)
    at com.sun.javafx.event.CompositeEventHandler.dispatchBubblingEvent(CompositeEventHandler.java:80)
    at com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(EventHandlerManager.java:238)
    at com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(EventHandlerManager.java:191)
    at com.sun.javafx.event.CompositeEventDispatcher.dispatchBubblingEvent(CompositeEventDispatcher.java:59)
    at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:58)
    at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
    at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:56)
    at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
    at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:56)
    at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
    at com.sun.javafx.event.EventUtil.fireEventImpl(EventUtil.java:74)
    at com.sun.javafx.event.EventUtil.fireEvent(EventUtil.java:54)
    at javafx.event.Event.fireEvent(Event.java:198)
    at javafx.scene.Scene$MouseHandler.process(Scene.java:3757)
    at javafx.scene.Scene$MouseHandler.access$1500(Scene.java:3485)
    at javafx.scene.Scene.impl_processMouseEvent(Scene.java:1762)
    at javafx.scene.Scene$ScenePeerListener.mouseEvent(Scene.java:2494)
    at com.sun.javafx.tk.quantum.GlassViewEventHandler$MouseEventNotification.run(GlassViewEventHandler.java:394)
    at com.sun.javafx.tk.quantum.GlassViewEventHandler$MouseEventNotification.run(GlassViewEventHandler.java:295)
    at java.security.AccessController.doPrivileged(Native Method)
    at com.sun.javafx.tk.quantum.GlassViewEventHandler.lambda$handleMouseEvent$2(GlassViewEventHandler.java:432)
    at com.sun.javafx.tk.quantum.QuantumToolkit.runWithoutRenderLock(QuantumToolkit.java:410)
    at com.sun.javafx.tk.quantum.GlassViewEventHandler.handleMouseEvent(GlassViewEventHandler.java:431)
    at com.sun.glass.ui.View.handleMouseEvent(View.java:555)
    at com.sun.glass.ui.View.notifyMouse(View.java:937)
    at com.sun.glass.ui.win.WinApplication._runLoop(Native Method)
    at com.sun.glass.ui.win.WinApplication.lambda$null$3(WinApplication.java:177)
    at java.lang.Thread.run(Thread.java:748)

Upvotes: 0

Views: 324

Answers (1)

Zohaib Ahmed
Zohaib Ahmed

Reputation: 211

You have to call the statement.executeQuery() in your createPreparedStatementQuery() function. Or you could return your statement object from createPreparedStatementQuery() function and call statement.executeQuery() in your createCustomer().

N.B. You can use statement.executeUpdate() or statement.executeQuery() depending on what you want.

What your createPreparedStatementQuery() is basically doing is that it is returning the original query with ? marks in it to the statement object in createCustomer(). The statement object outside does not know about about parameters you set in the inside statement object.

Upvotes: 1

Related Questions