Here is the problem statment:
Develop a JDBC application where KL University students will be able to choose their specialization. But there will be limited slots for each specialization which must be stored and queried from database. After successfully selecting a specialization, Specialization along with student details must be stored in the database.
Given constrains for the problem
- Limit the slots for specialization
- User should be able to choose a specialization
- Once chose, Slots must be updated then student data must be saved in the database.
- View the Students data.
solution for the problem
For the solution of this problem let us consider two tables let’s call the frist table specalization and
the second table student_choice
The specalization table will contain the specalization_name and slots_available queries
specalization_name will have the names of the specalizations it will be of the type VARCHAR
the slots_available column will have the number of slots available for that specalization and
it will be of type INT after creating the table we will also populate it with some specalizations
and slots for them
function to create specalization table
public static void create_specalization_table(Statement stmt, Connection con) {
try {
String create_specalization_table_query = "CREATE TABLE specalization(specalization_name VARCHAR(22), slots_available INTEGER)";
stmt.executeUpdate(create_specalization_table_query);
System.out.println("specalization table created succesfully");
String[] specalization_name = { "cloud", "AI", "big data", "gaming", "ui/ux" };
int[] slots_available = { 22, 44, 26, 28, 24 };
String specalization_insert_query = "INSERT INTO specalization (specalization_name, slots_available) VALUES (?,?)";
PreparedStatement pstmt = con.prepareStatement(specalization_insert_query);
for (int i = 0; i < specalization_name.length; i++) {
pstmt.setString(1, specalization_name[i]);
pstmt.setInt(2, slots_available[i]);
pstmt.executeUpdate();
System.out.println("Inserted " + specalization_name[i] + " data into table");
}
System.out.println("Inserted specalization data succesfully");
print_specialization_table_data_to_console(stmt);
} catch (Exception e) {
System.out.println("Error: " + e);
}
}
The above function will create the specialization table and populate it with data.
Now let’s create the student choice table in this table we will store the data of students specalization choice after they have succesfull selected their specialization so there is no need to populate this table before hand.
The student_choice table will have two columns one of type INT to store the students id number and
one of type VARCHAR to store the students specalization.
function to create the student choice table
public static void create_student_choice_table(Statement stmt) {
try {
String create_student_specalization_choice_table_query = "CREATE TABLE student_choice( student_id INTEGER, specalization_name VARCHAR(22))";
stmt.executeUpdate(create_student_specalization_choice_table_query);
System.out.println("student specalization choice table created succesfully");
} catch (Exception e) {
System.out.println("Error: " + e);
}
}The above function will create the student choice table
Now let’s create two helper functions to print these tables data to console
public static void print_student_table_data_to_console(Statement stmt) {
try {
// prints data to console
ResultSet rs = stmt.executeQuery("select * from student_choice");
if (!rs.isBeforeFirst()) {
System.out.println("No data");
} else {
while (rs.next())
System.out.println(rs.getInt(1) + " " + rs.getString(2));
}
} catch (Exception e) {
System.out.println("Error: " + e);
}
}
public static void print_specialization_table_data_to_console(Statement stmt) {
try {
// prints data to console
ResultSet rs = stmt.executeQuery("select * from specalization");
if (!rs.isBeforeFirst()) {
System.out.println("No data");
} else {
while (rs.next())
System.out.println(rs.getString(1) + " " + rs.getInt(2));
}
} catch (Exception e) {
System.out.println("Error: " + e);
}
}The two helper functions will be useful when we need to print the tables data.
Now let’s get to our main function inside our class it is just a standard procedure of creating a connection and making new instances of statment, connection and scanner which are required to perform database operations and accept input from the user
For the logic to give our users a menu driven program we will have a while loop inside of which we print the options and accept the input from the user then we will pass that data to a switch case and call necessary functions inside from that switch case.
after we exit the while loop we will close our connection and while loop.
main function
public static void main(String[] args) throws Exception {
Class.forName("oracle.jdbc.driver.OracleDriver");
String dbUserName = "system";
String dbUserPassword = "nikhil";
Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE", dbUserName, dbUserPassword);
Statement stmt = con.createStatement();
Scanner scanner = new Scanner(System.in);
create_specalization_table(stmt, con);
create_student_choice_table(stmt);
boolean exit = false;
while (!exit) {
System.out.println("1. Select a specalization");
System.out.println("2. View students choices");
System.out.println("3. Exit the program");
int choice = scanner.nextInt();
switch (choice) {
case 1:
chooseSpecalization(stmt, con, scanner);
break;
case 2:
print_student_table_data_to_console(stmt);
break;
case 3:
exit = true;
break;
default:
System.out.println("Please Try Again");
break;
}
}
con.close();
scanner.close();
}
There will be three options available to choose from
- choose a specalization
- view students data
- Exit the program
if the user chooses option 1 it wil call a method chooseSpecalization in which the user will be asked
to enter his id after which he will be displayed with a list of all available specalizations he could
choose from this list will be generated by another method which will query the database for all the
specalizations where the number of slots available is greater than 0.
function to query the database for all specalizations where slots available is greater than 1
public static void print_available_specalizations(Statement stmt) {
try {
ResultSet rs = stmt.executeQuery("select * from specalization where slots_available>0");
if (!rs.isBeforeFirst()) {
System.out.println("All specalizations are full");
} else {
while (rs.next()) {
System.out.format("%24s|%10d", rs.getString(1), rs.getInt(2));
System.out.println();
}
}
} catch (Exception e) {
System.out.println("Error: " + e);
}
}This function ensures that only specalizations which are available for the student are available to the student this way the student will know a specalization is full if it is not displayed in the list
After the available specalizations list is displayed the student will enter the specalization he wishes to register in, his input will be taken and passed through a function that checks if the selection is valid by cross checking with the database whether this specalization actually exists in the database and also that there are slots available.
function to check if the input is valid
public static boolean is_selection_valid(Connection con, String specalization_choice) {
try {
boolean isValid = false;
String is_choice_valid_query = "SELECT * FROM specalization WHERE specalization_name=?";
PreparedStatement pstmt = con.prepareStatement(is_choice_valid_query);
pstmt.setString(1, specalization_choice);
ResultSet rs = pstmt.executeQuery();
if (!rs.isBeforeFirst()) {
return isValid;
} else {
rs.next();
if (rs.getInt(2) > 1) {
isValid = true;
return isValid;
}
}
return isValid;
} catch (Exception e) {
System.out.println("Error: " + e);
return false;
}
}
If the input passes the validation then a function which updates the slots available and reduces their value by 1 will be called after which a function that will insert the student data into the student choice table will be called
function to decrease the slots available by 1
public static void reduce_available_slots(Connection con, String specalization_choice) {
try {
String reduce_available_slots_query = "UPDATE specalization SET slots_available = slots_available-1 WHERE specalization_name=?";
PreparedStatement pstmt = con.prepareStatement(reduce_available_slots_query);
pstmt.setString(1, specalization_choice);
pstmt.executeUpdate();
System.out.println("decreased available slots succesfully");
} catch (Exception e) {
System.out.println("Error" + e);
}
}function to insert the student data into the student choice table
public static void insert_into_student_table(Connection con, int id, String specalization_choice) {
try {
String insert_into_student_table_query = "INSERT INTO student_choice(student_id, specalization_name) VALUES(?, ?)";
PreparedStatement pstmt = con.prepareStatement(insert_into_student_table_query);
pstmt.setInt(1, id);
pstmt.setString(2, specalization_choice);
pstmt.executeUpdate();
System.out.println("inserted into Student database succesfully");
} catch (Exception e) {
System.out.println("Error: " + e);
}
}after all this process is completed our first function will now print Specalization selected successfully if the validation fails the user will be asked to enter the details again.
function to choose specalization
public static void chooseSpecalization(Statement stmt, Connection con, Scanner sc) {
System.out.println("Please enter your id number ");
int id = sc.nextInt();
sc.nextLine();
System.out.println("Please select a specalization from the given list");
System.out.println("Available specalizations");
System.out.println("Specalization name | Slots available");
print_available_specalizations(stmt);
System.out.println("Please enter your preffered specalization name");
String specalization_choice = sc.nextLine();
if (is_selection_valid(con, specalization_choice)) {
reduce_available_slots(con, specalization_choice);
insert_into_student_table(con, id, specalization_choice);
System.out.println("Specalization selected successfully");
} else {
System.out.println("please select a valid specalization from the given list");
}
}If the user chooses option 2 the helper function to print the data in the student_choice table will be called
and finally if the user selects option 3 the exit boolean will be set to true. this will exit the while loop and closes all the open connections and exits the program.
Output screenshots
The code will show that the data is inserted the first time you run it
Showing the student data and selecting the cloud specalization
Showing the student data and selecting another specalization notice that the slots available for the cloud specalization have decreased
Selecting an invalid specalization
Selecting gaming as specalization by anothe student and displaying the data
Showing both the tables in the sql command prompt
The compete code is
Please change the database connection parameters before executing the code
import java.sql.*;
import java.util.*;
class chooseSpecalizationSkill1 {
public static void main(String[] args) throws Exception {
Class.forName("oracle.jdbc.driver.OracleDriver");
String dbUserName = "system";
String dbUserPassword = "nikhil";
Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE", dbUserName, dbUserPassword);
Statement stmt = con.createStatement();
Scanner scanner = new Scanner(System.in);
create_specalization_table(stmt, con);
create_student_choice_table(stmt);
boolean exit = false;
while (!exit) {
System.out.println("1. Select a specalization");
System.out.println("2. View students choices");
System.out.println("3. Exit the program");
int choice = scanner.nextInt();
switch (choice) {
case 1:
chooseSpecalization(stmt, con, scanner);
break;
case 2:
print_student_table_data_to_console(stmt);
break;
case 3:
exit = true;
break;
default:
System.out.println("Please Try Again");
break;
}
}
con.close();
scanner.close();
}
public static void chooseSpecalization(Statement stmt, Connection con, Scanner sc) {
System.out.println("Please enter your id number ");
int id = sc.nextInt();
sc.nextLine();
System.out.println("Please select a specalization from the given list");
System.out.println("Available specalizations");
System.out.println("Specalization name | Slots available");
print_available_specalizations(stmt);
System.out.println("Please enter your preffered specalization name");
String specalization_choice = sc.nextLine();
if (is_selection_valid(con, specalization_choice)) {
reduce_available_slots(con, specalization_choice);
insert_into_student_table(con, id, specalization_choice);
System.out.println("Specalization selected successfully");
} else {
System.out.println("please select a valid specalization from the given list");
}
}
public static void reduce_available_slots(Connection con, String specalization_choice) {
try {
String reduce_available_slots_query = "UPDATE specalization SET slots_available = slots_available-1 WHERE specalization_name=?";
PreparedStatement pstmt = con.prepareStatement(reduce_available_slots_query);
pstmt.setString(1, specalization_choice);
pstmt.executeUpdate();
System.out.println("decreased available slots succesfully");
} catch (Exception e) {
System.out.println("Error" + e);
}
}
public static void insert_into_student_table(Connection con, int id, String specalization_choice) {
try {
String insert_into_student_table_query = "INSERT INTO student_choice(student_id, specalization_name) VALUES(?, ?)";
PreparedStatement pstmt = con.prepareStatement(insert_into_student_table_query);
pstmt.setInt(1, id);
pstmt.setString(2, specalization_choice);
pstmt.executeUpdate();
System.out.println("inserted into Student database succesfully");
} catch (Exception e) {
System.out.println("Error: " + e);
}
}
public static boolean is_selection_valid(Connection con, String specalization_choice) {
try {
boolean isValid = false;
String is_choice_valid_query = "SELECT * FROM specalization WHERE specalization_name=?";
PreparedStatement pstmt = con.prepareStatement(is_choice_valid_query);
pstmt.setString(1, specalization_choice);
ResultSet rs = pstmt.executeQuery();
if (!rs.isBeforeFirst()) {
return isValid;
} else {
rs.next();
if (rs.getInt(2) > 1) {
isValid = true;
return isValid;
}
}
return isValid;
} catch (Exception e) {
System.out.println("Error: " + e);
return false;
}
}
public static void print_available_specalizations(Statement stmt) {
try {
ResultSet rs = stmt.executeQuery("select * from specalization where slots_available>0");
if (!rs.isBeforeFirst()) {
System.out.println("All specalizations are full");
} else {
while (rs.next()) {
System.out.format("%24s|%10d", rs.getString(1), rs.getInt(2));
System.out.println();
// System.out.println(rs.getString(1) + " " + rs.getInt(2));
}
}
} catch (Exception e) {
System.out.println("Error: " + e);
}
}
public static void create_student_choice_table(Statement stmt) {
try {
String create_student_specalization_choice_table_query = "CREATE TABLE student_choice( student_id INTEGER, specalization_name VARCHAR(22))";
stmt.executeUpdate(create_student_specalization_choice_table_query);
System.out.println("student specalization choice table created succesfully");
} catch (Exception e) {
System.out.println("Error: " + e);
}
}
public static void create_specalization_table(Statement stmt, Connection con) {
try {
String create_specalization_table_query = "CREATE TABLE specalization(specalization_name VARCHAR(22), slots_available INTEGER)";
stmt.executeUpdate(create_specalization_table_query);
System.out.println("specalization table created succesfully");
String[] specalization_name = { "cloud", "AI", "big data", "gaming", "ui/ux" };
int[] slots_available = { 22, 44, 26, 28, 24 };
String specalization_insert_query = "INSERT INTO specalization (specalization_name, slots_available) VALUES (?,?)";
PreparedStatement pstmt = con.prepareStatement(specalization_insert_query);
for (int i = 0; i < specalization_name.length; i++) {
pstmt.setString(1, specalization_name[i]);
pstmt.setInt(2, slots_available[i]);
pstmt.executeUpdate();
System.out.println("Inserted " + specalization_name[i] + " data into table");
}
System.out.println("Inserted specalization data succesfully");
print_specialization_table_data_to_console(stmt);
} catch (Exception e) {
System.out.println("Error: " + e);
}
}
public static void print_student_table_data_to_console(Statement stmt) {
try {
// prints data to console
ResultSet rs = stmt.executeQuery("select * from student_choice");
if (!rs.isBeforeFirst()) {
System.out.println("No data");
} else {
while (rs.next())
System.out.println(rs.getInt(1) + " " + rs.getString(2));
}
} catch (Exception e) {
System.out.println("Error: " + e);
}
}
public static void print_specialization_table_data_to_console(Statement stmt) {
try {
// prints data to console
ResultSet rs = stmt.executeQuery("select * from specalization");
if (!rs.isBeforeFirst()) {
System.out.println("No data");
} else {
while (rs.next())
System.out.println(rs.getString(1) + " " + rs.getInt(2));
}
} catch (Exception e) {
System.out.println("Error: " + e);
}
}
}