Scribd
Upload
20 views2 pages

Oracle JDBC Connection and Examples

The document discusses connecting to an Oracle database using JDBC and performing basic operations like creating users, granting privileges, and inserting records. It includes steps to: 1) Create users sreekanth and kesava, grant them connect and resource privileges, and connect as those users. 2) Include the Oracle JDBC driver in the classpath and check it exists. 3) Write a simple program to connect to the database using the driver. 4) Create a student table to insert records into. 5) Write programs to insert records into the student table using static and dynamic SQL.

Uploaded by

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

Oracle JDBC Connection and Examples

The document discusses connecting to an Oracle database using JDBC and performing basic operations like creating users, granting privileges, and inserting records. It includes steps to: 1) Create users sreekanth and kesava, grant them connect and resource privileges, and connect as those users. 2) Include the Oracle JDBC driver in the classpath and check it exists. 3) Write a simple program to connect to the database using the driver. 4) Create a student table to insert records into. 5) Write programs to insert records into the student table using static and dynamic SQL.

Uploaded by

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

connect system/system

create user sreekanth identified by sreekanth;


grant connect,resource to sreekanth;
connect sreekanth/sreekanth;
show user;

select * from tab;

create user kesava identified by kesava;


grant connect,resource to kesava;
connect kesava/kesava;

show user;

include below path in classpath


oracle type iv driver jar file exist in below folder
C:\oraclexe\app\oracle\product\10.2.0\server\jdbc\lib\[Link]

open command prompt to check whether oracle type iv driver exist or not
javap [Link]

// to connect to oracle from jdbc program


// [Link]
import [Link].*;
public class DriverTest {
public static void main(String rags[]) throws ClassNotFoundException,
SQLException {
[Link]("[Link]");
Connection
con=[Link]("jdbc:oracle:thin:@localhost:1521:XE", "kesava",
"kesava");
[Link](con);
}
}

javac [Link]
java DriverTest

create table in sqlprompt


create table student (
sno number primary key,
rollno varchar2(10),
name varchar2(20)
);

// [Link]
import [Link].*;
public class InsertDemo {
public static void main(String rags[]) throws ClassNotFoundException,
SQLException {
[Link]("[Link]");
Connection
con=[Link]("jdbc:oracle:thin:@localhost:1521:XE", "kesava",
"kesava");
Statement stmt=[Link]();
int i=[Link]("insert into student values (1, '20195A1234',
'ABC')");
[Link](i+" row inserted");
}
}

// [Link]
import [Link].*;
import [Link].*;
public class DynamicInsertDemo {
public static void main(String rags[]) throws ClassNotFoundException,
SQLException {
[Link]("[Link]");
Connection
con=[Link]("jdbc:oracle:thin:@localhost:1521:XE", "kesava",
"kesava");
Statement stmt=[Link]();
Scanner sc=new Scanner([Link]);
int sno=[Link]();
String rollno=[Link]();
String name=[Link]();
int i=[Link]("insert into student values
("+sno+",'"+rollno+"','"+name+"')");
[Link](i+" row inserted");

}
}

Common questions

Powered by AI

Including 'ojdbc14.jar' in the classpath is essential because it contains the Oracle JDBC driver classes necessary for establishing a database connection from a Java application. Without this file, JDBC operation would fail as the Java Virtual Machine would be unable to locate driver classes required to handle Oracle database connectivity .

To connect to an Oracle database using JDBC, first load the JDBC driver using 'Class.forName("oracle.jdbc.driver.OracleDriver");'. Then, create a connection using 'DriverManager.getConnection()', providing the database URL, username, and password. The URL format is 'jdbc:oracle:thin:@[host]:[port]:[SID]'. For example, in the DriverTest.java program, the connection is established with 'DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE", "kesava", "kesava")' .

To perform a dynamic insertion with user input, use a Scanner to gather input, then pass these variables into the SQL query string in 'Statement.executeUpdate()'. For example, in DynamicInsertDemo.java, 'Scanner sc = new Scanner(System.in);' captures input for 'sno', 'rollno', and 'name'. These are then inserted dynamically into the SQL statement '"insert into student values ("+sno+",'"+rollno+"','"+name+"')"'. This method avoids hardcoding data, allowing for flexible data entry .

The 'Class.forName()' method in Java is used to dynamically load the JDBC driver class at runtime. It enables the JVM to register the driver with DriverManager, which is crucial for facilitating database connections. This method implies a loose coupling where driver details need not be hardcoded throughout the application, enhancing flexibility and maintainability. However, newer JDBC versions offer simpler alternatives, such as automatic loading from the classpath, reducing the necessity for explicit calls to 'Class.forName()' .

To create a new user in the Oracle database, use the SQL command: 'create user [username] identified by [password];'. After creating the user, grant necessary permissions with: 'grant connect, resource to [username];'. For example, to create a user 'kesava', execute 'create user kesava identified by kesava;' followed by 'grant connect, resource to kesava;' .

Primary keys, like the 'sno' field in the 'student' table, are essential in database design as they uniquely identify each record within the table. This uniqueness constraint prevents duplicate entries, ensuring data integrity and enabling effective querying, indexing, and relational integrity across database tables .

First, verify the inclusion of the driver in the classpath by confirming that the 'ojdbc14.jar' file exists in the specified folder path: 'C:\oraclexe\app\oracle\product\10.2.0\server\jdbc\lib\'. Next, open the command prompt and execute 'javap oracle.jdbc.driver.OracleDriver' to check whether the Oracle Type IV driver is available .

Using string concatenation for SQL queries, as demonstrated in DynamicInsertDemo.java, poses significant security risks, such as SQL Injection. This vulnerability arises when untrusted input is malformed in a way that alters the intended SQL command. Attackers can exploit this to execute arbitrary SQL code, leading to unauthorized access, data breach, or data manipulation. Using PreparedStatement objects instead mitigates this risk by parameterizing queries and safely incorporating user inputs .

The 'show user;' command in an Oracle SQL session outputs the name of the current user connected to the database. It's crucial for verifying that the correct user context is active, especially before performing user-specific operations or executing privilege-dependent commands .

In Java, a Statement object is used to execute static SQL statements and not meant for dynamic or repeated use efficiently. To execute an SQL command, first establish a database connection, create a Statement object using 'Connection.createStatement()', and then execute the SQL query using 'executeUpdate()' for DML operations, such as inserting data. For instance, in the InsertDemo program, 'stmt.executeUpdate("insert into student values (1, '20195A1234', 'ABC')")' is executed after connecting to the database .

You might also like