JSP访问数据库（灵魂所在）

5.1 MySQL数据库

MySQL 是一个小型关系型数据库管理系统，开发者为瑞典 MySQL AB 公司。在 2008 年 被 Sun 公司收购。而 2009 年,SUN 又被 Oracal 收购。目前 MySQL 被广泛地应用在 Internet 上的中小型网站中。由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点， 许多中小型网站为了降低网站总体拥有成本而选择了 MySQL 作为网站数据库。

Mysql数据库常用命令如下表

• Mysql数据库项目应用注意事项

  （1）MySQL 服务生存期设置方法

  MySQL 默认的服务生存期为 28800 秒（8 小时），如果超过 8 小时未访问 MySQL 数据库， 则 MySQL 将自动结束服务。查看有关 MySQL 服务生存期信息的 MySQL 命令为：

  MySQL> show variables like '%timeout%';
  

  可通过修改 MySQL 的初始化配置文件修改 MySQL 的服务生存期。修改方法是：

  ① 打开 MySQL 中的 my.ini 文件。

  ② 找到[MySQLd]节点，在其中添加以下两项，重启后生效。

  interactive_timeout=2880000 
  wait_timeout=2880000
  

• 关于 MySQL 中 LIMIT 的用法

  LIMIT 子句用于强制 SELECT 语句返回指定的记录数。使用 MySQL 中的 LIMIT 实现分 页比较方便，但要注意的是有些数据库不支持 LIMIT。 MySQL 中 LIMIT 语句格式：

  SELECT * FROM table LIMIT [offset,] rows ;
  

  LIMIT 接受一个或两个数字参数。参数必须是整型常量。如果给定两个参数，第一个 参数指定返回记录行的起始偏移量，第二个参数指定返回记录行的最大数目。注意，初始 记录行的偏移量是 0，而不是 1。

  MySQL 中 检索记录 6-15 行的 LIMIT 语句示例：

  MySQL> SELECT * FROM table LIMIT 5,10; 
  

  为了检索从某一个偏移量到记录集的结束所有的记录行，可以指定第二个参数 为 -1：

  SELECT * FROM table LIMIT 95,-1; // 检索记录 96 行至最后行。  
  

  如果只给定一个参数，它表示返回最大的记录行数目，也就是 LIMIT n 等价 于 LIMIT 0,n。 下列检索返回前 5 个记录行：

   SELECT * FROM table LIMIT 5;
  

5.2 使用JDBC访问数据库

5.2.1 JDBC简介

JDBC 全称为 Java DataBase Connectivity（Java 数据库连接）。它由一组用 Java 语言编写的类和接口组成，它是由 Sun 公司定义的一组接口，规定了 Java 开发人员访问 数据库所使用的方法的规范，由数据库厂商来实现，JDBC 也是 Java 核心类库的组成部分。

JDBC 可以连接的数据库包括 MySQL、Aceess、MS SQLServer、Oracle、Sybase、DB2 等。

JDBC 的最大特点是它独立于具体的关系数据库。与 ODBC 类似，JDBC API 中定义了 一些 Java 类和接口，分别用来实现与数据库的连接、发送 SQL 语句、获取结果集以及其 它的数据库对象，使得 Java 程序能方便地与数据库交互并处理所得的结果。JDBC 的 API 在 java.sql、javax.sql 等包中。

5.3.2 JDBC工作原理

Java 程序应用 JDBC，一般由以下步骤完成

（1）注册加载一个数据库驱动程序。

（2）创建数据库连接对象（Connection）。

（3）创建语句对象（Statement）。

（4）语句对象执行 sql 语句。

（5）用户程序处理执行 sql 语句的结果（主要是处理结果集 ResultSet 中的数据）。

（6）关闭连接（Connection）等资源。

前三步为准备阶段的工作，创建的语句对象供程序访问数据库使用。后边的三步是程 序在工作过程中需要访问数据库时，使用语句对象访问数据库的步骤。

创建语句对象的具体步骤和方法如下（以 MySQL 数据库为例）：

（1）加载相应数据库的 JDBC 驱动程序，该驱动会自动在 DriverManager 类中注册：

Class.forName("org.gjt.mm.MySQL.Driver"); 

（2）创建数据库连接对象，它由 DriverManager 根据已注册的驱动程序，调用 getConnection()方法实现：

 Connection con = null; 
 con =  DriverManager.getConnection("jdbc:MySQL://localhost:3306/books","root","123")

; 该连接对象连接的数据库名为“books”，连接用户名和密码分别为“root”和“123”。 注意，这里的密码即为安装 MySQL 时设置的 root 用户密码。

（3）创建用于执行用户的 SQL 语句的语句对象，它由已绑定数据库的连接对象生成：

Statement stmt=con.createStatement();  

可以看出，从加载驱动、创建连接到创建语句对象，这几步环环紧扣，保证了生成的 语句对象能够准确地访问到目标数据库。

以后用户访问数据库时，只需向语句对象提供相应的 SQL 语句即可，至于语句对象如 何操作数据库对用户来说是透明的，用户只需关心语句对象执行 SQL 语句返回的结果。

例如：

String strSQL="select * from titles"+"where isbn='"+request.getParameter("txt")+"'";  ResultSet rs = stmt.executeQuery(strSQL); 

该语句向语句对象的 executeQuery()方法提供了一条 SQL 查询语句，查看 books 数 据库中的图书表 titles，查询结果返回在结果集 ResulSet 对象 rs 中，程序可方便地从 结果集对象 rs 中获取所需的数据。

5.3.3 常用SQL语句

(1) 数据记录筛选：

sql="select * from 数据表 where 字段名=字段值 order by 字段名 "  sql="select * from 数据表 where 字段名 like ‘%字段值%‘ order by 字段名 "
sql="select top 10 * from 数据表 where 字段名 order by 字段名 " sql="select * from 数据表 where 字段名 in (‘值 1‘,‘值 2‘,‘值 3‘)"
sql="select * from 数据表 where 字段名 between 值 1 and 值 2" 

(2) 更新数据记录：

sql="update 数据表 set 字段名=字段值 where 条件表达式" 
sql="update 数据表 set 字段 1=值 1,字段 2=值 2 …… 字段 n=值 n where 条件表达式"

(3) 删除数据记录：

sql="delete from 数据表 where 条件表达式" 
sql="delete from 数据表" // (将数据表所有记录删除) 

(4) 添加数据记录：

sql="insert into 数据表 (字段 1,字段 2,字段 3 …) valuess (值 1,值 2,值 3 …)" 
sql="insert into 目标数据表 select * from 源数据表" //(把源表记录添加到目标数据
表) 

（5）字段处理与运算操作

排序：

 select * from table1 order by field1,field2 [desc]  

总数：

 select count * as totalcount from table1  

求和：

select sum(field1) as sumvalue from table1  

平均：

  select avg(field1) as avgvalue from table1

最大：

 select max(field1) as maxvalue from table1 

最小：

  select min(field1) as minvalue from table1

5.4 JDBC驱动类型

JDBC 驱动程序是用于特定数据库的一套实现了 JDBC 接口的类集。要通过 JDBC 来存 取某一特定的数据库，必须有相应的该数据库的 JDBC 驱动程序，它往往是由生产数据库 的厂家提供，是连接 JDBC API 与具体数据库之间的桥梁。目前，主流的数据库系统如 Oracle、SQLServer、Sybase、Informix 等都为客户提供了相应的驱动程序。

由于历史和厂商的原因，从驱动程序工作原理分析，通常有四种类型。分别是： JDBC-ODBC 桥、部分 Java、部分本机驱动程序、中间数据访问服务器和纯 Java 驱动程序。 分别说明如下。

1、JDBC-ODBC 桥（JDBC-ODBC bridge driver）

由于历史原因，ODBC 技术比 JDBC 更早或更成熟，所以通过该种方式访问一个 ODBC 数据库，是一个不错的选择。这种方法主要原理是：提供了一种把 JDBC 调用映射为 ODBC 调用的方法。因此，需要在客户机安装一个 ODBC 驱动。这种方式由于需要中间的转换过 程导致执行效率低，目前比较少用。实际上微软的数据库系统（如 SQLServer 和 Access） 仍然保留了该种技术的支持。

2、JDBC Native 桥（native-API, partly Java driver）

