1 / 40

Advanced JDBC

Advanced JDBC. Session 3. Session objectives. Work with ResultSets. Describe the PreparedStatement interface. Describe the CallableStatement interface. Discuss the DatabaseMetadata interface and its various methods. Introduction.

jemima
Download Presentation

Advanced JDBC

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Advanced JDBC Session 3

  2. Session objectives • Work with ResultSets • Describe the PreparedStatement interface • Describe the CallableStatement interface • Discuss the DatabaseMetadata interface and its various methods Advanced Java / Session 3 / 2 of 40

  3. Introduction • An individual is always on the lookout for a more efficient and speedier ways to accomplish a task • A better way of performing a task improves the style of functioning • Better features are incorporated in the newer version of a software program • For example : to Advanced Java / Session 3 / 3 of 40

  4. The PreparedStatement interface (1) • PreparedStatement increases the efficiency of the program • PreparedStatement objects help specify multiple arguments for a particular SQL command • PreparedStatement interface inherits from the Statement interface Advanced Java / Session 3 / 4 of 40

  5. The PreparedStatement interface (2) • SQL statement is precompiled in case of PreparedStatement object • SQL statement in PreparedStatement may have one or more IN parameters • An IN parameter value is not specified when the SQL statement is created • A value has to be specified using set<Type>() method • Statement object compiles and executes SQL Advanced Java / Session 3 / 5 of 40

  6. PreparedStatement object • Adds methods to pass values to the database in place of the placeholders for IN parameters • To create a PreparedStatement object: • PreparedStatement pstmt = con.preparedStatement(“UPDATE course SET hours=? WHERE coursetitle=?”); • con is a Connection object • Inherits all the functionality of the Statement object Advanced Java / Session 3 / 6 of 40

  7. Example Output Advanced Java / Session 3 / 7 of 40

  8. The CallableStatement interface (1) • Stored procedures can be invoked using the CallableStatement object • This call can be: • With a result parameter • Without a result parameter • Return value from a stored procedure is passed to an OUT parameter • A stored procedure is a block of SQL code Advanced Java / Session 3 / 8 of 40

  9. The CallableStatement interface (2) • ? Represents a placeholder for a parameter • Inherits methods from the Statement and PreparedStatement objects • The calls have different number of parameters used as: • Input (IN parameters) • Output (OUT parameters) • Both (INOUT parameters) Advanced Java / Session 3 / 9 of 40

  10. The CallableStatement interface (3) • getData() has two arguments and no result parameter • The type of ? whether it is IN, OUT or INOUT depends on the stored procedure “getData()” • To create a CallableStatement object: • CallableStatement cstm = con.prepareCall( “{call getData(?,?)}” ); Advanced Java / Session 3 / 10 of 40

  11. Parameters (1) • OUT • In case stored procedures return values, then JDBC type of each OUT parameter has to be registered • registerOutParameter is used to register JDBC type • After registration, get<Type>() methods of CallableStatement are used to retrieve the paarameter value • IN • set<Type> methods are used to pass any IN parameter values to a CallableStatement objetc • Example : setFloat() to pass a float value Advanced Java / Session 3 / 11 of 40

  12. Parameters (2) • A parameter whose Java is int, should use the method setInt() to assign input value and supply an INTEGER as JDBC type to registerOutParameter() • INOUT • A parameter that accepts input as well as output • Requires a call to the appropriate set<Type> method and a call to the registerOutParameter() method JDBC value Java value • Set<Type>() Advanced Java / Session 3 / 12 of 40

  13. CallableStatement - Example Output Advanced Java / Session 3 / 13 of 40

  14. DatabaseMetaData interface • At times, one needs to have information about the database as a whole • DatabaseMetaData interface is the largest interface in the java.sql package • It provides methods to find out the RDBMS and database state properties dynamically • Data in a database is of interest to any user Advanced Java / Session 3 / 14 of 40

  15. Methods to identify the RDBMS and its version • getDatabaseProductVersion() • returns the version number of the database • getTableTypes() • returns the table types available in the database • getDatabaseProductName() • returns the product name Advanced Java / Session 3 / 15 of 40

  16. Example (1) Advanced Java / Session 3 / 16 of 40

  17. Example (2) Output Advanced Java / Session 3 / 17 of 40

  18. Methods to detect the available JDBC Drivers • getDriverVersion() • returns the version number of this driver • getDriverMajorVersion() • returns the major version number of the JDBC driver • getDriverMinorVersion() • returns the minor version number of the JDBC driver • getDrivername() • returns the name of the current JDBC driver Advanced Java / Session 3 / 18 of 40

  19. Example (1) Advanced Java / Session 3 / 19 of 40

  20. Example (2) Output Advanced Java / Session 3 / 20 of 40

  21. Working with Tables (1) • getTableTypes() – retrieves a list of the types of tables available in the database • getTablePrivileges() – used to determine the users privileges or access rights for any particular table. Also enables us to grant privileges for the table • Methods to determine the name and properties of the table are: Advanced Java / Session 3 / 21 of 40

  22. Working with Tables (2) • Syntax for this method: public ResultSet getTables(String catalog, String schemaPattern, String tableNamePattern, String [] types) throws SQLException • getTables() – returns only the table descriptions matching the catalog, schema, table name and type criteria Advanced Java / Session 3 / 22 of 40

  23. Stored Procedures • Methods used to access stored procedure names and structures stored in a database: • getProcedureTerm() • returns vendor specific name • supportsStoredProcedure() • returns true/false depending on whether the database supports stored procedures • getProcedures() • returns ResultSet containing a list of procedure names and their types • getProcedureColumns() • returns ResultSet containing description of parameters and column names used in a particular procedure Advanced Java / Session 3 / 23 of 40

  24. Example (1) Advanced Java / Session 3 / 24 of 40

  25. Example (2) Output Advanced Java / Session 3 / 25 of 40

  26. Working with ResultSets • A query on the database returns the result in the form of a table • The ResultSet interface gives access to this tabular format • ResultSet maintains a pointer that keeps track of the current row • Tables are made of rows and columns Advanced Java / Session 3 / 26 of 40

  27. ResultSet interface • Execution of ResultSet overwrites the previous results • ResultSet object automatically closes when the related Statement is closed • ResultSet objects are entirely dependent on the Statement and Connection objects Advanced Java / Session 3 / 27 of 40

  28. ResultSet interface functions • getRow() • determines the number of rows retrieved by the ResultSet object • get<Type>() • returns the data from the ResultSet object • getString() – retrieves character type data • getInt() – retrieves integer type data • next() • positions ResultSet to the next row Advanced Java / Session 3 / 28 of 40

  29. Processing the current row • The pointer position determines the current row get() next() next method navigates through the ResultSet to process each row next moves the pointer from the current location to the next row If the row pointer advances to a row containing no data, the pointer returns false else it returns true get method processes the current row Advanced Java / Session 3 / 29 of 40

  30. Processing the columns • “get” method has two forms • One takes the column name as its argument • The other takes column index number as its argument • Columns can be directly accessed using the get<Type> method Advanced Java / Session 3 / 30 of 40

  31. Working with ResultSetMetaData object • Uses the getColumnCount() method to return the number of columns in the tabular data accessed by the ResultSet • getColumnName() method returns the column name • getColumnType() method returns the type of column • Used to get information about the types and properties of columns in a ResultSet object Advanced Java / Session 3 / 31 of 40

  32. Example Output Advanced Java / Session 3 / 32 of 40

  33. Using Transactions (1) • Related data may be stored in different computers • Transaction is a set of statements executed together as a unit • Either all statements are executed or none of them • Transactions help an action to be initiated if another action is successful • Distributed databases are becoming increasingly popular Advanced Java / Session 3 / 33 of 40

  34. Using Transactions (2) • Each SQL statement is treated as a transaction • Auto-commit mode has to be disabled to allow two or more statements to be grouped • No SQL statement will be committed until one calls the method explicitly • A connection by default is in auto-commit mode Advanced Java / Session 3 / 34 of 40

  35. Code Snippet • delStud and delResult will be committed together when the method commit is called • Last line turns auto-commit on Advanced Java / Session 3 / 35 of 40

  36. Using java.sql with applets • Applets help display results in a graphical interface Database Applets JDBC Driver Preferably JDBC-Net pure Java driver Advanced Java / Session 3 / 36 of 40

  37. Example (1) Advanced Java / Session 3 / 37 of 40

  38. Example (2) Advanced Java / Session 3 / 38 of 40

  39. Example (3) Advanced Java / Session 3 / 39 of 40

  40. Example (4) Output Advanced Java / Session 3 / 40 of 40

More Related