UNIT-05
Database Connectivity with MySQL
1. Introduction to MySQL Database Connectivity
Database connectivity with MySQL involves establishing a connection between
an application and a MySQL database, allowing the application to interact with
the database (e.g., performing CRUD operations: Create, Read, Update, Delete).
MySQL is an open-source Relational Database Management System (RDBMS) that
uses Structured Query Language (SQL) for managing databases.
2. Setting up MySQL Database Connectivity
A. MySQL Installation and Setup
Download and install MySQL server and MySQL Workbench (an IDE for
managing MySQL databases).
Create a database and user with necessary privileges.
B. Install MySQL Connector
MySQL Connector is a driver that facilitates the connection between
applications (Java, Python, PHP, etc.) and MySQL.
For Java: Use the [Link] library.
For Python: Install mysql-connector-python using pip.
3. Connecting to MySQL in Different Languages
A. By using Java
Steps to Connect to MySQL in Java
I. Load the MySQL Driver
[Link]("[Link]");
II. Establish a Connection
String url = "jdbc:mysql://localhost:3306/yourDatabase";
String username = "yourUsername";
String password = "yourPassword";
Connection conn = [Link](url, username, password);
III. Execute SQL Queries
Use Statement, PreparedStatement, or CallableStatement for executing SQL
commands.
Statement stmt = [Link]();
ResultSet rs = [Link]("SELECT * FROM yourTable");
B. By using Python
Steps to Connect to MySQL in Python
I. Install the MySQL Connector
pip install mysql-connector-python
II. Establish a Connection
import [Link]
conn = [Link](
host="localhost",
user="yourUsername",
password="yourPassword",
database="yourDatabase"
)
III. Execute SQL Queries
cursor = [Link]()
[Link]("SELECT * FROM yourTable")
result = [Link]()
Database Connectivity with Servlets
Servlets are server-side Java components that handle client requests and generate
responses. To perform database operations in a Servlet, we connect to a database
(e.g., MySQL) and use SQL queries for data handling.
1. Setting up Database Connectivity in Servlets
A. Required Libraries
MySQL Connector: Download and add the [Link] file to the
project’s WEB-INF/lib folder or set it in the classpath.
This library acts as the JDBC driver to facilitate database communication.
B. Configure Database (MySQL)
Set up a MySQL database and create any necessary tables for storing data.
2. Steps to Connect Database in a Servlet
A. Load the JDBC Driver: Load the MySQL JDBC driver to enable database
communication.
[Link]("[Link]");
B. Establish a Connection: Use DriverManager to create a connection to the
database, specifying the database URL, username, and password.
Connection conn = [Link]("jdbc:mysql:// localhost:3306/
yourDatabase", "yourUsername", "yourPassword");
C. Create Statements and Execute SQL Queries:
Use Statement,
PreparedStatement, or CallableStatement objects to execute SQL queries, handle
CRUD operations, and retrieve or modify data.
PreparedStatement pstmt = [Link]("SELECT * FROM users WHERE
id = ?");
[Link](1, 1);
ResultSet rs = [Link]();
D. Close the Connection: Always close database resources like Connection,
Statement, and ResultSet to avoid memory leaks.
[Link]();
[Link]();
[Link]();
3. Servlet Example with Database Connectivity
import [Link].*;
import [Link].*;
import [Link].*;
import [Link].*;
public class UserServlet extends HttpServlet {
@Override
protected void doGet(HttpServletRequest request, HttpServletResponse
response)
throws ServletException, IOException {
[Link]("text/html");
PrintWriter out = [Link]();
// Database connection details
String jdbcUrl = "jdbc:mysql://localhost:3306/yourDatabase";
String jdbcUser = "yourUsername";
String jdbcPassword = "yourPassword";
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
// Load JDBC Driver
[Link]("[Link]");
// Establish Connection
conn = [Link](jdbcUrl, jdbcUser, jdbcPassword);
// Execute Query
String sql = "SELECT id, name, email FROM users";
pstmt = [Link](sql);
rs = [Link]();
// Display Results
[Link]("<h2>User List</h2>");
while ([Link]()) {
int id = [Link]("id");
String name = [Link]("name");
String email = [Link]("email");
[Link]("<p>ID: " + id + ", Name: " + name + ", Email: " + email +
"</p>");
}
} catch (Exception e) {
[Link]("Error: " + [Link]());
[Link]();
} finally {
// Close Resources
try {
if (rs != null) [Link]();
if (pstmt != null) [Link]();
if (conn != null) [Link]();
} catch (SQLException ex) {
[Link]();
}
}
}
}
Database Connectivity with JSP
JSP (JavaServer Pages) is a technology that enables developers to create dynamically
generated web pages using Java. Using JSP, we can connect to a database (e.g.,
MySQL) and perform CRUD operations directly within the JSP page, although best
practices recommend separating database logic from presentation logic.
1. Setting up Database Connectivity in JSP
A. Required Libraries
MySQL Connector: Download [Link] and place it in the WEB-
INF/lib folder of our web application or set it in the classpath.
This JDBC driver is necessary for Java to communicate with MySQL.
B. Configure Database (MySQL)
Create a MySQL database and tables for storing data. Make sure to note the database
URL, username, and password.
2. Connecting to MySQL Database in a JSP Page
To connect to a database in JSP, the following steps are essential:
A. Load the JDBC Driver
Load the MySQL JDBC driver class using [Link]().
B. Establish a Connection
Use DriverManager to create a connection with the specified database URL,
username, and password.
C. Execute SQL Queries
Use Statement, PreparedStatement, or CallableStatement objects to perform
database operations, like reading from or writing to the database.
D. Close the Connection
Always close the database connection and other resources (e.g., Statement,
ResultSet) to avoid memory leaks.
3. Example: Database Connectivity in JSP
<%@ page import="[Link].*" %>
<!DOCTYPE html>
<html>
<head>
<title>Database Connectivity Example</title>
</head>
<body>
<h2>User Information</h2>
<%
// Database credentials
String jdbcUrl = "jdbc:mysql://localhost:3306/yourDatabase";
String jdbcUser = "yourUsername";
String jdbcPassword = "yourPassword";
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
// Load the MySQL JDBC Driver
[Link]("[Link]");
// Establish Connection
conn = [Link](jdbcUrl, jdbcUser, jdbcPassword);
// Create and execute query
String sql = "SELECT id, name, email FROM users";
stmt = [Link]();
rs = [Link](sql);
// Display data
[Link]("<table
border='1'><tr><th>ID</th><th>Name</th><th>Email</th></tr>");
while ([Link]()) {
int id = [Link]("id");
String name = [Link]("name");
String email = [Link]("email");
[Link]("<tr><td>" + id + "</td><td>" + name + "</td><td>" + email +
"</td></tr>");
}
[Link]("</table>");
} catch (Exception e) {
[Link]("Database error: " + [Link]());
} finally {
// Close resources
try {
if (rs != null) [Link]();
if (stmt != null) [Link]();
if (conn != null) [Link]();
} catch (SQLException ex) {
[Link]();
}
}
%>
</body>
</html>