JDBC学习笔记.doc

JDBCSD0601jdbc2.0jdbc1.0jdbc2.0JNDIDataSourceJDBCJDBCl. 1) ODBC(Open Database Connectivity)CSQL2) JDBCJavaODBC2. JDBCJDBC1) ;2) JAVAJDBC1) ;2) SQL;3) JDBC1) JAVAJAVA;2) JDBC3. JDBC Driver1) JDBC;3) java.sql.Driver4. JDBC Driver1) JDBC-ODBCODBCJDBCJDBCZODBC4) Native BridgeJavaAPI5) pure javaJava driverJDBCDBMSDBMS6) java driverJDBCDBMS5. JDBC1) java.sql-java 2JDBC(J2SE)7) javax.sql-java 2JDBC(J2EE)6. URLURL(Driver,);protocol:subprotocol:subnamejdbc:db2:MyTestjdbc:db2:/localhost:6789/MyTest-oracle-oracle.jdbc.driver.OracleDriverURLjdbc:oracle:thin:dbnamemachine_nameIPport1521-mysql-org.gjt.mm.mysql.DriverURLjdbc:mysql:/dbnamemachine_nameIPport3306-pointbase-com.pointbase.jdbc.jdbcUniversalDriverURLjdbc:pointbase:server:/dbnamemachine_nameIPport9092-SQL Server-com.microsoft.jdbc.sqlserver.SQLServerDriverURLjdbc:microsoft:sqlserver:/;DatabaseName=machine_nameIPport1433-DB2-com.ibm.db2.jdbc.app.DB2DriverURLjdbc:db2:/dbnamemachine_nameIPport50007. javax.sqlJDBC2.01) ;8) ;9) ;10) ;JDBC1JDBC1) driver;driver; JDBCURLdriver; driverdriver loader(;DriverManager)Class.forName(oracle.jdbc.driver.OracleDriver);a. Class.forName(Com.ibm.db2.jdbc.app.DB2Driver);b. Class.forName(C.DB2Driver);c. Class.forName(Com.microsoft.jdbc.sqlServer.SQLServerDriver);d. Class.forName(oracl.jdbc.driver.OracleDriver);e. Class.forName(sun.jdbc.odbc.JdbcOdbcDriver);Drivera. Driver drv = new COM.cloudscape.core.RmiJdbcDriver();2) ;DriverManagergetConnection(urlString)driverconnect(urlString);Connection con= DriverManager.getConnection(jdbc:oracle:thin:6:1521:tarenadb,tarena,tarena); driverURLDriverManager; drivernulldriver; SQLExcepitondriverconnectDriver; Driver drv = new COM.ibm.db2.jdbc.app.DB2Driver();/ Driver drv = new oracle.jdbc.dri;Connection con = null;try con = drv.connect(jdbc:db2:MyTest,new Properties()catch(SQLException e)3statement; Statement:a. Statement;b. PreparedStatement(Statement);c. CallableStatement(PreparedStatement);Connection.createStatement()StatementPreparedStatementProparedStatementstatement;Statement;PreparedStatement pstm = connection.prepareStatement(sqlString);CallableStatement CallableStatement; PreparedStatement; CallableStatement cstm = connection.prepareCall(call return_student?,?);cstm.setString(1,8623034);cstm.registerOutparameter(2, Types.REAL);cstm.execute();float gpa = cstm.getFloat(2);4SQL;SQLajava.sql.ResultSet Statementa. executeQuery(sqlString): SQL(ResultSet); ResultSet rs = stm.executeQuery(select name,gender,age,gpa from +TABLE_NAMEb. executeUpdate(sqlString): SQLINSERTUPDATEDELETESQL DDL;c. execute(sqlString): SQL5 ;(ResultSet);a. next():b. first():c. last():d. previous():while(rs.next()System.out.print(name: + rs.getString(1);System.out.print(tgender: + rs.getString(2);System.out.print(tage: + rs.getInt(3);System.out.println(tgpa: + rs.getFloat(4);rs.getString()rs.getString()6JDBC; ; public void close(ResultSet rs,Statement stm,Connection con)if(rs!=null)tryrs.close();catch(Exception me)if(stm!=null)trystm.close()catch(Exception me)if(con!=null)trycon.close()catch(Exception me)2. JDBC URL1) JDBC-ODBC: jdbc:odbc:2) Oracle: jdbc:oracle:oci: or jdbc:oracle:thin:3) Weblogic MS-SQL: jdbc:weblogic:mssqlserver4:4) DB2: jdbc:db2:MyTest or jdbc.db2:/localhost:6789/MyTest()3.Statement1) StatementSQLSQLa,StatementStatement stm=con.createStatement();Statement stm=con.createStatement(int resultSetType, int resultSetConcurrency); / StatementresultSetTypeResultSet.TYPE_FORWARD_ONLY /ResultSet.TYPE_SCROLL_INSENSITIVE /ResultSet.TYPE_SCROLL_SENSITIVE /resultSetConcurrency ResultSet.CONCUR_READ_ONLY /ResultSet.CONCUR_UPDATABLE /b,ResultSet rs = stm.executeQuery(select name,gender,age,gpa from student0506);()int i = stm.executeUpdate(insert into student0506 values (tony,male,20,89);boolean b = stm.execute(drop table student0506);2) PreparedStatementStatementSQL aPreparedStatementPreparedStatement pstm=con.prepareStatement(insert into student0506 values(?,?,?,?);con.prepareStatement()2bSQL,SQLpstm.setString(1,tony);pstm.setString(2,male);pstm.setInt(3,20);pstm.setFloat(4,89.5f);pstm.executeUpdate();3) CallableStatement, PreparedStatement,SQLSQLSQLa,CallableStatementCallableStatement cs=con.prepareCall(String sql)sql?CallableStatement cstm=con.prepareCall(call return_student)cstm.seString(1,8613034);cstm.registerOutParameter(2,Types.REAL);/REALcstm.execute();float gpa=cstm.getFloat(2);return_student(String str,float gpa)select gpa from Student where id=str4. StatementStatementPreparedStatementCallableStatementJavaSQLJavaSQLPL/SQLPreparedStatement5. 1) 2);3) 46. 1) 2) 3)7. SQLJavaSQL Java -INTEGERINT int 32SMALLINT short 16NUMBER(m,n) DECIMAL(m,n) Java.sql.Numeric mnDEC(m,n) Java.sql.Numeric mnFLOAT(n) double nREAL float 32DOUBLE double 64CHARACTER(n)CHAR(n) String nVARCHAR(n) String nBOOLEAN boolean DATE Java.sql.Date TIME Java.sql.Time TIMESTAMP Java.sql.Timestamp BLOB Java.sql.Blob CLOB Java.sql.Clob ARRAY Java.sql.ArrayJDBC1. JDBC API1) ;a. connection.getMetadataDataMetaDatab. DataMetaData.isReadOnly2) ;a. ResultSet.getMetadataResultSetMetaDatab. getColumnCount2.1) sql;2) JDBC APIa. falseConnection.setAutoCommit;b. sql;c. Cmit;d. Connection.rollbacktrycon.setAutoCommit(false);Statement stm = con.createStatement();stm.executeUpdate(insert into student(name, age, gpa) values(gzhu, 30, 4.8);mit();catch(SQLException e)trycon.rollback();catch(Exception e) 3.1) Connection setTransactionIsolation()2Dirty readsABANon-repeatable readsABAPlantom readsABA3) a. TRANSACTION_NONE;b. TRANSACTION_READ_UNCOMMITTED;c. TRANSACTION_READ_COMMITTED;d. TRANSACTION_REPEATABLE_READ;e. TRANSACTION_SERIALIZABLE3)con.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);con.getTransactionIsolationJDBC2.01. JDBC 2.0 1) ResultSeta. ;ResultSetnextb. ResuleSet;stm = con.createStatementint ResultSetType,int ResultSetConcurrency);con.prePareStatementsqlint ResultSetType,int ResultSetConcurrency);con. prePareCallsqlint ResultSetType,int ResultSetConcurrency);ResultSetType ResultSet.TYPE_FORWARD_ONLY; ()ResultSet.TYPE_SCROLL_INSESITIVE ;ResultSet.TYPE_SCROLL_SESITIVE ResultSetConcurrency ResultSet.CONCUR-READ-ONLYResultSet.CONCUR-UPDATEABLEresultsetcreateStatementsqlselect t.*, t.rowid from t_user tselect * from t_user for updaters.updateString19113015rs.updateFloatgpa4.8rs.updateRow/rs.absolute10/rs.delectRow();/rs.movetoInsertRow/rs.updateString19113015rs.updateFloatgpa4.8rs.insertRowCONCUR_READ_ONLY CONCUR_UPDATABLEcommitTYPE_FORWARD_ONLY next()TYPE_SCROLL_INSENSITIVEabsolute()ATYPE_SCROLL_INSENSITIVEATYPE_SCROLL_SENSITIVEabsolute()ATYPE_SCROLL_SENSITIVEAHOLD_CURSORS_OVER_COMMIT commitCLOSE_CURSORS_AT_COMMITcommit/FETCH_FORWARDFETCH_REVERSEFETCH_UNKNOWN2) Statement(batch updates);sqlStatement stm=con.createStatement();Stm.addBatch(insert into student(name,age,gpa) values (gzu,30,4.9);/addBatchsqlStm.addBatch(insert into student(name,age,gpa) values (wang,40,4.5);int results=st.executeBatch();/mit();3) (Blob,Clob,Array SQL)BLOB CLOBArrayblob clob :1) .BLOB:insert into tbl_blob(id,fileName,blobData) values(?,?,empty_blob()2) .BLOB:select blobData from tbl_blob where fileName= ? for update3) .,BLOB OutputStream out = blob.getBinaryOutputStream();4) .BLOBoracleupdate tbl_blob set blobData=? where fileName=?pstmt.setBlob(1,blob);pstmt.setString(2,args0);pstmt.executeUpdate();1) SELECT blobData +FROM tbl_blob +WHERE fileName = ?2) Blob b = rs.getBlob(1);3) InputStream in=b.getBinaryStream();6,JDBC 2.0(JNDI)1) JNDI(Java Naming and Directory Interface): Object,DataSource;4) DataSource:JNDI5) ConnectionPoolContext cyx=new InitialContext();/InitialContext()JNDItomcatDataSource ds=(DataSource)ctx.lookup(jdbc/my_oracle);Connection con=ds.getConnection(userid,userpasswd);8,RowSet(rowset )RowSet ResultSet 1) a. Register jdbc driversb. Create database connection through DriverManagerc. Prepare statementd. Execute statemente. = RowSet =CachedRo