Can anyone see the mistake in the SQL Command i am using to update a record in a local DB using JDBC-CodePudding

I am trying to update a DB Table at my college using JDBC and JAVAFx. I have tried everything to get the SQL Update command to work.

I have a table where the player_id is the foreign key from the table - Player -, and is a child table to its parent namely - Player and Game Information -.

I have set up the schema correctly for both tables as per instructions and am trying to update a record in the 'Player' table based off player_id since it is the foreign key for the 'Player'.

I understand this is very rudimentary as a command and have researched 'preparedstatements' in Java, but i was asked to do it this way. The codebelow basically collects a player_id via an input dialogue for which the record is being updated and all the columns are nullable except for the player_id. The code i have is as follows :

dbConnect();

JFrame frame;
frame = new JFrame();

int searchP_id = Integer.parseInt(JOptionPane.showInputDialog(frame, "Please enter the ID of the Player you would like to update"));

String sql = "UPDATE player SET"  " first_name= '"   first_name   "',"   " last_name= '"   last_name   "',"   " address= '"   address   "',"   " postal_code= '"   postal_code   "',"   " province= '"   province   "',"   " phone_number= '"   phone_number   "'"   " WHERE player_id ="   searchP_id   ";" ;

statement.executeUpdate(sql);

if (statement != null) {
    //Close Statement
    statement.close();
}

I tried verifying that i have the command right as per sql statements and when i run this within sqlDeveloper - it updates the record.

I have a controller class that basically runs these commands as part of a method that runs during an actionevent onUpdatePlayerButtonClick.

I tried checking he syntax and cannot see the problem, but the only error i get whenever running this line is - << Caused by: Error : 933, Position : 128, Sql = UPDATE player SET first_name= '', last_name= '', address= '', postal_code= '', province= '', phone_number= '' WHERE player_id =1;, OriginalSql = UPDATE player SET first_name= '', last_name= '', address= '', postal_code= '', province= '', phone_number= '' WHERE player_id =1;, Error Msg = ORA-00933: SQL command not properly ended >>.

I tried reasearching the error code online and it said that there is a clause added that shouldnot be there and might be casuing the problem -

this is a simple UPDATE query so where could it be going wrong ? The DB is getting connected to fine and the Insert command i have within my utility class works too.

Thank you for any help provided !

CodePudding user response：

It looks like you have an extra comma after the SET clause in your UPDATE query. This is causing the ORA-00933 error because the SQL parser is expecting another field to be updated after the comma.

To fix this, you can simply remove the extra comma at the end of the SET clause like this:

String sql = "UPDATE player SET"  
" first_name= '"   first_name   "',"  
" last_name= '"   last_name   "',"  
" address= '"   address   "',"  
" postal_code= '"   postal_code   "',"  
" province= '"   province   "',"  
" phone_number= '"   phone_number   "'"  
" WHERE player_id ="   searchP_id   ";";

It is also a good idea to use a PreparedStatement instead of concatenating the values into the query string like this. This will help prevent SQL injection attacks and make your code more efficient. Here is an example of how you can use a PreparedStatement to update the player record:

// Create the UPDATE query with placeholders for the values
String sql = "UPDATE player SET"  
" first_name= ?,"  
" last_name= ?,"  
" address= ?,"  
" postal_code= ?,"  
" province= ?,"  
" phone_number= ?"  
" WHERE player_id = ?";

// Create a PreparedStatement object
PreparedStatement pstmt = connection.prepareStatement(sql);

// Set the values for the placeholders in the query
pstmt.setString(1, first_name);
pstmt.setString(2, last_name);
pstmt.setString(3, address);
pstmt.setString(4, postal_code);
pstmt.setString(5, province);
pstmt.setString(6, phone_number);
pstmt.setInt(7, searchP_id);

// Execute the UPDATE query
pstmt.executeUpdate();

// Close the PreparedStatement
pstmt.close();

CodePudding user response：

Your query is not readable and you should use parameters in your JDBC statement.

dbConnect();

JFrame frame;
frame = new JFrame();

int searchP_id = Integer.parseInt(JOptionPane.showInputDialog(frame, "Please enter the ID of the Player you would like to update"));

String sql = "UPDATE player"   
        " SET first_name = ?, last_name = ?, address = ?,"   
        " postal_code = ?, province = ?, phone_number = ?"  
        " WHERE player_id = ?";

PreparedStatement preparedStatement =
        connection.prepareStatement(sql);

preparedStatement.setString(1, "the_first_name");
preparedStatement.setString(2, "the_last_name");
preparedStatement.setString(3, "the_address");
preparedStatement.setString(4, "the_postal_code");
preparedStatement.setString(5, "the_province");
preparedStatement.setString(6, "the_phone_number");
preparedStatement.setLong(7, "the_player_id");

preparedStatement.executeUpdate(sql);

if (preparedStatement != null) {
    //Close Statement
    preparedStatement.close();
}

Take look here for more details on parameters with JDBC.