JDBC

Question 1

What module is used to implement JDBC functionalities?

Answer 1

java.sql

Question 2

What package contains JDBC classes?

Answer 2

java.sql.*

Question 3

How does JDBC URL format look like?

Answer 3

jdbc:oracledb://localhost:54332/db

jdbc protocol
driver name
address and port
database name

Question 4

What type of JDBC is used in exams?

Answer 4

DriverManager

Question 5

What is the starting number of columns?

Answer 5

They count from 1, not 0

Question 6

How is JDBC roughly structured?

Answer 6

Driver -> Connection -> PreparedStatement, CallableStatement -> ResultSet

Question 7

How do we turn on auto. commit of the transactions?

Answer 7

conn.setAutoCommit(true)
This auto. commits current transaction, and every transaction that comes after it is auto. commited

Question 8

How can we turn on manual commit of the transactions?

Answer 8

conn.setAutoCommit(false)
Then we can conn.commit() or conn.rollback()
If we close connection before committing or roll backing, transaction may or may not be committed.

Question 9

How do we use bookmarks in JDBC?

Answer 9

We need to set autocommit to false.
Then we can set a new Savepoint().
When we want to go back to that savepoint, we just conn.rollback(savepoint);

Question 10

What do we need to close after using in JDBC?

Answer 10

Connection, PreparedStatement, ResultSet

Question 11

When is Statement used?

Answer 11

When we don’t need to provide user input data into SQL

Question 12

When is PreparedStatement used?

Answer 12

When we need to provide user input data into SQL. It also provides better performance when queries are used repeatedly, because DB caches prepared statements

Question 13

How do we create connection to DB?

Answer 13

Connection conn = DriverManager.gerConnection(url);
Best defined inside try-with-resources clause.

Question 14

How do we create Statement or PreparedStatement?

Answer 14

PreparedStatement ps = conn.prepareStatement(“SQL here”);
Best defined inside try-with-resources clause.

Question 15

Can SQL string in statement be empty?

Answer 15

No, there must be a query

Question 16

Can Statement be used multiple times?

Answer 16

Yes

Question 17

What extra properties does PreparedStatement has?

Answer 17

First comes type, ResultSet.TYPE_FORWARD_ONLY, TYPE_SCROLL_INSENSITIVE, TYPE_SCROLL_SENSITIVE,
then comes concurrency - CONCUR_READ_ONLY, CONCUR_UPDATEABLE. Both must be set if used.

Question 18

What PreparedStatement method do we use for INSERT, UPDATE or DELETE?

Answer 18

executeUpdate()

Question 19

What does PreparedStatement’s executeUpdate() metod return?

Answer 19

Number of affected rows

Question 20

What PreparedStatement method do we use for SELECT?

Answer 20

executeQuery()

Question 21

Where is best place to do PreparedStatement’s executeQuery(), or put ResultSet in general?

Answer 21

In try-with-resources clause

Question 22

What is alternative to executeQuery() or executeUpdate?

Answer 22

ps.execute()
Then we can get result set with: ResultSet rs = ps.getResultSet()
Or we can get number of affected rows: int result = ps.getUpdateCount()

Question 23

How do we use variables in PreparedStatement?

Answer 23

Query must contain ? at places where variables need to be.
Then we define variables by their type :
ps.setInt(1, 1)
ps.setLong(2, 3L)
ps.setNull(3, null)
There must be exact number of parameters set as there are ? in query.

Question 24

How do we execute database procedures without any parameters?

Answer 24

String sql = “{call procedure_name()}”;
1. conn.prepareCall(sql)
2. cs.executeQuery()

Question 25

How do we call database procedures that have IN parameters?

Answer 25

String sql = “{call procedure_name(?)}”;
1. conn.prepareCall(sql)
2. cs.setString(prefix”, “Z”)
3. cs.executeQuery()

Question 26

How do we execute database procedures that have OUT parameters?

Answer 26

String sql = “{? = call procedure_name(?)}”;
try (Connection con = DriverManager.getConnection(“”)) {
try (CallableStatement ps = con.prepareCall(“”)) {
ps.execute();
ps.registerOutParameter(1, Types.VARCHAR);
try (ResultSet rs = ps.getResultSet()) {
while (rs.next()) {
System.out.println(rs.getString(1));
}
}
}
}

Question 27

How do we execute database procedures that have IN OUT parameters?

Answer 27

String sql = “{call procedure_name(?)}”;
1. conn.prepareCall(sql)
2. cs.setString(prefix”, “Z”)
3. cs.registerOutParameter(1, Types.INTEGER);
4. cs.execute();
5. cs.getInt(“num”);

Question 28

How do we read from ResultSet?

Answer 28

We call rs.next(), which moves cursor from starting point to 0 and upwards.
rs.next() returns true until there is no more results, then it returns false.
We usually put rs.next() into while().
We get data by calling rs.getInt(“column name”), or rs.getString() etc.
We usually must check if there is something in ResultSet: