Chapter 15: JDBC Flashcards
What method is used in JDBC to obtain a database connection using DriverManager?
DriverManager.getConnection(url, userid, pwd) is used to obtain a database connection.
What exception must be handled or declared when calling DriverManager.getConnection()?
SQLException must be handled or declared.
What is the preferred way of obtaining a database connection in enterprise applications?
Using javax.sql.DataSource instead of java.sql.DriverManager.
Why is DataSource preferred over DriverManager in enterprise applications?
Because DataSource does not require hardcoded database credentials and supports connection pooling.
How can an application retrieve a DataSource in an enterprise environment?
By using JNDI lookup:
Context ctx = new InitialContext();
DataSource ds = (DataSource) ctx.lookup(“java:/comp/env/jdbc/MyLocalDB”);
Connection c = ds.getConnection();
What is a major performance benefit of using DataSource?
DataSource provides connection pooling, reducing the overhead of creating and closing connections.
What is an alternative way to pass credentials when using DriverManager.getConnection()?
Using a Properties object:
Properties p = new Properties();
p.setProperty(“user”, userid);
p.setProperty(“password”, pwd);
Connection conn = DriverManager.getConnection(url, p);
In which JDBC version was using a Properties object to pass credentials introduced as a valid approach?
JDBC 4.0
What happens if you pass invalid credentials to DriverManager.getConnection()?
A SQLException is thrown indicating authentication failure.
What is a PreparedStatement in Java?
A PreparedStatement represents a precompiled SQL statement that can be executed multiple times efficiently.
Why is a PreparedStatement more efficient than a regular SQL statement?
Because it is precompiled by the database, reducing parsing and optimization overhead when executed multiple times.
How do you set a NULL value for a parameter in a PreparedStatement?
Use the setNull(int parameterIndex, int sqlType) method, specifying the SQL type from java.sql.Types.
What happens if parameterIndex in setNull does not correspond to a valid parameter?
A SQLException is thrown.
What should you do if you need to set a parameter in a PreparedStatement with a different type than its SQL type?
Use setObject(int parameterIndex, Object x, int targetSqlType) to specify the target SQL type.
What are some SQL types that might not be supported by setNull in all JDBC drivers?
Types like ARRAY, BLOB, CLOB, DATALINK, JAVA_OBJECT, NCLOB, ROWID, SQLXML, and STRUCT may not be supported.
What method would you use to prepare a PreparedStatement in Java?
Connection.prepareStatement(String sql)
What is an example of setting a BigDecimal and an int parameter in a PreparedStatement?
BigDecimal sal = new BigDecimal(“153833.00”);
PreparedStatement pstmt = con.prepareStatement(“UPDATE EMPLOYEES SET SALARY = ? WHERE ID = ?”);
pstmt.setBigDecimal(1, sal);
pstmt.setInt(2, 110592);
What exception is thrown if a PreparedStatement method is called on a closed statement?
SQLException
What are the advantages of using a PreparedStatement over a regular Statement?
- Performance: Precompiled SQL improves execution speed.
- Security: Helps prevent SQL injection attacks.
- Reusability: The same statement can be executed multiple times with different parameters.
What does the commit() method do in Java’s JDBC Connection interface?
The commit() method makes all changes made since the last commit or rollback permanent and releases any database locks held by the Connection object. It should only be used when auto-commit mode is disabled.
What exceptions or conditions can cause the commit()in Java’s JDBC Connection interface method to throw an SQLException?
The commit() method throws an SQLException if:
1. A database access error occurs.
2. The method is called while participating in a distributed transaction.
3. The method is called on a closed connection.
4. The Connection object is in auto-commit mode.
What does the setAutoCommit(boolean autoCommit) method do in Java’s JDBC Connection interface?
The setAutoCommit method enables or disables auto-commit mode for a database connection.
* If true, each SQL statement is automatically committed as an individual transaction.
* If false, SQL statements are grouped into transactions that require an explicit commit() or rollback().
* By default, new connections are in auto-commit mode.
What are the key considerations when using setAutoCommit(boolean autoCommit)?
- If called during a transaction, changing auto-commit commits the current transaction.
- If the mode is not changed, the call is a no-op.
- Throws SQLException if:
* A database access error occurs.
* setAutoCommit(true) is called while participating in a distributed transaction.
* The method is called on a closed connection.
