Scribd
Upload
20 views30 pages

JDBC Database Connection Guide

The document provides an overview of JDBC, including definitions of database drivers, JDBC process steps, and how to establish connections using JDBC and ODBC. It also covers connection pooling, transaction processing, and various JDBC statements like Scrollable ResultSet and CallableStatement. Additionally, it includes code snippets for connecting to databases, executing queries, and handling exceptions in JDBC.

Uploaded by

xdnik76
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
20 views30 pages

JDBC Database Connection Guide

The document provides an overview of JDBC, including definitions of database drivers, JDBC process steps, and how to establish connections using JDBC and ODBC. It also covers connection pooling, transaction processing, and various JDBC statements like Scrollable ResultSet and CallableStatement. Additionally, it includes code snippets for connecting to databases, executing queries, and handling exceptions in JDBC.

Uploaded by

xdnik76
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

MODULE 5

1. What are database drivers? Explain the different JDBC driver types. (10M)

Sol.

Definition:
2. Explain the different steps of JDBC process with code snippets for each step. (10M)

Sol.
3. Mention all steps to create the association b/w the database & JDBC/ODBC bridge.
(12M)
1. Install the Database: Install a suitable Database Management System (DBMS) on your
computer, such as MS Access, MySQL, or Oracle. This software is required to store, manage,
and retrieve data for your application.

2. Create the Database: Using the DBMS, create a new database and define tables with
appropriate columns, data types, and primary keys. Example: Create a table named students
with fields like id, name, and marks.

3. Add Data to the Tables: Insert sample records into your tables to ensure there's data to
query and test your connection. This helps verify that your connection and SQL statements are
working correctly.

4. Open ODBC Data Source Administrator: Go to: Control Panel > Administrative Tools >
ODBC Data Sources Choose 32-bit or 64-bit depending on your Java and DB version.
5. Create a DSN (Data Source Name): In the ODBC Administrator window:
 Select User DSN or System DSN tab.
 Click Add, choose a driver (e.g., Microsoft Access Driver), and set up the data source.
This links your database to a readable name.

6. Name the DSN: Assign a unique and meaningful name to your DSN (e.g., "studentDB"). This
name will be used as an identifier in your Java connection string.
7. Import JDBC Package: In your Java program, import the necessary JDBC classes:
import [Link].*;
This provides access to interfaces like Connection, Statement, ResultSet, etc.

8. Load the JDBC-ODBC Driver: Load the JDBC-ODBC bridge driver class using:
[Link]("[Link]");
This step registers the driver with the JDBC driver manager.

9. Establish the Connection: Connect to the database using the DriverManager:


Connection con = [Link]("jdbc:odbc:studentDB");
This establishes a connection to the database using the specified DSN.
10. Create Statement: Object Create a Statement object to send SQL commands to the
database:
Statement stmt = [Link]();
Alternatively, use PreparedStatement for dynamic or parameterized queries.
11. Execute Queries and Process Results: Run SQL queries and handle the result:
ResultSet rs = [Link]("SELECT * FROM students");
while ([Link]()) {
[Link]("Name: " + [Link]("name"));
}
This loop prints each student's name retrieved from the database.
4. Write the java program to connect to database using URL and to connect database
using userID and password. (8M)

Sol.

package JDBCExample;

// 1. import sql package


import [Link].*;

public class JDBCDemo {


public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/student"; // use the correct DB name and port
String uname = "root";
String pass = "seetha1234";
String query = "SELECT * FROM students";

try {
// 2. Load MySQL JDBC driver
[Link]("[Link]");

// 3. Connect to database
Connection con = [Link](url, uname, pass);

// 4. Create statement and execute query


Statement st = [Link]();

// 5. Execute the query


ResultSet rs = [Link](query);

// 6. process and Display results


while ([Link]()) {
String name = [Link]("name");
int age = [Link]("age");
String email = [Link]("email");

[Link]("Name: " + name);


[Link]("Age: " + age);
[Link]("Email: " + email);
[Link]("--------------- ---------");
}

// 7. Close resources
[Link]();
[Link]();
[Link]();
} catch (Exception e) {
[Link]();
}
}
}
package JDBCExample;

//[Link] sql package


import [Link].*;

