# Q17

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

{% hint style="info" %}
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](https://github.com/MJK618/InternetTechnologies/blob/main/JAVA%20and%20JDBC/InternetTechnologies/src/main/java/jdbc_2.java)
{% endhint %}

### Code

{% tabs %}
{% tab title="jdbc\_2.java" %}

```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: ");
    }
}




```

{% file src="<https://1856586971-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MYBDzNEDANjFDzIvzi2%2F-MZWov-AUe55MpI0cqHb%2F-MZXBYRFv1LAIv2IK_hF%2Fjdbc_2.java?alt=media&token=52472556-1dbb-4324-b78d-5b98f1350512>" %}
Download jdbc\_2.java
{% endfile %}
{% endtab %}

{% tab title="SQL" %}

```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
);



```

{% endtab %}
{% endtabs %}

### Output

![Output (1/4)](https://1856586971-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MYBDzNEDANjFDzIvzi2%2F-MZWov-AUe55MpI0cqHb%2F-MZXCTrZENB1_eOJSyUh%2Fimage.png?alt=media\&token=2bbff3da-99f2-4d9e-943e-bf751c2f8126)

![Output (2/4)](https://1856586971-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MYBDzNEDANjFDzIvzi2%2F-MZWov-AUe55MpI0cqHb%2F-MZXCb6wBAXFhP2MiJMO%2Fimage.png?alt=media\&token=c337cb75-7c16-41a6-a698-cda6e5c0d9c6)

![Output (3/4)](https://1856586971-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MYBDzNEDANjFDzIvzi2%2F-MZWov-AUe55MpI0cqHb%2F-MZXChHMZocpjUto2wtF%2Fimage.png?alt=media\&token=b2baa3b1-2c95-41b5-adc4-ef378b070b93)

![Output (4/4)](https://1856586971-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MYBDzNEDANjFDzIvzi2%2F-MZWov-AUe55MpI0cqHb%2F-MZXCpwaGluxaSTjfw1E%2Fimage.png?alt=media\&token=85b2d072-1e2d-4be1-a5c6-bef3714a2988)