12. JDBC

Question 1

What are the 3 important steps when working with a database?

Answer 1

1. Creating a connection to the database
2. Creating a statement to execute in the database
3. Getting back a set of data that represents the result

Question 2

What is the driving force behind JDBC?

Answer 2

To provide a standard way to acces relational databases, but JDBC can also be used to acces file systems and OO data sources

Question 3

How is a JDBC driver provided?

Answer 3

A JDBC driver is typically provided by the vendor in a JAR or ZIP file.

Question 4

What are the requirements for a JDBC driver?

Answer 4

• Fully implement the interfaces: java.sql.Driver, java.sql.DatabaseMetaData, java.sql.ResultSetMetaData
• Implement the java.sql.Connection interface
• Implement java.sql.Statement and java.sql.PreparedStatement
• Implement the java.sql.CallableStatement interface if the database supports stored procedure
• Implement the java.sql.ResultSet interface

Question 5

What does the DriverManager class do?

Answer 5

This concrete class is used to interact with a JDBC driver and return instances
of Connection objects

Question 6

Which method does the DriverManager class have?

Answer 6

getConnection() method

Question 7

What does the DriverManager.getConnection() method do?

Answer 7

When you invoke the DriverManager’s getConnection(), you are asking the DriverManager to try passing the first String in the statement, the driver url, along with the username and password to each of the driver classes registered with the DriverManager in turn. If one of the driver classes
recognizes the URL string and the username and password are accepted, the driver returns an instance of a Connection object. If however, the URL is incorrect, or username or password or if there are no registered drivers, the method will throw an SQLException

Question 8

What does a JDBC URL look like?

Answer 8

jdbc:derby://localhost:1521/BookSellerDB

The first part (jdbc) simply identifies that this is a JDBC URL (versus HTTP or something else). The second part (derby) indicates that driver vendor is derby driver. The third part indicates that the database is on the localhost of this machine at port 1521. And the final part indicates that we are interested in the BookSellerDB database.

Question 9

What to do with JDBC < version 4?

Answer 9

If you use a JDBC driver that is an earlier version, then you must explicitly load the class provided by
the database vendor that implements the java.sql.Driver interface. For example:
Class.forName(“org.apache.derby.jdbc.ClientDriver”);

Question 10

Wich of the JDBC API method invocations throw SQLExceptions?

Answer 10

All of the JDBC API method invocations throw SQLException. A SQLException can be thrown when a method is used improperly of if the database is no longer responding.

Question 11

How can we create a Statement Object?

Answer 11

We can create an instance of a statement object from a connection object.

Question 12

What does a Statement object do?

Answer 12

The primary purpose of a Statement is to execute a SQL statement using a method and return
some type of result.

Question 13

public ResultSet executeQuery(String sql) throws SQLException

Answer 13

this most common method is used when we know that we want to return result – we are querying the database for one of more rows of data. This method must be called in a try-catch block or must be called in a method that also throws SQLException. One reason that these methods all throw SQLException is that a connection to the database is likely to a database on a network.

Question 14

public int executeUpdate(String sql) throws SQLException

Answer 14

This method is used for SQL operation that affects one or more rows and does not return results (vb. SQL INSERT, UPDATE, DELETE and DDL queries). These statements do not return results, but do return a count of the number of rows affected by the SQL query. Note that this Statement method can also be used to execute SQL queries that do not return a row count, such as CREATE TABLE or DROP TABLE. For DDL queries the return value is 0.

Question 15

public boolean execute(String sql) throws SQLException

Answer 15

This method is used when you are not sure what the result will be – perhaps the query will return a result set and perhaps not. This method can be used to excute a query whose type may not be known until runtime. The return value is true if the query resulted in a result set and false if the query resulted in an update count or no result.

Question 16

What if the execute method returns true / false?

Answer 16

The getResultSet() is used to retrieve results when the execute() method return true (if there are no results you will get null), and the getUpdateCount() is used to retrieve the count when the execute() method returns false (if there are no result you will get -1).

Question 17

What is a ResultSet?

Answer 17

When a query returns a result set, an instance of a class that implements the ResulSet interface is returned. The ResultSet object represent the result of the query – all of the data in each row on a per column basis. Using the methods defined in the ResultSet interface, you can read and manipulate
data.

Question 18

What does the ResultSet.next() method do?

Answer 18

The next() method moves the cursor forward one row and returns true if the cursor now points to a row of data in the ResultSet. If the cursor is beyond the last row of data or contains no rows, its return value is 0.

Question 19

With which number starts a column index?

Answer 19

1

Question 20

How to get information about the returned results?

Answer 20

Using ResultSetMetaData

Question 21

ResultSetMetaData.getColumnCount()

Answer 21

is the most used ResultSetMetaData method, it returns the
integer count of the number of columns returned by the query. With this method you can iterate through the columns to get information about each column.

Question 22

ResultSetMetaData.getColumnName(int column)

Answer 22

returns the string name of this column.

Question 23

ResultSetMetaData.getTableName(int column)

Answer 23

returns the String name of the table that this column belongs to. This
method is useful when the query is a join of two or more tables and we need to know which table a column came from.

Question 24

ResultSetMetaData.getColumnDisplaySize(int column)

Answer 24

method returns an integer of the size of the column. This
information is useful for determining the maximum number of characters a column can hold, and the spacing that is required between columns for a report.

Question 25

What does the try with resources do with Connections, Statements and ResultSets?

Answer 25

The try-with resources statement will automatically call the close() method on any resource declared in the parantheses at the end of the try block.

Question 26

How are the resources closed?

Answer 26

when more than one resource is declared in the try-with0resources statement, the
resources are closed in the reverse order of their declaration.

Question 27

What happens with exceptions thrown when closing resources?

Answer 27

Any exception thrown as a result of closing resources at the end of the try block are sppressed, if there was also an exception thrown in the try block. These exceptions can be retrieved from the exception thrown by calling the getSuppressed() method on the exception thrown.