[go: up one dir, main page]
Scribd
Upload
10 views4 pages

DA 4 Java

The document contains Java code for database operations using JDBC to manage student grade history and CGPA details. It includes creating and dropping tables, inserting data, and retrieving records from an H2 database. The author faced an issue extracting data from a gradesheet file and opted to manually enter the data instead.

Uploaded by

Anirudh J
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
10 views4 pages

DA 4 Java

The document contains Java code for database operations using JDBC to manage student grade history and CGPA details. It includes creating and dropping tables, inserting data, and retrieving records from an H2 database. The author faced an issue extracting data from a gradesheet file and opted to manually enter the data instead.

Uploaded by

Anirudh J
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
You are on page 1/ 4

Anirudh J 23MPI0015

DA 4 Java
Error:
I was unable to extract data from the gradesheet file. I manually entered the data in eclipse.

Code:
import java.sql.*; // Importing SQL package for database connectivity

public class DatabaseOperations { // Class for Database Operations


public static void main(String[] args) {
// 23MPI0015: JDBC URL, user, and password for H2 Database connection
String jdbcURL = "jdbc:h2:~/test";
String user = "sa";
String password = "";

try {
// 23MPI0015: Load the H2 Driver
Class.forName("org.h2.Driver");

// 23MPI0015: Establishing connection to the database


Connection connection = DriverManager.getConnection(jdbcURL, user,
password);
Statement statement = connection.createStatement();

// 23MPI0015: Drop table if it exists for fresh creation


String dropTableSQL = "DROP TABLE IF EXISTS STUDENTGRADEHISTORY";
statement.executeUpdate(dropTableSQL);
System.out.println("23MPI0015: Table dropped if it existed");

// 23MPI0015: SQL to create Grade History table


Anirudh J 23MPI0015

String gradeHistorySQL = "CREATE TABLE IF NOT EXISTS


STUDENTGRADEHISTORY ("
+ "ID INT PRIMARY KEY, "
+ "COURSECODE VARCHAR(100), "
+ "COURSENAME VARCHAR(100), "
+ "COURSETYPE VARCHAR(100), "
+ "CREDITSEARNED VARCHAR(100), "
+ "GRADE VARCHAR(100), "
+ "EXAMMONTH VARCHAR(100), "
+ "EXAMDATE VARCHAR(100), "
+ "COURSEOPTION VARCHAR(100), "
+ "COURSEDISTRIBUTION VARCHAR(100))";

statement.executeUpdate(gradeHistorySQL); // Executing the table


creation
System.out.println("23MPI0015: Grade History Table created");

// 23MPI0015: Inserting data into the Grade History table


String insertDataSQL = "INSERT INTO STUDENTGRADEHISTORY (ID,
COURSECODE, COURSENAME, COURSETYPE, CREDITSEARNED, GRADE, EXAMMONTH, EXAMDATE,
COURSEOPTION, COURSEDISTRIBUTION) VALUES "
+ "(1, 'TCHY102L', 'Inorganic and Organic Chemistry', 'TH',
'3.0', 'A', 'Dec-2023', '2024-01-10', 'NIL', 'FC'), "
+ "(2, 'TCHY102P', 'Inorganic and Organic Chemistry Lab',
'LO', '1.0', 'A', 'Dec-2023', '2024-01-10', 'NIL', 'FC'), "
+ "(3, 'TCHY140L', 'Environmental Studies', 'TH', '3.0', 'B',
'Dec-2023', '2024-01-10', 'NIL', 'UE'), "
+ "(4, 'TCSE103L', 'Programming in Python', 'TH', '2.0', 'A',
'Dec-2023', '2024-01-17', 'NIL', 'FC'), "
+ "(5, 'TCSE103P', 'Programming in Python Lab', 'LO', '2.0',
'B', 'Dec-2023', '2024-01-10', 'NIL', 'FC'), "
+ "(6, 'TENG101L', 'Effective English Communication', 'TH',
'2.0', 'A', 'Dec-2023', '2024-01-10', 'NIL', 'AE'), "
+ "(7, 'TMAT103L', 'Calculus and Analytical Geometry', 'TH',
'3.0', 'B', 'Dec-2023', '2024-01-10', 'NIL', 'FC'), "
+ "(8, 'TMAT103P', 'Calculus and Analytical Geometry Lab',
'LO', '1.0', 'B', 'Dec-2023', '2024-01-10', 'NIL', 'FC'), "
+ "(9, 'TPHY102L', 'Physics of Waves', 'TH', '3.0', 'B', 'Dec-
2023', '2024-01-10', 'NIL', 'FC'), "
+ "(10, 'TPHY102P', 'Physics of Waves Lab', 'LO', '1.0', 'S',
'Dec-2023', '2024-01-10', 'NIL', 'FC')";

