Lecture 6 - JDBC

Question 1

What is JDBC and what is it for?

Answer 1

Gives a standard API for databases (for programming access)

Question 2

What does java.sql.DriverManager do?

Answer 2

loads JDBC drivers and creates Connection objects

Question 3

What does DriverManager.getConnection(String url) do?

And what is the format of the URL?

Answer 3

Looks for the driver to handle the URL connection.

jdbc:subprotocol:datasourcename

NOTE: username + password can be given in the URL.

Question 4

What is jdbc.drivers?

Answer 4

A system property that drivers are loaded into.

Question 5

What exception do JDBC methods usually throw?

Answer 5

SQLException

Question 6

What is a statement object?

Answer 6

Create SQL Statement from the connection
to return Statement object

Used to query

Question 7

How do we connect with properties to avoid hard-coding?

Answer 7

Create new java.util.Properties object (e.g.
options)

Use options.load(new FileInputStream(..))
OR
Query a property with options.getProperty

Question 8

How do we query a database?

Answer 8

1. create a statement using connection.createStatement();

OR prepareStatement with SQL_command template that can be filled with values (useful for user input)

2. statement.executeQuery(some_SQL_code)
3. a result set is returned

Question 9

How do we create and fill placeholder values for prepareStatement()?

Answer 9

?
setType(position, value)
– E.g. setInt, setString, setDate, …

Question 10

How do we work with ResultSet object?

Answer 10

Data is organized into columns and rows

call next() to get first row
returns false if no next row

column data obtained with getType()
(Ex: getInt, getString, getDate)

Question 11

What is the issue with JDBC and why would be have to learn Jakarta Persistence?

Answer 11

Major issue is connections are expensive.

This is solved by creating a connection pool using javax.sql.DataSource.

Question 12

ResultSet can return object that contains
information about the returned results (T or F)

Answer 12

True
ResultSetMetaData

Question 13

Why do we NOT make a SQL query by concatenating user input?

Answer 13

Use prepared statements instead, will prevent attacks and SQL exceptions.

Question 14

How can we access SQL warnings?

Answer 14

Less severe errors are not thrown, but generate
SQLWarning or DataTruncation warnings
getWarnings() method of any Connection,
Statement, ResultSet object