Updating data with JDBC in MySQL database

Question

I just started learning JDBC and databases. I was trying to create a simple code of Updating data to already existing database. I created localhost with xampp added and some data. I read some tutorials on oracle web page and watched couple of lynda.com videos about JDBC and tried to write this program, but it doesn't work.

This is my main

package com.lynda.javatraining.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Main {

    private static final String USERNAME = "vato";
    private static final String PASSWORD = "vatopassword";
    private static final String CONN_STRING =
            "jdbc:mysql://localhost/university";

    public static void main(String[] args) throws SQLException {

        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;    
        try {
            conn = DriverManager.getConnection(CONN_STRING, USERNAME, PASSWORD);
            Building something = new Building();
            something.UpdateBuilding("Daniels", "Gordon", 5, conn);
        } catch (SQLException e) {
            System.err.println(e);
        } finally {
            if (rs != null) {
                rs.close();
            }
            if (stmt != null) {
                stmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        }
    }

}

I created a Building class for having some functions to add building, Update, delete etc.

This is my code for Updatebuilding class.

public void UpdateBuilding(String NewName, String buildingname, int IDnum, Connection conn) throws SQLException
    {
        Statement stmt = null;
        ResultSet rs = null;
        try {
            stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            stmt.executeUpdate("UPDATE building SET BuildingName = \"" + NewName + "\" WHERE BuildingID = '"+ IDnum +"' AND BuildingName = '" + buildingname + ";");
            this.PrintBuilding(conn);
        } catch (SQLException e) {
            System.err.println(e);
        } finally {
            if (rs != null) {
                rs.close();
            }
            if (stmt != null) {
                stmt.close();
            }
        }
    }

When compiling this code i get this error

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 ''Gordon' at line 1

I tried not to give Gordon as parameter, but then I got the same error about the ID number "5" at line 1.

Can u please help me? whats wrong with this code?

What is your built query looking like ?

Lorenz Meyer
– Lorenz Meyer

2014-08-12 06:53:31 +00:00
Commented Aug 12, 2014 at 6:53 — Lorenz Meyer
– Lorenz Meyer, Commented Aug 12, 2014 at 6:53

Jens · Accepted Answer · 2014-08-12 06:49:57Z

3

You miss an ' at and of your statement:

AND BuildingName = '" + buildingname + "';");
                                        ^^^

This should solve your problem. But think about prepared statement. It is much better to use prepared statement.

answered Aug 12, 2014 at 6:49

Jens

69.6k15 gold badges107 silver badges133 bronze badges

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

4 Comments

GreedyAi Over a year ago

OMG. -_- I'm so sorry for this dumb question. I tried to check for syntax error, but somehow missed it. I hate asking this kind of questions. Thanks for your answer. It Worked!

user3863110 Over a year ago

How about the double-quoted string value? It is a syntax error as well.

GreedyAi Over a year ago

@PeterWiggin Why is it a syntax error? I used double-quoted because some names of the buildings might consist of two words.

Lorenz Meyer Over a year ago

' might consist of two words ' is not a reason for using double quotes. Rather escape the values correctly and even a single quote in the word will not break the query.

Kousick Shanmugam Nagaraj · Accepted Answer · 2014-08-12 06:52:17Z

2

your IDnum must be a number, hence do not use ''.

Use the following :

stmt.executeUpdate("UPDATE building SET BuildingName = '" + NewName + "' WHERE BuildingID = " + IDnum +" AND BuildingName = '" + buildingname + "';");

If this is usefull, mark as answer

answered Aug 12, 2014 at 6:52

Kousick Shanmugam Nagaraj

8562 gold badges9 silver badges26 bronze badges

2 Comments

GreedyAi Over a year ago

I think Jens answered first. I have to mark his one as an answer. But I up voted your's. Thank you!

Kousick Shanmugam Nagaraj Over a year ago

You are adding a single quotes to ur IDnum too, just have a check at it.

Thusitha Thilina Dayaratne · Accepted Answer · 2014-08-12 06:55:23Z

2

Instead of

String sql  = "UPDATE building SET BuildingName = \"" + NewName + "\" WHERE BuildingID = '"+ IDnum +"' AND BuildingName = '" + buildingname + ";"

try to use PreparedStatement in proper manner e.g.

 PreparedStatement pstmt = con.prepareStatement("UPDATE building SET BuildingName = ? WHERE BuildingID = ? AND BuildingName = ?");
 pstmt.setString(1, NewName);
 pstmt.setString(2, IDnum);
 pstmt.setString(3, buildingname);
 pstmt.executeUpdate();

answered Aug 12, 2014 at 6:55

Thusitha Thilina Dayaratne

5,7204 gold badges45 silver badges71 bronze badges

1 Comment

GreedyAi Over a year ago

Thanks for the answer. I just read about PreparedStatement and totally forgot about it. I'm glad you reminded me of it.

Collectives™ on Stack Overflow

Updating data with JDBC in MySQL database

3 Answers 3

4 Comments

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related