task9

package task9;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class preparedstmt {
    // Database credentials and URL
    static final String JDBC_URL = "jdbc:mysql://localhost:3306/varshitha"; // Replace with your database name
    static final String JDBC_USER = "root"; // Replace with your MySQL username
    static final String JDBC_PASSWORD = "root"; // Replace with your MySQL password

    public static void main(String[] args) {
        // SQL query to insert data into the Students table
        String insertSQL = "INSERT INTO Students (name, age, grade) VALUES (?, ?, ?)";

        // Try with resources to automatically close the connection and statement
        try (Connection connection = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD);
             PreparedStatement preparedStatement = connection.prepareStatement(insertSQL)) {

            // Insert first student
            preparedStatement.setString(1, "Alice");
            preparedStatement.setInt(2, 20);
            preparedStatement.setString(3, "A");
            preparedStatement.executeUpdate();
            System.out.println("Inserted first student: Alice");

            // Insert second student
            preparedStatement.setString(1, "Bob");
            preparedStatement.setInt(2, 22);
            preparedStatement.setString(3, "B");
            preparedStatement.executeUpdate();
            System.out.println("Inserted second student:s Bob");

        } catch (SQLException e) {
            System.out.println("SQL Exception: " + e.getMessage());
        }
    }
}


package task9;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class callableprocedures {
    // Database credentials and URL
    static final String JDBC_URL = "jdbc:mysql://localhost:3306/varshitha"; // Replace with your database name
    static final String JDBC_USER = "root"; // Replace with your MySQL username
    static final String JDBC_PASSWORD = "root"; // Replace with your MySQL password

    public static void main(String[] args) {
        // SQL query to call the stored procedure
        String callProcedureSQL = "{call selectGradeAStudents()}";

        try (Connection connection = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD);
             CallableStatement callableStatement = connection.prepareCall(callProcedureSQL);
             ResultSet resultSet = callableStatement.executeQuery()) {

            System.out.println("Students with Grade A:");
            while (resultSet.next()) {
                // Assuming the Students table has columns: id, name, age, and grade
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                int age = resultSet.getInt("age");
                String grade = resultSet.getString("grade");
                
                System.out.printf("ID: %d, Name: %s, Age: %d, Grade: %s%n", id, name, age, grade);
            }

        } catch (SQLException e) {
            System.out.println("SQL Exception: " + e.getMessage());
        }
    }
}


package task9;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class callablefunctions {
    // Database credentials and URL
    static final String JDBC_URL = "jdbc:mysql://localhost:3306/varshitha"; // Replace with your database name
    static final String JDBC_USER = "root"; // Replace with your MySQL username
    static final String JDBC_PASSWORD = "root"; // Replace with your MySQL password

    public static void main(String[] args) {
        // SQL query to call the stored function
        String callFunctionSQL = "{? = call getAverageAgeByGrade(?)}";

        try (Connection connection = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD);
             CallableStatement callableStatement = connection.prepareCall(callFunctionSQL)) {

            // Register the output parameter (the average age)
            callableStatement.registerOutParameter(1, java.sql.Types.DOUBLE);
            // Set the input parameter (the grade for which we want the average age)
            callableStatement.setString(2, "A"); // Change the grade as needed

            // Execute the function
            callableStatement.execute();

            // Retrieve the output parameter
            double averageAge = callableStatement.getDouble(1);
            System.out.println("Average age of students with grade 'A': " + averageAge);

        } catch (SQLException e) {
            System.out.println("SQL Exception: " + e.getMessage());
        }
    }
}