WEB应用及开发-10JDBC及数据库(JDBC及SQL语句、存储过程应用)

第十讲课题JDBC与数据库（JDBC与SQL语句、存储过程的应用）目的要求掌握JDBC存取各种不同数据库的方法教学重点掌握JDBC与不同数据库的连接字符串、连接池教学难点JSP如何存取存储过程中输入参数、输出参数、输入输出参数教学课时4（含2课时上机）教学方法讲解、示例与启发式教学相结合教学内容和步骤10.1JDBC与数据库编程10.1.1JDBC的Driver可分为以下四种类型

(1)JDBC-ODBCBridge和ODBCDriver这种驱动器器通过ODBC驱动器提供数据库连接。使用这种驱动器，要求每一台客户机都装入ODBC的驱动器。

(2)Native-APIpartly-JavaDriver这种驱动器将JDBC指令转化成所连接使用的DBMS的操作形式。各客户机使用的数据库可能是Oracle，可能是Sybase，也可能是Access，都需要在客户机上装有相应DBMS的驱动程序。

(3)JDBC-NetAll-JavaDriver这种驱动器将JDBC指令转化成独立于DBMS的网络协议形式，再由服务器转化为特定DBMS的协议形式。有关DBMS的协议由各数据库厂商决定。这种驱动器可以联接到不同的数据库上，最为灵活。目前一些厂商已经开始添加JDBC的这种驱动器到他们已有的数据库中介产品中。要注意的是，为了支持广域网存取，需要增加有关安全性的措施，如防火墙等等。

(4)Native-protocolAll-JavaDriver这种驱动器将JDBC指令转化成网络协议后不再转换，由DBMS直接使用。相当于客户机直接与服务器联系，对局域网适用。在这四种驱动器中，后两类“纯Java”(All-Java)的驱动器效率更高，也更具有通用性。但目前第一、第二类驱动器比较容易获得，使用也较普遍。10.1.2关于JDBCUrl语法如下：

jdbc:<子协议>:<子名称><子协议>驱动程序名称或数据库连接机制，子协议名称的典型例子就是ODBC,如：jdbc:odbc:fff<子名称>一种标识数据库的方法。子名称可以依据不同的子协议而发生变化如：jdbc:dbnet//womat:356//fffODBC子协议:Jdbc:odbc:<数据源名称>[；<属性名>=<属性值>]如：

