Complete JDBC Programming Part-2

Understand stored procedures as groups of sql queries stored in the database, with in, out, and in out parameters, invoked from a java application using a callable statement.

Learn to create and compile an Oracle procedure with in and out parameters, using create or replace and slash for compilation, then declare a variable and execute to display results.

Learn to create and execute Oracle stored procedures with in and out parameters in SQL Plus, illustrated by square and salary examples, and prepare for Java callable statements.

Map Java data types to database types using JDBC bridge types, enabling stored procedures, with constants from java.sql.Types.

Learn to call a stored procedure from a Java application using a CallableStatement by following six steps: ensure procedure exists, set input values, register out parameters, execute, and retrieve results.

Explore calling stored procedures from Java using JDBC. Learn to create the procedure, use a callable statement, bind inputs, register the out parameter, execute, and retrieve results.

Create a stored procedure with an input employee id and an output salary, and call it from a JDBC program using a callable statement.

Learn to write a stored procedure that takes an employee number as input and returns name and salary as out parameters, and call it from Java JDBC application using CallableStatement.

Explore cursor concepts in databases and Java, including implicit and explicit cursors, ref cursors, and percentage row count. Learn how to use JDBC to call stored procedures that use cursors.

Learn how to create a stored procedure that returns a result set using a ref cursor, register the out parameter with Oracle types cursor, and fetch results in JDBC.

Learn to implement stored procedures with ref cursors, call them from JDBC, and retrieve result sets using OracleTypes.CURSOR for dynamic employee data queries.

Learn the difference between procedures and functions, write a function with a return value, and call it from Java using CallableStatement to compute averages like salary.

Create a function that returns employee information between two numbers and call it from Java with a callable statement, handling a ref cursor and the result set.

Learn to create a function that returns a ref cursor and an out parameter, then call it via JDBC CallableStatement to delete employees with salary cutoff and report the count.

Explore the three types of JDBC statements—statement, prepared statement, and callable statement—and learn their parent-child relationships, creation, and when to use each for queries and stored procedures.

Demonstrates batch updates by grouping multiple SQL queries into a single batch and sending to the database, improving performance and reducing network traffic.

Explore the need and benefits of batch updates in JDBC, comparing simple and prepared statements, and learn how batching reduces request, compilation, and network time for multiple queries.

Demonstrates batch updates in JDBC using addBatch and executeBatch to run insert, update, and delete statements, returns an int[] of results and total rows affected, with non-select queries only.

Demonstrates using a prepared statement with batch updates to insert multiple records into the employees table, boosting performance. Builds the prepared statement, adds to the batch, and executes the batch.

Learn how batch updates group multiple non-select sql queries into a batch using simple or prepared statements, via add batch and execute batch, to improve performance and reduce network traffic.

Four execute methods exist in jdbc: execute query for selects, execute update for non select operations, execute for mixed results or stored procedures, and execute batch for batch updates.

Learn the differences and relationship between java.util.Date and java.sql.Date, when to use each for database operations, and how date versus time and timestamp are handled.

Learn to insert date values in JDBC using prepared statements, converting string input to java.sql.Date via SimpleDateFormat, and handling database-specific date formats.

Insert date values into a database with JDBC by converting end-user string input to java.util.Date using SimpleDateFormat, then to java.sql.Date for the prepared statement.

Learn how to insert date values from end-user strings into a database by converting to java util date and then java sql date, or using java.sql.Date.valueOf shortcut with prepared statements.

Retrieve date values from the users table in the database and display them to end users. Cover using prepared or simple statements, result sets, getDate, and simple date format.

Explore date handling for database operations by comparing java.util.Date and java.sql.Date, their relationship as child of java.util.Date, and conversions between util date, sql date, and string formats with SimpleDateFormat.

Explore large objects in JDBC, introducing blob and clob as binary and character large objects, with examples like images, audio, video, resumes and XML, and note the four‑GB size limit.

Create a table with a blob column, read an image file as binary data via a file input stream, and insert it using a prepared statement with set binary stream.

Learn how to insert an image as a blob into a database using JDBC by reading a file, binding binary stream with a prepared statement.

