Java Assignment- 11

JDBC & MySQL

Basic Questions

  1. Write a small Java program that prints the JDBC driver class name for MySQL and a sample JDBC URL for the student database.
  2. Write a program that opens a Connection to jdbc:mysql://localhost:3306/student, prints “connected”, and then closes the connection safely.
  3. Write code that creates a Statement, executes SELECT NOW(), reads the single value from ResultSet, prints it, and closes all resources.
  4. Create a table students(id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100), email VARCHAR(150)) (use JDBC to execute the DDL). Print a success message.
  5. Insert one row into students using a PreparedStatement with parameters (name, email); use an email like alice@learntoearnlabs.com. Print the number of rows inserted.
  6. Query all rows from students using Statement, iterate the ResultSet, and print id, name, email for each row.
  7. Show the difference between run-time SQL concatenation (unsafe) and PreparedStatement (safe) by writing both versions of a query that searches by name. Only execute the safe version.
  8. Catch a SQLException and print out getMessage(), getSQLState(), and getErrorCode(). Use a deliberate bad SQL to trigger the exception.
  9. Demonstrate try-with-resources to auto-close Connection, Statement, and ResultSet in a simple SELECT 1 program.
  10. Use PreparedStatement to update a student’s email by id. Print rows affected.
  11. Use PreparedStatement to delete a student by id. Print rows affected.
  12. Select a single student by id and handle the case where no row is found (print “not found”).
  13. Demonstrate reading NULL safely: insert a row with email = NULL, select it, and print “email=null” if rs.getString(“email”) is null.
  14. Insert a row and retrieve the generated key (id) using RETURN_GENERATED_KEYS. Print the new id.
  15. Use PreparedStatement to insert two rows into students in one run (execute twice with different parameters).
  16. Demonstrate setting fetch size for a large SELECT by calling stmt.setFetchSize(50) before executing a query (use any table). Print a note that this is a hint to the driver.
  17. Write a program that reads a date from Java (e.g., LocalDate.now()), converts it to java.sql.Date, inserts it into a table admissions(id INT AUTO_INCREMENT, join_date DATE), and prints rows.
  18. Write code that reads DATE, TIME, and TIMESTAMP columns from a table and prints them as Java types (LocalDate, LocalTime, LocalDateTime) using appropriate conversions.
  19. Show how to map one row from students to a simple Java object Student { int id; String name; String email; } and print the object.
  20. Explain in code comments the steps to connect Java with MySQL (load driver if needed, get connection, create statement, execute, process results, close).

Intermediate Questions

  1. Perform CRUD end-to-end on students: insert 2 students, read all, update one name, delete one by id, and print the final list.
  2. Use PreparedStatement batch to insert 5 students efficiently (addBatch/executeBatch). Print the returned update counts.
  3. Demonstrate a transaction: set autoCommit(false), insert two related rows (e.g., into students and admissions), then commit(). Add a catch that calls rollback() on error.
  4. Show autoCommit(true) vs autoCommit(false) by timing a loop of 100 small inserts in each mode. Print both times with a short note.
  5. Use a stored procedure: create sp_find_student_by_id(IN p_id INT) in MySQL (selects one row). Call it with CallableStatement, read the row, and print it.
  6. Write a program that calls a stored procedure with an OUT parameter (e.g., sp_count_students(OUT total INT)) using CallableStatement. Print the OUT value.
  7. Create and use a DataSource (com.mysql.cj.jdbc.MysqlDataSource): set URL, user, password; get a Connection; run a simple query; close the connection.
  8. Explain and demonstrate connection pooling briefly by acquiring multiple connections from a DataSource in a loop (simulate pool use). Add comments on what pooling is and why it helps.
  9. Show batch updates with mixed operations: insert, update, and delete using a Statement batch. Print update counts and explain in a comment that failures may stop the batch depending on driver settings.
  10. Demonstrate paging: select students 5 at a time using LIMIT ? OFFSET ? with PreparedStatement. Print page 1 and page 2.
  11. Demonstrate safe handling of NULL when setting a parameter: call ps.setNull(index, Types.VARCHAR) if a Java value is null; otherwise ps.setString(…).
  12. Demonstrate getObject with target type (e.g., rs.getObject(“join_date”, LocalDate.class)) for date/time columns.
  13. Write a method Student mapRow(ResultSet rs) that reads current row into a Student object. Use it in a loop to build a List<Student> and print the size.
  14. Write a small JDBC utility class JdbcUtil with getConnection() and closeQuietly(AutoCloseable…). Use it in a sample query.
  15. Log the exact SQL before executing it by printing the SQL string and parameter values (simple logging with System.out.println or java.util.logging). Execute after logging.
  16. Handle SQLSyntaxErrorException specifically in a catch block and print a friendly “check your SQL” message. Then have a general SQLException catch as fallback.
  17. Demonstrate setFetchSize and setMaxRows together on a Statement and print how many rows you actually processed.
  18. Insert a student with a duplicate email (add a unique index to students(email) first), catch the exception, and print a clear message like “email already exists”.
  19. Write code that reads DB config from a .properties file (URL, user, password) and uses it to open a connection. Print “connected”.
  20. Use PreparedStatement to perform a parameterized IN-like filter by building SQL with the right number of placeholders for an input list size (e.g., 3 names → IN (?,?,?)). Execute and print matches.

Advanced Questions

  1. Configure and use a small connection pool (e.g., HikariCP or Apache DBCP2). Get a DataSource from the pool, run a few queries, and show in logs that connections are reused. Add comments on pooling benefits.
  2. Implement a robust transaction that transfers “credits” between two rows in a table wallet(id INT, balance DECIMAL): check balances, update both rows, commit; on any failure, rollback. Print the final balances.
  3. Implement a retry on deadlock: run a transaction that may deadlock (simulate with two threads or two connections). If SQLState indicates deadlock/lock timeout, retry up to 3 times with a small backoff.
  4. Build a small DAO layer: interface StudentDao { int save(Student s); Optional<Student> findById(int id); List<Student> findAll(); } and a JDBC implementation using only PreparedStatement. Demonstrate with main.
  5. Write a secure login check that avoids SQL injection: SELECT id FROM users WHERE email=? AND password_hash=?. Hash is a placeholder string here. Show in comments why string concatenation is unsafe.
  6. Implement structured SQL logging: log SQL string, parameter indexes→values, execution time in ms, and rows affected/read. Print one formatted line per statement.
  7. Write a custom row mapper functional interface RowMapper<T> with T map(ResultSet rs), and a generic method List<T> query(String sql, PreparedStatementSetter s, RowMapper<T> m). Use it to load Student objects.
  8. Show batch vs single inserts performance by timing 5,000 inserts with single executeUpdate calls vs one PreparedStatement with addBatch/executeBatch. Print both times and a short note.
  9. Demonstrate best practices in one sample: try-with-resources everywhere, PreparedStatement only, meaningful SQL names, small transactions, proper error messages, and finally a short bullet list (as comments) of best practices for efficient JDBC code.
  10. Build a stored procedure sp_register_student(IN p_name VARCHAR(100), IN p_email VARCHAR(150), OUT p_id INT) that inserts a row and returns the new id. Call it with CallableStatement, read the OUT parameter, and print the new id.