Reputation: 2839

Updating data with JDBC in MySQL database

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?

Upvotes: 0

Answers (3)

Thusitha Thilina Dayaratne

Reputation: 5712

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();

Upvotes: 2

Kousick Shanmugam Nagaraj

Reputation: 846

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

Upvotes: 2

Jens

Reputation: 69440

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.

Upvotes: 3

Updating data with JDBC in MySQL database

Answers (3)

Related Questions