这一类型的驱动程序是直接将 JDBC 调用转换为特定的数据库调用，而不经过 ODBC 了，执行效率比第一种高。但该种方法也存在转换的问题，且这类驱动程序与第一种驱动 程序类型一样，也要求客户端的机器安装相应的二进制代码（驱动程序和厂商专有的 API）。所以这类驱动程序应用存在限制，如不太适合用于 Applet 等。

3、JDBC Network 驱动（JDBC-Net pure Java driver）

这种驱动实际上是根据我们熟悉的三层结构建立的，JDBC 先把对数据库的访问请求 传递给网络上的中间件服务器，中间件服务器再把请求翻译为符合数据库规范的调用，再 把这种调用传给数据库服务器。这种类型的驱动程序不需要客户端的安装和管理，所以特 别适合于具有中间件(middle tier)的分布式应用，但目前这类驱动程序的产品不多。

4、纯 Java 的本地 JDBC 驱动（native protocol，pure Java driver）

这种驱动直接把 JDBC 调用转换为符合相关数据库系统规范的请求。它通过使用一个 纯 Java数据库驱动程序将 JDBC对数据库的操作直接转换为针对某种数据库进行操作的本 地协议，来执行数据库的 直接访问 ，与其他类型的驱动相比，由于它根本不需要在客户端 或服务器端装载任何的软件或驱动，在调用过程中也不再需要先把 JDBC 的调用传给诸如 ODBC 或本地数据库接口或者是中间层服务器，可以直接和数据库服务器通讯，完全由 Java 实现，执行效率是非常高，实现了平台独立性。它特别适合于通过网络使用后台数据库的 Applet 及 Web 应用。

用户开发 JDBC 应用系统，首先需要安装数据库的 JDBC 驱动程序，不同的数据库需要 下载不同的驱动程序。对于普通的 Java 应用程序，只需要将 JDBC 驱动包复制到 CLASSPATH 所指向的目录下就可以了，这和导入普通的 Java 包没什么区别。而对于 Web 应用，通常 将 JDBC 驱动包放置在 WEB-INF/lib 目录下即可。

5.5 JDBC 常用接口、类介绍

1.Driver 接口

Driver 接口在 java.sql 包中定义，每种数据库的驱动程序都提供一个实现该接口的 类，简称 Driver 类，应用程序必须首先加载它。加载的目的就是创建自己的实例并向 java.sql.DriverManager 类注册该实例，以便驱动程序管理类（DriverManager）对数据 库驱动程序的管理。

通常情况下，通过 java.lang.Class 类的静态方法 forName(String className)，加 载欲连接的数据库驱动程序类，该方法的入口参数为欲加载的数据库驱动程序完整类名。 该静态方法的作用是要求 JVM 查找并加载指定的类，并将加载的类自动向 DriverManager 注册。

在加载驱动程序之前，必须确保驱动程序已经在 Java 编译器的类路径中，否则会抛 出找不到相关类的异常信息。在工程中添加数据库驱动程序的方法是：将下载的 JDBC 驱动程序存放在 Web 服务目录的 WEB-INF/lib/目录下。

对于每种驱动程序，其完整类名的定义也不一样。若加载成功，系统会将驱动程序注 册到 DriverManager 类中。如果加载失败，将抛出 ClassNotFoundException 异常。以下 是加载驱动程序的代码。

 try { 
 Class.forName(driverName); //加载 JDBC 驱动器 
 } catch (ClassNotFoundException ex) { 
 ex.printStackTrace(); 
 }

需要注意的是，加载驱动程序行为属于单例模式，也就是说，整个数据库应用中，只 加载一次就可以了。

2.DriverManager 类

数 据 库 驱 动 程 序 加 载 成 功 后 ， 接 下 来 就 由 DriverManager 类 来 处 理 了 ， DriverManager 类的主要作用是管理用户程序与特定数据库（驱动程序）的连接。所以是 该类是 JDBC 的管理层，作用于用户和驱动程序之间。可以调用 DriverManager 类的静态 方法 getConnection()方法得到数据库的连接。

在建立连接过程中，DriverManager 将检查注册表中的每个驱动程序，查看它是否可 以建立连接，有时，可能有多个 JDBC 驱动程序可以和给定数据库建立连接。例如，与给 定远程数据库连接时，可以使用 JDBC-ODBC 桥驱动程序、JDBC 到通用网络协议驱动程序 或数据库厂商提供的驱动程序。在这种情况下，加载驱动程序的顺序至关重要，因为 DriverManager 将使用它找到的第一个可以成功连接到给定的数据库驱动程序进行连接。

在 DriverManager 类中定义了三个重载的 getConnection()方法，分别如下：

static Connection getConnection(String url);  
static Connection getConnection(String url,Properties info);  static Connection getConnection(String url,String user,String password); 

这三个方法都是静态方法，可以直接通过类名进行调用。方法中的参数含义如下：
url：表示数据库资源的地址 ，是建立数据库连接的字符串，不同的数据库其连接字符串 也不一样；

info：是一个 java.util.Properties 类的实例；

user：是建立数据库连接所需用户名；

password：是建立数据库连接所需的密码。

3.Connection 接口

Connection 接口类对象是应用程序连接数据库的连接对象，该对象由 DriverManager 类的 getConnection()方法提供。由于 DriverManager 类保存着已注册的数据库连接驱动 类的清单。当调用方法 getConnection() 时，它将从清单中到找到可与 URL 中指定的数 据库进行连接的驱动程序。一个应用程序与单个数据库可有一个或多个连接，或者可与许 多数据库有多个连接。

连接对象的主要作用是调用 createStatement()来创建语句对象。

4.Statement 接口

Statement 用于将 SQL 语句发送到数据库中，并获取指定 SQL 语句的结果。JDBC 中实 际上有三种类型的 Statement 对象，它们都作为在给定连接上执行 SQL 语句的包容器： Statement、PreparedStatement（从 Statement 继承而来）和 CallableStatement（从 PreparedStatement 继承而来）。它们都专用于执行特定类型的 SQL 语句。

PreparedStatement 对象用于执行带或不带 IN 参数的预编译 SQL 语句。 CallableStatement 接口添加了处理 OUT 参数的方法，用于执行对数据库中的存储过 程。

executeQuery()方法用于执行 SELECT 查询语句，此方法返回一个结果集，其类型为 ResutSet。ResultSet 是一个与数据库表结构一致的集合类容器，程序通过游标可访问结 果集里的数据记录。

executeUpdate()方法用于更新数据，如执行 INSERT、UPDATE和 DELETE语句及 SQL DDL （数据定义）语句，这些语句返回一个整数，表示受影响的行数。

当 Connection 对象处于默认状态下时，所有的 Statement 对象的执行都是自动的。 也就是说，当 Statement 语句对象执行 SQL 语句时，该 SQL 语句马上提交给数据库并返回 结果。如果将连接修改为手动提交的事务模式，那么只有当执行 commit 语句时，才会提 交相应的数据库操作。

5.PreparedStatement 接口

PreparedStatement 接口继承 Statement 接口，所以它具有 Statement 的所有方法， 同时添加了一些自己的方法。PreparedStatement 与 Statement 有以下两点不同：

• PreparedStatement 接口对象包含已编译的 SQL 语句；
• PreparedStatement 接口对象中的 SQL 语句可包含一个或多个 IN 参数，也可用 “？”作为占位符。

由于 PreparedStatement 对象已预编译过，其执行速度要快于 Statement 对象。因此， 对于多次执行的 SQL 语句应该使用 PreparedStatement 对象，可大提高执行效率。

注 意 ： 在 创 建 PreparedStatement 对 象 时 需 要 SQL 命 令 字 符 串 作 为 preparedStatement()方法的参数，这样才能实现 SQL 命令预编译。SQL 命令字符串中可 用“ ?”作为占位符，并且在执行 executeQuery()或 executeUpdate()方法之前用 setXxx(n,p)方法为占位符赋值，具体方法见表 5.10。如果参数类型为 String，则使用 setString()方法。在 setXxx(n,p)方法中的第一个参数 n 表示要赋值的参数在 SQL 命令 字符串中出现的次序，n 从 1 开始；第二个参数 p 为设置的参数值。

在访问数据库时，不再提供 SQL 语句及参数信息，而是直接调用 PreparedStatement 对象的 executeQuery()或 executeUpdate()方法执行查询，可以很明显地看出这个类使用 的便捷性。

