ORACLE数据库连接.docx
- 文档编号:10774137
- 上传时间:2023-02-22
- 格式:DOCX
- 页数:16
- 大小:178.72KB
ORACLE数据库连接.docx
《ORACLE数据库连接.docx》由会员分享,可在线阅读,更多相关《ORACLE数据库连接.docx(16页珍藏版)》请在冰豆网上搜索。
ORACLE数据库连接
ORACLE数据库连接([5]62-78/347-511/1009-1040、[2]565-593)
典型应用结构(C/S和B/S)
客户机
(安装驱动)
浏览器
(下载驱动)
WEB服务器
(安装驱动)
应用服务器
(安装驱动)
DB服务器
(安装驱动)
客户机
(安装驱动)
(C/S)
ODBC
JDBC
OLEDB
浏览器
(下载驱动)
(JDBCThinDriver)
(B/S)
(B/S)
ODBC
JDBC
OLEDB
WEB服务器
(安装驱动)
(B/S)
(HTTP)
(C/S)
ODBC
JDBC
OLEDB
应用服务器
(安装驱动)
(C/S)
(B/S)
(C/S)
(C/S)
ODBC
JDBC
OLEDB
DB服务器
(安装驱动)
ODBC
JDBC
OLEDB
ODBC
JDBC
OLEDB
ODBC
JDBC
OLEDB
ODBC
JDBC
OLEDB
ODBC
JDBC
OLEDB
数据库连接
ODBC连接
VSAM/ISAM:
虚拟顺序存取法/索引顺序存取法
OLEDB连接
ADO/OLEDB连接
连接软件层次结构
●ADO(AxtiveXDataObjects)Microsoft驱动程序的关键组件,可用于访问存储在任意地方的数据(如Oracle、SQLServer、文本文件、Exchange、LDAP等)
●OO4O(OracleObjectsforOLE)Oracle的一个专用COM层,VB和C++可以用OO4O与Oracle通信,提供类似ADO的功能
●ADO是Microsoft许多数据访问策略的修订和合并的结果,是DAO(DataAccessObjects)和RDO(RemoteDataObjects)的继承者。
ADO与OLEDB之间的关系类似RDO与ODBC之间的关系,RDO是ODBCAPI上的瘦包装器
●ADO、OLEDB和ODBC现在组装在单一的可安装组件内,即MDAC(MicrosoftDataAccessComponents),目前版本为ADO2.6(参考:
ADO2.6ProgrammersReference,Wrox,ISBN1-861004-63-x)
OverviewoftheOracleJDBCDrivers
∙Thindriver,a100%Javadriverforclient-sideusewithoutanOracleinstallation,particularlywithapplets
∙OCIdrivers(OCI8andOCI7)forclient-sideusewithanOracleclientinstallation
∙Server-sideThindriver,whichisfunctionallythesameastheclient-sideThindriver,butisforcodethatrunsinsideanOracleserverandneedstoaccessaremoteserver,includingmiddle-tierscenarios
∙Server-sideinternaldriverforcodethatrunsinsidethetargetserver(thatis,insidetheOracleserverthatitmustaccess)
Driver-DatabaseArchitecture
CommonFeaturesofOracleJDBCDrivers
Theserver-sideandclient-sideOracleJDBCdriversprovidethesamebasicfunctionality.Theyallsupportthefollowingstandardsandfeatures:
∙eitherJDK1.2.x/JDBC2.0orJDK1.1.x/JDBC1.22(withOracleextensionsforJDBC2.0functionality)
∙thesamesyntaxandAPIs
∙thesameOracleextensions
∙fullsupportformulti-threadedapplications
Notes:
∙Theserver-sideinternaldriversupportsonlyJDK1.2.x.
∙MostJDBC2.0functionality,includingthatforobjects,arrays,andLOBs,isavailableinaJDK1.1.xenvironmentthroughOracleextensions.
∙Startingwithrelease8.1.6,JDK1.0.2isnolongersupported.
JDBCThinDriver
TheOracleJDBCThindriverisa100%pureJava,TypeIVdriver.ItistargetedforOracleJDBCappletsbutcanbeusedforapplicationsaswell.
ForappletsitcanbedownloadedintoabrowseralongwiththeJavaappletbeingrun.TheHTTPprotocolisstateless,buttheThindriverisnot.TheinitialHTTPrequesttodownloadtheappletandtheThindriverisstateless.OncetheThindriverestablishesthedatabaseconnection,thecommunicationbetweenthebrowserandthedatabaseisstatefulandinatwo-tierconfiguration.
TheJDBCThindriverallowsadirectconnectiontothedatabasebyprovidinganimplementationofTCP/IPthatemulatesNet8andTTC(TwoTaskCommon:
thewireprotocolusedbyOCI)ontopofJavasockets.Bothoftheseprotocolsarelightweightimplementationversionsoftheircounterpartsontheserver.TheNet8protocolrunsoverTCP/IPonly.
ThedriversupportsonlyTCP/IPprotocolandrequiresaTNSlistenerontheTCP/IPsocketsfromthedatabaseserver.
Note:
WhentheJDBCThindriverisusedwithanapplet,theclientbrowsermusthavethecapabilitytosupportJavasockets.
JDBCOCIDrivers
TheJDBCOCIdrivers(OCI8forOracle8/8iandOCI7forOracle7)areTypeIIdriverstargetedforclient-serverJavaapplicationsprogrammers.TheyrequireanOracleclientinstallation,soareOracleplatform-specificandarenotsuitableforapplets.
TheOCIdrivers,writteninacombinationofJavaandC,convertJDBCinvocationstocallstotheOracleCallInterface(OCI),usingnativemethodstocallCentrypoints.ThesecallsarethensentoverNet8totheOracledatabaseserver.
ThesedriversusetheOCIlibraries,C-entrypoints,Net8,CORElibraries,andothernecessaryfilesontheclientmachineonwhichtheyareinstalled.
TheOCIdriversprovidethehighestcompatibilitywiththedifferentOracle7,8,and8iversions.TheyalsosupportallinstalledNet8adapters,includingIPC,namedpipes,TCP/IP,andIPX/SPX.
JDBCServer-SideThinDriver
TheOracleJDBCserver-sideThindriveroffersthesamefunctionalityastheclient-sideThindriver,butrunsinsideanOracledatabaseandaccessesaremotedatabase.
Thisisespeciallyusefulintwosituations:
∙toaccessaremoteOracleserverfromanOracleserveractingasamiddletier
∙moregenerally,toaccessoneOracleserverfrominsideanother,suchasfromanyJavastoredprocedureorEnterpriseJavaBean
ThereisnodifferenceinyourcodebetweenusingtheThindriverfromaclientapplicationorfrominsideaserver.
Note:
Statementcancel()andsetQueryTimeout()methodsarenotsupportedbytheserver-sideThindriver.
JDBCServer-SideInternalDriver
TheOracleJDBCserver-sideinternaldriversupportsanyJavacodethatrunsinsideanOracledatabase,suchasinaJavastoredproceduresorEnterpriseJavaBean,andmustaccessthesamedatabase.ThisdriverallowstheJavavirtualmachine(JVM)tocommunicatedirectlywiththeSQLengine.
Theserver-sideinternaldriver,theJVM,thedatabase,theKPRB(server-side)Clibrary,andtheSQLengineallrunwithinthesameaddressspace,sotheissueofnetworkroundtripsisirrelevant.TheprogramsaccesstheSQLenginebyusingfunctioncalls.
ChoosingtheAppropriateDriver
ConsiderthefollowingwhenchoosingwhichJDBCdrivertouseforyourapplicationorapplet:
∙Ifyouarewritinganapplet,youmustusetheJDBCThindriver.JDBCOCI-baseddriverclasseswillnotworkinsideaWebbrowser,becausetheycallnative(Clanguage)methods.
∙Ifyouwantmaximumportability,thenchoosetheJDBCThindriver.YoucanconnecttoanOracleserverfromeitheranapplicationoranappletusingtheJDBCThindriver.
∙IfyouarewritingaclientapplicationforanOracleclientenvironmentandneedmaximumperformance,thenchoosetheJDBCOCIdriver.
∙ForcodethatrunsinanOracleserveractingasamiddletier,usetheserver-sideThindriver.
∙IfyourcodewillruninsidethetargetOracleserver,thenusetheJDBCserver-sideinternaldrivertoaccessthatserver.(Youcanalsoaccessremoteserversusingtheserver-sideThindriver.)
应用编程举例(参考:
[5]12/13/14/25章,在线文档)
主机/终端模式(Pro*C)
例:
sample1.pc
#include
#include
/*DefineconstantsforVARCHARlengths.*/
#defineUNAME_LEN20
#definePWD_LEN40
/*Declarevariables.*/
VARCHARusername[UNAME_LEN];
varcharpassword[PWD_LEN];
/*DefineahostvariablesfortheoutputvaluesofaSELECTstatement.*/
VARCHARemp_name[UNAME_LEN];
floatsalary;
floatcommission;
/*Inputhostvariable.*/
intemp_number;
/*IncludetheSQLCommunicationsArea.*/
#include
/*Declareerrorhandlingfunction.*/
voidsql_error();
main(){
chartemp_char[32];
/*CopytheusernameintotheVARCHAR.*/
strncpy((char*)username.arr,"SCOTT",UNAME_LEN);
username.len=strlen((char*)username.arr);
/*CopythepasswordintotheVARCHAR.*/
strncpy((char*)password.arr,"TIGER",PWD_LEN);
password.len=strlen((char*)password.arr);
/*Registersql_error()astheerrorhandler.*/
EXECSQLWHENEVERSQLERRORDOsql_error("ORACLEerror--\n");
/*ConnecttoORACLE.*/
EXECSQLCONNECT:
usernameIDENTIFIEDBY:
password;
printf("\nConnectedtoORACLEasuser:
%s\n",username.arr);
/*Loop,selectingindividualemployee'sresults*/
for(;;)
{
EXECSQLWHENEVERNOTFOUNDDObreak;
for(;;)
{
emp_number=0;
printf("\nEnteremployeenumber(0toquit):
");
gets(temp_char);
emp_number=atoi(temp_char);
if(emp_number==0)
break;
EXECSQLSELECTename,sal,NVL(comm,0)
INTO:
emp_name,:
salary,:
commission
FROMEMP
WHEREEMPNO=:
emp_number;
/*其它处理*/
}
if(emp_number==0)break;
printf("\nNotavalidemployeenumber-tryagain.\n");
}
/*DisconnectfromORACLE.*/
EXECSQLCOMMITWORKRELEASE;
exit(0);
}
voidsql_error(msg)
char*msg;
{
charerr_msg[128];
intbuf_len,msg_len;
EXECSQLWHENEVERSQLERRORCONTINUE;
printf("\n%s\n",msg);
buf_len=sizeof(err_msg);
sqlglm(err_msg,&buf_len,&msg_len);
printf("%.*s\n",msg_len,err_msg);
EXECSQLROLLBACKRELEASE;
exit
(1);
}
客户机/服务器模式(Delphi)
Datasource配置(手工配置、程序配置)
Datasource使用(集中于数据模块、分散于功能模块;使用别名)
客户/服务器和浏览器/服务器模式(JDBC:
四种驱动器)
例:
SimpleStatement
packagejdbc.SimpleStatement;
importjava.sql.*;
importDriverData;
/*Aclasstodemonstratetheuseofanon-scrollable,
*non-updateableStatement.Thiscodewillcompileundereither
*JDBC1.0orJDBC2.0
*/
publicclassSimpleStatement{
Connectionconnection;
Statementstatement;
ResultSetresultSet;
//constructor
SimpleStatement()throwsClassNotFoundException,SQLException{
Class.forName("oracle.jdbc.driver.OracleDriver");
connection=
DriverManager.getConnection(DriverData.url,DriverData.user,DriverData.passw);
statement=connection.createStatement();
}
/**UsetheexecuteUpdatemethodtocreatetwotables*/
voidcreateTable()throwsSQLException{
Stringsql="createtablepatients("+
"patient_idnumber(9)notnullprimarykey,"+
"surnamevarchar(50),"+
"given_namevarchar(50)"+
")";
introws=statement.executeUpdate(sql);
//returnvaluefroma'createtable'shouldbezero
System.out.println("createtablestatementreturnvalueis"+rows);
System.out.println("Tablepatientscreated");
sql="createtablehospital_stays("+
"patient_idreferencespatients(patient_id),"+
"admit_datedate,"+
"discharge_datedate"+
")";
rows=statement.executeUpdate(sql);
System.out.p
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- ORACLE 数据库连接