UNIT

SRM Institute of Science and 1

Technology
S Q L commands, Views, Case Study: PL-SQL,

SRM Institute of Science and 2

Technology
 DDL
•
• COMMANDS
1. Data Definition Language (DDL)
DDL changes the structure of the table like creating a table, deleting a table, altering a
table, etc.
• All the command of DDL are auto-committed that means it permanently save all the
changes in the database.
• Here are some commands that come under DDL:
• CREATE
• ALTER
• DROP
• TRUNCATE
• a. CREATE It is used to create a new table in the database.
• Syntax:
1. CREATE TABLE TABLE_NAME (COLUMN_NAME DATATYPES[,....]);
b. DROP: It is used to delete both the structure and record stored in the
table.
Syntax
DROP TABLE table_name;
Example
DROP TABLE EMPLOYEE;
c. ALTER: It is used to alter the structure of the database. This change could be either to
modify the characteristics of an existing attribute or probably to add a new attribute.
Syntax:
To add a new column in the table
ALTER TABLE table_name ADD column_name COLUMN-definition;
To modify existing column in the table:
ALTER TABLE table_name MODIFY(column_definitions....);
EXAMPLE
ALTER TABLE STU_DETAILS ADD(ADDRESS VARCHAR2(20));
ALTER TABLE STU_DETAILS MODIFY (NAME VARCHAR2(20));

d. TRUNCATE: It is used to delete all the rows from the table and free the space containing
the table.
Syntax:
TRUNCATE TABLE table_name;
Example:
TRUNCATE TABLE EMPLOYEE;
 DML
• COMMANDS
2. Data Manipulation Language
• DML commands are used to modify the database. It is
responsible for all form of changes in the database.
• The command of DML is not auto-committed that means
it can't permanently save all the changes in the database.
They can be rollback.
• Here are some commands that come under DML:
• INSERT
• UPDATE
• DELETE
a. INSERT: The INSERT statement is a SQL query. It is used to insert data into the row of a table.
Syntax:
1.INSERT INTO TABLE_NAME
2.(col1, col2, col3,.... col N)
3.VALUES (value1, value2, value3, .... valueN);
Or
1. INSERT INTO TABLE_NAME
2. VALUES (value1, value2, value3, .... valueN);
For example:
1.INSERT INTO javatpoint (Author, Subject)
VALUES ("Sonoo", "DBMS");

b. UPDATE: This command is used to update or modify the value of a column in the
table.
Syntax:
1.UPDATE table_name SET [column_name1= value1,...column_nameN = valueN] [W
HERE CONDITION]
For example:
3. UPDATE students
4.SET User_Name = 'Sonoo'
3.WHERE Student_Id = '3'
c. DELETE: It is used to remove one or more row from a
table.
Syntax:
1.DELETE FROM table_name [WHERE condition];
For example:
1.DELETE FROM javatpoint
2.WHERE Author="Sonoo";
 3. Data Control Language

DCL commands are used to grant and take

back authority from any database user.
Here are some commands that come under
DCL:
• Grant
• Revoke
• a. Grant: It is used to give user access
privileges to a database.
• Example
1.GRANT SELECT, UPDATE ON MY_TABLE TO SOME_
USER, ANOTHER_USER;
• b.Revoke: It is used to take back
permissions from the user.
• Example
1.REVOKE SELECT, UPDATE ON MY_TABLE FROM US
ER1, USER2;
 4.Transaction Control Language

• TCL commands can only use with DML commands

like INSERT, DELETE and UPDATE only.
• These operations are automatically committed in
the database that's why they cannot be used while
creating tables or dropping them.
• Here are some commands that come under TCL:
• COMMIT
• ROLLBACK
• SAVEPOINT
a. Commit: Commit command is used to save all the transactions to the database.
• Syntax:
COMMIT;
• Example:
1. DELETE FROM CUSTOMERS
2. WHERE AGE = 25;
3. COMMIT;