public class JDBCDemo {


public static void main(String[] args) {
String url = "jdbc:odbc:Ex"; // JDBC/ODBC bridge URL
String uname = "orcl";
String pass = "tiger";
String query = "SELECT * FROM EMPLOYEE WHERE SALARY > 20000";

try {
// [Link] JDBC/ODBC bridge driver
[Link]("[Link]");

// 3. Connect to database
Connection con = [Link](url, uname, pass);

// [Link] statement and execute query


Statement st = [Link]();

// [Link] the query


ResultSet rs = [Link](query);

// [Link] and Display results


while ([Link]()) {
int empId = [Link]("EMP_ID");
String name = [Link]("EMP_NAME");
int salary = [Link]("SALARY");

[Link]("EMP ID: " + empId);


[Link]("Name: " + name);
[Link]("Salary: " + salary);
[Link]("---------------------------");
}

// 7. Close resources
[Link]();
[Link]();
[Link]();
} catch (Exception e) {
[Link]();
}
}
}

Output:

EMP ID: 101


Name: Kanha
Salary: 25000
---------------------------
EMP ID: 104
Name: Madhav
Salary: 31000
---------------------------

5. Write any two syntax of establishing a connection to the database.(4M)

Sol. Using Data source:


public class DataSourceExample {
public static void main(String[] args) {
try {
Context ctx = new InitialContext();
DataSource ds = (DataSource) [Link]("jdbc/MyDataSource");
Connection conn = [Link]();
[Link]("Connection established successfully!");
[Link]();
} catch (Exception e) {
[Link]();
}
}
6. What is connection pooling? Explain the same with neat diagram and code snippets.
(10M)

Sol.
Connection Pooling
Connection pooling is a database optimization technique that manages a pool of reusable
database connections.
Instead of opening and closing a new connection for every request, it reuses existing
connections, reducing the overhead of frequent connection creation.
This is especially useful in web applications with many users, as it improves performance,
reduces latency, and conserves database resources.

// Step 1: Access JNDI context


Context ctxt = new InitialContext();

// Step 2: Lookup the DataSource from the pool


DataSource pool = (DataSource) [Link]("java:comp/env/jdbc/pool");

// Step 3: Get a logical connection from the pool


Connection db = [Link]();
// Step 4: Use the connection for DB operations
// [Link](select * from users);

// Step 5: Close the logical connection (returns to pool)


[Link]();
7. With proper syntax describe three types of getConnection() methods.(6M)

Sol.
8. Explain the use of object in establishing the connection. Also write a program to call
stored procedures.(10M)

Sol.
9. Describe the following concepts: (10M)
(i) Scrollable ResultSet
(ii) Callable Statement
(iii) Transaction processing
(iv) Updatable ResultSet
(v) Prepared Statement

Sol.

(i) Scrollable ResultSet


A Scrollable ResultSet allows you to move the cursor both forward and backward, or to
a specific row.
Used with: ResultSet.TYPE_SCROLL_INSENSITIVE / TYPE_SCROLL_SENSITIVE
Code:
Connection con = [Link](url, user, pass);
Statement stmt = [Link](
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
ResultSet rs = [Link]("SELECT * FROM students");

[Link](); // Move to last row


[Link]("Last Student: " + [Link]("name"));

[Link](); // Move to first row


[Link]("First Student: " + [Link]("name"));

[Link](); // Move to previous row (if possible)

(ii) CallableStatement
Used to call stored procedures in the database.
Used for: ● Invoking pre-written database logic ● Accepts IN/OUT parameters
Code (MySQL stored procedure):
CREATE PROCEDURE getStudentName(IN id INT, OUT name VARCHAR(50))
BEGIN
SELECT student_name INTO name FROM students WHERE student_id = id;
END;
Java code:
CallableStatement cs = [Link]("{call getStudentName(?, ?)}");
[Link](1, 101); // IN parameter
[Link](2, [Link]); // OUT parameter

[Link]();
[Link]("Student Name: " + [Link](2));
(iii) Transaction Processing
A transaction is a group of SQL operations that are executed together. Either all
succeed or none (atomicity).
Steps:
1. Disable auto-commit
2. Execute multiple queries
3. Commit or rollback
Code:
[Link](false); // Start transaction

try {
Statement stmt = [Link]();
[Link]("UPDATE accounts SET balance = balance - 1000 WHERE id =
1");
[Link]("UPDATE accounts SET balance = balance + 1000 WHERE id =
2");

[Link](); // If both succeed


[Link]("Transaction Successful!");
} catch (SQLException e) {
[Link](); // If any fail
[Link]("Transaction Rolled Back!");
}

(iv) Updatable ResultSet


Allows you to update, insert, or delete rows directly from the ResultSet.
Used with: ResultSet.CONCUR_UPDATABLE
Code:
Statement stmt = [Link](
ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
ResultSet rs = [Link]("SELECT * FROM students");

[Link](1); // Go to first row


[Link]("name", "Nikhil Kumar");
[Link](); // Commit change to DB

// Inserting new row


[Link]();
[Link]("id", 105);
[Link]("name", "Nivi");
[Link]();
(v) PreparedStatement
Used for executing parameterized SQL queries, which helps prevent SQL injection.
Faster and secure than Statement
Code:
String query = "INSERT INTO students (id, name) VALUES (1,”Nikhil”)";
PreparedStatement ps = [Link](query);
[Link](1, 110);
[Link](2, "Nivi");

[Link]();
[Link]("Inserted Successfully");
10. Explain transaction processing in JDBC. WAP to execute a Database connection.
(10M)

Sol.

Transaction processing is explained in the previous question.

Program-
(chatgpt provided code with reference to the pdf material)
11. List and explain exceptions occurred in JDBC.(5M)

Sol.
1. SQLException Description: This is the most commonly encountered exception in JDBC. It
occurs due to issues like:
 Invalid SQL syntax
 Connection failure
 Missing tables or columns
 Incorrect credentials
Useful Methods:
 getMessage() – returns the error message.
 getErrorCode() – returns vendor-specific error code.
Example:
try {
Statement stmt = [Link]();
[Link]("SELECT * FROM non_existing_table");
} catch (SQLException e) {
[Link]("Error: " + [Link]());
}

2. SQLWarning Description: SQLWarnings are less severe than exceptions. They indicate
minor issues like:
 Deprecated features
 Execution warnings by the driver or database
These do not stop program execution but should still be checked.
Useful Methods:
 getWarning() – retrieves warnings on connections or statements.
 getNextWarning() – gets the next warning in the chain.

Example:
SQLWarning warning = [Link]();
while (warning != null) {
[Link]("Warning: " + [Link]());
warning = [Link]();
}

🔹 3. DataTruncation Description: Occurs when:


 Data is truncated during insert or update (e.g., inserting a long string into a short
column)
 Data exceeds the defined size of a column
**It is a subclass of **SQLWarning and may not throw an error but is important to check.
Example Scenario: Inserting a 20-character name into a VARCHAR(10) column may cause
DataTruncation.
12. List and elaborate Database Metadata object methods.(5M)

Sol.
DatabaseMetaData is an interface in JDBC used to retrieve detailed information (metadata)
about a database's structure, such as:
 Tables
 Columns
 Primary keys
 Schemas, etc.
You can access it using:

✅
DatabaseMetaData dbmd = [Link]();
**Common Methods of **DatabaseMetaData:
1. getDatabaseProductName()
 Purpose: Returns the name of the database product.
 Example Output: MySQL, Oracle, PostgreSQL

2. getUserName()
 Purpose: Returns the username used for the database connection.
 Example Output: "root" or any logged-in user

3. getURL()
 Purpose: Returns the URL of the connected database.
 Example: "jdbc:mysql://localhost:3306/mydb"

4. getSchemas()
 Purpose: Returns the names of all schemas available in the database.
 Helps identify database-level organization or structure.

5. getPrimaryKeys(String catalog, String schema, String table)


 Purpose: Returns primary key information for the specified table.
 Includes column names and sequence if composite keys exist.

6. getTables(String catalog, String schemaPattern, String tableNamePattern, String[] types)


 Purpose: Returns metadata about tables in the database.
 Can be filtered by table name or type (TABLE, VIEW, etc.).
13. Write a note on Metadata interface.(4M)

Sol.

\
package JDBCExample;

// 1. Import SQL package


import [Link].*;
import [Link];

public class JDBCDemoStudent {


public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/STUDENT"; // DB URL
String uname = "root"; // DB username
String pass = "password"; // DB password

Scanner sc = new Scanner([Link]);

try {
// 2. Load JDBC driver
[Link]("[Link]");

// 3. Connect to database
Connection con = [Link](url, uname, pass);

// 4. Create statement
Statement st = [Link]();
// 5. Insert data
[Link]("INSERT INTO STUDENT VALUES (1, 'Amit', 20)");
[Link]("INSERT INTO STUDENT VALUES (2, 'Neha', 22)");

// 6. Update record
[Link]("UPDATE STUDENT SET AGE = 21 WHERE ID = 1");

// 7. Delete record
[Link]("DELETE FROM STUDENT WHERE ID = 2");

// 8. Search and display


ResultSet rs = [Link]("SELECT * FROM STUDENT WHERE AGE > 18");

[Link]("Student Details (Age > 18):");


[Link]("---------------------------");
while ([Link]()) {
int id = [Link]("ID");
String name = [Link]("NAME");
int age = [Link]("AGE");

[Link]("ID: " + id);


[Link]("Name: " + name);
[Link]("Age: " + age);
[Link]("---------------------------");
}

// 9. Close resources
[Link]();
[Link]();
[Link]();
[Link]();

} catch (Exception e) {
[Link]();
}
}
}

Common questions

Powered by AI

JDBC drivers can be categorized into four main types: Type 1 drivers use a JDBC-ODBC bridge and rely on ODBC drivers for connectivity, which can be slower due to the additional layer; Type 2 drivers are native API drivers that convert JDBC calls into database-specific calls, offering faster access but requiring native libraries; Type 3 drivers are pure Java drivers that use middleware servers to convert JDBC calls into database-specific calls, offering flexibility with no native libraries needed; and Type 4 drivers are pure Java drivers for direct connectivity to the database, providing the fastest and simplest option without needing additional software .

Transaction processing in JDBC is necessary in scenarios involving multiple interdependent database operations, such as transferring funds between accounts in a banking application where both debit and credit operations must succeed or fail together. Transaction processing ensures data consistency by allowing these operations to be treated as a single unit; using con.setAutoCommit(false) to begin a transaction, con.commit() to save changes if all operations are successful, and con.rollback() to undo changes upon failure, thereby maintaining database integrity .

PreparedStatements are preferred over Statements in JDBC because they offer improved performance and security. PreparedStatements execute parameterized queries, which reduces the risk of SQL injection attacks by ensuring that input values are properly escaped and treated as data, not SQL code. Additionally, PreparedStatements can improve performance through pre-compilation, allowing repeated execution with different parameters without compiling the SQL each time .

Establishing a JDBC connection involves several steps: first, a suitable DBMS is installed and a database is created and populated with data; secondly, a DSN (Data Source Name) is set up using the ODBC data source administrator; then, necessary JDBC classes are imported in the Java program; the appropriate driver is loaded with Class.forName(); a database connection is established using DriverManager.getConnection() with the DSN; following this, a Statement object is created to execute SQL queries, and results are processed using ResultSet. Finally, resources are closed to free system resources .

Connection pooling enhances performance by maintaining a pool of reusable database connections, which significantly reduces the overhead of establishing a new connection for each database request. This approach allows connections to be reused instead of opening new ones every time, which is particularly beneficial in applications with a high number of concurrent users. By lowering the connection creation time, it decreases latency and conserves database resources, resulting in improved application performance .

SQLException in JDBC is thrown for various reasons such as invalid SQL syntax, connection failures, missing tables or columns, and incorrect login credentials. It should be properly handled using try-catch blocks in Java code. SQLException provides useful methods like getMessage() to obtain the error message and getErrorCode() for vendor-specific error codes. Proper handling includes logging the error, possibly retrying the operation, or presenting an informative message to the user .

The DatabaseMetaData interface in JDBC provides methods to retrieve detailed database information. Methods include getDatabaseProductName(), which returns the name of the database product; getUserName(), which provides the username used for the database connection; getURL(), which returns the database's URL; getSchemas(), which gives the names of all schemas in the database; getPrimaryKeys(), which retrieves primary key details for a specified table; and getTables(), which provides metadata about tables, such as performance insights based on table organization .

Stored procedures in JDBC are called using a CallableStatement object, which allows for executing pre-defined procedures stored in a database. This is done by preparing a call using con.prepareCall() with SQL syntax like '{call procedure_name}'. CallableStatement is beneficial because it reduces the need for hardcoding SQL logic into applications, provides efficient parameterized query execution with IN/OUT parameters, and can improve performance through pre-compilation in the database .

A Scrollable ResultSet in JDBC allows movement of the cursor both forward and backward through a ResultSet. It is implemented in JDBC by creating a Statement object with specific type arguments: ResultSet.TYPE_SCROLL_INSENSITIVE or TYPE_SCROLL_SENSITIVE, and concurrency type ResultSet.CONCUR_READ_ONLY. This setup lets you navigate the ResultSet using methods like rs.last(), rs.first(), and rs.previous(), providing more flexible data retrieval from a database .

The JDBC-ODBC bridge plays a crucial role in database connectivity by allowing Java applications to connect to databases via ODBC drivers. It is set up by first installing a DBMS and creating a database with required tables. An ODBC data source is configured through the ODBC Data Source Administrator, where a DSN is created and linked to a readable name. In Java, the JDBC package is imported, the JDBC-ODBC driver is loaded using Class.forName(), and the database connection is established via DriverManager.getConnection() using the DSN .

You might also like