1.
Write a JDBC program as below:
-> Database Name = Marksheet
-> Table name = Result (ID,Name,Sub1,Sub2,Sub3,Sub4,Percentage,
Grade('Distinction >=70 ','First >= 60','Second >= 50','Third >= 40'))
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class TestApplication {
static final String DB_URL = "jdbc:mysql://localhost/HOME";
static final String USER = "";
static final String PASS = "";
public static void main(String[] args) {
// Open a connection
try(Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
Statement stmt = conn.createStatement();
) {
String sql = "CREATE TABLE STUDENT_INFO" +
"(ID INTEGER NOT NULL PRIMARY KEY, " +
"NAME VARCHAR(20) NOT NULL, " +
" Subject1 VARCHAR(20) NULL,"+
" Subject2 VARCHAR(30) NOT NULL, " +
" Subject3 VARCHAR(30) NOT NULL,"+
"Subject4 VARCHAR(30) NOT NULL,"+
"Percentage VARCHAR(30) NOT NULL",
"Grade VARCHAR(30) NOT NULL"+ )";
stmt.executeUpdate(sql);
System.out.println("Created table in given database...");
} catch (SQLException e) {
e.printStackTrace();
}
}
}
2. Write a menu driven program on above table. (Through User input)
1.Insert
import java.sql.*;
public class insert1
{
public static void main(String args[])
{
String id = "id1";
String pwd = "pwd1";
try
{
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con = DriverManager.getConnection("
jdbc:oracle:thin:@localhost:1521:orcl", "login1", "pwd1");
Statement stmt = con.createStatement();
// Inserting data in database
String q1 = "insert into userid values('" +id+ "', '" +name+
"', '" +sub1+ "', '" +sub2+ "','" +sub3+ "','"
+sub4+ "','" +per+ "',
'" +grade+ "','" +pwd+ "')";
int x = stmt.executeUpdate(q1);
if (x > 0)
System.out.println("Successfully Inserted");
else
System.out.println("Insert Failed");
con.close();
}
catch(Exception e)
{
System.out.println(e);
}
}
}
2.Update
import java.sql.*;
public class update1
{
public static void main(String args[])
{
String id = "id1";
String pwd = "pwd1";
String newPwd = "newpwd";
try
{
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con = DriverManager.getConnection("
jdbc:oracle:thin:@localhost:1521:orcl", "login1", "pwd1");
Statement stmt = con.createStatement();
// Updating database
String q1 = "UPDATE userid set pwd = '" + newPwd +
"' WHERE id = '" +id+ "' AND pwd = '" + pwd + "'";
int x = stmt.executeUpdate(q1);
if (x > 0)
System.out.println("Password Successfully Updated");
else
System.out.println("ERROR OCCURED :(");
con.close();
}
catch(Exception e)
{
System.out.println(e);
}
}
}
3.Delete
import java.sql.*;
public class delete
{
public static void main(String args[])
{
String id = "id2";
String pwd = "pwd2";
try
{
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con = DriverManager.getConnection("
jdbc:oracle:thin:@localhost:1521:orcl", "login1", "pwd1");
Statement stmt = con.createStatement();
// Deleting from database
String q1 = "DELETE from userid WHERE id = '" + id +
"' AND pwd = '" + pwd + "'";
int x = stmt.executeUpdate(q1);
if (x > 0)
System.out.println("One User Successfully Deleted");
else
System.out.println("ERROR OCCURED :(");
con.close();
}
catch(Exception e)
{
System.out.println(e);
}
}
}
4.Select All
5.Select One Record
import java.sql.*;
public class select
{
public static void main(String args[])
{
String id = "id1";
String pwd = "pwd1";
try
{
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con = DriverManager.getConnection("
jdbc:oracle:thin:@localhost:1521:orcl", "login1", "pwd1");
Statement stmt = con.createStatement();
// SELECT query
String q1 = "select * from userid WHERE id = '" + id +
"' AND pwd = '" + pwd + "'";
ResultSet rs = stmt.executeQuery(q1);
if (rs.next())
{
System.out.println("name : " + rs.getString(1));
System.out.println("percentage :" + rs.getString(3));
System.out.println("gradel :" + rs.getString(4));
}
else
{
System.out.println("No such user id is already registered");
}
con.close();
}
catch(Exception e)
{
System.out.println(e);
}
}
}
6.Alter,Create and Drop
1.Create
2.Drop
3.Alter
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class JDBCExample {
static final String DB_URL = "jdbc:mysql://localhost/HOME";
public static void main(String[] args) {
// Open a connection
try(Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
Statement stmt = conn.createStatement();
) {
String sql = "DROP TABLE STUDENT_INFO";
stmt.executeUpdate(sql);
System.out.println("Table deleted in given database...");
} catch (SQLException e) {
e.printStackTrace();
}
}
}
7.Exit
