java连接数据库应用

第11章数据库应用11.1关系数据库系统11.2JDBC32本章首先回顾关系数据库系统的基本概念和结构化查询语言SQL,然后介绍JDBC各种驱动程序类型,介绍JDBC提供的连接数据库、执行SQL语句、处理结果集的接口和类。本章将以Access和MySQL两种数据库为例,使用JDBC两种驱动方式进行数据库应用程序设计。111.1

关系数据库系统11.1.1数据库系统11.1.2关系模型11.1.3客户-服务器结构的关系数据库系统11.1.4结构化查询语言SQL11.1.5Access数据库11.1.6MySQL数据库211.1.1数据库系统数据库(DataBase,DB)：是长期存储在计算机外存上的、有结构的、可共享的数据集合。数据库中的数据按一定的数据模型描述、组织和存储,具有较小的冗余度、较高的数据独立性和易扩展性,可为不同的用户共享。数据库管理系统(DBMS)：是位于用户与操作系统之间的一层数据管理软件，用于科学地组织和存储数据、高效地获取和维护数据。数据库系统(DBS)：是采用了数据库技术的计算机系统,包括数据库、数据库管理系统(DBMS)、应用程序、数据库管理员(DBA)和用户。在不引起混淆的情况下,通常把数据库系统简称为数据库。311.1.2

关系模型实体(entity)：客观存在、可相互区别的事物(数据、信息)统称为实体。实体集(entity

set)：指性质相同的同类实体的集合。属性(attribute)：指用于描述、刻画实体的若干特性。数据模型(DM)：指用来描述现实世界中的实体(数据、信息)及其相互联系的方法或工具，是对现实世界的模拟。关系模型(relationalmodel)：用于描述关系数据库系统，它使用二维表格表示实体集、用关键码进行数据导航。其描述的内容主要包括数据结构、数据操作和数据完整性约束3个部分。4关系模型的数据结构关系：即一张规范化的二维表，表示一个实体集。表的每个列表示实体的一个属性,每一行表示一个实体。行也称为记录(record),列也称为字段(field)。关系的性质(规范化)列是同质的,即每一列中的数据项具有相同的数据类型。不同的列表示实体不同的属性,必须以不同的列名相区别。但不同的列可以具有相同的数据类型,也可以具有相同的取值范围。不允许出现重复的行,即表中的任意两行不能完全相同。每一个属性都是不可分解的。换言之,不允许表中还有表。行和列的次序可以任意交换,不影响关系的实际意义。5主键：能够唯一标识实体的属性或属性组称为主键(primarykey)。主键用于在一张表中唯一标识不同的实体。外键：如果一个关系的一个属性引用(参照)了另一个关系的主键，则称这个属性为外键(foreignkey)。外键用于描述两个关系之间的联系。关系模式：是对一个关系的结构描述，包括其属性构成、各属性的数据类型、属性间的依赖及完整性约束等。关系模型是由若干个关系模式组成的集合。关系模式的简单格式为：关系(列{，列})，如：学生(学号，姓名，性别，省份，地区，出生年月，民族，团员)课程(课程号，课程名，学分，学时)成绩(学号，课程号，成绩)6关系模型的数据操纵关系模型的操作主要包括数据查询和数据更新,数据更新包括插入、删除和修改。关系模型中的数据操作属于集合操作,操作对象和操作结果都是关系。关系模型的存取路径对用户透明,使数据具有独立性和安全性,同时也降低了应用程序的设计难度。7关系模型的数据完整性规则关系数据库为数据提供实体完整性、参照完整性和用户定义完整性三类完整性约束。实体完整性规则：要求一个关系的主键不能取重复值和空值。否则就会破坏实体的唯一性。参照完整性规则：要求一个关系的外键要么为空，要么取与被参照关系对应的主码值，即外码值必须是主码中已存在的值。用户定义的完整性规则：指由用户根据特定需求而设置的数据完整性约束规则。属于语义要求，包括非空、唯一和布尔条件约束三种情况。811.1.3

