JDBC Flashcards
What module is used to implement JDBC functionalities?
java.sql
What package contains JDBC classes?
java.sql.*
How does JDBC URL format look like?
jdbc:oracledb://localhost:54332/db
jdbc protocol
driver name
address and port
database name
What type of JDBC is used in exams?
DriverManager
What is the starting number of columns?
They count from 1, not 0
How is JDBC roughly structured?
Driver -> Connection -> PreparedStatement, CallableStatement -> ResultSet
How do we turn on auto. commit of the transactions?
conn.setAutoCommit(true)
This auto. commits current transaction, and every transaction that comes after it is auto. commited
How can we turn on manual commit of the transactions?
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.
How do we use bookmarks in JDBC?
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);
What do we need to close after using in JDBC?
Connection, PreparedStatement, ResultSet
When is Statement used?
When we don’t need to provide user input data into SQL
When is PreparedStatement used?
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
How do we create connection to DB?
Connection conn = DriverManager.gerConnection(url);
Best defined inside try-with-resources clause.
How do we create Statement or PreparedStatement?
PreparedStatement ps = conn.prepareStatement(“SQL here”);
Best defined inside try-with-resources clause.
Can SQL string in statement be empty?
No, there must be a query
Can Statement be used multiple times?
Yes
What extra properties does PreparedStatement has?
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.
What PreparedStatement method do we use for INSERT, UPDATE or DELETE?
executeUpdate()
What does PreparedStatement’s executeUpdate() metod return?
Number of affected rows
What PreparedStatement method do we use for SELECT?
executeQuery()
Where is best place to do PreparedStatement’s executeQuery(), or put ResultSet in general?
In try-with-resources clause
What is alternative to executeQuery() or executeUpdate?
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()
How do we use variables in PreparedStatement?
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.
How do we execute database procedures without any parameters?
String sql = “{call procedure_name()}”;
1. conn.prepareCall(sql)
2. cs.executeQuery()
How do we call database procedures that have IN parameters?
String sql = “{call procedure_name(?)}”;
1. conn.prepareCall(sql)
2. cs.setString(prefix”, “Z”)
3. cs.executeQuery()
How do we execute database procedures that have OUT parameters?
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));
}
}
}
}
How do we execute database procedures that have IN OUT parameters?
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”);
How do we read from ResultSet?
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:
