JDBC 与数据库访问

chenzenan

贡献于2012-03-28

字数:0 关键词: JDBC Java开发

JDBC与数据库访问 Qiuyan Huo Software Engineering Institute qyhuo@mail.xidian.edu.cn Java (SSXDU  2009) JDBC与数据库访问  在Java 程序中如何连接和访问数据库呢?这 就要用到JDBC(Java DataBase Connectivity)技术。它是一项在Java中以 面向对象的方法来连接数据库的技术。  JDBC是为Java语言定义的一个SQL调用级接 口。将Java和JDBC结合起来将使程序员只 需写一遍程序,就可以访问各种类型的数据 库。 Application JDBC Driver 调用 装载 交互 Java (SSXDU  2009) Four ways to access a database Java application JDBC driver manager Native protocol partly Java driver JDBC-ODBC bridge driver ODBC driver Net protocol all Java driver Middleware translates net protocol to native DBMS protocol Native protocol all Java driver DBMS DBMS DBMSDBMS Java (SSXDU  2009) Employee table sample data. Result of selecting distinct Department and Location data from table Employee. Java (SSXDU  2009) JDBC与数据库访问 以下列数据库为例:讲解Java 程序中如何使用 JDBC连接和访问数据库。  假设我们有一个用Access设计的数据库: student.mdb,该库中有一个表,表的名字 是:chengjibiao,如图所示 Java (SSXDU  2009) The four types of Java drivers Type 1 A JDBC-ODBC bridge driver converts JDBC calls into ODBC calls that access the DBMS protocol. The ODBC drivers must be installed on the client machines. Type 2 A native protocol partly Java driver converts JDBC calls into calls in the native DBMS protocol. Since this conversion takes place on the client, some binary code must be installed on the client machine. Type 3 A net protocol all Java driver converts JDBC calls into a net protocol that’s independent of any native DBMS protocol. Then, middleware software running on a server converts the net protocol to the native DBMS protocol. Type 4 A native protocol all Java driver converts JDBC calls into a native DBMS protocol . This conversion takes place on the server side. Java (SSXDU  2009) JDBC与数据库访问  步骤一: – 为了同这个数据库建立连接,首先配置一个 ODBC数据源。 – 使用Windows中 的“ODBC数据源管理器”进 行设置。 Java (SSXDU  2009) JDBC与数据库访问  步骤二:建立JDBC—ODBC桥接器 – 现在你可以这样的直观理解:我们有了一个数据源, 这个数据源就是一个数据库。 – 为了要连接到这个数据库,我们首先要建立一个 JDBC─ODBC桥接器: Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); – 这里,Class是包java.sql中的一个类,该类通 过调用它的静态方法forName就可以建立JDBC- ODBC桥接器。 Java (SSXDU  2009) JDBC与数据库访问  步骤二:建立JDBC—ODBC桥接器 注意:建立桥接器时可能发生异常,因此 捕获这个异常。所以建立桥接器的标准是: try { Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); } catch(ClassNotFoundException e) { //错误处理 } Java (SSXDU  2009) JDBC与数据库访问  步骤三:连接到数据库 – 首先使用包java.sql中的Connection类声明一个 对象,然后再使用类DriverManager调用它的静态 方法getConnection创建这个连接对象: – 建立连接时应捕获SQLException异常, try{ Connection con = DriverManager.getConnection( "jdbc:odbc:redsun", "snow", "ookk "); } catch(SQLException e){ ... } Connection con = DriverManager.getConnection( “jdbc:odbc:datasource”, “name”, “password”); Java (SSXDU  2009) JDBC与数据库访问  步骤四:建立一个SQL语句对象 – 首先使用Statement 声明一个SQL语句对象引用变 量,然后通过刚才创建的连接数据库的对象con调 用方法createStatment() 创建这个SQL语句对象。 try { Statement sql=con.createStatement(); } catch(SQLException e){...} – SQL语句对象可以使用多种方法,执行SQL语句,对 指定的数据库进行操作。例如: 1. SQL语句对象.executeQuery("SELECT * FROM chengjibiao"); – 对指定数据库中的成绩表进行查询 ,将得到成绩表中的所有数据。 2. SQL语句对象.executeUpdata(―DALETE FROM chengjibiao‖); – 对指定数据库中的成绩表中的数据进行删除。 Java (SSXDU  2009) JDBC与数据库访问  步骤五:用ResultSet类声明一个结果集对 象引用变量,存放查询结果。  执行语句: SQL语句对象.executeQuery( "SELECT * FROM chengjibiao"); – 将得到成绩表中的所有数据,这些数据作为结果 集对象返回,故需要赋值给结果集对象引用变量。 ResultSet rs = sql.executeQuery( "SELECT * FROM chengjibiao"); Java (SSXDU  2009) JDBC与数据库访问  步骤六:使用结果集对象的各种方法提取查 询结果。 1. 使用next方法 – 数据库的查询结果以行的形式得到。 ResultSet 对象中含有多行信息,它有一个指示器,指向当 前可操作的行,初始状态下,指示器是指向第一 行之前,方法next的功能是将指示器下移一行, 所以第一次调用next方法时,指示器指向第一行。 以后每一次对next方法的成功调用,都会将指示 器移到下一行。 Java (SSXDU  2009) JDBC与数据库访问  步骤六:使用结果集对象的各种方法,提取查询结果 2. 使用 getXXX方法 – 使用相应类型的getXXX方法,可以从当前行指定列中,提 取不同数据类型的数据。  数据库的表中每一行信息,就是一条记录,由不同数据类型的 字段值组成。要对当前行的某个字段值进行操作,必须确认它 是属于何种数据类型,然后采用对应的getXXX方法。 – 数据库的表中每一列信息,都是相同数据类型的数据。还有 每一列信息,表中都指定了字段名,例如,第一列,字段名 是学号。 例如:String s = rs.getString(“No”); 或:String s = rs.getString(1); int math = rs.getInt(“Math"); int math = rs.getInt(4); Java (SSXDU  2009) 15 1 // DisplayAuthors.java 2 // Displaying the contents of the authors table. 3 import java.sql.Connection; 4 import java.sql.Statement; 5 import java.sql.DriverManager; 6 import java.sql.ResultSet; 7 import java.sql.ResultSetMetaData; 8 import java.sql.SQLException; 9 10 public class DisplayAuthors 11 { 12 // JDBC driver name and database URL 13 static final String DRIVER = "com.mysql.jdbc.Driver"; 14 static final String DATABASE_URL = "jdbc:mysql://localhost/books"; 15 16 // launch the application 17 public static void main( String args[] ) 18 { 19 Connection connection = null; // manages connection 20 Statement statement = null; // query statement 21 ResultSet resultSet = null; // manages results 22 23 // connect to database books and query database 24 try 25 { 26 // load the driver class 27 Class.forName( DRIVER ); 28 Outline DisplayAuthors . java (1 of 3 ) Imports for the JDBC classes and interfaces from package java.sql Declare a String constant that specifies the JDBC driver’s class name Loads the class definition for the database driver. Declare a String constant that specifies the database URL Java (SSXDU  2009) 16 29 // establish connection to database 30 connection = 31 DriverManager.getConnection( DATABASE_URL, "jhtp7", "jhtp7" ); 32 33 // create Statement for querying database 34 statement = connection.createStatement(); 35 36 // query database 37 resultSet = statement.executeQuery( 38 "SELECT authorID, firstName, lastName FROM authors" ); 39 40 // process query results 41 ResultSetMetaData metaData = resultSet.getMetaData(); 42 int numberOfColumns = metaData.getColumnCount(); 43 System.out.println( "Authors Table of Books Database:\n" ); 44 45 for ( int i = 1; i <= numberOfColumns; i++ ) 46 System.out.printf( "%-8s\t", metaData.getColumnName( i ) ); 47 System.out.println(); 48 49 while ( resultSet.next() ) 50 { 51 for ( int i = 1; i <= numberOfColumns; i++ ) 52 System.out.printf( "%-8s\t", resultSet.getObject( i ) ); 53 System.out.println(); 54 } // end while 55 } // end try Outline DisplayAuthors .jav a (2 of 3 ) Invokes Connection method createStatement to obtain an object that implements interface Statement. Use the Statement object’s executeQuery method to execute a query that selects all the author information from table authors. Obtains the metadata for the ResultSet. Uses ResultSetMetaData method getColumnCount to retrieve the number of columns in the ResultSet. Obtain column name using method getColumnN ame Position the ResultSet cursor to the first row in the ResultSet with method next Extract the contents of one column in the current row Initialize a Connection reference called connection. Java (SSXDU  2009) 17 56 catch ( SQLException sqlException ) 57 { 58 sqlException.printStackTrace(); 59 } // end catch 60 catch ( ClassNotFoundException classNotFound ) 61 { 62 classNotFound.printStackTrace(); 63 } // end catch 64 finally // ensure resultSet, statement and connection are closed 65 { 66 try 67 { 68 resultSet.close(); 69 statement.close(); 70 connection.close(); 71 } // end try 72 catch ( Exception exception ) 73 { 74 exception.printStackTrace(); 75 } // end catch 76 } // end finally 77 } // end main 78 } // end class DisplayAuthors Authors Table of Books Database: authorID firstName lastName 1 Harvey Deitel 2 Paul Deitel 3 Andrew Goldberg 4 David Choffnes Outline DisplayAuthors .jav a (3 of 3 ) Catch SQLException, which is thrown if the query execution or ResultSet process failsClassNotFoundExcepti on is thrown if the class loader cannot locate the driver class Close the Statement and the database Connection. Java (SSXDU  2009) SQL Syntax INSERT INTO table ( field1, field2 ) VALUES ( value1, value2 ) – 在表table中插入一条新纪录 UPDATE table SET ( field1 = value1, field2 = value2 ) WHERE condition – 更新现存记录 DELETE FROM table WHERE condition – 删除满足条件condition的记录 SELECT field1, field2 FROM table WHERE condition – 获取所有满足condition的记录 Java (SSXDU  2009) JDBC APIs Java (SSXDU  2009) java.sql包  JDBC的实现都在java.sql包中 – Driver – Connection – Statement – PreparedStatement:优化的Statement – ResultSet – DriverManager –…… Java (SSXDU  2009) Java Types 和 SQL Types SQL type Java Type CHAR, VARCHAR, LONGVARCHAR String NUMERIC, DECIMAL java.math.BigDecimal BIT boolean TINYINT byte SMALLINT short INTEGER int BIGINT long REAL float FLOAT, DOUBLE double BINARY, VARBINARY, LONGVARBINARY byte[] DATE java.sql.Date TIME java.sql.Time TIMESTAMP java.sql.Timestamp Java (SSXDU  2009) 数据库Time  SQL中的时间难以理解  Java定义了三个有助于理解和使用的类 – java.sql.Date  year, month, day – java.sql.Time  hours, minutes, seconds – java.sql.Timestamp  year, month, day, hours, minutes, seconds, nanoseconds(十亿分之一秒)  通常使用这个 Java (SSXDU  2009) 事务管理  Transactions不是显式打开的或者关闭的  而是connection具有一个叫做AutoCommit 模式  如果AutoCommit为true,那么每个语句都会 自动提交  默认值为 true – 如果为false,每个语句都加入当前的事务,必须 显式地调用提交和回滚:Connection.commit() 和 Connection.rollback() Connection.setAutoCommit(boolean); Java (SSXDU  2009) JDBC类图 Whoa! Java (SSXDU  2009) javax.sql包  Provides the API for server side data source access and processing from the JavaTM programming language.  类 – ConnectionEvent  An Event object that provides information about the source of a connection-related event. – RowSetEvent  An Event object generated when an event occurs to a RowSet object. – StatementEvent (Java SE6.0)  接口 – ConnectionPoolDataSource – DataSource – PooledConnection –… Java (SSXDU  2009)Interface Summary ConnectionEventListener An object that registers to be notified of events generated by a PooledConnection object. ConnectionPoolDataSource A factory for PooledConnection objects. DataSource A factory for connections to the physical data source that this DataSource object represents. PooledConnection An object that provides hooks for connection pool management. RowSet The interface that adds support to the JDBC API for the JavaBeansTM component model. RowSetInternal The interface that a RowSet object implements in order to present itself to a RowSetReader or RowSetWriter object. RowSetListener An interface that must be implemented by a component that wants to be notified when a significant event happens in the life of a RowSet object. RowSetMetaData An object that contains information about the columns in a RowSet object. RowSetReader The facility that a disconnected RowSet object calls on to populate itself with rows of data. RowSetWriter An object that implements the RowSetWriter interface, called a writer. XAConnection An object that provides support for distributed transactions. XADataSource A factory for XAConnection objects that is used internally.

下载文档,方便阅读与编辑

文档的实际排版效果,会与网站的显示效果略有不同!!

需要 10 金币 [ 分享文档获得金币 ] 3 人已下载

下载文档

相关文档