客户-服务器结构的关系数据库系统C/S模式数据库系统的功能结构C/S结构的关系数据库系统具有数据集中、功能分布的特点,将数据集中存储在数据库服务器上,服务器完成DBMS的核心功能,数据库的建立和维护工作由数据库管理员完成应用客户端完成数据处理、数据表示、用户接口等功能。两者之间采用请求-响应模式,即客户端提出服务请求、服务器响应请求并提供数据服务,如图11.1所示。主流的关系数据库系统(如Oracle、Sybase、DB2、SQLServer、MySQL等)都是采用C/S结构。9图11.1C/S模式数据库系统的功能结构图窗口服务器数据库开发工具客户机数据库应用程序数据库系统（服务端）数据库管理系统（DBMS）表用户数据库管理员（DBA）ODBC/专用接口软件设计人员数据库（客户端）请求发送SQL语句返回数据结果集响应10数据库连接应用程序与数据库的数据结构之间具有相互独立。应用程序能够访问不同的数据库,数据库也能够支持由不同工具所开发的应用程序。应用程序在访问数据库之前，需要借助一定的数据库连接机制与数据库建立连接。数据库连接技术主要有以下两大类:标准接口——为应用程序指定数据源的通用连接方式，如ODBC、JDBC。专用接口——提供与一个数据库直接相连的专用连接方式。如Sybase、SQLServer、Oralce、DB2等数据库具有专用驱动程序。11图11.2

应用程序与数据库连接示意图1211.1.4

结构化查询语言SQLSQL(StructuredQueryLanguage)是关系数据库的标准语言,对关系模型的发展和商用DBMS的研制起着重要的作用。SQL语言提供数据定义、数据查询、数据操纵和数据控制功能,具有综合统一、高度非过程化、面向集合等特点。目前所有关系数据库管理系统均支持SQL语言。13SQL数据库的体系结构一个SQL模式(schema)是一个命名的数据集合,由表、授权、规则、约束等组成。一个SQL表由行集构成,一行是列的序列,每列对应一个数据项。表有3种类型:基本表、视图和导出表。基本表是实际存储数据的表,视图(view)是由若干基本表或其他视图定义的虚表;导出表是执行查询后产生的临时表。用SQL语句可对基本表和视图进行查询等操作,从用户角度看,视图也是表。14SQL的特点与组成SQL语言是一种面向集合的描述性语言,是非过程性的,大多数语句都是独立执行,与上下文无关。非过程性语言的特点是只需要指出"做什么",不需要指出"怎样做"。SQL语言由四部分组成:数据定义语言(DDL)、数据操纵语言(DML)、数据控制语言(DCL)和嵌入式SQL语言的使用规定。表11-2SQL语言的动词SQL功能动词说明数据定义CREATE、DROP、ALTER创建表、删除表、修改表数据操纵INSERT、UPDATE、DELETE插入、更新、删除数据查询SELECT查询数据控制GRANT、REVOKE授予权限、收回权限15数据定义创建基本表CREATETABLE

表名(列名数据类型[列级完整性约束]

{,列名数据类型[列级完整性约束]}

[,表级完整性约束])；列的数据类型常见的有：整型(integer或int),浮点型(dec或float),字符型(char或varchar)，日期时间型(date,datetime或timestamp)等。字符型和浮点型还可说明其宽度及小数位数，如：char(20)，dec(5,2)。完整性约束条件包括：PRIMARYKEY、FOREIGNKEY、CHECK、NOTNULL、UNIQUE、DEFAULT等。如果涉及多列则定义在表级完整性约束位置。16修改基本表ALTERTABLE表名

[ADD新列名数据类型[列级完整性约束]][MODIFY列名数据类型][DROP完整性约束名]；删除表DROPTABLE表名；17数据更新插入数据INSERTINTO

表名[(列1{,列2})]VALUES(值1{,值2})；修改数据UPDATE

表名SET

列=表达式{，列=表达式}[WHERE条件表达式]；删除数据DELETEFROM

表名[WHERE条件表达式]；18数据查询SELECT语句语法SELECT[ALL|DISTINCT]列表达式{，列表达式}FROM表名

[WHERE条件表达式][GROUPBY列[HAVING条件表达式]][ORDERBY列[ASC|DESC]]运算符(表11-3)=、<>、<、<=、>、>=、AND、OR、LIKE集函数(表11-4)AVG([ALL|DISTINCT]列名)

COUNT(*)、COUNT(列名)SUM(列名)

