[Java2_10] Lỗi thường gặp khi kết nối với cơ sở dữ liệu

4.  JDBC Cycle

4.1  (Skip Unless...) How to Debug?

SYMPTOM: Can compile the JDBC program but Runtime Error
ERROR MESSAGE: 
   (Windows) No suitable driver found
   (macOS/Linux) NullPointerException
PROBABLE CAUSES: MySQL JDBC Driver Connector/J was NOT (properly) installed.
POSSIBLE SOLUTION:
   1. Read "2.3 Install MySQL JDBC Driver" again, again and again...
   2. You need to include MySQL JDBC driver via "cp" option to run your JDBC program:
      > java -cp .;path-to\mysql-connector-java-x.x.xx.jar JdbcClassName
   3. For Tomcat, you may copy the driver JAR-file into Tomcat's "lib" directory.

SYMPTOM: Can compile the JDBC program but Runtime Error
ERROR MESSAGE: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
PROBABLE CAUSES: 
   1. MySQL Server is NOT started, or
   2. The program was connecting to a wrong TCP port number or wrong hostname (or IP address)
      in your database-URL jdbc:mysql://localhost:port/databaseName.
POSSIBLE SOLUTION:
   1. Make sure that server has been started. Note down the server's port number 
      from the server's console.
   2. Check the database-URL's hostname and port number: jdbc:mysql://localhost:port/databaseName
   3. Run a MySQL client, issue command "status" to confirm the server's TCP port number.
   4. Run a mysql client, use "mysql -u root -p --port=xxxx" to specify the port number to
      confirm the server's port number.

SYMPTOM: Can compile the JDBC program but Runtime Error
ERROR MESSAGE: java.sql.SQLException: Access denied for user 'username'@'localhost' (using password: YES)
PROBABLE CAUSES: Wrong username or password in statement:
   DriverManager.getConnection(databaseURL, username, password).
POSSIBLE SOLUTION: Obvious!

SYMPTOM: Can compile the JDBC program but Runtime Error
ERROR MESSAGE: java.sql.SQLNonTransientConnectionException: Public Key Retrieval is not allowed
POSSIBLE SOLUTION:
1. Check you userid and password by logging in thru mysql command-line client
2. Add allowPublicKeyRetrieval=true and/or useSSL=false to Connnection's URL, e.g.,
jdbc:mysql://localhost:3306/ebookshop?allowPublicKeyRetrieval=true&useSSL=false&serverTimezone=UTC

SYMPTOM: Can compile the JDBC program but Runtime Error
ERROR MESSAGE: java.sql.SQLException: The server time zone value 'xxx' is unrecognized or represents more than one time zone.
POSSIBLE SOLUTION:
Add serverTimezone=UTC to Connnection's URL, e.g.,
jdbc:mysql://localhost:3306/ebookshop?allowPublicKeyRetrieval=true&useSSL=false&serverTimezone=UTC

SYMPTOM: Can compile the JDBC program but Runtime Error
ERROR MESSAGE: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 
   Unknown database 'xxxx'
PROBABLE CAUSES: DriverManager.getConnection("jdbc:mysql://localhost:8888/xxxx", userid, password) 
   specifies a database that does not exist in the server.
POSSIBLE SOLUTION: Create the database using a client, before running the Java program.

SYMPTOM: Can compile the JDBC program but Runtime Error
ERROR MESSAGE: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 
   Table 'xxxx.xxxx' doesn't exist
PROBABLE CAUSES: The SQL statement references a non-existence table.
POSSIBLE SOLUTION: Check your SQL statement and the database tables.

SYMPTOM: Can compile the JDBC program but Runtime Error
ERROR MESSAGE: java.sql.SQLException: Column 'xxx' not found.
PROBABLE CAUSES: The method ResultSet.getXxx(columnName) cannot locate 
   the requested columnName in the ResultSet.
POSSIBLE SOLUTION: Make sure that the column 'xxx' is included in the SELECT statement,
   so that it is included in the ResultSet.

SYMPTOM: Can compile the JDBC program but Runtime Error
ERROR MESSAGE: 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 .... at line x
PROBABLE CAUSES: Syntax error in your SQL statement.
POSSIBLE SOLUTION: Obvious!