b. Rollback: Rollback command is used to undo transactions that have not already
been saved to the database.
• Syntax:
ROLLBACK;
• Example:
1. DELETE FROM CUSTOMERS
2. WHERE AGE = 25;
3. ROLLBACK;
• c. SAVEPOINT: It is used to roll the transaction
back to a certain point without rolling back the
entire transaction.
Syntax:
1.SAVEPOINT SAVEPOINT_NAME;
 5. Data Query Language
• DQL is used to fetch the data from the database.
• It uses only one command:
• SELECT
• a. SELECT: This is the same as the projection operation of relational algebra. It is
used to select the attribute based on the condition described by WHERE clause.
• Syntax:
1. SELECT expressions
2. FROM TABLES
3. WHERE conditions;
• For example:
1. SELECT emp_name
2. FROM employee

3. WHERE age > 20;

 PL-SQL CASE STUDY
• Let's create a PL/SQL case study with an
example.
• In this case study, let's assume we are building a
simple library management system.
• We want to create a stored procedure that
calculates the late fee for books that are
returned past their due date.
• Here's the scenario:

1.We have a table named book_loans with

columns book_id, due_date, and
return_date.

2.We need to calculate the late fee for each book

based on the number of days it is overdue.
-- Create the table for book loans
CREATE TABLE book_loans (
book_id INT,
due_date DATE,
return_date DATE
);

