1)Write a java program that connects to a database using JDBC
import sql package to use it in our program
import [Link].*;
public class Sample_JDBC_Program {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
// store the SQL statement in a string
String QUERY = "select * from employee_details";
//register the oracle driver with DriverManager
[Link]("[Link]");
//Here we have used Java 8 so opening the connection in try statement
try(Connection conn =
[Link]("jdbc:oracle:thin:system/pass123@localhost:1521:XE"))
{
Statement statemnt1 = [Link]();
//Created statement and execute it
ResultSet rs1 = [Link](QUERY);
{
//Get the values of the record using while loop
while([Link]())
{
int empNum = [Link]("empNum");
String lastName = [Link]("lastName");
String firstName = [Link]("firstName");
String email = [Link]("email");
String deptNum = [Link]("deptNum");
String salary = [Link]("salary");
//store the values which are retrieved using ResultSet and print it
[Link](empNum + "," +lastName+ "," +firstName+ "," +email
+","+deptNum +"," +salary);
}
}
}
catch (SQLException e) {
//If exception occurs catch it and exit the program
[Link]();
}
}
}
Output : 1001,luther,Martin,ml@[Link],1,13000
1002,Murray,Keith,km@[Link],2,25000
1003,Brason,John,jb@[Link],3,15000
1004,Martin,Richard,rm@[Link],4,1600
1005,Hickman,David,dh@[Link],5,1700
B)Write a java program to connect to database using JDBC and insert values into it
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
public class InsertedIdExample {
public static void main(String[] args) {
try {
[Link]("[Link]");
Connection con =
[Link]("jdbc:mysql://localhost:3306/books", "root", "password");
// Create a PreparedStatement with the SQL statement and
RETURN_GENERATED_KEYS option
String sql = "INSERT INTO book (author, name, price) VALUES (?, ?, ?)";
PreparedStatement stmt = [Link](sql,
PreparedStatement.RETURN_GENERATED_KEYS);
// Set values for the parameters
[Link](1, "Gupta");
[Link](2, "My Book");
[Link](3, 29.99);
// Execute the insert operation
int rowsAffected = [Link]();
if (rowsAffected > 0) {
// Retrieve the auto-generated keys (insert ID)
ResultSet generatedKeys = [Link]();
if ([Link]()) {
int insertId = [Link](1);
[Link]("Record inserted successfully with ID: " + insertId);
} else {
[Link]("Failed to retrieve insert ID.");
}
} else {
[Link]("No records inserted.");
}
[Link]();
} catch (ClassNotFoundException | SQLException e)
{
[Link]();
}
}
}
Output :
Id author name price
7739 Gupta Mybook 29.99
C)Write a java program to connect to database using JDBC and delete values from it
import [Link].*;
// This class demonstrates DELETE command with LIMIT
public class DeleteWithLimit {
static final String DB_URL = "jdbc:mysql://localhost/TUTORIALSPOINT";
static final String USER = "root";
static final String PASS = "guest123";
public static void main(String args[]) {
try{
Connection conn = [Link](DB_URL, USER, PASS);
String sel_qry = "select * from employees ";
String del_qry = "DELETE FROM employees ORDER BY age LIMIT 3 ";
Statement stmt = [Link]();
ResultSet rs = [Link](sel_qry);
[Link](" Displaying records before deletion ");
[Link](" ----------------------------------" );
showResults(rs);
[Link](del_qry);
[Link]("Displaying records after deletion..");
[Link](" ----------------------------------" );
rs = [Link](sel_qry);
showResults(rs);
[Link]();
[Link]();
[Link]();
}catch(SQLException e){
[Link]();
}
}
public static void showResults(ResultSet res) {
try{
while([Link]()){
[Link]("ID: " + [Link](1));
[Link](", AGE: " + [Link](2));
[Link](", FirstName: " + [Link](3));
[Link](", LastName: " + [Link](4));
}
[Link](" ----------------------------------" );
}catch(SQLException sqle){
[Link]();
}
}
}
Output :Displaying records before deletion
----------------------------------
ID: 1, AGE: 50, FirstName: Shahbaz, LastName: Ali
ID: 2, AGE: 25, FirstName: Mahnaz, LastName: Fatma
ID: 3, AGE: 20, FirstName: Zaid, LastName: Khan
ID: 4, AGE: 28, FirstName: Sumit, LastName: Mittal
ID: 7, AGE: 20, FirstName: Rita, LastName: Tez
ID: 8, AGE: 20, FirstName: Sita, LastName: Singh
ID: 21, AGE: 35, FirstName: Jeevan, LastName: Rao
ID: 22, AGE: 40, FirstName: Aditya, LastName: Chaube
ID: 25, AGE: 35, FirstName: Jeevan, LastName: Rao
ID: 26, AGE: 35, FirstName: Aditya, LastName: Chaube
ID: 34, AGE: 45, FirstName: Ahmed, LastName: Ali
ID: 35, AGE: 51, FirstName: Raksha, LastName: Agarwal
----------------------------------
Displaying records after deletion..
----------------------------------
ID: 1, AGE: 50, FirstName: Shahbaz, LastName: Ali
ID: 2, AGE: 25, FirstName: Mahnaz, LastName: Fatma
ID: 4, AGE: 28, FirstName: Sumit, LastName: Mittal
ID: 21, AGE: 35, FirstName: Jeevan, LastName: Rao
ID: 22, AGE: 40, FirstName: Aditya, LastName: Chaube
ID: 25, AGE: 35, FirstName: Jeevan, LastName: Rao
ID: 26, AGE: 35, FirstName: Aditya, LastName: Chaube
ID: 34, AGE: 45, FirstName: Ahmed, LastName: Ali
ID: 35, AGE: 51, FirstName: Raksha, LastName: Agarwal
—-----------------------------