1 / 32

Java Database Connectivity

Java Database Connectivity. Review. A session is a long-term connection that utilizes the session layer of a network layer protocol. A session exists for each connection between a user and an instance of a running application.

avon
Download Presentation

Java Database Connectivity

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. Java Database Connectivity

  2. Review • A session is a long-term connection that utilizes the session layer of a network layer protocol. • A session exists for each connection between a user and an instance of a running application. • Session acts as a link between the server and the client events. The session helps the Web server to distinguish between different users. • JSP uses the sessions to store unique data of a particular client connected to a Web application. The different methods of session object include: • getAttribute() • getAttributeNames() • getCreationTime() • getId() • getLastAccessedTime() • getMaxInactiveInterval() • removeAttribute() • setAttribute() • setMaxInactiveInterval()

  3. Review – Contd… • Session tracking maintains the session information and keeps track of the multiple requests made by the client. • The session tracking feature in the servlets or JSP container maintains the state of a Web browser. • Cookies are text files that are stored on the user computer, and contain the session Id of the user sent by the Web server. • The cookie is sent back to the Web server with every subsequent request made by the user in the same session. • The information in the cookies helps the Web server to identify the user, as the value of each cookie is unique. • The session ID keeps track of requests made within the same session. • The session ID is encoded in the URLs that are created by the JSP pages. • URL Rewriting works with Web browsers that do not support cookies, or the cookies that are disabled on a Web browser. • The hidden field is used to store information about a session. In addition, the hidden form field helps to carry the information from one HTML page to another.

  4. Objectives • Explain Java database connectivity • Describe various JDBC drivers • Explain use of JDBC in JSP • Describe different database operations • Explain use of JDBC connectivity through JavaBeans • Describe database connection pooling process

  5. Java Database Connectivity • Provides a programming interface that is used to request a connection between the application and database • JDBC API executes SQL statements and sends the results through a single API • JDBC API executes simple SQL queries in the Java code to retrieve data from database

  6. Java Database Connectivity • Five steps

  7. Loading the Driver • The driver is a Java class that translates Java statements to SQL statements • The Class.forName() method is used to load the driver • A driver class needs to be loaded to load the driver • Syntax Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);

  8. Connecting to Database • The java.sql package provides classes and interfaces that are used to interact with the database • The classes send the SQL queries to the database and process the queries • Syntax Connection con= DriverManager.getConnection(jdbc:odbc:Datasource, “userid”, “pwd” ) • The JDBC classes for creating a connection are: • Java.sql.Driver • Java.sql.DriverManager • Java.sql.Connection

  9. Create Statement • Sends queries and command to the database • Created from the Connection object • Syntax Statement stat = con.createStatement()

  10. Execute Query • Used to send SQL queries to the database • Returns an object of type ResultSet • ResultSet() object provides methods that are used to access data from data source • Syntax String query = “Select * from table_name”; ResultSet resultset = stat.executeQuery(query);

  11. Processing Results • next() method of the ResultSet object is used to process the results from the database Points the cursor to next row while(rs.next()) { rs.getRow(); result += "<tr>"; for ( int i = 1; i <= columns; i++) { result += "<td>" + rs.getObject(i).toString() + "</td>"; } result += "</tr>"; } Traversing through records using for loop

  12. ResultSet Object Methods

  13. ResultSet Object Methods-Cont…

  14. getMetaData() methods

  15. getMetaData() Methods-Cont…

  16. JDBC Connectivity - Code Snippet <html> <head> <title>DB Test</title> </head> <body> <%@ page language="java" import="java.sql.*" %> <% try { out.println("loading driver...<br>"); Class.forName("sun.jdbc.odbc.JdbcOdbcDriver "); out.println("connecting...<br>"); Connection con= DriverManager.getConnection(jdbc:odbc:Datasource, "userid ", "pwd ") out.println("querying database...<br>"); Statement stat = con.createStatement(); String query = "Select * from table_name "; ResultSet rs = statement.executeQuery(query); Importing Java.sql.* package Loading the driver Creating Connection object Creating Statement Executing the query

  17. JDBC Connectivity - Code Snippet Cont… while (rs.next()) { out.println(rs.getString(1)+ "<br>"); } rs.close(); Stat.close(); c.close(); } catch(Exception e) { out.println("ERROR! "+e.getMessage()); } %> </body> </html> Processing results Closing connections Catch exception and display error message

  18. JDBC Drivers • Translates Java statements to SQL statements • Helps applications to interact with the database, using Java’s built-in Driver Manager • JDBC driver manager maintains a list of drivers created for different databases • JDBC drivers connect the Java application to the driver specified in the Java program • Provides Java applications that are DBMS independent • The four types of JDBC drivers are: • JDBC ODBC Bridge driver • Native API driver • Network-protocol Driver • Native protocol driver

  19. JDBC ODBC Bridge Driver • Type 1 driver • Translates JDBC API to ODBC API • Enables the Java applications to interact with any database • Provides platform dependence, as JDBC ODBC bridge driver uses ODBC • JDBC-ODBC bridge is useful when Java driver is not available for a database

  20. Native API Driver • Type 2 driver • Provides access to the database through C/C++ • Developed using native code libraries • Native code libraries provide access to the database, and improve the performance • Java application sends a request for database connectivity as a normal JDBC call to the Native API driver • Establishes the call, and translates the call to the particular database protocol that is forwarded to the database

  21. Network Protocol Driver • Type 3 driver • Communicates with the middle layer component that provides data connectivity • Manages multiple Java applications connecting to different databases

  22. Native Protocol Driver • Type 4 driver • Communicates directly with the database using Java sockets • Improves the performance as translation is not required • Converts JDBC queries into native calls used by the particular RDBMS

  23. Using JDBC in JSP • The java.sql.* package provides database access in Java • JDBC can be used in JSP by inserting JDBC query in the scriplet code • After compilation, the scriplet code that contains the query is placed in the jspService() method

  24. Database Operations • Accessing database requires JDBC code to perform database operations • The JDBC code includes queries for selecting, updating, inserting, or deleting data from the database • A connection object needs to be created for database access • The Connection object is used to generate SQL statements, which perform database operations

  25. Basic Database Operations

  26. JDBC Connectivity through JavaBeans • JavaBeans components are reusable Java classes, and can be put together into applications • JDBC connectivity through JavaBeans is an efficient way for interacting with database • The JDBC components facilitates interaction of Java Swing components with a database • JDBC connectivity through JavaBeans provides database access by inserting the JDBC code in scriplets

  27. JDBC Connectivity through JavaBeans Import Java class <%@ page import="java.sql.* " %> <jsp:useBean id="Account" class="AccountBean"> <% Connection connection = null; Statement statement = null; ResultSet results = null; AccountBean Account = new AccountBean(); //code for JDBC %> </jsp:useBean> //html code to display the results Initializing variables

  28. Database Connection Pooling • Connection pooling is a process that manages multiple database connection requests • Connection pooling process increases the speed of data access, and reduces the number of database connections for an application • Connection pooling process helps in improving the performance of application • Connection pooling tasks include: • Pre-allocate connections • Manage available connections • Allocate new connections • Close connections

  29. Summary • The JDBC programming interface is used to request a connection with database to retrieve data • The JDBC API contains some classes and interfaces that connect an application to a database using DBMS/RDBMS, and execute the SQL queries • The Class.forName() method takes string as a parameter which is used to load the driver • java.sql.DriverManager maintains a list of drivers created for databases and connects the java application to the required driver • java.sql.Connection sends a series of SQL statements to the database and processes the SQL queries • The Statement object is created from the Connection object. The Statement object sends queries and commands to the database. • The executeQuery() method is used to send the SQL queries to the database. • The next() method of the ResultSet object is used to process the results from the database. The methods in ResultSet class include: • getString() • getInt() • getFloat() • getDate()

  30. Summary-Cont… • findColumn() • wasNull() • getMetaData() • The getMetaData()returns the information regarding the columns of ResultSet object in a ResultSetMetaData class. The getMetaData()methods include: • getColumnCount() • getColumnName() • getColumnType() • isReadOnly() • isSearchable() • isNullable() • JDBC drivers translate Java statements to SQL statements. • The JDBC driver manager connect the Java application to the driver specified in the Java program

  31. Summary-Cont… • JDBC drivers make Java applications DBMS independent. The four types of drivers are: • JDBC-ODBC bridge driver • Native API driver • Network protocol driver • Native protocol driver • The JDBC code includes queries for selecting, updating, inserting, or deleting the required data from the database. The basic database operations are: • Insert data • Update data • Delete data • Select data • Connection pooling is a process that handles multiple database connection requests • The tasks of Connection pooling class include: • Pre-allocate connections • Manage available connections • Allocate new connections • Close connections

  32. Q & A

More Related