Java-Web程序设计与案例教程-第6章-数据库整合开发.ppt

1、第6章 数据库整合开发 本章内容o MySQL简介o JDBC概述o 数据库连接池o DBUtils框架简介o 简易购物商城6.1 MySQL简介o MySQL是一个关系型数据库，它由瑞典MySQL AB公司开发，目前属于Oracle旗下的产品。MySQL是最流行的关系型数据库管理系统之一，在Web应用方面，MySQL是最好的关系数据库管理系统（Relational DataBase Management System，RDBMS）应用软件。6.2 JDBC概述o Java 数据库连接技术（Java DataBase Connectivity，JDBC）是 Java 访问数据库资源的标准，JD

2、BC 标准定义了一组 Java API，允许用户写出SQL语句，然后交给数据库。6.2 JDBC概述o 如图6.1所示，如果没有 JDBC 或者 ODBC，开发人员必须使用不同的一组 API 来访问不同的数据库；而有了JDBC 或者 ODBC，则只需要使用一组 API，再加上数据库厂商提供的数据库驱动程序就可以访问不同的数据库了，如图6.2所示。所以，利用 JDBC，我们就可以把同一个企业级 Java 应用移植到另一个数据库应用上。6.2 JDBC概述图6.1 应用程序直接访问数据库图6.2 应用程序访问JDBC6.2 JDBC概述o JDBC主要包含两部分：面向Java程序员的JDBC AP

3、I和面向数据库厂商的JDBC Drive API。n 1面向 Java 程序员的 JDBC APIo 它主要由一系列的接口定义所构成。o java.sql.DriveManager：该接口定义了用来处理装载驱动的程序，并且为创建新的数据库连接提供支持。o java.sql.Connection：该接口定义了实现对某一种指定数据库连接的功能。6.2 JDBC概述o java.sql.Statement：该接口定义了在一个给定的连接中作为 SQL 语句执行声明的容器以实现对数据库的操作。它主要包含有如下两种子类型。n java.sql.PreparedStatement：该接口定义了用于执行带或不

4、带 IN 参数的预编译 SQL 语句。n java.sql.CallableStatement：该接口定义了用于执行数据库的存储过程的调用。o java.sql.ResultSet：该接口定义了执行数据库的操作后返回的结果集。n 2面向数据库厂商的JDBC Drive API6.2 JDBC概述o 6.2.1 创建数据库连接o 6.2.2 SQL的执行o 6.2.3 SQL执行结果的处理 6.2.1 创建数据库连接o 在Java程序中要操作数据库，一般应该通过如下几步。n 1下载数据库开发所需要的驱动包o 用户可以从对应的数据库厂商的官网进行下载，例如，下载MySQL驱动包，如图6.3所示，选

5、择“JDBC DriverforMySQL”进行下载。o 下载的驱动包是一个压缩包，将包内的“mysql-connector-java-5.1.42-bin.jar”复制到“WebRootlib”文件夹下，如图6.4所示。6.2.1 创建数据库连接图6.3 下载MySQL驱动包图6.4 加载MySQL驱动包6.2.1 创建数据库连接n 2数据库管理工具o 在开发中，我们一般会创建一个专用于创建数据库连接和释放数据库连接的工具类。建立与数据库的连接需要完成如下两个步骤。o（1）加载驱动类到内存Class.forName(com.mysql.jdbc.Driver);o（2）创建与数据源的连接 C

6、onnection con=DriverManager.getConnection(String url,String username,String password);6.2.2 SQL的执行o 在实际开发中，当需要访问数据库时，只需要调用以下方法：Connection conn=DBUtil.getConnection();o 就可以获得一个java.sql.Connection类型的数据库连接对象，通过这个连接对象，用户可以操作数据库，发送目标SQL给数据库，并接收响应结果。6.2.2 SQL的执行o 在已建立数据库连接的基础上，向数据库发送要执行的SQL语句的接口是Statement

7、。Statement用于执行静态的SQL语句。n（1）java.sql.Statemento j a v a.s q l.S t a t e m e n t 类 型 的 对 象，是 通 过java.sql.Connection对象获得的，其代码如下：Statement st=conn.createStatement();o所获得的Statement对象st，可以用来执行SQL语句，Statement执行SQL语句的主要方法有两个：int executeUpdate(String sql);ResultSet executeQuery(String sql);6.2.2 SQL的执行o 其中，e

