database
DESCRIPTION
TRANSCRIPT
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
DB2 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(field1,field2..) values (value1,value2...)
Delete from table where fieldname=value
Remote Database Access
UserDatabase
ClientAPI Database
ServerDatabase
VC++,VB
Java programs
ODBC
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
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.
JDBC Driver Types
Database Client
JDBC ODBC Bridge
OracleServer
DB2
SQL Server
ODBC
ODBC
ODBC
JDBC
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 , DB2 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.
DatabaseClient
Native partly Javadriver
DatabaseServer
Vendor specific
protocol
3. JDBC-Net pure Java driver: This driver translates JDBC calls into a DBMS- independent 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.
Database access server
Pure Java
driver
DatabaseClient
OracleServer
DB2 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.
DatabaseClient
Pure Javadriver
DatabaseServer
Vendor specific
protocol
•The acronym for JDBC does not exactly stands for Java Database Connectivity but is referred by that name
JDBC
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 = DriverManager.getConnection ( "jdbc:odbc:wombat", "login", "password"); Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery("SELECT a, b, c FROM Table1"); while (rs.next()) { int x = rs.getInt("a"); String s = rs.getString("b"); float f = rs.getFloat("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 DriverManager 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 database1. Loading the Driver
Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);
2. Constructing URL for the database.
String url=“jdbc:odbc:dsn”;
3. Getting the Connection.
Connection con=DriverManager.getConnection(url,userid,pwd);
4. Creating the statement
Statement stmt=con.createStatement();
5. Execute the corresponding SQL statements.
ResultSet rs=stmt.executeQuery(“select * from Table where empno=5”);
6. Reading the values from the Resultset.
while(rs.next())
{
String s=rs.getString(“empname”);
double sal=rs.getDouble(“empsal”);
}
execute(),executeUpdate(),executeQuery
executeQuery()--- used to execute only SQL query statements
executeUpdate()--- 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){
ResultSet rs=stmt.getResultSet( );
String str=rs.getString(“empnam”);
.
.
}
DatabasemetaData & ResultSetMetaData interfaces
• DatabaseMetaData interface has methods that are used to identify the database configuration
• ResultSetMetaData interface has methods that are used to identify the database configuration