、MAX(列名)、MIN(列名)

1911.1.5Access数据库Access数据库的操作【例11.1】

创建student数据库及表。数据库文件名：student.mdb创建表：stuinfo输入数据字段名称数据类型宽度NULL字段名称数据类型宽度NULL学号文本10是地区文本10否姓名文本10否出生日期日期/时间否性别文本2否民族文本10否省份文本10否团员是/否2否20配置ODBC数据源①选择[开始菜单]→[控制面板]→[管理工具]→[数据源(ODBC)],打开"ODBC数据源管理器"对话框中,单击<添加>按钮。②在"创建新数据源"对话框上,选择数据源驱动程序为"MicrosoflAccessDriver(*.mdb)",单击"完成"按钮。③在"ODBCMicrosoftAccess安装"对话框,输入"数据源名"(如student_access),单击<选择>按钮选择数据库文件student.mdb的路径全名,如图11.5所示。④回到"ODBC数据源管理器"对话框,在数据源列表框中就可见刚建立的数据源student_access。2111.1.6

MySQL数据库MySQL简介是Sun公司推出的一种C/S结构的开源免费数据库系统。下载、安装MySQL从/downloads下载MySQLCommunityServer的安装文件和JDBC驱动程序(Connector/J)文件。配置MySQL数据库安装的最后一步是配置MySQL,根据向导提示大多选择默认选项即可。MySQL默认使用3306端口提供数据库服务,默认的初始用户是root,为root输入密码界面如图11.8所示,若不输入,则root用户没有密码。启动MySQL数据库服务MySQL数据库服务默认会随Windows自动启动,可通过"控制面板→管理工具→服务"进行设置或停止/启动。22操纵数据库安装MySQL数据库客户端管理工具Navicat、MySQLFront、SQLyog等创建数据库数据库名为student，每个数据库对应安装目录下data目录的一个文件夹，如：D:\MySQLServer5.0\data\student创建表表名：stuinfo、course、stugrade(SQL语法参见P325)MySQL数据库的表均以单独的文件(.frm)存放在数据库文件夹中。输入数据：在表的数据视图窗口中以填表方式输入执行SQL语句：在数据库的查询视图窗口中执行SQL语句23创建外键：在表上右击，打开关系/外键对话框进行设置。查看关系模式：在模式设计视图中添加各表，就能直观查看各表间的关系。备份数据库成SQL文件：在数据库上右击，选择相应命令即可将数据库所有对象的定义和数据以SQL语句的方式导出到一个SQL文件(.sql)。以后可以再通过备份文件恢复数据库。24【例11.2】在查询视图(Query)中执行SQL语句创建stuinfo表CREATETABLEstuinfo(stu_idchar(10)NOTNULL,stu_namechar(10)DEFAULTNULL,sexchar(2)DEFAULTNULL,provincechar(20)DEFAULTNULL,areachar(20)DEFAULTNULL,birthdaydateDEFAULTNULL,nationchar(10)DEFAULT'汉',memberbit(1)DEFAULT1,departmentchar(20)DEFAULTNULL,PRIMARYKEY(stu_id));25另外两表的SQL创建语句CREATETABLEcourse(course_idchar(10)NOTNULL,course_namechar(20)DEFAULTNULL,credit_hourfloat(5,1)DEFAULTNULL,periodint(11)DEFAUIJNULL,PRIMARYKEY(course_id),CHECK(credit_hour>=0),CHECK(period>=0));CREATETABLEstugrade(stu_idchar(10)NOTNULL,course_idchar(10)NOTNULL,gradefloat(5,2)DEFAULTNULL,PRIMARYKEY(stu_id,course_id),FOREIGNKEY(stu_id)REFERENCESstuinfo(stu_id),FOREIGNKEY(course_id)REFERENCEScourse(course_id),CHECK(gradeBETWEEN0AND100));26其它相关SQL语句INSERT

INTOstuinfo(stu_id,stu_name,sex,province,area,binhday)VALUES('98111001','蔡尧强','男','浙江','金华','1980-1-20');

INSERTINT0stuinfoVALUES('98111002','冯正亚','女','江苏','连云港','1979-1-9','汉',1,NULL);

UPDATEstuinfoSETdepatment='计算机系';SELECTstu_name,sex,province,areaFROMstuinfoWHERE

