Scribd
Upload
29 views13 pages

JDBC Program .Dot

The document provides a detailed guide on how to create and manipulate tables in an Oracle database using JDBC. It includes syntax for creating tables, inserting records, updating records, and retrieving data, along with example Java code snippets for each operation. The document also explains the necessary steps such as registering the driver, establishing a connection, creating statements, and executing queries.

Uploaded by

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

JDBC Program .Dot

The document provides a detailed guide on how to create and manipulate tables in an Oracle database using JDBC. It includes syntax for creating tables, inserting records, updating records, and retrieving data, along with example Java code snippets for each operation. The document also explains the necessary steps such as registering the driver, establishing a connection, creating statements, and executing queries.

Uploaded by

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

How to create a table in Oracle using JDBC?

You can create a table in a database using the CREATE


TABLE query.

Syntax
CREATE TABLE table_name(
column1 datatype,
column2 datatype,
column3 datatype,
.....
columnN datatype,
PRIMARY KEY( one or more columns )
);

To create a table in a database using JDBC API you need to −

Register the Driver: Register the driver class using the


registerDriver() method of the DriverManager class. Pass the
driver class name to it, as parameter.

Establish a connection: Connect ot the database using the


getConnection() method of the DriverManager class. Passing
URL (String), username (String), password (String) as
parameters to it.

Create Statement: Create a Statement object using the


createStatement() method of the Connection interface.

Execute the Query: Execute the query using the execute()


method of the Statement interface.
Following JDBC program establishes connection with Oracle
database and creates a table named DISPATCHES −

Example
import [Link];
import [Link];
import [Link];
import [Link];
public class CreateTable_Oracle
{
public static void main(String args[]) throws SQLException
{
//Registering the Driver
[Link](new [Link]());

//Getting the connection


String oracleUrl = "jdbc:oracle:thin:@localhost:1521/xe";

Connection con = [Link](oracleUrl, "system", "password");

[Link]("Connection established......");

//Creating the Statement


Statement stmt = [Link]();

//Query to create a table


String query = "CREATE TABLE DISPATCHES("
+ "ProductName VARCHAR (20) NOT NULL, "
+ "CustomerName VARCHAR (20) NOT NULL, "
+ "DispatchDate date, "
+ "DeliveryTime timestamp, "
+ "Price INT, "
+ "Location varchar(20))";
[Link](query);
[Link]("Table Created......");
}
}

Output
Connection established......
Table Created......
In oracle you can get the list of tables using the
query/command −

Select * from tab;


If you verify the list of tables in the database using this, you
can observe the newly created table in it as −
How to insert record in a table from Oracle database using JDBC

You can insert records into a table using the INSERT query.

Syntax
INSERT INTO TABLE_NAME (column1, column2, column3,...columnN)
VALUES (value1, value2, value3,...valueN);
Or,
INSERT INTO TABLE_NAME VALUES (value1, value2, value3,...valueN);

To insert a record into a table in a database using JDBC API you need to −

Register the Driver: Register the driver class using the registerDriver() method of
the DriverManager class. Pass the driver class name to it, as parameter.

Establish a connection: Connect to the database using the getConnection() method of


the DriverManager class. Passing URL (String), username (String), password (String) as
parameters to it.

Create Statement: Create a Statement object using the createStatement() method of


the Connection interface.

Execute the Query: Execute the query using the executeUpdate() method of the Statement
interface.

Let us create a table with name dispatches in Oracle database using CREATE statement as
shown below −

CREATE TABLE dispatches(


PRODUCTNAME VARCHAR2(20),
CUSTOMERNAME VARCHAR2(20),
DISPATCHDATE DATE,
DELIVERYTIME TIMESTAMP(6),
PRICE NUMBER(38),
LOCATION VARCHAR2(20)
);

Following JDBC program establishes connection with the Oracle database and inserts 5 records in
the Dispatches table −

Example

import [Link];

import [Link];

import [Link];

import [Link];

import [Link];

import [Link];

