PUSHPARANI KHWAIRAKPAM-1BM18MCA17
1. Write an application that connects to a database through JDBC and
implement DDL and DML operations
SOURCE CODE:
package jdbc;
import java.sql.*;
public class jdbcExample {
// JDBC driver name and database URL
static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
static final String DB_URL = "jdbc:mysql://localhost/notes";
// Database credentials
static final String USER = "root";
static final String PASS = "";
public static void main(String[] args) { Connection conn = null;
Statement stmt = null;
try{ Class.forName("com.mysql.jdbc.Driver");
System.out.println("**********DDL COMMANDS*********");
System.out.println("Connecting to a selected database...");
conn = DriverManager.getConnection(DB_URL, USER, PASS);
stmt = conn.createStatement();
System.out.println("Connected database successfully...");
System.out.println("Creating database...");
String sql = "CREATE DATABASE STUDENTS_WORLD";
stmt.executeUpdate(sql);
System.out.println("Database created successfully...");
System.out.println("Creating table in given database...");
String sql1 = "CREATE TABLE REGISTRATION " + "(id INTEGER not NULL, " +
" first VARCHAR(255), " + " last VARCHAR(255), " +
" age INTEGER, " +
" PRIMARY KEY ( id ))";
stmt.executeUpdate(sql1);
System.out.println("Created table in given database...");
System.out.println("Deleting database...");
String sql2 = "DROP DATABASE STUDENTS_WORLD";
stmt.executeUpdate(sql2);
System.out.println("Database deleted successfully...");
System.out.println("******DML COMMANDS******");
System.out.println("Inserting records into the table...");
String sqlinsert = "INSERT INTO Registration " + "VALUES (100, 'BISHWAJIT',
'SAIKIA', 24)";
stmt.executeUpdate(sqlinsert);
sqlinsert = "INSERT INTO Registration " + "VALUES (101, 'PUSHPA', 'KH', 21)";
‘
18MCA4PEAJ: Advanced Java Programming
PUSHPARANI KHWAIRAKPAM-1BM18MCA17
stmt.executeUpdate(sqlinsert);
sqlinsert = "INSERT INTO Registration " + "VALUES (102, 'ISHANI', 'BORA', 22)";
stmt.executeUpdate(sqlinsert);
sqlinsert = "INSERT INTO Registration " + "VALUES(103, 'ROHAN', 'SINGH', 22)";
stmt.executeUpdate(sqlinsert); System.out.println("Inserted records into the
table...");
sqlinsert = "SELECT id, first, last, age FROM Registration"; ResultSet rs =
stmt.executeQuery(sqlinsert); while(rs.next()){
//Retrieve by column name int id = rs.getInt("id");
int age = rs.getInt("age");
String first = rs.getString("first"); String last = rs.getString("last");
//Display values System.out.print("ID: " + id); System.out.print(", Age: " + age);
System.out.print(", First: " + first); System.out.println(", Last: " + last);
}
System.out.println("Upadating Records ");
String sqlupdate = "UPDATE Registration " + "SET age = 23 WHERE id in (100, 101)";
stmt.executeUpdate(sqlupdate);
sqlupdate = "SELECT id, first, last, age FROM Registration"; ResultSet rs1 =
stmt.executeQuery(sqlupdate); while(rs1.next()){
//Retrieve by column name int id = rs1.getInt("id");
int age = rs1.getInt("age");
String first = rs1.getString("first"); String last = rs1.getString("last");
//Display values System.out.print("ID: " + id); System.out.print(", Age: " + age);
System.out.print(", First: " + first); System.out.println(", Last: " + last);
System.out.println("Records Updated");
}
System.out.println("Deleting Records ");
String sqldelete = "DELETE FROM Registration " + "WHERE id = 101";
stmt.executeUpdate(sqldelete);
//Now you can extract all the records
//to see the remaining records
sql = "SELECT id, first, last, age FROM Registration"; ResultSet rs2 =
stmt.executeQuery(sql); while(rs2.next()){
//Retrieve by column name int id = rs2.getInt("id");
int age = rs2.getInt("age");
String first = rs2.getString("first"); String last = rs2.getString("last");
//Display values System.out.print("ID: " + id); System.out.print(", Age: " + age);
System.out.print(", First: " + first); System.out.println(", Last: " + last);
System.out.println("Records Deleted");
}
rs.close();
rs1.close();
rs2.close();
}catch(SQLException se){
//Handle errors for JDBC
se.printStackTrace();
}catch(Exception e){
‘
18MCA4PEAJ: Advanced Java Programming
PUSHPARANI KHWAIRAKPAM-1BM18MCA17
//Handle errors for Class.forName
e.printStackTrace();
}finally{
//finally block used to close resources
try
{
if(stmt!=null)
stmt.close();
}catch(SQLException se2){
}// nothing we can do
try
{
if(conn!=null) conn.close();
}catch(SQLException se){ se.printStackTrace();
}//end finally try
}//end try
}//end ma
}
18MCA4PEAJ: Advanced Java Programming
PUSHPARANI KHWAIRAKPAM-1BM18MCA17
OUTPUT:
18MCA4PEAJ: Advanced Java Programming
PUSHPARANI KHWAIRAKPAM-1BM18MCA17
2. Develop a jdbc program to implement prepared statement
SOURCE CODE:
package jdbc;
import java.sql.*;
public class JDBCE {
// JDBC driver name and database URL
static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
static final String DB_URL = "jdbc:mysql://localhost/notes";
// Database credentials
static final String USER = "root";
static final String PASS = "";
public static void main(String[] args) {
Connection conn = null; PreparedStatement stmt = null;
try{
//STEP 2: Register JDBC driver
Class.forName("com.mysql.jdbc.Driver");
//STEP 3: Open a connection
System.out.println("Connecting to database...");
conn = DriverManager.getConnection(DB_URL,USER,PASS);
//STEP 4: Execute a query
System.out.println("Creating statement...");
String sql = "UPDATE Employees set age=? WHERE id=?";
stmt = conn.prepareStatement(sql);
//Bind values into the parameters.
stmt.setInt(1, 35); // This would set age
stmt.setInt(2, 102); // This would set ID
// Let us update age of the record with ID = 102;
int rows = stmt.executeUpdate();
System.out.println("Rows impacted : " + rows );
// Let us select all the records and display them.
sql = "SELECT id, first, last, age FROM employees";
ResultSet rs = stmt.executeQuery(sql);
//STEP 5: Extract data from result set
while(rs.next()){
//Retrieve by column name
int id = rs.getInt("Id");
int age = rs.getInt("age");
String first = rs.getString("first");
String last = rs.getString("last");
//Display values
System.out.print("ID: " + id);
System.out.print(",Age: " + age);
‘
18MCA4PEAJ: Advanced Java Programming
PUSHPARANI KHWAIRAKPAM-1BM18MCA17
System.out.print(",First: " + first);
System.out.println(",Last: " + last);
}
rs.close();
stmt.close();
conn.close();
}catch(SQLException se){
//Handle errors for JDBC
se.printStackTrace();
}catch(Exception e){
//Handle errors for Class.forName
e.printStackTrace();
}finally{
//finally block used to close resources
try{
if(stmt!=null)
stmt.close();
}catch(SQLException se2){
}// nothing we can do
try{
if(conn!=null)
conn.close();
}catch(SQLException se){
se.printStackTrace();
}//end finally try
}//end try
System.out.println("Goodbye!");
}//end main
}//end JDBCExample
18MCA4PEAJ: Advanced Java Programming
PUSHPARANI KHWAIRAKPAM-1BM18MCA17
OUTPUT:
18MCA4PEAJ: Advanced Java Programming
PUSHPARANI KHWAIRAKPAM-1BM18MCA17
3.Write a program to implement advanced jdbc data types(BLOB & CLOB)
SOURCE CODE:
package jdbc;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class BOB {
public static void main(String... arg) { Connection con = null; PreparedStatement
prepStmt = null; FileInputStream fin = null;
String imagePath="C:\\Users\\DELL\\Desktop\\jj.jpg";
try {
// registering Oracle driver class
Class.forName("com.mysql.jdbc.Driver");
// getting connection
con = DriverManager.getConnection( "jdbc:mysql://localhost:3306/notes", "root",
"");
System.out.println("Connection established successfully!");
//create FileInputStream object
fin=new FileInputStream(imagePath);
//prepare insert query
prepStmt=con.prepareStatement("INSERT into test_img (ID,IMG_COL) "+ "values (1,
?)");
//Substitute first occurrence of ? with fileInputStream
prepStmt.setBinaryStream(1, fin);
//execute insert query
int numberOfRowsInserted = prepStmt.executeUpdate();
System.out.println("numberOfRowsInserted = " +numberOfRowsInserted);
System.out.println(imagePath+" > Image stored in database");
} catch (Exception e) {
e.printStackTrace();
}
finally{
try {
if(fin!=null) fin.close(); //close file
if(prepStmt!=null) prepStmt.close(); //close PreparedStatement
if(con!=null) con.close(); // close connection
} catch (SQLException e) {
18MCA4PEAJ: Advanced Java Programming
PUSHPARANI KHWAIRAKPAM-1BM18MCA17
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
OUTPUT:
CLOB:
package jdbc;
import java.io.File;
import java.io.FileReader;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
/** Copyright (c), AnkitMittal JavaMadeSoEasy.com */
public class CLOB {
public static void main(String... arg) {
Connection con =null; PreparedStatement prepStmt = null;
File file= null;
FileReader fr = null;
String filePath="C:\\Users\\DELL\\Desktop\\BB.txt";
try {
// registering Oracle driver class
Class.forName("com.mysql.jdbc.Driver");
// getting connection
con = DriverManager.getConnection( "jdbc:mysql://localhost:3306/notes", "root",
"");
System.out.println("Connection established successfully!");
//create File object
file=new File(filePath);
‘
18MCA4PEAJ: Advanced Java Programming
PUSHPARANI KHWAIRAKPAM-1BM18MCA17
//create a FileReader object which will read from File
fr=new FileReader(file);
prepStmt=con.prepareStatement("INSERT into test_files (ID, FILE_COL) "+ "values
(1, ?)");
prepStmt.setCharacterStream(1,fr);
//execute insert query
int numberOfRowsInserted = prepStmt.executeUpdate();
System.out.println("numberOfRowsInserted = " +numberOfRowsInserted);
System.out.println(filePath+" > File stored in database");
} catch (Exception e) {
e.printStackTrace();
}
finally{
try {
if(fr!=null)
fr.close(); //close reader
if(prepStmt!=null)
prepStmt.close(); //close PreparedStatement
if(con!=null) con.close(); // close connection
} catch (SQLException e) { e.printStackTrace();
} catch (IOException e) { e.printStackTrace();
}
}
}
}
OUTPUT:
18MCA4PEAJ: Advanced Java Programming
PUSHPARANI KHWAIRAKPAM-1BM18MCA17
4. Write a Jdbc program to demonstrate the batch update using Statement
SOURCE CODE:
package jdbc;
import java.sql.*;
public class batchUpdate {
// JDBC driver name and database URL
static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
static final String DB_URL = "jdbc:mysql://localhost/notes";
// Database credentials
static final String USER = "root";
static final String PASS = "";
public static void main(String[] args) {
Connection conn = null;
PreparedStatement stmt = null;
try{
// Register JDBC driver
Class.forName("com.mysql.jdbc.Driver");
// Open a connection
System.out.println("Connecting to database...");
conn = DriverManager.getConnection(DB_URL,USER,PASS);
// Create SQL statement
String SQL = "INSERT INTO Employees(id,first,last,age) " + "VALUES(?, ?, ?, ?)";
// Create preparedStatemen
System.out.println("Creating statement...");
stmt = conn.prepareStatement(SQL);
// Set auto-commit to false
conn.setAutoCommit(false);
// First, let us select all the records and display them.
printRows( stmt );
// Set the variables
stmt.setInt( 1, 400 );
stmt.setString( 2, "Provu" );
stmt.setString( 3, "Singh" );
stmt.setInt( 4, 33 );
// Add it to the batch stmt.addBatch();
// Set the variables
stmt.setInt( 1, 401 );
stmt.setString( 2, "Rohan" );
stmt.setString( 3, "Singh" );
stmt.setInt( 4, 31 );
// Add it to the batch
‘
18MCA4PEAJ: Advanced Java Programming
PUSHPARANI KHWAIRAKPAM-1BM18MCA17
stmt.addBatch();
// Create an int[] to hold returned values
int[] count = stmt.executeBatch();
//Explicitly commit statements to apply changes
conn.commit();
// Again, let us select all the records and display them.
printRows( stmt );
// Clean-up environment stmt.close();
conn.close();
}catch(SQLException se){
//Handle errors for JDBC
se.printStackTrace();
}catch(Exception e){
//Handle errors for Class.forName
e.printStackTrace();
}finally{
//finally block used to close resources
try{
if(stmt!=null)
stmt.close();
}catch(SQLException se2){
}// nothing we can do
try{
if(conn!=null)
conn.close();
}catch(SQLException se){
se.printStackTrace();
}//end finally try
}//end try
System.out.println("Goodbye!");
}//end main
public static void printRows(Statement stmt)
throws SQLException{
System.out.println("Displaying available rows...");
// Let us select all the records and display them.
String sql = "SELECT id, first, last, age FROM Employees";
ResultSet rs = stmt.executeQuery(sql);
while(rs.next()){
//Retrieve by column name
int id = rs.getInt("id");
int age = rs.getInt("age");
String first = rs.getString("first");
String last = rs.getString("last");
//Display values
System.out.print("ID: " + id);
System.out.print(", Age: " + age);
System.out.print(", First: " + first);
System.out.println(", Last: " + last);
‘
18MCA4PEAJ: Advanced Java Programming
PUSHPARANI KHWAIRAKPAM-1BM18MCA17
}
System.out.println();
rs.close();
}//end printRows()
}//end JDBCExample
OUTPUT:
18MCA4PEAJ: Advanced Java Programming