Learn how a buffer improves input/output performance by reducing reads and writes using a truck analogy, and apply buffering when reading a blob image from a database with JDBC.

read blob type image data from a database using JDBC, by using a binary stream and buffering, then write to a local file with an output stream.

Learn to insert clob data into a database using JDBC by reading text files with a file reader and set character stream, contrasting with blob handling via binary streams.

Learn to retrieve clob type data from a database by mirroring the blob approach, using get character stream, a file writer, and buffered reads to a txt file.

Discover the connection pool concept: a pool of ready-to-use connections, reused to communicate with the database, improving performance and reducing costs.

learn how to implement connection pooling by creating a data source, configuring JDBC properties (url, user, password), and obtaining connections from the data source to reuse connections for better performance.

Demonstrate connection pooling for Oracle and MySQL databases using data source implementations, configure properties, obtain connections, and execute queries in a standalone application.

Compare driver manager get connection with data source get connection, showing driver manager creates new connections while data source pools connections for better performance and aligns with industry standards.

Explain why properties files are needed to avoid hard coding JDBC URL, username, and password, read properties in Java, and reflect changes without recompiling via redeploy.

Use a properties file to load JDBC settings into a Java program, read URL, user, and password via Properties, and switch between Oracle and MySQL without recompiling.

Learn to read database connection details from a properties file using java.util.Properties, load them at runtime, and switch from Oracle to MySQL without recompiling.

Learn to configure JDBC connections using a properties file by loading url, user, and password into a Properties object and obtaining a connection via three-argument or two-argument getConnection.

This lecture explains that DriverManager exposes three getConnection methods (three-argument, two-argument, and one-argument forms) and provides practical examples using MySQL and Oracle URLs, including properties-based authentication.

Learn how transaction management in JDBC combines related operations into a single unit, executing all or none to ensure data consistency in scenarios like funds transfer and ticket booking.

Examine two transaction types: local, where all operations stay on the same database, and global, across different databases. JDBC supports only local transactions; use EJB or Spring for global support.

Learn how to implement transactions in JDBC by using setAutoCommit(false), commit, and rollback to ensure all-or-none execution and preserve acid properties across local transactions.

Learn how to implement transactions in JDBC by debiting and crediting accounts with autocommit off, and using commit or rollback to ensure all-or-nothing updates.

Explore save point in jdbc 3.0, a java.sql save point interface, to set, rollback to, and release within a transaction, rolling back only selected operations.

Learn how to use savepoints in JDBC transactions to selectively roll back operations, set and release savepoints, and understand driver support in JDBC 3.0.

Explore how concurrent transactions in JDBC can cause dirty reads, non-repeatable reads, and phantom reads, and learn how isolation levels help overcome these problems.

Learn how JDBC transaction isolation levels—read uncommitted, read committed, repeatable read, and serializable—prevent dirty, non-repeatable, and phantom reads, with practical defaults in Oracle and MySQL.

Explore the five jdbc transaction isolation levels—none, read uncommitted, read committed, repeatable read, serializable—and learn which dirty read, non-repeatable read, and phantom read problems each level prevents, plus trade-offs.

Explore metadata in JDBC: learn data about data, including database metadata, result set metadata, and parameter metadata, and how to access these through JDBC.

Use the database metadata object from a JDBC connection to retrieve key details such as product name, version, max columns, and support for stored procedures and joins.

Learn to list all table names using database metadata and the getTables method. Explore how catalogs and schemas influence results and print the table names from the result set.

Explore result set metadata in JDBC, learn how to retrieve column count, names, and types from a result set using ResultSetMetadata, and understand driver-provided information.

Explore how to use Java's ResultSet metadata to display column names, types, and display sizes, and to print table data with headers from the employees table.

Explore parameter metadata in jdbc, learn how to obtain it from a prepared statement, and retrieve parameter count, mode, type, precision, and scale, with notes on driver support limitations.

This lecture shows how JDBC can read data from Excel sheets using an ODBC DSN and the Type 1 driver, though Apache POI is preferred for Excel.

Read data from an Excel sheet and write it to an Oracle database using JDBC, employing a prepared statement to insert into the employees table.