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.
// Java Program to implementat// Materialized viewimport 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 // 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); } }} |
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.