6.ResultSet 接口

ResultSet 接口用于获取语句对象执行 SQL 语句返回的结果，它的实例对象包含符合 SQL 语句中条件的所有记录的集合。

程序中使用结果集名称作为访问结果集数据表的游标，当获得一个 ResultSet 时， 它的游标正好指向第一行之前的位置。可以使用游标的 next()方法转到下一行，每调用 一次 next()方法游标向下移动一行，当数据行结束时，该方法会返回 false。

对于不支持游标滚动的数据集，必须按顺序访问 ResultSet 数据行，但可访问任意顺 序数据列。

7．DatabaseMetaData 类

DatabaseMetaData 对象可提供整个数据库的相关信息。主要用它获取数据库中表的 名称，以及表中列的名称。

DatabaseMetaData 提供了如下方法用来获取数据库表的定义：

 ResultSet getTables(String catalog, String schemaPattern, String tableNamePattern,  String[] types) throws SQLException

对于 getTables()方法的四个参数的含义如下：

• catalog 要在其中查找表名的目录名。可将其设置为 null。MySQL 数据库的目 录项实际上是它在文件系统中的绝对路径名称。
• schemaPattern 要包括的数据库“方案”。许多数据库不支持方案，而对另一些 数据库而言，它代表数据库所有者的用户名。一般将它设置为 null。
• tableNamePattern 用来描述要检索的表的名称。如果希望检索所有表名，则将 其设为通配符“%”。

types[] 获取哪些类型的表,每种类型以字符串的形式放入该数组中,典型的表类型 一般包括"TABLE"、“VIEW”、“SYSTEM TABLE”、“GLOBAL TEMPORARY”、“LOCAL TEMPORARY”、 “ALIAS” 和 “SYNONYM”。该参数可以为 null，此时不设检索条件，会得到所有这些表。 一般来说，我们要获取的就是表和视图的信息,因此字符传数组 types 的值一般写成 {“TABLE”,“VIEW”}。

8．ResultSetMetaData 类

使 用 ResultSet 类 的 getMetaData() 方 法 可 以 从 ResultSet 中 获 取 ResultSetMetaData 对象。ResultSetMetaData 类对象保存了所有 ResultSet 类对象中关 于字段的信息，并提供许多方法来取得这些信息。例如，可以使用此对象可以获得列的数 目和类型以及每一列的名称。

5.6 数据库连接池原理

数据库连接池的基本思想就是为数据库连接建立一个“存储池”。 连接池是一个可以 存储多个数据库连接对象的容器，当程序需要连接数据库时，可直接从连接池中获取一个 连接，使用结束时将连接还给连接池。这样一个连接可以被很多程序共享，无需每次都与 数据库交互时都与数据库进行连接与断开，提高数据库访问速度。

数据库建立初期，预先在缓冲池中放入一定数量的连接，当需要建立数据库连接时， 只需从“连接池”中申请一个，使用完毕之后再将该连接作为公共资源保存在“连接池” 中，以供其他连接申请使用。在这种情况下，当需要连接时，就不需要再重新建立连接， 这样就在很大程度上提高了数据库连接处理的速度；同时，还可以通过设定连接池最大连 接数来防止系统无控制地与数据库连接；更为重要的是可以通过连接池管理机制监视数据 库的连接数量以及各连接的使用情况，为系统开发﹑测试及性能调整提供依据。

除了向连接池请求分配数据库连接之外，连接池还负责按照一定的规则释放使用次数 较多的连接，并重新生成新的连接实例，保持连接池中所有连接的可用性。

数据库连接池的最小连接数和最大连接数的设置要考虑到下列几个因素： 1) 最小连接数是连接池一直保持的数据库连接，所以如果应用程序对数据库连接的 使用量不大，将会有大量的数据库连接资源被浪费； 2) 最大连接数是连接池能申请的最大连接数，如果数据库连接请求超过此数，后面 的数据库连接请求将被加入到等待队列中，这会影响之后的数据库操作。 3) 超过最小连接数量的连接请求等价于建立一个新的数据库连接。不过，这些大 于最小连接数的数据库连接在使用完不会马上被释放，它将被放到连接池中等待重复使用 或是空闲超时后被释放。