Java课件 第24讲 JDBC编程1

2005JulyJ2EE-JDBCJDBC:PartI2005JulyJ2EE-JDBCRoadMap•IntroductiontoJDBC/JDBCDrivers•Overview:SixStepstousingJDBC•Example1:SettingupTablesviaJDBC•Example2:InsertingDataviaJDBC•Example3:QueryingDataviaJDBC•ExceptionHandlingOverview2005JulyJ2EE-JDBCIntroductiontoJDBCandJDBCDrivers2005JulyJ2EE-JDBCIntroductiontoJDBC•JDBCisasimpleAPIforconnectingfromJavaapplicationstomultipledatabases.•Letsyousmoothlytranslatebetweentheworldofthedatabase,andtheworldoftheJavaapplication.•TheideaofauniversaldatabaseaccessAPIisnotanewone.–Forexample,OpenDatabaseConnectivity(ODBC)wasdevelopedtocreateasinglestandardfordatabaseaccessintheWindowsenvironment.•JDBCAPIaimstobeassimpleaspossiblewhileprovidingdeveloperswithmaximumflexibility.2005JulyJ2EE-JDBCUnderstandingJDBCDrivers•Toconnecttoadatabase–,youfirstneedaJDBCDriver.•JDBCDriver:setofclassesthatinterfacewithaspecificdatabaseengine.JavaApplicationJDBCDriverManagerJDBC-ODBCBridgeVendorSpecificJDBCDriverVendorSpecificoDBCDriverDatabaseDatabaseDiagramSource:MartyHall,CoreWebProgramming(PrenticeHall.)2005JulyJ2EE-JDBCJDBCDrivers•JDBCdriversexistforeverymajordatabaseincluding:–Oracle,SQLServer,Sybase,andMySQL.•ForSQLServer,–Goto•TousetheSQLServerDriver,AddthefollowingJARtoyourCLASSPATHmsbase.jar;mssqlserver.jar;msutil.jar2005JulyJ2EE-JDBCInstallingtheSQLServerDriverWitheclipse:Propertiesforproject_namelibrariesAddJARs.2005JulyJ2EE-JDBCOverview:SixStepstoUsingJDBC2005JulyJ2EE-JDBCSixStepstoUsingJDBC1.LoadtheJDBCDriver2.EstablishtheDatabaseConnection3.CreateaStatementObject4.ExecuteaQuery5.ProcesstheResults6.ClosetheConnection2005JulyJ2EE-JDBC1)LoadingtheJDBCDriver•TouseaJDBCdriver,–youmustloadthedriverviatheClass.forName()method.•TouseOracleandloadjdbcdriver:–Class.forName(”oracle.jdbc.driver.OracleDriver);•IfyouareusingSUNJDBC-ODBCDriver,yourcodewilllooklikethis:Class.forName(sun.jdbc.odbc.JdbcOdbcDriver);2005JulyJ2EE-JDBCLoadingtheSQLServerDrivertry{Class.forName(com.microsoft.jdbc.sqlserver.SQLServerDriver);}catch(java.lang.ClassNotFoundExceptione){System.err.println(e.getMessage());}//connection-urljdbc:oracle:thin:@youroraclehost:1521:yoursid/connection-url•Class.forName()willthrowaClassNotFoundExceptionifyourCLASSPATHisnotsetupproperly.•Hence,it'sagoodideatosurroundtheforName()withatry/catchblock.2005JulyJ2EE-JDBC2)EstablishtheConnection•OnceyouhaveloadedyourJDBCdriver,thenextstepistoestablishadatabaseconnection.•Thefollowinglineofcodeillustratesthebasicidea:Connectioncon=DriverManager.getConnection(url);2005JulyJ2EE-JDBC2)EstablishtheConnection•Ingeneral,theURLhasthefollowingformat.Stringurl=“jdbc:mysql://host:port/database”;Connectioncon=DriverManager.getConnection(url,“username”,“password”);OrStringurl=“jdbc:mysql://localhost:3306/myDB?user=soft&password=soft1234&useUnicode=true&characterEncoding=8859_1”;Connectioncon=DriverManager.getConnection(url);2005JulyJ2EE-JDBCConnectionURL:SQLServerStringurl=“jdbc:microsoft:sqlserver://localhost:1433;User=nlc;Password=nlc;DatabaseName=nlc”;Connectioncon=DriverManager.getConnection(url);•jdbc:oracle:thin:@youroraclehost:1521:yoursid•oracle.jdbc.driver.OracleDriver2005JulyJ2EE-JDBCConnectionURL:OracleStringurl=“jdbc:oracle:thin://localhost:1521:brs”;Connectioncon=DriverManager.getConnection(url,username,password);•WeareusingtheOracleJDBCDriver•Ifthiscodeexecutessuccessfully,wewillhaveaConnectionobjectforcommunicatingdirectlywiththedatabase.2005JulyJ2EE-JDBC3)CreateaStatementObject•TheJDBCStatementobjectsendsSQLstatementstothedatabase.•StatementobjectsarecreatedfromactiveConnectionobjects.•Forexample:–Statementstmt=con.createStatement();•WithaStatementobject,youcanissueSQLcallsdirectlytothedatabase.2005JulyJ2EE-JDBC4)ExecuteaQuery•executeQuery()ExecutestheSQLqueryandreturnsthedatainatable(ResultSet)TheresultingtablemaybeemptybutnevernullResultSetresults=statement.executeQuery(SELECTa,bFROMtable);•executeUpdate()UsedtoexecuteforINSERT,UPDATE,orDELETESQLstatementsThereturnisthenumberofrowsthatwereaffectedinthedatabaseSupportsDataDefinitionLanguage(DDL)statementsCREATETABLE,DROPTABLEandALTERTABLE2005JulyJ2EE-JDBCUsefulStatementMethods•getMaxRows/setMaxRows–DeterminesthenumberofrowsaResultSetmaycontain–Unlessexplicitlyset,thenumberofrowsareunlimited(returnvalueof0)•getQueryTimeout/setQueryTimeout–SpecifiestheamountofatimeadriverwillwaitforaSTATEMENTtocompletebeforethrowingaSQLException2005JulyJ2EE-JDBC5)ProcesstheResults•AResultSetcontainstheresultsoftheSQLquery.•UsefulMethods(AllmethodscanthrowaSQLException)–close()•ReleasestheJDBCanddatabaseresources•TheresultsetisautomaticallyclosedwhentheassociatedStatementobjectexecutesanewquery.2005JulyJ2EE-JDBCResultSet(Continued)•UsefulMethods–next()•AttemptstomovetothenextrowintheResultSet–Ifsuccessfultrueisreturned;–otherwise,false–Thefirstcalltonextpositionsthecursorathefirstrow2005JulyJ2EE-JDBCResultSet(Continued)•UsefulMethods–getMetaDataObject()•ReturnsaResultSetMetaDataobjectcontaininginformationaboutthecolumnsi