Chapter 10: JDBC

Question 1

DDL and DML

Answer 1

DB definition language and DB manipulation language

Question 2

SQL statements

Answer 2

INSERT: add new row
SELECT: Retrieve from table
UPDATE: Change 0 or more rows
DELETE: Remove 0 or more rows

Question 3

JDBC Interfaces

Answer 3

JDBC has interfaces which can be used by a Driver (which provides the implementation of the interfaces).

Interfaces:
Driver -> gets a connection to DB
Connection -> communicates with DB
Statement -> Runs SQL
ResultSet -> Returned by SELECT

Question 4

Connecting to a DB

Answer 4

jdbc:postgres://…

Here jdbc is the protocal (always jdbc)
postgres is the vendor name
and … are specific connection parameters

Needs to include DB name, but host/port are optional.

Question 5

Getting a connection to the DB

Answer 5

Either through a DriverManager or a DataSource. DriverManager is covered in the exam but we should use DataSource in real life!

DriverManager.getConnection(url) static factory method. Remember, all objects returned from static factory methods will give vendor implementations of the interfaces.

Can also do .getConnection(url, username, password)
Remember this is static –> you should watch out for a constructor in the exam!

Used to have to do Class.forName(“”) to get the connection without looking in META-INF, but not needed since JDBC 4..0 >

Question 6

Obtaining a statement

Answer 6

Statement s = connection.createStatement()
or s = connection.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY)

When the requested mode is not supported, it will be downgraded to one that is (rather than throwing an exception)

Question 7

ResultSet type

Answer 7

TYPE_FORWARD_ONLY -> goes through data once in order. This is the default if it a type isn’t provided. I.e. we can’t do previous or next (we can’t scroll) -> so watch out for this in the exam!
TYPE_SCROLL_INSENSITIVE -> you see data s it was when you ran the query, but can scroll
TYPE_SCROLL_SENSITIVE -> You can see data as it is updated when scrolling through ResultSet
CONCUR_READ_ONLY -> You can’t change the ResultSet once you’ve retrieved
CONCUR_UPDATABLE -> Can modify DB then ResultSet

Question 8

Executing a statement

Answer 8

statement.executeUpdate(SQL) -> for DELETE, INSERT, UPDATE, will return int of number of rows affected
statement.executeQuery(SQL) -> Used for SELECT and returns a ResultSet.
statement.execute(SQL) returns a boolean of if it is a resultSet.
If it is a resultSet, we can call statement.getResultSet(), otherwise we can call statement.getUpdateCount().

If we use a bad SQL string, we will get SQLException

Question 9

Getting data from a ResultSet

Answer 9

Forward-only ResultSet:
Loop through using while(rs.next()), which uses a cursor to go through the rows to determine if any more data can be read. If you don’t call rs.next() the cursor still points to previous row

You can retrieve data using rs.getString(“colName”)
or rs.getInt(“colName”)
Or you can use column idnex e.g. getString(2) getInt(1) etc:
Note that JDBC starts indexing at 1 rather than 0.

If you're just returning a single row (or a count value of number of rows)
Use if (rs.next()) to check if there is a value, otherwise could throw SQLException

Question 10

SQL Dates and Time

Answer 10

java. sql.Date -> has a .toLocalDate() method
java. sql.Time -> has a .toLocalTime() method
java. sql.TimeStamp -> has a .toLocalDateTime() method

Question 11

Scrollable ResultSet

Answer 11

Make sure type is Scrollable when you see anything other than .next()!
boolean .absolute(int) moves cursor to specified row number (returns false if no data in row) Negative numbers start counting from the back
void afterLast() cursor to location immediately after last
void beforeFirst()
--> note remember you can't print out void it won't compile!
boolean first()
boolean last()
boolean previous()
boolean relative(int) back of forward #of rows (negative moves backwards)

Question 12

Closing resources in correct order

Answer 12

Try-with-resources:
try (Connection conn= …
Statement stmt = …
ResultSet rs = …)
Java closes resources in reverse order, so will close rs, stmt, then conn.
Closing Connection closes stmt and rs.
If you exeucte a new Query with same statement object, it wll close previous resultset

Question 13

SQLException

Answer 13

.getMessage() human readable
.getSQLState()
.getErrorCode()

last 2 are DB specific

SQLException is checked - so all code needs to declare it throws it or handle it!

Question 14

Driver implementation in JAR file

Answer 14

In JAR file need to have META/service/java.sql.Driver which is a file that will contain the package name of the impl class for JDBC 4.0 >