Using variables with JDBC

Question

I want to have to use the users input for driverID, change onJob to true, if the user inputs the id.

So for example, if the user enters 1 as the driver id, the driver with id number 1 will have the onJob variable change to 1.

Here is my code currently;

public class TaxiDriver {

    //String driverLocation = DRIVERFIRSTLOCATION;
    //String destinationintoAPI;

    //JDBC driver name and database URL
    static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
    static final String DB_URL = "jdbc:mysql://localhost/DRIVER";

    //Database credentials

    static final String USER = "user";
    static final String PASS = "password";
    String driverId;
    static Scanner reader = new Scanner(System.in);

    public static void main(String[] args) {
        Connection conn = null;
        Statement stmt = null;
        try {
            //STEP 2: Register JDBC driver
            Class.forName("com.mysql.jdbc.Driver");

            //STEP 3: Open a connection
            System.out.println("Connecting to a selected database...");
            conn = DriverManager.getConnection(DB_URL, USER, PASS);
            System.out.println("Connected database successfully...");

            System.out.println("Assign a driver to a jo...");
            reader.nextInt();

            //STEP 4: Execute a query
            System.out.println("Creating statement...");
            stmt = conn.createStatement();
            String sql = "UPDATE Drivers " +
                "SET OnJob = 1 WHERE id = (driverId)";
            stmt.executeUpdate(sql);

            // Now you can extract all the records
            // to see the updated records
            sql = "SELECT id, license, first, last, OnJob, Email, Telephone, Address, Postcode, Veichle  FROM Drivers";
            ResultSet rs = stmt.executeQuery(sql);

            while (rs.next()) {
                //retrieve by column name

                int id = rs.getInt("id");
                int license = rs.getInt("license");
                String first = rs.getString("first");
                String last = rs.getString("last");
                int OnJob = rs.getInt("OnJob");
                String Email = rs.getString("Email");
                String Telephone = rs.getString("Telephone");
                String Address = rs.getString("Address");
                String Postcode = rs.getString("Postcode");
                String Veichle = rs.getString("Veichle");
                //Display
                System.out.print("ID: " + id);
                System.out.print(", license: " + license);
                System.out.print(", First: " + first);
                System.out.print(", Last: " + last);
                System.out.print(", Email; " + Email);
                System.out.print(", Telephone; " + Telephone);
                System.out.print(", Address; " + Address);
                System.out.print(", Postcode; " + Postcode);
                System.out.print(", Veichle;" + Veichle);
                if (OnJob == 0) {
                    System.out.println(": Driver is aviliable for pickup");
                } else {
                    System.out.println(": Driver is not aviliable for pickup");
                }
            }
            rs.close();
        } catch (SQLException se) {
            //Handle errors for JDBC
            se.printStackTrace();
        } catch (Exception e) {
            //Handle errors for Class.forName
            e.printStackTrace();
        } finally {
            //finally block used to close resources
            try {
                if (stmt != null)
                    conn.close();
            } catch (SQLException se) {
            }// do nothing
            try {
                if (conn != null)
                    conn.close();
            } catch (SQLException se) {
                se.printStackTrace();
            }//end finally try
        }//end try
        System.out.println("Goodbye!");
    }//end main
}//end JDBCExample

Right now I am getting these errors.

Connecting to a selected database...
Tue May 03 00:18:28 BST 2016 WARN: Establishing SSL connection without server's identity verification is not recommended. 
According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. 
For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. 
You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
Connected database successfully...
Assign a driver to a jo...
2
Creating statement...
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'driverId' in 'where clause'
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
    at java.lang.reflect.Constructor.newInstance(Unknown Source)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:404)
    at com.mysql.jdbc.Util.getInstance(Util.java:387)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:939)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3878)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3814)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2478)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2625)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2547)
    at com.mysql.jdbc.StatementImpl.executeUpdateInternal(StatementImpl.java:1541)
    at com.mysql.jdbc.StatementImpl.executeLargeUpdate(StatementImpl.java:2605)
    at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1469)
    at TaxiDriver.main(TaxiDriver.java:71)
Goodbye!

Much thanks in advance.

QuantumMechanic · Accepted Answer · 2016-05-02 23:38:37Z

4

First, you actually need to read in a value into driverId somewhere.

That aside, the problem is with your SQL statement. You have:

String sql = "UPDATE Drivers " +
             "SET OnJob = 1 WHERE id = (driverId)";

You are telling the database to look for records where the value of the records' id column equals the value of their driverId column.

You need to get the value of your driverId variable into the SQL rather than putting the actual characters "driverId" into the SQL, where (as you see) it is interpreted as the name of a column.

Using your approach you need to do:

String sql = "UPDATE drivers SET OnJob=1 WHERE id=" + driverId;

But dynamic SQL is dangerous and you are better off using a PreparedStatement. That will sanitize the user inputs and prevent SQL injection attacks.

String sql = "UPDATE drivers SET OnJob=1 WHERE id=?";
PreparedStatement ps = connection.createPreparedStatement(sql);
ps.setInt(1, Integer.parseInt(driverId));
ResultSet rs = ps.executeQuery();

edited May 2, 2016 at 23:38

answered May 2, 2016 at 23:33

QuantumMechanic

14k4 gold badges47 silver badges67 bronze badges

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

1 Comment

Ryan Johnston Over a year ago

Thanks very much, I can't believe I forgot to read in a value.

MSameer · Accepted Answer · 2016-05-03 00:11:36Z

0

You don't seem to be setting the driverId from user input. Update your sql statement with valid id value, before executing the script.

i.e.

int driverId = 123;// This is user input, am assuming its type int.
String sql = "UPDATE Drivers SET OnJob = 1 WHERE id =" + driverId;
//now execute your sql

String driverId = "123";//This is user input, am assuming its type String.
String sql = "UPDATE Drivers SET OnJob = 1 WHERE id =" + "'" + driverId  + "'";
//now execute your sql

Edit: I highly recommend QuantumMechanic's solution of using PreparedStatement as its much safer and hides all the handling of different types.

edited May 3, 2016 at 0:11

answered May 2, 2016 at 23:36

MSameer

4432 silver badges9 bronze badges

1 Comment

MSameer Over a year ago

@QuantumMechanic, thanks for pointing out. Like you suggested, its better to use PreparedStatement, its definitely more safer.

Collectives™ on Stack Overflow

Using variables with JDBC

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related