SHAHEED ZULFIKAR ALI BHUTTO INSTITUTE
OF
SCIENCE AND TECHNOLOGY
(SZABIST)
Course: CSCL-2203 Database Systems
Department of Computer Science
Lab Manual
1) Stage J(Journey inside-out the concept)
2) Stage a1(Apply the learned)
3) Stage v(Verify the accuracy)
4) Stage a2(Assess your work)
1
LAB # 13
Statement Purpose:
This lab gives us about the details to connect JSP with MYSQL
Activity Outcomes:
We will cover the topic:
Connectivity between JSP and MYSQL
Instructor Note:
Discussion with students regarding home activities.
73
1) Stage J (Journey)
Connection between Application Program and Database
What is JDBC?
Java Database Connectivity ( JDBC) is a standard Java API to interact with relational
databases form Java. JDBC has set of classes and interfaces which can use from Java
application and talk to database without learning RDBMS details and using Database
Specific JDBC Drivers.
The components of JDBC Api basically include:
1. Driver Manager:
Manages a list of database drivers. Matches connection requests from the java
application with the proper database driver using communication subprotocol. The first
driver that recognizes a certain subprotocol under JDBC will be used to establish a
database Connection.
2. Driver:
The database communications link, handling all communication with the database.
Normally, once the driver is loaded, the developer need not call it explicitly.
3. Connection :
Interface with all methods for contacting a database. The connection object represents
communication context, i.e., all communication with database is through connection
object only.
4. Statement :
Encapsulates an SQL statement which is passed to the database to be parsed, compiled,
planned and executed.
5. Result Set:
The Result Set represents set of rows retrieved due to query execution.
How to connect java with database?
Java using JDBC to work with the database.
74
For example, when working with Oracle Database from Java you need to have a Driver
(It is a class driving the connection with types of database you want). In JDBC API, we
have java.sql.Driver, it is only an interface and it is available in JDK. Thus, you have to
download library Driver compatible with the type of Database you want.
For example, with Oracle, the class implements
the Interface java.sql.Driver is oracle.jdbc.driver.OracleDriver
java.sql.DriverManager is a class in JDBC API. It is responsible for managing
Drivers.
2) Stage a1 (apply)
Create project to start with JDBC
Create project JavaJdbcTutorial
Create libs folder on project, copy libraries and connected them directly with database
MySQL. You can copy the whole or one of these libraries compatible with the DB type
you use.
75
Right-click and select Properties Project:
Create utility class ConnectionUtils to get Connection
76
MySQLConnUtils.java
1 package org.o7planning.tutorial.jdbc;
2
3 import java.sql.Connection;
4 import java.sql.DriverManager;
5 import java.sql.SQLException;
6
7 public class MySQLConnUtils {
8
9
10// Connect to MySQL
11public static Connection getMySQLConnection() throws SQLException,
12ClassNotFoundException {
13String hostName = "localhost";
14
15String dbName = "simplehr";
16String userName = "root";
17String password = "1234";
18
19return getMySQLConnection(hostName, dbName, userName, password);
20}
21
22public static Connection getMySQLConnection(String hostName, String dbName,
23String userName, String password) throws SQLException,
24ClassNotFoundException {
77
25// Declare the class Driver for MySQL DB
26// This is necessary with Java 5 (or older)
27// Java6 (or newer) automatically find the appropriate driver.
28// If you use Java> 5, then this line is not needed.
29Class.forName("com.mysql.jdbc.Driver");
30
31String connectionURL = "jdbc:mysql://" + hostName + ":3306/" + dbName;
32
33Connection conn = DriverManager.getConnection(connectionURL, userName,
34password);
35return conn;
36}
37}
You can change ConnectionUtils class for connecting to familiar Database, and run this
class for testing the connection.
3) Stage v (verify)
Follow the steps and configure the connection between JSP and MYSQL in PC’s or laptop.
4) Stage a2 (access)
Students will show the configuration of JSP and MSQL
78
