java - 在 JDBC 中使用变量

Question

我想必须使用用户输入的 driverID，如果用户输入 id，则将 onJob 更改为 true。

因此，例如，如果用户输入 1 作为驱动程序 ID，则 ID 号为 1 的驱动程序会将 onJob 变量更改为 1。

这是我目前的代码；

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

现在我遇到了这些错误。

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!

非常感谢。

Answer 1

首先，您实际上需要在某处将值读入 driverId。

除此之外，问题出在您的 SQL 语句上。你有:

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

您是在告诉数据库查找记录的 id 列的值等于其 driverId 列的值的记录。

您需要将 driverId 变量的值放入 SQL 而不是将实际字符“driverId”放入 SQL，其中(如您所见)它被解释为一个名称专栏。

使用你的方法你需要做的:

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

但是动态 SQL 很危险，您最好使用 PreparedStatement。这将清理用户输入并防止 SQL 注入(inject)攻击。

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