Recent Posts

Saturday, 17 November 2018

MetaData


     Metadata means data about data. i.e., Metadata provides extra information about our original data. E.g:
Metadata about database is nothing but database product name, database version etc.
Metadata about ResultSet means no of columns, each column name, column type etc.
     JDBC provides support for 3 Types of Metadata
1. DatabaseMetaData
2. ResultSetMetaData
3. ParameterMetaData

1. DatabaseMetaData 
☀ It is an interface present in java.sql package.
☀ Driver Software vendor is responsible to provide implementation.
☀ We can use DatabaseMetaData to get information about our database like database product name, driver name, version, number of tables etc.
☀ We can also use DatabaseMetaData to check whether a particular feature is supported by DB or not like stored procedures, full joins etc.
☀ We can get DatabaseMetaData object by using getMetaData() method of Connection interface.
E.g. DatabaseMetaData dbmd=con.getMetaData();
     Once we got DatabaseMetaData object we can call several methods on that object like
getDatabaseProductName()
getDatabaseProductVersion()
getMaxColumnsInTable()
supportsStoredProcedures() etc.
import java.sql.*;
/**
 * 
 * @author ashok.mariyala
 *
 */
public class DatabaseMetaDataTest { 
   public static void main(String[] args) throws Exception { 
      Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE","system","ashok");
      DatabaseMetaData dbmd = con.getMetaData(); 
      System.out.println("Database Product Name : "+ dbmd.getDatabaseProductName()); 
      System.out.println("DatabaseProductVersion : "+ dbmd.getDatabaseProductVersion()); 
      System.out.println("DatabaseMajorVersion : "+ dbmd.getDatabaseMajorVersion()); 
      System.out.println("DatabaseMinorVersion : "+ dbmd.getDatabaseMinorVersion()); 
      System.out.println("JDBCMajorVersion : "+ dbmd.getJDBCMajorVersion()); 
      System.out.println("JDBCMinorVersion : "+ dbmd.getJDBCMinorVersion()); 
      System.out.println("DriverName : "+ dbmd.getDriverName()); 
      System.out.println("DriverVersion : "+ dbmd.getDriverVersion());
      System.out.println("URL : "+ dbmd.getURL()); 
      System.out.println("UserName : "+ dbmd.getUserName()); 
      System.out.println("MaxColumnsInTable : "+ dbmd.getMaxColumnsInTable());
      System.out.println("MaxRowSize : "+ dbmd.getMaxRowSize()); 
      System.out.println("MaxStatementLength : "+ dbmd.getMaxStatementLength());
      System.out.println("MaxTablesInSelect : "+ dbmd.getMaxTablesInSelect()); 
      System.out.println("MaxTableNameLength : "+ dbmd.getMaxTableNameLength()); 
      System.out.println("SQLKeywords : "+ dbmd.getSQLKeywords()); 
      System.out.println("NumericFunctions : "+ dbmd.getNumericFunctions()); 
      System.out.println("StringFunctions : "+ dbmd.getStringFunctions()); 
      System.out.println("SystemFunctions : "+ dbmd.getSystemFunctions()); 
      System.out.println("supportsFullOuterJoins : "+ dbmd.supportsFullOuterJoins()); 
      System.out.println("supportsStoredProcedures : "+ dbmd.supportsStoredProcedures());   
   con.close(); 
   }
}
2. ResultSetMetaData
☀ It is an interface present in java.sql package.
☀ Driver software vendor is responsible to provide implementation.
☀ It provides information about database table represented by ResultSet object.
☀ Useful to get number of columns, column types etc
     We can get ResultSetMetaData object by using getMetaData() method of ResultSet interface.
E.g
ResultSetMetaData rsmd=rs.getMetaData();
     Once we got ResultSetMetaData object, we can call the following methods on that object like getColumnCount()
getColumnName()
getColumnType() etc.
import java.sql.*;
/**
 * 
 * @author ashok.mariyala
 *
 */
public class DatabaseMetaDataTest { 
   public static void main(String[] args) throws Exception { 
      Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE","system","ashok");
      Statement st = con.createStatement(); 
      ResultSet rs = st.executeQuery("select * from emp");
      ResultSetMetaData rsmd = rs.getMetaData(); 
      int count = rsmd.getColumnCount(); 
      for(int i=1;i<= count;i++) { 
         System.out.println("Column Number : "+ i); 
         System.out.println("Column Name : "+ rsmd.getColumnName(i)); 
         System.out.println("Column Type : "+ rsmd.getColumnType(i));       
         System.out.println("Column Size : "+ rsmd.getColumnDisplaySize(i));
         System.out.println("---------------"); 
      }
      con.close(); 
   } 
}
3. ParameterMetaData
☀ It is an interface and present in java.sql package.
☀ Driver Software vendor is responsible to provide implementation.
☀ In General we can use positional parameters(?) while creating PreparedStatement object.
PreparedStatement pst = con.prepareStatement("insert into employees values(?,?,?,?)"); 
☀ We can use ParameterMetaData to get information about positional parameters like parameter count, parameter mode, and parameter type etc.
☀ We can get ParameterMetaData object by using getParameterMetaData() method of PreparedStatement interface.
ParameterMetaData pmd=pst.getParameterMetaData();
     Once we got ParameterMetaData object, we can call several methods on that object like
1. int getParameterCount()
2. int getParameterMode(int param)
3. int getParameterType(int param)
4. String getParameterTypeName(int param) etc..

Important Methods of ParameterMetaData
1. int getParameterCount()
     Retrieves the number of parameters in the PreparedStatement object for which this ParameterMetaData object contains information.

2.int getParameterMode(int param) 
     Retrieves the designated parameter's mode.

3. int getParameterType(int param) 
     Retrieves the designated parameter's SQL type.

4. String getParameterTypeName(int param) 
     Retrieves the designated parameter's database-specific type name.

5. int getPrecision(int param) 
     Retrieves the designated parameter's specified column size.

6. int getScale(int param) 
     Retrieves the designated parameter's number of digits to right of the decimal point.

7. int isNullable(int param)
    Retrieves whether null values are allowed in the designated parameter.

8. boolean isSigned(int param) 
     Retrieves whether values for the designated parameter can be signed numbers.

import java.sql.*;
/**
 * 
 * @author ashok.mariyala
 *
 */
public class DatabaseMetaDataTest { 
   public static void main(String[] args) throws Exception { 
      Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE","system","ashok");
      PreparedStatement pst = con.prepareStatement("insert into emp values(?,?,?,?)"); 
      ResultSet rs = st.executeQuery("select * from emp");
      ParameterMetaData pmd = pst.getParameterMetaData();
      int count=pmd.getParameterCount();
      for(int i=1;i<= count;i++) { 
         System.out.println("Parameter Number : "+ i); 
         System.out.println("Parameter Mode : "+ pmd.getParameterMode(i)); 
         System.out.println("Parameter Type : "+ pmd.getParameterType(i)); 
         System.out.println("Parameter Precision : "+ pmd.getPrecision(i)); 
         System.out.println("Parameter Scale : "+ pmd.getScale(i)); 
         System.out.println("Parameter isSigned : "+ pmd.isSigned(i)); 
         System.out.println("Parameter isNullable : "+ pmd.isNullable(i)); 
         System.out.println("---------------"); 
      }
      con.close(); 
   } 
}
Note
     Most of the drivers won't provide support for ParameterMetaData.

Next Tutorial : ResultSet Types

Previous Tutorial : Transaction Management in JDBC


No comments:

Post a Comment