Database Collection of Tables which are collection of
Database • Collection of Tables which are collection of Records Relational Database • Tables are related. • Operations on one table can be made to affect another table by using Referential Integrity.
RDBMS • Collection of several relational databases (Ex) Oracle Server SQL Server DB 2 Server
SQL • SQL commands are used to speak to RDBMS Servers. • Using common SQL commands, all these servers could be contacted. (EX) Select * from table where <condition> Update table set field= <values> where <condition> Insert into table(field 1, field 2. . ) values (value 1, value 2. . . ) Delete from table where fieldname=value
Remote Database Access User Database Client API VC++, VB ODBC Database Server Database Java programs or JDBC For different databases, database client need not talk in different ways. Database client talks through ODBC API or JDBC API and the API talks to the database server. Database Server listens to a port and responds only to SQL commands passed by ODBC or JDBC.
JDBC versus ODBC • ODBC is a C language API, not a Java API. Java is object oriented and C is not. C uses pointers and dangerous programming constructs that Java does not support. • ODBC drivers must be installed on client machines. This means that the Applet that has to access to databases needs a driver in the client side. If the driver is a ODBC driver, the applet does not execute. • A Pure Java solution allows JDBC drivers to be automatically installed along with the applet.
JDBC Driver Types 1. JDBC-ODBC bridge plus ODBC driver 2. Native-API partly-Java driver 3. JDBC-Net pure Java driver 4. Native-protocol pure Java driver
JDBC Driver Types 1. JDBC-ODBC bridge plus ODBC driver: This driver uses Microsoft’s ODBC driver to communicate with Database servers. It is an attempt to use the existing ODBC drivers. It is implemented using C and Java and must be preinstalled on a client computer before it can be used.
Oracle Server ODBC JDBC Database Client JDBC ODBC Bridge ODBC DB 2 ODBC SQL Server
2. Native-API partly-Java driver: These drivers talks to database servers in a server’s native protocol. There are certain C language libraries for connecting to Oracle , DB 2 or any other database. This driver will use those C language libraries for speaking with the particular database. These drivers are implemented in a combination of binary code in Java and must be installed on a client machine.
Server specific protocol Database Client Native partly Java driver Database Server
3. JDBC-Net pure Java driver: This driver translates JDBC calls into a DBMSindependent net protocol (HTTP) which is then translated to a DBMS protocol by a server. This net server middleware is able to connect its pure Java clients to many different databases. The specific protocol used depends on the vendor. This is the best solution for applets which wants to talk to database directly without using servlets.
Oracle Server Pure Java driver Database Client Database access server DB 2 server SQL server
4. Native-protocol pure Java driver: This driver category consists of pure Java driver that uses vendor specific database protocol of the database server directly.
server specific protocol Database Client Pure Java driver Database Server
JDBC • The acronym for JDBC does not exactly stands for Java Database Connectivity but is referred by that name What Does JDBC Do? JDBC makes it possible to do three things: · establish a connection with a database · send SQL statements · process the results.
The following code fragment gives a basic example of these three steps: Connection con = Driver. Manager. get. Connection ( "jdbc: odbc: wombat", "login", "password"); Statement stmt = con. create. Statement(); Result. Set rs = stmt. execute. Query("SELECT a, b, c FROM Table 1"); while (rs. next()) { int x = rs. get. Int("a"); String s = rs. get. String("b"); float f = rs. get. Float("c"); }
The standard syntax for JDBC URLs is shown below. It has three parts, which are separated by colons: jdbc: <subprotocol>: <subname>
Driver Manager The Driver. Manager class is the management layer of JDBC, working between the user and the drivers. It keeps track of the drivers that are available and handles establishing a connection between a database and the appropriate driver.
Steps for connecting database 1. Loading the Driver Class. for. Name(“sun. jdbc. odbc. Jdbc. Odbc. Driver”); 2. Constructing URL for the database. String url=“jdbc: odbc: dsn”; 3. Getting the Connection con=Driver. Manager. get. Connection(url, userid, pwd);
4. Creating the statement Statement stmt=con. create. Statement(); 5. Execute the corresponding SQL statements. Result. Set rs=stmt. execute. Query(“select * from Table where empno=5”); 6. Reading the values from the Resultset. while(rs. next()) { String s=rs. get. String(“empname”); double sal=rs. get. Double(“empsal”); }
execute(), execute. Update(), execute. Query()--- used to execute only SQL query statements execute. Update()--- used to execute either insert, update or delete statements execute()--- used to execute any type of statements like insert, update, delete or select statements
How to use execute() statement boolean hasresults=stmt. execute(“select statement”); if(hasresults){ Result. Set rs=stmt. get. Result. Set( ); String str=rs. get. String(“empnam”); . . }
Databasemeta. Data & Result. Set. Meta. Data interfaces • Database. Meta. Data interface has methods that are used to identify the database configuration • Result. Set. Meta. Data interface has methods that are used to identify the database configuration
- Slides: 23