(sex='女')AND(province='江苏')OR(provincelike'广％')

ORDER

BYprovince;SELECT

DISTINCTprovinceFROMstuinfo;SELECTprovince,COUNT(stu_name)FROMstuinfo

GROUP

BYprovinceORDER

BY

COUNT(stu_name);2711.2JDBC11.2.1JDBC的作用和功能11.2.2

指定JDBC驱动程序11.2.3

连接数据库11.2.4

执行SQL语句11.2.5

处理数据查询的结果集2811.2.1JDBC的作用和功能什么是JDBCJDBC(JavaDataBaseConnectivity)是基于Java的、提供连接、访问和操纵关系数据库的应用程序接口(API),JDBC提供在Java应用程序中执行SQL语句以访问和操纵关系数据库的方法。JDBC驱动程序类型JDBC-ODBC桥驱动程序(将JDBC翻译为ODBC)本地库Java实现驱动程序(本地驱动+Java)网络协议驱动程序(服务器中间件)数据库协议驱动程序(全Java编写,最常用)29图11.16JDBC驱动程序类型及其工作原理

30JDBC的基本功能、组成和工作原理数据库厂商提供的JDBC驱动程序必须实现JDBC规范的4个主要接口，它们是Driver、Connection、Statement和ResultSet。其中,Driver用于装入和管理JDBC驱动程序,可以通过DriverManager类使用Driver接口提供的功能;其他3个接口在应用程序中通常都会用到。图11.18JDBC的组成和工作原理示意图3111.2.2

指定JDBC驱动程序选择和安装JDBC驱动程序首选的驱动程序通常为数据库协议驱动程序，通常由数据库厂商以jar包的方式提供。也些数据库厂商会提供多种驱动程序，可根据需要选择，但优先考虑纯Java驱动方式。也有些数据库(如Access)只提供ODBC驱动程序，因而只能使用JDBC-ODBC桥驱动程序。JDBC-ODBC桥驱动程序由JDK默认安装，包含在JDK运行时库rt.jar中,对应的类名是sun.jdbc.odbc.JdbcOdbcDriver

。jar包一般无需特别的安装，只要将其加入classpath搜索路径中即可。也可只在IDE开发环境中设置好。部署应用时则需要将驱动程序jar包放于应用的lib目录中。MySQL的JDBC驱动程序下载解压后的jar包如：mysql-connector-java-5.1.6-bin.jar32MyEclipse设置JDBC驱动程序包在MyEclipse的project树视图中,右击某应用程序项目,选择快捷菜单"BuildPath｜ConfigBuildPath...",然后在项目属性对话框的Libraries页,单击"AddExtemalJARs…"按钮,在随后弹出的JARSelection对话框中,选择相应数据库的JDBC驱动程序jar包。在应用程序中指定JDBC驱动程序java.lang.Class类的forName()方法可用于装载JDBC驱动程序,如果指定的程序不存在则返回ClassNotFoundException异常例如：Class.forName("sun.jdbc.odbc.JdbcOdcDriver");//AccessClass.forName("com.mysql.jdbc.Driver");

//MySQL3311.2.3

连接数据库连接指定数据库的功能由DriverManager类和Connection接口实现,DatabaseMetaData接口用于获取数据库的属性信息。34DriverManager类提供了用于建立数据连接的方法publicstaticConnectiongetConnection(Stringurl)throwsSQLExceptionpublicstaticConnectiongetConnection(Stringurl,Stringuser,Stringpassword)throwsSQLException例如：Connectioncon1=DriverManager.getConnection("jdbc:odbc:student_access"); //student_access为Access数据库的ODBC数据源名Connection

con2=DriverManager.getConnection("jdbc:mysql://localhost/student?user=root&password=");

//student为MySQL数据库名，注意格式java.sql.DriverManager类35Connection接口Connection接口用于管理连接对象，进而获得Statement对象和DatabaseMetaData对象。主要方法如下：Statement

createStatement()throwsSQLException;

//创建执行SQL的语句对象Statement

createStatement(int

resultSetType,int

resultSetConcurrency)throwsSQLException;

//根据指定的参数创建语句对象void

close()throwsSQLException;

//关闭数据库连接boolean