statement.executeUpdate(insertDataSQL); // Inserting data into the


table
System.out.println("23MPI0015: Data inserted into Grade History
Table");

// 23MPI0015: Selecting data from Grade History table


String selectSQL = "SELECT * FROM STUDENTGRADEHISTORY";
ResultSet resultSet = statement.executeQuery(selectSQL);

// 23MPI0015: Displaying fetched records from Grade History


while (resultSet.next()) {
int id = resultSet.getInt("ID");
String courseCode = resultSet.getString("COURSECODE");
String courseName = resultSet.getString("COURSENAME");
String type = resultSet.getString("COURSETYPE");
String credits = resultSet.getString("CREDITSEARNED");
String grade = resultSet.getString("GRADE");
Anirudh J 23MPI0015

String examMonth = resultSet.getString("EXAMMONTH");


String examDate = resultSet.getString("EXAMDATE");
String courseOption = resultSet.getString("COURSEOPTION");
String courseDistribution =
resultSet.getString("COURSEDISTRIBUTION");

// 23MPI0015: Printing each record from Grade History


System.out.println(id + "\t" + courseCode + "\t" + courseName +
"\t" + type + "\t" + credits + "\t" + grade + "\t" + examMonth + "\t" + examDate
+ "\t" + courseOption + "\t" + courseDistribution);
}

// 23MPI0015: Dropping the CGPA table if it exists


String dropCGPATableSQL = "DROP TABLE IF EXISTS STUDENTCGPA";
statement.executeUpdate(dropCGPATableSQL);
System.out.println("23MPI0015: Table STUDENTCGPA dropped if it
existed");

// 23MPI0015: Creating CGPA details table


String cgpaSQL = "CREATE TABLE IF NOT EXISTS STUDENTCGPA("
+ "ID INT PRIMARY KEY, "
+ "CREDITSREGISTERED VARCHAR(100), "
+ "CREDITSEARNED VARCHAR(100), "
+ "CGPA VARCHAR(100), "
+ "SGRADES VARCHAR(100), "
+ "AGRADES VARCHAR(100), "
+ "BGRADES VARCHAR(100), "
+ "CGRADES VARCHAR(100), "
+ "DGRADES VARCHAR(100), "
+ "EGRADES VARCHAR(100), "
+ "FGRADES VARCHAR(100), "
+ "NGRADES VARCHAR(100))";
statement.executeUpdate(cgpaSQL); // Executing CGPA table creation
System.out.println("23MPI0015: CGPA Table created");

// 23MPI0015: Inserting data into the CGPA table


String insertCGPADetails = "INSERT INTO STUDENTCGPA (ID,
CREDITSREGISTERED, CREDITSEARNED, CGPA, SGRADES, AGRADES, BGRADES, CGRADES,
DGRADES, EGRADES, FGRADES, NGRADES) VALUES "
+ "(1, 'Registered', 'Earned', 'CGPA', 'S', 'A', 'B', 'C',
'D', 'E', 'F', 'N'), "
+ "(2, '46.0', '46.0', '8.48', '1', '4', '5', '0', '0', '0',
'0', '0')";
statement.executeUpdate(insertCGPADetails); // Inserting data into the
CGPA table

// 23MPI0015: Selecting data from CGPA table


ResultSet resultSet1 = statement.executeQuery("SELECT * FROM
STUDENTCGPA");
// 23MPI0015: Displaying fetched records from CGPA details
while (resultSet1.next()) {
int id = resultSet1.getInt("ID");
String credits = resultSet1.getString("CREDITSREGISTERED");
String creditsReg = resultSet1.getString("CREDITSEARNED");
String cgpa = resultSet1.getString("CGPA");
String sGrade = resultSet1.getString("SGRADES");
String aGrade = resultSet1.getString("AGRADES");
String bGrade = resultSet1.getString("BGRADES");
String cGrade = resultSet1.getString("CGRADES");
Anirudh J 23MPI0015

String dGrade = resultSet1.getString("DGRADES");


String eGrade = resultSet1.getString("EGRADES");
String fGrade = resultSet1.getString("FGRADES");
String nGrade = resultSet1.getString("NGRADES");

// 23MPI0015: Printing each record from CGPA details


System.out.println(id + "\t" + credits + "\t" + creditsReg + "\t"
+ cgpa + "\t" + sGrade + "\t" + aGrade + "\t" + bGrade + "\t"
+ cGrade + "\t" + dGrade + "\t" + eGrade + "\t" + fGrade +
"\t" + nGrade);
}

statement.close();
connection.close();

} catch (Exception e) {
e.printStackTrace(); // Updated to print the full stack trace
for debugging
}
}
}

Output:

You might also like