Q17

Question 17

Create a procedure in MySQL to count the number of Rows in table 'Student'. Use Callable Statement to call this method from Java code.

Imported necessary libs for SQL and User Input, Installed JDBC drivers and ran using IntelliJ Ultimate 2021.1 for handy database management tools.

Created the connection from driver to the already created database and table and performed Menu operations afterwards under try catch block.

Running the callable SQL command in Menu-Driven manner. Using a generic method to print tables (ResultSet).

Browse Source Code

Code

jdbc_2.java

import java.sql.*;
import java.util.Scanner;

import static net.efabrika.util.DBTablePrinter.*;

public class jdbc_2 {
    public static void main(String[] args) throws
            SQLException, ClassNotFoundException {

        // JDBC  URL
        final String jdbc_url = "jdbc:mysql://localhost:3306" +
                "/College";

        //  Database credentials
        final String user_id = "root";
        final String password = "MJK618";
        Connection connection = null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            connection = DriverManager.getConnection(
                    jdbc_url, user_id, password);
            Statement stmt = connection.createStatement();
            System.out.println("Connection to the " +
                    "Database Established." +
                    "\n\n\tWelcome to the JDBC CRUD " +
                    "Operations Program");
            int ch = 1;
            Scanner sc = new Scanner(System.in);
            if (ch == 1) {
                do {
                    mainMenu();
                    ch = sc.nextInt();
                    switch (ch) {
                        case 1:
                            showStudentTableRows(stmt);
                            break;
                        case 2:
                            //showTables(stmt);
                            ResultSet rs;
                            stmt.executeQuery("SELECT COUNT(1) " +
                                    "FROM student");
                            rs = stmt.getResultSet();
                            printResultSet(rs);

                            break;
                        case -1:
                            ch = -1;
                            break;
                        default:
                            System.out.println("\n\t XXXXX--" +
                                    " INVALID INPUT --XXXXX \n");
                    }
                } while (ch != -1);

            } else {
                System.out.println("End");
            }

        } catch (SQLException e) {
            System.out.println("Connection Failed" + e);
        } finally {

            System.out.println("Connection Closed");
            assert connection != null;
            connection.close();
        }


    }
    private static void showStudentTableRows(
            Statement stmt) throws SQLException {
        ResultSet rs;
        stmt.executeQuery("SELECT * from student");
        rs = stmt.getResultSet();
        printResultSet(rs);
    }
    private static void mainMenu() {
        System.out.println("\n--- Menu ---");
        System.out.println(" 1. Show all Rows in Table:" +
                " student");
        System.out.println(" 2. Count number of rows in " +
                "Table: student");
        System.out.println("-1. Exit");
        System.out.println("Enter Your Choice: ");
    }
}

3KB

jdbc_2.java

Download jdbc_2.java

SQL

create table student
(
    S_id       int auto_increment
        primary key,
    roll_no    int          null,
    first_name varchar(25)  null,
    last_name  varchar(25)  null,
    course     varchar(50)  null,
    address    varchar(100) null
);

Output

Output (1/4)

Output (2/4)

Output (3/4)

Output (4/4)