8、xecuteUpdate(String sql)方法可用来执行数据库的更新操作。该方法的返回值类型为int，代表影响的记录条数，即插入了几条数据，修改了几条数据，删除了几条数据等。而executeQuery(String sql)方法可用来执行数据库的查询操作。该方法的返回值类型是java.sql.ResultSet，该类型能够存储数据库返回的所有记录，并支持按条读取结果数据。6.2.2 SQL的执行n（2）java.sql.PreparedStatement接口o 在Statement中可以看到，动态SQL的生成是通过字符串拼接而成的，但是字符串拼接会带来很多的安全隐患，其中最为常见的安全漏

9、洞就是SQL注入。o 下面以登录功能为例，在cn.edu.zzti.dao包下的UserDAO接口中定义登录判断方法，代码如下：public UserDO findUser(String username,String password)throws SQLException;o 具体代码如下：6.2.2 SQL的执行o在代码的第6行，我们将拼接后的SQL语句打印到控制台进行观察。在第12行处，需要将获得的数据库查询结果进行封装，这里暂不实现这个功能。下面先来创建一个cn.edu.zzti.dao.impl.mysql.jdbc.test.UserDAOImplTest类，对方法findUse

10、r进行测试，具体代码如下。1 public UserDO findUser(String username,String password)throws SQLException 2 String sql=select*from user where username=+3 username+4 and password=+5 password+;6 System.out.println(登录sql是：+sql);7 Connection conn=DBUtil.getConnection();8 Statement st=conn.createStatement();9 ResultSet

