Week 5 Flashcards
What is the difference between a simple statement and a prepared statement in JDBC?
A simple statement is used for executing a single SQL query directly. A prepared statement is precompiled and can be reused with different parameters, improving performance and security.
Write an example of using a prepared statement in Java to insert data into a database.
PreparedStatement pstmt = connection.prepareStatement(“INSERT INTO users (name, email) VALUES (?, ?)”);
pstmt.setString(1, “John Doe”);
pstmt.setString(2, “john@example.com”);
pstmt.executeUpdate();
Explain how prepared statements help in preventing SQL injection attacks.
Prepared statements use parameterized queries, which separate SQL code from data, preventing malicious input from altering the query structure.
How do prepared statements help in improving performance?
Prepared statements are precompiled and can be executed multiple times with different parameters without the overhead of compiling the SQL statement each time.
Can you reuse a prepared statement in JDBC? How?
Yes, prepared statements can be reused. Once prepared, you can set different parameters and execute the statement multiple times.
Write an example of using a simple statement to execute a query in Java.
Statement stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery(“SELECT * FROM users”);
What are some scenarios where you would prefer using a simple statement over a prepared statement?
You might use a simple statement for executing static queries or when performance is not a concern. However, prepared statements are preferred for dynamic user inputs.
What is parameter binding in prepared statements, and how does it work?
Parameter binding allows you to specify placeholders in the SQL query (e.g., ?) and then set values for these placeholders using methods like setString(), setInt(), etc.
Why is it not recommended to use simple statements with dynamic SQL queries?
Concatenating user inputs in SQL queries can lead to SQL injection, as malicious users can manipulate the input to alter the query logic.
How does a prepared statement handle queries with user input compared to a simple statement?
Prepared statements ensure that user input is treated as data, not executable code, thus protecting against SQL injection.
What common information is stored in a JDBC properties file?
JDBC properties files typically include database URL, username, password, driver class name, and any additional connection properties.
What is a JDBC properties file, and why is it used?
A JDBC properties file is a configuration file that contains database connection parameters (like URL, username, and password) to simplify database access in Java applications.
How can you load a JDBC properties file in a Java application?
Properties props = new Properties();
try (InputStream input = new FileInputStream(“db.properties”)) {
props.load(input);
}
How can you retrieve database connection details from a JDBC properties file in Java?
String url = props.getProperty(“db.url”);
String user = props.getProperty(“db.user”);
String password = props.getProperty(“db.password”);
How does using a properties file simplify database configuration?
It centralizes configuration settings, making it easier to modify connection parameters without changing the code.
Write a sample JDBC properties file for a MySQL database connection.
db.url=jdbc:mysql://localhost:3306/mydb
db.user=root
db.password=secret
db.driver=com.mysql.jdbc.Driver
How can you change the database configuration dynamically using a properties file?
You can use encryption for sensitive data or restrict access to the properties file through file permissions.
Can you secure sensitive information in a JDBC properties file? If so, how?
What are some best practices for maintaining and using JDBC properties files?
By editing the properties file, you can change connection settings without recompiling the application.
How can you handle exceptions when loading a JDBC properties file?
Keep the properties file outside the source code, avoid hardcoding sensitive information, and document configuration options.
What is a utility class in the context of JDBC, and why is it useful?
Use try-catch blocks when loading the properties file to handle potential IOException.
How do you set up a utility class for managing database connections in Java?
A utility class can be set up by creating a class with methods for opening and closing database connections.
public class DBUtil {
public static Connection getConnection() { /* implementation */ }
public static void closeConnection(Connection conn) { /* implementation */ } }
What methods should a JDBC utility class contain to manage connections effectively?
Methods for getting a connection, closing connections, handling transactions, and error logging.
Write a sample utility class that provides a method to get a database connection.
public class DBUtil {
private static final String URL = “jdbc:mysql://localhost:3306/mydb”;
private static final String USER = “root”;
private static final String PASSWORD = “secret”;
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(URL, USER, PASSWORD);
}
public static void closeConnection(Connection conn) {
if (conn != null) {
try {
conn.close();
} catch (SQLException e) { /* handle exception */ }
}
} }
