UNIT 5 NOTES AJP 22517 Part 4 PDF

Unit V – Interacting with Database TEACHING HOURS – 12 TOTAL MARKS – 12 Course Outcome (CO-5) – Develop programs using database. 11. Explain ResultSetMetaData interface in Java- Explanation:  It is used to get information about...

Unit V – Interacting with Database TEACHING HOURS – 12 TOTAL MARKS – 12 Course Outcome (CO-5) – Develop programs using database. 11. Explain ResultSetMetaData interface in Java- Explanation:  It is used to get information about the types and properties of the columns in a ResultSet object.  The object of ResultSetMetaData can be retrieved using following method of ResultSet object. ResultSetMetaData rsmd = rs.getMetaData(); // here rs is ResultSet Methods:  public String getCatalogName(int column) throws SQLException o Gets the designated column's table's catalog name. It returns "" if not applicable.  public int getColumnCount() throws SQLException o Returns the number of columns in this ResultSet object.  public int getColumnDisplaySize(int column) throws SQLException o Indicates the designated column's normal maximum width in characters.  public String getColumnLabel(int column) throws SQLException o Gets the designated column's suggested title for use in printouts and displays. The suggested title is usually specified by the SQL AS clause. If a SQL AS is not specified, the value returned from getColumnLabel will be the same as the value returned by the getColumnName method.  public String getColumnName(int column) throws SQLException o Get the designated column's name.  public String getColumnTypeName(int column) throws SQLException o Retrieves the designated column's database specific SQL type. 12. Explain DatabaseMetaData interface in Java- Explanation: DatabaseMetaData interface provides methods to get meta data of a database such as database product name, database product version, driver name, name of total number of tables, name of total number of views etc. Methods: Lecture Notes On Advanced Java Programming (AJP-22517) Under MSBTE I-SCHEME Prepared By Prof. S.M. Inwate  public int getDatabaseMajorVersion() throws SQLException o Retrieves the major version number of the underlying database.  public int getDatabaseMinorVersion() throws SQLException o Retrieves the minor version number of the underlying database.  public String getDatabaseProductName() throws SQLException o Returns the product name of the database.  public String getDatabaseProductVersion() throws SQLException o Returns the product version of the database.  public String getDriverName() throws SQLException o Returns the name of the JDBC driver.  public String getDriverVersion() throws SQLException o Returns the version number of the JDBC driver.  public String getUserName() throws SQLException o Returns the username of the database.  public ResultSet getTables(String catalog, String schemaPattern, String tableNamePattern, String[] types) throws SQLException o Returns the description of the tables of the specified catalog. The table type can be TABLE, VIEW, ALIAS, SYSTEM TABLE, SYNONYM etc.  public ResultSet getProcedures(String catalog, String schemaPattern, String procedureNamePattern) throws SQLException o Retrieves a description of the stored procedures available in the given catalog.  public ResultSet getFunctions(String catalog, String schemaPattern, String functionNamePattern) throws SQLException o Retrieves a description of the system and user functions available in the given catalog.  public String getNumericFunctions() throws SQLException o Retrieves a comma-separated list of math functions available with this database. Lecture Notes On Advanced Java Programming (AJP-22517) Under MSBTE I-SCHEME Prepared By Prof. S.M. Inwate  public String getStringFunctions() throws SQLException o Retrieves a comma-separated list of string functions available with this database.  public String getSystemFunctions() throws SQLException o Retrieves a comma-separated list of system functions available with this database.  public String getTimeDateFunctions() throws SQLException o Retrieves a comma-separated list of the time and date functions available with this database.  public String getSQLKeywords() throws SQLException o Retrieves a comma-separated list of all of this database's SQL keywords. =================================================================== Programs on DatabaseMetaData ================================================================== Aim: To display properties of the database used. import java.sql.*; import java.io.*; @SuppressWarnings("deprecation") class Mydatabase { public static void main(String[] args) { try { // create driver and register it Class.forName("com.mysql.cj.jdbc.Driver"); // Establish the connection with college database String url = "jdbc:mysql://localhost:3306/college"; String username = "root"; String password = "Admin@123"; Connection con = DriverManager.getConnection(url, username, password); // Create a database metadata object DatabaseMetaData dbmd = con.getMetaData(); Lecture Notes On Advanced Java Programming (AJP-22517) Under MSBTE I-SCHEME Prepared By Prof. S.M. Inwate  // Obtain database information System.out.println("Database Major Version= " + dbmd.getDatabaseMajorVersion()); System.out.println("Database Minor Version= " + dbmd.getDatabaseMinorVersion()); System.out.println("Database Product Name= " + dbmd.getDatabaseProductName()); System.out.println("Database Product Version= " + dbmd.getDatabaseProductVersion()); // Close the connection con.close(); } catch (Exception e) { System.out.println("Error:"+e); } } } OUTPUT: Aim: To display list of Stored Procedures and tables available in SQL. import java.sql.*; import java.io.*; @SuppressWarnings("deprecation") class Mydatabase { public static void main(String[] args) { try { Lecture Notes On Advanced Java Programming (AJP-22517) Under MSBTE I-SCHEME Prepared By Prof. S.M. Inwate  // create driver and register it Class.forName("com.mysql.cj.jdbc.Driver"); // Establish the connection with college database String url = "jdbc:mysql://localhost:3306/college"; String username = "root"; String password = "Admin@123"; Connection con = DriverManager.getConnection(url, username, password); // Create a database metadata object DatabaseMetaData dbmd = con.getMetaData(); ResultSet rs = dbmd.getTables("college",null,null,null); // Obtain table information System.out.println("List of Tables= "); while(rs.next()) { System.out.println(" "+rs.getString(1)+ " " + rs.getString(2)+ " "+rs.getString(3)+ " " + rs.getString(4)); } rs.close(); System.out.println("List of Procedures= "); rs = dbmd.getProcedures("sys",null,null); // Obtain procedure information while(rs.next()) { System.out.println(" "+rs.getString(1)+ " " + rs.getString(2)+ " "+rs.getString(3)); } // Close the connection rs.close(); con.close(); Lecture Notes On Advanced Java Programming (AJP-22517) Under MSBTE I-SCHEME Prepared By Prof. S.M. Inwate  } catch (Exception e) { System.out.println("Error:"+e); } } } OUTPUT: ====================================================================================== Aim: To display list of SQL Keywords, math functions, string functions and numeric functions available in SQL. import java.sql.*; import java.io.*; @SuppressWarnings("deprecation") class Mydatabase { public static void main(String[] args) { try { // create driver and register it Class.forName("com.mysql.cj.jdbc.Driver"); // Establish the connection with college database String url = "jdbc:mysql://localhost:3306/college"; String username = "root"; String password = "Admin@123"; Connection con = DriverManager.getConnection(url, username, password); Lecture Notes On Advanced Java Programming (AJP-22517) Under MSBTE I-SCHEME Prepared By Prof. S.M. Inwate  // Create a database metadata object DatabaseMetaData dbmd = con.getMetaData(); System.out.println("Keywords Available in SQL :"); System.out.println(dbmd.getSQLKeywords()); System.out.println("Numeric Functions Available in SQL :"); System.out.println(dbmd.getNumericFunctions()); System.out.println("String Functions Available in SQL :"); System.out.println(dbmd.getStringFunctions()); System.out.println("System Functions Available in SQL :"); System.out.println(dbmd.getSystemFunctions()); System.out.println("Time Date Functions Available in SQL :"); System.out.println(dbmd.getTimeDateFunctions()); con.close(); } catch (Exception e) { System.out.println("Error:"+e); } } } OUTPUT: =================================================================== Programs on ResultSetMetaData ================================================================== Lecture Notes On Advanced Java Programming (AJP-22517) Under MSBTE I-SCHEME Prepared By Prof. S.M. Inwate Aim: To display properties of the table student available in college database. import java.sql.*; import java.io.*; @SuppressWarnings("deprecation") class Mydatabase { public static void main(String[] args) { try { // create driver and register it Class.forName("com.mysql.cj.jdbc.Driver"); // Establish the connection with college database String url = "jdbc:mysql://localhost:3306/college"; String username = "root"; String password = "Admin@123"; Connection con = DriverManager.getConnection(url, username, password); Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery("select * from student"); ResultSetMetaData rsmd = rs.getMetaData(); int cols = rsmd.getColumnCount(); System.out.println("S.No \t Column Name Column Type"); for(int i=1;i