-- Insert some
sample data
INSERT INTO
book_loans
VALUES (1,
TO_DATE('2024-
01-20', 'YYYY-MM-
DD'),
TO_DATE('2024-
01-25', 'YYYY-MM-
DD'));
INSERT INTO book_loans VALUES (2, TO_DATE('2024-01-15', 'YYYY-MM-DD'),
TO_DATE('2024-01-22', 'YYYY-MM-DD'));
INSERT INTO book_loans VALUES (3, TO_DATE('2024-01-10', 'YYYY-MM-DD'),
TO_DATE('2024-01-18', 'YYYY-MM-DD'));

-- Create a stored procedure to calculate late fees

v_days_late NUMBER;
v_late_fee NUMBER := 0.50;
BEGIN
OPEN book_cursor;
LOOP
FETCH book_cursor INTO v_book_id, v_due_date, v_return_date;
EXIT WHEN book_cursor%NOTFOUND;
-- Calculate the number of days late
v_days_late := v_return_date - v_due_date;
-- If the book is returned late, calculate and display the late fee
IF v_days_late > 0 THEN
DBMS_OUTPUT.PUT_LINE('Book ID ' || v_book_id || ' is ' ||
v_days_late || '
days late.');
DBMS_OUTPUT.PUT_LINE('Late Fee: $' || v_days_late *
v_late_fee);
ELSE
DBMS_OUTPUT.PUT_LINE('Book ID ' || v_book_id || ' was returned on time.');
END IF;
END LOOP;

CLOSE book_cursor;
END
calculate_late_fee;
Explanation:
1.We create a table book_loans to store information about
book loans.
2. We insert some sample data into the table.
3.We create a stored procedure calculate_late_fee that
uses a cursor to iterate through each book loan record.
4.Inside the loop, we calculate the number of days the
book is overdue.
5.If the book is overdue, we print a message with the
book ID, days late, and the late fee.
6. If the book is returned on time, we print a message
indicating that.
 Constraints with examples

• SQL constraints are used to specify rules for the data in a table.

• Constraints are used to limit the type of data that can go into a table. This ensures the
accuracy and reliability of the data in the table. If there is any violation between the constraint
and the data action, the action is aborted.

• Constraints can be column level or table level. Column level constraints apply to a column,
and table level constraints apply to the whole table.

The following constraints are commonly used in SQL:

•NOT NULL - Ensures that a column cannot have a NULL value
•UNIQUE - Ensures that all values in a column are different
•PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a
table
•FOREIGN KEY - Prevents actions that would destroy links between tables
•CHECK - Ensures that the values in a column satisfies a specific condition
•DEFAULT - Sets a default value for a column if no value is specified
•CREATE INDEX - Used to create and retrieve data from the database very quickly
 SQL NOT NULL
• SQL NOT NULLCONSTRAINT
on CREATE TABLE
• Example
• CREATE TABLE Persons
( ID int NOT NULL,
LastName varchar(255) NOT
NULL, FirstName varchar(255)
NOT NULL, Age int
);

• SQL NOT NULL on ALTER TABLE

• ALTER TABLE Persons
ALTER COLUMN Age int NOT NULL;
2.
This constraint
UNIQUE – helps to uniquely identify each row in the table. i.e. for
a particular column, all the rows should have unique values. We can
have more than one UNIQUE columns in a table.
For example, the below query creates a table Student where the
field ID is specified as UNIQUE. i.e, no two students can have
the same ID.

CREATE TABLE
Student (
ID int(6) NOT NULL
UNIQUE,
NAME varchar(10),
ADDRESS
varchar(20)
);
3. PRIMARY KEY –
• Primary Key is a field which uniquely identifies each row in the table.
If a field in a table as primary key, then the field will not be able to
contain NULL values as well as all the rows should have unique values
for this field. So, in other words we can say that this is combination of
NOT NULL and UNIQUE constraints.
•
A table can have only one field as primary key. Below query will
create a table named Student and specifies the field ID as primary
key.

CREATE TABLE
Student (
ID int(6) NOT NULL
UNIQUE,
NAME varchar(10),
ADDRESS
varchar(20),
PRIMARY KEY(ID)
);
4. FOREIGN KEY –
Foreign Key is a field in a table which uniquely identifies each row of a another table. That is, this
field points to primary key of another table. This usually creates a kind of link between the
tables.
Consider the two tables as shown below:
O_ID ORDER_NO C_ID
1 2253 3
2 3325 3
3 4521 2
4 8532 1

C_ID NAME ADDRESS

1 RAMESH DELHI
2 SURESH NOIDA
3 DHARMESH GURGAON
As we can see clearly that the field C_ID in Orders table is the
primary key in Customers table, i.e. it uniquely identifies each row
in the Customers table.
Therefore, it is a Foreign Key in Orders
table. Syntax:

CREATE TABLE
Orders (
O_ID int NOT NULL,
ORDER_NO int NOT NULL,
C_ID int, PRIMARY KEY
(O_ID),
FOREIGN KEY (C_ID)
REFERENCES
Customers(C_ID)
)
(i) CHECK –
Using the CHECK constraint we can specify a condition for a field,
which should be satisfied at the time of entering values for this
field.
For example, the below query creates a table Student and specifies
the condition for the field AGE as (AGE >= 18 ). That is, the user will
not be allowed to enter any record in the table with A GE < 18. Check
constraint in detail

CREATE TABLE
Student (
ID int(6) NOT
NULL,
NAME varchar(10)
NOT NULL,
AGE int NOT NULL
CHECK (AGE >= 18)
);
(ii) DEFAULT –
This constraint is used to provide a default value for the fields. That is, if at
the time of entering new records in the table if the user does not specify
any value for these fields then the default value will be assigned to them.
For example, the below query will create a table named Student and
specify the
default value for the field AGE as 18.

CREATE TABLE
Student ( ID
int(6) NOT NULL,
NAME varchar(10)
NOT NULL,
AGE int DEFAULT 18
);
 J O I N S AND ITS TYPES

• SQL Join statement is used to combine data or rows

from two or more tables based on a common field
between
them. Different types of Joins are as follows:
• INNER JOIN
• LEFT JOIN
• RIGHT JOIN
• FULL JOIN
• NATURAL JOIN
A. INNER JOIN
• The INNER JOIN keyword selects all rows from both the
tables as long as the condition is satisfied. This
keyword will create the result-set by combining all
rows from both the tables where the condition
satisfies i.e value of the common field will be the
same.
Example Queries(INNER JOIN)
This query will show the names and age of students enrolled in
different courses.
B. LEFT JOIN
• This join returns all the rows of the table on the left side
of the join and matches rows for the table on the right
side of the join. For the rows for which there is no
matching row on the right side, the result-set will
contain null. LEFT JOIN is also known as LEFT OUTER JOIN.
• C. RIGHT JOIN
• RIGHT JOIN is similar to LEFT JOIN.
• This join returns all the rows of the table on the right side of the join and
matching rows for the table on the left side of the join.
• For the rows for which there is no matching row on the left side, the
result-set will contain null.
• RIGHT JOIN is also known as RIGHT OUTER JOIN.
• D. FULL JOIN
• FULL JOIN creates the result-set by combining results of both
LEFT JOIN and RIGHT JOIN.
• The result-set will contain all the rows from both tables.
• For the rows for which there is no matching, the
result-set will contain NULL values.
• E. Natural join (?)
• Natural join can join tables based on the common columns
in the tables being joined. A natural join returns all rows by
matching values in common columns having same name
and data type of columns and that column should be
present in both tables.
• Both table must have at list one common column with
same column name and same data type.
• The two table are joined using Cross join.
• DBMS will look for a common column with same name and
data type Tuples having exactly same values in common
columns are kept in result.
 S E T OPERATIONS
• The SQL Set operation is used to combine the two or more SQL
SELECT statements.
•Types of Set Operation
1.Union
2. UnionAll
3.Intersect
4.Minus
1.Union
• The SQL Union operation is used to combine the result of two or
more SQL SELECT queries.
• In the union operation, all the number of datatype and columns must
be same in both the tables on which UNION operation is being
applied.
• The union operation eliminates the duplicate rows from its resultset.
Syntax
1.SELECT column_name FROM table1
2.UNION
3.SELECT column_name FROM
table2;
3. Intersect
• It is used to combine two SELECT statements. The Intersect operation
returns the common rows from both the SELECT statements.
• In the Intersect operation, the number of datatype and columns must
be the same.
• It has no duplicates and it arranges the data in ascending order by
default.
4. Minus
• It combines the result of two SELECT statements. Minus operator is
used to display the rows which are present in the first query but
absent in the second query.
• It has no duplicates and data arranged in ascending order by default.
 Sub queries-Nested Queries
• A Subquery is a query within another SQL query and embedded within the
WHERE clause.
• Important Rule:
• A subquery can be placed in a number of SQL clauses like WHERE clause,
FROM clause, HAVING clause.
• You can use Subquery with SELECT, UPDATE, INSERT, DELETE statements
along with the operators like =, <, >, >=, <=, IN, BETWEEN, etc.
• A subquery is a query within another query. The outer query is known as the
main query, and the inner query is known as a subquery.
• Subqueries are on the right side of the comparison operator.
• A subquery is enclosed in parentheses.
• In the Subquery, ORDER BY command cannot be used. But GROUP BY
command can be used to perform the same function as ORDER BY
command.
1. Subqueries with the Select Statement
• SQL subqueries are most frequently used with the Select statement.
Syntax
2. Subqueries with the INSERT Statement
• SQL subquery can also be used with the Insert statement.
• In the insert statement, data returned from the subquery is used to
insert into another table.In the subquery, the selected data can be
modified with any of the character, date functions.
3. Subqueries with the UPDATE Statement
• The subquery of SQL can be used in conjunction with the Update
statement. When a subquery is used with the Update statement,
then either single or multiple columns in a table can be
updated.
• This would impact three rows, and finally, the EMPLOYEE table would
have the following records.
4. Subqueries with the DELETE Statement
• The subquery of SQL can be used in conjunction with the Delete
statement just like any other statements mentioned above.
MODIFYING DATA IN SQL:
SQL supports four primary data modifying operations:

INSERT: This operation allows you to add new records or rows to a table.

UPDATE: The UPDATE operation enables you to modify existing records in a table.

DELETE: The DELETE operation allows you to remove records from a table.

ALTER: The ALTER operation is used to add,deleteor modify coloums in an existing table.
1. Using INSERT Statement

INSERT statement in the PL/SQL is used to add the new records or rows of data into the table. It allows u to specify the table name, column
s where the data will be inserted and the corresponding values to be inserted into the columns.
Syntax for INSERT Statement:
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

Explanation:
2.table_name is the name of the table, which we want to insert the data

2.(column1, column2, ….) are optional lists of the columns in table. If specified, you need to be provide the values for those columns in the
same order.

3.VALUES (value1, value2, …) are values to be inserted into specified columns. These values must match the data types of the provided
columns.

student_id
Examples of INSERT Statement first_name last_name
Example 1:
1 John Doe
Let’ insert some data into the students table.
2
INSERT INTO students (student_id, Jane
first_name, last_name) Smith
VALUES (1, 'John', 'Doe');

INSERT INTO students (student_id, first_name, last_name)

VALUES (2, 'Jane', 'Smith');
Output:
2. Using UPDATE Statement

UPDATE statement in the PL/SQL is used to the modify the existing records in table. It is allowed us to specify the table name,
columns to updated new values for these columns and an optional conditional to the filter which rows are to be updated.
Syntax for UPDATE Statement:
UPDATE table_name
Setcolumn1=value1,column2=value2,...
WHERE condition;

Explanation:
1.table_name is the name of the table, which we want to update.
2.SET column1 = value1, column2 = value2, … are specified the columns to the updated with their new values.
3.WHERE condition is specify which rows are to be update. if we not use where condition all rows in the table will be update.

Examples on UPDATE Statement

Example 1:
Let’s update the last name of the students as Anderson whose student_id is 3.
UPDATEstudents
SET last_name = ‘Anderson’
WHERE student_id = 3;
Output:
student_id first_name last_name
1 John Doe

2 Jane Smith

3 Michael Anderson

4 Emily Williams
3. Using DELETE Statement

DELETE statement in the PL/SQL is used to remove the one or more records from the table. It is allowed you to the specify the table name and
optional condition to the filter which rows are to be deleted.
Syntax for DELETE Statement:
DELETE FROM table_ name
WHERE condition;

Explanation:
1.table_name is the name of the table, which we want to delete the rows of the table.
2.WHERE condition is specify which rows are to be delete. if we not use where condition all rows in the table will be delete.

Examples on DELETE Statement

Example 1:
Let’s delete the student from the table whose student_id is 4.
DELETE FROM students
WHERE student_id = 4;
Output:
4.SQL ALTER TABLE Examples

Below are the examples of ALTER TABLE statement. These examples demonstrates different use cases and shows how to use ALTER
TABLE statement in SQL.

SQL ALTER TABLE ADD Column Example

The following SQL query adds an “Email” column to the “Students” table:

ALTER TABLE Students

ADD Email varchar(255);

SQL ALTER TABLE DROP Column Example

The following query deletes the “Email” column from “Students” table:

ALTER TABLE Students

DROP COLUMN Email;

SQL ALTER TABLE MODIFY Column Example

ALTER TABLE table_name

MODIFY COLUMN column_name datatype;
• Declarative Programming Paradigm
• Java Database Connectivity (JDBC)
• Java JDBC Tutorial
• JDBC stands for Java Database Connectivity. JDBC is a Java API to connect and execute the query with the
database. It is a part of JavaSE (Java Standard Edition). JDBC API uses JDBC drivers to connect with the
database. There are four types of JDBC drivers:
 JDBC-ODBC Bridge Driver,
 Native Driver,
 Network Protocol Driver, and
 Thin Driver
• We can use JDBC API to access tabular data stored in any relational database. By the help of JDBC API, we
can save, update, delete and fetch data from the database. It is like Open Database Connectivity (ODBC)
provided by Microsoft.
• The current version of JDBC is 4.3. It is the stable release since 21st September, 2017. It is based on the X/Open SQL Call Level
Interface. The java.sql package contains classes and interfaces for JDBC API. A list of popular interfaces of JDBC API are given
below:
 Driver interface
 Connection interface
 Statement interface
 PreparedStatement interface
 CallableStatement interface
 ResultSet interface
 ResultSetMetaData interface
 DatabaseMetaData interface
 RowSet interface
• A list of popular classes of JDBC API are given below:
 DriverManager class
 Blob class
 Clob class
 Types class
• Why Should We Use JDBC
• Before JDBC, ODBC API was the database API to connect and execute the query with the database. But,
ODBC API uses ODBC driver which is written in C language (i.e. platform dependent and unsecured). That is
why Java has defined its own API (JDBC API) that uses JDBC drivers (written in Java language).
• We can use JDBC API to handle database using Java program and can perform the following activities:
1. Connect to the database
2. Execute queries and update statements to the database
3. Retrieve the result received from the database.
• What is API
• API (Application programming interface) is a document that contains a description of all the features of a
product or software. It represents classes and interfaces that software programs can follow to communicate
with each other. An API can be created for applications, libraries, operating systems, etc.
• JDBC Driver
• JDBC Driver is a software component that enables java
application to interact with the database. There are 4 types of
JDBC drivers:
1. JDBC-ODBC bridge driver
2. Native-API driver (partially java driver)
3. Network Protocol driver (fully java driver)
4. Thin driver (fully java driver)
1) JDBC-ODBC bridge driver
The JDBC-ODBC bridge driver uses ODBC driver to connect to
the database. The JDBC-ODBC bridge driver converts JDBC
method calls into the ODBC function calls. This is now
discouraged because of thin driver.
• In Java 8, the JDBC-ODBC Bridge has been removed.

• Oracle does not support the JDBC-ODBC Bridge from Java 8.

Oracle recommends that you use JDBC drivers provided by the
vendor of your database instead of the JDBC-ODBC Bridge.
• Advantages:
 easy to use.
 can be easily connected to any database.
• Disadvantages:

Performance degraded because JDBC method call
is converted into the ODBC function calls.

The ODBC driver needs to be installed on the
client machine.
2) Native-API driver
The Native API driver uses the client-side libraries of the
database. The driver converts JDBC method calls into
native calls of the database API. It is not written entirely in
java.
• Advantage:
 performance upgraded than JDBC-ODBC bridge driver.
• Disadvantage:
 The Native driver needs to be installed on the each client
machine.
 The Vendor client library needs to be installed on client
machine.
3) Network Protocol driver
The Network Protocol driver uses middleware
(application server) that converts JDBC calls directly or
indirectly into the vendor-specific database protocol. It
is fully written in java.
• Advantage:
 No client side library is required because of application
server that can perform many tasks like auditing, load
balancing, logging etc.
• Disadvantages:
 Network support is required on client machine.
 Requires database-specific coding to be done in the middle
tier.
 Maintenance of Network Protocol driver becomes costly
because it requires database-specific coding to be done in
the middle tier.
4) Thin
driver
• The thin driver converts JDBC
calls directly into the vendor-
specific database protocol. That is
why it is known as thin driver. It
is fully written in Java language.
• Advantage:

