Francis Francis -3 years ago 247
Java Question

Java mysql - How to Update database from textbox

Can someone please tell me what is wrong to my query.
i have textbox to update the occupantname but it doesn't work, Only Status works.

String gOccupied = "Occupied" ;
String query = "UPDATE `rooms` SET `occupantname` = '"+txtFirstNames.getText()+"' , `status`='"+gOccupied+"' WHERE roomnumber = " +CBRoomNumber.getSelectedItem();
executeSQlquery(query,""+" Updated");

Answer Source

Can someone please tell me what is wrong to my query. i have textbox to update the occupantname but it doesn't work, Only Status works.

Don't use direct MySql SQL with these special characters `

String query = "UPDATE rooms SET occupantname = '"+txtFirstNames.getText()+"' , status='"+gOccupied+"' WHERE roomnumber = " +CBRoomNumber.getSelectedItem(); Blockquote

Instated Use below SQL

String query = "UPDATE rooms SET occupantname = ? , status= ? WHERE roomnumber = ?";

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

public class Main {

    private static PreparedStatement preparedStmt = null;
    private static Connection connection = null;

    public static void main(String[] args) {
        String gOccupied = "Occupied";
        String occupantname = txtFirstNames.getText();
        String query = "UPDATE rooms SET occupantname = ? , status= ? WHERE roomnumber = ?";
        try {
            executeSQlquery(query, occupantname, gOccupied, "Updated");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private static void executeSQlquery(String query, String occupantname, String gOccupied, String status) throws SQLException {
        try {
            // create a java mysql database connection
            String myDriver = "org.mysql.Driver";
            String myUrl = "jdbc:mysql://localhost/test";
            Class.forName(myDriver);
            connection = DriverManager.getConnection(myUrl, "username", "password");
            preparedStmt = connection.prepareStatement(query);
            preparedStmt.setString(1, occupantname);
            preparedStmt.setString(2, gOccupied);
            preparedStmt.setInt(3, 101);

            // execute the java preparedstatement
            preparedStmt.executeUpdate();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } finally {
            //finally block used to close resources
            if (preparedStmt != null) {
                connection.close();
            }
            if (connection != null) {
                connection.close();
            }
        }
    }

}

It is generally a terrible idea to construct SQL queries the way you currently do, as it opens the door to all sorts of SQL injection attacks. To do this properly, you'll have to use Prepared Statements instead. This will also resolve all sorts of escaping issues that you're evidently having at the moment.

SQL select statement with where clause

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download