jdbc:odbc:sqlconn:UID=sa;PWD=ww10.2JDBC编程10.2.1程序基本结构一般的JDBC程序都完成三项功能：与数据库建立连接；传送SQL声明以及对返回结果进行处理。下面我们通过一个具体例子说明这三项功能的实现过程。例1:Creage.java给出了一个简单的JDBC程序，此程序执行后创建一张名为testTable的表，表中包括两个域，域名分别为id和name。import.URL;importjava.sql.*;classCreate{publicstaticvoidmain(String[]args){Stringurl="jdbc:odbc:demo";Stringquery="CREATETABLEtestTable"+"(idINT,nameCHAR(10))";try{//下载jdbc-odbcbridge驱动器

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");//关于此句参见下面Cyclone的注释//与驱动器建立连接,这里可使用不同的特定驱动，如jdbcfororacle或jdbcforsqlserverConnectioncon=DriverManager.getConnection(url,"user","password");//创建一个Statement对象Statementstmt=con.createStatement();//执行SQL声明stmt.executeUpdate(query);System.out.println("Createsuccessfully!");//关闭stmstmt.close();//关闭连接con.close();}catch(SQLExceptionex){//SQL异常信息System.out.println("\n***SQLExceptioncaught***\n");while(ex!=null){System.out.println("SQLState:"+ex.getSQLState());System.out.println("Message:"+ex.getMessage());System.out.println("Vendor:"+ex.getErrorCode());ex=ex.getNextException();System.out.println("");}}catch(java.lang.Exceptionex){ex.printStackTrace();}}}2．2Statement类及其子类1)Statement接口Statementstmt=con.createStatement();//执行SQL声明intcount1=stmt.executeUpdate("INSERTINTOtestTable(id,name)VALUES(1,'wu')");intcount2=stmt.executeUpdate("INSERTINTOtestTable(id,name)VALUES(2,'wang')");2）PreparedStatement接口Stringdata[][]={{"5","xu"},{"6","yan"}};PreparedStatement

pstmt=con.prepareStatement("INSERTINTOtestTable(id,name)VALUES(?,?)");//参数赋值，执行SQL声明for(inti=0;i<data.length;i++){pstmt.setInt(1,Integer.parseInt(data[i][0]));pstmt.setString(2,data[i][1]);

pstmt.executeUpdate();}3）CallableStatement接口

CallableStatement

cstmt=con.prepareCall("{callSearch(?)}");//参数赋值cstmt.setInt(1,934678);//执行储存过程。cstmt.execute();2.3结果集ResultSet及ResultSetMetaDataStringquery="SELECT*FROMtestTable";Statementstmt=con.createStatement();//发出查询要求，获得结果集ResultSet

rs=stmt.executeQuery(query);//显示结果集各行各列System.out.println("ThedetailoftestTableis:");ResultSetMetaData

rsmd=rs.getMetaData();//获得结果集列数innumCols=rsmd.getColumnCount();//显示列标题for(inti=1;i<=numCols;i++){if(i>1)System.out.print(",");System.out.print(rsmd.getColumnLabel(i));}System.out.println("");//显示结果集信息while(rs.next()){//显示一行for(inti=1;i<=numCols;i++)

{if(i>1)System.out.print(",");System.out.print(rs.getString(i));}2.4DatabaseMetaDataDatabaseMetaData

dma=con.getMetaData();//驱动器和URL信息System.out.println("\nConnectedto"+dma.getURL());System.out.println("Driver"+dma.getDriverName());System.out.println("Version"+dma.getDriverVersion()//数据库信息System.out.println("\nDataBasename:"+dma.getDatabaseProductName()+dma.getDatabaseProductVersion());System.out.println("DataBasesupportsSQLkeywords:\n\t"+dma.getSQLKeywords());//数据库功能信息函数dma.supportsANSI92EntryLevelSQL()dma.supportsANSI92FullSQL()dma.supportsStoredProcedures()10.2.2JDBC数据类型及类型转换⑴JDBC的数据类型

JDBC的sql包中除了与数据库连接有关的抽象接口及与驱动器有关的DriverManager、DriverPropertyInfo等类型外，还定义了若干数据类，用以代表数据库中可能用到的SQL类型。下面我们就对它们逐一进行简略介绍。①sql.Date

sql包中的日期类Date是util包中Date类的子类，实际上也是util.Date类的子集。它只处理年月日，而忽略小时和分秒，用以代表SQL的DATE信息。

Date类的构造方法为：

publicDate(intyear,intmouth,intday)其中参数格式同util.Date类的构造方法一样，年参数为所需设定的年份减去1900所得的整数值，月参数为0至11，日参数为1至31。如1998年1月23日所对应创建日期类的方法调用为：

Dated=newDate(98,0,23);

Date类还提供两个与String类互相转换的方法，分别是：

publicstaticDatevalueOf(Strings)将字符串类参数转换为日期类对象。其中String类参数S的格式为“年-月-日”，加“1997-04-12”。

publicStringtoString()将日期类对象转换为String类对象表示，同样采用“年-月-日”的格式。②sql.Time该类是util.Date类的子类，也是它的一个子集。在Time类里，只处理小时和分秒，代表SQL的TIME类型。它与sql.Date合起来才表示完整的util.Date类信息。

Time类的构造方法为：

publicTime(int

hour,int

minute,intsecond)其中小时参数值为0至23，分秒参数取值均为0至59。与sql.Date一样，Time类也定义了两个与String类互相转换的函数ValueOf和String。不同的是String类对象的格式为“小时：分：秒”，如“12：26：06”。③sql.Timestamp这个类也是util.Date类的子类，其中除了包含年月日、小时和分秒和信息之外，还加入了纳秒信息(nanosecond)，1纳秒即1毫微秒。Timestamp类用来代表SQL时间戳(Timestamp)类型信息。

Timestamp类的构造方法为：

publicTimestamp(intyear,intmouth,intdate,inthour,intminute,intsecond,int

nano)其中纳秒参数的取值从0至999,999,999，其余各参数同前。

Timestamp类特别定义了设置和获得纳秒信息的方法，分别是

publicgetnanos()获取时间戳的纳秒部分

publicvoidsetNanos(intn)以给定数值设置时间戳的纳秒部分④sql.Types

Types类是Object类的直接子类。在这个类中以静态常量的形式定义了可使用的SQL的数值类型。所有这些类型常量都以前缀

publicfinalstaticint的形式标明是公有静态整数，且不可改动。具体的类型名和含义如表11.1所示。其中OTHER用来代表数据库定义的特殊数据，可以用getObject或setObject方法将其映射为一个Java的Object对象。表1Types中定义的SQL类型类型名含义BIGINT长整型数BINARY二进制数BIT比特数CHAR字符型DATE日期型DECIMAL十进制数DOUBLE双精度数FLOAT浮点数INTEGER整数LONGVARBINARY可变长型二进制数LONGVARCHAR可变长型字符NULL空类型NUMERIC数值型OTHER其他类型REAL实数SMALLINT短整型TIME时间类型TIMESTAMP时间戳类型TINYINT微整型VARBINARY可变二进制数VARCHAR可变字符型⑵SQL与Java由于SQL数据类型与Java的数据类型不一致，因而在使用Java类型的应用程序与使用SQL类型的数据库之间，需要某种读写类型转换机制。实际上我们前面介绍的ResultSet类的“get”系列方法，Statement及其子类的“set“系列方法和registerOutParameter方法，都是这一转换机制的组成部分。需要进行的读写转换包括下面情况：从数据库中读取数值后，存放在ResultSet对象中的是SQL类型的数据。而调用“get”系列方法时，JDBC才将SQL类型转换为指定的Java类型。在一般情形下，SQL类型相对应的Java类型如表11-2所示。⑵SQL与Java由于SQL数据类型与Java的数据类型不一致，因而在使用Java类型的应用程序与使用SQL类型的数据库之间，需要某种读写类型转换机制。实际上我们前面介绍的ResultSet类的“get”系列方法，Statement及其子类的“set“系列方法和registerOutParameter方法，都是这一转换机制的组成部分。需要进行的读写转换包括下面情况：从数据库中读取数值后，存放在ResultSet对象中的是SQL类型的数据。而调用“get”系列方法时，JDBC才将SQL类型转换为指定的Java类型。在一般情形下，SQL类型相对应的Java类型如表11-2所示。表2SQL类型一般所对应的Java类型SQLtypeJavatypeCHARjava.lang.StringVARCHARjava.lang.StringLONGVARCHARjava.lang.StringNUMERICjava.lang.BignumDECIMALjava.lang.Bignum⑶字符类型JDBC字符串数据类型为CHAR、VARCHAR和LONGVARCHAR。类型说明Fixed-lengthSQLServerchar

和nchar

数据类型直接映射到JDBCCHAR

类型。这些都是在列具有SETANSI_PADDINGON的情况下，具有由服务器提供的填充的固定长度的类型。对于nchar，填充始终是打开的，但对于char，在未填充服务器字符列的情况下，JDBC驱动程序将添加填充。Variable-lengthSQLServervarchar

和nvarchar

类型直接映射到JDBCVARCHAR类型。LongSQLServertext和ntext

类型映射到JDBCLONGVARCHAR类型。对于SQLServer2005来说，这些类型已过时，因此应改用大值类型。针对text和ntext

服务器列使用update<NumericType>和updateObject(int,java.lang.Object)

方法时将失败。然而，对于text和ntext

服务器列，支持将setObject

方法用于指定的字符转换类型。⑷二进制字符串类型JDBC二进制字符串类型为BINARY、VARBINARY和LONGVARBINARY。类型说明固定长度SQLServerbinary类型直接映射到JDBCBINARY类型。这是在列具有SETANSI_PADDINGON的情况下，具有由服务器提供填充的固定长度类型。没有填充服务器char列时，JDBC驱动程序会添加填充。SQLServertimestamp类型是具有8个字节的固定长度的JDBCBINARY类型。可变长度SQLServervarbinary

类型映射到JDBCVARBINARY类型。LongSQLServerimage类型映射到JDBCLONGVARBINARY类型。对于SQLServer2005来说，该类型已过时，因此应改用大值类型。SQLServer2005中的udt

类型作为LONGBINARY类型映射到JDBC。⑸精确数字类型JDBC精确数字类型直接映射到其对应的SQLServer

类型。类型说明BITJDBCBIT类型表示可能是0或1的单个位。此类型映射到SQLServerbit类型。TINYINTJDBCTINYINT类型表示单个字节。此类型映射到SQLServertinyint

类型。SMALLINTJDBCSMALLINT类型表示有符号的16位整数。此类型映射到SQLServersmallint

类型。INTEGERJDBCINTEGER类型表示有符号的32位整数。此类型映射到SQLServerint

类型。BIGINTJDBCBIGINT类型表示有符号的64位整数。此类型映射到SQLServerbigint

类型。NUMERICJDBCNUMERIC类型表示固定精度的十进制值，它可存放相同精度的值。NUMERIC类型映射到SQLServernumeric类型。DECIMALJDBCDECIMAL类型表示固定精度的十进制值，它可存放至少具有指定精度的值。DECIMAL类型映射到SQLServerdecimal类型。JDBCDECIMAL类型还映射到SQLServermoney和smallmoney

类型，这些类型是特定的固定精度的十进制类型，分别以8个字节和4个字节进行存储。⑹近似数字类型JDBC近似数值数据类型为REAL、DOUBLE

和FLOAT。类型说明

REALJDBCREAL

类型具有7位精度（单精度）并直接映射到SQLServerreal

类型。DOUBLEJDBCDOUBLE类型具有15位精度（双精度）并直接映射到SQLServerfloat类型。JDBCFLOAT类型是DOUBLE的同义词。由于FLOAT与DOUBLE之间可能存在冲突，因此DOUBLE为首选类型。⑺日期时间类型JDBCTIMESTAMP类型映射到SQLServerdatetime

和smalldatetime

类型。datetime

类型以两个4字节整数进行存储。smalldatetime

类型可存放相同的信息（日期和时间），但精度较低，为两个2字节的小整数。10.3JDBC与数据库的连接字符串⑴MySQL()mm.mysql-2.0.2-bin.jarClass.forName("org.gjt.mm.mysql.Driver");cn=DriverManager.getConnection("jdbc:mysql://MyDbComputerNameOrIP:3306/myDatabaseName",sUsr,sPwd);⑵Oracle(/ip/deploy/database/oracle9i/)classes12.zipClass.forName("oracle.jdbc.driver.OracleDriver");cn=DriverManager.getConnection("jdbc:oracle:thin:@MyDbComputerNameOrIP:1521:ORCL",sUsr,sPwd);⑶Sybase()jconn2.jarClass.forName("com.sybase..jdbc.SybDriver");cn=DriverManager.getConnection("jdbc:sybase:Tds:MyDbComputerNameOrIP:2638",sUsr,sPwd);//(Default-Username/Password:"dba"/"sql")⑷MicrosoftSQLServer(http://)Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");cn=DriverManager.getConnection("jdbc:microsoft:sqlserver://MyDbComputerNameOrIP:1433;databaseName=master",sUsr,sPwd);⑸ODBCClass.forName("sun.jdbc.odbc.JdbcOdbcDriver");Connectioncn=DriverManager.getConnection("jdbc:odbc:"+sDsn,sUsr,sPwd);⑹informix

Class.forName("rmix.jdbc.IfxDriver").newInstance();Stringurl="jdbc:informix-sqli://9:1533/testDB:INFORMIXSERVER=myserver;user=testuser;password=testpassword";⑺sybaseClass.forName("com.sybase.jdbc.SybDriver")url="jdbc:sybase:Tds::2638/asademo";SybConnectionconnection=(SybConnection)DriverManager.getConnection(url,"dba","sql");

例2:queryBook.jsp<%@pagecontentType="text/html;charset=gb2312"%><%@pagelanguage="java"import="java.sql.*,java.io.*"%><HTML><BODY><CENTER><FONTSIZE=5COLOR=blue>图书信息浏览</FONT><tableborder=3><tr><td><b><center>ID号</td><td><b><center>书名</td><td><b><center>出版社</td><td><b><center>价格</td></tr><%Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver").newInstance();Connectioncon=java.sql.DriverManager.getConnection("jdbc:microsoft:sqlserver://:1433;DatabaseName=jspdb","sa","");Statementstmt=con.createStatement();ResultSet

rst=stmt.executeQuery("select*frombook;");while(rst.next()){out.println("<tr>");out.println("<td>"+rst.getString("bkld")+"</td>");out.println("<td>"+rst.getString("bkName")+"</td>");out.println("<td>"+rst.getString("bkPublisher")+"</td>");out.println("<td>"+rst.getFloat("bkPrice")+"元"+"</td>");out.println("</tr>");}rst.close();stmt.close();con.close();%></table></BODY></HTML>10.4连接池⑴连接池原理

连接池技术的核心思想是：连接复用，通过建立一个数据库连接池以及一套连接使用、分配、管理策略，使得该连接池中的连接可以得到高效、安全的复用，避免了数据库连接频繁建立、关闭的开销。另外，由于对JDBC中的原始连接进行了封装，从而方便了数据库应用对于连接的使用（特别是对于事务处理），提高了开发效率，也正是因为这个封装层的存在，隔离了应用的本身的处理逻辑和具体数据库访问逻辑，使应用本身的复用成为可能。连接池主要由三部分组成：连接池的建立、连接池中连接的使用管理、连接池的关闭。⑵连接池的管理连接池管理策略是连接池机制的核心。当连接池建立后，如何对连接池中的连接进行管理，解决好连接池内连接的分配和释放，对系统的性能有很大的影响。连接的合理分配、释放可提高连接的复用，降低了系统建立新连接的开销，同时也加速了用户的访问速度。下面介绍连接池中连接的分配、释放策略。

连接池的分配、释放策略对于有效复用连接非常重要，我们采用的方法是一个很有名的设计模式：ReferenceCounting（引用记数）。该模式在复用资源方面应用的非常广泛，把该方法运用到对于连接的分配释放上，为每一个数据库连接，保留一个引用记数，用来记录该连接的使用者的个数。具体的实现方法是：当客户请求数据库连接时，首先查看连接池中是否有空闲连接（指当前没有分配出去的连接）。如果存在空闲连接，则把连接分配给客户并作相应处理（即标记该连接为正在使用，引用计数加1）。如果没有空闲连接，则查看当前所开的连接数是不是已经达到maxConn（最大连接数），如果没达到就重新创建一个连接给请求的客户；如果达到就按设定的maxWaitTime（最大等待时间）进行等待，如果等待maxWaitTime后仍没有空闲连接，就抛出无空闲连接的异常给用户。当客户释放数据库连接时，先判断该连接的引用次数是否超过了规定值，如果超过就删除该连接，并判断当前连接池内总的连接数是否小于minConn（最小连接数），若小于就将连接池充满；如果没超过就将该连接标记为开放状态，可供再次复用。可以看出正是这套策略保证了数据库连接的有效复用，避免频繁地建立、释放连接所带来的系统资源开销。⑶连接池的配置数据库连接池中到底要放置多少个连接，才能使系统的性能更佳，用minConn和maxConn来限制。minConn是当应用启动的时候连接池所创建的连接数，如果过大启动将变慢，但是启动后响应更快；如果过小启动加快，但是最初使用的用户将因为连接池中没有足够的连接不可避免的延缓了执行速度。因此应该在开发的过程中设定较小minConn，而在实际应用的中设定较大minConn。maxConn是连接池中的最大连接数，可以通过反复试验来确定此饱和点。为此在连接池类ConnectionPool中加入两个方法getActiveSize（）和getOpenSize（），ActiveSize

表示某一时间有多少连接正被使用，OpenSize表示连接池中有多少连接被打开，反映了连接池使用的峰值。将这两个值在日志信息中反应出来，minConn的值应该小于平均ActiveSize，而maxConn的值应该在activeSize和OpenSize之间。⑷连接池的关键技术①事务处理

前面讨论的是关于使用数据库连接进行普通的数据库访问。对于事务处理，情况就变得比较复杂。因为事务本身要求原则性的保证，此时就要求对于数据库的操作符合"All-All-Nothing"原则，即要么全部完成，要么什么都不做。如果简单的采用上述的连接复用的策略，就会发生问题，因为没有办法控制属于同一个事务的多个数据库操作方法的动作，可能这些数据库操作是在多个连接上进行的，并且这些连接可能被其他非事务方法复用。Connection本身具有提供了对于事务的支持，可以通过设置Connection的AutoCommit属性为false，显式的调用commit或rollback方法来实现。但是要安全、高效的进行连接复用，就必须提供相应的事务支持机制。方法是：采用显式的事务支撑方法，每一个事务独占一个连接。这种方法可以大大降低对于事务处理的复杂性，并且又不会妨碍连接的复用。连接管理服务提供了显式的事务开始、结束（commit或rollback）声明，以及一个事务注册表，用于登记事务发起者和事务使用的连接的对应关系，通过该表，使用事务的部分和连接管理部分就隔离开，因为该表是在运行时根据实际的调用情况动态生成的。事务使用的连接在该事务运行中不能被复用。在实现中，用户标识是通过使用者所在的线程来标识的。后面的所有对于数据库的访问都是通过查找该注册表，使用已经分配的连接来完成的。当事务结束时，从注册表中删除相应表项。②并发为了使连接管理服务有更大的通用性，我们必须要考虑到多线程环境，即并发问题。在一个多线程的环境下，必须要保证连接管理自身数据的一致性和连接内部数据的一致性，在这方面Java提供很好的支持（synchronized关键字），这样就很容易使连接管理成为线程安全的。③多数据库服务器在实际应用中，应用程序常常需要访问多个不同的数据库。如何通过同一个连接池访问不同的数据库，是应用程序需要解决的一个核心问题⑸连接池应用的实现一个完整的连接池应用包括三个部分：DBConnectionPool类，负责从连接池获取(或创建)连接、将连接返回给连接池、系统关闭时关闭所有连接释放所有资源；DBConnectionManager类，负责装载和注册JDBC驱动、根据属性文件中定义的属性创建DBConnectionPool、跟踪应用程序对连接池的引用等；应用程序对连接池的使用。本文实现的数据库连接池包括一个管理类DBConnectionManager，负责提供与多个连接池对象(DBConnectionPool类)之间的接口。每一个连接池对象管理一组封装过的JDBC连接对象Conn，封装过的JDBC连接对象Conn可以被任意数量的Model层的组件共享。类Conn

的设计很简单，如下所示：

ClassConn{

Privatejava.sgl.Connectioncon;//数据库连接对象

PublicBooleaninUse;//是否被使用

PubliclonglastAccess;//最近一次释放该连接的时间

Publicint

useCount;//被使用次数

}下面是实现连接池的主要代码：

//初始化数据库连接池

publicstaticsynchronizedvoidFastInitPool()

throwsException{

try{Class.forName(driver);

for(inti=0;i<size;i++){

Connectioncon=createConnection();

if(con!=null)addConnection(con);

}}}

//向连接池对象中添加数据库连接

privatestaticvoidaddConnection(Connectioncon){

if(pool=null||pool1=null){

pool=newVector(size);

pool1=newVector(size);}

pool.addElement(con);

pool1.addElement("false");}

//获取数据库连接

publicstaticsynchronizedConnectiongetConn()

throwsException{

Connectionconn=null;

try{if(driver=null)

FastInitPool();

//获得一个可用的(空闲的)连接

.for(inti=0;i<pool.size();i++){

conn=(Connection)pool.elementAt(i);

if(pool1.elementAt(i)=="false"){

pool1.set(i,"true");

//System.out.println("从连接池中获取第"+(i+1)+"个空闲连接");

returnconn;

}

}

//如果没有可用连接，且已有连接数小于最大连接数限制，则创建并增加一个新连接到连接池

conn=createConnection();

pool.addElement(conn);

pool1.addElement("true");

//System.out.println("所有连接都在使用,在连接池中再创建一个新连接");

}

catch(Exceptione){

System.err.println(e.g