Quiz: JDBC Drivers and Connecting Java to a Database
Study the lesson first: JDBC Drivers and Connecting Java to a Database
Total: 8 questions
1. What is JDBC, and why do you need a JDBC driver?
What is JDBC, and why do you need a JDBC driver?
JDBC (Java Database Connectivity) is the standard Java API for connecting to a relational database and running SQL queries. It is part of Java SE as the java.sql package plus the javax.sql extension. These packages contain mostly interfaces: Connection, Statement, ResultSet.
A JDBC driver is a JAR library from the database vendor or the community that implements these interfaces and talks to one specific database over its network protocol. Without a driver, JDBC cannot connect to any database.
The chain is: application → JDBC API → DriverManager → driver → DBMS. The code is written against interfaces, so moving from MySQL to PostgreSQL means changing the driver and the URL. ORM frameworks such as Hibernate, Spring Data JPA and MyBatis are built on top of JDBC too.
2. What are the types of JDBC drivers, and which one is used today?
What are the types of JDBC drivers, and which one is used today?
The JDBC specification divides drivers into four types by the way they reach the database:
- Type 1: JDBC-ODBC bridge — translates JDBC calls into ODBC and needs an ODBC driver in the OS (
sun.jdbc.odbc.JdbcOdbcDriver). Removed from the JDK in Java 8. - Type 2: Native API — partly Java, partly a native client library of the DBMS, for example Oracle OCI. Requires a DBMS client installed.
- Type 3: Network protocol — the driver talks to a middleware server, which talks to the database. Practically unused.
- Type 4: Thin (pure Java) — entirely in Java, speaks the DBMS protocol directly: MySQL Connector/J, PostgreSQL JDBC, Oracle thin.
Type 4 is the standard today. The others survive in legacy systems and in interview questions.
3. Do you need Class.forName to load a JDBC driver, and how does a driver register itself?
Do you need Class.forName to load a JDBC driver, and how does a driver register itself?
Not since JDBC 4.0 (Java 6). A driver registers itself through ServiceLoader: its JAR contains the file META-INF/services/java.sql.Driver with the driver class name. On the first use of DriverManager, all such drivers on the classpath register themselves.
When you call getConnection(url, ...), DriverManager calls connect() on the registered drivers one by one. A driver that does not handle the URL returns null, and the first real connection wins. If every driver says no, you get “No suitable driver found”.
Old code uses Class.forName("com.mysql.jdbc.Driver"). Since Connector/J 8.0 that name is kept only for compatibility and prints a This is deprecated warning. The current class name is com.mysql.cj.jdbc.Driver, but usually the Class.forName line can simply be deleted. The class name is still needed for pool settings such as driverClassName.
4. How do you add the MySQL JDBC driver to a project with Maven or Gradle?
How do you add the MySQL JDBC driver to a project with Maven or Gradle?
The driver is not part of the JDK; you add it to the classpath as a dependency. For MySQL it is the artifact com.mysql:mysql-connector-j:
<dependency>
<groupId>com.mysql</groupId>
<artifactId>mysql-connector-j</artifactId>
<version>9.1.0</version>
<scope>runtime</scope>
</dependency>
The runtime scope fits when the code uses only the java.sql interfaces. If the code refers to driver classes (for example MysqlDataSource), remove the scope. In Gradle: runtimeOnly 'com.mysql:mysql-connector-j:9.1.0'.
The old artifact mysql:mysql-connector-java was renamed in 8.0.31, and new versions are published only as mysql-connector-j. Without Maven or Gradle, download the JAR from the MySQL website and add it to the classpath: java -cp .;mysql-connector-j-9.1.0.jar Main (with : on Linux and macOS).
5. What does a JDBC connection URL look like for MySQL, PostgreSQL, Oracle and SQL Server?
What does a JDBC connection URL look like for MySQL, PostgreSQL, Oracle and SQL Server?
A JDBC URL always starts with jdbc:, followed by the driver subprotocol, the server address and the database name: jdbc:<subprotocol>:[//host[:port]/]<database name>[?parameters]. DriverManager uses the subprotocol to pick the driver.
- MySQL:
jdbc:mysql://localhost:3306/shop - PostgreSQL:
jdbc:postgresql://localhost:5432/shop - Oracle:
jdbc:oracle:thin:@//localhost:1521/ORCLPDB1(by service name; the old style by SID is@host:1521:SID) - SQL Server:
jdbc:sqlserver://localhost:1433;databaseName=shop - H2 in memory:
jdbc:h2:mem:test, SQLite:jdbc:sqlite:shop.db
Driver parameters follow ?, separated by &: jdbc:mysql://localhost:3306/shop?useSSL=false&serverTimezone=UTC. SQL Server separates parameters with semicolons.
6. How do you connect Java to a database with DriverManager.getConnection? List the steps of working with a database through JDBC.
How do you connect Java to a database with DriverManager.getConnection? List the steps of working with a database through JDBC.
Four steps:
- The driver is in memory: since JDBC 4.0 it registers itself,
Class.forNameis not needed. - Open a connection by URL, user name and password:
DriverManager.getConnection(url, user, password). - Create objects for queries through the connection (
StatementorPreparedStatement). - Close all resources, preferably with try-with-resources: automatically and in reverse order, even if an exception is thrown.
try (Connection connection = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/shop", "root", "admin")) {
DatabaseMetaData meta = connection.getMetaData();
System.out.println(meta.getDatabaseProductName());
System.out.println(connection.isValid(2));
}
DatabaseMetaData reports the DBMS and the driver version, and isValid(2) sends a lightweight check and waits at most 2 seconds. Without the driver JAR on the classpath this code fails with No suitable driver found.
7. What is the difference between DriverManager and DataSource, and which one should you use?
What is the difference between DriverManager and DataSource, and which one should you use?
DriverManager (package java.sql) opens a new physical connection on every getConnection call, and the URL and password are passed in every call. It is fine for learning examples, utilities and tests.
DataSource (package javax.sql) is a connection factory configured once. It is usually backed by a pool, and then close() does not close the connection but returns it to the pool. This is how web applications, Spring and application servers work.
The simplest implementation comes with the MySQL driver: MysqlDataSource with setURL, setUser, setPassword. It holds no pool; it only keeps the settings in one place. A pool comes from a separate library, most often HikariCP (the Spring Boot default): opening a TCP connection with authentication for every query is expensive, while a pool keeps several connections open and hands them out to threads.
8. What are the most common JDBC connection errors, and how do you fix them?
What are the most common JDBC connection errors, and how do you fix them?
No suitable driver found for jdbc:mysql://...— the driver JAR is not on the classpath, or the start of the URL has a typo. Addmysql-connector-j, check the URL.ClassNotFoundException: com.mysql.cj.jdbc.Driver— the same missing driver, found byClass.forName.Communications link failure— the server is not running, wrong host or port, or the port is blocked by a firewall.Access denied for user 'root'@'localhost'— wrong user name or password, or no rights on the database (grant them withGRANT).Unknown database 'shop'— the database does not exist: runCREATE DATABASE shopor fix the name in the URL.Public Key Retrieval is not allowed— MySQL 8+ withcaching_sha2_passwordanduseSSL=false. Enable SSL or addallowPublicKeyRetrieval=true, but only for a local database: on a production server it exposes the password to interception.