Better performance than all
other drivers.

No software is required at client side
or server side.
• Disadvantage:

Drivers depend on the Database.
• Java Database
Connectivity with 5 Steps
• There are 5 steps to connect any
java application with the
database using JDBC. These
steps are as follows:
 Register the Driver class
 Create connection
 Create statement
 Execute queries
• Close connection
1) Register the driver class
• The forName() method of Class class is used to register the driver class. This method is used to dynamically
load the driver class.
Syntax of forName() method
• public static void forName(String className)throws ClassNotFoundException
• Note: Since JDBC 4.0, explicitly registering the driver is optional. We just need to put vender's Jar in
the classpath, and then JDBC driver manager can detect and load the driver automatically.
• Example to register the OracleDriver class
• Here, Java program is loading oracle driver to esteblish database connection.
• Class.forName("oracle.jdbc.driver.OracleDriver");
2) Create the connection object
The getConnection() method of DriverManager class is used to establish connection with the
database. Syntax of getConnection() method
1) public static Connection getConnection(String url)throws SQLException
2) public static Connection getConnection(String url,String name,String password)
3) throws SQLException
Example to establish connection with the Oracle database
1. Connection con=DriverManager.getConnection(
2. "jdbc:oracle:thin:@localhost:1521:xe","system","password");
3) Create the Statement object
The createStatement() method of Connection interface is used to create statement. The object of statement
is responsible to execute queries with the database.
Syntax of createStatement() method
1. public Statement createStatement()throws SQLException
Example to create the statement object
Statement stmt=con.createStatement();
4) Execute the query
The executeQuery() method of Statement interface is used to execute queries to the database. This method
returns the object of ResultSet that can be used to get all the records of a table.
Syntax of executeQuery() method
public ResultSet executeQuery(String sql)throws SQLException
Example to execute query
1. ResultSet rs=stmt.executeQuery("select * from emp");
2. while(rs.next()){
3. System.out.println(rs.getInt(1)+" "+rs.getString(2));
4. }
5) Close the connection object
By closing connection object statement and ResultSet will be closed automatically. The close() method
of Connection interface is used to close the connection.
Syntax of close() method
public void close()throws SQLException
Example to close connection
con.close();
Note: Since Java 7, JDBC has ability to use try-with-resources statement to automatically close
resources of type Connection, ResultSet, and Statement.
It avoids explicit connection closing step.
• Java Database Connectivity with MySQL
• To connect Java application with the MySQL database, we need to follow 5 following steps.
• In this example we are using MySql as the database. So we need to know following informations for the mysql
database:
1. Driverclass: The driver class for the mysql database is com.mysql.jdbc.Driver.
2. Connection URL: The connection URL for the mysql database is jdbc:mysql://localhost:3306/sonoo where jdbc is
the API, mysql is the database, localhost is the server name on which mysql is running, we may also use IP address,
3306 is the port number and sonoo is the database name. We may use any database, in such case, we need to replace
the sonoo with our database name.
3. Username: The default username for the mysql database is root.
4. Password: It is the password given by the user at the time of installing the mysql database. In this example, we are
going to use root as the password.
• Let's first create a table in the mysql database, but before creating table, we need to create database first.
1. create database sonoo;
2. use sonoo;
3. create table emp(id int(10),name varchar(40),age int(3));
• Example to Connect Java Application with mysql database
• In this example, sonoo is the database name, root is the username and password both.
1. import java.sql.*;
2. class MysqlCon{
3. public static void main(String args[]){
4. try{
5. Class.forName("com.mysql.jdbc.Driver");
6. Connection con=DriverManager.getConnection(
7. "jdbc:mysql://localhost:3306/sonoo","root","root");
8. //here sonoo is database name, root is username and password
9. Statement stmt=con.createStatement();
10. ResultSet rs=stmt.executeQuery("select * from emp");
11. while(rs.next())
12. System.out.println(rs.getInt(1)+" "+rs.getString(2)+" "+rs.getString(3));
13. con.close();
14. }catch(Exception e){ System.out.println(e);}
15. }
16. }
To connect java application with the mysql database, mysqlconnector.jar file is required to be
loaded.
• Two ways to load the jar file:
1. Paste the mysqlconnector.jar file in jre/lib/ext folder
2. Set classpath
• 1) Paste the mysqlconnector.jar file in JRE/lib/ext folder:
• Download the mysqlconnector.jar file. Go to jre/lib/ext folder and paste the jar file here.
2) Set classpath
• There are two ways to set the classpath:
• temporary
• Permanent
How to set the temporary classpath
open command prompt and write:
C:>set classpath=c:\folder\mysql-connector-java-5.0.8-bin.jar;.;
How to set the permanent classpath
Go to environment variable then click on new tab. In variable name write classpath and in variable value
paste the path to the mysqlconnector.jar file by appending mysqlconnector.jar;.; as C:\folder\mysql-connector-
java- 5.0.8-bin.jar
How to Update Contents of a Table using
JDBC Connection?
•The Statement interface in JDBC is used to create SQL statements in Java and execute queries with
the database. There are different types of statements used in JDBC:
•Create Statement
•Prepared Statement
•Callable Statement
Thank
you