1 / 33

Understanding SQL Joins and JDBC API for Java Applications

Learn about different SQL joins (Inner, Cross, Outer) to retrieve data from multiple tables in Java applications. Explore the JDBC API methods to interact with databases effectively.

rickycollins
Download Presentation

Understanding SQL Joins and JDBC API for Java Applications

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. Knowledge Byte • In this section, you will learn about: • Working with Joins • JDBC API • Isolation Levels Collaborate

  2. Working with Joins • SQL joins in queries are executed using the Statement or PreparedStatement objects. • A join is an SQL operation that enables you to specify conditions to retrieve data from multiple tables. • Different types of joins that you can use in a Java application are: • Inner joins • Cross joins • Outer joins Collaborate

  3. Working with Joins (Contd.) • Inner Join: • Combines the rows retrieved from multiple tables on the basis of the common columns of the tables. • Specifies the condition to join the tables using comparison operators in the ON clause of the SELECT statement. • Can be created using the following code snippet in a java application: • String str = "SELECT EmpInfo.EName, EmpInfo.DeptID, DeptInfo.DeptName, EmpInfo.EmpDesig FROM EmpInfo INNER JOIN DeptInfo ON EmpInfo.DeptID = DeptInfo.DeptID"; • Connection con = DriverManager.getConnection("jdbc:odbc:MyDataSource", "administrator", ""); • Statement stat = con.createStatement(); • ResultSet rs = stat.executeQuery(str); Collaborate

  4. Working with Joins (Contd.) • Cross Join: • Includes more than one table without any condition in the ON clause. • Is also known as cartesian join. • Can be created using the following code snippet in a Java application: • String str = "SELECT EmpID, EName, DeptName, EmpDesig FROM EmpInfo CROSS JOIN DeptInfo"; • Connection con = DriverManager.getConnection("jdbc:odbc:MyDataSource", "administrator", ""); • Statement stat = con.createStatement(); • ResultSet rs = stat.executeQuery(str); Collaborate

  5. Working with Joins (Contd.) • Outer Join: • Retrieves all the rows from one table and the matching rows from another. • Eliminates the information contained in the row that does not match the condition for only one of the tables. • Is an extension of equi join. • Can be created using the following code snippet in a Java application: • String str = "SELECT EmpInfo.EmpID, EmpInfo.EName, EmpInfo.DeptID, DeptInfo.DeptName FROM EmpInfo LEFT OUTER JOIN DeptInfo ON EmpInfo.DeptID = DeptInfo.DeptID"; • Connection con = DriverManager.getConnection("jdbc:odbc:MyDataSource", "administrator", ""); • Statement stat = con.createStatement(); • ResultSet rs = stat.executeQuery(str); Collaborate

  6. JDBC API • JDBC API consists of various interfaces and methods that enable the Java application to communicate with a database. • The interfaces defined in the JDBC API are categorized in two packages: • The java.sql package • The javax.sql package Collaborate

  7. JDBC API (Contd.) • The java.sql Package: • Is also called the JDBC core API. • Provides the following classes and interfaces to access databases from Java applications: • Statement • PreparedStatement • CallableStatement • Connection • ResultSet • DatabaseMetaData • ResultSetMetaData Collaborate

  8. Method Description void close() Closes a Statement object and releases all the resources associated with it. void setMaxRows(int numRow) Specifies the maximum number of rows that a ResultSet object, generated by a Statement object, can store. int getMaxRows() Returns the total number of rows that can be stored in a ResultSet object. void setQueryTimeOut(int sec) Specifies the time, in seconds, for which the driver should wait to allow a Statement object to execute. int getQueryTimeout() Returns the time, in seconds, for which a driver waits to allow a Statement object to execute. • JDBC API (Contd.) • The Statement Interface • The following table lists some methods of the Statement interface: Collaborate

  9. Method Description void setObject(int index, Object obj) Sets the Object, obj, as the value for the parameter corresponding to the index. The index identifies the placeholder in the SQL statement for which the value needs to be set. void setDate(int index, Date date) Sets the java.sql.Date value for the parameter corresponding to the index. The java.sql.Date class represents the SQL DATE value in JDBC. void setTime(int index, Time time) Sets the java.sql.Time value for the parameter corresponding to the index. The java.sql.Time class represents the SQL TIME value in JDBC. • JDBC API (Contd.) • The PreparedStatement Interface • The following table lists some methods of the PreparedStatement interface: Collaborate

  10. Method Description void setBlob(int index, Blob blob) Sets the Blob type value for the parameter corresponding to the index. Binary Large Object (BLOB) is a datatype that enables you to store binary objects in a database. void setClob(int index, Clob clob) Sets the Clob type value for the parameter corresponding to the index. Character Large Objects (CLOB) is a datatype that enables you to store large data in the character format in a database. • JDBC API (Contd.) • Methods of the PreparedStatement Interface (Contd.) Collaborate

  11. Method Description boolean getBoolean(int index) Returns the boolean value for the JDBC BIT corresponding to the index passed as a parameter. byte getByte(int index) Returns the byte value for the JDBC TINYINT corresponding to the index passed as a parameter. int getInt(int index) Returns the int value for the JDBC INTEGER corresponding to the index passed as a parameter. String getString(int index) Returns the String value for the JDBC CHAR, VARCHAR, or LONGVARCHAR corresponding to the index passed as a parameter. • JDBC API (Contd.) • The CallableStatement Interface • The following table lists some methods of the CallableStatement interface: Collaborate

  12. Method Description boolean isClosed() Checks whether or not a connection with a database is closed. void setReadOnly(Boolean b) Sets the read-only mode for the connection. boolean isReadOnly() Checks whether or not a Connection object is read-only. void rollback() Rolls back the changes made to a database since the last commit or rollback operation. • JDBC API (Contd.) • The Connection Interface • The following table lists some methods of the Connection interface: Collaborate

  13. Method Description Array getArray(int in) Returns an Array object that contains the value of the column in the current row of the ResultSet object. Statement getStatement() Returns the Statement object that has generated the ResultSet object. int getInt(String columnName) Returns the value in the column, columnName, in the current row of the ResultSet object as an integer type. String getString(int index) Returns the value of the table column in the current row of the ResultSet object as a Java String. boolean rowUpdated() Returns a boolean value that specifies whether or not the current row of the ResultSet object has been updated. • JDBC API (Contd.) • The ResultSet Interface • The following table lists some methods of the ResultSet interface: Collaborate

  14. Method Description Connection getConnection() Returns the Connection object that has created the current metadata object. int getMaxColumnsInTable() Returns the maximum number of columns a database table can contain. int getMaxConnections() Returns the maximum number of connections that can be established with a database. int getMaxRowSize() Returns the maximum bytes that a single row in a database table can store . • JDBC API (Contd.) • The DatabaseMetaData Interface • The following table lists some methods of the DatabaseMetaData interface: • JDBC API (Contd.) • The DatabaseMetaData Interface • The following table lists some methods of the DatabaseMetaData interface: Collaborate

  15. Method Description boolean supportsGroupBy() Returns a boolean value that indicates whether or not the database supports the GROUP BY clause. The GROUP BY clause groups the result of a SELECT statement. boolean supportsUnion() Returns a boolean value that indicates whether or not the database supports the UNION operator. The UNION operator combines the results of two SELECT statements. • JDBC API (Contd.) • The methods of the DatabaseMetaData interface: (Contd.) Collaborate

  16. Method Description boolean isCurrency(int column) Returns a boolean value that indicates whether the column stores currency value or not. boolean isDefinitelyWritable(int column) Returns a boolean value that indicates whether the operations to write and update a column will be successfully executed or not. boolean isSearchable(int column) Returns a boolean value that indicates whether the column can be used in the WHERE clause of an SQL statement or not. boolean isSigned(int column) Returns a boolean value that indicates whether the value stored in the specified column is a signed number or not. • JDBC API (Contd.) • The ResultSetMetaData Interface • The following table lists some methods of the ResultSetMetaData interface: Collaborate

  17. JDBC API (Contd.) • The javax.sql Package: • Is also called the JDBC Standard Extension API. • Provides classes and interfaces to implement database access from Java 2 Enterprise Edition (J2EE) applications • Contains the DataSource interface. Collaborate

  18. Method Description Connection getConnection() Establishes a database connection and returns a connection object. Connection getConnection(String username, String password) Establishes a database connection after verifying the username and password passed as parameters. int getLoginTimeout() Returns the maximum time, in seconds, for which a data source should wait while establishing a database connection. • JDBC API (Contd.) • The DataSource Interface • Enables a Java application to establish a connection with a database. • The following table lists the methods of DataSource interface: Collaborate

  19. Method Description void setLoginTimeout(int seconds) Sets the maximum time, in seconds, for which a data source should wait while establishing a database connection. • JDBC API (Contd.) • Methods of the DataSource interface: (Contd.) Collaborate

  20. Isolation Levels • The transaction isolation levels in JDBC determine whether the concurrently running transactions in a database can affect each other or not. • Some common problems that might occur when multiple transactions simultaneously access a database are: • Dirty reads • Non-repeatable reads • Phantom reads Collaborate

  21. Isolation Levels (Contd.) • The isolation levels enable you to isolate the concurrently running transactions so that a transaction cannot affect the result of another transaction. • The Connection interface of the JDBC API provides the following fields as int values to set isolation levels: • TRANSACTION_READ_UNCOMMITTED • TRANSACTION_READ_COMMITTED • TRANSACTION_REPEATABLE_READ • TRANSACTION_SERIALIZABLE Collaborate

  22. Isolation Levels (Contd.) • The Connection interface contains the following methods to retrieve and set the value of transaction isolation level for a database: • getTransactionIsolationLevel() • setTransactionIsolationLevel() • The code snippet to use the getTransactionIsolationLevel() method is: • Connection con = DriverManager.getConnection ("jdbc:odbc:MyDatasource","administrator",""); • int transLevel=getTransactionIsolationLevel(); • The code snippet to use the setTransactionIsolationLevel() method is • Connection con = DriverManager.getConnection ("jdbc:odbc:MyDatasource","administrator",""); • int transLevel=getTransactionIsolationLevel(); • con.setTransactionIsolationLevel(TRANSACTION_SERIALIZABLE); Collaborate

  23. From the Expert’s Desk • In this section, you will learn: • Best practices on: • Creating Database Access Object • Storing Connection Information in a Property File • FAQs on JDBC Collaborate

  24. Best Practices • Creating a Single Database Access Object • You can create a single class to establish a connection, send SQL statements, and retrieve results from a database. • It centralizes the data access logic and any changes to the logic do not require rewriting and recompiling all application classes. • For example, you can create a Connect class in your application, all other classes of your application can use the Connect class to access the database and perform the required operation. • If any changes occur you only need to make changes in the Connect class. Collaborate

  25. Best Practices • Creating a Single Database Access Object (Contd.) • The steps to create the Connect class are: • Declare the Connection object, ResultSet object, and an int variable in the Connect class as static. • Create a static method connectDb() to obtain database connection. • Create a static method processQuery() that executes SQL queries and returns a ResultSet object. • Create a static method processDML() that executes DML commands and returns an int that specifies the number of affected rows. Collaborate

  26. Best Practices • Storing Connection Information in a Property File • A property file can be used to store the database connection information, such as driver name, data source name, username, and password to access a database. • You can create a file that Java developers can call in any Java application that requires database connection. • If the information specified in a property files changes, you need not change the Java application using the property file. • You need to change the information in the property file only. This prevents recompilation of the Java applications that use the property file. Collaborate

  27. FAQs • Can you use the JDBC-ODBC Bridge driver with applets? • Yes, you can use JDBC-ODBC bridge driver in applet to access a database. However, you need to specify permissions in the Java security policy file to allow the applet to load the Driver class. If you do not specify the permission, the Java security manager will throw an exception of type java.security.AccessControlException. This is because an applet cannot access a system resource without the proper permission. Collaborate

  28. FAQs (Contd.) • To allow the applet to use the JDBC-ODBC bridge driver, creates a .java.policy file in your home directory and adds the following permission to the file: • grant • { • permission java.lang.RuntimePermission "accessClassInPackage.sun.jdbc.odbc"; • permission java.util.PropertyPermission "file.encoding", "read"; • }; • The above entry grants the permission, RuntimePermission, to access the sun.jdbc.odbc package that contains the JdbcOdbcDriver class and PropertyPermission to read binary files. Now you can use the Applet Viewer to test the applet. Collaborate

  29. FAQs (Contd.) • Can you scroll through a ResultSet object returned from a stored procedure? • You can scroll a ResultSet object that is returned from a stored procedure, if it supports JDBC 2.0 or above. Collaborate

  30. FAQs (Contd.) • What are Large Objects (LOBs)? Does JDBC support LOBs? • LOBs are used to retrieve and update large data, such as images, files, or scanned files in the database. JDBC 3.0 supports the use of LOB data type to store data in the database. The LOBs data are sent to the database in form of Unicode stream. There are two types of LOBs, Binary Large Objects (BLOBs) and Character Large Objects (CLOBs). JDBC 3.0 API contains methods, such as getBlob(), getClob(),setBlob(), and setClob() to retrieve and specify the data stored in LOBs. The advantages of using LOBs are: • LOBs provide random access to data. • LOBs allow you to search for a pattern in the data. • LOBs enable you to determine the length of the data before actually retrieving the data. Collaborate

  31. Challenge • An object of the ________ interface enables a Java application to establish a connection with a database. • The ______________ method is used to set the transaction isolation level. • The equi join retrieves data from multiple tables on the basis of the condition specified in the WHERE clause. (True/False) • Which one of the following constants sets the database isolation levels to prevent dirty reads and non-repeatable reads? • TRANSACTION_READ_UNCOMMITTED • TRANSACTION_READ_COMMITTED • TRANSACTION_REPEATABLE READ • TRANSACTION_SERIALIZABLE Collaborate

  32. Challenge (Contd.) • What will be the result of executing the following code snippet?Statement sql2 = con.createStatement();int result=sql2.executeUpdate("Select * From Publishers"); • Will throw an exception • Will result in compilation error • Will return the number of rows retrieved from Publishers table • Will return null Collaborate

  33. Solutions to Challenge • Connection • setTransactionIsolationLevel() • True • a. TYPE_SCROLL_SENSITIVE • b. Will result in compilation error Collaborate

More Related