What Are the View Names for a Database? JDBC

A view is an alternative representation of data from one or more tables or views. A view can include all or some of the columns contained in one or more tables on which it is defined. A view is effectively a SQL query stored in the database catalog. Some database vendors, including Oracle, support the concept of views, while others, such as MySQL, do not at the present time (however, MySQL will support views starting with version 5.0). Therefore, a view is a virtual table and can be defined by SQL statements, but it may be vendor dependent. In general, views can be used for security purposes, such as hiding a salary field, or for the convenience of programmers or database administrators. Views enable the user to see only the information he or she needs at the moment, and provides security for the database managers. Using DatabaseMetaData.getTables(catalog, schemaPattern, tableNamePattern, types) method, and by passing {"VIEW"} to the types parameter, you can get a list of views belonging to a database user. For Oracle databases, this method returns user-defined and system views. To get only user-created views, I use Oracle’s metadata table, user_objects, which includes user-created tables and views. To get the views, use the following query:

select object_name from user_objects where object_type = 'VIEW'; To get the tables and views together, you can issue: select object_name from user_objects where object_type = 'TABLE' or object_type = 'VIEW';

Consider the following table, which lists names of employees and their salaries:

create table MyPayrollTable( id varchar(9) not null primary key, name varchar(30) not null, salary int not null);

If you want the employee names available but not their salaries, you can define the following view:

create view EmployeeNamesView (id, name)as select id, name from MyPayrollTable;

Here is how the EmployeeNamesView is created in the Oracle 9i SQL*Plus program:

Oracle Solution: getOracleViewNames()

Next, let’s look at the JDBC solution for finding the views for a given database. Here is the solution for Oracle:

Testing getOracleViewNames()

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

JDBC Topics