JDBC Application Program

Write a program to accept rollno, name, CGPA of “n” students and store the data to a database using JDBC connectivity. Display the list of students having CGPA greater than 7. (Use MySQL /PostgreSQL).

Steps:

  1. Set up MySQL Database: Ensure MySQL is running and create a database and table.
  2. Add MySQL Connector/J: Include the JDBC driver for MySQL in your project.
  3. Java Program: Write the Java program to interact with the database.

1. Set up MySQL Database

Execute the following SQL commands to create a database and table:

CREATE DATABASE studentdb;
USE studentdb;

CREATE TABLE students (
    rollno INT PRIMARY KEY,
    name VARCHAR(50),
    cgpa FLOAT
);
2. Add MySQL Connector/J Dependency
If you are using Gradle, add the following to your build.gradle
implementation 'mysql:mysql-connector-java:8.0.33'

3.Java Program
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;

public class StudentDatabaseApp {

    // JDBC URL, username and password of MySQL server
    private static final String JDBC_URL = "jdbc:mysql://localhost:3306/studentdb";
    private static final String JDBC_USERNAME = "root";
    private static final String JDBC_PASSWORD = "password";

    public static void main(String[] args) {
        Scanner scanner = new Scanner(System.in);

        // Get the number of students
        System.out.print("Enter the number of students: ");
        int n = scanner.nextInt();
        scanner.nextLine(); // consume the newline character

        // Connect to the database
        try (Connection connection = DriverManager.getConnection(JDBC_URL, JDBC_USERNAME, JDBC_PASSWORD)) {
            System.out.println("Connected to the database!");

            // Insert student data
            String insertSql = "INSERT INTO students (rollno, name, cgpa) VALUES (?, ?, ?)";
            try (PreparedStatement preparedStatement = connection.prepareStatement(insertSql)) {
                for (int i = 0; i < n; i++) {
                    System.out.print("Enter roll number for student " + (i + 1) + ": ");
                    int rollno = scanner.nextInt();
                    scanner.nextLine(); // consume the newline character

                    System.out.print("Enter name for student " + (i + 1) + ": ");
                    String name = scanner.nextLine();

                    System.out.print("Enter CGPA for student " + (i + 1) + ": ");
                    float cgpa = scanner.nextFloat();
                    scanner.nextLine(); // consume the newline character

                    preparedStatement.setInt(1, rollno);
                    preparedStatement.setString(2, name);
                    preparedStatement.setFloat(3, cgpa);
                    preparedStatement.executeUpdate();
                }
            }

            // Query and display students with CGPA greater than 7
            String querySql = "SELECT rollno, name, cgpa FROM students WHERE cgpa > 7";
            try (PreparedStatement preparedStatement = connection.prepareStatement(querySql);
                 ResultSet resultSet = preparedStatement.executeQuery()) {
                System.out.println("Students with CGPA greater than 7:");
                while (resultSet.next()) {
                    int rollno = resultSet.getInt("rollno");
                    String name = resultSet.getString("name");
                    float cgpa = resultSet.getFloat("cgpa");
                    System.out.println("Roll No: " + rollno + ", Name: " + name + ", CGPA: " + cgpa);
                }
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Explanation:

  1. JDBC Connection: Establishes a connection to the MySQL database using the DriverManager.getConnection() method.
  2. Insert Data: Uses PreparedStatement to insert roll number, name, and CGPA of "n" students into the database.
  3. Query Data: Queries the database for students with a CGPA greater than 7 and prints their details.
  4. Exception Handling: Catches and prints SQLException in case of any database errors.
  5. Scanner: Used to read input from the user.

Make sure to replace the JDBC_URL, JDBC_USERNAME, and JDBC_PASSWORD with your actual database details. Run the program, and it will prompt you to enter student details and then display students with a CGPA greater than 7.

same program with table creation in code

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

public class StudentDatabase {
    public static void main(String[] args) {
        // Database connection details
        String dbUrl = "jdbc:mysql://localhost:3306/your_database_name"; // Change to your database URL
        String username = "your_username";
        String password = "your_password";

        try {
            // Load the MySQL JDBC driver
            Class.forName("com.mysql.cj.jdbc.Driver");

            // Establish the database connection
            Connection connection = DriverManager.getConnection(dbUrl, username, password);

            // Create a table (if not exists) to store student data
            String createTableQuery = "CREATE TABLE IF NOT EXISTS students (" +
                    "rollno INT PRIMARY KEY, " +
                    "name VARCHAR(255), " +
                    "cgpa FLOAT)";
            PreparedStatement createTableStmt = connection.prepareStatement(createTableQuery);
            createTableStmt.executeUpdate();

            // Insert student data (you can loop through n students)
            String insertQuery = "INSERT INTO students (rollno, name, cgpa) VALUES (?, ?, ?)";
            PreparedStatement insertStmt = connection.prepareStatement(insertQuery);
            insertStmt.setInt(1, 101); // Example roll number
            insertStmt.setString(2, "John Doe"); // Example name
            insertStmt.setFloat(3, 8.5f); // Example CGPA
            insertStmt.executeUpdate();

            // Retrieve students with CGPA > 7
            String selectQuery = "SELECT rollno, name FROM students WHERE cgpa > 7";
            PreparedStatement selectStmt = connection.prepareStatement(selectQuery);
            ResultSet resultSet = selectStmt.executeQuery();

            // Display the results
            System.out.println("Students with CGPA > 7:");
            while (resultSet.next()) {
                int roll = resultSet.getInt("rollno");
                String studentName = resultSet.getString("name");
                System.out.println("Roll No: " + roll + ", Name: " + studentName);
            }

            // Close resources
            resultSet.close();
            selectStmt.close();
            insertStmt.close();
            createTableStmt.close();
            connection.close();
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
    }
}

Comments

Post a Comment

Popular posts from this blog

KTU OOP LAB JAVA CSL 203 BTech CS S3 - Dr Binu V P

About Me Scheme and Evaluation Syllabus and Practice Questions ******************************** How to do Java Programs in  Debian PART A Basic Java Programs Using Control  Statements 1.String Problems 2.Numerical Problems 3.Arrays 4.Matrix Lab Exercise Java-1 PART B  Object Oriented Programming 1.Creating classes and objects. constructor for initializing object 2.Method overloading in Java 3.Single Inheritance 4.Multilevel Inheritance 5.Method overriding in Java 6. Hierarchical Inheritance 7.Interface in Java 8.Abstract classes in java  PART C Exception and File Handling 1.Exception Handling in Java 2.Reading and Writing Text Files 3.Buffered Reader and String Tokenizer PART D Multi-Threading  Creating Threads in Java Thread Synchronization in Java Thread Priorities in Java Programs using Multi-Threading PART E Graphics and Database Programming Introduction to Java Swing Drawing Shapes Drawing  a Sine Wave Handling Mouse Events Handling Keyboard ...
Read more

Reading and Writing Text Files

Handling text files in Java involves performing operations like reading from, writing to, and appending to files. Here are some common techniques and examples for handling text files in Java: FileReader and BufferedReader are classes in Java used for reading text from files. Here’s a detailed explanation of each: FileReader FileReader is a class in Java that is used to read character files. It is a convenient class for reading text files using the default character encoding of the operating system. Key Points: Purpose : To read text files. Inheritance : It extends InputStreamReader , which in turn extends Reader . Encoding : Uses the default character encoding of the operating system unless specified otherwise. Basic Usage : Suitable for simple file reading tasks. Example: create a text file "example.txt" using any text editor and then run this program.In this example, FileReader reads the file character by character. import java.io.FileReader; import java.io.IOException; ...
Read more

String Problems

Reverse a String import java.util.Scanner; public class ReverseString { public static void main(String[] args) {      Scanner scanner = new Scanner(System.in);      System.out.print("Enter a string to reverse: ");      String input = scanner.nextLine();      String reversed = new StringBuilder(input).reverse().toString();      System.out.println("Reversed string: " + reversed);      scanner.close(); } } without using StringBuilder import java.util.Scanner; public class ReverseString {     public static void main(String[] args) {         Scanner scanner = new Scanner(System.in);         System.out.print("Enter a string to reverse: ");         String input = scanner.nextLine();         // Manually reverse the string         String reversed = "";         for (int i = input....
Read more