330 likes | 497 Views
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.
E N D
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()
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.
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
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
Java Database Connectivity • Five steps
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”);
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
Create Statement • Sends queries and command to the database • Created from the Connection object • Syntax Statement stat = con.createStatement()
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);
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
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
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
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
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
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
Network Protocol Driver • Type 3 driver • Communicates with the middle layer component that provides data connectivity • Manages multiple Java applications connecting to different databases
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
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
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
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
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
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
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()
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
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