Implementation of Materialized view through the Java Program


Prerequisites: JDBC, SQL views.

A view is simply any SELECT query that has been given a name and saved in the database. For this reason, a view is sometimes called a named query or a stored query.
Materialized views are the views which are similar to the normal views except one property that they are refreshed faster. These views are updated automatically when any update happens in the master table.

Suppose if any insertion happened in master table then the Materialized view also gets updated. From the below program we have tried to show how Materialized views works. Actual Implementation may differ in each database software.



filter_none

edit
close

play_arrow

link
brightness_4
code

// Java Program to implementat
// Materialized view
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Scanner;
import java.sql.PreparedStatement;
  
public class Materialised_View {
    public static void main(String[] args)
    {
        // Declare connection and resultset objects
        Connection conn;
        ResultSet rs;
  
        // Declare statement objects
        Statement stmt, stmt1;
  
        // query to retrieve the details of student table
        String query = "select * from student;";
        Scanner sc = new Scanner(System.in);
        System.out.println("Student Table is Created as Follows:\n");
        System.out.println
              ("*******Student*********\nsid\tsname\taddress\n---- ");
        try {
  
            // Register the mysql driver
            DriverManager.registerDriver(new com.mysql.jdbc.Driver());
  
            // get the connection
            conn = DriverManager.getConnection
             ("jdbc:mysql:// localhost:3306/adbms_practicals", "root", "kernel");
  
            // Initialize the statement object
            stmt = conn.createStatement();
  
            // execute the query and store the result
            rs = stmt.executeQuery("select * from student;");
  
            while (rs.next()) {
                // Iterate through the result set and display
                // content of each row of student table.
                System.out.println(rs.getString(1) + "\t" 
                   + rs.getString(2) + "\t" + rs.getString(3));
            }
  
            System.out.println("\n\nView of this Student Table is As Follows:");
            rs = stmt.executeQuery("select * from Student_Data2;");
            System.out.println
             ("\n***********Student View*******\nsid\tsname\taddress\n-------");
            while (rs.next()) {
                // Iterate through the result set and display
                // content of view of student table.
                System.out.println(rs.getString(1) + "\t" 
                  + rs.getString(2) + "\t" + rs.getString(3));
            }
            while (true) {
  
                // Perform the operations on table
                System.out.println("Menu:\n1.Insert\n2.Delete\n3.
                 See Changes in View Student_Data2\n4.Exit\nEnter Choice:");
  
                // take the input from user to insert / delete / see changes
                int ch = sc.nextInt(); 
  
                int sid;
                String sname = "";
                String address = "";
                switch (ch) {
                case 1:
                    System.out.println("Enter sid:");
                    sid = sc.nextInt();
  
                    System.out.println("Enter sname:");
                    sname = sc.nextLine();
  
                    System.out.println("Enter address:");
                    address = sc.nextLine();
  
                    // create the preparedStatement object and set the values
                    PreparedStatement st3 = conn.prepareStatement
                      ("insert into student values(?, ?, ?);");
  
                    st3.setInt(1, sid);
                    st3.setString(2, sname);
                    st3.setString(3, address);
                    st3.executeUpdate();
  
                    // create the preparedStatement object and set the values
                    PreparedStatement st1 = conn.prepareStatement
                        ("insert into Student_Data2 values(?, ?, ?);");
  
                    st1.setInt(1, sid);
                    st1.setString(2, sname);
                    st1.setString(3, address);
                    st1.executeUpdate(); // execute the preparedStatement to insert row
                    System.out.println("row inserted");
  
                    break;
  
                case 2:
                    System.out.println("Enter sid to be deleted:");
                    sid = sc.nextInt();
  
                    // create the preparedStatement object and set the values
                    PreparedStatement st4 = conn.prepareStatement
                      ("delete from Student_Data2 where sid=?;");
                    st4.setInt(1, sid);
  
                    PreparedStatement st2 = conn.prepareStatement
                        ("delete from student where sid=?");
                    st2.setInt(1, sid);
  
                    // execute the preparedStatement to delete row
                    st2.executeUpdate();
  
                    System.out.println("row deleted");
                    break;
  
                case 3:
                    System.out.println("\n\nChanges Done in the 
                     Student table also Affects the View Student_Data2");
  
                    rs = stmt.executeQuery("select * from Student_Data2;");
                    System.out.println
                      ("\n*****Student*******\nsid\tsname\taddress\n--------");
                    while (rs.next()) {
  
                        // Iterate through the result set and display
                        // recent changes of the table
                            // which got reflected in view.
                            System.out.println(rs.getString(1) + "\t" 
                            + rs.getString(2) + "\t" + rs.getString(3));
                    }
                    break;
  
                case 4:
                    System.exit(0); // User wants to exit
                    break;
                }
            }
        }
        catch (Exception e) { // catch all exceptions here.
            System.out.println(e);
        }
    }
}

chevron_right


Output:

Student Table is Created as Follows:
***********Student**************
sid    sname    address
-----------------------------------------------
131    Rohit Pawar    Mumbai
141    Ashish Mane    Pune
152    Sagar Gore    Mumbai
155    Nitesh Chaure    Nagpur
501    Ritesh Dev    Chennai
531    Dev Khire    Delhi

View of this Student Table is As Follows:
***********Student**************
sid    sname    address
-----------------------------------------------
131    Rohit Pawar    Mumbai
141    Ashish Mane    Pune
152    Sagar Gore    Mumbai
155    Nitesh Chaure    Nagpur
501    Ritesh Dev    Chennai
531    Dev Khire    Delhi

Menu:
1.Insert
2.Delete
3.See Changes in View Student_Data2
4.Exit
Enter Choice:1

Enter sid: 123
Enter sname: Amit Kumar
Enter address: Nanded
row inserted

Menu:
1.Insert
2.Delete
3.See Changes in View Student_Data2
4.Exit
Enter Choice:3

Changes Done in the Student table also Affects the View Student_Data2
***********Student**************
sid    sname    address
-----------------------------------------------
131    Rohit Pawar    Mumbai
141    Ashish Mane    Pune
152    Sagar Gore    Mumbai
155    Nitesh Chaure    Nagpur
501    Ritesh Dev    Chennai
531    Dev Khire    Delhi
123    Amit Kumar Nanded

Menu:
1.Insert
2.Delete
3.See Changes in View Student_Data2
4.Exit
Enter Choice:2

Enter sid to be deleted: 123
row deleted

Menu:
1.Insert
2.Delete
3.See Changes in View Student_Data2
4.Exit
Enter Choice:3

Changes Done in the Student table also Affects the View Student_Data2
***********Student**************
sid    sname    address
-----------------------------------------------
131    Rohit Pawar    Mumbai
141    Ashish Mane    Pune
152    Sagar Gore    Mumbai
155    Nitesh Chaure    Nagpur
501    Ritesh Dev    Chennai
531    Dev Khire    Delhi

Menu:
1.Insert
2.Delete
3.See Changes in View Student_Data2
4.Exit
Enter Choice:4

Explanation :
The Student table and Student View are printed at the starting of output. Then if user selects the option 1 to insert the data in student table ‘123 Amit Kumar Nanded’ this new row is inserted into the table. Then user select the 3rd option to see the changes reflected in student view or not, after selecting the 3rd option we could see that the same entry is also got added in the Student View. Then user selects the 2nd option and gives the sid = 123 as input to delete the entry from Student table Similarly we could see that the same entry got deleted from the Student View as well. This is how the Refresh Materialized views works.

Please note that for each database software the internal implementation may differ.



My Personal Notes arrow_drop_up

Check out this Author's contributed articles.

If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.


Article Tags :
Practice Tags :


Be the First to upvote.


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.