isClosed()throwsSQLException;

//判断数据库连接是否已关闭DatabaseMetaData

getMetaData()throwsSQLException;

//获得所连接数据库的元数据对象，以便得到数据库表的结构信息}36DatabaseMetaData接口利用DatabaseMetaData接口可获得数据库的元数据信息。常用的方法有：String

getURL()throwsSQLException

//返回连接数据库的URLStringgetDriverName()throwsSQLException

//返回驱动程序名称StringgetDriverVersion()throwsSQLException //返回驱动程序版本号String

getUserName()throwsSQLException

//返回数据库的用户名String

getDatabaseProductName()throwsSQLException //返回数据库产品名称String

getDatabaseProductVersion()throwsSQLException

//返回数据库版本号37【例11.3】连接指定数据库并获取其属性信息本例分别与Access和MySQL数据库连接,获得数据库属性信息。Access数据库采用JDBC-ODBC桥驱动程序,MysQL数据库采用专用JDBC驱动程序。3811.2.4

执行SQL语句Statement接口是提供了执行SQL语句的相应方法，常用方法如下：int

executeUpdate(String

sql)throwsSQLException //执行数据定义和数据更新SQL语句ResultSet

executeQuery(String

sql)throwsSQLException

//执行数据查询SQL语句boolean

execute(String

sql)throwsSQLException

//执行各类SQL语句int

getUpdateCount()throwsSQLException

//获得数据更新所影响的行数ResultSet

getResultSet()throwsSQLException

//获得数据查询结果集void

close()throwsSQLException

//关闭语句对象39执行数据定义和数据更新SQL语句Statementstmt=conn.createStatement();

Stringsql="INSERTINTOstuinfo

(stu_id,stu_name)VALUES('98111041','李伟')";intresult=stmt.executeUpdate(sql);

//返回影响的行数1执行数据查询SQL语句Stringsql="SELECT*FROMstuinfo";

ResultSet

rset=stmt.executeQuery(sql);

//得到结果集对象，其中封装了查询结果的记录数据执行SQL语句调用execute()方法执行任意SQL语句；调用getResultSet()方法获得数据查询结果集调用getUpdateCount()方法获得数据更新所影响的行数。40本例演示执行数据更新SQL语句的executeUpdate()方法,可执行插入数据的INSERT语句和删除数据的DELETE语句。注意:程序执行一次,如果在stuinfo表中插入一行数据而没有删除,则再次执行会出错。为什么？【例11.4】

执行插入和删除数据的SQL语句4111.2.5

处理数据查询的结果集ResultSet接口ResultSet对象用于存储查询返回的记录集数据，具有指向其当前数据行的光标，光标最初被置于第一行之前。默认的ResultSet对象为只读，仅有一个向前移动的光标。ResultSet接口提供了大量方法，分别介绍如下：光标移动int

getRow()throwsSQLException

//获得当前行的行号boolean

previous()throwsSQLException

//将光标向前移一行boolean

next()throwsSQLException

//将光标向后移一行voidbeforeFirst()throwsSQLException//将光标移至首行之前

例如：introws=0;while(rset.next())rows++;

//获得结果集总行数beforeFirst(); //光标移回到首行之前42获得当前行指定列的数据Object

getObject(int

columnIndex)throwsSQLExceptionObjectgetObject(String

columnName)throwsSQLExceptionStringgetString(int

columnIndex)throwsSQLExceptionStringgetInt(String

columnName)throwsSQLException

例如：while(rset.next())

System.out.println(rset.getString(1));

//逐行输出第一列的数据43ResultSetMetaData接口从结果集中获得元数据ResultSetMetaData接口用于从查询结果集中获得元数据信息，常用的方法有：int

getColumnCount()throwsSQLException //返回列数String

getColumnName(int

column)throwsSQLException

//返回列名String

getColumnTypeName(int

column)throwsSQLException

//返回列数据类型名int

getColumnDisplaySize(int

column)throwsSQLException

//返回列的最大字符宽度例如：ResultSetMetaData

rsmd=resultset.getMetaData();

int

columnCount=rsmd.getColumnCount();//获得列数44【例11.5】

使用JTable显示数据查询的结果集本例演示执行数据查询的SELECT语句,将获得指定表的所有列名