Monday, 21 May 2007
Sunday, 20 May 2007
javabean_introduction
java bean
最初,JavaBean的目的是为了将可以重复使用的软件代码打包标准。特别是用与帮助厂家开发在综合开发环境(IDE)下使用的java软件部件。这些包括如Grid控件,用户可以将该部件拖放到开发环境中。从此,JavaBean就可以扩展为一个java web 应用的标准部件,并且JavaBean部件框架已经扩展为企业版的 Bean(EJB)。
JavaBean 和 Server Bean(通常称为 Enterprise JavaBean (EJB))有一些基本相同之处。它们都是用一组特性创建,以执行其特定任务的对象或组件。它们还有从当前所驻留服务器上的容器获得其它特性的能力。这使得 bean 的行为根据特定任务和所在环境的不同而有所不同。
Enterprise Bean 与 JavaBean 不同。JavaBean 是使用 java.beans 包开发的,它是 Java 2 标准版的一部分。JavaBean 是一台机器上同一个地址空间中运行的组件。JavaBean 是进程内组件。Enterprise Bean 是使用 javax.ejb 包开发的,它是标准 JDK 的扩展,是 Java 2 Enterprise Edition 的一部分。Enterprise Bean 是在多台机器上跨几个地址空间运行的组件。因此 Enterprise Bean 是进程间组件。JavaBean 通常用作 GUI 窗口小部件,而 Enterprise Bean 则用作分布式商业对象.
JavaBean 是一种JAVA语言写成的可重用组件。为写成JavaBean,类必须是具体的和公共的,并且具有无参数的构造器。JavaBeans 通过提供符合一致性设计模式的公共方法将内部域暴露称为属性。众所周知,属性名称符合这种模式,其他Java 类可以通过自省机制发现和操作这些JavaBean 属性。
用户可以使用JavaBean将功能、处理、值、数据库访问和其他任何可以用java代码创造的对象进行打包,并且其他的开发者可以通过内部的JSP页面、Servlet、其他JavaBean、applet程序或者应用来使用这些对象。用户可以认为JavaBean提供了一种随时随地的复制和粘贴的功能,而不用关心任何改变。
JavaBean是Sun微系统的一个面向对象的编程接口,它可以让你建可重用应用程序或能在网络中任何主流操作系统平台上配置的程序块,称作组件。像Java applet一样,JavaBeans组件(或“Beans”)能够给予万维网页面交互的能力,例如:计算感兴趣的比率或是根据用户或浏览器的特性改变页面内容。
从用户的观点来看,一个组件可以是一个与你交互的按钮或是一个当你按下按钮它便开始的小计算程序。从一个开发者的观点来看,那个按钮组件和计算器组件是分别被创建的,并且他们可以一起使用或是在不同的应用程序或情况下和不同的组件产生不同的组合来使用。
当组件或Beans在使用过程中,Bean的性质(比如,一个窗口的背景色)对于其他Bean来说是可见的,并且,之前没“碰到”过的Bean也可以动态地获悉彼此的特性并从而进行交互。
Bean是随Sun的Bean开发包(BDK)开发出来的,并且能在任何主流操作系统平台的许多应用程序环境(人们所说的“容器”,container)中运行,包括浏览器,文字处理软件,以及一些其他应用。
要想用JavaBeans建一个组件,你必须用Sun的Java编程语言来写程序,并且在程序中包括描述组件特性的JavaBeans语句,这些组件特性例如:用户接口的特性,以及触发一个bean和在同一个容器中或网络其他地方的其他的bean交流的事件。
JavaBeans给Java应用程序提供了OpenDoc和ActiveX接口已提供的这种复合文档的能力。
JavaBean的任务就是: “Write once, run anywhere, reuse everywhere”,即“一次性编写,任何地方执行,任何地方重用”。这个任何实际上就是要解决困扰软件工业的日益增加的复杂性,提供一个简单的、紧凑的和优秀的问题解决方案。1. 一个开发良好的软件组件应该是一次性地编写,而不需要再重新编写代码以增强或完善功能。因此,JavaBean应该提供一个实际的方法来增强现有代码的利用率,而不再需要在原有代码上重新进行编程。除了在节约开发资源方面的意义外,一次性地编写JavaBean组件也可以在版本控制方面起到非常好的作用。开发者可以不断地对组件进行改进,而不必从头开始编写代码。这样就可以在原有基础上不断提高组件功能,而不会犯相同的错误。2. JavaBean组件在任意地方运行是指组件可以在任何环境和平台上使用,这可以满足各种交互式平台的需求。由于JavaBean是基于Java的,所以它可以很容易地得到交互式平台的支持。JavaBean组件在任意地方执行不仅是指组件可以在不同的操作平台上运行,还包括在分布式网络环境中运行。3.JavaBean组件在任意地方的重用说的是它能够在包括应用程序、其他组件、文档、Web站点和应用程序构造器工具的多种方案中再利用。这也许是JavaBean组件的最为重要的任务了,因为它正是JavaBean组件区别于Java程序的特点之一。Java程序的任务就是JavaBean组件所具有的前两个任务,而这第3个任务却是JavaBean组件独有的。
JavaBean是可复用的平台独立的软件组件,开发者可以在软件构造器工具中其直接进行可视化操作。
软件构造器工具可以是Web页面构造器、可视化应用程序构造器、CUI设计构造器或服务器应用程序构造器。有时,构造器工具也可以是一个包含子一些bean的复合文档的文档编辑器。
JavaBean可以是简单的CUI要素,如按钮或滚动条;也可以是复杂的可视化软件组件,如数据库视图,有些JavaBean是没有GUI表现形式的,但这些JavaBean仍然可以使用应用程序构造器可视化地进行组合。
一个JavaBean和一个Javaapplet相似,是一个非常简单的遵循某种严格协议的Java类。每个JavaBean的功能都可能不一样,但它们都必须支持以下特征。
一个bean没有必须继承的特定的基类或接口。可视化的bean必须继承的类是java.awt.Component,这样它们才能添加到可视化容器中去,非可视化bean则不需要继承这个类。有许多bean,无论是在应用程序构造器工具中,还是在最后创建好的应用程序中,都具有很强的可视化特征,但这并非每个bean必须的特征。
在使用Java编程时,并不是所有软件模块都需要转换成bean。Bean比较适合于那些具有可视化操作和定制特性的软件组件。
从基本上说,JavaBean可以看成是一个黑盒子,即只需要知道其功能而不必管其内部结构的软件设备。黑盒子只介绍和定义其外部特征和与其他部分的接口,如按钮、窗口、颜色、形状、句柄等。
通过将系统看成使用黑盒子关联起来的通讯网络,我们可以忽略黑盒子内部的系统细节,从而有效地控制系统的整体性能。作为一个黑盒子的模型,JavaBean有3个接口面,可以独立进行开发。
1. JavaBean可以调用的方法。
2. JavaBean提供的可读写的属性。
3. JavaBean向外部发送的或从外部接收的事件。
JavaBean设计注意事项
1.不要试图在JavaBean返回的HTML中放置任何字体尺寸。
并不是所有的浏览器都相同。很多浏览器无法处理完整的字体尺寸。
2.不要试图在JavaBean返回的HTML中放置任何脚本或者DHTML。
向页面直接输出脚本或者DHTML相当于自我毁灭,因为某些浏览器版本在处理不正确的脚本时会崩溃(非常少但是有)。如果用户的JavaBean在运行时是动态的推出复杂的HTML语言,用户将陷入调试的噩梦。另外,复杂的HTML将限制JavaBean的寿命和灵活性。
3.不要提供任何的选择。
最初,JavaBean的目的是为了将可以重复使用的软件代码打包标准。特别是用与帮助厂家开发在综合开发环境(IDE)下使用的java软件部件。这些包括如Grid控件,用户可以将该部件拖放到开发环境中。从此,JavaBean就可以扩展为一个java web 应用的标准部件,并且JavaBean部件框架已经扩展为企业版的 Bean(EJB)。
JavaBean 和 Server Bean(通常称为 Enterprise JavaBean (EJB))有一些基本相同之处。它们都是用一组特性创建,以执行其特定任务的对象或组件。它们还有从当前所驻留服务器上的容器获得其它特性的能力。这使得 bean 的行为根据特定任务和所在环境的不同而有所不同。
Enterprise Bean 与 JavaBean 不同。JavaBean 是使用 java.beans 包开发的,它是 Java 2 标准版的一部分。JavaBean 是一台机器上同一个地址空间中运行的组件。JavaBean 是进程内组件。Enterprise Bean 是使用 javax.ejb 包开发的,它是标准 JDK 的扩展,是 Java 2 Enterprise Edition 的一部分。Enterprise Bean 是在多台机器上跨几个地址空间运行的组件。因此 Enterprise Bean 是进程间组件。JavaBean 通常用作 GUI 窗口小部件,而 Enterprise Bean 则用作分布式商业对象.
JavaBean 是一种JAVA语言写成的可重用组件。为写成JavaBean,类必须是具体的和公共的,并且具有无参数的构造器。JavaBeans 通过提供符合一致性设计模式的公共方法将内部域暴露称为属性。众所周知,属性名称符合这种模式,其他Java 类可以通过自省机制发现和操作这些JavaBean 属性。
用户可以使用JavaBean将功能、处理、值、数据库访问和其他任何可以用java代码创造的对象进行打包,并且其他的开发者可以通过内部的JSP页面、Servlet、其他JavaBean、applet程序或者应用来使用这些对象。用户可以认为JavaBean提供了一种随时随地的复制和粘贴的功能,而不用关心任何改变。
JavaBean是Sun微系统的一个面向对象的编程接口,它可以让你建可重用应用程序或能在网络中任何主流操作系统平台上配置的程序块,称作组件。像Java applet一样,JavaBeans组件(或“Beans”)能够给予万维网页面交互的能力,例如:计算感兴趣的比率或是根据用户或浏览器的特性改变页面内容。
从用户的观点来看,一个组件可以是一个与你交互的按钮或是一个当你按下按钮它便开始的小计算程序。从一个开发者的观点来看,那个按钮组件和计算器组件是分别被创建的,并且他们可以一起使用或是在不同的应用程序或情况下和不同的组件产生不同的组合来使用。
当组件或Beans在使用过程中,Bean的性质(比如,一个窗口的背景色)对于其他Bean来说是可见的,并且,之前没“碰到”过的Bean也可以动态地获悉彼此的特性并从而进行交互。
Bean是随Sun的Bean开发包(BDK)开发出来的,并且能在任何主流操作系统平台的许多应用程序环境(人们所说的“容器”,container)中运行,包括浏览器,文字处理软件,以及一些其他应用。
要想用JavaBeans建一个组件,你必须用Sun的Java编程语言来写程序,并且在程序中包括描述组件特性的JavaBeans语句,这些组件特性例如:用户接口的特性,以及触发一个bean和在同一个容器中或网络其他地方的其他的bean交流的事件。
JavaBeans给Java应用程序提供了OpenDoc和ActiveX接口已提供的这种复合文档的能力。
JavaBean的任务就是: “Write once, run anywhere, reuse everywhere”,即“一次性编写,任何地方执行,任何地方重用”。这个任何实际上就是要解决困扰软件工业的日益增加的复杂性,提供一个简单的、紧凑的和优秀的问题解决方案。1. 一个开发良好的软件组件应该是一次性地编写,而不需要再重新编写代码以增强或完善功能。因此,JavaBean应该提供一个实际的方法来增强现有代码的利用率,而不再需要在原有代码上重新进行编程。除了在节约开发资源方面的意义外,一次性地编写JavaBean组件也可以在版本控制方面起到非常好的作用。开发者可以不断地对组件进行改进,而不必从头开始编写代码。这样就可以在原有基础上不断提高组件功能,而不会犯相同的错误。2. JavaBean组件在任意地方运行是指组件可以在任何环境和平台上使用,这可以满足各种交互式平台的需求。由于JavaBean是基于Java的,所以它可以很容易地得到交互式平台的支持。JavaBean组件在任意地方执行不仅是指组件可以在不同的操作平台上运行,还包括在分布式网络环境中运行。3.JavaBean组件在任意地方的重用说的是它能够在包括应用程序、其他组件、文档、Web站点和应用程序构造器工具的多种方案中再利用。这也许是JavaBean组件的最为重要的任务了,因为它正是JavaBean组件区别于Java程序的特点之一。Java程序的任务就是JavaBean组件所具有的前两个任务,而这第3个任务却是JavaBean组件独有的。
JavaBean是可复用的平台独立的软件组件,开发者可以在软件构造器工具中其直接进行可视化操作。
软件构造器工具可以是Web页面构造器、可视化应用程序构造器、CUI设计构造器或服务器应用程序构造器。有时,构造器工具也可以是一个包含子一些bean的复合文档的文档编辑器。
JavaBean可以是简单的CUI要素,如按钮或滚动条;也可以是复杂的可视化软件组件,如数据库视图,有些JavaBean是没有GUI表现形式的,但这些JavaBean仍然可以使用应用程序构造器可视化地进行组合。
一个JavaBean和一个Javaapplet相似,是一个非常简单的遵循某种严格协议的Java类。每个JavaBean的功能都可能不一样,但它们都必须支持以下特征。
一个bean没有必须继承的特定的基类或接口。可视化的bean必须继承的类是java.awt.Component,这样它们才能添加到可视化容器中去,非可视化bean则不需要继承这个类。有许多bean,无论是在应用程序构造器工具中,还是在最后创建好的应用程序中,都具有很强的可视化特征,但这并非每个bean必须的特征。
在使用Java编程时,并不是所有软件模块都需要转换成bean。Bean比较适合于那些具有可视化操作和定制特性的软件组件。
从基本上说,JavaBean可以看成是一个黑盒子,即只需要知道其功能而不必管其内部结构的软件设备。黑盒子只介绍和定义其外部特征和与其他部分的接口,如按钮、窗口、颜色、形状、句柄等。
通过将系统看成使用黑盒子关联起来的通讯网络,我们可以忽略黑盒子内部的系统细节,从而有效地控制系统的整体性能。作为一个黑盒子的模型,JavaBean有3个接口面,可以独立进行开发。
1. JavaBean可以调用的方法。
2. JavaBean提供的可读写的属性。
3. JavaBean向外部发送的或从外部接收的事件。
JavaBean设计注意事项
1.不要试图在JavaBean返回的HTML中放置任何字体尺寸。
并不是所有的浏览器都相同。很多浏览器无法处理完整的字体尺寸。
2.不要试图在JavaBean返回的HTML中放置任何脚本或者DHTML。
向页面直接输出脚本或者DHTML相当于自我毁灭,因为某些浏览器版本在处理不正确的脚本时会崩溃(非常少但是有)。如果用户的JavaBean在运行时是动态的推出复杂的HTML语言,用户将陷入调试的噩梦。另外,复杂的HTML将限制JavaBean的寿命和灵活性。
3.不要提供任何的选择。
认了吧-陈奕迅
歌曲:淘汰
曲:周杰伦词:周杰伦
编曲: c y kong
监制: davy chan c y kong 陈奕迅
我说了所有的谎
你全都相信
简单的我爱你
你却老不信
你书里的剧情我不想上演
因为我喜欢喜剧收尾
我试过完美放弃
的确很踏实
醒来了梦散了
你我都走散了
情歌的词何必押韵
就算我是k歌之王
也不见得把爱情唱得完美
只能说我输了
也许是你怕了
我们的回忆没有皱折
你却用离开烫下句点
只能说我认了
你的不安得到你信任
我却得到你安慰的淘汰
end
曲:周杰伦词:周杰伦
编曲: c y kong
监制: davy chan c y kong 陈奕迅
我说了所有的谎
你全都相信
简单的我爱你
你却老不信
你书里的剧情我不想上演
因为我喜欢喜剧收尾
我试过完美放弃
的确很踏实
醒来了梦散了
你我都走散了
情歌的词何必押韵
就算我是k歌之王
也不见得把爱情唱得完美
只能说我输了
也许是你怕了
我们的回忆没有皱折
你却用离开烫下句点
只能说我认了
你的不安得到你信任
我却得到你安慰的淘汰
end
bat文件的基本应用
bat是dos下的批处理文件
.cmd是nt内核命令行环境的另一种批处理文件
从更广义的角度来看,unix的shell脚本以及其它操作系统甚至应用程序中由外壳进行解释执行的文本,都具有与批处理文件十分相似的作用,而且同样是由专用解释器以行为单位解释执行,这种文本形式更通用的称谓是脚本语言。所以从某个程度分析,batch, unix shell, awk, basic, perl 等脚本语言都是一样的,只不过应用的范围和解释的平台各有不同而已。甚至有些应用程序仍然沿用批处理这一称呼,而其内容和扩展名与dos的批处理却又完全不同。
===================================
首先批处理文件是一个文本文件,这个文件的每一行都是一条DOS命令(大部分时候就好象我们在DOS提示符下执行的命令行一样),你可以使用DOS下的Edit或者Windows的记事本(notepad)等任何文本文件编辑工具创建和修改批处理文件。
==== 注 ===================
批处理文件中完全可以使用非dos命令,甚至可以使用不具有可执行特性的普通数据性文件,这缘于windows系统这个新型解释平台的涉入,使得批处理的应用越来越"边缘化"。所以我们讨论的批处理应该限定在dos环境或者命令行环境中,否则很多观念和设定都需要做比较大的变动。
========================
其次,批处理文件是一种简单的程序,可以通过条件语句(if)和流程控制语句(goto)来控制命令运行的流程,在批处理中也可以使用循环语句(for)来循环执行一条命令。当然,批处理文件的编程能力与C语言等编程语句比起来是十分有限的,也是十分不规范的。批处理的程序语句就是一条条的DOS命令(包括内部命令和外部命令),而批处理的能力主要取决于你所使用的命令。
==== 注 ==================
批处理文件(batch file)也可以称之为批处理程序(batch program),这一点与编译型语言有所不同,就c语言来说,扩展名为c或者cpp的文件可以称之为c语言文件或者c语言源代码,但只有编译连接后的exe文件才可以称之为c语言程序。因为批处理文件本身既具有文本的可读性,又具有程序的可执行性,这些称谓的界限是比较模糊的。
===========================
第三,每个编写好的批处理文件都相当于一个DOS的外部命令,你可以把它所在的目录放到你的DOS搜索路径(path)中来使得它可以在任意位置运行。一个良好的习惯是在硬盘上建立一个bat或者batch目录(例如C:\BATCH),然后将所有你编写的批处理文件放到该目录中,这样只要在path中设置上c:\batch,你就可以在任意位置运行所有你编写的批处理程序。
==== 注 =====
纯以dos系统而言,可执行程序大约可以细分为五类,依照执行优先级由高到低排列分别是:DOSKEY宏命令(预先驻留内存),COMMAND.COM中的内部命令(根据内存的环境随时进驻内存),以com为扩展名的可执行程序(由command.com 直接载入内存),以exe位扩展名的可执行程序(由command.com 重定位后载入内存),以bat位扩展名的批处理程序(由command.com 解释分析,根据其内容按优先级顺序调用第2,3,4,5种可执行程序,分析一行,执行一行,文件本身不载入内存)
============
第四,在DOS和Win9x/Me系统下,C:盘根目录下的AUTOEXEC.BAT批处理文件是自动运行批处理文件,每次系统启动时会自动运行该文件,你可以将系统每次启动时都要运行的命令放入该文件中,例如设置搜索路径,调入鼠标驱动和磁盘缓存,设置系统环境变量等。下面是一个运行于Windows 98下的autoexec.bat的示例:
@ECHO OFF
PATH C:\WINDOWS;C:\WINDOWS\COMMAND;C:\UCDOS;C:\DOSTools;
C:\SYSTOOLS;C:\WINTOOLS;C:\BATCH
LH SMARTDRV.EXE /X
LH DOSKEY.COM /insert
LH CTMOUSE.EXE
SET TEMP=D:\TEMP
SET TMP=D:\TEMP
==== 注 =====
AUTOEXEC.BAT为DOS系统的自动运行批处理文件,由COMMAND.COM启动时解释执行;
而在Win9x环境中,不仅增加支持了 DOSSTART.BAT, WINSTART.BAT 等许多其它自动运行的批处理文件,对AUTOEXEC.BAT 也增加了 .DOS .W40 .BAK .OLD .PWS 等许多变体以适应复杂的环境和多变的需求。
==== willsort 编注 =============
以下关于命令的分类,有很多值得推敲的地方。常用命令中的@本不是命令,而dir、copy等也很常用的命令却没有列入, 而特殊命令中所有命令对我来说都是常用命令。建议将批处理所引用的命令分为内部命令、外部命令、第三方程序三类。而内部命令和外部命令中别有一类是专用于或常用于批处理中的命令可称之为"批处理命令"。
以下摘录MS-DOS 6.22 帮助文档中关于"批处理命令"的文字,当然,其中有些概念和定义已经有些落后了。
批处理命令
批处理文件或批处理程序是一个包含若干MS-DOS命令的正文文件,扩展名为.BAT。当在命令提示符下敲入批处理程序的名称时,MS-DOS成组执行此批处理程序中的命令。
任何在命令提示符下可使用的命令都可用在批处理程序中。此外,下面MS-DOS命令是专门在批处理程序中使用的。
==========
常用命令
echo、@、call、pause、rem(小技巧:用::代替rem)是批处理文件最常用的几个命令,我们就从他们开始学起。
==== 注 ===========
首先, @ 不是一个命令, 而是DOS 批处理的一个特殊标记符, 仅用于屏蔽命令行回显. 下面是DOS命令行或批处理中可能会见到的一些特殊标记符:
CR(0D) 命令行结束符
Escape(1B) ANSI转义字符引导符
Space(20) 常用的参数界定符
Tab(09) ; = 不常用的参数界定符
+ COPY命令文件连接符
* ? 文件通配符
"" 字符串界定符
| 命令管道符
< > >> 文件重定向符
@ 命令行回显屏蔽符
/ 参数开关引导符
: 批处理标签引导符
% 批处理变量引导符
其次, :: 确实可以起到rem 的注释作用, 而且更简洁有效; 但有两点需要注意:
第一, 除了 :: 之外, 任何以 :开头的字符行, 在批处理中都被视作标号, 而直接忽略其后的所有内容, 只是为了与正常的标号相区别, 建议使用 goto 所无法识别的标号, 即在 :后紧跟一个非字母数字的一个特殊符号.
第二, 与rem 不同的是, ::后的字符行在执行时不会回显, 无论是否用echo on打开命令行回显状态, 因为命令解释器不认为他是一个有效的命令行, 就此点来看, rem 在某些场合下将比 :: 更为适用; 另外, rem 可以用于 config.sys 文件中.
=====================
echo 表示显示此命令后的字符
echo off 表示在此语句后所有运行的命令都不显示命令行本身
@与echo off相象,但它是加在每个命令行的最前面,表示运行时不显示这一行的命令行(只能影响当前行)。
call 调用另一个批处理文件(如果不用call而直接调用别的批处理文件,那么执行完那个批处理文件后将无法返回当前文件并执行当前文件的后续命令)。
pause 运行此句会暂停批处理的执行并在屏幕上显示Press any key to continue...的提示,等待用户按任意键后继续
rem 表示此命令后的字符为解释行(注释),不执行,只是给自己今后参考用的(相当于程序中的注释)。
==== 注 =====
此处的描述较为混乱, 不如直接引用个命令的命令行帮助更为条理
-------------------------
ECHO
当程序运行时,显示或隐藏批处理程序中的正文。也可用于允许或禁止命令的回显。
在运行批处理程序时,MS-DOS一般在屏幕上显示(回显)批处理程序中的命令。
使用ECHO命令可关闭此功能。
语法
ECHO [ON|OFF]
若要用echo命令显示一条命令,可用下述语法:
echo [message]
参数
ON|OFF
指定是否允许命令的回显。若要显示当前的ECHO的设置,可使用不带参数的ECHO
命令。
message
指定让MS-DOS在屏幕上显示的正文。
-------------------
CALL
从一个批处理程序中调用另一个批处理程序,而不会引起第一个批处理的中止。
语法
CALL [drive:][path]filename [batch-parameters]
参数
[drive:][path]filename
指定要调用的批处理程序的名字及其存放处。文件名必须用.BAT作扩展名。
batch-parameters
指定批处理程序所需的命令行信息。
-------------------------------
PAUSE
暂停批处理程序的执行并显示一条消息,提示用户按任意键继续执行。只能在批处
理程序中使用该命令。
语法
PAUSE
REM
在批处理文件或CONFIG.SYS中加入注解。也可用REM命令来屏蔽命令(在CONFIG.SYS
中也可以用分号 ; 代替REM命令,但在批处理文件中则不能替代)。
语法
REM [string]
参数
string
指定要屏蔽的命令或要包含的注解。
=======================
例1:用edit编辑a.bat文件,输入下列内容后存盘为c:\a.bat,执行该批处理文件后可实现:将根目录中所有文件写入 a.txt中,启动UCDOS,进入WPS等功能。
批处理文件的内容为: 命令注释:
@echo off 不显示后续命令行及当前命令行
dir c:\*.* >a.txt 将c盘文件列表写入a.txt
call c:\ucdos\ucdos.bat 调用ucdos
echo 你好 显示"你好"
pause 暂停,等待按键继续
rem 准备运行wps 注释:准备运行wps
cd ucdos 进入ucdos目录
wps 运行wps
批处理文件的参数
批处理文件还可以像C语言的函数一样使用参数(相当于DOS命令的命令行参数),这需要用到一个参数表示符"%"。
%[1-9]表示参数,参数是指在运行批处理文件时在文件名后加的以空格(或者Tab)分隔的字符串。变量可以从%0到%9,%0表示批处理命令本身,其它参数字符串用%1到%9顺序表示。
例2:C:根目录下有一批处理文件名为f.bat,内容为:
@echo off
format %1
如果执行C:\>f a:
那么在执行f.bat时,%1就表示a:,这样format %1就相当于format a:,于是上面的命令运行时实际执行的是format a:
例3:C:根目录下一批处理文件名为t.bat,内容为:
@echo off
type %1
type %2
那么运行C:\>t a.txt b.txt
%1 : 表示a.txt
%2 : 表示b.txt
于是上面的命令将顺序地显示a.txt和b.txt文件的内容。
==== 注 ===============
参数在批处理中也作为变量处理, 所以同样使用百分号作为引导符, 其后跟0-9中的一个数字构成参数引用符. 引用符和参数之间 (例如上文中的 %1 与 a: ) 的关系类似于变量指针与变量值的关系. 当我们要引用第十一个或更多个参数时, 就必须移动DOS 的参数起始指针. shift 命令正充当了这个移动指针的角色, 它将参数的起始指针移动到下一个参数, 类似C 语言中的指针操作. 图示如下:
初始状态, cmd 为命令名, 可以用 %0 引用
cmd arg1 arg2 arg3 arg4 arg5 arg6 arg7 arg8 arg9 arg10
^ ^ ^ ^ ^ ^ ^ ^ ^ ^
| | | | | | | | | |
%0 %1 %2 %3 %4 %5 %6 %7 %8 %9
经过1次shift后, cmd 将无法被引用
cmd arg1 arg2 arg3 arg4 arg5 arg6 arg7 arg8 arg9 arg10
^ ^ ^ ^ ^ ^ ^ ^ ^ ^
| | | | | | | | | |
%0 %1 %2 %3 %4 %5 %6 %7 %8 %9
经过2次shift后, arg1也被废弃, %9指向为空, 没有引用意义
cmd arg1 arg2 arg3 arg4 arg5 arg6 arg7 arg8 arg9 arg10
^ ^ ^ ^ ^ ^ ^ ^ ^
| | | | | | | | |
%0 %1 %2 %3 %4 %5 %6 %7 %8
遗憾的是, win9x 和DOS下均不支持 shift 的逆操作. 只有在 nt 内核命令行环境下, shift 才支持 /n 参数, 可以以第一参数为基准返复移动起始指针.
=================
特殊命令
if goto choice for是批处理文件中比较高级的命令,如果这几个你用得很熟练,你就是批处理文件的专家啦。
一、if 是条件语句,用来判断是否符合规定的条件,从而决定执行不同的命令。 有三种格式:
1、if [not] "参数" == "字符串" 待执行的命令
参数如果等于(not表示不等,下同)指定的字符串,则条件成立,运行命令,否则运行下一句。
例:if "%1"=="a" format a:
====
if 的命令行帮助中关于此点的描述为:
IF [NOT] string1==string2 command
在此有以下几点需要注意:
1. 包含字符串的双引号不是语法所必须的, 而只是习惯上使用的一种"防空"字符
2. string1 未必是参数, 它也可以是环境变量, 循环变量以及其他字符串常量或变量
3. command 不是语法所必须的, string2 后跟一个空格就可以构成一个有效的命令行
=============================
2、if [not] exist [路径\]文件名 待执行的命令
如果有指定的文件,则条件成立,运行命令,否则运行下一句。
如: if exist c:\config.sys type c:\config.sys
表示如果存在c:\config.sys文件,则显示它的内容。
****** 注 ********
也可以使用以下的用法:
if exist command
device 是指DOS系统中已加载的设备, 在win98下通常有:
AUX, PRN, CON, NUL
COM1, COM2, COM3, COM4
LPT1, LPT2, LPT3, LPT4
XMSXXXX0, EMMXXXX0
A: B: C: ...,
CLOCK$, CONFIG$, DblBuff$, IFS$HLP$
具体的内容会因硬软件环境的不同而略有差异, 使用这些设备名称时, 需要保证以下三点:
1. 该设备确实存在(由软件虚拟的设备除外)
2. 该设备驱动程序已加载(aux, prn等标准设备由系统缺省定义)
3. 该设备已准备好(主要是指a: b: ..., com1..., lpt1...等)
可通过命令 mem/d | find "device" /i 来检阅你的系统中所加载的设备
另外, 在DOS系统中, 设备也被认为是一种特殊的文件, 而文件也可以称作字符设备; 因为设备(device)与文件都是使用句柄(handle)来管理的, 句柄就是名字, 类似于文件名, 只不过句柄不是应用于磁盘管理, 而是应用于内存管理而已, 所谓设备加载也即指在内存中为其分配可引用的句柄.
==================================
3、if errorlevel <数字> 待执行的命令
很多DOS程序在运行结束后会返回一个数字值用来表示程序运行的结果(或者状态),通过if errorlevel命令可以判断程序的返回值,根据不同的返回值来决定执行不同的命令(返回值必须按照从大到小的顺序排列)。如果返回值等于指定的数字,则条件成立,运行命令,否则运行下一句。
如if errorlevel 2 goto x2
==== 注 ===========
返回值从大到小的顺序排列不是必须的, 而只是执行命令为 goto 时的习惯用法, 当使用 set 作为执行命令时, 通常会从小到大顺序排列, 比如需将返回码置入环境变量, 就需使用以下的顺序形式:
if errorlevel 1 set el=1
if errorlevel 2 set el=2
if errorlevel 3 set el=3
if errorlevel 4 set el=4
if errorlevel 5 set el=5
...
当然, 也可以使用以下循环来替代, 原理是一致的:
for %%e in (1 2 3 4 5 6 7 8...) do if errorlevel %%e set el=%%e
更高Ъ蚪嗟挠梅? 可以参考我写的另一篇关于获取 errorlevel 的文章
出现此种现象的原因是, if errorlevel 比较返回码的判断条件并非等于, 而是大于等于. 由于 goto 的跳转特性, 由小到大排序会导致在较小的返回码处就跳出; 而由于 set命令的 "重复" 赋值特性, 由大到小排序会导致较小的返回码 "覆盖" 较大的返回码.
另外, 虽然 if errorlevel=<数字> command 也是有效的命令行, 但也只是 command.com 解释命令行时将 = 作为命令行切分符而忽略掉罢了
===========================
二、goto 批处理文件运行到这里将跳到goto所指定的标号(标号即label,标号用:后跟标准字符串来定义)处,goto语句一般与if配合使用,根据不同的条件来执行不同的命令组。
如:
goto end
:end
echo this is the end
标号用":字符串"来定义,标号所在行不被执行。
==== willsort 编注
label 常被译为 "标签" , 但是这并不具有广泛的约定性.
goto 与 : 联用可实现执行中途的跳转, 再结合 if 可实现执行过程的条件分支, 多个 if 即可实现命令的分组, 类似 C 中 switch case 结构或者 Basic 中的 select case 结构, 大规模且结构化的命令分组即可实现高级语言中的函数功能. 以下是批处理和C/Basic在语法结构上的对照:
Batch C / Basic
goto&: goto&:
goto&:&if if{}&else{} / if&elseif&endif
goto&:&if... switch&case / select case
goto&:&if&set&envar... function() / function(),sub()
==================================
三、choice 使用此命令可以让用户输入一个字符(用于选择),从而根据用户的选择返回不同的errorlevel,然后于if errorlevel配合,根据用户的选择运行不同的命令。
注意:choice命令为DOS或者Windows系统提供的外部命令,不同版本的choice命令语法会稍有不同,请用choice /?查看用法。
choice的命令语法(该语法为Windows 2003中choice命令的语法,其它版本的choice的命令语法与此大同小异):
CHOICE [/C choices] [/N] [/CS] [/T timeout /D choice] [/M text]
描述:
该工具允许用户从选择列表选择一个项目并返回所选项目的索引。
参数列表:
/C choices 指定要创建的选项列表。默认列表是 "YN"。
/N 在提示符中隐藏选项列表。提示前面的消息得到显示,
选项依旧处于启用状态。
/CS 允许选择分大小写的选项。在默认情况下,这个工具
是不分大小写的。
/T timeout 做出默认选择之前,暂停的秒数。可接受的值是从 0
到 9999。如果指定了 0,就不会有暂停,默认选项
会得到选择。
/D choice 在 nnnn 秒之后指定默认选项。字符必须在用 /C 选
项指定的一组选择中; 同时,必须用 /T 指定 nnnn。
/M text 指定提示之前要显示的消息。如果没有指定,工具只
显示提示。
/? 显示帮助消息。
注意:
ERRORLEVEL 环境变量被设置为从选择集选择的键索引。列出的第一个选
择返回 1,第二个选择返回 2,等等。如果用户按的键不是有效的选择,
该工具会发出警告响声。如果该工具检测到错误状态,它会返回 255 的
ERRORLEVEL 值。如果用户按 Ctrl+Break 或 Ctrl+C 键,该工具会返回 0
的 ERRORLEVEL 值。在一个批程序中使用 ERRORLEVEL 参数时,将参数降
序排列。
示例:
CHOICE /?
CHOICE /C YNC /M "确认请按 Y,否请按 N,或者取消请按 C。"
CHOICE /T 10 /C ync /CS /D y
CHOICE /C ab /M "选项 1 请选择 a,选项 2 请选择 b。"
CHOICE /C ab /N /M "选项 1 请选择 a,选项 2 请选择 b。"
==== willsort 编注 ===============================
我列出win98下choice的用法帮助, 已资区分
Waits for the user to choose one of a set of choices.
等待用户选择一组待选字符中的一个
CHOICE [/C[:]choices] [/N] [/S] [/T[:]c,nn] [text]
/C[:]choices Specifies allowable keys. Default is YN
指定允许的按键(待选字符), 默认为YN
/N Do not display choices and ? at end of prompt string.
不显示提示字符串中的问号和待选字符
/S Treat choice keys as case sensitive.
处理待选字符时大小写敏感
/T[:]c,nn Default choice to c after nn seconds
在 nn 秒后默认选择 c
text Prompt string to display
要显示的提示字符串
ERRORLEVEL is set to offset of key user presses in choices.
ERRORLEVEL 被设置为用户键入的字符在待选字符中的偏移值
如果我运行命令:CHOICE /C YNC /M "确认请按 Y,否请按 N,或者取消请按 C。"
屏幕上会显示:
确认请按 Y,否请按 N,或者取消请按 C。 [Y,N,C]?
例:test.bat的内容如下(注意,用if errorlevel判断返回值时,要按返回值从高到低排列):
@echo off
choice /C dme /M "defrag,mem,end"
if errorlevel 3 goto end
if errorlevel 2 goto mem
if errorlevel 1 goto defrag
:defrag
c:\dos\defrag
goto end
:mem
mem
goto end
:end
echo good bye
此批处理运行后,将显示"defrag,mem,end[D,M,E]?" ,用户可选择d m e ,然后if语句根据用户的选择作出判断,d表示执行标号为defrag的程序段,m表示执行标号为mem的程序段,e表示执行标号为end的程序段,每个程序段最后都以goto end将程序跳到end标号处,然后程序将显示good bye,批处理运行结束。
四、for 循环命令,只要条件符合,它将多次执行同一命令。
语法:
对一组文件中的每一个文件执行某个特定命令。
FOR %%variable IN (set) DO command [command-parameters]
%%variable 指定一个单一字母可替换的参数。
(set) 指定一个或一组文件。可以使用通配符。
command 指定对每个文件执行的命令。
command-parameters
为特定命令指定参数或命令行开关。
例如一个批处理文件中有一行:
for %%c in (*.bat *.txt) do type %%c
则该命令行会显示当前目录下所有以bat和txt为扩展名的文件的内容。
==== willsort 编注 =====================================================
需要指出的是, 当()中的字符串并非单个或多个文件名时, 它将单纯被当作字符串替换, 这个特性再加上()中可以嵌入多个字符串的特性, 很明显 for 可以被看作一种遍历型循环.
当然, 在 nt/2000/xp/2003 系列的命令行环境中, for 被赋予了更多的特性, 使之可以分析命令输出或者文件中的字符串, 也有很多开关被用于扩展了文件替换功能.
========================================================================
批处理示例
1. IF-EXIST
1) 首先用记事本在C:\建立一个test1.bat批处理文件,文件内容如下:
@echo off
IF EXIST \AUTOEXEC.BAT TYPE \AUTOEXEC.BAT
IF NOT EXIST \AUTOEXEC.BAT ECHO \AUTOEXEC.BAT does not exist
然后运行它:
C:\>TEST1.BAT
如果C:\存在AUTOEXEC.BAT文件,那么它的内容就会被显示出来,如果不存在,批处理就会提示你该文件不存在。
2) 接着再建立一个test2.bat文件,内容如下:
@ECHO OFF
IF EXIST \%1 TYPE \%1
IF NOT EXIST \%1 ECHO \%1 does not exist
执行:
C:\>TEST2 AUTOEXEC.BAT
该命令运行结果同上。
说明:
(1) IF EXIST 是用来测试文件是否存在的,格式为
IF EXIST [路径+文件名] 命令
(2) test2.bat文件中的%1是参数,DOS允许传递9个批参数信息给批处理文件,分别为%1~%9(%0表示test2命令本身) ,这有点象编程中的实参和形参的关系,%1是形参,AUTOEXEC.BAT是实参。
==== willsort 编注 =====================================================
DOS没有 "允许传递9个批参数信息" 的限制, 参数的个数只会受到命令行长度和所调用命令处理能力的限制. 但是, 我们在批处理程序中, 在同一时刻只能同时引用10个参数, 因为 DOS只给出了 %0~%9这十个参数引用符.
========================================================================
3) 更进一步的,建立一个名为TEST3.BAT的文件,内容如下:
@echo off
IF "%1" == "A" ECHO XIAO
IF "%2" == "B" ECHO TIAN
IF "%3" == "C" ECHO XIN
如果运行:
C:\>TEST3 A B C
屏幕上会显示:
XIAO
TIAN
XIN
如果运行:
C:\>TEST3 A B
屏幕上会显示
XIAO
TIAN
在这个命令执行过程中,DOS会将一个空字符串指定给参数%3。
2、IF-ERRORLEVEL
建立TEST4.BAT,内容如下:
@ECHO OFF
XCOPY C:\AUTOEXEC.BAT D:\
IF ERRORLEVEL 1 ECHO 文件拷贝失败
IF ERRORLEVEL 0 ECHO 成功拷贝文件
然后执行文件:
C:\>TEST4
如果文件拷贝成功,屏幕就会显示"成功拷贝文件",否则就会显示"文件拷贝失败"。
IF ERRORLEVEL 是用来测试它的上一个DOS命令的返回值的,注意只是上一个命令的返回值,而且返回值必须依照从大到小次序顺序判断。
因此下面的批处理文件是错误的:
@ECHO OFF
XCOPY C:\AUTOEXEC.BAT D:\
IF ERRORLEVEL 0 ECHO 成功拷贝文件
IF ERRORLEVEL 1 ECHO 未找到拷贝文件
IF ERRORLEVEL 2 ECHO 用户通过ctrl-c中止拷贝操作
IF ERRORLEVEL 3 ECHO 预置错误阻止文件拷贝操作
IF ERRORLEVEL 4 ECHO 拷贝过程中写盘错误
无论拷贝是否成功,后面的:
未找到拷贝文件
用户通过ctrl-c中止拷贝操作
预置错误阻止文件拷贝操作
拷贝过程中写盘错误
都将显示出来。
以下就是几个常用命令的返回值及其代表的意义:
backup
0 备份成功
1 未找到备份文件
2 文件共享冲突阻止备份完成
3 用户用ctrl-c中止备份
4 由于致命的错误使备份操作中止
diskcomp
0 盘比较相同
1 盘比较不同
2 用户通过ctrl-c中止比较操作
3 由于致命的错误使比较操作中止
4 预置错误中止比较
diskcopy
0 盘拷贝操作成功
1 非致命盘读/写错
2 用户通过ctrl-c结束拷贝操作
3 因致命的处理错误使盘拷贝中止
4 预置错误阻止拷贝操作
format
0 格式化成功
3 用户
.cmd是nt内核命令行环境的另一种批处理文件
从更广义的角度来看,unix的shell脚本以及其它操作系统甚至应用程序中由外壳进行解释执行的文本,都具有与批处理文件十分相似的作用,而且同样是由专用解释器以行为单位解释执行,这种文本形式更通用的称谓是脚本语言。所以从某个程度分析,batch, unix shell, awk, basic, perl 等脚本语言都是一样的,只不过应用的范围和解释的平台各有不同而已。甚至有些应用程序仍然沿用批处理这一称呼,而其内容和扩展名与dos的批处理却又完全不同。
===================================
首先批处理文件是一个文本文件,这个文件的每一行都是一条DOS命令(大部分时候就好象我们在DOS提示符下执行的命令行一样),你可以使用DOS下的Edit或者Windows的记事本(notepad)等任何文本文件编辑工具创建和修改批处理文件。
==== 注 ===================
批处理文件中完全可以使用非dos命令,甚至可以使用不具有可执行特性的普通数据性文件,这缘于windows系统这个新型解释平台的涉入,使得批处理的应用越来越"边缘化"。所以我们讨论的批处理应该限定在dos环境或者命令行环境中,否则很多观念和设定都需要做比较大的变动。
========================
其次,批处理文件是一种简单的程序,可以通过条件语句(if)和流程控制语句(goto)来控制命令运行的流程,在批处理中也可以使用循环语句(for)来循环执行一条命令。当然,批处理文件的编程能力与C语言等编程语句比起来是十分有限的,也是十分不规范的。批处理的程序语句就是一条条的DOS命令(包括内部命令和外部命令),而批处理的能力主要取决于你所使用的命令。
==== 注 ==================
批处理文件(batch file)也可以称之为批处理程序(batch program),这一点与编译型语言有所不同,就c语言来说,扩展名为c或者cpp的文件可以称之为c语言文件或者c语言源代码,但只有编译连接后的exe文件才可以称之为c语言程序。因为批处理文件本身既具有文本的可读性,又具有程序的可执行性,这些称谓的界限是比较模糊的。
===========================
第三,每个编写好的批处理文件都相当于一个DOS的外部命令,你可以把它所在的目录放到你的DOS搜索路径(path)中来使得它可以在任意位置运行。一个良好的习惯是在硬盘上建立一个bat或者batch目录(例如C:\BATCH),然后将所有你编写的批处理文件放到该目录中,这样只要在path中设置上c:\batch,你就可以在任意位置运行所有你编写的批处理程序。
==== 注 =====
纯以dos系统而言,可执行程序大约可以细分为五类,依照执行优先级由高到低排列分别是:DOSKEY宏命令(预先驻留内存),COMMAND.COM中的内部命令(根据内存的环境随时进驻内存),以com为扩展名的可执行程序(由command.com 直接载入内存),以exe位扩展名的可执行程序(由command.com 重定位后载入内存),以bat位扩展名的批处理程序(由command.com 解释分析,根据其内容按优先级顺序调用第2,3,4,5种可执行程序,分析一行,执行一行,文件本身不载入内存)
============
第四,在DOS和Win9x/Me系统下,C:盘根目录下的AUTOEXEC.BAT批处理文件是自动运行批处理文件,每次系统启动时会自动运行该文件,你可以将系统每次启动时都要运行的命令放入该文件中,例如设置搜索路径,调入鼠标驱动和磁盘缓存,设置系统环境变量等。下面是一个运行于Windows 98下的autoexec.bat的示例:
@ECHO OFF
PATH C:\WINDOWS;C:\WINDOWS\COMMAND;C:\UCDOS;C:\DOSTools;
C:\SYSTOOLS;C:\WINTOOLS;C:\BATCH
LH SMARTDRV.EXE /X
LH DOSKEY.COM /insert
LH CTMOUSE.EXE
SET TEMP=D:\TEMP
SET TMP=D:\TEMP
==== 注 =====
AUTOEXEC.BAT为DOS系统的自动运行批处理文件,由COMMAND.COM启动时解释执行;
而在Win9x环境中,不仅增加支持了 DOSSTART.BAT, WINSTART.BAT 等许多其它自动运行的批处理文件,对AUTOEXEC.BAT 也增加了 .DOS .W40 .BAK .OLD .PWS 等许多变体以适应复杂的环境和多变的需求。
==== willsort 编注 =============
以下关于命令的分类,有很多值得推敲的地方。常用命令中的@本不是命令,而dir、copy等也很常用的命令却没有列入, 而特殊命令中所有命令对我来说都是常用命令。建议将批处理所引用的命令分为内部命令、外部命令、第三方程序三类。而内部命令和外部命令中别有一类是专用于或常用于批处理中的命令可称之为"批处理命令"。
以下摘录MS-DOS 6.22 帮助文档中关于"批处理命令"的文字,当然,其中有些概念和定义已经有些落后了。
批处理命令
批处理文件或批处理程序是一个包含若干MS-DOS命令的正文文件,扩展名为.BAT。当在命令提示符下敲入批处理程序的名称时,MS-DOS成组执行此批处理程序中的命令。
任何在命令提示符下可使用的命令都可用在批处理程序中。此外,下面MS-DOS命令是专门在批处理程序中使用的。
==========
常用命令
echo、@、call、pause、rem(小技巧:用::代替rem)是批处理文件最常用的几个命令,我们就从他们开始学起。
==== 注 ===========
首先, @ 不是一个命令, 而是DOS 批处理的一个特殊标记符, 仅用于屏蔽命令行回显. 下面是DOS命令行或批处理中可能会见到的一些特殊标记符:
CR(0D) 命令行结束符
Escape(1B) ANSI转义字符引导符
Space(20) 常用的参数界定符
Tab(09) ; = 不常用的参数界定符
+ COPY命令文件连接符
* ? 文件通配符
"" 字符串界定符
| 命令管道符
< > >> 文件重定向符
@ 命令行回显屏蔽符
/ 参数开关引导符
: 批处理标签引导符
% 批处理变量引导符
其次, :: 确实可以起到rem 的注释作用, 而且更简洁有效; 但有两点需要注意:
第一, 除了 :: 之外, 任何以 :开头的字符行, 在批处理中都被视作标号, 而直接忽略其后的所有内容, 只是为了与正常的标号相区别, 建议使用 goto 所无法识别的标号, 即在 :后紧跟一个非字母数字的一个特殊符号.
第二, 与rem 不同的是, ::后的字符行在执行时不会回显, 无论是否用echo on打开命令行回显状态, 因为命令解释器不认为他是一个有效的命令行, 就此点来看, rem 在某些场合下将比 :: 更为适用; 另外, rem 可以用于 config.sys 文件中.
=====================
echo 表示显示此命令后的字符
echo off 表示在此语句后所有运行的命令都不显示命令行本身
@与echo off相象,但它是加在每个命令行的最前面,表示运行时不显示这一行的命令行(只能影响当前行)。
call 调用另一个批处理文件(如果不用call而直接调用别的批处理文件,那么执行完那个批处理文件后将无法返回当前文件并执行当前文件的后续命令)。
pause 运行此句会暂停批处理的执行并在屏幕上显示Press any key to continue...的提示,等待用户按任意键后继续
rem 表示此命令后的字符为解释行(注释),不执行,只是给自己今后参考用的(相当于程序中的注释)。
==== 注 =====
此处的描述较为混乱, 不如直接引用个命令的命令行帮助更为条理
-------------------------
ECHO
当程序运行时,显示或隐藏批处理程序中的正文。也可用于允许或禁止命令的回显。
在运行批处理程序时,MS-DOS一般在屏幕上显示(回显)批处理程序中的命令。
使用ECHO命令可关闭此功能。
语法
ECHO [ON|OFF]
若要用echo命令显示一条命令,可用下述语法:
echo [message]
参数
ON|OFF
指定是否允许命令的回显。若要显示当前的ECHO的设置,可使用不带参数的ECHO
命令。
message
指定让MS-DOS在屏幕上显示的正文。
-------------------
CALL
从一个批处理程序中调用另一个批处理程序,而不会引起第一个批处理的中止。
语法
CALL [drive:][path]filename [batch-parameters]
参数
[drive:][path]filename
指定要调用的批处理程序的名字及其存放处。文件名必须用.BAT作扩展名。
batch-parameters
指定批处理程序所需的命令行信息。
-------------------------------
PAUSE
暂停批处理程序的执行并显示一条消息,提示用户按任意键继续执行。只能在批处
理程序中使用该命令。
语法
PAUSE
REM
在批处理文件或CONFIG.SYS中加入注解。也可用REM命令来屏蔽命令(在CONFIG.SYS
中也可以用分号 ; 代替REM命令,但在批处理文件中则不能替代)。
语法
REM [string]
参数
string
指定要屏蔽的命令或要包含的注解。
=======================
例1:用edit编辑a.bat文件,输入下列内容后存盘为c:\a.bat,执行该批处理文件后可实现:将根目录中所有文件写入 a.txt中,启动UCDOS,进入WPS等功能。
批处理文件的内容为: 命令注释:
@echo off 不显示后续命令行及当前命令行
dir c:\*.* >a.txt 将c盘文件列表写入a.txt
call c:\ucdos\ucdos.bat 调用ucdos
echo 你好 显示"你好"
pause 暂停,等待按键继续
rem 准备运行wps 注释:准备运行wps
cd ucdos 进入ucdos目录
wps 运行wps
批处理文件的参数
批处理文件还可以像C语言的函数一样使用参数(相当于DOS命令的命令行参数),这需要用到一个参数表示符"%"。
%[1-9]表示参数,参数是指在运行批处理文件时在文件名后加的以空格(或者Tab)分隔的字符串。变量可以从%0到%9,%0表示批处理命令本身,其它参数字符串用%1到%9顺序表示。
例2:C:根目录下有一批处理文件名为f.bat,内容为:
@echo off
format %1
如果执行C:\>f a:
那么在执行f.bat时,%1就表示a:,这样format %1就相当于format a:,于是上面的命令运行时实际执行的是format a:
例3:C:根目录下一批处理文件名为t.bat,内容为:
@echo off
type %1
type %2
那么运行C:\>t a.txt b.txt
%1 : 表示a.txt
%2 : 表示b.txt
于是上面的命令将顺序地显示a.txt和b.txt文件的内容。
==== 注 ===============
参数在批处理中也作为变量处理, 所以同样使用百分号作为引导符, 其后跟0-9中的一个数字构成参数引用符. 引用符和参数之间 (例如上文中的 %1 与 a: ) 的关系类似于变量指针与变量值的关系. 当我们要引用第十一个或更多个参数时, 就必须移动DOS 的参数起始指针. shift 命令正充当了这个移动指针的角色, 它将参数的起始指针移动到下一个参数, 类似C 语言中的指针操作. 图示如下:
初始状态, cmd 为命令名, 可以用 %0 引用
cmd arg1 arg2 arg3 arg4 arg5 arg6 arg7 arg8 arg9 arg10
^ ^ ^ ^ ^ ^ ^ ^ ^ ^
| | | | | | | | | |
%0 %1 %2 %3 %4 %5 %6 %7 %8 %9
经过1次shift后, cmd 将无法被引用
cmd arg1 arg2 arg3 arg4 arg5 arg6 arg7 arg8 arg9 arg10
^ ^ ^ ^ ^ ^ ^ ^ ^ ^
| | | | | | | | | |
%0 %1 %2 %3 %4 %5 %6 %7 %8 %9
经过2次shift后, arg1也被废弃, %9指向为空, 没有引用意义
cmd arg1 arg2 arg3 arg4 arg5 arg6 arg7 arg8 arg9 arg10
^ ^ ^ ^ ^ ^ ^ ^ ^
| | | | | | | | |
%0 %1 %2 %3 %4 %5 %6 %7 %8
遗憾的是, win9x 和DOS下均不支持 shift 的逆操作. 只有在 nt 内核命令行环境下, shift 才支持 /n 参数, 可以以第一参数为基准返复移动起始指针.
=================
特殊命令
if goto choice for是批处理文件中比较高级的命令,如果这几个你用得很熟练,你就是批处理文件的专家啦。
一、if 是条件语句,用来判断是否符合规定的条件,从而决定执行不同的命令。 有三种格式:
1、if [not] "参数" == "字符串" 待执行的命令
参数如果等于(not表示不等,下同)指定的字符串,则条件成立,运行命令,否则运行下一句。
例:if "%1"=="a" format a:
====
if 的命令行帮助中关于此点的描述为:
IF [NOT] string1==string2 command
在此有以下几点需要注意:
1. 包含字符串的双引号不是语法所必须的, 而只是习惯上使用的一种"防空"字符
2. string1 未必是参数, 它也可以是环境变量, 循环变量以及其他字符串常量或变量
3. command 不是语法所必须的, string2 后跟一个空格就可以构成一个有效的命令行
=============================
2、if [not] exist [路径\]文件名 待执行的命令
如果有指定的文件,则条件成立,运行命令,否则运行下一句。
如: if exist c:\config.sys type c:\config.sys
表示如果存在c:\config.sys文件,则显示它的内容。
****** 注 ********
也可以使用以下的用法:
if exist command
device 是指DOS系统中已加载的设备, 在win98下通常有:
AUX, PRN, CON, NUL
COM1, COM2, COM3, COM4
LPT1, LPT2, LPT3, LPT4
XMSXXXX0, EMMXXXX0
A: B: C: ...,
CLOCK$, CONFIG$, DblBuff$, IFS$HLP$
具体的内容会因硬软件环境的不同而略有差异, 使用这些设备名称时, 需要保证以下三点:
1. 该设备确实存在(由软件虚拟的设备除外)
2. 该设备驱动程序已加载(aux, prn等标准设备由系统缺省定义)
3. 该设备已准备好(主要是指a: b: ..., com1..., lpt1...等)
可通过命令 mem/d | find "device" /i 来检阅你的系统中所加载的设备
另外, 在DOS系统中, 设备也被认为是一种特殊的文件, 而文件也可以称作字符设备; 因为设备(device)与文件都是使用句柄(handle)来管理的, 句柄就是名字, 类似于文件名, 只不过句柄不是应用于磁盘管理, 而是应用于内存管理而已, 所谓设备加载也即指在内存中为其分配可引用的句柄.
==================================
3、if errorlevel <数字> 待执行的命令
很多DOS程序在运行结束后会返回一个数字值用来表示程序运行的结果(或者状态),通过if errorlevel命令可以判断程序的返回值,根据不同的返回值来决定执行不同的命令(返回值必须按照从大到小的顺序排列)。如果返回值等于指定的数字,则条件成立,运行命令,否则运行下一句。
如if errorlevel 2 goto x2
==== 注 ===========
返回值从大到小的顺序排列不是必须的, 而只是执行命令为 goto 时的习惯用法, 当使用 set 作为执行命令时, 通常会从小到大顺序排列, 比如需将返回码置入环境变量, 就需使用以下的顺序形式:
if errorlevel 1 set el=1
if errorlevel 2 set el=2
if errorlevel 3 set el=3
if errorlevel 4 set el=4
if errorlevel 5 set el=5
...
当然, 也可以使用以下循环来替代, 原理是一致的:
for %%e in (1 2 3 4 5 6 7 8...) do if errorlevel %%e set el=%%e
更高Ъ蚪嗟挠梅? 可以参考我写的另一篇关于获取 errorlevel 的文章
出现此种现象的原因是, if errorlevel 比较返回码的判断条件并非等于, 而是大于等于. 由于 goto 的跳转特性, 由小到大排序会导致在较小的返回码处就跳出; 而由于 set命令的 "重复" 赋值特性, 由大到小排序会导致较小的返回码 "覆盖" 较大的返回码.
另外, 虽然 if errorlevel=<数字> command 也是有效的命令行, 但也只是 command.com 解释命令行时将 = 作为命令行切分符而忽略掉罢了
===========================
二、goto 批处理文件运行到这里将跳到goto所指定的标号(标号即label,标号用:后跟标准字符串来定义)处,goto语句一般与if配合使用,根据不同的条件来执行不同的命令组。
如:
goto end
:end
echo this is the end
标号用":字符串"来定义,标号所在行不被执行。
==== willsort 编注
label 常被译为 "标签" , 但是这并不具有广泛的约定性.
goto 与 : 联用可实现执行中途的跳转, 再结合 if 可实现执行过程的条件分支, 多个 if 即可实现命令的分组, 类似 C 中 switch case 结构或者 Basic 中的 select case 结构, 大规模且结构化的命令分组即可实现高级语言中的函数功能. 以下是批处理和C/Basic在语法结构上的对照:
Batch C / Basic
goto&: goto&:
goto&:&if if{}&else{} / if&elseif&endif
goto&:&if... switch&case / select case
goto&:&if&set&envar... function() / function(),sub()
==================================
三、choice 使用此命令可以让用户输入一个字符(用于选择),从而根据用户的选择返回不同的errorlevel,然后于if errorlevel配合,根据用户的选择运行不同的命令。
注意:choice命令为DOS或者Windows系统提供的外部命令,不同版本的choice命令语法会稍有不同,请用choice /?查看用法。
choice的命令语法(该语法为Windows 2003中choice命令的语法,其它版本的choice的命令语法与此大同小异):
CHOICE [/C choices] [/N] [/CS] [/T timeout /D choice] [/M text]
描述:
该工具允许用户从选择列表选择一个项目并返回所选项目的索引。
参数列表:
/C choices 指定要创建的选项列表。默认列表是 "YN"。
/N 在提示符中隐藏选项列表。提示前面的消息得到显示,
选项依旧处于启用状态。
/CS 允许选择分大小写的选项。在默认情况下,这个工具
是不分大小写的。
/T timeout 做出默认选择之前,暂停的秒数。可接受的值是从 0
到 9999。如果指定了 0,就不会有暂停,默认选项
会得到选择。
/D choice 在 nnnn 秒之后指定默认选项。字符必须在用 /C 选
项指定的一组选择中; 同时,必须用 /T 指定 nnnn。
/M text 指定提示之前要显示的消息。如果没有指定,工具只
显示提示。
/? 显示帮助消息。
注意:
ERRORLEVEL 环境变量被设置为从选择集选择的键索引。列出的第一个选
择返回 1,第二个选择返回 2,等等。如果用户按的键不是有效的选择,
该工具会发出警告响声。如果该工具检测到错误状态,它会返回 255 的
ERRORLEVEL 值。如果用户按 Ctrl+Break 或 Ctrl+C 键,该工具会返回 0
的 ERRORLEVEL 值。在一个批程序中使用 ERRORLEVEL 参数时,将参数降
序排列。
示例:
CHOICE /?
CHOICE /C YNC /M "确认请按 Y,否请按 N,或者取消请按 C。"
CHOICE /T 10 /C ync /CS /D y
CHOICE /C ab /M "选项 1 请选择 a,选项 2 请选择 b。"
CHOICE /C ab /N /M "选项 1 请选择 a,选项 2 请选择 b。"
==== willsort 编注 ===============================
我列出win98下choice的用法帮助, 已资区分
Waits for the user to choose one of a set of choices.
等待用户选择一组待选字符中的一个
CHOICE [/C[:]choices] [/N] [/S] [/T[:]c,nn] [text]
/C[:]choices Specifies allowable keys. Default is YN
指定允许的按键(待选字符), 默认为YN
/N Do not display choices and ? at end of prompt string.
不显示提示字符串中的问号和待选字符
/S Treat choice keys as case sensitive.
处理待选字符时大小写敏感
/T[:]c,nn Default choice to c after nn seconds
在 nn 秒后默认选择 c
text Prompt string to display
要显示的提示字符串
ERRORLEVEL is set to offset of key user presses in choices.
ERRORLEVEL 被设置为用户键入的字符在待选字符中的偏移值
如果我运行命令:CHOICE /C YNC /M "确认请按 Y,否请按 N,或者取消请按 C。"
屏幕上会显示:
确认请按 Y,否请按 N,或者取消请按 C。 [Y,N,C]?
例:test.bat的内容如下(注意,用if errorlevel判断返回值时,要按返回值从高到低排列):
@echo off
choice /C dme /M "defrag,mem,end"
if errorlevel 3 goto end
if errorlevel 2 goto mem
if errorlevel 1 goto defrag
:defrag
c:\dos\defrag
goto end
:mem
mem
goto end
:end
echo good bye
此批处理运行后,将显示"defrag,mem,end[D,M,E]?" ,用户可选择d m e ,然后if语句根据用户的选择作出判断,d表示执行标号为defrag的程序段,m表示执行标号为mem的程序段,e表示执行标号为end的程序段,每个程序段最后都以goto end将程序跳到end标号处,然后程序将显示good bye,批处理运行结束。
四、for 循环命令,只要条件符合,它将多次执行同一命令。
语法:
对一组文件中的每一个文件执行某个特定命令。
FOR %%variable IN (set) DO command [command-parameters]
%%variable 指定一个单一字母可替换的参数。
(set) 指定一个或一组文件。可以使用通配符。
command 指定对每个文件执行的命令。
command-parameters
为特定命令指定参数或命令行开关。
例如一个批处理文件中有一行:
for %%c in (*.bat *.txt) do type %%c
则该命令行会显示当前目录下所有以bat和txt为扩展名的文件的内容。
==== willsort 编注 =====================================================
需要指出的是, 当()中的字符串并非单个或多个文件名时, 它将单纯被当作字符串替换, 这个特性再加上()中可以嵌入多个字符串的特性, 很明显 for 可以被看作一种遍历型循环.
当然, 在 nt/2000/xp/2003 系列的命令行环境中, for 被赋予了更多的特性, 使之可以分析命令输出或者文件中的字符串, 也有很多开关被用于扩展了文件替换功能.
========================================================================
批处理示例
1. IF-EXIST
1) 首先用记事本在C:\建立一个test1.bat批处理文件,文件内容如下:
@echo off
IF EXIST \AUTOEXEC.BAT TYPE \AUTOEXEC.BAT
IF NOT EXIST \AUTOEXEC.BAT ECHO \AUTOEXEC.BAT does not exist
然后运行它:
C:\>TEST1.BAT
如果C:\存在AUTOEXEC.BAT文件,那么它的内容就会被显示出来,如果不存在,批处理就会提示你该文件不存在。
2) 接着再建立一个test2.bat文件,内容如下:
@ECHO OFF
IF EXIST \%1 TYPE \%1
IF NOT EXIST \%1 ECHO \%1 does not exist
执行:
C:\>TEST2 AUTOEXEC.BAT
该命令运行结果同上。
说明:
(1) IF EXIST 是用来测试文件是否存在的,格式为
IF EXIST [路径+文件名] 命令
(2) test2.bat文件中的%1是参数,DOS允许传递9个批参数信息给批处理文件,分别为%1~%9(%0表示test2命令本身) ,这有点象编程中的实参和形参的关系,%1是形参,AUTOEXEC.BAT是实参。
==== willsort 编注 =====================================================
DOS没有 "允许传递9个批参数信息" 的限制, 参数的个数只会受到命令行长度和所调用命令处理能力的限制. 但是, 我们在批处理程序中, 在同一时刻只能同时引用10个参数, 因为 DOS只给出了 %0~%9这十个参数引用符.
========================================================================
3) 更进一步的,建立一个名为TEST3.BAT的文件,内容如下:
@echo off
IF "%1" == "A" ECHO XIAO
IF "%2" == "B" ECHO TIAN
IF "%3" == "C" ECHO XIN
如果运行:
C:\>TEST3 A B C
屏幕上会显示:
XIAO
TIAN
XIN
如果运行:
C:\>TEST3 A B
屏幕上会显示
XIAO
TIAN
在这个命令执行过程中,DOS会将一个空字符串指定给参数%3。
2、IF-ERRORLEVEL
建立TEST4.BAT,内容如下:
@ECHO OFF
XCOPY C:\AUTOEXEC.BAT D:\
IF ERRORLEVEL 1 ECHO 文件拷贝失败
IF ERRORLEVEL 0 ECHO 成功拷贝文件
然后执行文件:
C:\>TEST4
如果文件拷贝成功,屏幕就会显示"成功拷贝文件",否则就会显示"文件拷贝失败"。
IF ERRORLEVEL 是用来测试它的上一个DOS命令的返回值的,注意只是上一个命令的返回值,而且返回值必须依照从大到小次序顺序判断。
因此下面的批处理文件是错误的:
@ECHO OFF
XCOPY C:\AUTOEXEC.BAT D:\
IF ERRORLEVEL 0 ECHO 成功拷贝文件
IF ERRORLEVEL 1 ECHO 未找到拷贝文件
IF ERRORLEVEL 2 ECHO 用户通过ctrl-c中止拷贝操作
IF ERRORLEVEL 3 ECHO 预置错误阻止文件拷贝操作
IF ERRORLEVEL 4 ECHO 拷贝过程中写盘错误
无论拷贝是否成功,后面的:
未找到拷贝文件
用户通过ctrl-c中止拷贝操作
预置错误阻止文件拷贝操作
拷贝过程中写盘错误
都将显示出来。
以下就是几个常用命令的返回值及其代表的意义:
backup
0 备份成功
1 未找到备份文件
2 文件共享冲突阻止备份完成
3 用户用ctrl-c中止备份
4 由于致命的错误使备份操作中止
diskcomp
0 盘比较相同
1 盘比较不同
2 用户通过ctrl-c中止比较操作
3 由于致命的错误使比较操作中止
4 预置错误中止比较
diskcopy
0 盘拷贝操作成功
1 非致命盘读/写错
2 用户通过ctrl-c结束拷贝操作
3 因致命的处理错误使盘拷贝中止
4 预置错误阻止拷贝操作
format
0 格式化成功
3 用户
Saturday, 19 May 2007
Lecture 14: Transactions and Recovery
A transaction is an action, or a series of actions, carried out by a single user or an application program, which reads or updates the contents of a database.
Atomicity
Transactions are atomic – they don’t have parts (conceptually)
So can’t be executed partially
Consistency
Transactions take the database from one consistent state into another
In the middle of a transaction the database might not be consistent
Isolation
The effects of a transaction are not visible to other transactions until it has completed
From outside the transaction has either happened or not
To me this actually sounds like a consequence of atomicity…
Durability
Once a transaction has completed, its changes are made permanent
Even if the system crashes, the effects of a transaction must remain in place
The Transaction Manager
The transaction manager enforces the ACID properties
It schedules the operations of transactions
COMMIT and ROLLBACK are used to ensure atomicity
Locks are used to ensure consistency and isolation for concurrent transactions (next lecture)
A log is kept to ensure durability in the event of system failure (this lecture)
COMMIT signals the successful end of a transaction
Any changes made by the transaction should be saved
These changes are now visible to other transactions
ROLLBACK signals the unsuccessful end of a transaction
Any changes made by the transaction should be undone
It is now as if the transaction never existed
Prevention is better than cure
Can’t protect against everything though
The transaction log records the details of all transactions
Any changes the transaction makes to the database
How to undo these changes
When transactions complete and how
The log is stored on disk, not in memory
If the system crashes it is preserved
Write ahead log rule
The entry in the log must be made before COMMIT processing can complete
A system failure means all running transactions are affected
Software crashes or Power failures
At various times a DBMS takes a checkpoint
All committed transactions are written to disk
A record is made (on disk) of the transactions that are currently running
Any transaction that was running at the time of failure needs to be undone and restarted
Any transactions that committed since the last checkpoint need to be redone
Transaction Recovery
UNDO = all transactions running at the last checkpoint
REDO = empty
Backwards recovery
We need to undo some transactions
Working backwards through the log we undo any operation by a transaction on the UNDO list
This returns the database to a consistent state
Forwards recovery
Some transactions need to be redone
Working forwards through the log we redo any operation by a transaction on the REDO list
This brings the database up to date
System failures are not too severe
Only information since the last checkpoint is affected
This can be recovered from the transaction log
Media failures (disk crashes etc) are more serious
The data stored to disk is damaged
The transaction log itself may be damaged
Backups are needed to recover from media failure
The transaction log and entire contents of the database is written to secondary storage (often tape)
Time consuming, and often requires down time
Backups frequency
Frequent enough that little information is lost
Not so frequent as to cause problems
Every day (night) is common
Backup storage
Restore the database from the last backup
Use the transaction log to redo any changes made since the last backup
If the transaction log is damaged you can’t do step 2
Store the log on a separate physical device to the database
The risk of losing both is then reduced
Lecture 15: Concurrency
A schedule is a sequence of the operations by a set of concurrent transactions that preserves the order of operations in each of the individual transactions
A serial schedule is a schedule where operations of each transaction are executed consecutively without any interleaved operations from other transactions (each transaction commits before the next one is allowed to begin)
Two schedules are equivalent if they always have the same effect.
A schedule is serialisable if it is equivalent to some serial schedule.
A schedule is conflict serialisable if transactions in the schedule have a conflict but the schedule is still serialisable
To determine if a schedule is conflict serialisable we use a precedence graph
Locking is a procedure used to control concurrent access to data (to ensure serialisability of concurrent transactions)
Two types of lock
Shared lock (S-lock or read-lock)
Exclusive lock (X-lock or write-lock)
Read lock allows several transactions simultaneously to read a resource (but no transactions can change it at the same time)
Write lock allows one transaction exclusive access to write to a resource. No other transaction can read this resource at the same time.
The lock manager in the DBMS assigns locks and records them in the data dictionary
Before reading from a resource a transaction must acquire a read-lock
Before writing to a resource a transaction must acquire a write-lock
Locks are released on commit/rollback
A transaction may not acquire a lock on any resource that is write-locked by another transaction
A transaction may not acquire a write-lock on a resource that is locked by another transaction
If the requested lock is not available, transaction waits
A transaction follows the two-phase locking protocol (2PL) if all locking operations precede the first unlock operation in the transaction
A deadlock is an impasse that may result when two or more transactions are waiting for locks to be released which are held by each
Given a schedule, we can detect deadlocks which will happen in this schedule using a wait-for graph (WFG).
An alternative is timestamping
Requires less overhead in terms of tracking locks or detecting deadlock
Determines the order of transactions before they are executed
The protocol means that transactions with higher times take precedence
Equivalent to running transactions in order of their final time values
Transactions don’t wait - no deadlock
Lecture 17: Security and Integrity
GRANT
ON
A transaction is an action, or a series of actions, carried out by a single user or an application program, which reads or updates the contents of a database.
Atomicity
Transactions are atomic – they don’t have parts (conceptually)
So can’t be executed partially
Consistency
Transactions take the database from one consistent state into another
In the middle of a transaction the database might not be consistent
Isolation
The effects of a transaction are not visible to other transactions until it has completed
From outside the transaction has either happened or not
To me this actually sounds like a consequence of atomicity…
Durability
Once a transaction has completed, its changes are made permanent
Even if the system crashes, the effects of a transaction must remain in place
The Transaction Manager
The transaction manager enforces the ACID properties
It schedules the operations of transactions
COMMIT and ROLLBACK are used to ensure atomicity
Locks are used to ensure consistency and isolation for concurrent transactions (next lecture)
A log is kept to ensure durability in the event of system failure (this lecture)
COMMIT signals the successful end of a transaction
Any changes made by the transaction should be saved
These changes are now visible to other transactions
ROLLBACK signals the unsuccessful end of a transaction
Any changes made by the transaction should be undone
It is now as if the transaction never existed
Prevention is better than cure
Can’t protect against everything though
The transaction log records the details of all transactions
Any changes the transaction makes to the database
How to undo these changes
When transactions complete and how
The log is stored on disk, not in memory
If the system crashes it is preserved
Write ahead log rule
The entry in the log must be made before COMMIT processing can complete
A system failure means all running transactions are affected
Software crashes or Power failures
At various times a DBMS takes a checkpoint
All committed transactions are written to disk
A record is made (on disk) of the transactions that are currently running
Any transaction that was running at the time of failure needs to be undone and restarted
Any transactions that committed since the last checkpoint need to be redone
Transaction Recovery
UNDO = all transactions running at the last checkpoint
REDO = empty
For each entry in the log, starting at the last checkpoint
If a BEGIN TRANSACTION entry is found for T
Add T to UNDO
If a COMMIT entry is found for T
Move T from UNDO to REDO
Backwards recovery
We need to undo some transactions
Working backwards through the log we undo any operation by a transaction on the UNDO list
This returns the database to a consistent state
Forwards recovery
Some transactions need to be redone
Working forwards through the log we redo any operation by a transaction on the REDO list
This brings the database up to date
System failures are not too severe
Only information since the last checkpoint is affected
This can be recovered from the transaction log
Media failures (disk crashes etc) are more serious
The data stored to disk is damaged
The transaction log itself may be damaged
Backups are needed to recover from media failure
The transaction log and entire contents of the database is written to secondary storage (often tape)
Time consuming, and often requires down time
Backups frequency
Frequent enough that little information is lost
Not so frequent as to cause problems
Every day (night) is common
Backup storage
Restore the database from the last backup
Use the transaction log to redo any changes made since the last backup
If the transaction log is damaged you can’t do step 2
Store the log on a separate physical device to the database
The risk of losing both is then reduced
Lecture 15: Concurrency
A schedule is a sequence of the operations by a set of concurrent transactions that preserves the order of operations in each of the individual transactions
A serial schedule is a schedule where operations of each transaction are executed consecutively without any interleaved operations from other transactions (each transaction commits before the next one is allowed to begin)
Two schedules are equivalent if they always have the same effect.
A schedule is serialisable if it is equivalent to some serial schedule.
A schedule is conflict serialisable if transactions in the schedule have a conflict but the schedule is still serialisable
To determine if a schedule is conflict serialisable we use a precedence graph
Locking is a procedure used to control concurrent access to data (to ensure serialisability of concurrent transactions)
Two types of lock
Shared lock (S-lock or read-lock)
Exclusive lock (X-lock or write-lock)
Read lock allows several transactions simultaneously to read a resource (but no transactions can change it at the same time)
Write lock allows one transaction exclusive access to write to a resource. No other transaction can read this resource at the same time.
The lock manager in the DBMS assigns locks and records them in the data dictionary
Before reading from a resource a transaction must acquire a read-lock
Before writing to a resource a transaction must acquire a write-lock
Locks are released on commit/rollback
A transaction may not acquire a lock on any resource that is write-locked by another transaction
A transaction may not acquire a write-lock on a resource that is locked by another transaction
If the requested lock is not available, transaction waits
A transaction follows the two-phase locking protocol (2PL) if all locking operations precede the first unlock operation in the transaction
A deadlock is an impasse that may result when two or more transactions are waiting for locks to be released which are held by each
Given a schedule, we can detect deadlocks which will happen in this schedule using a wait-for graph (WFG).
An alternative is timestamping
Requires less overhead in terms of tracking locks or detecting deadlock
Determines the order of transactions before they are executed
The protocol means that transactions with higher times take precedence
Equivalent to running transactions in order of their final time values
Transactions don’t wait - no deadlock
Lecture 17: Security and Integrity
GRANT
ON
Thursday, 17 May 2007
JDBC
JDBC Introduction
The JDBC API is a Java API that can access any kind of tabular data, especially data stored in a Relational Database.
JDBC helps you to write java applications that manage these three programming activities:
1. Connect to a data source, like a database
2. Send queries and update statements to the database
3. Retrieve and process the results received from the database in answer to your query
The following simple code fragment gives a simple example of these three steps:
This short code fragment instantiates a DriverManager object to connect to a database driver and log into the database, instantiates a Statement object that carries your SQL language query to the database; instantiates a ResultSet object that retrieves the results of your query, and executes a simple while loop, which retrieves and displays those results. It's that simple.
JDBC Product Components
JDBC includes four components:
1. The JDBC API —
The JDBC™ API provides programmatic access to relational data from the Java™ programming language. Using the JDBC API, applications can execute SQL statements, retrieve results, and propagate changes back to an underlying data source. The JDBC API can also interact with multiple data sources in a distributed, heterogeneous environment.
The JDBC API is part of the Java platform, which includes the Java™ Standard Edition (Java™ SE ) and the Java™ Enterprise Edition (Java™ EE). The JDBC 4.0 API is divided into two packages: java.sql and javax.sql. Both packages are included in the Java SE and Java EE platforms.
2. JDBC Driver Manager —
The JDBC DriverManager class defines objects which can connect Java applications to a JDBC driver. DriverManager has traditionally been the backbone of the JDBC architecture. It is quite small and simple.
The Standard Extension packages javax.naming and javax.sql let you use a DataSource object registered with a Java Naming and Directory Interface™ (JNDI) naming service to establish a connection with a data source. You can use either connecting mechanism, but using a DataSource object is recommended whenever possible.
3. JDBC Test Suite —
The JDBC driver test suite helps you to determine that JDBC drivers will run your program. These tests are not comprehensive or exhaustive, but they do exercise many of the important features in the JDBC API.
4. JDBC-ODBC Bridge —
The Java Software bridge provides JDBC access via ODBC drivers. Note that you need to load ODBC binary code onto each client machine that uses this driver. As a result, the ODBC driver is most appropriate on a corporate network where client installations are not a major problem, or for application server code written in Java in a three-tier architecture.
This Trail uses the first two of these these four JDBC components to connect to a database and then build a java program that uses SQL commands to communicate with a test Relational Database. The last two components are used in specialized environments to test web applications, or to communicate with ODBC-aware DBMSs.
JDBC Architecture
Two-tier and Three-tier Processing Models
The JDBC API supports both two-tier and three-tier processing models for database access.
Figure 1: Two-tier Architecture for Data Access.
The DBMS-proprietary protocol provides two-way communication between the client machine and the database server
In the two-tier model, a Java applet or application talks directly to the data source. This requires a JDBC driver that can communicate with the particular data source being accessed. A user's commands are delivered to the database or other data source, and the results of those statements are sent back to the user. The data source may be located on another machine to which the user is connected via a network. This is referred to as a client/server configuration, with the user's machine as the client, and the machine housing the data source as the server. The network can be an intranet, which, for example, connects employees within a corporation, or it can be the Internet.
In the three-tier model, commands are sent to a "middle tier" of services, which then sends the commands to the data source. The data source processes the commands and sends the results back to the middle tier, which then sends them to the user. MIS directors find the three-tier model very attractive because the middle tier makes it possible to maintain control over access and the kinds of updates that can be made to corporate data. Another advantage is that it simplifies the deployment of applications. Finally, in many cases, the three-tier architecture can provide performance advantages.
Figure 2: Three-tier Architecture for Data Access.
The DBMS-proprietary protocol provides two-way communication between the database server and the server machine. HTTP, RMI, CORBA or other calls provide two way communication between the server machine and the client machine
Until recently, the middle tier has often been written in languages such as C or C++, which offer fast performance. However, with the introduction of optimizing compilers that translate Java bytecode into efficient machine-specific code and technologies such as Enterprise JavaBeans™, the Java platform is fast becoming the standard platform for middle-tier development. This is a big plus, making it possible to take advantage of Java's robustness, multithreading, and security features.
With enterprises increasingly using the Java programming language for writing server code, the JDBC API is being used more and more in the middle tier of a three-tier architecture. Some of the features that make JDBC a server technology are its support for connection pooling, distributed transactions, and disconnected rowsets. The JDBC API is also what allows access to a data source from a Java middle tier.
A Relational Database Overview
A database is a means of storing information in such a way that information can be retrieved from it. In simplest terms, a relational database is one that presents information in tables with rows and columns. A table is referred to as a relation in the sense that it is a collection of objects of the same type (rows). Data in a table can be related according to common keys or concepts, and the ability to retrieve related data from a table is the basis for the term relational database. A Database Management System (DBMS) handles the way data is stored, maintained, and retrieved. In the case of a relational database, a Relational Database Management System (RDBMS) performs these tasks. DBMS as used in this book is a general term that includes RDBMS.
Integrity Rules
Relational tables follow certain integrity rules to ensure that the data they contain stay accurate and are always accessible. First, the rows in a relational table should all be distinct. If there are duplicate rows, there can be problems resolving which of two possible selections is the correct one. For most DBMSs, the user can specify that duplicate rows are not allowed, and if that is done, the DBMS will prevent the addition of any rows that duplicate an existing row.
A second integrity rule of the traditional relational model is that column values must not be repeating groups or arrays. A third aspect of data integrity involves the concept of a null value. A database takes care of situations where data may not be available by using a null value to indicate that a value is missing. It does not equate to a blank or zero. A blank is considered equal to another blank, a zero is equal to another zero, but two null values are not considered equal.
When each row in a table is different, it is possible to use one or more columns to identify a particular row. This unique column or group of columns is called a primary key. Any column that is part of a primary key cannot be null; if it were, the primary key containing it would no longer be a complete identifier. This rule is referred to as entity integrity.
Table 1.2 illustrates some of these relational database concepts. It has five columns and six rows, with each row representing a different employee.
The primary key for this table would generally be the employee number because each one is guaranteed to be different. (A number is also more efficient than a string for making comparisons.) It would also be possible to use First_Name and Last_Name because the combination of the two also identifies just one row in our sample database. Using the last name alone would not work because there are two employees with the last name of "Washington." In this particular case the first names are all different, so one could conceivably use that column as a primary key, but it is best to avoid using a column where duplicates could occur. If Elizabeth Taylor gets a job at this company and the primary key is First_Name, the RDBMS will not allow her name to be added (if it has been specified that no duplicates are permitted). Because there is already an Elizabeth in the table, adding a second one would make the primary key useless as a way of identifying just one row. Note that although using First_Name and Last_Name is a unique composite key for this example, it might not be unique in a larger database. Note also that Table 1.2 assumes that there can be only one car per employee.
SELECT Statements
SQL is a language designed to be used with relational databases. There is a set of basic SQL commands that is considered standard and is used by all RDBMSs. For example, all RDBMSs use the SELECT statement.
A SELECT statement, also called a query, is used to get information from a table. It specifies one or more column headings, one or more tables from which to select, and some criteria for selection. The RDBMS returns rows of the column entries that satisfy the stated requirements. A SELECT statement such as the following will fetch the first and last names of employees who have company cars:
SELECT First_Name, Last_Name
FROM Employees
WHERE Car_Number IS NOT NULL
The result set (the set of rows that satisfy the requirement of not having null in the Car_Number column) follows. The first name and last name are printed for each row that satisfies the requirement because the SELECT statement (the first line) specifies the columns First_Name and Last_Name. The FROM clause (the second line) gives the table from which the columns will be selected.
FIRST_NAME LAST_NAME
---------- -----------
Axel Washington
Florence Wojokowski
The following code produces a result set that includes the whole table because it asks for all of the columns in the table Employees with no restrictions (no WHERE clause). Note that SELECT * means "SELECT all columns."
SELECT *
FROM Employees
WHERE Clauses
The WHERE clause in a SELECT statement provides the criteria for selecting values. For example, in the following code fragment, values will be selected only if they occur in a row in which the column Last_Name begins with the string 'Washington'.
SELECT First_Name, Last_Name
FROM Employees
WHERE Last_Name LIKE 'Washington%'
The keyword LIKE is used to compare strings, and it offers the feature that patterns containing wildcards can be used. For example, in the code fragment above, there is a percent sign (%) at the end of 'Washington', which signifies that any value containing the string 'Washington' plus zero or more additional characters will satisfy this selection criterion. So 'Washington' or 'Washingtonian' would be matches, but 'Washing' would not be. The other wildcard used in LIKE clauses is an underbar (_), which stands for any one character. For example,
WHERE Last_Name LIKE 'Ba_man'
would match 'Batman', 'Barman', 'Badman', 'Balman', 'Bagman', 'Bamman', and so on.
The code fragment below has a WHERE clause that uses the equal sign (=) to compare numbers. It selects the first and last name of the employee who is assigned car 12.
SELECT First_Name, Last_Name
FROM Employees
WHERE Car_Number = 12
The next code fragment selects the first and last names of employees whose employee number is greater than 10005:
SELECT First_Name, Last_Name
FROM Employees
WHERE Employee_Number > 10005
WHERE clauses can get rather elaborate, with multiple conditions and, in some DBMSs, nested conditions. This overview will not cover complicated WHERE clauses, but the following code fragment has a WHERE clause with two conditions; this query selects the first and last names of employees whose employee number is less than 10100 and who do not have a company car.
SELECT First_Name, Last_Name
FROM Employees
WHERE Employee_Number < 10100 and Car_Number IS NULL
A special type of WHERE clause involves a join, which is explained in the next section.
Joins
A distinguishing feature of relational databases is that it is possible to get data from more than one table in what is called a join. Suppose that after retrieving the names of employees who have company cars, one wanted to find out who has which car, including the make, model, and year of car. This information is stored in another table, Cars, shown in Table 1.3.
Table 1.3. Cars
Car Number Make Model Year
5 Honda Civic DX 1996
12 Toyota Corolla 1999
There must be one column that appears in both tables in order to relate them to each other. This column, which must be the primary key in one table, is called the foreign key in the other table. In this case, the column that appears in two tables is Car_Number, which is the primary key for the table Cars and the foreign key in the table Employees. If the 1996 Honda Civic were wrecked and deleted from the Cars table, then Car_Number 5 would also have to be removed from the Employees table in order to maintain what is called referential integrity. Otherwise, the foreign key column (Car_Number) in Employees would contain an entry that did not refer to anything in Cars. A foreign key must either be null or equal to an existing primary key value of the table to which it refers. This is different from a primary key, which may not be null. There are several null values in the Car_Number column in the table Employees because it is possible for an employee not to have a company car.
The following code asks for the first and last names of employees who have company cars and for the make, model, and year of those cars. Note that the FROM clause lists both Employees and Cars because the requested data is contained in both tables. Using the table name and a dot (.) before the column name indicates which table contains the column.
Common SQL Commands
SQL commands are divided into categories, the two main ones being Data Manipulation Language (DML) commands and Data Definition Language (DDL) commands. DML commands deal with data, either retrieving it or modifying it to keep it up-to-date. DDL commands create or change tables and other database objects such as views and indexes.
A list of the more common DML commands follows:
* SELECT — used to query and display data from a database. The SELECT statement specifies which columns to include in the result set. The vast majority of the SQL commands used in applications are SELECT statements.
* INSERT — adds new rows to a table. INSERT is used to populate a newly created table or to add a new row (or rows) to an already-existing table.
* DELETE — removes a specified row or set of rows from a table
* UPDATE — changes an existing value in a column or group of columns in a table
The more common DDL commands follow:
* CREATE TABLE — creates a table with the column names the user provides. The user also needs to specify a type for the data in each column. Data types vary from one RDBMS to another, so a user might need to use metadata to establish the data types used by a particular database. CREATE TABLE is normally used less often than the data manipulation commands because a table is created only once, whereas adding or deleting rows or changing individual values generally occurs more frequently.
* DROP TABLE — deletes all rows and removes the table definition from the database. A JDBC API implementation is required to support the DROP TABLE command as specified by SQL92, Transitional Level. However, support for the CASCADE and RESTRICT options of DROP TABLE is optional. In addition, the behavior of DROP TABLE is implementation-defined when there are views or integrity constraints defined that reference the table being dropped.
* ALTER TABLE — adds or removes a column from a table. It also adds or drops table constraints and alters column attributes
Result Sets and Cursors
The rows that satisfy the conditions of a query are called the result set. The number of rows returned in a result set can be zero, one, or many. A user can access the data in a result set one row at a time, and a cursor provides the means to do that. A cursor can be thought of as a pointer into a file that contains the rows of the result set, and that pointer has the ability to keep track of which row is currently being accessed. A cursor allows a user to process each row of a result set from top to bottom and consequently may be used for iterative processing. Most DBMSs create a cursor automatically when a result set is generated.
Earlier JDBC API versions added new capabilities for a result set's cursor, allowing it to move both forward and backward and also allowing it to move to a specified row or to a row whose position is relative to another row.
Transactions
When one user is accessing data in a database, another user may be accessing the same data at the same time. If, for instance, the first user is updating some columns in a table at the same time the second user is selecting columns from that same table, it is possible for the second user to get partly old data and partly updated data. For this reason, DBMSs use transactions to maintain data in a consistent state (data consistency) while allowing more than one user to access a database at the same time (data concurrency).
A transaction is a set of one or more SQL statements that make up a logical unit of work. A transaction ends with either a commit or a rollback, depending on whether there are any problems with data consistency or data concurrency. The commit statement makes permanent the changes resulting from the SQL statements in the transaction, and the rollback statement undoes all changes resulting from the SQL statements in the transaction.
A lock is a mechanism that prohibits two transactions from manipulating the same data at the same time. For example, a table lock prevents a table from being dropped if there is an uncommitted transaction on that table. In some DBMSs, a table lock also locks all of the rows in a table. A row lock prevents two transactions from modifying the same row, or it prevents one transaction from selecting a row while another transaction is still modifying it.
Stored Procedures
A stored procedure is a group of SQL statements that can be called by name. In other words, it is executable code, a mini-program, that performs a particular task that can be invoked the same way one can call a function or method. Traditionally, stored procedures have been written in a DBMS-specific programming language. The latest generation of database products allows stored procedures to be written using the Java programming language and the JDBC API. Stored procedures written in the Java programming language are bytecode portable between DBMSs. Once a stored procedure is written, it can be used and reused because a DBMS that supports stored procedures will, as its name implies, store it in the database.
The following code is an example of how to create a very simple stored procedure using the Java programming language. Note that the stored procedure is just a static Java method that contains normal JDBC code. It accepts two input parameters and uses them to change an employee's car number.
Do not worry if you do not understand the example at this point. The code example below is presented only to illustrate what a stored procedure looks like. You will learn how to write the code in this example in the tutorials that follow.
Metadata
Databases store user data, and they also store information about the database itself. Most DBMSs have a set of system tables, which list tables in the database, column names in each table, primary keys, foreign keys, stored procedures, and so forth. Each DBMS has its own functions for getting information about table layouts and database features. JDBC provides the interface DatabaseMetaData, which a driver writer must implement so that its methods return information about the driver and/or DBMS for which the driver is written. For example, a large number of methods return whether or not the driver supports a particular functionality. This interface gives users and tools a standardized way to get metadata. In general, developers writing tools and drivers are the ones most likely to be concerned with metadata.
Establishing a Connection
First, you need to establish a connection with the DBMS you want to use. Typically, a JDBC™ application connects to a target data source using one of two mechanisms:
* DriverManager: This fully implemented class requires an application to load a specific driver, using a hardcoded URL. As part of its initialization, the DriverManager class attempts to load the driver classes referenced in the jdbc.drivers system property. This allows you to customize the JDBC Drivers used by your applications.
* DataSource: This interface is preferred over DriverManager because it allows details about the underlying data source to be transparent to your application. A DataSource object's properties are set so that it represents a particular data source.
Establishing a connection involves two steps: Loading the driver, and making the connection.
Loading the Driver
Loading the driver you want to use is very simple. It involves just one line of code in your program. To use the Java DB driver, add the following line of code:
Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
Your driver documentation provides the class name to use. In the example above, EmbeddedDriver is one of the drivers for Java DB.
Calling the Class.forName automatically creates an instance of a driver and registers it with the DriverManager, so you don't need to create an instance of the class. If you were to create your own instance, you would be creating an unnecessary duplicate, but it would do no harm.
After you have loaded a driver, it can make a connection with a DBMS.
Making the Connection
The second step in establishing a connection is to have the appropriate driver connect to the DBMS.
Using the DriverManager Class
The DriverManager class works with the Driver interface to manage the set of drivers available to a JDBC client. When the client requests a connection and provides a URL, the DriverManager is responsible for finding a driver that recognizes the URL and for using it to connect to the corresponding data source. Connection URLs have the following form:
jdbc:derby:[propertyList]
The dbName portion of the URL identifies a specific database. A database can be in one of many locations: in the current working directory, on the classpath, in a JAR file, in a specific Java DB database home directory, or in an absolute location on your file system.
If you are using a vendor-specific driver, such as Oracle, the documentation will tell you what subprotocol to use, that is, what to put after jdbc: in the JDBC URL. For example, if the driver developer has registered the name OracleDriver as the subprotocol, the first and second parts of the JDBC URL will be jdbc.driver.OracleDriver . The driver documentation will also give you guidelines for the rest of the JDBC URL. This last part of the JDBC URL supplies information for identifying the data source.
The getConnection method establishes a connection:
Connection conn = DriverManager.getConnection("jdbc:derby:COFFEES");
In place of " myLogin " you insert the name you use to log in to the DBMS; in place of " myPassword " you insert your password for the DBMS. So, if you log in to your DBMS with a login name of " Fernanda " and a password of " J8, " just these two lines of code will establish a connection:
String url = "jdbc:derby:Fred";
Connection con = DriverManager.getConnection(url, "Fernanda", "J8");
If one of the drivers you loaded recognizes the JDBC URL supplied to the method DriverManager.getConnection, that driver establishes a connection to the DBMS specified in the JDBC URL. The DriverManager class, true to its name, manages all of the details of establishing the connection for you behind the scenes. Unless you are writing a driver, you probably won't use any of the methods in the interface Driver, and the only DriverManager method you really need to know is DriverManager.getConnection
The connection returned by the method DriverManager.getConnection is an open connection you can use to create JDBC statements that pass your SQL statements to the DBMS. In the previous example, con is an open connection, and you use it in the examples that follow.
Using a DataSource Object for a connection
Using a DataSource object increases application portability by making it possible for an application to use a logical name for a data source instead of having to supply information specific to a particular driver. The following example shows how to use a DataSource to establish a connection:
You can configure a DataSource using a tool or manually. For example, Here is an example of a DataSource lookup:
DataSource implementations must provide getter and setter methods for each property they support. These properties typically are initialized when the DataSource object is deployed.
JDBC-ODBC Bridge Driver
For normal use, you should obtain a commercial JDBC driver from a vendor such as your database vendor or your database middleware vendor. The JDBC-ODBC Bridge driver provided with JDBC is recommended only for development and testing, or when no other alternative is available
Setting Up Tables
For the following examples, you need to create a few tables in your database. The first table, COFFEES, contains the essential information about the coffees sold at The Coffee Break, including the coffee names, their prices, the number of pounds sold the current week, and the number of pounds sold to date. The table COFFEES, which is describe in more detail later, is shown here:
The Tables Used in the Example Database
The column storing the coffee name is COF_NAME, and it holds values with an SQL type of VARCHAR and a maximum length of 32 characters. Since the names are different for each type of coffee sold, the name uniquely identifies a particular coffee and serves as the primary key. The second column, named SUP_ID, holds a number identifying the coffee supplier; this number is of SQL type INTEGER. The third column, called PRICE, stores values with an SQL type of FLOAT because it needs to hold values with decimal points. (Note that money values would normally be stored in an SQL type DECIMAL or NUMERIC, but because of differences among DBMSs and to avoid incompatibility with older versions of JDBC, you are using the more standard type FLOAT for this tutorial.) The column named SALES stores values of SQL type INTEGER and indicates the number of pounds of coffee sold during the current week. The final column, TOTAL, contains an SQL INTEGER, which gives the total number of pounds of coffee sold to date.
SUPPLIERS, the second table in our database, gives information about each of the suppliers:
The tables COFFEES and SUPPLIERS both contain the column SUP_ID. These two tables can be used in SELECT statements to get data based on the information in both tables. The column SUP_ID is the primary key in the table SUPPLIERS, and as such, it uniquely identifies each of the coffee suppliers. In the table COFFEES, SUP_ID is called a foreign key. (You can think of a foreign key as being foreign in the sense that it is imported from another table.) Note that each SUP_ID number appears only once in the SUPPLIERS table; this is required for it to be a primary key. In the COFFEES table, where it is a foreign key, it is all right for there to be duplicate SUP_ID numbers because one supplier may sell many types of coffee. Later in this chapter, you see an example of how to use primary and foreign keys in a SELECT statement.
Creating Tables
You can create your tables in at least three ways:
Creating a Tables Using the NetBeans IDE
Most database vendors provide a graphical user interface (GUI) that makes creating tables in your database easy. In addition, many IDEs, such as the NetBeans IDE, also provides an easy-to-use GUI for table creation.
For example, in the NetBeans™ 5.0 IDE, creating tables in the Java DB™ database is easy. After starting the database, either through the pull-down menu, or by right-clicking Databases, you add tables simply by right-clicking the Tables icon and selecting BBBCreate Table. You get a dialog box that looks like the one below:
Table creator in NetBeans
Fill in the dialog box fields and the NetBeans IDE creates the table. Adding or deleting rows is just as easy. Right-click the table to want to edit.
Creating Tables Using ANT
First, you can use a properties file to feed ANT, which has a simple sql target. Create a simple properties file, such as the one below:
Next, create a build.xml file that includes targets for compiling the Java code as well as a target similar to the following:
The tutorial.sql file can contain the commands to create the tables and, possibly, populate the tables if need be, such as the example below:
The ';' matches the db.delimiter property.
NetBeans contains everything you need, including the Java DB and ANT. Download NetBeans.
Using SQL in an Application
Finally, you can create tables from within the application code itself. The following SQL statement creates the table named COFFEES. The entries within the outer pair of parentheses consist of the name of a column followed by a space and the SQL type to be stored in that column. A comma separates the entry for one column (consisting of column name and SQL type) from the next one. To be sure you are using the correct syntax, see your DBMS documentation. For the Java DB, read the current documentation.
This code does not end with a DBMS statement terminator, which can vary from DBMS to DBMS. For example, Oracle uses a semicolon (;) to indicate the end of a statement, and Sybase uses the word go. The driver you are using will automatically supply the appropriate statement terminator, and you will not need to include it in your JDBC code.
Note: In the CREATE TABLE statement, key words are printed in all capital letters, and each item is on a separate line. SQL does not require either; these conventions simply make statements easier to read. The standard in SQL is that keywords are not case sensitive, so, for example, the following SELECT statement can be written various ways. As an example, these two versions below are equivalent as far as SQL is concerned:
Quoted material, however, is case sensitive: in the name " Washington," W must be capitalized, and the rest of the letters must be lowercase.
Requirements can vary from one DBMS to another when it comes to identifier names.
Retrieving Values from Result Sets
In the previous lesson, ResultSet was briefly mentioned. Now, you will learn the details of the ResultSet interface. The ResultSet interface provides methods for retrieving and manipulting the results of executed queries, and ResultSet objects can have different functionality and characteristics. These characteristics are result set type, result set concurrency, and cursor holdability. A table of data representing a database result set is usually generated by executing a statement that queries the database.
The type of a ResultSet object determines the level of its functionality in two areas: the ways in which the cursor can be manipulated, and how concurrent changes made to the underlying data source are reflected by the ResultSet object.
The sensitivity of the ResultSet object is determined by one of three different ResultSet types:
1. TYPE_FORWARD_ONLY — The result set is not scrollable; its cursor moves forward only, from before the first row to after the last row. The rows contained in the result set depend on how the underlying database materializes the results. That is, it contains the rows that satisfy the query at either the time the query is executed or as the rows are retrieved.
2. TYPE_SCROLL_INSENSITIVE — The result set is scrollable; its cursor can move both forward and backward relative to the current position, and it can move to an absolute position.
3. TYPE_SCROLL_SENSITIVE — The result set is scrollable; its cursor can move both forward and backward relative to the current position, and it can move to an absolute position.
Now, you'll see how to send the above SELECT statements from a program written in the Java™ programming language and how you get the results we showed.
JDBC™ returns results in a ResultSet object, so we need to declare an instance of the class ResultSet to hold our results. In addition, the Statement methods executeQuery and getResultSet both return a ResultSet object, as do various DatabaseMetaData methods. The following code demonstrates declaring the ResultSet object rs and assigning the results of our earlier query to it by using the executeQuery method.
Before you can take advantage of these features, however, you need to create a scrollable ResultSet object. The following line of code illustrates one way to create a scrollable ResultSet object:
This code is similar to what you have used earlier, except that it adds two arguments to the createStatement method. The first argument is one of three constants added to the ResultSet API to indicate the type of a ResultSet object: TYPE_FORWARD_ONLY, TYPE_SCROLL_INSENSITIVE, and TYPE_SCROLL_SENSITIVE. The second argument is one of two ResultSet constants for specifying whether a result set is read-only or updatable: CONCUR_READ_ONLY and CONCUR_UPDATABLE . The point to remember here is that if you specify a type, you must also specify whether it is read-only or updatable. Also, you must specify the type first, and because both parameters are of type int, the compiler will not complain if you switch the order.
Specifying the constant TYPE_FORWARD_ONLY creates a nonscrollable result set, that is, one in which the cursor moves only forward. If you do not specify any constants for the type and updatability of a ResultSet object, you will automatically get one that is TYPE_FORWARD_ONLY and CONCUR_READ_ONLY.
Using the ResultSet Methods
The variable srs, which is an instance of ResultSet, contains the rows of coffees and prices shown in the result set example above. In order to access the names and prices. A ResultSet object maintains a cursor, which points to its current row of data.
When a ResultSet object is first created, the cursor is positioned before the first row. To move the cursor, you can use the following methods:
* next() - moves the cursor forward one row. Returns true if the cursor is now positioned on a row and false if the cursor is positioned after the last row.
* previous() - moves the cursor backwards one row. Returns true if the cursor is now positioned on a row and false if the cursor is positioned before the first row.
* first() - moves the cursor to the first row in the ResultSet object. Returns true if the cursor is now positioned on the first row and false if the ResultSet object does not contain any rows.
* last() - moves the cursor to the last row in the ResultSet object. Returns true if the cursor is now positioned on the last row and false if the ResultSet object does not contain any rows.
* beforeFirst() - positions the cursor at the start of the ResultSet object, before the first row. If the ResultSet object does not contain any rows, this method has no effect.
* afterLast() - positions the cursor at the end of the ResultSet object, after the last row. If the ResultSet object does not contain any rows, this method has no effect.
* relative(int rows) - moves the cursor relative to its current position.
* absolute(int row) - positions the cursor on the row-th row of the ResultSet object.
Once you have a scrollable ResultSet object, srs in the previous example, you can use it to move the cursor around in the result set. Since the cursor is initially positioned just above the first row of a ResultSet object, the first call to the method next moves the cursor to the first row and makes it the current row. Successive invocations of the method next move the cursor down one row at a time from top to bottom.
Using the getXXX Methods
The ResultSet interface declares getter methods (getBoolean, getLong, and so on) for retrieving column values from the current row. Your application can retrieve values using either the index number of the column or the name of the column. The column index is usually more efficient. Columns are numbered from 1. For maximum portability, result set columns within each row should be read in left-to-right order, and each column should be read only once.
Column names used as input to getter methods are case insensitive. When a getter method is called with a column name and several columns have the same name, the value of the first matching column will be returned. The column name option is designed to be used when column names are used in the SQL query that generated the result set. For columns that are NOT explicitly named in the query, it is best to use column numbers. If column names are used, the programmer should take care to guarantee that they uniquely refer to the intended columns, which can be assured with the SQL AS clause.
The getXXX method of the appropriate type retrieves the value in each column. For example, the first column in each row of srs is COF_NAME, which stores a value of SQL type VARCHAR. The method for retrieving a value of SQL type VARCHAR is getString. The second column in each row stores a value of SQL type FLOAT, and the method for retrieving values of that type is getFloat. The following code accesses the values stored in the current row of srs and prints a line with the name followed by three spaces and the price. Each time the method next is invoked, the next row becomes the current row, and the loop continues until there are no more rows in rs.
The method getString is invoked on the ResultSet object srs, so getString retrieves (gets) the value stored in the column COF_NAME in the current row of srs . The value that getString retrieves has been converted from an SQL VARCHAR to a String in the Java programming language, and it is assigned to the String object s.
Note that although the method getString is recommended for retrieving the SQL types CHAR and VARCHAR, it is possible to retrieve any of the basic SQL types with it. (You cannot, however, retrieve the new SQL3 datatypes with it. We will discuss SQL3 types later in this tutorial.) Getting all values with getString can be very useful, but it also has its limitations. For instance, if it is used to retrieve a numeric type, getString converts the numeric value to a Java String object, and the value has to be converted back to a numeric type before it can be operated on as a number. In cases where the value is treated as a string anyway, there is no drawback. Further, if you want an application to retrieve values of any standard SQL type other than SQL3 types, use the getString method.
The output will look something like this:
You can process all of the rows is srs going backward, but to do this, the cursor must start out located after the last row. You can move the cursor explicitly to the position after the last row with the method afterLast. Then the method previous() moves the cursor from the position after the last row to the last row, and then to the previous row with each iteration through the while loop. The loop ends when the cursor reaches the position before the first row, where the method previous() returns false .
The printout will look similar to this:
French_Roast_Decaf 9.99
Colombian_Decaf 8.99
Espresso 9.99
French_Roast 8.99
Colombian 7.99
As you can see, the printout for each has the same values, but the rows are in the opposite order.
The situation is similar with the method getFloat except that it retrieves the value stored in the column PRICE, which is an SQL FLOAT, and converts it to a Java float before assigning it to the variable n.
JDBC offers two ways to identify the column from which a getXXX method gets a value. One way is to give the column name, as was done in the example above. The second way is to give the column index (number of the column), with 1 signifying the first column, 2 , the second, and so on. Using the column number instead of the column name looks like this:
String s = srs.getString(1);
float n = srs.getFloat(2);
The first line of code gets the value in the first column of the current row of rs (column COF_NAME), converts it to a Java String object, and assigns it to s. The second line of code gets the value stored in the second column of the current row of rs , converts it to a Java float, and assigns it to n. Note that the column number refers to the column number in the result set, not in the original table.
You can move the cursor to a particular row in a ResultSet object. The methods first, last, beforeFirst, and afterLast move the cursor to the row indicated in their names. The method absolute will move the cursor to the row number indicated in the argument passed to it. If the number is positive, the cursor moves the given number from the beginning, so calling absolute(1) puts the cursor on the first row. If the number is negative, the cursor moves the given number from the end, so calling absolute(-1) puts the cursor on the last row. The following line of code moves the cursor to the fourth row of srs:
srs.absolute(4);
If srs has 500 rows, the following line of code moves the cursor to row 497:
srs.absolute(-4);
Three methods move the cursor to a position relative to its current position. As you have seen, the method next moves the cursor forward one row, and the method previous moves the cursor backward one row. With the method relative, you can specify how many rows to move from the current row and also the direction in which to move. A positive number moves the cursor forward the given number of rows; a negative number moves the cursor backward the given number of rows. For example, in the following code fragment, the cursor moves to the fourth row, then to the first row, and finally to the third row:
srs.absolute(4); // cursor is on the fourth row
. . .
srs.relative(-3); // cursor is on the first row
. . .
srs.relative(2); // cursor is on the third row
The method getRow lets you check the number of the row where the cursor is positioned. For example, you can use getRow to verify the current position of the cursor in the previous example as follows:
srs.absolute(4);
int rowNum = srs.getRow(); // rowNum should be 4
srs.relative(-3);
int rowNum = srs.getRow(); // rowNum should be 1
srs.relative(2);
int rowNum = srs.getRow(); // rowNum should be 3
Four additional methods let you verify whether the cursor is at a particular position. The position is stated in their names: isFirst, isLast, isBeforeFirst, isAfterLast. These methods all return a boolean and can therefore be used in a conditional statement. For example, the following code fragment tests to see whether the cursor is after the last row before invoking the method previous in a while loop. If the method isAfterLast returns false, the cursor is not after the last row, so the method afterLast is invoked. This guarantees that the cursor will be after the last row and that using the method previous in the while loop will cover every row in srs.
In summary, JDBC allows you to use either the column name or the column number as the argument to a getXXX method. Using the column number is slightly more efficient, and there are some cases where the column number is required. In general, though, supplying the column name is essentially equivalent to supplying the column number.
JDBC allows a lot of latitude as far as which getXXX methods you can use to retrieve the different SQL types. For example, the method getInt can be used to retrieve any of the numeric or character types. The data it retrieves will be converted to an int; that is, if the SQL type is VARCHAR , JDBC will attempt to parse an integer out of the VARCHAR. The method getInt is recommended for retrieving only SQL INTEGER types, however, and it cannot be used for the SQL types BINARY, VARBINARY, LONGVARBINARY, DATE , TIME, or TIMESTAMP
Updating Tables
Next, you learn to update rows in a result set, using methods in the Java programming language rather than having to send an SQL command.
Updating a row in a ResultSet object is a two-phase process. First, the new value for each column being updated is set, and then the change is applied to the row. The row in the underlying data source is not updated until the second phase is completed.
The ResultSet interface contains two update methods for each JDBC™ type, one specifying the column to be updated as an index and one specifying the column name as it appears in the select list. Column names supplied to updater methods are case insensitive. If a select list contains the same column more than once, the first instance of the column will be updated.
First, you need to create a ResultSet object that is updatable. To do this, supply the ResultSet constant CONCUR_UPDATABLE to the createStatement method, as you have seen in previous examples. The Statement object it creates produces an updatable ResultSet object each time it executes a query. The following code fragment illustrates creating the updatable ResultSet object uprs. Note that the code also makes uprs scrollable. An updatable ResultSet object does not necessarily have to be scrollable, but when you are making changes to a result set, you generally want to be able to move around in it. With a scrollable result set, you can move to rows you want to change, and if the type is TYPE_SCROLL_SENSITIVE, you can get the new value in a row after you have changed it.
The ResultSet object uprs looks something like this:
The method updateRow applies all column changes to the current row. The changes are not made to the row until updateRow has been called. You can use the cancelUpdates method to back out changes made to the row before the updateRow method is called.
An update is the modification of a column value in the current row. Let's suppose that you want to raise the price of French Roast Decaf coffee to 10.99:
Update operations affect column values in the row where the cursor is positioned, so in the first line the ResultSet uprs calls the method last to move its cursor to the last row (the row where the column COF_NAME has the value FRENCH_ROAST_DECAF). Once the cursor is on the last row, all of the update methods you call will operate on that row until you move the cursor to another row. The second line changes the value in the PRICE column to 10.99 by calling the method updateFloat. This method is used because the column value we want to update is a float in the Java programming language.
The ResultSet. updateXXX methods take two parameters: the column to update and the new value to put in that column. As with the ResultSet.getXXX methods, the parameter designating the column may be either the column name or the column number. There is a different updateXXX method for updating each datatype ( updateString, updateBigDecimal, updateInt, and so on) just as there are different getXXX methods for retrieving different datatypes.
At this point, the price in uprs for French Roast Decaf will be 10.99, but the price in the table COFFEES in the database will still be 9.99. To make the update take effect in the database and not just the result set, we must call the ResultSet method updateRow. Here is what the code should look like to update both uprs and COFFEES :
If you had moved the cursor to a different row before calling the method updateRow, the update would have been lost. If, on the other hand, you realized that the price should really have been 10.79 instead of 10.99, you could have cancelled the update to 10.99 by calling the method cancelRowUpdates. You have to invoke cancelRowUpdates before invoking the method updateRow; once updateRow is called, calling the method cancelRowUpdates does nothing. Note that cancelRowUpdates cancels all of the updates in a row, so if there are many invocations of the updateXXX methods on the same row, you cannot cancel just one of them. The following code fragment first cancels updating the price to 10.99 and then updates it to 10.79:
In this example, only one column value was updated, but you can call an appropriate updateXXX method for any or all of the column values in a single row. The concept to remember is that updates and related operations apply to the row where the cursor is positioned. Even if there are many calls to updateXXX methods, it takes only one call to the method updateRow to update the database with all of the changes made in the current row.
If you want to update the price for COLOMBIAN_DECAF as well, you have to move the cursor to the row containing that coffee. Because the row for COLOMBIAN_DECAF immediately precedes the row for FRENCH_ROAST_DECAF, you can call the method previous to position the cursor on the row for COLOMBIAN_DECAF. The following code fragment changes the price in that row to 9.79 in both the result set and the underlying table in the database:
All cursor movements refer to rows in a ResultSet object, not rows in the underlying database. If a query selects five rows from a database table, there will be five rows in the result set, with the first row being row 1, the second row being row 2, and so on. Row 1 can also be identified as the first, and, in a result set with five rows, row 5 is the last.
The ordering of the rows in the result set has nothing at all to do with the order of the rows in the base table. In fact, the order of the rows in a database table is indeterminate. The DBMS keeps track of which rows were selected, and it makes updates to the proper rows, but they may be located anywhere in the table. When a row is inserted, for example, there is no way to know where in the table it has been inserted.
Using Prepared Statements
Sometimes it is more convenient to use a PreparedStatement object for sending SQL statements to the database. This special type of statement is derived from the more general class, Statement, that you already know.
If you want to execute a Statement object many times, it normally reduces execution time to use a PreparedStatement object instead.
The main feature of a PreparedStatement object is that, unlike a Statement object, it is given an SQL statement when it is created. The advantage to this is that in most cases, this SQL statement is sent to the DBMS right away, where it is compiled. As a result, the PreparedStatement object contains not just an SQL statement, but an SQL statement that has been precompiled. This means that when the PreparedStatement is executed, the DBMS can just run the PreparedStatement SQL statement without having to compile it first.
Although PreparedStatement objects can be used for SQL statements with no parameters, you probably use them most often for SQL statements that take parameters. The advantage of using SQL statements that take parameters is that you can use the same statement and supply it with different values each time you execute it. Examples of this are in the following sections.
Creating a PreparedStatement Object
As with Statement objects, you create PreparedStatement objects with a Connection method. Using our open connection con from previous examples, you might write code such as the following to create a PreparedStatement object that takes two input parameters:
The variable updateSales now contains the SQL statement, "UPDATE COFFEES SET SALES = ? WHERE COF_NAME LIKE ?", which has also, in most cases, been sent to the DBMS and been precompiled.
Supplying Values for PreparedStatement Parameters
You need to supply values to be used in place of the question mark placeholders (if there are any) before you can execute a PreparedStatement object. You do this by calling one of the setXXX methods defined in the PreparedStatement class. If the value you want to substitute for a question mark is a Java int, you call the method setInt. If the value you want to substitute for a question mark is a Java String, you call the method setString, and so on. In general, there is a setXXX method for each primitive type declared in the Java programming language.
setXXX Using the PreparedStatement object updateSales from the previous example, the following line of code sets the first question mark placeholder to a Java int with a value of 75:
updateSales.setInt(1, 75);
setXXX The first argument given to a setXXX method indicates which question mark placeholder is to be set, and the second argument indicates the value to which it is to be set. The next example sets the second placeholder parameter to the string " Colombian":
updateSales.setString(2, "Colombian");
setXXX After these values have been set for its two input parameters, the SQL statement in updateSales is the equivalent to the SQL statement in the String object updateString that was used in the previous update example. Therefore, the following two code fragments accomplish the same thing:
The method executeUpdate was used to execute both the Statement stmt and the PreparedStatement updateSales. Notice, however, that no argument is supplied to executeUpdate when it is used to execute updateSales. This is true because updateSales already contains the SQL statement to be executed.
Looking at these examples, you might wonder why you would choose to use a PreparedStatement object with parameters instead of just a simple statement, since the simple statement involves fewer steps. If you were going to update the SALES column only once or twice, then there would be no need to use an SQL statement with input parameters. If you will be updating often, on the other hand, it might be much easier to use a PreparedStatement object, especially in situations where you can use a for loop or while loop to set a parameter to a succession of values. You will see an example of this later in this section.
Once a parameter has been set with a value, it retains that value until it is reset to another value, or the method clearParameters is called. Using the PreparedStatement object updateSales, the following code fragment illustrates reusing a prepared statement after resetting the value of one of its parameters and leaving the other one the same:
Using a Loop to Set Values
You can often make coding easier by using a for loop or a while loop to set values for input parameters.
The code fragment that follows demonstrates using a for loop to set values for parameters in the PreparedStatement object updateSales. The array salesForWeek holds the weekly sales amounts. These sales amounts correspond to the coffee names listed in the array coffees, so that the first amount in salesForWeek (175) applies to the first coffee name in coffees ("Colombian"), the second amount in salesForWeek (150) applies to the second coffee name in coffees ("French_Roast "), and so on. This code fragment demonstrates updating the SALES column for all the coffees in the table COFFEES:
When the proprietor wants to update the sales amounts for the next week, he can use this same code as a template. All he has to do is enter the new sales amounts in the proper order in the array salesForWeek. The coffee names in the array coffees remain constant, so they do not need to be changed. (In a real application, the values would probably be input from the user rather than from an initialized Java array.)
Return Values for the executeUpdate Method
Whereas executeQuery returns a ResultSet object containing the results of the query sent to the DBMS, the return value for executeUpdate is an int that indicates how many rows of a table were updated. For instance, the following code shows the return value of executeUpdate being assigned to the variable n:
The table COFFEES was updated by having the value 50 replace the value in the column SALES in the row for Espresso. That update affected one row in the table, so n is equal to 1.
When the method executeUpdate is used to execute a DDL statement, such as in creating a table, it returns the int 0. Consequently, in the following code fragment, which executes the DDL statement used to create the table COFFEES, n is assigned a value of 0:
int n = executeUpdate(createTableCoffees); // n = 0
Note that when the return value for executeUpdate is 0, it can mean one of two things:
* the statement executed was an update statement that affected zero rows
*
* the statement executed was a DDL statement.
Using Joins
Sometimes you need to use two or more tables to get the data you want. For example, suppose the proprietor of The Coffee Break wants a list of the coffees he buys from Acme, Inc. This involves information in the COFFEES table as well as the yet-to-be-created SUPPLIERS table. This is a case where a join is needed. A join is a database operation that relates two or more tables by means of values that they share in common. In our example database, the tables COFFEES and SUPPLIERS both have the column SUP_ID, which can be used to join them.
Before we go any further, we need to create the table SUPPLIERS and populate it with values.
The code below creates the table SUPPLIERS:
The following code inserts rows for three suppliers into SUPPLIERS:
The following code selects the whole table and lets us see what the table SUPPLIERS looks like:
ResultSet rs = stmt.executeQuery("select * from SUPPLIERS");
Now that we have the tables COFFEES and SUPPLIERS, we can proceed with the scenario where the owner wants to get a list of the coffees he buys from a particular supplier. The names of the suppliers are in the table SUPPLIERS, and the names of the coffees are in the table COFFEES. Since both tables have the column SUP_ID, this column can be used in a join. It follows that you need some way to distinguish which SUP_ID column you are referring to. This is done by preceding the column name with the table name, as in "COFFEES.SUP_ID" to indicate that you mean the column SUP_ID in the table COFFEES. The following code, in which stmt is a Statement object, selects the coffees bought from Acme, Inc.:
This will produce the following output:
Using Transactions
There are times when you do not want one statement to take effect unless another one completes. For example, when the proprietor of The Coffee Break updates the amount of coffee sold each week, he will also want to update the total amount sold to date. However, he will not want to update one without updating the other; otherwise, the data will be inconsistent. The way to be sure that either both actions occur or neither action occurs is to use a transaction. A transaction is a set of one or more statements that are executed together as a unit, so either all of the statements are executed, or none of the statements is executed.
Disabling Auto-commit Mode
When a connection is created, it is in auto-commit mode. This means that each individual SQL statement is treated as a transaction and is automatically committed right after it is executed. (To be more precise, the default is for an SQL statement to be committed when it is completed, not when it is executed. A statement is completed when all of its result sets and update counts have been retrieved. In almost all cases, however, a statement is completed, and therefore committed, right after it is executed.)
The way to allow two or more statements to be grouped into a transaction is to disable auto-commit mode. This is demonstrated in the following line of code, where con is an active connection:
con.setAutoCommit(false);
Committing a Transaction
Once auto-commit mode is disabled, no SQL statements are committed until you call the method commit explicitly. All statements executed after the previous call to the method commit are included in the current transaction and committed together as a unit. The following code, in which con is an active connection, illustrates a transaction:
In this example, auto-commit mode is disabled for the connection con, which means that the two prepared statements updateSales and updateTotal are committed together when the method commit is called. Whenever the commit method is called (either automatically when auto-commit mode is enabled or explicitly when it is disabled), all changes resulting from statements in the transaction are made permanent. In this case, that means that the SALES and TOTAL columns for Colombian coffee have been changed to 50 (if TOTAL had been 0 previously) and will retain this value until they are changed with another update statement. TransactionPairs.java illustrates a similar kind of transaction but uses a for loop to supply values to the setXXX methods for updateSales and updateTotal.
The final line of the previous example enables auto-commit mode, which means that each statement is once again committed automatically when it is completed. Then, you are back to the default state where you do not have to call the method commit yourself. It is advisable to disable auto-commit mode only while you want to be in transaction mode. This way, you avoid holding database locks for multiple statements, which increases the likelihood of conflicts with other users.
Using Transactions to Preserve Data Integrity
In addition to grouping statements together for execution as a unit, transactions can help to preserve the integrity of the data in a table. For instance, suppose that an employee was supposed to enter new coffee prices in the table COFFEES but delayed doing it for a few days. In the meantime, prices rose, and today the owner is in the process of entering the higher prices. The employee finally gets around to entering the now outdated prices at the same time that the owner is trying to update the table. After inserting the outdated prices, the employee realizes that they are no longer valid and calls the Connection method rollback to undo their effects. (The method rollback aborts a transaction and restores values to what they were before the attempted update.) At the same time, the owner is executing a SELECT statement and printing out the new prices. In this situation, it is possible that the owner will print a price that was later rolled back to its previous value, making the printed price incorrect.
This kind of situation can be avoided by using transactions, providing some level of protection against conflicts that arise when two users access data at the same time.
To avoid conflicts during a transaction, a DBMS uses locks, mechanisms for blocking access by others to the data that is being accessed by the transaction. (Note that in auto-commit mode, where each statement is a transaction, locks are held for only one statement.) Once a lock is set, it remains in force until the transaction is committed or rolled back. For example, a DBMS could lock a row of a table until updates to it have been committed. The effect of this lock would be to prevent a user from getting a dirty read, that is, reading a value before it is made permanent. (Accessing an updated value that has not been committed is considered a dirty read because it is possible for that value to be rolled back to its previous value. If you read a value that is later rolled back, you will have read an invalid value.)
How locks are set is determined by what is called a transaction isolation level, which can range from not supporting transactions at all to supporting transactions that enforce very strict access rules.
One example of a transaction isolation level is TRANSACTION_READ_COMMITTED, which will not allow a value to be accessed until after it has been committed. In other words, if the transaction isolation level is set to TRANSACTION_READ_COMMITTED, the DBMS does not allow dirty reads to occur. The interface Connection includes five values which represent the transaction isolation levels you can use in JDBC.
Normally, you do not need to do anything about the transaction isolation level; you can just use the default one for your DBMS. JDBC allows you to find out what transaction isolation level your DBMS is set to (using the Connection method getTransactionIsolation) and also allows you to set it to another level (using the Connection method setTransactionIsolation). Keep in mind, however, that even though JDBC allows you to set a transaction isolation level, doing so has no effect unless the driver and DBMS you are using support it.
Setting and Rolling Back to a Savepoint
The JDBC 3.0 API adds the method Connection.setSavepoint, which sets a savepoint within the current transaction. The Connection.rollback method has been overloaded to take a savepoint argument.
The example below inserts a row into a table, sets the savepoint svpt1, and then inserts a second row. When the transaction is later rolled back to svpt1, the second insertion is undone, but the first insertion remains intact. In other words, when the transaction is committed, only the row containing ?FIRST? will be added to TAB1:
Releasing a Savepoint
The method Connection.releaseSavepoint takes a Savepoint object as a parameter and removes it from the current transaction.
Once a savepoint has been released, attempting to reference it in a rollback operation causes an SQLException to be thrown. Any savepoints that have been created in a transaction are automatically released and become invalid when the transaction is committed, or when the entire transaction is rolled back. Rolling a transaction back to a savepoint automatically releases and makes invalid any other savepoints that were created after the savepoint in question.
When to Call the Method rollback
As mentioned earlier, calling the method rollback aborts a transaction and returns any values that were modified to their previous values. If you are trying to execute one or more statements in a transaction and get an SQLException, you should call the method rollback to abort the transaction and start the transaction all over again. That is the only way to be sure of what has been committed and what has not been committed. Catching an SQLException tells you that something is wrong, but it does not tell you what was or was not committed. Since you cannot count on the fact that nothing was committed, calling the method rollback is the only way to be sure.
TransactionPairs.java demonstrates a transaction and includes a catch block that invokes the method rollback. In this particular situation, it is not really necessary to call rollback, and we do it mainly to illustrate how it is done. If the application continued and used the results of the transaction, however, it would be necessary to include a call to rollback in the catch block in order to protect against using possibly incorrect data.
Stored Procedures
A stored procedure is a group of SQL statements that form a logical unit and perform a particular task. Stored procedures are used to encapsulate a set of operations or queries to execute on a database server. For example, operations on an employee database (hire, fire, promote, lookup) could be coded as stored procedures executed by application code. Stored procedures can be compiled and executed with different parameters and results, and they may have any combination of input, output, and input/output parameters.
Stored procedures are supported by most DBMSs, but there is a fair amount of variation in their syntax and capabilities. For this reason, we will show you a simple example of what a stored procedure looks like and how it is invoked from JDBC, but this sample is not intended to be run.
SQL Statements for Creating a Stored Procedure
A stored procedure is a group of SQL statements that form a logical unit and perform a particular task, and they are used to encapsulate a set of operations or queries to execute on a database server. For example, operations on an employee database (hire, fire, promote, lookup) could be coded as stored procedures executed by application code. Stored procedures can be compiled and executed with different parameters and results, and they may have any combination of input, output, and input/output parameters.
Note: Stored procedures are supported by most DBMSs, but there is a fair amount of variation in their syntax and capabilities. For this reason, this simple example of what a stored procedure looks like and how it is invoked from JDBC is not intended to be run.
This simple stored procedure has no parameters. Even though most stored procedures do something more complex than this example, it serves to illustrate some basic points about them. As previously stated, the syntax for defining a stored procedure is different for each DBMS. For example, some use begin . . . end , or other keywords to indicate the beginning and ending of the procedure definition. In some DBMSs, the following SQL statement creates a stored procedure:
The following code puts the SQL statement into a string and assigns it to the variable createProcedure, which we will use later:
The following code fragment uses the Connection object con to create a Statement object, which is used to send the SQL statement creating the stored procedure to the database:
The procedure SHOW_SUPPLIERS is compiled and stored in the database as a database object that can be called, similar to the way you would call a method.
Calling a Stored Procedure from JDBC
JDBC allows you to call a database stored procedure from an application written in the Java programming language. The first step is to create a CallableStatement object. As with Statement and PreparedStatement objects, this is done with an open Connection object. A callableStatement object contains a call to a stored procedure; it does not contain the stored procedure itself. The first line of code below creates a call to the stored procedure SHOW_SUPPLIERS using the connection con. The part that is enclosed in curly braces is the escape syntax for stored procedures. When the driver encounters "{call SHOW_SUPPLIERS}", it will translate this escape syntax into the native SQL used by the database to call the stored procedure named SHOW_SUPPLIERS.
The ResultSet rs will be similar to the following:
Note that the method used to execute cs is executeQuery because cs calls a stored procedure that contains one query and thus produces one result set. If the procedure had contained one update or one DDL statement, the method executeUpdate would have been the one to use. It is sometimes the case, however, that a stored procedure contains more than one SQL statement, in which case it will produce more than one result set, more than one update count, or some combination of result sets and update counts. In this case, where there are multiple results, the method execute should be used to execute the CallableStatement .
The class CallableStatement is a subclass of PreparedStatement, so a CallableStatement object can take input parameters just as a PreparedStatement object can. In addition, a CallableStatement object can take output parameters, or parameters that are for both input and output. INOUT parameters and the method execute are used rarely.
Creating Complete JDBC Applications
Up to this point, you have seen only code fragments. Now you will see some samples.
The first sample code creates the table COFFEES; the second one inserts values into the table and prints the results of a query. The third application creates the table SUPPLIERS, and the fourth populates it with values. After you have run this code, you can try a query that is a join between the tables COFFEES and SUPPLIERS, as in the fifth code example. The sixth code sample is an application that demonstrates a transaction and also shows how to set placeholder parameters in a PreparedStatement object using a for loop.
Because they are complete applications, they include some elements of the Java programming language we have not shown before in the code fragments. We will explain these elements briefly here.
Putting Code in a Class Definition
In the Java™ programming language, any code you want to execute must be inside a class definition. You type the class definition in a file and give the file the name of the class with .java appended to it. So if you have a class named MySQLStatement, its definition should be in a file named MySQLStatement.java.
Importing Classes to Make Them Visible
The first thing to do is to import the packages or classes you will be using in the new class. The classes in our examples all use the java.sql package (the JDBC™ API), which becomes available when the following line of code precedes the class definition:
import java.sql.*;
The star ( * ) indicates that all of the classes in the package java.sql are to be imported. Importing a class makes it visible and means that you do not have to write out the fully qualified name when you use a method or field from that class. If you do not include " import java.sql.*; " in your code, you will have to write " java.sql. " plus the class name in front of all the JDBC fields or methods you use every time you use them. Note that you can import individual classes selectively rather than a whole package. Java does not require that you import classes or packages, but doing so makes writing code a lot more convenient.
Any lines importing classes appear at the top of all the code samples, as they must if they are going to make the imported classes visible to the class being defined. The actual class definition follows any lines that import classes.
Using the main Method
If a class is to be executed, it must contain a static public main method. This method comes right after the line declaring the class and invokes the other methods in the class. The keyword static indicates that this method operates on a class level rather than on individual instances of a class. The keyword public means that members of any class can access this method. Since we are not just defining classes to be used by other classes but instead want to run them, the example applications in this chapter all include a main method.
Using try and catch Blocks
Something else all the sample applications include is try and catch blocks. These are the Java programming language's mechanism for handling exceptions. Java requires that when a method throws an exception, there be some mechanism to handle it. Generally a catch block will catch the exception and specify what happens (which you may choose to be nothing). In the sample code, we use two try blocks and two catch blocks. The first try block contains the method Class.forName, from the java.lang package. This method throws a ClassNotFoundException, so the catch block immediately following it deals with that exception. The second try block contains JDBC methods, which all throw SQLExceptions, so one catch block at the end of the application can handle all of the rest of the exceptions that might be thrown because they will all be SQLException objects.
Retrieving Exceptions
JDBC lets you see the warnings and exceptions generated by your DBMS and by the Java compiler. To see exceptions, you can have a catch block print them out. For example, the following two catch blocks from the sample code print out a message explaining the exception:
If you were to run CreateCOFFEES.java twice, you would get an error message similar to this:
This example illustrates printing out the message component of an SQLException object, which is sufficient for most situations.
There are actually three components, however, and to be complete, you can print them all out. The following code fragment shows a catch block that is complete in two ways. First, it prints out all three parts of an SQLException object: the message (a string that describes the error), the SQL state (a string identifying the error according to the X/Open SQLState conventions), and the vendor error code (a number that is the driver vendor's error code number). The SQLException object ex is caught, and its three components are accessed with the methods getMessage , getSQLState , and getErrorCode .
The second way the following catch block is complete is that it gets all of the exceptions that might have been thrown. If there is a second exception, it will be chained to ex, so ex.getNextException is called to see if there is another exception. If there is, the while loop continues and prints out the next exception's message, SQLState, and vendor error code. This continues until there are no more exceptions.
If you were to substitute the catch block above into the code and run it after the table COFFEES had already been created, you would get the following printout:
--- SQLException caught ---
Message: There is already an object named 'COFFEES' in the database.
Severity 16, State 1, Line 1
SQLState: 42501
ErrorCode: 2714
The vendor error code is specific to each driver, so you need to check your driver documentation for a list of error codes and what they mean.
Retrieving Warnings
SQLWarning objects are a subclass of SQLException that deal with database access warnings. Warnings do not stop the execution of an application, as exceptions do; they simply alert the user that something did not happen as planned. For example, a warning might let you know that a privilege you attempted to revoke was not revoked. Or a warning might tell you that an error occurred during a requested disconnection.
A warning can be reported on a Connection object, a Statement object (including PreparedStatement and CallableStatement objects), or a ResultSet object. Each of these classes has a getWarnings method, which you must invoke in order to see the first warning reported on the calling object. If getWarnings returns a warning, you can call the SQLWarning method getNextWarning on it to get any additional warnings. Executing a statement automatically clears the warnings from a previous statement, so they do not build up. This means, however, that if you want to retrieve warnings reported on a statement, you must do so before you execute another statement.
The following code fragment illustrates how to get complete information about any warnings reported on the Statement object stmt and also on the ResultSet object rs :
Warnings are actually rather uncommon. Of those that are reported, by far the most common warning is a DataTruncation warning, a subclass of SQLWarning. All DataTruncation objects have an SQLState of 01004, indicating that there was a problem with reading or writing data. DataTruncation methods let you find out in which column or parameter data was truncated, whether the truncation was on a read or write operation, how many bytes should have been transferred, and how many bytes were actually transferred.
Running the Sample Applications
You are now ready to actually try out some sample code. The directory book.html contains complete, runnable applications that illustrate concepts presented in this chapter and the next. You can download this sample code from the JDBC web site located at:
JDBC Technology Code Samples
Before you can run one of these applications, you will need to edit the file by substituting the appropriate information for the following variables:
url
the JDBC URL; parts one and two are supplied by your driver, and the third part specifies your data source
myLogin
your login name or user name
myPassword
your password for the DBMS
myDriver.ClassName
the class name supplied with your driver
The first example application is the class CreateCoffees , which is in a file named CreateCoffees.java . Below are instructions for running CreateCoffees.java on the three major platforms.
The first line in the instructions below compiles the code in the file CreateCoffees.java . If the compilation is successful, it will produce a file named CreateCoffees.class , which contains the bytecodes translated from the file CreateCoffees.java . These bytecodes will be interpreted by the Java Virtual Machine, which is what makes it possible for Java code to run on any machine with a Java Virtual Machine installed on it.
The second line of code is what actually makes the code run. Note that you use the name of the class, CreateCoffees , not the name of the file, CreateCoffees.class .
UNIX
javac CreateCoffees.java
java CreateCoffees
Windows 95/NT
javac CreateCoffees.java
java CreateCoffees
Creating an Applet from an Application
Suppose that the owner of The Coffee Break wants to display his current coffee prices in an applet on his web page. He can be sure of always displaying the most current price by having the applet get the price directly from his database.
In order to do this, he needs to create two files of code, one with applet code, and one with HTML code. The applet code contains the JDBC code that would appear in a regular application plus additional code for running the applet and displaying the results of the database query. In our example, the applet code is in the file OutputApplet.java. To display our applet in an HTML page, the file OutputApplet.html tells the browser what to display and where to display it.
The rest of this section will tell you about various elements found in applet code that are not present in standalone application code. Some of these elements involve advanced aspects of the Java programming language. We will give you some rationale and some basic explanation, but explaining them fully is beyond the scope of this tutorial. For purposes of this sample applet, you only need to grasp the general idea, so don't worry if you don't understand everything. You can use the applet code as a template, substituting your own queries for the one in the applet.
Writing Applet Code
To begin with, applets will import classes not used by standalone applications. Our applet imports two classes that are special to applets: the class Applet , which is part of the java.applet package, and the class Graphics , which is part of the java.awt package. This applet also imports the general-purpose class java.util.Vector so that we have access to an array-like container whose size can be modified. This code uses Vector objects to store query results so that they can be displayed later.
All applets extend the Applet class; that is, they are subclasses of Applet . Therefore, every applet definition must contain the words extends Applet , as shown here:
public class MyAppletName extends Applet {
. . .
}
In our applet example, OutputApplet, this line also includes the words implements Runnable , so it looks like this:
public class OutputApplet extends Applet implements Runnable {
. . .
}
Runnable is an interface that makes it possible to run more than one thread at a time. A thread is a sequential flow of control, and it is possible for a program to be multithreaded, that is, to have many threads doing different things concurrently. The class OutputApplet implements the interface Runnable by defining the method run , the only method in Runnable . In our example the run method contains the JDBC code for opening a connection, executing a query, and getting the results from the result set. Since database connections can be slow, and can sometimes take several seconds, it is generally a good idea to structure an applet so that it can handle the database work in a separate thread.
Similar to a standalone application, which must have a main method, an applet must implement at least one init , start , or paint method. Our example applet defines a start method and a paint method. Every time start is invoked, it creates a new thread (named worker ) to re-evaluate the database query. Every time paint is invoked, it displays either the query results or a string describing the current status of the applet.
As stated previously, the run method defined in OutputApplet contains the JDBC code. When the thread worker invokes the method start , the run method is called automatically, and it executes the JDBC code in the thread worker . The code in run is very similar to the code you have seen in our other sample code with three exceptions. First, it uses the class Vector to store the results of the query. Second, it does not print out the results but rather adds them to the Vector results for display later. Third, it likewise does not print out exceptions and instead records error messages for later display.
Applets have various ways of drawing, or displaying, their content. This applet, a very simple one that has only text, uses the method drawString (part of the Graphics class) to display its text. The method drawString takes three arguments: (1) the string to be displayed, (2) the x coordinate, indicating the horizontal starting point for displaying the string, and (3) the y coordinate, indicating the vertical starting point for displaying the string (which is below the text).
The method paint is what actually displays something on the screen, and in OutputApplet.java , it is defined to contain calls to the method drawString . The main thing drawString displays is the contents of the Vector results (the stored query results). When there are no query results to display, drawString will display the current contents of the String message . This string will be "Initializing" to begin with. It gets set to "Connecting to database" when the method start is called, and the method setError sets it to an error message when an exception is caught. Thus, if the database connection takes much time, the person viewing this applet will see the message "Connecting to database" because that will be the contents of message at that time. (The method paint is called by AWT when it wants the applet to display its current state on the screen.)
The last two methods defined in the class OutputApplet, setError and setResults are private, which means that they can be used only by OutputApplet. These methods both invoke the method repaint , which clears the screen and calls paint . So if setResults calls repaint , the query results will be displayed, and if setError calls repaint , an error message will be displayed.
A final point to be made is that all the methods defined in OutputApplet except run are synchronized . The keyword synchronized indicates that while a method is accessing an object, other synchronized methods are blocked from accessing that object. The method run is not declared synchronized so that the applet can still paint itself on the screen while the database connection is in progress. If the database access methods were synchronized , they would prevent the applet from being repainted while they are executing, and that could result in delays with no accompanying status message.
To summarize, in an applet, it is good programming practice to do some things you would not need to do in a standalone application:
1. Put your JDBC code in a separate thread
2. Display status messages on the screen during any delays, such as when a database connection is taking a long time
3. Display error messages on the screen instead of printing them to System.out or System.err .
Running an Applet
Before running our sample applet, you need to compile the file OutputApplet.java . This creates the file OutputApplet.class , which is referenced by the file OutputApplet.html.
The easiest way to run an applet is to use the appletviewer, which is included as part of the JDK. Simply follow the instructions below for your platform to compile and run OutputApplet.java :
UNIX
javac OutputApplet.java
appletviewer OutputApplet.html
Windows 95/NT
javac OutputApplet.java
appletviewer OutputApplet.html
Applets loaded over the network are subject to various security restrictions. Although this can seem bothersome at times, it is absolutely necessary for network security, and security is one of the major advantages of using the Java programming language. An applet cannot make network connections except to the host it came from unless the browser allows it. Whether one is able to treat locally installed applets as "trusted" also depends on the security restrictions imposed by the browser. An applet cannot ordinarily read or write files on the host that is executing it, and it cannot load libraries or define native methods.
Applets can usually make network connections to the host they came from, so they can work very well on intranets.
The JDBC-ODBC Bridge driver is a somewhat special case. It can be used quite successfully for intranet access, but it requires that ODBC, the bridge, the bridge native library, and JDBC be installed on every client. With this configuration, intranet access works from Java applications and from trusted applets. However, since the bridge requires special client configuration, it is not practical to run applets on the Internet with the JDBC-ODBC Bridge driver. Note that this is a limitation of the JDBC-ODBC Bridge, not of JDBC. With a pure Java JDBC driver, you do not need any special configuration to run applets on the Internet.
from java lecture
Connecting to a Database
We have a network which supports a communication protocol e.g. TCP/IP
The Database listens to a port (a 2 way thing)
The Database will define some higher level protocol for queries (SQL) and returned results (tables)
we DON’T WANT to have to code at this level! Hence JDBC (it is all done for us again!)
ODBC Came First
Slow
JDBC is A Java API for database communication
Connect to relational databases
Send SQL commands
Process results
About the Database
host = “unnc-cslinux”;
database = “your username”;
port = 5432;
user = “your username”;
password = “I will email you";
Register the Driver
import java.sql.*; // You NEED this!
…
Connection con;
try {
Class.forName(“org.postgresql.Driver");
} catch (ClassNotFoundException ex) {
System.out.println(ex.getErrorCode());
}
Connect to Database
Connection con;
con = DriverManager.getConnection(url, username, passwd);
Where:
String url = “jdbc:postgresql://unnc-cslinux:5432/yourusername”;
Create and Execute a Statement
Statement st = con.createStatement();
String qry = “SELECT ...”;
ResultSet rs = st.executeQuery(qry);
String qry = “INSERT ...”;
st.executeUpdate(qry);
JDBC Statements
Getting the data returned
String code = rs.getString(“code");
Needs to be in a try block
Of course other methods are:
getInt();
getDouble();
Navigating the ResultSet
while(rs.next()) {
… process a row
}
Important point…
con.close();
How to connect in Java
DBconnect() {
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
pConnection=DriverManager.getConnection("jdbc:odbc:mydb.mdb","","");
pStatement = pConnection.createStatement();
}
catch(Exception e) { e.getMessage(); // print this out
}
}
from DBS lecture:
Basic steps when using JDBC
Register a database driver
Open a connection
Pass some queries to the database
Process the results as needed
Close the connection
Deal with any errors
// Declaration of any database objects
try {
// Some database code
} catch (Exception e) {
// Error reporting etc.
} finally {
// Make sure all database objects are
// closed and cleaned up
}
The JDBC API is a Java API that can access any kind of tabular data, especially data stored in a Relational Database.
JDBC helps you to write java applications that manage these three programming activities:
1. Connect to a data source, like a database
2. Send queries and update statements to the database
3. Retrieve and process the results received from the database in answer to your query
The following simple code fragment gives a simple example of these three steps:
Connection con = DriverManager.getConnection
( "jdbc:myDriver:wombat", "myLogin","myPassword");
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("SELECT a, b, c FROM Table1");
while (rs.next()) {
int x = rs.getInt("a");
String s = rs.getString("b");
float f = rs.getFloat("c");
}
This short code fragment instantiates a DriverManager object to connect to a database driver and log into the database, instantiates a Statement object that carries your SQL language query to the database; instantiates a ResultSet object that retrieves the results of your query, and executes a simple while loop, which retrieves and displays those results. It's that simple.
JDBC Product Components
JDBC includes four components:
1. The JDBC API —
The JDBC™ API provides programmatic access to relational data from the Java™ programming language. Using the JDBC API, applications can execute SQL statements, retrieve results, and propagate changes back to an underlying data source. The JDBC API can also interact with multiple data sources in a distributed, heterogeneous environment.
The JDBC API is part of the Java platform, which includes the Java™ Standard Edition (Java™ SE ) and the Java™ Enterprise Edition (Java™ EE). The JDBC 4.0 API is divided into two packages: java.sql and javax.sql. Both packages are included in the Java SE and Java EE platforms.
2. JDBC Driver Manager —
The JDBC DriverManager class defines objects which can connect Java applications to a JDBC driver. DriverManager has traditionally been the backbone of the JDBC architecture. It is quite small and simple.
The Standard Extension packages javax.naming and javax.sql let you use a DataSource object registered with a Java Naming and Directory Interface™ (JNDI) naming service to establish a connection with a data source. You can use either connecting mechanism, but using a DataSource object is recommended whenever possible.
3. JDBC Test Suite —
The JDBC driver test suite helps you to determine that JDBC drivers will run your program. These tests are not comprehensive or exhaustive, but they do exercise many of the important features in the JDBC API.
4. JDBC-ODBC Bridge —
The Java Software bridge provides JDBC access via ODBC drivers. Note that you need to load ODBC binary code onto each client machine that uses this driver. As a result, the ODBC driver is most appropriate on a corporate network where client installations are not a major problem, or for application server code written in Java in a three-tier architecture.
This Trail uses the first two of these these four JDBC components to connect to a database and then build a java program that uses SQL commands to communicate with a test Relational Database. The last two components are used in specialized environments to test web applications, or to communicate with ODBC-aware DBMSs.
JDBC Architecture
Two-tier and Three-tier Processing Models
The JDBC API supports both two-tier and three-tier processing models for database access.
Figure 1: Two-tier Architecture for Data Access.
The DBMS-proprietary protocol provides two-way communication between the client machine and the database server
In the two-tier model, a Java applet or application talks directly to the data source. This requires a JDBC driver that can communicate with the particular data source being accessed. A user's commands are delivered to the database or other data source, and the results of those statements are sent back to the user. The data source may be located on another machine to which the user is connected via a network. This is referred to as a client/server configuration, with the user's machine as the client, and the machine housing the data source as the server. The network can be an intranet, which, for example, connects employees within a corporation, or it can be the Internet.
In the three-tier model, commands are sent to a "middle tier" of services, which then sends the commands to the data source. The data source processes the commands and sends the results back to the middle tier, which then sends them to the user. MIS directors find the three-tier model very attractive because the middle tier makes it possible to maintain control over access and the kinds of updates that can be made to corporate data. Another advantage is that it simplifies the deployment of applications. Finally, in many cases, the three-tier architecture can provide performance advantages.
Figure 2: Three-tier Architecture for Data Access.
The DBMS-proprietary protocol provides two-way communication between the database server and the server machine. HTTP, RMI, CORBA or other calls provide two way communication between the server machine and the client machine
Until recently, the middle tier has often been written in languages such as C or C++, which offer fast performance. However, with the introduction of optimizing compilers that translate Java bytecode into efficient machine-specific code and technologies such as Enterprise JavaBeans™, the Java platform is fast becoming the standard platform for middle-tier development. This is a big plus, making it possible to take advantage of Java's robustness, multithreading, and security features.
With enterprises increasingly using the Java programming language for writing server code, the JDBC API is being used more and more in the middle tier of a three-tier architecture. Some of the features that make JDBC a server technology are its support for connection pooling, distributed transactions, and disconnected rowsets. The JDBC API is also what allows access to a data source from a Java middle tier.
A Relational Database Overview
A database is a means of storing information in such a way that information can be retrieved from it. In simplest terms, a relational database is one that presents information in tables with rows and columns. A table is referred to as a relation in the sense that it is a collection of objects of the same type (rows). Data in a table can be related according to common keys or concepts, and the ability to retrieve related data from a table is the basis for the term relational database. A Database Management System (DBMS) handles the way data is stored, maintained, and retrieved. In the case of a relational database, a Relational Database Management System (RDBMS) performs these tasks. DBMS as used in this book is a general term that includes RDBMS.
Integrity Rules
Relational tables follow certain integrity rules to ensure that the data they contain stay accurate and are always accessible. First, the rows in a relational table should all be distinct. If there are duplicate rows, there can be problems resolving which of two possible selections is the correct one. For most DBMSs, the user can specify that duplicate rows are not allowed, and if that is done, the DBMS will prevent the addition of any rows that duplicate an existing row.
A second integrity rule of the traditional relational model is that column values must not be repeating groups or arrays. A third aspect of data integrity involves the concept of a null value. A database takes care of situations where data may not be available by using a null value to indicate that a value is missing. It does not equate to a blank or zero. A blank is considered equal to another blank, a zero is equal to another zero, but two null values are not considered equal.
When each row in a table is different, it is possible to use one or more columns to identify a particular row. This unique column or group of columns is called a primary key. Any column that is part of a primary key cannot be null; if it were, the primary key containing it would no longer be a complete identifier. This rule is referred to as entity integrity.
Table 1.2 illustrates some of these relational database concepts. It has five columns and six rows, with each row representing a different employee.
Table 1.2: Employees
Employee_Number First_name Last_Name Date_of_Birth Car_Number
10001 Axel Washington 28-Aug-43 5
10083 Arvid Sharma 24-Nov-54 null
10120 Jonas Ginsberg 01-Jan-69 null
10005 Florence Wojokowski 04-Jul-71 12
10099 Sean Washington 21-Sep-66 null
10035 Elizabeth Yamaguchi 24-Dec-59 null
The primary key for this table would generally be the employee number because each one is guaranteed to be different. (A number is also more efficient than a string for making comparisons.) It would also be possible to use First_Name and Last_Name because the combination of the two also identifies just one row in our sample database. Using the last name alone would not work because there are two employees with the last name of "Washington." In this particular case the first names are all different, so one could conceivably use that column as a primary key, but it is best to avoid using a column where duplicates could occur. If Elizabeth Taylor gets a job at this company and the primary key is First_Name, the RDBMS will not allow her name to be added (if it has been specified that no duplicates are permitted). Because there is already an Elizabeth in the table, adding a second one would make the primary key useless as a way of identifying just one row. Note that although using First_Name and Last_Name is a unique composite key for this example, it might not be unique in a larger database. Note also that Table 1.2 assumes that there can be only one car per employee.
SELECT Statements
SQL is a language designed to be used with relational databases. There is a set of basic SQL commands that is considered standard and is used by all RDBMSs. For example, all RDBMSs use the SELECT statement.
A SELECT statement, also called a query, is used to get information from a table. It specifies one or more column headings, one or more tables from which to select, and some criteria for selection. The RDBMS returns rows of the column entries that satisfy the stated requirements. A SELECT statement such as the following will fetch the first and last names of employees who have company cars:
SELECT First_Name, Last_Name
FROM Employees
WHERE Car_Number IS NOT NULL
The result set (the set of rows that satisfy the requirement of not having null in the Car_Number column) follows. The first name and last name are printed for each row that satisfies the requirement because the SELECT statement (the first line) specifies the columns First_Name and Last_Name. The FROM clause (the second line) gives the table from which the columns will be selected.
FIRST_NAME LAST_NAME
---------- -----------
Axel Washington
Florence Wojokowski
The following code produces a result set that includes the whole table because it asks for all of the columns in the table Employees with no restrictions (no WHERE clause). Note that SELECT * means "SELECT all columns."
SELECT *
FROM Employees
WHERE Clauses
The WHERE clause in a SELECT statement provides the criteria for selecting values. For example, in the following code fragment, values will be selected only if they occur in a row in which the column Last_Name begins with the string 'Washington'.
SELECT First_Name, Last_Name
FROM Employees
WHERE Last_Name LIKE 'Washington%'
The keyword LIKE is used to compare strings, and it offers the feature that patterns containing wildcards can be used. For example, in the code fragment above, there is a percent sign (%) at the end of 'Washington', which signifies that any value containing the string 'Washington' plus zero or more additional characters will satisfy this selection criterion. So 'Washington' or 'Washingtonian' would be matches, but 'Washing' would not be. The other wildcard used in LIKE clauses is an underbar (_), which stands for any one character. For example,
WHERE Last_Name LIKE 'Ba_man'
would match 'Batman', 'Barman', 'Badman', 'Balman', 'Bagman', 'Bamman', and so on.
The code fragment below has a WHERE clause that uses the equal sign (=) to compare numbers. It selects the first and last name of the employee who is assigned car 12.
SELECT First_Name, Last_Name
FROM Employees
WHERE Car_Number = 12
The next code fragment selects the first and last names of employees whose employee number is greater than 10005:
SELECT First_Name, Last_Name
FROM Employees
WHERE Employee_Number > 10005
WHERE clauses can get rather elaborate, with multiple conditions and, in some DBMSs, nested conditions. This overview will not cover complicated WHERE clauses, but the following code fragment has a WHERE clause with two conditions; this query selects the first and last names of employees whose employee number is less than 10100 and who do not have a company car.
SELECT First_Name, Last_Name
FROM Employees
WHERE Employee_Number < 10100 and Car_Number IS NULL
A special type of WHERE clause involves a join, which is explained in the next section.
Joins
A distinguishing feature of relational databases is that it is possible to get data from more than one table in what is called a join. Suppose that after retrieving the names of employees who have company cars, one wanted to find out who has which car, including the make, model, and year of car. This information is stored in another table, Cars, shown in Table 1.3.
Table 1.3. Cars
Car Number Make Model Year
5 Honda Civic DX 1996
12 Toyota Corolla 1999
There must be one column that appears in both tables in order to relate them to each other. This column, which must be the primary key in one table, is called the foreign key in the other table. In this case, the column that appears in two tables is Car_Number, which is the primary key for the table Cars and the foreign key in the table Employees. If the 1996 Honda Civic were wrecked and deleted from the Cars table, then Car_Number 5 would also have to be removed from the Employees table in order to maintain what is called referential integrity. Otherwise, the foreign key column (Car_Number) in Employees would contain an entry that did not refer to anything in Cars. A foreign key must either be null or equal to an existing primary key value of the table to which it refers. This is different from a primary key, which may not be null. There are several null values in the Car_Number column in the table Employees because it is possible for an employee not to have a company car.
The following code asks for the first and last names of employees who have company cars and for the make, model, and year of those cars. Note that the FROM clause lists both Employees and Cars because the requested data is contained in both tables. Using the table name and a dot (.) before the column name indicates which table contains the column.
SELECT Employees.First_Name, Employees.Last_Name, Cars.Make,
Cars.Model, Cars.Year
FROM Employees, Cars
WHERE Employees.Car_Number = Cars.Car_Number
This returns a result set that will look similar to the following:
FIRST_NAME LAST_NAME MAKE MODEL YEAR
----------- ------------ -------- --------- -------
Axel Washington Honda CivicDX 1996
Florence Wojokowski Toyota Corolla 1999
Common SQL Commands
SQL commands are divided into categories, the two main ones being Data Manipulation Language (DML) commands and Data Definition Language (DDL) commands. DML commands deal with data, either retrieving it or modifying it to keep it up-to-date. DDL commands create or change tables and other database objects such as views and indexes.
A list of the more common DML commands follows:
* SELECT — used to query and display data from a database. The SELECT statement specifies which columns to include in the result set. The vast majority of the SQL commands used in applications are SELECT statements.
* INSERT — adds new rows to a table. INSERT is used to populate a newly created table or to add a new row (or rows) to an already-existing table.
* DELETE — removes a specified row or set of rows from a table
* UPDATE — changes an existing value in a column or group of columns in a table
The more common DDL commands follow:
* CREATE TABLE — creates a table with the column names the user provides. The user also needs to specify a type for the data in each column. Data types vary from one RDBMS to another, so a user might need to use metadata to establish the data types used by a particular database. CREATE TABLE is normally used less often than the data manipulation commands because a table is created only once, whereas adding or deleting rows or changing individual values generally occurs more frequently.
* DROP TABLE — deletes all rows and removes the table definition from the database. A JDBC API implementation is required to support the DROP TABLE command as specified by SQL92, Transitional Level. However, support for the CASCADE and RESTRICT options of DROP TABLE is optional. In addition, the behavior of DROP TABLE is implementation-defined when there are views or integrity constraints defined that reference the table being dropped.
* ALTER TABLE — adds or removes a column from a table. It also adds or drops table constraints and alters column attributes
Result Sets and Cursors
The rows that satisfy the conditions of a query are called the result set. The number of rows returned in a result set can be zero, one, or many. A user can access the data in a result set one row at a time, and a cursor provides the means to do that. A cursor can be thought of as a pointer into a file that contains the rows of the result set, and that pointer has the ability to keep track of which row is currently being accessed. A cursor allows a user to process each row of a result set from top to bottom and consequently may be used for iterative processing. Most DBMSs create a cursor automatically when a result set is generated.
Earlier JDBC API versions added new capabilities for a result set's cursor, allowing it to move both forward and backward and also allowing it to move to a specified row or to a row whose position is relative to another row.
Transactions
When one user is accessing data in a database, another user may be accessing the same data at the same time. If, for instance, the first user is updating some columns in a table at the same time the second user is selecting columns from that same table, it is possible for the second user to get partly old data and partly updated data. For this reason, DBMSs use transactions to maintain data in a consistent state (data consistency) while allowing more than one user to access a database at the same time (data concurrency).
A transaction is a set of one or more SQL statements that make up a logical unit of work. A transaction ends with either a commit or a rollback, depending on whether there are any problems with data consistency or data concurrency. The commit statement makes permanent the changes resulting from the SQL statements in the transaction, and the rollback statement undoes all changes resulting from the SQL statements in the transaction.
A lock is a mechanism that prohibits two transactions from manipulating the same data at the same time. For example, a table lock prevents a table from being dropped if there is an uncommitted transaction on that table. In some DBMSs, a table lock also locks all of the rows in a table. A row lock prevents two transactions from modifying the same row, or it prevents one transaction from selecting a row while another transaction is still modifying it.
Stored Procedures
A stored procedure is a group of SQL statements that can be called by name. In other words, it is executable code, a mini-program, that performs a particular task that can be invoked the same way one can call a function or method. Traditionally, stored procedures have been written in a DBMS-specific programming language. The latest generation of database products allows stored procedures to be written using the Java programming language and the JDBC API. Stored procedures written in the Java programming language are bytecode portable between DBMSs. Once a stored procedure is written, it can be used and reused because a DBMS that supports stored procedures will, as its name implies, store it in the database.
The following code is an example of how to create a very simple stored procedure using the Java programming language. Note that the stored procedure is just a static Java method that contains normal JDBC code. It accepts two input parameters and uses them to change an employee's car number.
Do not worry if you do not understand the example at this point. The code example below is presented only to illustrate what a stored procedure looks like. You will learn how to write the code in this example in the tutorials that follow.
import java.sql.*;
public class UpdateCar {
public static void UpdateCarNum(int carNo, int empNo)
throws SQLException {
Connection con = null;
PreparedStatement pstmt = null;
try {
con = DriverManager.getConnection("jdbc:default:connection");
pstmt = con.prepareStatement(
"UPDATE EMPLOYEES SET CAR_NUMBER = ? " +
"WHERE EMPLOYEE_NUMBER = ?");
pstmt.setInt(1, carNo);
pstmt.setInt(2, empNo);
pstmt.executeUpdate();
}
finally {
if (pstmt != null) pstmt.close();
}
}
}
Metadata
Databases store user data, and they also store information about the database itself. Most DBMSs have a set of system tables, which list tables in the database, column names in each table, primary keys, foreign keys, stored procedures, and so forth. Each DBMS has its own functions for getting information about table layouts and database features. JDBC provides the interface DatabaseMetaData, which a driver writer must implement so that its methods return information about the driver and/or DBMS for which the driver is written. For example, a large number of methods return whether or not the driver supports a particular functionality. This interface gives users and tools a standardized way to get metadata. In general, developers writing tools and drivers are the ones most likely to be concerned with metadata.
Establishing a Connection
First, you need to establish a connection with the DBMS you want to use. Typically, a JDBC™ application connects to a target data source using one of two mechanisms:
* DriverManager: This fully implemented class requires an application to load a specific driver, using a hardcoded URL. As part of its initialization, the DriverManager class attempts to load the driver classes referenced in the jdbc.drivers system property. This allows you to customize the JDBC Drivers used by your applications.
* DataSource: This interface is preferred over DriverManager because it allows details about the underlying data source to be transparent to your application. A DataSource object's properties are set so that it represents a particular data source.
Establishing a connection involves two steps: Loading the driver, and making the connection.
Loading the Driver
Loading the driver you want to use is very simple. It involves just one line of code in your program. To use the Java DB driver, add the following line of code:
Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
Your driver documentation provides the class name to use. In the example above, EmbeddedDriver is one of the drivers for Java DB.
Calling the Class.forName automatically creates an instance of a driver and registers it with the DriverManager, so you don't need to create an instance of the class. If you were to create your own instance, you would be creating an unnecessary duplicate, but it would do no harm.
After you have loaded a driver, it can make a connection with a DBMS.
Making the Connection
The second step in establishing a connection is to have the appropriate driver connect to the DBMS.
Using the DriverManager Class
The DriverManager class works with the Driver interface to manage the set of drivers available to a JDBC client. When the client requests a connection and provides a URL, the DriverManager is responsible for finding a driver that recognizes the URL and for using it to connect to the corresponding data source. Connection URLs have the following form:
jdbc:derby:
The dbName portion of the URL identifies a specific database. A database can be in one of many locations: in the current working directory, on the classpath, in a JAR file, in a specific Java DB database home directory, or in an absolute location on your file system.
If you are using a vendor-specific driver, such as Oracle, the documentation will tell you what subprotocol to use, that is, what to put after jdbc: in the JDBC URL. For example, if the driver developer has registered the name OracleDriver as the subprotocol, the first and second parts of the JDBC URL will be jdbc.driver.OracleDriver . The driver documentation will also give you guidelines for the rest of the JDBC URL. This last part of the JDBC URL supplies information for identifying the data source.
The getConnection method establishes a connection:
Connection conn = DriverManager.getConnection("jdbc:derby:COFFEES");
In place of " myLogin " you insert the name you use to log in to the DBMS; in place of " myPassword " you insert your password for the DBMS. So, if you log in to your DBMS with a login name of " Fernanda " and a password of " J8, " just these two lines of code will establish a connection:
String url = "jdbc:derby:Fred";
Connection con = DriverManager.getConnection(url, "Fernanda", "J8");
If one of the drivers you loaded recognizes the JDBC URL supplied to the method DriverManager.getConnection, that driver establishes a connection to the DBMS specified in the JDBC URL. The DriverManager class, true to its name, manages all of the details of establishing the connection for you behind the scenes. Unless you are writing a driver, you probably won't use any of the methods in the interface Driver, and the only DriverManager method you really need to know is DriverManager.getConnection
The connection returned by the method DriverManager.getConnection is an open connection you can use to create JDBC statements that pass your SQL statements to the DBMS. In the previous example, con is an open connection, and you use it in the examples that follow.
Using a DataSource Object for a connection
Using a DataSource object increases application portability by making it possible for an application to use a logical name for a data source instead of having to supply information specific to a particular driver. The following example shows how to use a DataSource to establish a connection:
You can configure a DataSource using a tool or manually. For example, Here is an example of a DataSource lookup:
InitialContext ic = new InitialContext()
DataSource ds = ic.lookup("java:comp/env/jdbc/myDB");
Connection con = ds.getConnection();
DataSource ds = (DataSource) org.apache.derby.jdbc.ClientDataSource()
ds.setPort(1527);
ds.setHost("localhost");
ds.setUser("APP")
ds.setPassword("APP");
Connection con = ds.getConnection();
DataSource implementations must provide getter and setter methods for each property they support. These properties typically are initialized when the DataSource object is deployed.
VendorDataSource vds = new VendorDataSource();
vds.setServerName("my_database_server");
String name = vds.getServerName();
JDBC-ODBC Bridge Driver
For normal use, you should obtain a commercial JDBC driver from a vendor such as your database vendor or your database middleware vendor. The JDBC-ODBC Bridge driver provided with JDBC is recommended only for development and testing, or when no other alternative is available
Setting Up Tables
For the following examples, you need to create a few tables in your database. The first table, COFFEES, contains the essential information about the coffees sold at The Coffee Break, including the coffee names, their prices, the number of pounds sold the current week, and the number of pounds sold to date. The table COFFEES, which is describe in more detail later, is shown here:
The Tables Used in the Example Database
The column storing the coffee name is COF_NAME, and it holds values with an SQL type of VARCHAR and a maximum length of 32 characters. Since the names are different for each type of coffee sold, the name uniquely identifies a particular coffee and serves as the primary key. The second column, named SUP_ID, holds a number identifying the coffee supplier; this number is of SQL type INTEGER. The third column, called PRICE, stores values with an SQL type of FLOAT because it needs to hold values with decimal points. (Note that money values would normally be stored in an SQL type DECIMAL or NUMERIC, but because of differences among DBMSs and to avoid incompatibility with older versions of JDBC, you are using the more standard type FLOAT for this tutorial.) The column named SALES stores values of SQL type INTEGER and indicates the number of pounds of coffee sold during the current week. The final column, TOTAL, contains an SQL INTEGER, which gives the total number of pounds of coffee sold to date.
SUPPLIERS, the second table in our database, gives information about each of the suppliers:
The tables COFFEES and SUPPLIERS both contain the column SUP_ID. These two tables can be used in SELECT statements to get data based on the information in both tables. The column SUP_ID is the primary key in the table SUPPLIERS, and as such, it uniquely identifies each of the coffee suppliers. In the table COFFEES, SUP_ID is called a foreign key. (You can think of a foreign key as being foreign in the sense that it is imported from another table.) Note that each SUP_ID number appears only once in the SUPPLIERS table; this is required for it to be a primary key. In the COFFEES table, where it is a foreign key, it is all right for there to be duplicate SUP_ID numbers because one supplier may sell many types of coffee. Later in this chapter, you see an example of how to use primary and foreign keys in a SELECT statement.
Creating Tables
You can create your tables in at least three ways:
* Using the database or IDE graphical interface
* Using ANT
* Using JDBC SQL written into an application
Creating a Tables Using the NetBeans IDE
Most database vendors provide a graphical user interface (GUI) that makes creating tables in your database easy. In addition, many IDEs, such as the NetBeans IDE, also provides an easy-to-use GUI for table creation.
For example, in the NetBeans™ 5.0 IDE, creating tables in the Java DB™ database is easy. After starting the database, either through the pull-down menu, or by right-clicking Databases, you add tables simply by right-clicking the Tables icon and selecting BBBCreate Table. You get a dialog box that looks like the one below:
Table creator in NetBeans
Fill in the dialog box fields and the NetBeans IDE creates the table. Adding or deleting rows is just as easy. Right-click the table to want to edit.
Creating Tables Using ANT
First, you can use a properties file to feed ANT, which has a simple sql target. Create a simple properties file, such as the one below:
# Database properties are in derby.properties
db.delimiter=;
db.root=${javaee.home}/derby
db.driver=org.apache.derby.jdbc.ClientDriver
db.datasource=org.apache.derby.jdbc.ClientDataSource
db.host=localhost
db.port=1527
db.sid=sun-appserv-samples
db.url=jdbc:derby://${db.host}:${db.port}/${db.sid};create=true;
db.user=APP
db.pwd=APP
Next, create a build.xml file that includes targets for compiling the Java code as well as a target similar to the following:
description="Create database tables and populate database.">
-
password="${db.password}" classpathref="db.classpath"
delimiter="${db.delimiter}" autocommit="false" onerror="abort">
The tutorial.sql file can contain the commands to create the tables and, possibly, populate the tables if need be, such as the example below:
create table COFFEES(cof_name varchar32, price int, sup_id int,
price float, sales int, total int);
The ';' matches the db.delimiter property.
NetBeans contains everything you need, including the Java DB and ANT. Download NetBeans.
Using SQL in an Application
Finally, you can create tables from within the application code itself. The following SQL statement creates the table named COFFEES. The entries within the outer pair of parentheses consist of the name of a column followed by a space and the SQL type to be stored in that column. A comma separates the entry for one column (consisting of column name and SQL type) from the next one. To be sure you are using the correct syntax, see your DBMS documentation. For the Java DB, read the current documentation.
This code does not end with a DBMS statement terminator, which can vary from DBMS to DBMS. For example, Oracle uses a semicolon (;) to indicate the end of a statement, and Sybase uses the word go. The driver you are using will automatically supply the appropriate statement terminator, and you will not need to include it in your JDBC code.
Note: In the CREATE TABLE statement, key words are printed in all capital letters, and each item is on a separate line. SQL does not require either; these conventions simply make statements easier to read. The standard in SQL is that keywords are not case sensitive, so, for example, the following SELECT statement can be written various ways. As an example, these two versions below are equivalent as far as SQL is concerned:
SELECT SUP_NAME, CITY
FROM Suppliers
WHERE SUP_NAME LIKE "Acme, Inc"
select SUP_Name, CITY from Suppliers where
SUP_NAME like "Acme, Inc;
Quoted material, however, is case sensitive: in the name " Washington," W must be capitalized, and the rest of the letters must be lowercase.
Requirements can vary from one DBMS to another when it comes to identifier names.
Retrieving Values from Result Sets
In the previous lesson, ResultSet was briefly mentioned. Now, you will learn the details of the ResultSet interface. The ResultSet interface provides methods for retrieving and manipulting the results of executed queries, and ResultSet objects can have different functionality and characteristics. These characteristics are result set type, result set concurrency, and cursor holdability. A table of data representing a database result set is usually generated by executing a statement that queries the database.
The type of a ResultSet object determines the level of its functionality in two areas: the ways in which the cursor can be manipulated, and how concurrent changes made to the underlying data source are reflected by the ResultSet object.
The sensitivity of the ResultSet object is determined by one of three different ResultSet types:
1. TYPE_FORWARD_ONLY — The result set is not scrollable; its cursor moves forward only, from before the first row to after the last row. The rows contained in the result set depend on how the underlying database materializes the results. That is, it contains the rows that satisfy the query at either the time the query is executed or as the rows are retrieved.
2. TYPE_SCROLL_INSENSITIVE — The result set is scrollable; its cursor can move both forward and backward relative to the current position, and it can move to an absolute position.
3. TYPE_SCROLL_SENSITIVE — The result set is scrollable; its cursor can move both forward and backward relative to the current position, and it can move to an absolute position.
Now, you'll see how to send the above SELECT statements from a program written in the Java™ programming language and how you get the results we showed.
JDBC™ returns results in a ResultSet object, so we need to declare an instance of the class ResultSet to hold our results. In addition, the Statement methods executeQuery and getResultSet both return a ResultSet object, as do various DatabaseMetaData methods. The following code demonstrates declaring the ResultSet object rs and assigning the results of our earlier query to it by using the executeQuery method.
Before you can take advantage of these features, however, you need to create a scrollable ResultSet object. The following line of code illustrates one way to create a scrollable ResultSet object:
Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_READ_ONLY);
ResultSet srs = stmt.executeQuery("SELECT COF_NAME, PRICE FROM COFFEES");
This code is similar to what you have used earlier, except that it adds two arguments to the createStatement method. The first argument is one of three constants added to the ResultSet API to indicate the type of a ResultSet object: TYPE_FORWARD_ONLY, TYPE_SCROLL_INSENSITIVE, and TYPE_SCROLL_SENSITIVE. The second argument is one of two ResultSet constants for specifying whether a result set is read-only or updatable: CONCUR_READ_ONLY and CONCUR_UPDATABLE . The point to remember here is that if you specify a type, you must also specify whether it is read-only or updatable. Also, you must specify the type first, and because both parameters are of type int, the compiler will not complain if you switch the order.
Specifying the constant TYPE_FORWARD_ONLY creates a nonscrollable result set, that is, one in which the cursor moves only forward. If you do not specify any constants for the type and updatability of a ResultSet object, you will automatically get one that is TYPE_FORWARD_ONLY and CONCUR_READ_ONLY.
Using the ResultSet Methods
The variable srs, which is an instance of ResultSet, contains the rows of coffees and prices shown in the result set example above. In order to access the names and prices. A ResultSet object maintains a cursor, which points to its current row of data.
When a ResultSet object is first created, the cursor is positioned before the first row. To move the cursor, you can use the following methods:
* next() - moves the cursor forward one row. Returns true if the cursor is now positioned on a row and false if the cursor is positioned after the last row.
* previous() - moves the cursor backwards one row. Returns true if the cursor is now positioned on a row and false if the cursor is positioned before the first row.
* first() - moves the cursor to the first row in the ResultSet object. Returns true if the cursor is now positioned on the first row and false if the ResultSet object does not contain any rows.
* last() - moves the cursor to the last row in the ResultSet object. Returns true if the cursor is now positioned on the last row and false if the ResultSet object does not contain any rows.
* beforeFirst() - positions the cursor at the start of the ResultSet object, before the first row. If the ResultSet object does not contain any rows, this method has no effect.
* afterLast() - positions the cursor at the end of the ResultSet object, after the last row. If the ResultSet object does not contain any rows, this method has no effect.
* relative(int rows) - moves the cursor relative to its current position.
* absolute(int row) - positions the cursor on the row-th row of the ResultSet object.
Once you have a scrollable ResultSet object, srs in the previous example, you can use it to move the cursor around in the result set. Since the cursor is initially positioned just above the first row of a ResultSet object, the first call to the method next moves the cursor to the first row and makes it the current row. Successive invocations of the method next move the cursor down one row at a time from top to bottom.
Using the getXXX Methods
The ResultSet interface declares getter methods (getBoolean, getLong, and so on) for retrieving column values from the current row. Your application can retrieve values using either the index number of the column or the name of the column. The column index is usually more efficient. Columns are numbered from 1. For maximum portability, result set columns within each row should be read in left-to-right order, and each column should be read only once.
Column names used as input to getter methods are case insensitive. When a getter method is called with a column name and several columns have the same name, the value of the first matching column will be returned. The column name option is designed to be used when column names are used in the SQL query that generated the result set. For columns that are NOT explicitly named in the query, it is best to use column numbers. If column names are used, the programmer should take care to guarantee that they uniquely refer to the intended columns, which can be assured with the SQL AS clause.
The getXXX method of the appropriate type retrieves the value in each column. For example, the first column in each row of srs is COF_NAME, which stores a value of SQL type VARCHAR. The method for retrieving a value of SQL type VARCHAR is getString. The second column in each row stores a value of SQL type FLOAT, and the method for retrieving values of that type is getFloat. The following code accesses the values stored in the current row of srs and prints a line with the name followed by three spaces and the price. Each time the method next is invoked, the next row becomes the current row, and the loop continues until there are no more rows in rs.
The method getString is invoked on the ResultSet object srs, so getString retrieves (gets) the value stored in the column COF_NAME in the current row of srs . The value that getString retrieves has been converted from an SQL VARCHAR to a String in the Java programming language, and it is assigned to the String object s.
Note that although the method getString is recommended for retrieving the SQL types CHAR and VARCHAR, it is possible to retrieve any of the basic SQL types with it. (You cannot, however, retrieve the new SQL3 datatypes with it. We will discuss SQL3 types later in this tutorial.) Getting all values with getString can be very useful, but it also has its limitations. For instance, if it is used to retrieve a numeric type, getString converts the numeric value to a Java String object, and the value has to be converted back to a numeric type before it can be operated on as a number. In cases where the value is treated as a string anyway, there is no drawback. Further, if you want an application to retrieve values of any standard SQL type other than SQL3 types, use the getString method.
Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_READ_ONLY);
ResultSet srs = stmt.executeQuery(
"SELECT COF_NAME, PRICE FROM COFFEES");
while (srs.next()) {
String name = srs.getString("COF_NAME");
float price = srs.getFloat("PRICE");
System.out.println(name + " " + price);
}
The output will look something like this:
Colombian 7.99
French_Roast 8.99
Espresso 9.99
Colombian_Decaf 8.99
French_Roast_Decaf 9.99
You can process all of the rows is srs going backward, but to do this, the cursor must start out located after the last row. You can move the cursor explicitly to the position after the last row with the method afterLast. Then the method previous() moves the cursor from the position after the last row to the last row, and then to the previous row with each iteration through the while loop. The loop ends when the cursor reaches the position before the first row, where the method previous() returns false .
Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
ResultSet srs = stmt.executeQuery("SELECT COF_NAME, PRICE FROM COFFEES");
srs.afterLast();
while (srs.previous()) {
String name = srs.getString("COF_NAME");
float price = srs.getFloat("PRICE");
System.out.println(name + " " + price);
}
The printout will look similar to this:
French_Roast_Decaf 9.99
Colombian_Decaf 8.99
Espresso 9.99
French_Roast 8.99
Colombian 7.99
As you can see, the printout for each has the same values, but the rows are in the opposite order.
The situation is similar with the method getFloat except that it retrieves the value stored in the column PRICE, which is an SQL FLOAT, and converts it to a Java float before assigning it to the variable n.
JDBC offers two ways to identify the column from which a getXXX method gets a value. One way is to give the column name, as was done in the example above. The second way is to give the column index (number of the column), with 1 signifying the first column, 2 , the second, and so on. Using the column number instead of the column name looks like this:
String s = srs.getString(1);
float n = srs.getFloat(2);
The first line of code gets the value in the first column of the current row of rs (column COF_NAME), converts it to a Java String object, and assigns it to s. The second line of code gets the value stored in the second column of the current row of rs , converts it to a Java float, and assigns it to n. Note that the column number refers to the column number in the result set, not in the original table.
You can move the cursor to a particular row in a ResultSet object. The methods first, last, beforeFirst, and afterLast move the cursor to the row indicated in their names. The method absolute will move the cursor to the row number indicated in the argument passed to it. If the number is positive, the cursor moves the given number from the beginning, so calling absolute(1) puts the cursor on the first row. If the number is negative, the cursor moves the given number from the end, so calling absolute(-1) puts the cursor on the last row. The following line of code moves the cursor to the fourth row of srs:
srs.absolute(4);
If srs has 500 rows, the following line of code moves the cursor to row 497:
srs.absolute(-4);
Three methods move the cursor to a position relative to its current position. As you have seen, the method next moves the cursor forward one row, and the method previous moves the cursor backward one row. With the method relative, you can specify how many rows to move from the current row and also the direction in which to move. A positive number moves the cursor forward the given number of rows; a negative number moves the cursor backward the given number of rows. For example, in the following code fragment, the cursor moves to the fourth row, then to the first row, and finally to the third row:
srs.absolute(4); // cursor is on the fourth row
. . .
srs.relative(-3); // cursor is on the first row
. . .
srs.relative(2); // cursor is on the third row
The method getRow lets you check the number of the row where the cursor is positioned. For example, you can use getRow to verify the current position of the cursor in the previous example as follows:
srs.absolute(4);
int rowNum = srs.getRow(); // rowNum should be 4
srs.relative(-3);
int rowNum = srs.getRow(); // rowNum should be 1
srs.relative(2);
int rowNum = srs.getRow(); // rowNum should be 3
Four additional methods let you verify whether the cursor is at a particular position. The position is stated in their names: isFirst, isLast, isBeforeFirst, isAfterLast. These methods all return a boolean and can therefore be used in a conditional statement. For example, the following code fragment tests to see whether the cursor is after the last row before invoking the method previous in a while loop. If the method isAfterLast returns false, the cursor is not after the last row, so the method afterLast is invoked. This guarantees that the cursor will be after the last row and that using the method previous in the while loop will cover every row in srs.
if (srs.isAfterLast() == false) {
srs.afterLast();
}
while (srs.previous()) {
String name = srs.getString("COF_NAME");
float price = srs.getFloat("PRICE");
System.out.println(name + " " + price);
}
In summary, JDBC allows you to use either the column name or the column number as the argument to a getXXX method. Using the column number is slightly more efficient, and there are some cases where the column number is required. In general, though, supplying the column name is essentially equivalent to supplying the column number.
JDBC allows a lot of latitude as far as which getXXX methods you can use to retrieve the different SQL types. For example, the method getInt can be used to retrieve any of the numeric or character types. The data it retrieves will be converted to an int; that is, if the SQL type is VARCHAR , JDBC will attempt to parse an integer out of the VARCHAR. The method getInt is recommended for retrieving only SQL INTEGER types, however, and it cannot be used for the SQL types BINARY, VARBINARY, LONGVARBINARY, DATE , TIME, or TIMESTAMP
Updating Tables
Next, you learn to update rows in a result set, using methods in the Java programming language rather than having to send an SQL command.
Updating a row in a ResultSet object is a two-phase process. First, the new value for each column being updated is set, and then the change is applied to the row. The row in the underlying data source is not updated until the second phase is completed.
The ResultSet interface contains two update methods for each JDBC™ type, one specifying the column to be updated as an index and one specifying the column name as it appears in the select list. Column names supplied to updater methods are case insensitive. If a select list contains the same column more than once, the first instance of the column will be updated.
First, you need to create a ResultSet object that is updatable. To do this, supply the ResultSet constant CONCUR_UPDATABLE to the createStatement method, as you have seen in previous examples. The Statement object it creates produces an updatable ResultSet object each time it executes a query. The following code fragment illustrates creating the updatable ResultSet object uprs. Note that the code also makes uprs scrollable. An updatable ResultSet object does not necessarily have to be scrollable, but when you are making changes to a result set, you generally want to be able to move around in it. With a scrollable result set, you can move to rows you want to change, and if the type is TYPE_SCROLL_SENSITIVE, you can get the new value in a row after you have changed it.
Connection con = DriverManager.getConnection("jdbc:mySubprotocol:mySubName");
Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
ResultSet uprs = stmt.executeQuery("SELECT COF_NAME, PRICE FROM COFFEES");
The ResultSet object uprs looks something like this:
COF_NAME PRICE
------------------ -----
Colombian 7.99
French_Roast 8.99
Espresso 9.99
Colombian_Decaf 8.99
French_Roast_Decaf 9.99
The method updateRow applies all column changes to the current row. The changes are not made to the row until updateRow has been called. You can use the cancelUpdates method to back out changes made to the row before the updateRow method is called.
Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_UPDATABLE);
ResultSet srs = stmt.executeQuery("select COF_Name from COFFEES " +
"where price = 7.99");
srs.next();
srs.updateString("COF_NAME", "Foldgers");
srs.updateRow();
An update is the modification of a column value in the current row. Let's suppose that you want to raise the price of French Roast Decaf coffee to 10.99:
uprs.last();
uprs.updateFloat("PRICE", 10.99);
Update operations affect column values in the row where the cursor is positioned, so in the first line the ResultSet uprs calls the method last to move its cursor to the last row (the row where the column COF_NAME has the value FRENCH_ROAST_DECAF). Once the cursor is on the last row, all of the update methods you call will operate on that row until you move the cursor to another row. The second line changes the value in the PRICE column to 10.99 by calling the method updateFloat. This method is used because the column value we want to update is a float in the Java programming language.
The ResultSet. updateXXX methods take two parameters: the column to update and the new value to put in that column. As with the ResultSet.getXXX methods, the parameter designating the column may be either the column name or the column number. There is a different updateXXX method for updating each datatype ( updateString, updateBigDecimal, updateInt, and so on) just as there are different getXXX methods for retrieving different datatypes.
At this point, the price in uprs for French Roast Decaf will be 10.99, but the price in the table COFFEES in the database will still be 9.99. To make the update take effect in the database and not just the result set, we must call the ResultSet method updateRow. Here is what the code should look like to update both uprs and COFFEES :
uprs.last();
uprs.updateFloat("PRICE", 10.99f);
uprs.updateRow();
If you had moved the cursor to a different row before calling the method updateRow, the update would have been lost. If, on the other hand, you realized that the price should really have been 10.79 instead of 10.99, you could have cancelled the update to 10.99 by calling the method cancelRowUpdates. You have to invoke cancelRowUpdates before invoking the method updateRow; once updateRow is called, calling the method cancelRowUpdates does nothing. Note that cancelRowUpdates cancels all of the updates in a row, so if there are many invocations of the updateXXX methods on the same row, you cannot cancel just one of them. The following code fragment first cancels updating the price to 10.99 and then updates it to 10.79:
uprs.last();
uprs.updateFloat("PRICE", 10.99);
uprs.cancelRowUpdates();
uprs.updateFloat("PRICE", 10.79);
uprs.updateRow();
In this example, only one column value was updated, but you can call an appropriate updateXXX method for any or all of the column values in a single row. The concept to remember is that updates and related operations apply to the row where the cursor is positioned. Even if there are many calls to updateXXX methods, it takes only one call to the method updateRow to update the database with all of the changes made in the current row.
If you want to update the price for COLOMBIAN_DECAF as well, you have to move the cursor to the row containing that coffee. Because the row for COLOMBIAN_DECAF immediately precedes the row for FRENCH_ROAST_DECAF, you can call the method previous to position the cursor on the row for COLOMBIAN_DECAF. The following code fragment changes the price in that row to 9.79 in both the result set and the underlying table in the database:
uprs.previous();
uprs.updateFloat("PRICE", 9.79);
uprs.updateRow();
All cursor movements refer to rows in a ResultSet object, not rows in the underlying database. If a query selects five rows from a database table, there will be five rows in the result set, with the first row being row 1, the second row being row 2, and so on. Row 1 can also be identified as the first, and, in a result set with five rows, row 5 is the last.
The ordering of the rows in the result set has nothing at all to do with the order of the rows in the base table. In fact, the order of the rows in a database table is indeterminate. The DBMS keeps track of which rows were selected, and it makes updates to the proper rows, but they may be located anywhere in the table. When a row is inserted, for example, there is no way to know where in the table it has been inserted.
Using Prepared Statements
Sometimes it is more convenient to use a PreparedStatement object for sending SQL statements to the database. This special type of statement is derived from the more general class, Statement, that you already know.
If you want to execute a Statement object many times, it normally reduces execution time to use a PreparedStatement object instead.
The main feature of a PreparedStatement object is that, unlike a Statement object, it is given an SQL statement when it is created. The advantage to this is that in most cases, this SQL statement is sent to the DBMS right away, where it is compiled. As a result, the PreparedStatement object contains not just an SQL statement, but an SQL statement that has been precompiled. This means that when the PreparedStatement is executed, the DBMS can just run the PreparedStatement SQL statement without having to compile it first.
Although PreparedStatement objects can be used for SQL statements with no parameters, you probably use them most often for SQL statements that take parameters. The advantage of using SQL statements that take parameters is that you can use the same statement and supply it with different values each time you execute it. Examples of this are in the following sections.
Creating a PreparedStatement Object
As with Statement objects, you create PreparedStatement objects with a Connection method. Using our open connection con from previous examples, you might write code such as the following to create a PreparedStatement object that takes two input parameters:
PreparedStatement updateSales = con.prepareStatement(
"UPDATE COFFEES SET SALES = ? WHERE COF_NAME LIKE ?");
The variable updateSales now contains the SQL statement, "UPDATE COFFEES SET SALES = ? WHERE COF_NAME LIKE ?", which has also, in most cases, been sent to the DBMS and been precompiled.
Supplying Values for PreparedStatement Parameters
You need to supply values to be used in place of the question mark placeholders (if there are any) before you can execute a PreparedStatement object. You do this by calling one of the setXXX methods defined in the PreparedStatement class. If the value you want to substitute for a question mark is a Java int, you call the method setInt. If the value you want to substitute for a question mark is a Java String, you call the method setString, and so on. In general, there is a setXXX method for each primitive type declared in the Java programming language.
setXXX Using the PreparedStatement object updateSales from the previous example, the following line of code sets the first question mark placeholder to a Java int with a value of 75:
updateSales.setInt(1, 75);
setXXX The first argument given to a setXXX method indicates which question mark placeholder is to be set, and the second argument indicates the value to which it is to be set. The next example sets the second placeholder parameter to the string " Colombian":
updateSales.setString(2, "Colombian");
setXXX After these values have been set for its two input parameters, the SQL statement in updateSales is the equivalent to the SQL statement in the String object updateString that was used in the previous update example. Therefore, the following two code fragments accomplish the same thing:
Code Fragment 1:
String updateString = "UPDATE COFFEES SET SALES = 75 " +
"WHERE COF_NAME LIKE 'Colombian'";
stmt.executeUpdate(updateString);
Code Fragment 2:
PreparedStatement updateSales = con.prepareStatement(
"UPDATE COFFEES SET SALES = ? WHERE COF_NAME LIKE ? ");
updateSales.setInt(1, 75);
updateSales.setString(2, "Colombian");
updateSales.executeUpdate():
The method executeUpdate was used to execute both the Statement stmt and the PreparedStatement updateSales. Notice, however, that no argument is supplied to executeUpdate when it is used to execute updateSales. This is true because updateSales already contains the SQL statement to be executed.
Looking at these examples, you might wonder why you would choose to use a PreparedStatement object with parameters instead of just a simple statement, since the simple statement involves fewer steps. If you were going to update the SALES column only once or twice, then there would be no need to use an SQL statement with input parameters. If you will be updating often, on the other hand, it might be much easier to use a PreparedStatement object, especially in situations where you can use a for loop or while loop to set a parameter to a succession of values. You will see an example of this later in this section.
Once a parameter has been set with a value, it retains that value until it is reset to another value, or the method clearParameters is called. Using the PreparedStatement object updateSales, the following code fragment illustrates reusing a prepared statement after resetting the value of one of its parameters and leaving the other one the same:
updateSales.setInt(1, 100);
updateSales.setString(2, "French_Roast");
updateSales.executeUpdate();
// changes SALES column of French Roast row to 100
updateSales.setString(2, "Espresso");
updateSales.executeUpdate();
// changes SALES column of Espresso row to 100 (the first
// parameter stayed 100, and the second parameter was reset
// to "Espresso")
Using a Loop to Set Values
You can often make coding easier by using a for loop or a while loop to set values for input parameters.
The code fragment that follows demonstrates using a for loop to set values for parameters in the PreparedStatement object updateSales. The array salesForWeek holds the weekly sales amounts. These sales amounts correspond to the coffee names listed in the array coffees, so that the first amount in salesForWeek (175) applies to the first coffee name in coffees ("Colombian"), the second amount in salesForWeek (150) applies to the second coffee name in coffees ("French_Roast "), and so on. This code fragment demonstrates updating the SALES column for all the coffees in the table COFFEES:
PreparedStatement updateSales;
String updateString = "update COFFEES " +
"set SALES = ? where COF_NAME like ?";
updateSales = con.prepareStatement(updateString);
int [] salesForWeek = {175, 150, 60, 155, 90};
String [] coffees = {"Colombian", "French_Roast", "Espresso",
"Colombian_Decaf", "French_Roast_Decaf"};
int len = coffees.length;
for(int i = 0; i < len; i++) {
updateSales.setInt(1, salesForWeek[i]);
updateSales.setString(2, coffees[i]);
updateSales.executeUpdate();
}
When the proprietor wants to update the sales amounts for the next week, he can use this same code as a template. All he has to do is enter the new sales amounts in the proper order in the array salesForWeek. The coffee names in the array coffees remain constant, so they do not need to be changed. (In a real application, the values would probably be input from the user rather than from an initialized Java array.)
Return Values for the executeUpdate Method
Whereas executeQuery returns a ResultSet object containing the results of the query sent to the DBMS, the return value for executeUpdate is an int that indicates how many rows of a table were updated. For instance, the following code shows the return value of executeUpdate being assigned to the variable n:
updateSales.setInt(1, 50);
updateSales.setString(2, "Espresso");
int n = updateSales.executeUpdate();
// n = 1 because one row had a change in it
The table COFFEES was updated by having the value 50 replace the value in the column SALES in the row for Espresso. That update affected one row in the table, so n is equal to 1.
When the method executeUpdate is used to execute a DDL statement, such as in creating a table, it returns the int 0. Consequently, in the following code fragment, which executes the DDL statement used to create the table COFFEES, n is assigned a value of 0:
int n = executeUpdate(createTableCoffees); // n = 0
Note that when the return value for executeUpdate is 0, it can mean one of two things:
* the statement executed was an update statement that affected zero rows
*
* the statement executed was a DDL statement.
Using Joins
Sometimes you need to use two or more tables to get the data you want. For example, suppose the proprietor of The Coffee Break wants a list of the coffees he buys from Acme, Inc. This involves information in the COFFEES table as well as the yet-to-be-created SUPPLIERS table. This is a case where a join is needed. A join is a database operation that relates two or more tables by means of values that they share in common. In our example database, the tables COFFEES and SUPPLIERS both have the column SUP_ID, which can be used to join them.
Before we go any further, we need to create the table SUPPLIERS and populate it with values.
The code below creates the table SUPPLIERS:
String createSUPPLIERS = "create table SUPPLIERS " +
"(SUP_ID INTEGER, SUP_NAME VARCHAR(40), " +
"STREET VARCHAR(40), CITY VARCHAR(20), " +
"STATE CHAR(2), ZIP CHAR(5))";
stmt.executeUpdate(createSUPPLIERS);
The following code inserts rows for three suppliers into SUPPLIERS:
stmt.executeUpdate("insert into SUPPLIERS values (101, " +
"'Acme, Inc.', '99 Market Street', 'Groundsville', " + "'CA', '95199'");
stmt.executeUpdate("Insert into SUPPLIERS values (49," +
"'Superior Coffee', '1 Party Place', 'Mendocino', 'CA', " + "'95460'");
stmt.executeUpdate("Insert into SUPPLIERS values (150, " +
"'The High Ground', '100 Coffee Lane', 'Meadows', 'CA', " + "'93966'");
The following code selects the whole table and lets us see what the table SUPPLIERS looks like:
ResultSet rs = stmt.executeQuery("select * from SUPPLIERS");
Now that we have the tables COFFEES and SUPPLIERS, we can proceed with the scenario where the owner wants to get a list of the coffees he buys from a particular supplier. The names of the suppliers are in the table SUPPLIERS, and the names of the coffees are in the table COFFEES. Since both tables have the column SUP_ID, this column can be used in a join. It follows that you need some way to distinguish which SUP_ID column you are referring to. This is done by preceding the column name with the table name, as in "COFFEES.SUP_ID" to indicate that you mean the column SUP_ID in the table COFFEES. The following code, in which stmt is a Statement object, selects the coffees bought from Acme, Inc.:
String query = "
SELECT COFFEES.COF_NAME " +
"FROM COFFEES, SUPPLIERS " +
"WHERE SUPPLIERS.SUP_NAME LIKE 'Acme, Inc.' " +
"and SUPPLIERS.SUP_ID = COFFEES.SUP_ID";
ResultSet rs = stmt.executeQuery(query);
System.out.println("Coffees bought from Acme, Inc.: ");
while (rs.next()) {
String coffeeName = rs.getString("COF_NAME");
System.out.println(" " + coffeeName);
}
This will produce the following output:
Coffees bought from Acme, Inc.:
Colombian
Colombian_Decaf
Using Transactions
There are times when you do not want one statement to take effect unless another one completes. For example, when the proprietor of The Coffee Break updates the amount of coffee sold each week, he will also want to update the total amount sold to date. However, he will not want to update one without updating the other; otherwise, the data will be inconsistent. The way to be sure that either both actions occur or neither action occurs is to use a transaction. A transaction is a set of one or more statements that are executed together as a unit, so either all of the statements are executed, or none of the statements is executed.
Disabling Auto-commit Mode
When a connection is created, it is in auto-commit mode. This means that each individual SQL statement is treated as a transaction and is automatically committed right after it is executed. (To be more precise, the default is for an SQL statement to be committed when it is completed, not when it is executed. A statement is completed when all of its result sets and update counts have been retrieved. In almost all cases, however, a statement is completed, and therefore committed, right after it is executed.)
The way to allow two or more statements to be grouped into a transaction is to disable auto-commit mode. This is demonstrated in the following line of code, where con is an active connection:
con.setAutoCommit(false);
Committing a Transaction
Once auto-commit mode is disabled, no SQL statements are committed until you call the method commit explicitly. All statements executed after the previous call to the method commit are included in the current transaction and committed together as a unit. The following code, in which con is an active connection, illustrates a transaction:
con.setAutoCommit(false);
PreparedStatement updateSales = con.prepareStatement(
"UPDATE COFFEES SET SALES = ? WHERE COF_NAME LIKE ?");
updateSales.setInt(1, 50);
updateSales.setString(2, "Colombian");
updateSales.executeUpdate();
PreparedStatement updateTotal = con.prepareStatement(
"UPDATE COFFEES SET TOTAL = TOTAL + ? WHERE COF_NAME LIKE ?");
updateTotal.setInt(1, 50);
updateTotal.setString(2, "Colombian");
updateTotal.executeUpdate();
con.commit();
con.setAutoCommit(true);
In this example, auto-commit mode is disabled for the connection con, which means that the two prepared statements updateSales and updateTotal are committed together when the method commit is called. Whenever the commit method is called (either automatically when auto-commit mode is enabled or explicitly when it is disabled), all changes resulting from statements in the transaction are made permanent. In this case, that means that the SALES and TOTAL columns for Colombian coffee have been changed to 50 (if TOTAL had been 0 previously) and will retain this value until they are changed with another update statement. TransactionPairs.java illustrates a similar kind of transaction but uses a for loop to supply values to the setXXX methods for updateSales and updateTotal.
The final line of the previous example enables auto-commit mode, which means that each statement is once again committed automatically when it is completed. Then, you are back to the default state where you do not have to call the method commit yourself. It is advisable to disable auto-commit mode only while you want to be in transaction mode. This way, you avoid holding database locks for multiple statements, which increases the likelihood of conflicts with other users.
Using Transactions to Preserve Data Integrity
In addition to grouping statements together for execution as a unit, transactions can help to preserve the integrity of the data in a table. For instance, suppose that an employee was supposed to enter new coffee prices in the table COFFEES but delayed doing it for a few days. In the meantime, prices rose, and today the owner is in the process of entering the higher prices. The employee finally gets around to entering the now outdated prices at the same time that the owner is trying to update the table. After inserting the outdated prices, the employee realizes that they are no longer valid and calls the Connection method rollback to undo their effects. (The method rollback aborts a transaction and restores values to what they were before the attempted update.) At the same time, the owner is executing a SELECT statement and printing out the new prices. In this situation, it is possible that the owner will print a price that was later rolled back to its previous value, making the printed price incorrect.
This kind of situation can be avoided by using transactions, providing some level of protection against conflicts that arise when two users access data at the same time.
To avoid conflicts during a transaction, a DBMS uses locks, mechanisms for blocking access by others to the data that is being accessed by the transaction. (Note that in auto-commit mode, where each statement is a transaction, locks are held for only one statement.) Once a lock is set, it remains in force until the transaction is committed or rolled back. For example, a DBMS could lock a row of a table until updates to it have been committed. The effect of this lock would be to prevent a user from getting a dirty read, that is, reading a value before it is made permanent. (Accessing an updated value that has not been committed is considered a dirty read because it is possible for that value to be rolled back to its previous value. If you read a value that is later rolled back, you will have read an invalid value.)
How locks are set is determined by what is called a transaction isolation level, which can range from not supporting transactions at all to supporting transactions that enforce very strict access rules.
One example of a transaction isolation level is TRANSACTION_READ_COMMITTED, which will not allow a value to be accessed until after it has been committed. In other words, if the transaction isolation level is set to TRANSACTION_READ_COMMITTED, the DBMS does not allow dirty reads to occur. The interface Connection includes five values which represent the transaction isolation levels you can use in JDBC.
Normally, you do not need to do anything about the transaction isolation level; you can just use the default one for your DBMS. JDBC allows you to find out what transaction isolation level your DBMS is set to (using the Connection method getTransactionIsolation) and also allows you to set it to another level (using the Connection method setTransactionIsolation). Keep in mind, however, that even though JDBC allows you to set a transaction isolation level, doing so has no effect unless the driver and DBMS you are using support it.
Setting and Rolling Back to a Savepoint
The JDBC 3.0 API adds the method Connection.setSavepoint, which sets a savepoint within the current transaction. The Connection.rollback method has been overloaded to take a savepoint argument.
The example below inserts a row into a table, sets the savepoint svpt1, and then inserts a second row. When the transaction is later rolled back to svpt1, the second insertion is undone, but the first insertion remains intact. In other words, when the transaction is committed, only the row containing ?FIRST? will be added to TAB1:
Statement stmt = conn.createStatement();
int rows = stmt.executeUpdate("INSERT INTO TAB1 (COL1) VALUES " +
"(?FIRST?)");
// set savepoint
Savepoint svpt1 = conn.setSavepoint("SAVEPOINT_1");
rows = stmt.executeUpdate("INSERT INTO TAB1 (COL1) " +
"VALUES (?SECOND?)");
...
conn.rollback(svpt1);
...
conn.commit();
Releasing a Savepoint
The method Connection.releaseSavepoint takes a Savepoint object as a parameter and removes it from the current transaction.
Once a savepoint has been released, attempting to reference it in a rollback operation causes an SQLException to be thrown. Any savepoints that have been created in a transaction are automatically released and become invalid when the transaction is committed, or when the entire transaction is rolled back. Rolling a transaction back to a savepoint automatically releases and makes invalid any other savepoints that were created after the savepoint in question.
When to Call the Method rollback
As mentioned earlier, calling the method rollback aborts a transaction and returns any values that were modified to their previous values. If you are trying to execute one or more statements in a transaction and get an SQLException, you should call the method rollback to abort the transaction and start the transaction all over again. That is the only way to be sure of what has been committed and what has not been committed. Catching an SQLException tells you that something is wrong, but it does not tell you what was or was not committed. Since you cannot count on the fact that nothing was committed, calling the method rollback is the only way to be sure.
TransactionPairs.java demonstrates a transaction and includes a catch block that invokes the method rollback. In this particular situation, it is not really necessary to call rollback, and we do it mainly to illustrate how it is done. If the application continued and used the results of the transaction, however, it would be necessary to include a call to rollback in the catch block in order to protect against using possibly incorrect data.
Stored Procedures
A stored procedure is a group of SQL statements that form a logical unit and perform a particular task. Stored procedures are used to encapsulate a set of operations or queries to execute on a database server. For example, operations on an employee database (hire, fire, promote, lookup) could be coded as stored procedures executed by application code. Stored procedures can be compiled and executed with different parameters and results, and they may have any combination of input, output, and input/output parameters.
Stored procedures are supported by most DBMSs, but there is a fair amount of variation in their syntax and capabilities. For this reason, we will show you a simple example of what a stored procedure looks like and how it is invoked from JDBC, but this sample is not intended to be run.
SQL Statements for Creating a Stored Procedure
A stored procedure is a group of SQL statements that form a logical unit and perform a particular task, and they are used to encapsulate a set of operations or queries to execute on a database server. For example, operations on an employee database (hire, fire, promote, lookup) could be coded as stored procedures executed by application code. Stored procedures can be compiled and executed with different parameters and results, and they may have any combination of input, output, and input/output parameters.
Note: Stored procedures are supported by most DBMSs, but there is a fair amount of variation in their syntax and capabilities. For this reason, this simple example of what a stored procedure looks like and how it is invoked from JDBC is not intended to be run.
This simple stored procedure has no parameters. Even though most stored procedures do something more complex than this example, it serves to illustrate some basic points about them. As previously stated, the syntax for defining a stored procedure is different for each DBMS. For example, some use begin . . . end , or other keywords to indicate the beginning and ending of the procedure definition. In some DBMSs, the following SQL statement creates a stored procedure:
create procedure SHOW_SUPPLIERS
as
select SUPPLIERS.SUP_NAME, COFFEES.COF_NAME
from SUPPLIERS, COFFEES
where SUPPLIERS.SUP_ID = COFFEES.SUP_ID
order by SUP_NAME
The following code puts the SQL statement into a string and assigns it to the variable createProcedure, which we will use later:
String createProcedure = "create procedure SHOW_SUPPLIERS " +
"as " +
"select SUPPLIERS.SUP_NAME, COFFEES.COF_NAME " +
"from SUPPLIERS, COFFEES " +
"where SUPPLIERS.SUP_ID = COFFEES.SUP_ID " +
"order by SUP_NAME";
The following code fragment uses the Connection object con to create a Statement object, which is used to send the SQL statement creating the stored procedure to the database:
Statement stmt = con.createStatement();
stmt.executeUpdate(createProcedure);
The procedure SHOW_SUPPLIERS is compiled and stored in the database as a database object that can be called, similar to the way you would call a method.
Calling a Stored Procedure from JDBC
JDBC allows you to call a database stored procedure from an application written in the Java programming language. The first step is to create a CallableStatement object. As with Statement and PreparedStatement objects, this is done with an open Connection object. A callableStatement object contains a call to a stored procedure; it does not contain the stored procedure itself. The first line of code below creates a call to the stored procedure SHOW_SUPPLIERS using the connection con. The part that is enclosed in curly braces is the escape syntax for stored procedures. When the driver encounters "{call SHOW_SUPPLIERS}", it will translate this escape syntax into the native SQL used by the database to call the stored procedure named SHOW_SUPPLIERS.
CallableStatement cs = con.prepareCall("{call SHOW_SUPPLIERS}");
ResultSet rs = cs.executeQuery();
The ResultSet rs will be similar to the following:
SUP_NAME COF_NAME
---------------- -----------------------
Acme, Inc. Colombian
Acme, Inc. Colombian_Decaf
Superior Coffee French_Roast
Superior Coffee French_Roast_Decaf
The High Ground Espresso
Note that the method used to execute cs is executeQuery because cs calls a stored procedure that contains one query and thus produces one result set. If the procedure had contained one update or one DDL statement, the method executeUpdate would have been the one to use. It is sometimes the case, however, that a stored procedure contains more than one SQL statement, in which case it will produce more than one result set, more than one update count, or some combination of result sets and update counts. In this case, where there are multiple results, the method execute should be used to execute the CallableStatement .
The class CallableStatement is a subclass of PreparedStatement, so a CallableStatement object can take input parameters just as a PreparedStatement object can. In addition, a CallableStatement object can take output parameters, or parameters that are for both input and output. INOUT parameters and the method execute are used rarely.
Creating Complete JDBC Applications
Up to this point, you have seen only code fragments. Now you will see some samples.
The first sample code creates the table COFFEES; the second one inserts values into the table and prints the results of a query. The third application creates the table SUPPLIERS, and the fourth populates it with values. After you have run this code, you can try a query that is a join between the tables COFFEES and SUPPLIERS, as in the fifth code example. The sixth code sample is an application that demonstrates a transaction and also shows how to set placeholder parameters in a PreparedStatement object using a for loop.
Because they are complete applications, they include some elements of the Java programming language we have not shown before in the code fragments. We will explain these elements briefly here.
Putting Code in a Class Definition
In the Java™ programming language, any code you want to execute must be inside a class definition. You type the class definition in a file and give the file the name of the class with .java appended to it. So if you have a class named MySQLStatement, its definition should be in a file named MySQLStatement.java.
Importing Classes to Make Them Visible
The first thing to do is to import the packages or classes you will be using in the new class. The classes in our examples all use the java.sql package (the JDBC™ API), which becomes available when the following line of code precedes the class definition:
import java.sql.*;
The star ( * ) indicates that all of the classes in the package java.sql are to be imported. Importing a class makes it visible and means that you do not have to write out the fully qualified name when you use a method or field from that class. If you do not include " import java.sql.*; " in your code, you will have to write " java.sql. " plus the class name in front of all the JDBC fields or methods you use every time you use them. Note that you can import individual classes selectively rather than a whole package. Java does not require that you import classes or packages, but doing so makes writing code a lot more convenient.
Any lines importing classes appear at the top of all the code samples, as they must if they are going to make the imported classes visible to the class being defined. The actual class definition follows any lines that import classes.
Using the main Method
If a class is to be executed, it must contain a static public main method. This method comes right after the line declaring the class and invokes the other methods in the class. The keyword static indicates that this method operates on a class level rather than on individual instances of a class. The keyword public means that members of any class can access this method. Since we are not just defining classes to be used by other classes but instead want to run them, the example applications in this chapter all include a main method.
Using try and catch Blocks
Something else all the sample applications include is try and catch blocks. These are the Java programming language's mechanism for handling exceptions. Java requires that when a method throws an exception, there be some mechanism to handle it. Generally a catch block will catch the exception and specify what happens (which you may choose to be nothing). In the sample code, we use two try blocks and two catch blocks. The first try block contains the method Class.forName, from the java.lang package. This method throws a ClassNotFoundException, so the catch block immediately following it deals with that exception. The second try block contains JDBC methods, which all throw SQLExceptions, so one catch block at the end of the application can handle all of the rest of the exceptions that might be thrown because they will all be SQLException objects.
Retrieving Exceptions
JDBC lets you see the warnings and exceptions generated by your DBMS and by the Java compiler. To see exceptions, you can have a catch block print them out. For example, the following two catch blocks from the sample code print out a message explaining the exception:
try {
// Code that could generate an exception goes here.
// If an exception is generated, the catch block below
// will print out information about it.
} catch(SQLException ex) {
System.err.println("SQLException: " + ex.getMessage());
}
try {
Class.forName("myDriverClassName");
} catch(java.lang.ClassNotFoundException e) {
System.err.print("ClassNotFoundException: ");
System.err.println(e.getMessage());
}
If you were to run CreateCOFFEES.java twice, you would get an error message similar to this:
SQLException: There is already an object named 'COFFEES'
in the database.
Severity 16, State 1, Line 1
This example illustrates printing out the message component of an SQLException object, which is sufficient for most situations.
There are actually three components, however, and to be complete, you can print them all out. The following code fragment shows a catch block that is complete in two ways. First, it prints out all three parts of an SQLException object: the message (a string that describes the error), the SQL state (a string identifying the error according to the X/Open SQLState conventions), and the vendor error code (a number that is the driver vendor's error code number). The SQLException object ex is caught, and its three components are accessed with the methods getMessage , getSQLState , and getErrorCode .
The second way the following catch block is complete is that it gets all of the exceptions that might have been thrown. If there is a second exception, it will be chained to ex, so ex.getNextException is called to see if there is another exception. If there is, the while loop continues and prints out the next exception's message, SQLState, and vendor error code. This continues until there are no more exceptions.
try {
// Code that could generate an exception goes here.
// If an exception is generated, the catch block below
// will print out information about it.
} catch(SQLException ex) {
System.out.println("\n--- SQLException caught ---\n");
while (ex != null) {
System.out.println("Message: "
+ ex.getMessage ());
System.out.println("SQLState: "
+ ex.getSQLState ());
System.out.println("ErrorCode: "
+ ex.getErrorCode ());
ex = ex.getNextException();
System.out.println("");
}
}
If you were to substitute the catch block above into the code and run it after the table COFFEES had already been created, you would get the following printout:
--- SQLException caught ---
Message: There is already an object named 'COFFEES' in the database.
Severity 16, State 1, Line 1
SQLState: 42501
ErrorCode: 2714
The vendor error code is specific to each driver, so you need to check your driver documentation for a list of error codes and what they mean.
Retrieving Warnings
SQLWarning objects are a subclass of SQLException that deal with database access warnings. Warnings do not stop the execution of an application, as exceptions do; they simply alert the user that something did not happen as planned. For example, a warning might let you know that a privilege you attempted to revoke was not revoked. Or a warning might tell you that an error occurred during a requested disconnection.
A warning can be reported on a Connection object, a Statement object (including PreparedStatement and CallableStatement objects), or a ResultSet object. Each of these classes has a getWarnings method, which you must invoke in order to see the first warning reported on the calling object. If getWarnings returns a warning, you can call the SQLWarning method getNextWarning on it to get any additional warnings. Executing a statement automatically clears the warnings from a previous statement, so they do not build up. This means, however, that if you want to retrieve warnings reported on a statement, you must do so before you execute another statement.
The following code fragment illustrates how to get complete information about any warnings reported on the Statement object stmt and also on the ResultSet object rs :
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("select COF_NAME from COFFEES");
while (rs.next()) {
String coffeeName = rs.getString("COF_NAME");
System.out.println("Coffees available at the Coffee Break: ");
System.out.println(" " + coffeeName);
SQLWarning warning = stmt.getWarnings();
if (warning != null) {
System.out.println("\n---Warning---\n");
while (warning != null) {
System.out.println("Message: "
+ warning.getMessage());
System.out.println("SQLState: "
+ warning.getSQLState());
System.out.print("Vendor error code: ");
System.out.println(warning.getErrorCode());
System.out.println("");
warning = warning.getNextWarning();
}
}
SQLWarning warn = rs.getWarnings();
if (warn != null) {
System.out.println("\n---Warning---\n");
while (warn != null) {
System.out.println("Message: "
+ warn.getMessage());
System.out.println("SQLState: "
+ warn.getSQLState());
System.out.print("Vendor error code: ");
System.out.println(warn.getErrorCode());
System.out.println("");
warn = warn.getNextWarning();
}
}
}
Warnings are actually rather uncommon. Of those that are reported, by far the most common warning is a DataTruncation warning, a subclass of SQLWarning. All DataTruncation objects have an SQLState of 01004, indicating that there was a problem with reading or writing data. DataTruncation methods let you find out in which column or parameter data was truncated, whether the truncation was on a read or write operation, how many bytes should have been transferred, and how many bytes were actually transferred.
Running the Sample Applications
You are now ready to actually try out some sample code. The directory book.html contains complete, runnable applications that illustrate concepts presented in this chapter and the next. You can download this sample code from the JDBC web site located at:
JDBC Technology Code Samples
Before you can run one of these applications, you will need to edit the file by substituting the appropriate information for the following variables:
url
the JDBC URL; parts one and two are supplied by your driver, and the third part specifies your data source
myLogin
your login name or user name
myPassword
your password for the DBMS
myDriver.ClassName
the class name supplied with your driver
The first example application is the class CreateCoffees , which is in a file named CreateCoffees.java . Below are instructions for running CreateCoffees.java on the three major platforms.
The first line in the instructions below compiles the code in the file CreateCoffees.java . If the compilation is successful, it will produce a file named CreateCoffees.class , which contains the bytecodes translated from the file CreateCoffees.java . These bytecodes will be interpreted by the Java Virtual Machine, which is what makes it possible for Java code to run on any machine with a Java Virtual Machine installed on it.
The second line of code is what actually makes the code run. Note that you use the name of the class, CreateCoffees , not the name of the file, CreateCoffees.class .
UNIX
javac CreateCoffees.java
java CreateCoffees
Windows 95/NT
javac CreateCoffees.java
java CreateCoffees
Creating an Applet from an Application
Suppose that the owner of The Coffee Break wants to display his current coffee prices in an applet on his web page. He can be sure of always displaying the most current price by having the applet get the price directly from his database.
In order to do this, he needs to create two files of code, one with applet code, and one with HTML code. The applet code contains the JDBC code that would appear in a regular application plus additional code for running the applet and displaying the results of the database query. In our example, the applet code is in the file OutputApplet.java. To display our applet in an HTML page, the file OutputApplet.html tells the browser what to display and where to display it.
The rest of this section will tell you about various elements found in applet code that are not present in standalone application code. Some of these elements involve advanced aspects of the Java programming language. We will give you some rationale and some basic explanation, but explaining them fully is beyond the scope of this tutorial. For purposes of this sample applet, you only need to grasp the general idea, so don't worry if you don't understand everything. You can use the applet code as a template, substituting your own queries for the one in the applet.
Writing Applet Code
To begin with, applets will import classes not used by standalone applications. Our applet imports two classes that are special to applets: the class Applet , which is part of the java.applet package, and the class Graphics , which is part of the java.awt package. This applet also imports the general-purpose class java.util.Vector so that we have access to an array-like container whose size can be modified. This code uses Vector objects to store query results so that they can be displayed later.
All applets extend the Applet class; that is, they are subclasses of Applet . Therefore, every applet definition must contain the words extends Applet , as shown here:
public class MyAppletName extends Applet {
. . .
}
In our applet example, OutputApplet, this line also includes the words implements Runnable , so it looks like this:
public class OutputApplet extends Applet implements Runnable {
. . .
}
Runnable is an interface that makes it possible to run more than one thread at a time. A thread is a sequential flow of control, and it is possible for a program to be multithreaded, that is, to have many threads doing different things concurrently. The class OutputApplet implements the interface Runnable by defining the method run , the only method in Runnable . In our example the run method contains the JDBC code for opening a connection, executing a query, and getting the results from the result set. Since database connections can be slow, and can sometimes take several seconds, it is generally a good idea to structure an applet so that it can handle the database work in a separate thread.
Similar to a standalone application, which must have a main method, an applet must implement at least one init , start , or paint method. Our example applet defines a start method and a paint method. Every time start is invoked, it creates a new thread (named worker ) to re-evaluate the database query. Every time paint is invoked, it displays either the query results or a string describing the current status of the applet.
As stated previously, the run method defined in OutputApplet contains the JDBC code. When the thread worker invokes the method start , the run method is called automatically, and it executes the JDBC code in the thread worker . The code in run is very similar to the code you have seen in our other sample code with three exceptions. First, it uses the class Vector to store the results of the query. Second, it does not print out the results but rather adds them to the Vector results for display later. Third, it likewise does not print out exceptions and instead records error messages for later display.
Applets have various ways of drawing, or displaying, their content. This applet, a very simple one that has only text, uses the method drawString (part of the Graphics class) to display its text. The method drawString takes three arguments: (1) the string to be displayed, (2) the x coordinate, indicating the horizontal starting point for displaying the string, and (3) the y coordinate, indicating the vertical starting point for displaying the string (which is below the text).
The method paint is what actually displays something on the screen, and in OutputApplet.java , it is defined to contain calls to the method drawString . The main thing drawString displays is the contents of the Vector results (the stored query results). When there are no query results to display, drawString will display the current contents of the String message . This string will be "Initializing" to begin with. It gets set to "Connecting to database" when the method start is called, and the method setError sets it to an error message when an exception is caught. Thus, if the database connection takes much time, the person viewing this applet will see the message "Connecting to database" because that will be the contents of message at that time. (The method paint is called by AWT when it wants the applet to display its current state on the screen.)
The last two methods defined in the class OutputApplet, setError and setResults are private, which means that they can be used only by OutputApplet. These methods both invoke the method repaint , which clears the screen and calls paint . So if setResults calls repaint , the query results will be displayed, and if setError calls repaint , an error message will be displayed.
A final point to be made is that all the methods defined in OutputApplet except run are synchronized . The keyword synchronized indicates that while a method is accessing an object, other synchronized methods are blocked from accessing that object. The method run is not declared synchronized so that the applet can still paint itself on the screen while the database connection is in progress. If the database access methods were synchronized , they would prevent the applet from being repainted while they are executing, and that could result in delays with no accompanying status message.
To summarize, in an applet, it is good programming practice to do some things you would not need to do in a standalone application:
1. Put your JDBC code in a separate thread
2. Display status messages on the screen during any delays, such as when a database connection is taking a long time
3. Display error messages on the screen instead of printing them to System.out or System.err .
Running an Applet
Before running our sample applet, you need to compile the file OutputApplet.java . This creates the file OutputApplet.class , which is referenced by the file OutputApplet.html.
The easiest way to run an applet is to use the appletviewer, which is included as part of the JDK. Simply follow the instructions below for your platform to compile and run OutputApplet.java :
UNIX
javac OutputApplet.java
appletviewer OutputApplet.html
Windows 95/NT
javac OutputApplet.java
appletviewer OutputApplet.html
Applets loaded over the network are subject to various security restrictions. Although this can seem bothersome at times, it is absolutely necessary for network security, and security is one of the major advantages of using the Java programming language. An applet cannot make network connections except to the host it came from unless the browser allows it. Whether one is able to treat locally installed applets as "trusted" also depends on the security restrictions imposed by the browser. An applet cannot ordinarily read or write files on the host that is executing it, and it cannot load libraries or define native methods.
Applets can usually make network connections to the host they came from, so they can work very well on intranets.
The JDBC-ODBC Bridge driver is a somewhat special case. It can be used quite successfully for intranet access, but it requires that ODBC, the bridge, the bridge native library, and JDBC be installed on every client. With this configuration, intranet access works from Java applications and from trusted applets. However, since the bridge requires special client configuration, it is not practical to run applets on the Internet with the JDBC-ODBC Bridge driver. Note that this is a limitation of the JDBC-ODBC Bridge, not of JDBC. With a pure Java JDBC driver, you do not need any special configuration to run applets on the Internet.
from java lecture
Connecting to a Database
We have a network which supports a communication protocol e.g. TCP/IP
The Database listens to a port (a 2 way thing)
The Database will define some higher level protocol for queries (SQL) and returned results (tables)
we DON’T WANT to have to code at this level! Hence JDBC (it is all done for us again!)
ODBC Came First
Slow
JDBC is A Java API for database communication
Connect to relational databases
Send SQL commands
Process results
About the Database
host = “unnc-cslinux”;
database = “your username”;
port = 5432;
user = “your username”;
password = “I will email you";
Register the Driver
import java.sql.*; // You NEED this!
…
Connection con;
try {
Class.forName(“org.postgresql.Driver");
} catch (ClassNotFoundException ex) {
System.out.println(ex.getErrorCode());
}
Connect to Database
Connection con;
con = DriverManager.getConnection(url, username, passwd);
Where:
String url = “jdbc:postgresql://unnc-cslinux:5432/yourusername”;
Create and Execute a Statement
Statement st = con.createStatement();
String qry = “SELECT ...”;
ResultSet rs = st.executeQuery(qry);
String qry = “INSERT ...”;
st.executeUpdate(qry);
JDBC Statements
Getting the data returned
String code = rs.getString(“code");
Needs to be in a try block
Of course other methods are:
getInt();
getDouble();
Navigating the ResultSet
while(rs.next()) {
… process a row
}
Important point…
con.close();
How to connect in Java
DBconnect() {
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
pConnection=DriverManager.getConnection("jdbc:odbc:mydb.mdb","","");
pStatement = pConnection.createStatement();
}
catch(Exception e) { e.getMessage(); // print this out
}
}
from DBS lecture:
Basic steps when using JDBC
Register a database driver
Open a connection
Pass some queries to the database
Process the results as needed
Close the connection
Deal with any errors
// Declaration of any database objects
try {
// Some database code
} catch (Exception e) {
// Error reporting etc.
} finally {
// Make sure all database objects are
// closed and cleaned up
}
Subscribe to:
Posts (Atom)