public class InsertData_Oracle {

public static void main(String args[]) throws SQLException {

//Registering the Driver

[Link](new [Link]());

//Getting the connection

String oracleUrl = "jdbc:oracle:thin:@localhost:1521/xe";


Connection con = [Link](oracleUrl, "system", "password");

[Link]("Connection established......");

//Inserting values to the table

String query = "INSERT INTO dispatches VALUES (?, ?, ?, ?, ?, ?)";

PreparedStatement pstmt = [Link](query);

[Link](1, "Key-Board");

[Link](2, "Raja");

[Link](3, new Date(1567315800000L));

[Link](4, new Time(1567315800000L));

[Link](5, 7000);

[Link](6, "Hyderabad");

[Link]();

[Link](1, "Earphones");

[Link](2, "Roja");

[Link](3, new Date(1556688600000L));

[Link](4, new Time(1556688600000L));

[Link](5, 2000);

[Link](6, "Vishakhapatnam");

[Link]();

[Link](1, "Mouse");

[Link](2, "Puja");

[Link](3, new Date(1551418199000L));

[Link](4, new Time(1551418199000L));

[Link](5, 3000);

[Link](6, "Vijayawada");

[Link]();
[Link](1, "Mobile");

[Link](2, "Vanaja");

[Link](3, new Date(1551415252000L));

[Link](4, new Time(1551415252000L));

[Link](5, 9000);

[Link](6, "Chennai");

[Link]();

[Link](1, "Headset");

[Link](2, "Jalaja");

[Link](3, new Date(1554529139000L));

[Link](4, new Time(1554529139000L));

[Link](5, 6000);

[Link](6, "Goa");

[Link]();

[Link]("Records inserted......");

Output
Connection established......
Records inserted......

SQL> select * from dispatches;

PRODUCTNAME CUSTOMERNAME DISPATCHDATE DELIVERYTIME PRICE


LOCATION
------------------------------------------------------------------------------------------
Key-Board Raja 01-SEP-19 01-SEP-19 11.00.00.000000 AM 7001 Hyderabad
Earphones Roja 01-MAY-19 01-MAY-19 11.00.00.000000 AM 2000 Vishakhapatnam
Mouse Puja 01-MAR-19 01-MAR-19 10.59.59.000000 AM 3000 Vijayawada
Mobile Vanaja 01-MAR-19 01-MAR-19 10.10.52.000000 AM 9001 Chennai
Headset Jalaja 06-APR-19 06-APR-19 11.08.59.000000 AM 6000 Goa

//JDBC PROGRAM FOR SELECT DATA FROM TABLE

/* create table first using oracle database


CREATE TABLE dispatches(
2 PRODUCTNAME VARCHAR2(20),
3 CUSTOMERNAME VARCHAR2(20),
4 DISPATCHDATE DATE,
5 DELIVERYTIME TIMESTAMP(6),
6 PRICE NUMBER(38),
7 LOCATION VARCHAR2(20)
*/

/* insert record using oracle database


SQL> insert into dispatches values('Key-Board', 'Raja', TO_DATE('2019-09-01', 'yyyy/mm/dd'),
TO_DATE('[Link]', 'hh:mi:ss'), 7000, 'India');

1 row created.

SQL> insert into dispatches values('Earphones', 'Roja', TO_DATE('2019-05-01', 'yyyy/mm/dd'),


TO_DATE('[Link]', 'hh:mi:ss'), 2000, 'Vishakhapatnam');
1 row created.

SQL> insert into dispatches values('Mouse', 'Puja', TO_DATE('2019-03-01', 'yyyy/mm/dd'),


TO_DATE('[Link]', 'hh:mi:ss'), 3000, 'Vijayawada');

1 row created.

SQL> insert into dispatches values('Mobile', 'Vanaja', TO_DATE('2019-03-01', 'yyyy/mm/dd'),


TO_DATE('[Link]', 'hh:mi:ss'), 9000, 'Chennai');

1 row created.

SQL> insert into dispatches values('Headset', 'Jalaja', TO_DATE('2019-04-06', 'yyyy/mm/dd'),


TO_DATE('[Link]', 'hh:mi:ss' ), 6000, 'Goa');

1 row created.

*/

import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
public class Readdata
{
public static void main(String args[]) throws SQLException
{
int cnt=0;
//Registering the Driver
[Link](new [Link]());
//Getting the connection
String oracleUrl = "jdbc:oracle:thin:@localhost:1521:xe";
Connection con = [Link](oracleUrl, "system",
"system");

[Link]("Connection established......");

//Creating the Statement


Statement stmt = [Link]();

[Link]("Contents of the dispatches table ");


//Retrieving data
ResultSet rs = [Link]("Select * from dispatches");
while([Link]()) {
[Link]("Name: " + [Link](1) +", ");
[Link]("Customer Name: " + [Link](2)+", ");
[Link]("Dispatch Date: " + [Link](3)+", ");
[Link]("Delivery Time: " + [Link](4)+", ");
[Link]("Price: " + [Link](5)+", ");
[Link]("Location: " + [Link](6));
[Link]( );
}
}
}

Update Query
To update the contents of a record in a table using JDBC API you need to –

Register the Driver: Register the driver class using the registerDriver() method of
the DriverManager class. Pass the driver class name to it, as parameter.

Establish a connection: Connect to the database using the getConnection() method of


the DriverManager class. Passing URL (String), username (String), password (String) as
parameters to it.
Create Statement: Create a Statement object using the createStatement() method of
the Connection interface.

Execute the Query: Execute the query using the executeUpdate() method of the Statement
interface.

Update Query Example


create a table with name dispatches in Oracle database using CREATE statement as shown below –

CREATE TABLE Dispatches(


PRODUCTNAME VARCHAR2(20),
CUSTOMERNAME VARCHAR2(20),
DISPATCHDATE DATE,
DELIVERYTIME TIMESTAMP(6),
PRICE NUMBER(38),
LOCATION VARCHAR2(20)
);

Now, we will insert 5 records in dispatches table using INSERT statements −


insert into dispatches values('Key-Board', 'Raja', TO_DATE('2019-09-01', 'yyyy/mm/dd'),
TO_DATE('[Link]', 'hh:mi:ss'), 7000, 'India');
insert into dispatches values('Earphones', 'Roja', TO_DATE('2019-05-01', 'yyyy/mm/dd'),
TO_DATE('[Link]', 'hh:mi:ss'), 2000, 'Vishakhapatnam');
insert into dispatches values('Mouse', 'Puja', TO_DATE('2019-03-01', 'yyyy/mm/dd'),
TO_DATE('[Link]', 'hh:mi:ss'), 3000, 'Vijayawada');
insert into dispatches values('Mobile', 'Vanaja', TO_DATE('2019-03-01', 'yyyy/mm/dd'),
TO_DATE('[Link]', 'hh:mi:ss'), 9000, 'Chennai');
insert into dispatches values('Headset', 'Jalaja', TO_DATE('2019-04-06', 'yyyy/mm/dd'),
TO_DATE('[Link]', 'hh:mi:ss' ), 6000, 'Goa');
Following JDBC program establishes connection with the Oracle database and increases the prices of
each product by 3000.

Example
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
public class UpdateRecordsExample {
public static void main(String args[]) throws SQLException {
//Registering the Driver
[Link](new [Link]());
//Getting the connection
String oracleUrl = "jdbc:oracle:thin:@localhost:1521/xe";
Connection con = [Link](oracleUrl, "system", "password");
[Link]("Connection established......");
//Creating the Statement
Statement stmt = [Link]();
//Query to update records, Increasing the price of all items by 3000
String query = "Update dispatches set PRICE = PRICE+3000";
//Executing the query
int i = [Link](query);
[Link]("Rows updated: "+i);
[Link]("Contents of the dispatches table after updating the records: ");
//Retrieving data
ResultSet rs = [Link]("Select * from dispatches");
while([Link]()) {
[Link]("Name: "+[Link]("ProductName")+", ");
[Link]("Customer Name: "+[Link]("CustomerName")+", ");
[Link]("Dispatch Date: "+[Link]("DispatchDate")+", ");
[Link]("Delivery Time: "+[Link]("DeliveryTime")+", ");
[Link]("Price: "+[Link]("Price")+", ");
[Link]("Location: "+[Link]("Location"));
[Link]();
}
}
}

Output
Connection established......
Rows updated: 5
Contents of the dispatches table after updating the records:
Name: Key-Board, Customer Name: Raja, Dispatch Date: 2019-09-01, Delivery Time: [Link],
Price: 10001, Location: Hyderabad
Name: Earphones, Customer Name: Roja, Dispatch Date: 2019-05-01, Delivery Time: [Link],
Price: 5000, Location: Vishakhapatnam
Name: Mouse, Customer Name: Puja, Dispatch Date: 2019-03-01, Delivery Time: [Link], Price:
6000, Location: Vijayawada
Name: Mobile, Customer Name: Vanaja, Dispatch Date: 2019-03-01, Delivery Time: [Link], Price:
12001, Location: Chennai
Name: Headset, Customer Name: Jalaja, Dispatch Date: 2019-04-06, Delivery Time: [Link]

You might also like