11、rs=st.executeQuery(sql);10 if(rs.next()11 UserDO user=new UserDO();12 /将查询结果封装到user对象中，此处先不处理13 return user;14 15 return null;16 6.2.2 SQL的执行o Assert.assertNotNull(Object o)是Junit的断言，该断言方法判断对象o是否为空，为空则当前测试方法通过，否则测试方法失败。执行结果显示，使用不存在的用户名和密码，调用登录方法执行结果为登录正常。在输出的SQL结果显示，最终执行的SQL如下：1 public class UserDAO

12、ImplTest 2 private UserDAO userDAO=(UserDAO)3 DAOFactory.getDAO(cn.edu.zzti.dao.impl.mysql.jdbc.UserDAOImpl);4 Test5 public void testFinUser()6 String username=test or 1=1#;7 String password=;8 try 9 Assert.assertNull(userDAO.findUser(username,password);10 catch(SQLException e)11 e.printStackTrace()

13、;12 fail(出现异常，执行失败);13 14 15 6.2.2 SQL的执行select*from user where username=test or 1=1#and password=o 在这个SQL中，“#”代表MySQL中的注释。该语句的本意是从数据库中获取全部的数据，因为“or 1=1”使得这个条件恒为真。select*from user where username=test or 1=16.2.2 SQL的执行o 为了避免SQL注入的问题，JDBC提供了一种SQL预编译的机制，即PreparedStatement。首先用户提交的SQL中可以不指定具体的参数，对于可变值部分

14、让用户使用“?”（即占位符）来代替。然后再对SQL中的占位符单独设置值，将两者提交给数据库引擎进行编译，此时数据库引擎仅仅编译带有占位符“?”的SQL语句，等到编译完成后，在执行SQL时，将参数带入编译结果，此时，参数就只会作为参数整体进行数据比较，而不会作为SQL语法的一部分。6.2.2 SQL的执行nPreparedStatement对象的创建方式如下：PreparedStatement ps=conn.prepareStatement(String sql);n而SQL也需要进行相应的改写：String sql=select*from user where username=?and p

15、assword=?;o 与之前的SQL对比可以发现，原来的形式参数部分被占位符“?”代替，那么就需要将形式参数与具体的“?”绑定。绑定操作可通过调用 setXXX 方法来完成，其中，XXX 是与该参数相对应的类型。例如，如果参数的数据类型是long，则使用的方法就是 setLong。setXXX 方法的第一个参数是要设置的参数的序数位置，第二个参数是设置给该参数的值。例如，上述SQL将第一个参数设为形式参数username，第二个参数设为形式参数password，代码如下所示：ps.setString(1,username);ps.setString(2,password);6.2.3 SQL

16、执行结果处理o 无论是Statement，还是PreparedStatement，在执行SQL的时候，主要应用的执行方法是executeQuery和executeUpdate，Statement在执行execute*方法时，需要以SQL为字符串参数进行传递，而PreparedStatement则不需要参数。调用规则总结如表6.2所示。6.2.3 SQL执行结果处理类 名方 法 定 义说 明StatementResultSet executeQuery(String sql)执行select等Statementint executeUpdate(String sql)执行insert、updat

17、e、delete等PreparedStatementResultSet executeQuery()执行select等PreparedStatementint executeUpdate(String sql)执行insert、update、delete等表6.2 SQL执行结果6.3 数据库连接池o 创建数据库连接是一个十分耗时的操作，也容易让数据库产生安全隐患。因此，在程序初始化的时候，集中创建了多个数据库连接，并对它们进行集中管理，以供程序使用，这样就可以保证较快的数据库读/写速度，而且更加安全可靠。o 数据库连接池的运行原理如图6.16所示。6.3 数据库连接池Servlet1Serv

18、let2.ServletnDAO1.DAOmMySQLuser1.usersConnectionConnection.Connection图6.16 数据库连接池原理6.3 数据库连接池o 6.3.1 DataSourceo 6.3.2 Tomcat数据源o 6.3.3 DBCP6.3.1 DataSourceo JDBC1.0原来是用DriverManager类来产生一个对数据源的连接。JDBC2.0用一种替代的方法，使用java.sql.DataSource实现，代码变得更小巧精致，也更容易控制。编写数据库连接池需实现Java.sql.DataSource接口。DataSource接口中定

19、义了两个重载的getConnection方法：6.3.1 DataSourceConnection getConnection()Connection getConnection(String username,String password)o 开发数据库连接池实现DataSource接口时，在DataSource的实现类的构造方法中批量创建与数据库的连接，并把创建的连接加入存储java.sql.Connection对象的集合中。实现getConnection方法，让getConnection方法每次调用时，从存储java.sql.Connection对象的集合中取一个Connection返

20、回给用户。6.3.2 Tomcat数据源o Tomcat提供了数据源和连接池的实现，开发者直接使用即可。这里的Tomcat需要JDBC驱动，而不再是应用程序需要JDBC驱动，所以要先将对应数据库的JDBC驱动类库复制到Tomcat目录中的lib文件夹下，供Tomcat调用。o 首先在Tomcat目录中的“lib”目录下放入数据库驱动jar包，在工程的“META-INF”目录下创建一个“context.xml”文件，如图6.17和图6.18所示。6.3.2 Tomcat数据源图6.17 添加MySQL驱动包图6.18 工程添加配置文件6.3.2 Tomcat数据源o“context.xml”的配

21、置内容如下：o 这些属性含义如表6.4所示。1 2 12 6.3.2 Tomcat数据源键 名含 义name指定资源相对于java:comp/env上下文的JNDI名auth指定资源的管理者（默认Container即可）type指定资源所属的Java类的完整限定名（默认即可）maxIdle指定连接池中保留的空闲数据库连接的最大数目maxWait指定等待一个数据库连接成为可用状态的最大时间，单位毫秒username指定连接数据库的用户名password指定连接数据库的密码driverClassName指定JDBC驱动程序类名url指定连接数据库的URL表6.4 Tomcat数据源配置文件属性解析

22、6.3.2 Tomcat数据源o 这里元素的name属性即为我们使用JNDI去检索的关键字，在本例中为“shop”。接下来采用与之前工程相同的方式，创建数据库工具类cn.edu.zzti.util.tomcat.DBUtil来简化对数据库的操作。6.3.2 Tomcat数据源1 public class DBUtil2 private static DataSource ds=null;3 static4 try5 Context initCtx=new InitialContext();6 Context envCtx=(Context)initCtx.lookup(java:comp/en

23、v);7 ds=(DataSource)envCtx.lookup(shop);8 /根据元素的name属性值到JNDI容器中检索连接池对象9 catch(Exception e)10 throw new ExceptionInInitializerError(e);11 12 13 public static Connection getConnection()throws SQLException 14 return ds.getConnection();/利用数据源获取连接15 16 6.3.3 DBCPo 数据库连接池（DataBase Connection Pool，DBCP）是Ja

24、va数据库连接池的一种，由Apache开发，通过数据库连接池可以让程序自动管理数据库连接的释放和断开。DBCP是Apache上的一个Java连接池项目，也是Tomcat使用的连接池组件。单独使用DBCP需要准备3个包：n commons-dbcp-版本.jar；n commons-pool-版本.jar；n commons-logging-版本.jar。6.3.3 DBCPo DBCP的配置文件的内容如下，其参数含义如表6.5所示。参 数描 述username传递给JDBC驱动的用于建立连接的用户名password传递给JDBC驱动的用于建立连接的密码url传递给JDBC驱动的用于建立连接的U

25、RLdriverClassName使用的JDBC驱动的完整有效的Java类名connectionProperties当建立新连接时被发送给JDBC驱动的连接参数表6.5 DBCP常见配置参数含义6.4 DBUtils框架简介 o Commons DBUtils是Apache组织提供的一个对JDBC进行简单封装的开源工具类库，使用它能够简化JDBC应用程序的开发，同时也不会影响程序的性能。DBUtils是Java编程中的数据库操作实用工具，简单且实用。6.4 DBUtils框架简介 o DBUtils对于数据表的读操作，它可以把结果转换成List、Array、Set等Java集合，以便于程序员操

26、作。对于数据表的写操作，也变得很简单，只需写SQL语句；可以使用数据源、JNDI、数据库连接池等技术来优化性能，重用已经构建好的数据库连接对象，而不必像PHP、ASP那样，需要费时费力地不断重复构建和析构这样的对象。o Commons DBUtils的核心类有3个：mons.dbutils.ResultSetHandler、mons.dbutils.QueryRunner、mons.dbutils.DBUtils。6.4 DBUtils框架简介 o 6.4.1 QueryRunnero 6.4.2 ResultSetHandlero 6.4.3 资源释放6.4.1 QueryRunner o

27、QueryRunner类简单化了SQL查询，它与ResultSetHandler组合在一起使用可以完成大部分的数据库操作，能够大大减少编码量。n（1）QueryRunner类的构造方法o 默认的构造方法QueryRunner queryRunner=new QueryRunner();。o 需要一个 javax.sql.DataSource 来作参数的构造方法。QueryRunner qr=new QueryRunner(DBUtil.getDataSource()6.4.1 QueryRunner o（2）QueryRunner类的主要方法n public Object query(Conn

28、ection conn,String sql,Object params,ResultSetHandler rsh)throws SQLException：执行一个查询操作，在这个查询中，对象数组中的每个元素值被用来作为查询语句的置换参数。该方法会自行处理 PreparedStatement 和 ResultSet 的创建和关闭流程。n public Object query(String sql,Object params,ResultSetHandler rsh)throws SQLException：基本与第一种方法相同，唯一的不同在于它不将数据库连接提供给方法，并且它是从提供给构造方法

29、的DataSource数据源或使用setDataSource方法设置的数据源。6.4.1 QueryRunner n public Object query(Connection conn,String sql,ResultSetHandler rsh)throws SQLException：执行一个不需要置换参数的查询操作。n public int update(Connection conn,String sql,Object params)throws SQLException：用来执行一个更新操作，如插入、更新或删除。n public int update(Connection con

30、n,String sql)throws SQLException：用来执行一个不需要置换参数的更新操作。6.4.2 ResultSetHandlero ResultSetHandler接口用于处理java.sql.ResultSet，将数据按要求转换为另一种形式。ResultSetHandler接口提供了一个单独的处理java.sql.ResultSet的方法：Object handle(java.sql.ResultSet rs)6.4.2 ResultSetHandlern(1)查询类操作方法介绍o ResultSetHandler接口的实现类见表6.6，下面对每个接口的实现类的具体使用方

31、式进行介绍。功 能实 现 类单行数据处理ScalarHandler、ArrayHandler、MapHandler、BeanHandler多行数据处理BeanListHandler、ArrayListHandler、MapListHandler、ColumnListHandler、KeyedHandler、BeanMapHandler可供扩展的类BaseResultSetHandler表6.6 ResultSetHandler接口实现类说明6.4.2 ResultSetHandlero ArrayHandler：把结果集中的第一行数据转换成对象数组，示例代码如下。o ArrayListHand

32、ler：把结果集中的每一行数据都转换成一个对象数组，再存放到List中，示例代码如下。1 public static void queryToArray(Connection conn)throws SQLException 2 QueryRunner queryRunner=new QueryRunner();3 String sql=select*from auction;4 Object rs=queryRunner.query(conn,sql,new ArrayHandler();5 for(int i=0;irs.length;i+)6 System.out.print(第一列：+

33、rsi+t);7 8 System.out.print(n);9 1 public static void queryToArrayList(Connection conn)throws SQLException 2 QueryRunner queryRunner=new QueryRunner();3 String sql=select*from auction;4 List rs=queryRunner.query(conn,sql,new ArrayListHandler();5 for(Object record:rs)6 System.out.println(Arrays.toStr

34、ing(Object)record);7 8 6.4.2 ResultSetHandlero BeanHandler：把结果集中的第一行数据封装到一个对应的JavaBean实例中，示例代码如下。o BeanListHandler：把结果集中的每一行数据都封装到一个对应的JavaBean实例中，再存放到List中，示例代码如下。1 public static void queryToBean(Connection conn)throws SQLException 2 QueryRunner queryRunner=new QueryRunner();3 4 AuctionDO auc=query

35、Runner.query(conn,5 select*from auction,6 new BeanHandler(AuctionDO.class);7 System.out.println(auc);8 1 public static void queryToBeanList(Connection conn)throws SQLException 2 String sql=select*from auction;3 QueryRunner queryRunner=new QueryRunner();4 List list=queryRunner.query(conn,sql,5 new Be

36、anListHandler(AuctionDO.class);6 for(int i=0;i list.size();i+)7 System.out.println(list.get(i);8 9 6.4.2 ResultSetHandlero MapHandler：把结果集中的第一行数据封装到一个Map中，key是列名，value就是对应的值，示例代码如下。o MapListHandler：把结果集中的每一行数据都封装到一个Map中，然后再存放到List，示例代码如下。1 public static void queryToMap(Connection conn)throws SQLExce

37、ption 2 String sql=select*from auction;3 QueryRunner queryRunner=new QueryRunner();4 Map map=queryRunner.query(conn,sql,new MapHandler();5 SetEntry set=map.entrySet();6 for(Entry entry:set)7 System.out.println(entry.getKey()+:+entry.getValue();8 9 1 public static void queryToMapList(Connection conn)

38、throws SQLException 2 String sql=select*from auction;3 QueryRunner queryRunner=new QueryRunner();4 istMap list=queryRunner.query(conn,sql,new MapListHandler();5 for(Map mapValue:list)6 SetEntry set1=mapValue.entrySet();7 for(Entry entry:set1)8 System.out.println(entry.getKey()+:+entry.getValue();9 1

39、0 116.4.2 ResultSetHandlero ColumnListHandler：把结果集中的某一列的数据存放到List中，示例代码如下。o KeyedHandler：把结果集中的每一行数据都封装到一个Map中（List），再把这些Map存到一个Map里，其key为指定的列，示例代码如下。1 public static void queryToColList(Connection conn)throws SQLException2 QueryRunner queryRunner=new QueryRunner();3 String sql=select*from auction;/w

40、here id=14 List list=(List)queryRunner.query(conn,sql,5 new ColumnListHandler(name);6 System.out.println(list);7 1 public static void queryToKeyedHandler(Connection conn)throws SQLException2 QueryRunner queryRunner=new QueryRunner();3 String sql=select*from users;4 MapInteger,Map rs1=queryRunner.que

41、ry(conn,sql,5 new KeyedHandler(1);6 System.out.println(KeyedHandler:+rs1);7 MapInteger,Map rs2=queryRunner.query(conn,sql,8 new KeyedHandler(title);9 System.out.println(KeyedHandler:+rs2);10 6.4.2 ResultSetHandlero BeanMapHandler：用于获取所有结果集，将每行结果集转换为Javabean作为value，并指定某列为key，封装到HashMap中。相当于对每行数据进行与Be

42、anHandler相同的处理后，再指定列值为Key封装到HashMap中。1 public static void queryToBeanMap(Connection conn)throws SQLException2 QueryRunner queryRunner=new QueryRunner();3 String sql=select*from auction;4 Map rs=queryRunner.query(conn,sql,5 new BeanMapHandler(AuctionDO.class,1);6 System.out.println(BeanMapHandler:+rs

43、);7 6.4.2 ResultSetHandlero ScalarHandler：获取结果集中第一行数据指定列的值，常用来进行单值查询，示例代码如下。n（2）更新类操作方法介绍o 在执行insert、delete、update等更新数据库方法时，将调用QueryRunner的update方法，该方法返回影响的记录条数。1 public static void queryToBeanMap(Connection conn)throws SQLException2 QueryRunner queryRunner=new QueryRunner();3 String sql=select*from

44、 auction;4 int rs=runner.query(conn,sql,new ScalarHandler();5 System.out.println(ScalarHandler:+rs);6 String rs=runner.query(conn,sql,new ScalarHandler(2);7 /或者 String rs=runner.query(conn,sql,new ScalarHandler(userName);8 System.out.println(ScalarHandler:+rs);9 6.4.3 资源释放o DBUtils 框架提供了关闭连接、装载JDBC驱

45、动程序等常规工作的工具类，里面的所有方法都是静态的。主要方法如下：public static void close()throws SQLExceptiono DBUtils类提供了3个重载的关闭方法。这些方法检查所提供的参数是否为NULL，如果不是，它们就关闭Connection、Statement和ResultSet6.5 简易购物商城o 使用DBCP作为数据库连接池连接数据库，与DBUtils框架整合来改写简易购物商城的前台系统，将商品、用户、个人信息持久化存储到MySQL数据库中。6.5 简易购物商城o 6.5.1 数据库设计o 6.5.2 DAO接口实现6.5.1 数据库设计o 根据

46、前面章节的需求分析，简易购物商城前台系统共设计数据库表三张，首先创建数据库shop，然后在数据库shop中设计auction、user和personalInfo三张数据表，具体表结构如图6.23图6.25所示。图6.23 action商品表图6.24 user用户表图6.25 personal个人信息表6.5.2 DAO接口实现o 首先在工程中导入相关的jar包，并创建cn.edu.zzti.dao包中各个接口的具体实现类，放在cn.edu.zzti.dao.impl.mysql包下，如图6.26和图6.27所示。图6.26 导入相关jar包图6.27 基于DBUtils的DAO实现6.5.2

47、 DAO接口实现o 各个实现类的具体实现代码如下所示。n（1）AuctionDAOImpl1 public class AuctionDAOImpl implements AuctionDAO2 Override3 public AuctionDO getAuction(String id)throws SQLException 4 QueryRunner queryrunner=new QueryRunner(DBUtil.getDataSource();5 String sql=select*from auction where id=?;6 AuctionDO auctionDO=que

48、ryrunner.query(sql,7 new BeanHandler(AuctionDO.class),id);8 return auctionDO;9 10 Override11 public void addAuction(AuctionDO auc)throws SQLException 12 QueryRunner queryrunner=new QueryRunner(DBUtil.getDataSource();13 String sql=insert into auction values(?,?,?,?);14 Object params=new ObjectUUID.ra

49、ndomUUID().toString(),15 auc.getTitle(),auc.getDescription(),auc.getPrice();16 queryrunner.update(sql,params);17 6.5.2 DAO接口实现o 各个实现类的具体实现代码如下所示。n（1）AuctionDAOImpl18 Override19 public List getAll()throws SQLException 20 QueryRunner queryrunner=new QueryRunner(DBUtil.getDataSource();21 String sql=sel

50、ect*from auction;22 List list=queryrunner.query(sql,23 new BeanListHandler(AuctionDO.class);24 return list;25 26 Override27 public void deleteAuction(String id)throws SQLException 28 QueryRunner queryrunner=new QueryRunner(DBUtil.getDataSource();29 String sql=delete from auction where id=?;30 queryr