ubuntu SSH 连接
安装 SSH(Secure Shell) 服务以提供远程管理服务
sudo apt-get install ssh
SSH 远程登入 Ubuntu 机
ssh username@192.168.0.1
将 文件/文件夹 从远程 Ubuntu 机拷至本地(scp)
scp -r username@192.168.0.1:/home/username/remotefile.txt .
将 文件/文件夹 从本地拷至远程 Ubuntu 机(scp)
scp -r localfile.txt username@192.168.0.1:/home/username/
将 文件/文件夹 从远程 Ubuntu 机拷至本地(rsync)
rsync -v -u -a --delete --rsh=ssh --stats username@192.168.0.1:/home/username/remotefile.txt .
将 文件/文件夹 从本地拷至远程 Ubuntu 机(rsync)
rsync -v -u -a --delete --rsh=ssh --stats localfile.txt username@192.168.0.1:/home/username/
在 Windows 机上用 SSH 远程登录 Ubuntu 机
下载 PuTTY
如何在 Windows 机上拷贝 文件/文件夹 从/到 远程 Ubuntu 机
下载 FileZilla
如何限制通过SSH远程连接的用户帐号
如，假如你启用了SSH服务，那么任何有有效帐号的用户都可以远程连接。这可能会导致一些安全问题，由于有一些远程密码破解工具可以尝试常见的用户名／密码
备份SSH服务的配置文件
sudo cp /etc/ssh/sshd_config /etc/ssh/sshd_config.ORIGINAL
编辑配置文件
sudo gedit /etc/ssh/sshd_config
* 将参数 PermitRootLogin 由 yes 更改为 no。 超级用户不能直接通过远程联机。
* 添加参数 AllowUsers 设定远程连接的用户名 (用空格来分割) 。
* 您也可以使用 DenyUsers for fine-grained selection of users.
* If you enable the openssh server and you have no intention for now to enable remote connections, you may add AllowUsers nosuchuserhere to disable anyone connecting.
SSH 命令
ssh 命令可以用来在远程机器上不经 shell 提示登录而执行命令。它的语法格式是： ssh hostname command。譬如，如果你想在远程主机 penguin.example.net 上执行 ls /usr/share/doc 命令，在 shell 提示下键入下面的命令：
ssh penguin.example.net ls /usr/share/doc
3.2. 使用 scp 命令
scp 命令可以用来通过安全、加密的连接在机器间传输文件。它与 rcp 相似。
把本地文件传输给远程系统的一般语法是：
scp localfile username@tohostname:/newfilename
localfile 指定源文件，username@tohostname:/newfilename 指定目标文件。
要把本地文件 shadowman 传送到你在 penguin.example.net 上的账号内，在 shell 提示下键入(把 username 替换成你的用户名)：
scp shadowman username@penguin.example.net:/home/username
这会把本地文件 shadowman 传输给 penguin.example.net 上的 /home/username/shadowman 文件。
把远程文件传输给本地系统的一般语法是：
scp username@tohostname:/remotefile /newlocalfile
remotefile 指定源文件，newlocalfile 指定目标文件。
源文件可以由多个文件组成。譬如，要把目录 /downloads 的内容传输到远程机器 penguin.example.net 上现存的 uploads 目录，在 shell 提示下键入下列命令：
scp /downloads/* username@penguin.example.net:/uploads/
3.3. 使用 sftp 命令
sftp 工具可以用来打开一次安全互动的 FTP 会话。它与 ftp 相似，只不过，它使用安全、加密的连接。它的一般语法是：sftp username@hostname.com。一旦通过 验证，你可以使用一组和使用 FTP 相似的命令。请参阅 sftp 的说明书页(man)来获取这些 命令的列表。要阅读说明书页，在 shell 提示下执行 man sftp 命令。sftp 工具只在 OpenSSH 版本 2.5.0p1 以上才有。
SSH 概念
SSH是指Secure Shell，SSH协议族由IETF（Internet Engineering Task Force）的Network Working Group制定，SSH协议的内容SSH协议是建立在应用层和传输层基础上的安全协议。
传统的网络服务程序，如FTP、Pop和Telnet其本质上都是不安全的；因为它们在网络上用明文传送数据、用户帐号和用户口令，很容易受到中间人（man-in-the-middle）攻击方式的攻击。就是存在另一个人或者一台机器冒充真正的服务器接收用户传给服务器的数据，然后再冒充用户把数据传给真正的服务器。
SSH(Secure Shell)是目前比较可靠的为远程登录会话和其他网络服务提供安全性的协议。利用SSH协议可以有效防止远程管理过程中的信息泄露问题。通过SSH，可以把所有传输的数据进行加密，也能够防止DNS欺骗和IP欺骗。
SSH，还有一个额外的好处就是传输的数据是经过压缩的，所以可以加快传输的速度。SSH有很多功能，它既可以代替Telnet，又可以为FTP、Pop、甚至为PPP提供一个安全的"通道"。
ssh -X guoshuang@192.168.100.4
支持 SSH 图形界面。也就是说，gedit 打开和另存都是在服务器端操作的。nautilus 打开服务器端的文件管理器。这下就比只用命令行方便多了。不知道 windows 下的 putty 支持不。
ssh -X guoshuang@192.168.100.4 ls
直接在服务器端执行 ls 返回结果到客户端
12.20.2007
ubuntu SSH 连接
12.19.2007
DB2上机操作指令指南
DB2上机操作指令指南
1. 启动实例(db2inst1):
db2start
2. 停止实例(db2inst1):
db2stop
3. 列出所有实例(db2inst1)
db2ilist
5.列出当前实例:
db2 get instance
4. 察看示例配置文件:
db2 get dbm cfg|more
5. 更新数据库管理器参数信息:
db2 update dbm cfg using para_name para_value
6. 创建数据库:
db2 create db test
7. 察看数据库配置参数信息
db2 get db cfg for test|more
8. 更新数据库参数配置信息
db2 update db cfg for test using para_name para_value
10.删除数据库:
db2 drop db test
11.连接数据库
db2 connect to test
11.列出所有表空间的详细信息。
db2 list tablespaces show detail
12.列出容器的信息
db2 list tablespace containers for tbs_id show detail
13.创建表:
db2 ceate table tb1(id integer not null,name char(10))
14.列出所有表
db2 list tables
12.插入数据:
db2 insert into tb1 values(1,’sam’);
db2 insert into tb2 values(2,’smitty’);
13.查询数据:
db2 select * from tb1
14.数据:
db2 delete from tb1 where id=1
15.创建索引:
db2 create index idx1 on tb1(id);
16.创建视图:
db2 create view view1 as select id from tb1
17.查询视图:
db2 select * from view1
18.节点编目
db2 catalog tcp node node_name remote server_ip server server_port
19.察看端口号
db2 get dbm cfg|grep SVCENAME
20.测试节点的附接
db2 attach to node_name
21.察看本地节点
db2 list node direcotry
22.节点反编目
db2 uncatalog node node_name
23.数据库编目
db2 catalog db db_name as db_alias at node node_name
24.察看数据库的编目
db2 list db directory
25.连接数据库
db2 connect to db_alias user user_name using user_password
26.数据库反编目
db2 uncatalog db db_alias
27.导出数据
db2 export to myfile of ixf messages msg select * from tb1
28.导入数据
db2 import from myfile of ixf messages msg replace into tb1
29.导出数据库的所有表数据
db2move test export
30.生成数据库的定义
db2look -d db_alias -a -e -m -l -x -f -o db2look.sql
31.创建数据库
db2 create db test1
32.生成定义
db2 -tvf db2look.sql
33.导入数据库所有的数据
db2move db_alias import
34.重组检查
db2 reorgchk
35.重组表tb1
db2 reorg table tb1
36.更新统计信息
db2 runstats on table tb1
37.备份数据库test
db2 backup db test
38.恢复数据库test
db2 restore db test
出自：http://blog.csdn.net/greener2000/
1. 启动实例(db2inst1):
db2start
2. 停止实例(db2inst1):
db2stop
3. 列出所有实例(db2inst1)
db2ilist
5.列出当前实例:
db2 get instance
4. 察看示例配置文件:
db2 get dbm cfg|more
5. 更新数据库管理器参数信息:
db2 update dbm cfg using para_name para_value
6. 创建数据库:
db2 create db test
7. 察看数据库配置参数信息
db2 get db cfg for test|more
8. 更新数据库参数配置信息
db2 update db cfg for test using para_name para_value
10.删除数据库:
db2 drop db test
11.连接数据库
db2 connect to test
11.列出所有表空间的详细信息。
db2 list tablespaces show detail
12.列出容器的信息
db2 list tablespace containers for tbs_id show detail
13.创建表:
db2 ceate table tb1(id integer not null,name char(10))
14.列出所有表
db2 list tables
12.插入数据:
db2 insert into tb1 values(1,’sam’);
db2 insert into tb2 values(2,’smitty’);
13.查询数据:
db2 select * from tb1
14.数据:
db2 delete from tb1 where id=1
15.创建索引:
db2 create index idx1 on tb1(id);
16.创建视图:
db2 create view view1 as select id from tb1
17.查询视图:
db2 select * from view1
18.节点编目
db2 catalog tcp node node_name remote server_ip server server_port
19.察看端口号
db2 get dbm cfg|grep SVCENAME
20.测试节点的附接
db2 attach to node_name
21.察看本地节点
db2 list node direcotry
22.节点反编目
db2 uncatalog node node_name
23.数据库编目
db2 catalog db db_name as db_alias at node node_name
24.察看数据库的编目
db2 list db directory
25.连接数据库
db2 connect to db_alias user user_name using user_password
26.数据库反编目
db2 uncatalog db db_alias
27.导出数据
db2 export to myfile of ixf messages msg select * from tb1
28.导入数据
db2 import from myfile of ixf messages msg replace into tb1
29.导出数据库的所有表数据
db2move test export
30.生成数据库的定义
db2look -d db_alias -a -e -m -l -x -f -o db2look.sql
31.创建数据库
db2 create db test1
32.生成定义
db2 -tvf db2look.sql
33.导入数据库所有的数据
db2move db_alias import
34.重组检查
db2 reorgchk
35.重组表tb1
db2 reorg table tb1
36.更新统计信息
db2 runstats on table tb1
37.备份数据库test
db2 backup db test
38.恢复数据库test
db2 restore db test
出自：http://blog.csdn.net/greener2000/
MySql常用操作命令
一、连接mysql。
格式： mysql -h主机地址 -u用户名 －p用户密码
1、 例1：连接到本机上的mysql。
首先在打开DOS窗口，然后进入目录mysqlin，再键入命令mysql -uroot -p， 回
车后提示你输密码，如果刚安装好mysql，超级用户root是没有密码的， 故直接回
车即可进入到mysql中了，mysql的提示符是：mysql>
2、 例2：连接到远程主机上的mysql。假设远程主机的IP为：110.110.110.110， 用户
名为root,密码为abcd123。则键入以下命令：
mysql -h110.110.110.110 -uroot -pabcd123
（注:u与root可以不用加空格，其它也一样）
3、 退出mysql命令：exit（回车）
二、修改密码。
格式：mysqladmin -u用户名 -p旧密码 password 新密码
1、 例1：给root加个密码ab12。首先在DOS下进入目录mysqlbin，然后键入以下命令
mysqladmin -uroot password ab12
注：因为开始时root没有密码，所以-p旧密码一项就可以省略了。
2、 例2：再将root的密码改为djg345。
mysqladmin -uroot -pab12 password djg345
另一种方法：
shell>mysql -u root -p
mysql>SET PASSWORD FOR root=PASSWORD("root");
三、增加新用户。（注意：和上面不同，下面的因为是mysql环境中的命令， 所以后面都带
一个分号作为命令结束符）
格式：grant select on 数据库.* to 用户名@登录主机 identified by "密码"
例1、增加一个用户test1密码为abc，让他可以在任何主机上登录， 并对所有数据库有
查询、插入、修改、删除的权限。首先用以root用户连入mysql，然后键入以下命
令：
grant select,insert,update,delete on *.* to test1@"%" Identified
by "abc";
但例1增加的用户是十分危险的，你想如某个人知道test1的密码，那么他就可以在
internet上的任何一台电脑上登录你的mysql数据库并对你的数据可以为所欲为了，
解决办法见例2。
例2、增加一个用户test2密码为abc,让其只可以在localhost上登录， 并可以对数据库
mydb进行查询、插入、修改、删除的操作（localhost指本地主机， 即mysql数据
库所在的那台主机），这样用户即使用知道test2的密码，也无法从internet上直
接访问数据库，只能通过mysql主机上的web页来访问了。
grant select,insert,update,delete on mydb.* to test2@localhost
identified by "abc";
如果你不想test2有密码，可以再打一个命令将密码消掉。
grant select,insert,update,delete on mydb.* to test2@localhost
identified by "";
有关数据库方面的操作。注意：你必须首先登录到mysql中，以下操作都是在mysql的提
示符下进行的，而且每个命令以分号结束。
一、操作技巧
1、 如果你打命令时，回车后发现忘记加分号，你无须重打一遍命令，只要打个分号回
车就可以了。也就是说你可以把一个完整的命令分成几行来打，完后用分号作结束
标志就OK。
2、 你可以使用光标上下键调出以前的命令。但以前我用过的一个mysql旧版本不支持。
我现在用的是mysql-3.23.27-beta-win。
二、显示命令
1、 显示数据库列表。
show databases;
刚开始时才两个数据库：mysql和test。 mysql库很重要它里面有mysql的系统信息，
我们改密码和新增用户，实际上就是用这个库进行操作。
2、 显示库中的数据表：
use mysql； ／／打开库，学过FOXBASE的一定不会陌生吧
show tables;
3、 显示数据表的结构：
describe 表名;
4、 建库：
create database 库名;
5、 建表：
use 库名；
create table 表名(字段设定列表)；
6、 删库和删表:
drop database 库名;
drop table 表名；
7、 将表中记录清空：
delete from 表名;
8、 显示表中的记录：
select * from 表名;
三、一个建库和建表以及插入数据的实例
drop database if exists school; //如果存在SCHOOL则删除
create database school; //建立库SCHOOL
use school; //打开库SCHOOL
create table teacher //建立表TEACHER
(
id int(3) auto_increment not null primary key,
name char(10) not null,
address varchar(50) default '深圳',
year date
); //建表结束
//以下为插入字段
insert into teacher values('','glchengang','深圳一中','1976-10-10');
insert into teacher values('','jack','深圳一中','1975-12-23');
注：在建表中
(1) 将ID设为长度为3的数字字段:int(3)，并让它每个记录自动加一: auto_increment，
并不能为空:not null，而且让它成为主字段primary key
(2) 将NAME设为长度为10的字符字段
(3) 将ADDRESS设为长度50的字符字段，而且缺省值为深圳。varchar和char有什么区别
呢，只有等以后的文章再说了。
(4) 将YEAR设为日期字段。
如果你在mysql提示符键入上面的命令也可以，但不方便调试。 你可以将以上命令
原样写入一个文本文件中假设为school.sql，然后复制到c:下，并在DOS状态进入目录
mysqlin，然后键入以下命令：
mysql -uroot -p密码 < c:school.sql
如果成功，空出一行无任何显示；如有错误，会有提示。（以上命令已经调试，你
只要将//的注释去掉即可使用）。
四、将文本数据转到数据库中
1、 文本数据应符合的格式：字段数据之间用tab键隔开，null值用来代替。例：
3 rose 深圳二中 1976-10-10
4 mike 深圳一中 1975-12-23
2、 数据传入命令load data local infile "文件名" into table 表名;
注意：你最好将文件复制到mysqlin目录下，并且要先用use命令选表所在的库。
五、导出和导入数据：(命令在DOS的mysqlin目录下执行)
导出表
mysqldump --opt school > school.sql
注释：将数据库school中的表全部备份到school.sql文件，school.sql是一个文本文件，
文件名任取，打开看看你会有新发现。
mysqldump --opt school teacher student > school.teacher.student.sql
注释：将数据库school中的teacher表和student表备份到school.teacher.student.sql文
件，school.teacher.student.sql是一个文本文件，文件名任取，打开看看你会有新发现。
导入表
mysql
mysql>create database school;
mysql>use school;
mysql>source school.sql;
(或将school.sql换为school.teacher.sql / school.teacher.student.sql)
导出数据库
mysqldump --databases db1 db2 > db1.db2.sql
注释：将数据库dbl和db2备份到db1.db2.sql文件，db1.db2.sql是一个文本文件，文件名
任取，打开看看你会有新发现。
(举个例子：
mysqldump -h host -u user -p pass --databases dbname > file.dump
就是把host上的以名字user，口令pass的数据库dbname导入到文件file.dump中。)
导入数据库
mysql < db1.db2.sql
复制数据库
mysqldump --all-databases > all-databases.sql
注释：将所有数据库备份到all-databases.sql文件，all-databases.sql是一个文本文件，
文件名任取。
导入数据库
mysql
mysql>drop database a;
mysql>drop database b;
mysql>drop database c;
...
mysql>source all-databases.sql; (或exit退出mysql后 mysql < all-databases.sql)
后记：
其实mysql的对数据库的操作与其它的SQL类数据库大同小异， 您最好找本将SQL的书看
看。我在这里只介绍一些基本的，其实我也就只懂这些了，呵呵。
最好的mysql教程还是"晏子"译的"mysql中文参考手册"。不仅免费，每个相关网站都有
下载，而且它是最权威的。可惜不象"PHP4中文手册"那样是chm的格式， 在查找函数命令的
时候不太方便。
3.打开数据库：use dbname；
显示所有数据库：show databases;
显示数据库mysql中所有的表：先use mysql；然后show tables;
显示表的列信息：describe user;(显示表mysql数据库中user表的信息）；
4.创建一个可以从任何地方连接服务器的一个完全的超级用户，但是必须使用一个口令something做这个
GRANT ALL PRIVILEGES ON *.* TO monty@localhost IDENTIFIED BY 'something' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO monty@"%" IDENTIFIED BY 'something' WITH GRANT OPTION;
5.删除授权：
REVOKE ALL PRIVILEGES ON *.* FROM root@"%";
USE mysql;
DELETE FROM user WHERE User="root" and Host="%";
FLUSH PRIVILEGES;
6. 创建一个用户custom在特定客户端weiqiong.com登录，可访问特定数据库bankaccount
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON bankaccount.*
TO [email]custom@weiqiong.com[/email] IDENTIFIED BY 'stupid';
7.重命名表:
ALTER TABLE t1 RENAME t2;
为了改变列a，从INTEGER改为TINYINT NOT NULL(名字一样)，
并且改变列b，从CHAR(10)改为CHAR(20)，同时重命名它，从b改为c:
ALTER TABLE t2 MODIFY a TINYINT NOT NULL, CHANGE b c CHAR(20);
增加一个新TIMESTAMP列，名为d：
ALTER TABLE t2 ADD d TIMESTAMP;
在列d上增加一个索引，并且使列a为主键：
ALTER TABLE t2 ADD INDEX (d), ADD PRIMARY KEY (a);
删除列c：
ALTER TABLE t2 DROP COLUMN c;
增加一个新的AUTO_INCREMENT整数列，命名为c：
ALTER TABLE t2 ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT,ADD INDEX (c);
注意，我们索引了c，因为AUTO_INCREMENT柱必须被索引，并且另外我们声明c为NOT NULL，
因为索引了的列不能是NULL。
8.删除记录:
DELETE FROM t1 WHERE C>10;
6.改变某几行:
UPDATE t1 SET user=weiqiong,password=weiqiong;
7.使用name列的头10个字符创建一个索引:
CREATE INDEX part_of_name ON customer (name(10));
数据导入问题 （by sese and Zjtv）
大家一定使用过 phpmyadmin 里面的数据库导入，导出功能，非常方便。但是在实际应用中，我发现如下几个问题：
1、数据库超过一定尺寸，比如6M 这时使用导出一般没问题，可以正确的保存到本机硬盘上面，但是导入则不行！原因是：一般的 PHP.INI 里面设置临时文件/上传文件的大小限制为2M，而phpmyadmin使用了上传的方式，造成失败。
2、导出到硬盘的 .SQL 文件在导回时，经常出现由于某些单引号的问题引起失败，造成导入失败，只能用 mysql等应用程序导入了。
我的数据库已经超过10M,所以必须解决这个问题。我的思路：
导出： 用phpmyadmin 保存数据库/表格结构，用脚本读取数据库内容并保存到文件里面！
导入： 用phpmyadmin 恢复数据库/表格结构，用脚本读取文件，然后保存到库里面！
导出程序如下：调用方法为 ****.php?table=tablename
这个简单的程序目前一次保存一个表格！！每行为一个字段的数据！！
if($table=="")exit();
mysql_connect("localhost","name","password");
mysql_select_db("database");
$result = mysql_query("select * from $table");
if(mysql_num_rows($result) <= 0) exit();
echo "开始转换数据到文本...
";
$handle = fopen("$table.txt","w");
$numfields = mysql_num_fields($result);
fputs($handle,$numfields."\r\n");
for($k=0;$k
{
$msg = mysql_fetch_row($result);
for($i=0;$i<$numfields;$i++)
{
$msg[$i] = str_replace("\r\n","&&php2000mysqlreturn&&",$msg[$i]);
$msg[$i] = str_replace("\n" ,"&&php2000mysqlreturn&&",$msg[$i]);
fputs($handle,$msg[$i]."\r\n");
}
fputs($handle,"------- php2000 dump data program V1.0 for MySQL --------\r\n");
}
fclose($handle);
echo "ok";
?>
导入的程序如下：用法同上面！
if($table=="")exit();
mysql_connect("localhost","name","password");
mysql_select_db("database");
$message = file("$table.txt");
echo $numfields = chop($message[0]);
for($k=1;$k
{
$value="";
for ($i=$k;$i<($k+$numfields-1);$i++)
{
$tmp = str_replace("&&php2000mysqlreturn&&","\r\n",chop($message[$i]));
$value .= "'".addslashes($tmp)."',";
}
$tmp = str_replace("&&php2000mysqlreturn&&","\r\n",chop($message[$k+$numfields-1]));
$value .= "'".$tmp."'";
$query = "insert into $table values (".$value.")";
echo mysql_error();
mysql_query($query);
echo $k." ";
}
echo "ok";
?>
使用方法和可能的问题！
1、导入时 file()函数可能会有问题（我的10M的数据没出现问题），可以改为 fopen()然后没次读一行！！
2、导入，导出都需要用 ftp 操作，也就是导出后，用 ftp 把数据转到本机，导入时先用ftp转移数据到服务器！
上面的是在phpmyadmin导入大数据的方法
小于2m的可以用下面的方法
备份:登陆空间的phpmyadmin后在左侧选择要备份及导入的数据库,然后在右侧的数据表列表最上面选择导出标签,就是sql后面的那个,在左侧选择要备份的表,选上下面的另存为文件,点最下面的执行就会直接跳出下载
导入:
进入PHPMYADMIN控制面板
选择中文显示
在左侧选择你需要导入的数据库--点击
在出现页面的上方点击SQL
在跳转的页面中可以导入数据，导入方法和你上传文件类似
也可以把数据输入文本框内导入的
大家知道.MYSQL管理工具PHPMYADMIN对于大于2M的数据就很难直接导入了
DB2常用命令集
* version: V1.0
* author: xiedd
* update: 2006-06-14
* memo: 详细命令请使用"db2 ?
*************************************************
1. 关闭db2
db2stop 或
db2stop force。
在脚本中一般两个都写上，避免使用db2stop force命令，如：
db2stop
db2stop force
2. 启动db2
db2start
3. 创建数据库
db2 create db
或db2 create db using codeset GBK territory CN
4. 删除数据库
执行此操作要小心。
db2 drop db
如果不能删除，断开所有数据库连接或者重启db2。
5. 断开数据库连接
db2 force application all
6. 连接数据库
db2 connect to
7. 断开数据库连接
断开当前数据库连接：db2 connect reset
或者：db2 disconnect current
断开所有数据库的连接：db2 disconnect all
8. 备份数据库
db2 backup db
备注：执行以上命令之前需要断开数据库连接
9. 恢复数据库
db2 restore db
10. 导出数据文件
db2move
11. 导入数据文件
db2move
12. 列出数据库中所有db
db2 list db directory
13. 进入db2命令环境
在“运行”中执行：db2cmd
14. 获取db2数据库管理配置环境信息
db2 get dbm cfg
15. 获取db2某个数据库数据库管理配置环境信息
db2 get db cfg for
或者：连接至某个数据库以后执行db2 get db cfg。
16. 设置联合数据库为可用（默认联合数据库不可用）
db2 update dbm cfg using federated yes
17. 更改db2日志空间的大小
备注：以下命令为了防止db2数据库过份使用硬盘空间而设，仅用于开发者自己机器上的db2，如果是服务器，则参数需要修改。
db2 UPDATE DB CFG FOR
如果页大小是4KB，则以上命令创建3个100M的日志文件，占用300MB硬盘空间。25600*4KB=102400KB。
18. 创建临时表空间
DB2 CREATE USER TEMPORARY TABLESPACE STMASPACE PAGESIZE 32 K MANAGED BY DATABASE USING (FILE 'D:\DB2_TAB\STMASPACE.F1' 10000) EXTENTSIZE 256
19. 获取数据库管理器的快照数据
db2 –v get snapshot for dbm
20. 显示进行程号
db2 list applications show detail
21. 调查错误
sqlcode:产品特定错误码；
sqlstate:DB2系列产品的公共错误码，符合ISO/ANSI 92SQL标准。
调查sqlcode : db2 ? sql1403n
调查sqlstate: db2 ? 08004
22. 创建表空间
rem 创建缓冲池空间 8K
db2 connect to gather
db2 CREATE BUFFERPOOL STMABMP IMMEDIATE SIZE 25000 PAGESIZE 8K
rem 创建表空间：STMA
rem 必须确认路径正确
rem D:\DB2Container\Stma
db2 drop tablespace stma
db2 CREATE REGULAR TABLESPACE STMA PAGESIZE 8 K MANAGED BY SYSTEM USING ('D:\DB2Container\Stma' ) EXTENTSIZE 8 OVERHEAD 10.5 PREFETCHSIZE 8 TRANSFERRATE 0.14 BUFFERPOOL STMABMP DROPPED TABLE RECOVERY OFF
db2 connect reset
23. 将暂挂的数据恢复到前滚状态
db2 ROLLFORWARD DATABASE TESTDB TO END OF LOGS AND COMPLETE NORETRIEVE
24. 备份表空间
BACKUP DATABASE YNDC TABLESPACE ( USERSPACE1 ) TO "D:\temp" WITH 2 BUFFERS BUFFER 1024 PARALLELISM 1 WITHOUT PROMPTING
25. 创建db2工具数据库
db2 create tools catalog systools create new database toolsdb
26. 如何进行增量/差量备份
增量：上一次完整备份至本次备份之间增加的数据部分；
差量(delta)：上次备份以来（可能是完整备份、增量备份或者差量备份）至本次备份之间增加的数据部分；
27. 更新所有表的统计信息
db2 -v connect to DB_NAME
db2 -v "select tbname, nleaf, nlevels, stats_timefrom sysibm.sysindexes"
db2 -v reorgchkupdate statistics on table all
db2 -v "select tbname, nleaf, nlevels, stats_timefrom sysibm.sysindexes"
db2 -v terminate
28. 对一张表运行统计信息
db2 -v runstatson table TAB_NAMEand indexes all
29. 查看是否对数据库执行了RUNSTATS
db2 -v "select tbname, nleaf, nlevels, stats_timefrom sysibm.sysindexes"
30. 更改缓冲池的大小
缓冲池中，当syscat.bufferpools的npages是-1时，由数据库的配置参数bufferpage控制缓冲池的大小。
将npages的值更改为-1的命令：
db2 -v connect to DB_NAME
db2 -v select * from syscat.bufferpools
db2 -v alter bufferpoolIBMDEFAULTBP size -1
db2 -v connect reset
db2 -v terminate
更改数据库配置参数BufferPages的命令如下：
db2 -v update db cfgfor dbnameusing BUFFPAGE bigger_value
db2 -v terminate
31. 查看数据库监视内容列表
db2 -v get monitor switches
32. 打开某个数据库监视内容
db2 -v update monitor switches using bufferpoolon
33. 获取数据库快照
db2 -v get snapshot for all databases > snap.out
db2 -v get snapshot for dbm>> snap.out
db2 -v get snapshot for all bufferpools>> snap.out
db2 -v terminate
34. 重置数据库快照
db2 -v reset monitor all
35. 计算缓冲池命中率
理想情况下缓冲池命中率在95%以上，计算公式如下：
(1 -((buffer pool data physical reads + buffer pool index physical reads) /(buffer pool data logical reads + pool index logical reads))) *100%
36. 创建db2实例
db2icrt <实例名称>
37. 删除db2实例
db2idrop <实例名称>
38. 设置当前db2实例
set db2intance=db2
39. 显示db2拥有的实例
db2ilist
40. 恢复离线增量备份数据库的命令
DB2 RESTORE DATABASE YNDC INCREMENTAL AUTOMATIC FROM D:\backup\autobak\db2 TAKEN AT 20060314232015
41. 创建样本数据库
在unix平台，使用：sqllib/bin/db2sampl
在windows,os/2平台，使用：db2sampl e,e是可选参数，指定将创建数据库的驱动器；
42. 列出数据库中所有的表
db2 list tables
43. 列出某个表的数据结构
db2 describe table v_ro_role
44. 给表增加列
ALTER TABLE STAFF ADD COLUMN PNHONE VARCHAR(20)
45. 数据迁移方法1
export脚本示例
db2 connect to testdb user test password test
db2 "export to aa1.ixf of ixf select * from table1"
db2 "export to aa2.ixf of ixf select * from table2"
db2 connect reset
import脚本示例
db2 connect to testdb user test password test
db2 "load from aa1.ixf of ixf replace into table1 COPY NO without prompting "
db2 "load from aa2.ixf of ixf replace into table2 COPY NO without prompting "
db2 connect reset
46.
db2常用操作笔记
db2常用操作笔记
d:\winnt\system32\drivers\etc\services文件是对机器名到IP地址的解析。
d:\winnt\system32\drivers\etc\hosts文件是对端口名到端口号的解析。
以上两条上非DB2的，但在DB2中使用到。
db2admin start 起动DB2管理
DB2里对一个命令的HELP是:
>db2 ? 该命令
db2 连接到远程数据库
第一步建一个结点：
>db2 catalog tcpip node 结点名 remote 数据库服务器IP地址 server 端口(50000)
第二步建一个到库的联结别名：
>db2 catalog db 库名 as 别名 at node 结点名
第三步建立联结：
>db2 connect to 别名 user 用户名 using 用户密码
断开联结：
db2 connect reset
db2里需要用到一个系统环境变量：db2codepage， 缺省值是1386，此值非常重要，客户端于数据库端的db2codepage不一样时客户端就无法连上数据库端。
DB2里一个表的全名是：schema.表名，缺什情况下不同的用户看到的表是不一样的，
在程序的sql语句里必需用表的全名(切记)。
查看有哪些程序在使用该数据库：
>db2 list application
其结果中有一个字段application-handle在杀死该引用中用到
杀死一个程序对该数据库的使用:
>db2 force application {all | (application-handle)}
在库被使用时db2stop不能执行
DB2命令后所带参数：
-t 以；作为一句结束
-f 指向一个文件(即执行一个文件中的语句，在command窗口里有些命令无法执行，可先用notepad建一个文件然后执行它)
-v (我不知道是什么)
注释：-v用于显示当前所执行的sql命令。
一个windows nt 里的一般用户可以连到库，但无一般SQL语句的执行权，授权给一个用户：
>db2 grant sql语句(例如：select) on 表名 to user 用户名
取消该用户的权：
>db2 revoke sql语句 on 表名 from 用户名
DB2日志有两种：循环日志和永久型日志
循环日志：有3个文件循环写，所以会产生以前的操作记录被覆盖。好处：日志文件大小不变，备份方便，但不可以在线备份。
永久日志：其日志文件不断增涨，但操作记录不会被覆盖，可以在线备份。
如何备份：
>db2 backup db 数据库名 to 设备名(如：c:\);
恢复：
>db2 restore db 数据库名 from 设备名
(回滚rollforward我不太清楚)
注释：rollforword 是前滚的意思，向前到某一个时间，以保持数据的一致性，用于在线备份后的恢复，恢复是从log日志中开始到日志中的某一个时间，即可。只有在数据一致性得到保证的情况下，才能继续对数据库操作。
连接到数据库时报回滚错误
用下面的命令：
db2 rollforward db fmisadd to end of logs and complete
导出导入数据库：
--export
db2move hadb export -u userid -p password;
--import
db2move hadb import -u userid -p password;
导出库中表的数据：
>export to 文件名.ixf of ixf select * from 表名
>import from 文件名.ixf of ixf create into 表名
导出库的数据格式还有两种del(文件格式)和wsf，但ixf格式信息最全，包含表结构信息，可恢复出已删去的表。
sql的inner/left/right/full join，这些概念在<<数据库概论>>中有说明，left以左表为主，right以右表为主，full左右表记录都会在查寻结果中。
例如：>select aa,bb from db1 left join db2 on db1.id=db2.id
合并查寻：把两个或几个查寻结果合并到一个字段(条件是字段必须兼容)
格式：select ...... union select ....；
使用临时表：
with tmptable (字段1，...) as (select....) --建一个临时表
select 字段1,.... from tmptable,另一个表 where.... --使用该临时表于另一个表交叉查寻。
**order by 必需出现在结果集，在临时表中不能用。
截取字符串
substr(字段名,开始位置,字符个数)
判断是否是空
字段名 is null
例子：
到什么时候活了10000天:
select distinct date('1980-01-01')+10000 day from a
一共活了多少天:
select distinct days(current date)-days(date('1980-01-01')) from a
类型转换：用cast ,例：
select distinct cast(current date as char(10))||'aa' from a
case的使用：
select case when length(rtrim(学历))=0 then '未知学历' else xl end,rs from a
例：查寻一个公司的年龄分布：
with tmptable as (select case when year(current day)-csrq<20 then '小于20岁' when year(current day)-csrq<25 then '20-24岁' when year(current day)-csrq<30 then '25-29岁'
when year(current day)-csrq<35 then '30-34岁'
when year(current day)-csrq<40 then '35-39岁'
when year(current day)-csrq<45 then '40-44岁'
when year(current day)-csrq<50 then '45-49岁' else '大于50岁' end
as x from a)
select x,count(*) from tmptable group by x;
一个数据库中有些系统建的表是用来保存该库各种信息的，如：syscat.tables
例：查有多少个userid的表：
select count(*) from syscat.tables where type='T' and tabschema='uerid'
例：产生一个备分库中所有表的文本：
select 'export to '|| tabname || '.ixf of ixf select * from userid.' ||tabname||';' from syscat.tables where type='T' and tabschema='userid';
性能调整：
影响到DB2性能的有：
能够利用服务器的性能：1.并行性 2.减少通信
建立Index
<,>,= 第一类搜索谓词，
<>,like 第二类搜索谓词，
需要用函数 第三类搜索谓词，
select * from a
*尽量要哪个字段写哪个字段。
blocking的概念 客户端请求一次，数据库返回n个记录。
复合型SQL(我不太清楚)
注释：是指他所讲的联结union，建立临时表等复杂的sql语句。
DB2会对SQL语句优化，系统对表的信息知道的越多，优化越好，所以要做runstats
runstats命令把表的信息告诉系统，一般当数据量增加一倍时用该命令一次。
REORG命令是数据库整理，类似于磁盘碎片整理。
得到该instance的各种参数：(dbm cfg是整个DB2的参数，db cfg是对某个数据库的参数)
>db2 get dbm cfg
>db2 get db cfg for 数据库名
修改参数：
>db2 update dbm cfg using 参数 你要的数
>db2 update db cfg for 数据库名 using 参数 你要的数
当第一个用户连结到该数据库时会申请一个内存缓冲，默认是250页每页4k即1M。
Buffer pool size<4K> [BUFFPAGE]=250
修改该值：
>db2 update db cfg for test using buffpage 你要的数
但它的有效还取决于syscat.bufferpools中的npages是否为-1，为-1时它才有效，否则以syscat.bufferpols中的npage为准。
可用下面的命令看：
>select * from syscat.bufferpools
一般此值的设置应为系统内存的40％左右，太大时会使系统因动用虚拟内存从而太吃力。
sql语句的优化级别：一般取2或5（最高为9）
Default query optinization class
该值越大优化越好，但优化所化时间也越长。
并行性设置：
Degree=-1 并行性全由操作系统完成。
当操作系统有并行处理时，Degree=-1
*****CUP与硬盘的个数一般为1:4到1:6;
用户最大连结数：
BUFFPAGE与MAXPPLS的关系：BUFFPAGE>2*MXAPPLS
Trackback: http://tb.blog.csdn.net/TrackBack.aspx?PostId=290057
Firefox 快捷键
转：Firefox 快捷键大全(2.0版)
作者：大徐 发表时间：2006-11-06 10:59:02
版权声明：可以任意转载，转载时请务必以超链接形式标明文章原始出处。
http://daxu.net/archives/434.html
Firefox 键盘快捷键
导航类
后退： Alt+左方向键 或 Backspace
前进： Shift+Backspace 或 Alt+右方向键
首页： Alt+Home
打开文件： Ctrl+O
重新载入： F5 或 Ctrl+R
重新载入 (忽略缓存)： Ctrl+F5 或 Ctrl+Shift+R
停止： Esc
当前页
到页面底部： End
到页面顶部： Home
移到下一帧： F6
移到上一帧： Shift+F6
页面源代码： Ctrl+U
打印： Ctrl+P
页面另存为： Ctrl+S
放大文字： Ctrl++
缩小文字： Ctrl+-
恢复文本大小： Ctrl+0
编辑
复制： Ctrl+C
剪切： Ctrl+X
删除： Del
粘贴： Ctrl+V
重做： Ctrl+Y
全选： Ctrl+A
撤消： Ctrl+Z
搜索
本页查找： Ctrl+F
再次查找： F3
输入查找链接： '
输入查找文本： /
查找： Shift+F3
网页搜索： Ctrl+K 或 Ctrl+E
窗口和标签
关闭标签： Ctrl+W 或 Ctrl+F4
关闭窗口： Ctrl+Shift+W 或 Alt+F4
向左移动标签： Ctrl+左方向键 或 Ctrl+上方向键
向右移动标签： Ctrl+右方向键 或 Ctrl+下方向键
移动标签到开头： Ctrl+Home
移动标签到结尾： Ctrl+End
新建标签页： Ctrl+T
新建窗口： Ctrl+N
下一个标签页： Ctrl+Tab 或 Ctrl+PageDown
在新标签页打开网址： Alt+Enter
前一个标签页： Ctrl+Shift+Tab 或 Ctrl+PageUp
撤销关闭标签页： Ctrl+Shift+T
选择标签页 [1 to 8]： Ctrl+[1 到 8]
选择最后标签页： Ctrl+9
工具
添加所有为书签： Ctrl+Shift+D
添加当前为书签： Ctrl+D
打开书签： Ctrl+B 或 Ctrl+I
插入浏览： F7
下载： Ctrl+J
历史： Ctrl+H
清除私有数据： Ctrl+Shift+Del
其他
补全 .com 地址： Ctrl+Enter
补全 .net 地址： Shift+Enter
补全 .org 地址： Ctrl+Shift+Enter
删除选定的自匹配输入： Del
全屏： F11
选择地址条： Alt+D 或 F6 或 Ctrl+L
选择或管理搜索引擎： Alt+上方向键 或 Alt+下方向键 或 F4
Firefox 鼠标快捷键
后退： Shift+Scroll down
关闭标签页： 在标签上按鼠标中键
减小文字大小： Ctrl+Scroll up
前进： Shift+Scroll up
增大文字大小： Ctrl+Scroll down
新建标签页： 在标签页栏双击鼠标
在后台打开标签页： Ctrl+Left-click 或 点击鼠标中键
在前台打开标签页： Shift+Ctrl+Left-click 或 Shift+点击鼠标中键
在新窗口打开： Shift+Left-click
刷新 (覆盖缓存)： Shift+Reload button
保存页面为： Alt+Left-click
逐行滚动： Alt+Scroll
以上快捷键由困兽根据 Firefox 2.0 帮助文件整理，适用于 Firefox 2.0 版本。
KDE的使用技巧
转自 linuxsir ，作者：pluskid
最近一直用KDE，有一些小技巧，共享出来给大家吧！
1. 加速 Konqueror 启动：
设置-> 配置 Konqueror -> 性能 -> 预加载:
保留预加载的实例数 设为2
并选中 “KDE 启动之后预加载实例” 和 “必须有一个预加载实例”。
怎么样？现在Konqueror启动是不是快如飞啦？Opera尚比不上，就更别提那个乌龟似的Firefox了。
2. Konqueror 的标签浏览
和Firefox一样，Ctrl+点击就是在新的标签中打开，更进一步的设置请见：
设置-> 配置 Konqueror -> Web行为 -> 标签式浏览 -> 高级选项
值得注意的是里面的一个“显示关闭按钮而不是网站图标”，选中之后，标签上就有一个可以直接点击就关掉标签的按钮了。
3. Konqueror “狗皮膏药”
按一下 Ctrl ，是不是屏幕上出来一些稀奇古怪的黄颜色的像狗皮膏药一样的东西？其实那是为了笔记本的用户设计的，你看没一个膏药里面都有一个对应键盘上的数字或者字母，按那个键就好比用鼠标点击了那个“膏药”处一样，当然，膏药都是在网页上的链接呀、按钮呀之类的地方出现的，这可是一个很人性化的设置哟！如果你不喜欢，在 ~/.kde/share/config/konquerorrc 里面加入：
[Access Keys]
Enabled=false
就可以禁用了。
4. Konqueror 上选课网
设置-> 配置 Konqueror -> 浏览器识别
里面新建，把 10.10.10.32, 10.10.10.33, 10.10.10.34 (不知道教务网怎么网址是变来变去的）都分别添加进去，并选择标识为IE 6.0 on Windows XP 就可以正常访问选课网了。
5. Konqueror flash插件
如果你给 Mozilla 或者 Firefox 装过 flash 的插件
设置-> 配置 Konqueror -> 插件
扫描新插件 就可以扫描到 Firefox 的 flash 插件，就可以正常使用了。
6. Konqueror 的速搜
在地址栏输入
gg:Konqueror
是不是就连接上了 google搜索 Konqueror 了？你可以在
设置-> 配置 Konqueror -> 速搜
里面配置，我是把 gg 的网址改成了 www.google.cn 了的，这样会快一点嘛。
7. 鼠标手势
KDE 里面不止Konqueror可以使用鼠标手势，任何地方都可以使用鼠标手势，在
控制中心 -> 区域和辅助功能 -> 输入动作
里面可以配置鼠标手势，请注意，要启用鼠标手势，请点击右下的“全局设置”，并把“手势设置”一栏里面的“全局禁用鼠标手势”选项前面的勾去掉。
8. Konqueror 中键点击关闭标签
本来是用那个按钮关闭标签，后来不想用按钮了，用鼠标手势，我现在已经很习惯中键点击关闭标签了，要实现这个功能，只要在 ~/.kde/share/config/konquerorrc 里面的 [FMSettings] 一节里面加入
MouseMiddleClickClosesTab=true
就可以了，如果不行的话，请在 设置-> 配置 Konqueror -> Web行为 -> 标签式浏览 里面把鼠标行为里面的“中键单击打开选中的URL”前面的勾去掉。
9. yakuake 超酷终端！
apt-get install yakuake
然后运行yakuake，恩？没有反应？按一下F12，哈哈！屏幕上方出现了一个终端！按F12它有缩回去了。你可以配置成失去焦点之后自动缩回去，可以改变大小，其他配置都是共享 Konsole 的配置方法的，设置背景、颜色之类的，有几个默认快捷键：Ctrl+Shift+N 新建标签。Shift+Right转到右边一个标签。Shift+Left转到左边一个标签。呵呵！真的很方便哟！用了这个之后，我再也没有开过其他终端了。不要再犹豫了，做个链接，让他随着KDE的启动而启动吧！
ln -s /usr/bin/yakuake ~/.kde/Autostart/
10. 加速 KDE
是不是觉得 KDE 有时候有些地方显示有点慢甚至很慢？其实这是 KDE 现在的一个 Bug，就是字体的问题，在
控制中心 -> 外观和主题 -> 字体
那里面看看，如果有设置为 Monospace 或者是 Serif 、 Sans Serif 的，把他改成其他的你喜欢的字体就可以加快KDE的速度。因为 Serif 这些字体其实是 fontconfig 虚拟出来的字体，而KDE现在在处理这个问题上会造成性能很大的下降，官方说的是一个Bug，希望以后会改正。同理，在 Konqueror 的设置里面更改字体也可以提升 Konqueror 的性能。我以前 Konqueror 的地址栏的那个下拉列表，每次我输入网址至少都要4、5秒种，郁闷死了，改过之后就基本上没有延迟了。如果你想使用fontconfig那类似的字体替换，例如，英文使用Courier New,而中文使用SimSun，请参见[11. 用 qtconfig 来实现字体替换]
11. 用 qtconfig 来实现字体替换
apt-get install qt3-qtconfig
现在，运行qtconfig，选 Fonts 一栏。左边有说明的，如果不愿意看，直接设置把，那儿“Font Substitution”的地方，在"Select or enter a family" 那儿选择要替换的字体，比如 Tahoma ，然后在下面 "select substitute family" 选择一种中文字体，比如 SimSun ，然后按 Add。就是这类似的步骤，现在，你可以选择系统的字体为 Tahoma 了，系统使用 Tahoma 字体，但是当他遇到中文字体而 Tahoma 这种英文字体里面不能找到中文字体的时候，就会依次查找你设置的替换字体列表，现在，他找到了 SimSun ，OK！中文就用SimSun来显示了。这有点类似fontconfig的那一套吧？不过这是针对qt程序的，其实KDE就是基于Qt的嘛！^_^
12. 窗口遍历
左边 Alt + Tab 就是遍历窗口，右边Alt+Tab 就是在最近使用的两个窗口之间来回切换。（有些网友说他的刚好相反，我还没有找到这个是在哪儿设置的呢，呵呵！）
在 控制中心 -> 桌面 -> 窗口行为 -> 导航 里面可以设置一些具体的东西。另外 Ctrl+Tab 是用来在桌面之间切换的，不过被我禁用掉了，因为我把这个快捷键用到我的Emacs里面去了。
13. 鼠标单击还是双击的问题
很多朋友都习惯了双击打开文件，但是Konqueror默认是单击打开的，而且Konqueror里面还没有设置选项。其实是可以设置的，但是是在另外一个地方： 控制中心 -> 外设 -> 鼠标 里面改。不过，我还是喜欢单击，习惯了之后，你会发现单击比双击要舒服哟！
14. Konqueror 里面查看 man 、info
在地址栏输入：man:/konqueror 就可以查看 konqueror 的 man page了。同理，输入 info:/cvs 就可以查看 cvs 的 info 文档。当然，前提是里的系统里面装了这些文档。直接输入 info: 会打开一个系统里面的 info 列表。man也是一样，作为一个快捷输入，你可以输入 “#” 来代替 “man:/”，比如： #kate
15. Konqueror 里面通过 ssh 进行远程文件管理
在地址栏输入 fish://user@host 然后按提示输入密码，怎么样？远程文件出现了吧？可以直接预览图像呀之类的，这可比开一个远程的X程序要快得多哟！也可以直接编辑远程文件，但是必须用 KDE的那些程序如Kate之类的，好像是用到kio还是什么吧？反正是很方便的哟！
16. Konqueror 的拼写检查
这个功能会造成文本框输入很慢，而且像我们这样经常输入代码（被Konqueror检查就是一大堆错别字），几乎用不上那个功能，但是这个又不能永久关掉。其实，你可以配置拼写检查，把他改成你没有的拼写检查工具就可以了，例如，我设置的是 zemberek 土耳其语。 这下感觉好多了。
Koqueror的常见问题，很多有用的Tip
http://www.konqueror.org/faq/
其实有不少问题，认真阅读KDE的帮助中心，会找到答案，帮助中心中也有不少所谓Tips & Tricks，比如一些影响KDE的环境变量如`KDE_DEBUG', `KDE_NO_IPV6', `KDE_LANG'等。但也有一些隐藏的，只有通过配置文件才能修改的设置，希望大家补充。
比如说在
~/.kde/share/config/konqsidebartng.rc
开头加入
ShowArchivesAsFolders=false
这样文件管理器视图的侧边栏中的目录树中就不会出现归档文件了。
c学习笔记 tag1
今天才知道，原来link还要加 -lm， sign。。
/*
* pol_draw.c
*
* Copyright 2007 kaiyuan
*
* This program is free software; you can redistribute it and/or modify
* it under the terms of the GNU General Public License as published by
* the Free Software Foundation; either version 2 of the License, or
* (at your option) any later version.
*
* This program is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
* GNU General Public License for more details.
*
* You should have received a copy of the GNU General Public License
* along with this program; if not, write to the Free Software
* Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston,
* MA 02110-1301, USA.
*/
#include
#include
int main(int argc, char** argv)
{
double y;
int x,m,n,yy;
for(yy=0; yy<=20; yy++){
y=0.1 * yy;
m=acos(1-y)+31;
n=45 * (y-1) + 31;
for(x=0; x<=62; x++)
if(x==m && x==n)
printf("+");
else if(x==n)
printf("+");
else if(x==m || x==62-m)
printf("*");
else
printf(" ");
printf("\n");
}
return 0;
}
12.18.2007
Bib Tex 使用笔记（引）
这里记录一下今天练习使用BibTex，插入Latex文件的经历。另外，也建议大家，如果的话，不要直接跑盗版软件摊，先搜一下google的free software，估计这种软件的存在。是不是？不做开发者，但是好的使用者也会让作者有激情继续下去。
我是在sourceforge.net找到的JabRef，是一个java程序。
另外，找到一个教程，叫做how to use Bibtex，网址是：
http://cmtw.harvard.edu/Documentation/TeX/Bibtex/Example.html
我首先做的是安装BiBtex，因为先前已经安装了Latex，现在只需要apt-get 一下Bibtex就好了，另外选了几个相关的工具包，包括一个python的图形界面的工具，但是感觉没有JabRef强。名字叫做pyBibliographer。
笔记：
先建立一个bib文件，命名为qhe.bib，内容大家熟悉html或者latex的话，应该很容易理解。内容为：
@STRING(PRL="Phys. Rev. Lett.")
@STRING(RMP="Rev. Mod. Phys.")
@ARTICLE{klitzing:qhe,
AUTHOR="K. von Klitzing and G. Dorda and M. Pepper",
TITLE="New method for high accuracy determination of fine structure
constant based on quantised hall resistance",
JOURNAL=PRL,
VOLUME=45,
PAGES=494,
YEAR=1980
}
@ARTICLE{klitzing:nobel,
AUTHOR="Klaus von Klitzing",
TITLE="The Quantised Hall Effect",
JOURNAL=RMP,
VOLUME=58,
PAGES=519,
YEAR=1986
}
然后建立tex文件，在文件里用~\cite去引用参考文献。每个bibliography都有一个key，这个就是代号，用于引用作用。见例子：
\documentstyle{article}
\begin{document}
\bibliographystyle{prsty} % Choose Phys. Rev. style for bibliography
\section{Introduction}
The discovery of the Quantised Hall Effect was made by
Klitzing~\cite{klitzing:qhe} for which he was awarded the 1985 Nobel
prize for physics~\cite{klitzing:nobel}.
\bibliography{qhe} % qhe.bib is the name of our database
\end{document}
然后执行命令：
1. latex example
2. bibtex example
3. latex example
4. latex example
以后使用就是在JabRef生成bib文件，然后在lyx，或者latex源文件里引用就可以了。很方便。
JabRef可以用于搜索和整理。而且可以链接网址和pdf文件。虽然不一定非要pdf文件格式，因为我试验了其他的格式。
12.17.2007
Linux实用命令--文件系统操作
2007-09-22 23:14 来源: sense5.cublog.cn 作者：sense5 网友评论 0 条 浏览次数 67
文件系统操作是最基本的操作，没有文件系统，操作系统根本就运行不了。
下面是我们经常要做的一些事情。在下面具体参数意义不解释，要了解这些
可以查询该命令帮助文档
1. 创建目录
mkdir
NO1. 在当前路径创建一级目录
[root@rehat root]# mkdir test
NO2. 在当前路径创建多级目录
[root@rehat root]# mkdir -p mytest/test1/test1_1
NO3. 在创建目录的同时给新建的目录赋权限
[root@rehat root]# mkdir -m 777 testmod
这样任何人对此目录都有任何权限
2. 复制文件与文件夹
cp
NO1. 复制指定目录的文件到当前目录，并重命名
[root@rehat root]# cp ~/.bashrc bashrc_bak
NO2. 强制复制指定目录的文件到当前目录，而不管当前目录是否含有该文件
[root@rehat root]# cp -f ~/.bashrc bashrc
NO2. 复制指定目录到当前目录
[root@rehat root]# cp -r /root/test .
[root@rehat root]# cp -r /root/test/ .
两者效果一样，在复制目录时，会将源路径的最后一级目录全部复制过去，包括它本身。
NO3. 复制指定目录的文件到指定目录
[root@rehat root]# cp ~/.bashrc /bak/.bashrc
NO4. 在复制时将源文件的全部属性也复制过来。若不指定参数，则目标文件与源文件属性可能不一致。
[root@rehat root]# cp -a ~/.bashrc /bak/.bashrc
NO5. 若两个文件夹要保证同步，一个文件的改了，另一个文件也跟着改，但是要保证两个文件的文件都是最新的。
[root@rehat root]# cp -u /src/.bashrc /bak_src/bashrc
3. 建立链接文件，包括硬链接与软链接
ln
NO1. 建立类似于 Windows 的快捷方式
[root@rehat root]# ln -s test.txt test.txt_slnk
NO2. 当想备份一个文件，但空间又不够，则可以为该文件建立一个硬连接。这样，就算原文件删除了，只要该
链接文件没被删除，则在存储空间里还是没有被删除。
[root@rehat root]# ln -l test.txt test.txt_hlnk
4. 删除文件
rm
NO1. 删除当前目录的文件
[root@rehat root]# rm test.txt
NO2. 强制删除当前目录的文件，不弹出提示
[root@rehat root]# rm -f test.txt
NO3. 强制删除整个目录，包括目录与文件全部删除，需要管理员权限
[root@rehat root]# rm -r -f test
5. 删除文件夹
rmdir
NO1. 删除一个空目录
[root@rehat root]# rmdir emptydir
NO2. 删除多级空目录
[root@rehat root]# rmdir -p emptydir/d1/d11
6. 挂载文件系统与卸载文件系统
mount / umount
NO1. 挂载光驱
[root@rehat root]# mount -t iso9660 /dev/cdrom /mnt/cdrom
NO2. 挂载光驱，支持中文
[root@rehat root]# mount -t iso9660 -o codepage=936,iocharset=cp936 /dev/cdrom /mnt/cdrom
NO3. 挂载 Windows 分区，FAT文件系统
[root@rehat root]# mount -t vfat /dev/hda3 /mnt/cdrom
NO4. 挂载 Windows 分区，NTFS文件系统
[root@rehat root]# mount -t ntfs -o iocharset=cp936 /dev/hda7 /mnt/had7
No5. 挂载 ISO 文件
[root@rehat root]# mount -o loop /abc.iso /mnt/cdrom
NO6. 挂载 软驱
[root@rehat root]# mount /dev/fd0 /mnt/floppy
NO7. 挂载闪盘
[root@rehat root]# mount /dev/sda1 /mnt/cdrom
NO8. 挂载 Windows 操作系统共享的文件夹
[root@rehat root]# mount -t smbfs -o username=guest,password=guest //machine/path /mnt/cdrom
NO9. 显示挂载的文件系统
[root@rehat root]# mount
[root@rehat root]# cat /etc/fstab 显示系统启动自动加载的文件系统
[root@rehat root]# cat /etc/mtab 显示当前加载的文件系统
7. 检查磁盘空间
df
NO1. 显示所有存储系统空间使用情况,同时显示存储系统的文件系统类型s
[root@rehat root]# df -aT
NO2. 显示指定文件系统的空间使用情况
[root@rehat root]# df -t ext3
NO3. 人性化显示各存储空间大小
[root@rehat root]# df -ah
NO4. 有时候挂载了网络文件系统，若只想看本机的文件系统用如下命令
[root@rehat root]# df -ahlT
NO5. 查看某个文件系统的磁盘使用情况
[root@rehat root]# df -h /dev/cdrom
8. 检查目录空间大小
du
NO1. 查看当前文件夹大小
[root@rehat root]# du -sh
NO2. 查看当前文件及文件中包含的子文件夹大小
[root@rehat root]# du -ch
NO3. 查看文件的大小
[root@rehat root]# du -h test1.txt
NO4. 同时查看多个文件的大小
[root@rehat root]# du -h test1.txt test2.txt
9. 磁盘碎片整理
linux 下基本上不用碎片整理，它每隔一段时间会自动整理
10. 创建/改变文件系统
NO1. 创建文件系统类型
[root@rehat root]# umount /dev/sdb1
[root@rehat root]# mkfs -t ext3 /dev/db1
[root@rehat root]# mount /dev/sdb1 /practice
11. 改变文件或文件夹权限
chmod
NO1. 将自己的笔记设为只有自己才能看
[root@rehat root]# chmod go-rwx test.txt
或者
[root@rehat root]# chmod 700 test.txt
NO2. 同时修改多个文件的权限
[root@rehat root]# chmod 700 test1.txt test2.txt
NO3. 修改一个目录的权限，包括其子目录及文件
[root@rehat root]# chmod 700 -R test
12. 改变文件或文件夹拥有者
chown 该命令只有 root 才能使用
NO1. 更改某个文件的拥有者
[root@rehat root]# chown jim:usergroup test.txt
NO2. 更改某个目录的拥有者,并包含子目录
[root@rehat root]# chown jim:usergroup -R test
13. 查看文本文件内容
cat
NO1. 查看文件内容，并在每行前面加上行号
[root@rehat root]# cat -n test.txt
NO2. 查看文件内容，在不是空行的前面加上行号
[root@rehat root]# cat -b test.txt
NO3. 合并两个文件的内容
[root@rehat root]# cat test1.txt test2.txt > test_new.txt
NO4. 全并两具文件的内容，并追回到一个文件
[root@rehat root]# cat test1.txt test2.txt >> test_total.txt
NO5. 清空某个文件的内容
[root@rehat root]# cat /dev/null > test.txt
NO6. 创建一个新的文件
[root@rehat root]# cat > new.txt 按 CTRL + C 结束录入
14. 编辑文件文件
vi
NO1. 新建档案文件
[root@rehat root]# vi newfile.txt
NO2. 修改档案文件
[root@rehat root]# vi test.txt test.txt 已存在
NO3. vi 的两种工作模式：命令模式，编辑模式
NO4. 进入 vi 后为命令模式，按 Insrt 键进入编辑模式
按 ESC 进入命令模式，在命令模式不能编辑，只能输入命令
NO5. 命令模式常用命令
:w 保存当前文档
:q 直接退出 vi
:wq 先保存后退出
15. 路径操作
cd pwd
NO1. 显示当前路径
[root@rehat root]# pwd
NO2. 返回用户主目录
[root@rehat root]# cd
NO3. 改变到其它路径
[root@rehat root]# cd /etc
NO4. 返回到上一级目录
[root@rehat root]# cd ..
NO5. 返回到根目录
[root@rehat root]# cd /
16. 查询文件或文件夹
find
NO1. 查找当前用户主目录下的所有文件
[root@rehat root]# find ~
NO2. 让当前目录中文件属主具有读、写权限，并且文件所属组的用户和其他用户具有读权限的文件；
[root@rehat root]# find . -perm 644 -exec ls -l {} \;
NO3. 为了查找系统中所有文件长度为0的普通文件，并列出它们的完整路径；
[root@rehat root]# find / size 0 -type f -exec ls -l {} \;
NO4. 查找/var/logs目录中更改时间在7日以前的普通文件，并在删除之前询问它们；
[root@rehat root]# find /var/logs -mtime +7 -type f -ok rm -i {} \;
NO5. 为/找系统中所有属于root组的文件；
[root@rehat root]# find / -group root -exec ls -l {} \;
NO6. find命令将删除当目录中访问时间在7日以来、含有数字后缀的admin.log文件
[root@rehat root]# find . -name "admin.log[0-9][0-9][0-9]" -atime -7 -ok rm { } \;
NO7. 为了查找当前文件系统中的所有目录并排序
[root@rehat root]# find . -type d | sort
NO8. 为了查找系统中所有的rmt磁带设备
[root@rehat root]# find /dev/rmt
17. 显示文件/文件夹清单
ls / dir
NO1. 显示所有文件，包括以.开头的隐含文件
[root@rehat root]# ls -a
NO2. 显示文件的详细信息
[root@rehat root]# ls -l
NO3. 显示当前目录及所有子目录信息
[root@rehat root]# ls -Rl
NO4. 以时间排序显示目录,这在找最新文件有用
[root@rehat root]# ls -tl
NO5. 以文件大小排序
[root@rehat root]# ls -Sl
NO6. 显示文件大小,并按大小排序
[root@rehat root]# ls -s -l -S
18. 移动或更改文件/文件夹名称
mv 与 cp命令用法相似
NO1. 若移动目标文件已存在，要在移动之前，先备份原来的目录文件
[root@rehat root]# mv -b test.txt test2/
这样在 test2 下将有两个文件 test.txt 及 text.txt~
其中 test.txt~ 是备份文件，test.txt是新的文件
NO2. 若移动目标文件已存在，但不想弹出是否覆盖的提示，直接覆盖
[root@rehat root]# mv -f test.txt test2/
NO3. 当源与目标都拥有同一个文件，若源文件比目标新则移动，否则不移动
[root@rehat root]# mv -u test.txt test2/
NO4. 更改文件名称
[root@rehat root]# mv test.txt test2.txt
NO5. 更改目录名称
[root@rehat root]# mv /test2 /test2_2
数据库实习
Es gibt noch einfaches:
muss man aber zum ersten Konquror hat...
zum Bsp: fish://
网址：
http://dbcip.cs.uni-duesseldorf.de/~
Also das einfachste ist(fand ich), einfach über SSH reinzukommen:
ssh -L 2401:localhost:2401 dbcip.cs.uni-duesseldorf.de -l
Und wenn man Dateien kopieren will:
scp ./beispiel.txt
Ich bin aber auch kein Fachmann dafür, die Linuxexperten hier werden da sicher noch einiges mehr zu sagen können. Ich hab das immer mit cygwin unter Windows gemacht...
Ubuntu下格式化U盘
U盘有时候莫名其妙的变慢，格式化一下就好了，XP下很简单，linux就要google一下了，
ubuntu下面现在也不错了（其实是从debian看到的），先装一下：
apt-get install dosfstools
然后卸载掉U盘：
umount /dev/sdb1
再格式化：
mkfs.vfat /dev/sda1 (格式化成fat32格式以便在windows上读取)
Game Over
12.13.2007
关于表约束constraint
关于表约束constraint
===========================================================
作者: xsb(http://xsb.itpub.net)
发表于:2005.08.19 09:18
分类: Oracle
出处：http://xsb.itpub.net/post/419/38481
---------------------------------------------------------------
可以使用disable,enable novalidate,enable validate选项。
可用于检查历史数据是否违反某种业务规则，并找出这些记录！
CREATE TABLE t1 (c1 NUMBER,c2 NUMBER);
INSERT INTO t1 VALUES (1,2);
INSERT INTO t1 VALUES (1,20);
COMMIT;
SELECT * FROM t1;
alter table T1
add constraint ck_t1
check (c2 BETWEEN 18 and 80)
ENABLE novalidate;
create table exceptions(row_id rowid,
owner varchar2(30),
table_name varchar2(30),
constraint varchar2(30));
ALTER TABLE t1 ENABLE Validate CONSTRAINT ck_t1
EXCEPTIONS INTO EXCEPTIONS;
SELECT *
FROM T1
WHERE ROWID IN (SELECT ROW_ID FROM EXCEPTIONS WHERE TABLE_NAME = 'T1')
FOR UPDATE;
TRUNCATE TABLE EXCEPTIONS;
------------------------------------------------------------------------------------------------
constraints 三个需要注意的地方
Ref: http://spaces.msn.com/sunmoonking/
工作许多年，一直没有看重CONSTRINTS的作用，除了用CHECK，NOT NULL，INDEX，等，其他的一般也就看看就过去了。最近把零散的知识整理一下，才发现CONSTRAINTS发展15年来的成熟与重要。
ORACLE提供了众多的constraint，如果没有充分利用这些constraints，那么也就是没有充分利用关系型数据库。如果能了解各种 constraint的各种参数，那么就能减少locking的时间，减少constraint检验数据的时间，减少影响其他应用的时间。
CONSTRAINTS：就是让数据满足某些规则。
CONSTRAINTS TYPE： NOT NULL
UNIQUE
PRIMARY KEY
FOREIGN KEY
CHECK
CONSTRAINTS 不但可以建立在TABLE上，也可以建立在VIEW上，
CONSTRAINTS 的状态：ENABLED/DISABLED
VALIDATED/NOVALIDATED
DEFERRABLE/NON-DEFERRABLE
DEFERRED/IMMEDIATE
RELY/NORELY
constraint只能被difered如果最初定义时是deferrable
view的constraints必须被设置成disabled，novalidated或rely
1. deferrable
一个constraint如果被定义成deferrable那么这个constraints可以在deferred和imediate两种状态相互转换。 deferred只在transaction中有效，也就是只可以在transaction过程中使constraint失效，但如果 transaction commit的话，transaction会变成immediate。
1* create table cons_parent (id number(10),name varchar2(10))
SQL> /
Table created.
SQL> create table cons_child (id number(10),name varchar2(10));
Table created.
1* alter table cons_parent add primary key (id)
SQL> /
Table altered.
alter table cons_child add constraints chi_fk_par foreign key (id)
references cons_parent(id)
SQL> alter table cons_child add constraints chi_fk_par foreign key (id)
2 references cons_parent(id)
3 /
Table altered.
一个constraints默认是NOT DEFERRABLE的。
1 select constraint_name||' '||deferrable from all_constraints
2* where constraint_name='CHI_FK_PAR'
SQL> /
CONSTRAINT_NAME||''||DEFERRABLE
---------------------------------------------
CHI_FK_PAR NOT DEFERRABLE
NOT DEFERRABLE的不能在deferred和imediate两种状态相互转换
SQL> set constraints chi_fk_par deferred;
SET constraints chi_fk_par deferred
*
ERROR at line 1:
ORA-02447: cannot defer a constraint that is not deferrable
SQL> alter table cons_child drop constraints chi_fk_par;
Table altered.
1 alter table cons_child add constraints chi_fk_par foreign key (id)
2* references cons_parent(id) deferrable
SQL> /
Table altered.
1 select constraint_name||' '||deferrable from all_constraints
2* where constraint_name='CHI_FK_PAR'
SQL> /
CONSTRAINT_NAME||''||DEFERRABLE
---------------------------------------------
CHI_FK_PAR DEFERRABLE
一个constraint如果被定义成deferrable那么这个constraints可以在deferred和imediate两种状态相互转换
SQL> set constraints chi_fk_par immediate;
Constraint set.
1* insert into cons_child values (2,'llll')
SQL> /
insert into cons_child values (2,'llll')
*
ERROR at line 1:
ORA-02291: integrity constraint (SYSTEM.CHI_FK_PAR) violated - parent key not found
SQL> set constraints chi_fk_par deferred;
Constraint set.
SQL> insert into cons_child values (2,'llll');
1 row created.
SQL> set constraints chi_fk_par immediate;
SET constraints chi_fk_par immediate
*
ERROR at line 1:
ORA-02291: integrity constraint (SYSTEM.CHI_FK_PAR) violated - parent key not found
deferred只在transaction中有效，也就是只可以在transaction过程中使constraint失效，但如果transaction commit的话，transaction会变成immediate。
SQL> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02291: integrity constraint (SYSTEM.CHI_FK_PAR) violated - parent key not found
deferrable会影响CBO的计划，并且正常情况下没有应用的必要，所以建议不要修改，而用系统默认的non deferrable
2. enable/disable validate/novalidate
enable/disable对未来的数据有约束/无约束。
validate/novalidate对已有的数据有约束/无约束。
如果加约束到一个大表，那么ORACLE会LOCK这个表，然后SCAN所有数据，来判断是否符合CONSTRAINT的要求，在繁忙的系统里显然是不合适的。所以用enable novalidate比较合适，因为ORACLE仅仅会LOCK表一小段时间来建立CONSTRAINT，当CONSTRAINT建立后再 VALIDATE，这时检验数据是不会LOCK表的。
这方面很多书上都有例子，就不在这里累述了
3.REFERENCE 让人疑惑的地方
1* create table wwm_father (id number,name varchar2(10),primary key (id,name))
SQL> /
Table created.
SQL> create table wwm_child (id number,name varchar2(10),
2 foreign key (id,name) references wwm_father on delete set null);
Table created.
SQL> insert into wwm_father values (6,'wwm');
1 row created.
SQL> insret into wwm_child values (6,'fff');
SP2-0734: unknown command beginning "insret int..." - rest of line ignored.
可以看出，REFERENCE是起作用的。但下面就有点让人疑惑了，似乎ORACLE不用该用这种策略来做，
SQL> insert into wwm_child values (6,null);
1 row created.
SQL> insert into wwm_child values(null,'lll');
1 row created.
1* insert into wwm_child values (null,null)
SQL> /
1 row created.
SQL> select * from wwm_father;
ID NAME
---------- --------------------
6 wwm
SQL> select * from wwm_child;
ID NAME
---------- --------------------
6
lll
SQL> select count(*) from wwm_child;
COUNT(*)
----------
3
可见，如果向CHILD表插入NULL的话，ORACLE默认认为NULL是匹配FATHER表里相关的REFERENCE的字段内容的。因此FOREIGN KEY的COLUMN大家就需要认真考虑是否要设置成NOT NULL了
12.11.2007
Ubuntu feisty + Tomcat 5.5 + mysql 5.0.26 遠端 jdbc 登入
Ubuntu feisty + Tomcat 5.5 + mysql 5.0.26 遠端 jdbc 登入
1. 首先在mysql中你要先有一個帳號是可以從任何host登入的，如最常見的
root@% 或 root@
也可以自己新增，別忘了設密碼！不然你的資料庫就可以給人亂搞了。
2. 修改mysql使其可接受遠端登入，首先開啟mysql設定檔
sudo gedit /etc/mysql/my.cnf
然後搜尋"bind-address"開頭的那行，在前面把它註解掉
#bind-address 127.0.0.1
記得重新啟動mysql才會載入新設定
sudo /etc/init.d/mysql restart
3. 在/usr/share/tomcat5.5/common/lib目錄中，放入mysql的jdbc connector(driver) jar檔
mysql-connector-java-5.0.7-bin.jar
4. 修改tomcat使其能接受jdbc透過自己擁有的IP或domain名稱連線（例如no-ip提供的定址服務）
sudo gedit /etc/tomcat5.5/policy.d/04webapps.policy
自己在裡面加上一行（大小寫符號要正確）紫色部份代表要用自己的設定替換！
permission java.net.SocketPermission "yourHostAddress", "connect, resolve";
記得重啟tomcat才會載入新設定
sudo /etc/init.d/tomcat5.5 restart
5. 現在準備工作已經完成，只要在你的應用程式或jsp網頁正確設定driver名稱、URL、使用者名稱跟密碼即可進行jdbc連線
Driver - com.mysql.jdbc.Driver
URL - jdbc:mysql://yourHostAddress:mysqlport/databasename
UserName - yourRemoteConnectableMysqlAccount
Password - **************
6. 使用愉快！網路上一堆說明都很不完整…找超久＝＝
database.properties实例
1. DataBase.properties的内容
2.
3.
4. driver=com.mysql.jdbc.Driver
5. url=jdbc:mysql://127.0.0.1:3306/jpetstore
6. username=root
7. password=dongguoh
8.
9. DataBase.xml的内容
10.
11.
12.
13.
14. PropertiesTest
15. "driver">com.mysql.jdbc.Driver
16. "url">jdbc:mysql://127.0.0.1:3306/jpetstore
17. "username">root
18. "password">dongguoh
19.
20.
21. 下面是测试类
22.
23. package DataBase;
24. import java.io.FileInputStream;
25. import java.io.FileNotFoundException;
26. import java.io.IOException;
27. import java.util.Iterator;
28. import java.util.Properties;
29. import java.util.Set;
30.
31.
32. public class TestProperties {
33.
34. public static void main(String[] args) {
35. TestProperties test=new TestProperties();
36. test.luanch();
37. test.luanchXML();
38.
39. }
40. private void luanch(){
41. Properties ppt=new Properties();
42. try {
43. String path=this.getClass().getResource("/").getPath();
44. path=path+"DataBase/DataBase.properties";
45. FileInputStream fis=new FileInputStream(path);
46. ppt.load(fis);
47. fis.close();
48. Set set=ppt.keySet();
49. Iterator it=set.iterator();
50. System.out.println("*********显示的读取DataBase.properties显示的内容*****");
51. while(it.hasNext()){
52. String id=(String)it.next();
53. System.out.println(id+"="+ppt.getProperty(id));
54. }
55.
56. System.out.println("另外一种显示方式");
57. ppt.list(System.out);
58. } catch (FileNotFoundException e) {
59. System.out.println("找不到DataBase.properties这个文件，或者是路径发生错误");
60. } catch (IOException e) {
61. System.out.println("加载DataBase.properties文件时出错！！");
62. }
63. }
64. private void luanchXML(){
65. Properties ppt=new Properties();
66. try {
67. String path=this.getClass().getResource("/").getPath();
68. path=path+"DataBase/DataBase.xml";
69. System.out.println(path);
70. FileInputStream fis=new FileInputStream(path);
71. ppt.loadFromXML(fis);
72. fis.close();
73. Set set=ppt.keySet();
74. Iterator it=set.iterator();
75. System.out.println("*********显示的读取DataBase.xml 显示的内容*****");
76. while(it.hasNext()){
77. String id=(String)it.next();
78. System.out.println(id+"="+ppt.getProperty(id));
79. }
80. } catch (FileNotFoundException e) {
81. System.out.println("找不到DataBase.xml 这个文件，或者是路径发生错误");
82. } catch (IOException e) {
83. System.out.println("加载DataBase.xml 文件时出错！！");
84. }
85. }
86.
87. }
88.
89.
90.
91. 结果：
92.
93. *********显示的读取DataBase.properties显示的内容*****
94. password=dongguoh
95. url=jdbc:mysql://127.0.0.1:3306/jpetstore
96. driver=com.mysql.jdbc.Driver
97. username=root
98. 另外一种显示方式
99. -- listing properties --
100. url=jdbc:mysql://127.0.0.1:3306/jpetstore
101. password=dongguoh
102. driver=com.mysql.jdbc.Driver
103. username=root
104. /E:/MyJavaProject/Ibatis/WebRoot/WEB-INF/classes/DataBase/DataBase.xml
105. *********显示的读取DataBase.xml 显示的内容*****
106. password=dongguoh
107. url=jdbc:mysql://127.0.0.1:3306/jpetstore
108. driver=com.mysql.jdbc.Driver
109. username=root
110.
A List of JDBC Drivers
IBM DB2
jdbc:db2://
COM.ibm.db2.jdbc.app.DB2Driver
JDBC-ODBC Bridge
jdbc:odbc:
sun.jdbc.odbc.JdbcOdbcDriver
Microsoft SQL Server
jdbc:weblogic:mssqlserver4:
weblogic.jdbc.mssqlserver4.Driver
Oracle Thin
jdbc:oracle:thin:@
oracle.jdbc.driver.OracleDriver
PointBase Embedded Server
jdbc:pointbase://embedded[:
com.pointbase.jdbc.jdbcUniversalDriver
Cloudscape
jdbc:cloudscape:
COM.cloudscape.core.JDBCDriver
Cloudscape RMI
jdbc:rmi://
RmiJdbc.RJDriver
Firebird (JCA/JDBC Driver)
jdbc:firebirdsql:[//
org.firebirdsql.jdbc.FBDriver
IDS Server
jdbc:ids://
ids.sql.IDSDriver
Informix Dynamic Server
jdbc:informix-sqli://
com.informix.jdbc.IfxDriver
InstantDB (v3.13 and earlier)
jdbc:idb:
jdbc.idbDriver
InstantDB (v3.14 and later)
jdbc:idb:
org.enhydra.instantdb.jdbc.idbDriver
Interbase (InterClient Driver)
jdbc:interbase://
interbase.interclient.Driver
Hypersonic SQL (v1.2 and earlier)
jdbc:HypersonicSQL:
hSql.hDriver
Hypersonic SQL (v1.3 and later)
jdbc:HypersonicSQL:
org.hsql.jdbcDriver
Microsoft SQL Server (JTurbo Driver)
jdbc:JTurbo://
com.ashna.jturbo.driver.Driver
Microsoft SQL Server (Sprinta Driver)
jdbc:inetdae:
com.inet.tds.TdsDriver
Microsoft SQL Server 2000 (Microsoft Driver)
jdbc:microsoft:sqlserver://
com.microsoft.jdbc.sqlserver.SQLServerDriver
MySQL (MM.MySQL Driver)
jdbc:mysql://
org.gjt.mm.mysql.Driver
Oracle OCI 8i
jdbc:oracle:oci8:@
oracle.jdbc.driver.OracleDriver
Oracle OCI 9i
jdbc:oracle:oci:@
oracle.jdbc.driver.OracleDriver
PostgreSQL (v6.5 and earlier)
jdbc:postgresql://
postgresql.Driver
PostgreSQL (v7.0 and later)
jdbc:postgresql://
org.postgresql.Driver
Sybase (jConnect 4.2 and earlier)
jdbc:sybase:Tds:
com.sybase.jdbc.SybDriver
Sybase (jConnect 5.2)
jdbc:sybase:Tds:
com.sybase.jdbc2.jdbc.SybDriver
To test your driver once it's installed, try the following code:
{
Class.forName("Driver name");
Connection con = DriverManager.getConnenction("jdbcurl","username","password");
//other manipulation using jdbc commands
}
catch(Exception e)
{
}
12.10.2007
JDBC and Mysql
Preface
Purpose
The purpose of this lesson is to get you beyond the initial hurdles involved in:
- Downloading and installing a MySQL database server.
- Preparing that database for use with JDBC.
- Writing and testing your first JDBC programs to administer the database and to manipulate the data stored in the MySQL database.
What is JDBC?
JDBC technology is an API (included in both J2SE and J2EE) that provides cross-DBMS connectivity to a wide range of SQL databases and access to other tabular data sources, such as spreadsheets or flat files.
What is MySQL?
The MySQL database server is probably the world's most popular open source database software, with more than five million active installations as of September 2004.
The database server software from MySQL is available under a "dual licensing" model. Under this model, users may choose to use MySQL products under the free software/open source GNU General Public License (commonly known as the "GPL") or under a commercial license.
A powerful combination
Simply stated, JDBC makes it possible to write platform independent Java programs that can be used to manipulate the data in a wide range of SQL databases without the requirement to modify and/or recompile the Java programs when moving from platform to platform or from DBMS to DBMS.
MySQL is available for a wide variety of platforms.
Since both JDBC and MySQL are freely available for many purposes, the combination of JDBC and MySQL is a powerful combination that should be of interest for a wide variety of applications.
Viewing tip
You may find it useful to open another copy of this lesson in a separate browser window. That will make it easier for you to scroll back and forth among the different listings and figures while you are reading about them.
Supplementary material
I recommend that you also study the other lessons in my extensive collection of online Java tutorials. You will find those lessons published at Gamelan.com. However, as of the date of this writing, Gamelan doesn't maintain a consolidated index of my Java tutorial lessons, and sometimes they are difficult to locate there. You will find a consolidated index at www.DickBaldwin.com.
Preview
Download, install, and prepare the database server
It is often possible to use the same Java program to manipulate the data in a wide variety of SQL databases without a requirement to modify and/or recompile the Java program.
However, the installation and preparation procedures for different SQL databases vary widely. A large part of the battle in using JDBC with a particular database is getting the database installed and properly prepared for use with JDBC.
In this lesson, I will show you how to download, install, and prepare a MySQL database as a localhost server on a Windows platform for use with JDBC. Then I will show you how to write three simple JDBC programs to administer the database server and to manipulate data stored on the database server after it is installed.
(I will assume that you already have Java SDK v1.4.2 or later, which includes JDBC, installed on your computer.)
URLs and version numbers for downloading
I will provide specific URLs and version numbers for downloading MySQL software and documentation as of September 2004. The version numbers will certainly change over time as new versions of the software are released. Hopefully, the folks at MySQL will preserve the integrity of the URLs.
At least one URL, http://www.mysql.com/ should remain constant over time. If the other links to MySQL in this lesson become broken with time, you should revert to the main MySQL URL given above and begin your search for the software and documentation from that point.
Saving time
If you are a newcomer to the installation of database server software and the preparation of that software for use with JDBC, the information that I will provide in this lesson should save you several days of effort in pouring through documentation trying to figure out how to download, install, and tie everything together.
Even if you are experienced in these matters, this information should save you several hours of effort.
Installation and Preparation of MySQL
Getting things up and running
As a minimum, getting up and running with MySQL and JDBC involves at least the following steps:
- Download and install the appropriate release of the MySQL database server software (several different releases are available).
- Download and install the MySQL Connector/J -- for connecting to a MySQL database server from Java.
- Download and install the documentation for the MySQL database server.
- Download and install the documentation for the Connector, which is a separate documentation package from the database server documentation.
- Write and test one or more JDBC programs that will act as a database administrator, creating one or more users and possibly one or more databases on the database server. (I will show you three different ways to accomplish this.)
- Write and test a JDBC program that will log in as a user and manipulate data stored in one of those databases.
Additional MySQL software
Beyond the minimum, there are a variety of additional software packages, (such as GUI administrator packages) that can be downloaded from MySQL and installed on your computer.
Since the main thrust of this lesson has to do with JDBC rather than database administration, I won't get into that. Rather, I will show you how to use a command-line monitor program that is included with the MySQL database software to perform the minimal database administrative tasks required to satisfy the objectives of this lesson.
Documentation
I will begin with a discussion of the available documentation for both the MySQL database server and the MySQL Connector/J.
MySQL database server documentation
The MySQL Reference Manual can be downloaded from http://dev.mysql.com/doc/. In addition, there is an online searchable version of the Reference Manual available at http://dev.mysql.com/doc/mysql/en/Reference.html.
The downloadable version is available in several different formats, including:
You would probably do well to have both of these formats locally available on your computer if you have sufficient disk space.
The one-page-per-chapter formatted manual
The first format in the above list consists of a large number of HTML files. There is one HTML file for the table of contents plus about thirty-three additional files containing the text of the reference manual.
This format has a major advantage over the second format in terms of speed. It is relatively fast to click on a hyperlink in the table of contents and to see that material appear in the browser window.
There are a couple of downsides to this format, however. One downside is that this format is not very useful for searching the entire manual for keywords, (using your browser) because it is broken down into a large number of separate HTML files.
A second downside is that even though MySQL 4.0 is the recommended release in September of 2004, this manual contains information up through version 5.0.1-alpha. Sometimes information about the newer versions tends to obscure information about version 4.0.
Installing the one-page-per-chapter formatted manual
All that you need to do to install the database server documentation in this format is to download the zip file and extract the various HTML files into a folder on your disk. Then open the file named manual_toc.html in your browser to view the manual.
For convenience, I created a desktop icon that links to the table of contents file.
The all-on-one-page formatted manual
This format is very useful for searching (using your browser) because all of the text is in a single HTML file.
(There are actually two HTML files. One file contains a hyperlinked table of contents. The second file contains the text of the entire manual.)
The primary downside to this format is speed, or lack thereof. The HTML file containing the text of the manual is about four megabytes in size. On my machine, navigating this manual in a browser is a very slow process.
The downloadable version in this format also contains information up through version 5.0.1-alpha, resulting in the same disadvantage mentioned earlier.
Included in the software distribution
When you download and install the currently recommended version of MySQL, (which is version 4.0.21), the Docs folder in the installation tree structure will contain a copy of the manual in the all-on-one-page format that purports to be for version 4.0.21. Thus, you don't need to download this format separately. You will get it when you download the software.
(Even though this version purports to be for version 4.0.21, it also contains a lot of information about later versions. It may be exactly the same as the version that can be downloaded separately except that the title page is different.)
Installation of the all-on-one-page formatted manual
As mentioned above, you don't need to do anything special to install this format of the manual. It will be installed automatically when you install the MySQL 4.0.21 version of the database.
(Presumably, later versions of the software will also contain a copy of the current manual in this format.)
Once you have installed the database, the Docs folder of the installation tree will contain the files named manual_toc.html and manual.html. The first file contains a hyperlinked table of contents, and the second file contains the entire text of the manual.
Once again, for convenience, I created a desktop icon linked to the table of contents file to make it convenient to open in my browser.
Using both formats
Because I have plenty of space on my disk, I have both formats installed on my computer with an icon on the desktop for each. I occasionally open the version that contains the entire manual in a single HTML file when I need to search the entire document for something. Most of the time, however, I open and use the multi-file version due to its increased speed.
MySQL Connector/J documentation
I did not find a separate downloadable version of the connector documentation at the MySQL site. However, I did find an online version at http://dev.mysql.com/doc/connector/j/en/index.html. I was able to save the connector documentation locally by selecting the Save Page As... item on the File menu of my Netscape 7.2 browser.
(I was unable to save the page locally using Internet Explorer version 6 for some reason. However, I also discovered later that essentially the same documentation is contained in the downloadable zip file for the connector software in a file named mysql-connector-java-3.0.15-ga\docs\index.html.)
Saving the page in Netscape 7.2 resulted in a local file named index.html and an associated folder named index_files. The file contains the text of the connector documentation. The folder contains style sheets and other related material.
Installation of the connector documentation consisted simply of saving this material locally and creating a desktop icon linked to the file named index.html.
Downloading the MySQL Database Server
The download page
The main download page for both the database server and the connector as of September 2004 is http://dev.mysql.com/downloads/. Hopefully, this URL will also remain intact as MySQL releases later versions of the software.
Several different versions of the database server are available for downloading as of September 2004, including:
- MySQL 4.0 -- Generally Available (GA) release (recommended)
- MySQL 4.1 -- Gamma release (use this for new development)
- MySQL 5.0 -- Alpha release (use this for previewing and testing new features)
- MySQL 5.0.1 -- Snapshot release (use this for previewing and testing new features)
- Older releases -- older releases (only recommended for special needs)
- Snapshots -- source code snapshots of the development trees
This list can be expected to change over time as new versions of the database server are released. Thus, the links in the above list will become obsolete. When that happens, you should revert back to the download page at http://dev.mysql.com/downloads/ and download the version that best suits your needs at that time.
The different versions of the database server
As of September 2004, the database server documentation has this to say about these different versions:
- MySQL 5.0 is the newest development release series and is under very active development for new features. Alpha releases have been issued to allow more widespread testing.
- MySQL 4.1 is in gamma status, soon moving to production status.
- MySQL 4.0 is the current stable (production-quality) release series. New releases are issued for bugfixes. No new features are added that could diminish the code stability.
- MySQL 3.23 is the old stable (production-quality) release series. This series is retired, so new releases are issued only to fix critical bugs.
I elected MySQL 4.0
I elected to download MySQL 4.0 since it is the stable production quality version as of September 2004. This resulted in the downloading of a distribution file named mysql-4.0.21-win.zip.
(The distribution file name is likely to be different for future versions of the MySQL database server.)
Installing MySQL Database Server
Installation instructions
Installation instructions for the database server are provided in the database server documentation, Section 2, entitled Installing MySQL.
Since I was installing on Windows XP and had no desire to deal with source code, I quickly skipped down to Section 2.2.1.2 entitled Installing a Windows Binary Distribution.
In my case, installation was easy
Because I did not have an earlier version of MySQL installed and I was logged onto Windows as an administrator, all that I needed to do was to execute the following instructions from the database server documentation to install the MySQL database server on my computer.
- Unzip the distribution file to a temporary directory.
- Run the setup.exe program to begin the installation process. If you want to install MySQL into a location other than the default directory (`C:\mysql'), use the Browse button to specify your preferred directory. If you do not install MySQL into the default location, you will need to specify the location whenever you start the server. The easiest way to do this is to use an option file, as described in Section 2.2.1.3 Preparing the Windows MySQL Environment.
Because I didn't want to deal with option files, I elected to allow the software to be installed in the default directory, C:\mysql.
Testing the installation
After completing the installation, I performed some of the procedures shown in the database server documentation, Section 2.4.1 entitled Windows Post-Installation Procedures. Although I didn't get exactly the same results as those shown in the documentation, my results were close enough to convince me that the MySQL database server was correctly installed on my computer.
(The reason that I didn't get exactly the same results was that I didn't log in with administrator privileges.)
Not installed as a Windows service
Section 2.2.1.7 of the database server documentation entitled Starting MySQL as a Windows Service contains the following:
"On the NT family (Windows NT, 2000, or XP), the recommended way to run MySQL is to install it as a Windows service. Then Windows starts and stops the MySQL server automatically when Windows starts and stops."
I have no desire for the MySQL database server to start running every time I start Windows running. I already waste enough time waiting for Windows XP to become ready for use on my laptop each time I start it.
Therefore, I did not install the database server as a service. I will explain how I manually start and stop the database server whenever I need to use it later in this lesson.
Downloading MySQL Connector/J
What is MySQL Connector/J?
For those who don't know, let me begin by explaining the purpose of MySQL Connector/J.
The JDBC API is designed to make it possible for you to write a single Java program and to use it to manipulate the data in a variety of different SQL database servers without a requirement to modify and/or recompile the program. In order to do this, it is necessary for you to:
- Inform the Java program as to the URL of the database server. You can accomplish this with input data when you start the program.
- Provide the Java program with a programming interface to the specific database server that you intend to use. Assuming that the programming interface has been installed on your computer, you can also accomplish this with input data when you run the program.
The programming interface
The programming interface deals with the interface peculiarities of the different database servers.
Sun refers to the process of providing this information to the program as registering the database server with the Java program. You will see how this is done in the sample programs later in this lesson.
The connector download page
The download page for MySQL Connector/J is http://dev.mysql.com/downloads/index.html. As of September 2004, the following versions are available for downloading from this page:
- MySQL Connector/J 3.1 -- development release
- MySQL Connector/J 3.0 -- production release
- Older releases -- older releases (only recommended for special needs)
- Snapshots -- source code snapshots of the development trees
As with the MySQL database server software, these individual links are likely to become obsolete as new versions of the software are released. Hopefully the link to http://dev.mysql.com/downloads/index.html will remain intact.
The MySQL Connector/J 3.0 distribution file
Because I was very interested in stability, I elected to download and install MySQL Connector/J 3.0, identified above as the production release. This resulted in the download of a file named mysql-connector-java-3.0.15-ga.zip.
This zip file encapsulates 194 individual files in different folders including source code files, class files, pdf files, xml files, jar files, license files, files with no extensions, a manifest file, HTML files, and other file types not listed here.
The zip file also contains several java programs in a folder named testsuite that can be used to test your installation. You may find them useful for that purpose. In addition, these programs illustrate a variety of database operations using JDBC, so you may find them useful as example programs as well.
Fortunately, as I will explain below, all but one of these files can be ignored insofar as installation of the connector software is concerned.
Installing MySQL Connector/J
General installation instructions
The following statement appears in the connector documentation Section 2.2.1. entitled Setting the CLASSPATH (For Standalone Use).
"Once you have un-archived the distribution archive, you can install the driver in one of two ways: Either copy the "com" and "org" subdirectories and all of their contents to anywhere you like, and put the directory holding the "com" and "org" subdirectories in your classpath, or put mysql-connector-java-[version]-bin.jar in your classpath, either by adding the FULL path to it to your CLASSPATH environment variable, or by copying the .jar file to $JAVA_HOME/jre/lib/ext."
My installation
Actually, the above quotation describes three options instead of just two. To make a long story short, I elected the third option. I extracted the jar file named mysql-connector-java-3.0.15-ga-bin.jar from the zip file and copied it into the folder named c:\j2sdk1.4.2\jre\lib\ext, which is the installation directory tree for the currently installed version of Java on my computer.
The advantage of doing it this way was that I didn't have to modify the classpath environment variable. The disadvantage is that the next time I upgrade to a new version of Java, I must remember to save the MySQL connector jar file and copy it into the directory tree for my new Java installation.
Your installation
If you prefer the first option, the connector documentation contains a wealth of information to help you perform the necessary steps to modify the classpath, etc.
Testing the installation
I didn't use any of the test programs mentioned above in the folder named testsuite. Rather, I tested my installation using JDBC programs that I had developed earlier using a different SQL database server.
You can use the test programs mentioned earlier in the testsuite folder to test your installation. Also, I will provide and explain three sample JDBC programs later in this lesson that you can use to test your installation. Before you can test the installation, however, you must start the MySQL database server running.
Starting the database server
At this point, all of the software necessary to use the database server in a JDBC program should be installed on your computer ready for use. The next step is to confirm that you can start the database server running.
Selecting a Windows server
I found it necessary to pull together several pieces of information from the database server documentation to determine the best way to start the server from a command line. For example, the following table is found in the database server documentation, Section 2.2.1.4 entitled Selecting a Windows Server.
| Binary | Description |
| mysqld | Compiled with full debugging and automatic memory allocation checking, symbolic links, and InnoDB and BDB tables. |
| mysqld-opt | Optimized binary. From version 4.0 on, InnoDB is enabled. Before 4.0, this server includes no transactional table support. |
| mysqld-nt | Optimized binary for Windows NT, 2000, and XP with support for named pipes. |
| mysqld-max | Optimized binary with support for symbolic links, and InnoDB and BDB tables. |
| mysqld-max-nt | Like mysqld-max, but compiled with support for named pipes. |
Explaining the different types of servers
The following explanation follows the table:
"We have found that the server with the most generic name (mysqld) is the one that many users are likely to choose by default. However, that is also the server that results in the highest memory and CPU use due to the inclusion of full debugging support. The server named mysqld-opt is a better general-use server choice to make instead if you don't need debugging support and don't want the maximal feature set offered by the -max servers or named pipe support offered by the -nt servers."
Changes in MySQL 4.1.2
This is followed by another explanation indicating that beginning with MySQL 4.1.2, the server names were changed eliminating the server name mysqld-opt and replacing the debug version (mysqld) with mysqld-debug. Therefore, if you are installing MySQL 4.1.2 or a later version, you should use the syntax mysqld instead of mysqld-opt to start the server running from an optimized binary file.
Starting MySQL 4.0.21
Since I am running MySQL 4.0.21 and need to make certain that what I am doing is compatible with a large number of students having different operating systems, I concluded that I should start the server running by using the syntax mysqld-opt.
Section 2.2.1.5 of the database server documentation entitled Starting the Server for the First Time indicates that the following command should be used at the command prompt to start the server running:
C:\mysql\bin\mysqld --console
As I understand it, the purpose of --console is to cause error messages to be displayed on the standard error device (typically the screen) rather than to be entered into an error log file. This is what I want to happen.
Combining the two pieces of information given above, I concluded that I should start the MySQL database server by entering the following command at a command prompt:
C:\mysql\bin\mysqld-opt --console
Encapsulated in a batch file
Therefore, I created a batch file named MySqlStart.bat and linked that file to an icon on the desktop for convenience. The batch file contains the two commands shown in Listing 1 and repeated later in Listing 30 near the end of the lesson.
C:\mysql\bin\mysqld-opt --console |
The startup screen output
Figure 1 shows the screen output following the execution of the batch file named MySqlStart.bat.
(Note that it was necessary for me to manually enter a line break ahead of the word port to cause the screen output to fit in this narrow publication format.)
C:\mysql>C:\mysql\bin\mysqld-opt --console Figure 1 MySQL database server startup sequence |
The process window shown in Figure 1 remains open and active until the server is stopped. It should be possible to connect to the server using JDBC during this period.
Stopping the database server
It is probably a good idea to shut down the server before shutting down the computer. Section 2.2.1.6 of the database server documentation entitled Starting MySQL from the Windows Command Line states that you can stop the MySQL server by executing the following command:
C:\mysql\bin\mysqladmin -u root shutdown
Therefore, I created a batch file named MySqlStop.bat and linked that file to an icon on my desktop to make it convenient to stop the server. The batch file contains the two commands shown in Listing 2 and repeated in Listing 31 near the end of the lesson.
C:\mysql\bin\mysqladmin -u root shutdown |
The screen output at server shutdown
Figure 2 shows the screen output in the server process window when the file named MySqlStop.bat is executed.
040918 14:00:02 C:\mysql\bin\mysqld-opt: Figure 2 Screen output when MySQL server is |
Once the server is shut down, attempts to connect to the server from JDBC will fail.(As before, it was necessary for me to manually enter line breaks in Figure 2 to cause the screen output to fit in this narrow publication format.)
Creating a New Database using the Monitor Program
Now that you know how to start the MySQL database server, it's time to learn how to:
- Create a database that can be manipulated using JDBC in a Java program.
- Create a new user having the necessary privileges to manipulate the database using JDBC in a Java program.
Three different approaches
I'm going to show you three different ways to accomplish this:
- Using a command-line program named mysql coupled with manual data entry at runtime. (I will refer to this as the monitor program for reasons that will become self-evident later.)
- Using the monitor program coupled with data input derived from a text file.
- Using JDBC in a Java program.
I will illustrate the first approach in this and the later section entitled Creating a New User using the Monitor Program .
I will illustrate the second approach in the section entitled Administering the Database Server using Text Files.
I will illustrate the third approach in the section entitled Discussion and Sample Code, which shows how to use JDBC to manage and manipulate the database server.
The monitor program and manual data entry
The monitor program is named mysql.exe. It is located in c:\mysql\bin. This program makes it possible to log onto the database server and to enter commands at the command line to:
- Create databases
- Add new users
- Modify databases
- Perform ad-hoc queries, etc.
(Unless you really enjoy typing, the monitor program is not a lot of fun to use.)
To really learn MySQL ...
In order to really learn how to use MySQL, you will need to study the MySQL database server documentation in detail and probably some good SQL books as well.
The purpose of this lesson is to teach you just enough to get you started. When you finish this lesson, you should be able to successfully write and execute simple JDBC programs that will manipulate database tables on the MySQL database server.
The default administrative user
As I understand it, when the MySQL database server is first installed, there is a default user named root with full administrative privileges and no password. At this point, the server is totally wide open and insecure.
(The server documentation provides various suggestions as to what you should do to add security to the server.)
The user named root has the ability to create new databases as well as to create new users and to register those users on the databases.
Existing databases at MySQL installation
Also, as I understand it, there are two existing databases on the server when it is first installed. There is a database named test, which is wide open with no password requirements. Any user can access this database.
There is also a database named mysql, which is apparently used to keep track of things such as databases, users, etc. I believe that this database is accessible only by users having administrative privileges.
Adding a new database using the monitor program
The first step in adding a new database using the monitor program is to log onto the database server as the administrative user named root with access to the database named mysql. Until a password is assigned to the root user, login can be accomplished by entering the following command at the command prompt:
c:\mysql\bin\mysql --user=root mysql
(In case it isn't clear on your display, the word user is preceded by two minus sign characters.)
The screen output
Assuming that the MySQL database server is running, the screen output produced by entering this command is shown in Figure 3.
(Note that in Figure 3, and several of the figures that follow, it was necessary for me to manually enter line breaks in the screen output to force the material to fit in this narrow publication format.)
C:\jnk>c:\mysql\bin\mysql --user=root mysql Figure 3 Monitor output for administrator login. |
The monitor program
Note that this program refers to itself as the MySQL monitor. That explains why I refer to it as the monitor or the monitor program.
The purpose of this program is to make it possible for you to enter SQL database commands from the keyboard. Note in particular the prompt shown in boldface at the end of Figure 3, which reads:
mysql>
This is not a command-line prompt, which typically looks something like:
C:\jnk>
Rather, this is a program-generated prompt where the monitor program is requesting input from the user.
SQL command terminators
As indicated in Figure 3, SQL commands end with either a semicolon character or \g (note the difference in typeface for the character g in this text relative to that shown in Figure 3).
SQL commands are often quite long. You can enter successive portions of SQL commands at successive program prompts.
(Later, we will see that the monitor program uses a different syntax for continuation prompts.)
It is not until you enter a semicolon character or a \g that the program responds to and attempts to execute the entire SQL command.
Terminating the monitor program
You can terminate the monitor program by entering a \q at the program prompt.
(Note that this is a q as in quit and is not a g as in good.)
Creating a new database named JunkDB
Figure 4 shows the screen output for the use of the monitor program by the user named root to create a new database named JunkDB and then to terminate the monitor program.
C:\jnk>c:\mysql\bin\mysql --user=root mysql Figure 4 Creating database named JunkDB |
The new material in Figure 4 is shown in boldface in the bottom half of the figure. The material in the top half of Figure 4 is a repeat of the material shown in Figure 3.
Using a batch file and a text file
Shortly, I will show you how to create a new database using a Windows batch file and an associated text file. Later on, I will show you how to create a new database using a Java JDBC program.
Creating a New User with the Monitor Program
Figure 5 shows the screen output for the use of the monitor program by the user named root to add a new user named auser.
The new material is shown in boldface in the lower half of the figure.
C:\jnk>c:\mysql\bin\mysql --user=root mysql Figure 5 Adding new user named auser. |
A longer SQL command
This SQL command is much longer than the command used to create the new database. This command requires several continuation lines to complete to prevent it from exceeding the screen width.
(Note the difference in the syntax of new program prompts and continuation program prompts. The prompts that look like an arrow are the continuation prompts.)
I won't try to explain the SQL command is detail. I will simply refer you to the MySQL database documentation and a good SQL book for that purpose. However, the SQL command is relatively self explanatory.
The meaning of the SQL command
This SQL command grants a list of six different privileges on the database named JunkDB to a user named auser who will be accessing the database from localhost.
(Granting access to the same user from a different machine on the network would require a different syntax.)
The user named auser will be allowed to access the database named JunkDB using the password drowssap, (which is password spelled backwards to make it easy for me to remember).
Administering the Database Server using Text Files
As mentioned earlier, unless you really enjoy typing, using the monitor program in manual data entry mode is not a fun way to work with the database. For example, if you make a typing error, you must go back and retype the entire command from the beginning.
Fortunately, there is a better approach. That approach is to provide the commands to the monitor program using a text file as input. Then if you make an error, you can simply edit the text file and rerun the process.
How does it work?
To make a long story short, you start the monitor program by redirecting the input so that the input is derived from a text file instead of from the keyboard. This process is described in the database server documentation, Section 3.5 entitled Using mysql in Batch Mode.
Creating a new database
There are probably several ways to set this process up. I elected to use a combination of a batch file and a text file. The batch file starts the monitor program, logging in as root, and redirects input to the associated text file.
For example, the files used to create a new database named JunkDB are shown in Listing 32 and Listing 33 near the end of the lesson.
Making a new user
The files used to make a new user named auser are shown in Listing 34 and Listing 35.
Compare the contents of these two files with the manual data entry shown earlier in Figure 5. The new user is granted six different privileges on the database named JunkDB from localhost with a password of drowssap.
The files used to remove the user named auser are shown in Listing 36 and Listing 37. The procedure for revoking a user's privileges and removing the user is explained near the end of Section 14.5.1.1 entitled DROP USER Syntax in the database server documentation.
Deleting the database named JunkDB
The files used to delete the database named JunkDB are shown in Listing 38 and Listing 39. The procedure for deleting a database is explained in the database server documentation, Section 14.2.8 entitled DROP DATABASE Syntax.
Discussion and Sample Code for JDBC Programs
Three separate programs
I will explain three programs. The first program, named Jdbc11 shows how to:
- Log onto the server as the administrator named root.
- Create a new database named JunkDB.
- Register a new user named auser on the database named JunkDB with six different privileges and a password of drowssap.
The second program named Jdbc12 shows how to:
- Log onto the server as the administrator named root.
- Revoke the privileges of and remove the user named auser.
- Delete the database named JunkDB.
The third program named Jdbc10 shows how to log onto the server as the user named auser and to manipulate the database named JunkDB in a variety of ways.
I will break each of these programs down into fragments and discuss the fragments. Complete listings of all the programs are shown in Listings 40, 41, and 42 near the end of the lesson.
Jdbc11 - Create a database and make a new user
The purpose of the program named Jdbc11 is to log onto the master database named mysql as the default administrator named root whose password is blank in order to perform the following updates on the MySQL database server:
- Create a new database named JunkDB.
- Create a new user named auser with a password of drowssap with six different privileges on the database named JunkDB.
The output, or lack thereof
These two operations produce no visible output when successful. However, they produce error messages in the output when unsuccessful.
(Note, however, that print statements in the program produce several lines of output that are independent of the operations being performed on the database server.)
Server must be running
The MySQL server must be running on localhost before this program is started. Instructions for starting and stopping the database server were provided earlier (see Listing 1, Figure 1, Listing 2, and Figure 2).
The program was tested using Java SDK 1.4.2 under WinXP, MySQL version 4.0.21-win, and JDBC connector version mysql-connector-java-3.0.15-ga.
There are five critical steps in using JDBC to manipulate a database:
- Load and register the JDBC driver classes (programming interface) for the database server that you intend to use.
- Get a Connection object that represents a connection to the database server (analogous to logging onto the server).
- Get one or more Statement objects for use in manipulating the database.
- Use the Statement objects to manipulate the database.
- Close the connection to the database.
I will highlight these five steps in the discussion of the sample program that follows.
Beginning of class definition for Jdbc11
The first program fragment for the program named Jdbc11 is shown in Listing 3. The entire program is shown in Listing 40 near the end of the lesson.
public class Jdbc11 { |
The code in Listing 3 is straightforward, showing the beginning of the class, the beginning of the main method, and a print statement.
Listing 3 also declares a local variable of type Statement. I will have more to say about the Statement interface later.
Register the JDBC driver for MySQL
Listing 4 shows the statement that implements the first critical step listed earlier (load and register the JDBC driver classes). This statement registers the MySQL driver classes with the Java program, making it possible for this program to manipulate data on the MySQL server.
Class.forName("com.mysql.jdbc.Driver"); |
Reference to the driver class
The following statement appears in the MySQL Connector documentation, Section 2.2.1. entitled Setting the CLASSPATH (For Standalone Use).
"If you are going to use the driver with the JDBC DriverManager, you would use "com.mysql.jdbc.Driver" as the class that implements java.sql.Driver."
This information is also provided in the connector documentation in Section 2.2.2. entitled Driver Class Name and JDBC URL Format.
The Driver interface
Note the reference to the Driver interface in the above quotation. Here is some of what Sun has to say about the Driver interface:
"The interface that every driver class must implement.
The Java SQL framework allows for multiple database drivers.
Each driver should supply a class that implements the Driver interface.
The DriverManager will try to load as many drivers as it can find and then for any given connection request, it will ask each driver in turn to try to connect to the target URL. ...
When a Driver class is loaded, it should create an instance of itself and register it with the DriverManager. This means that a user can load and register a driver by calling
Class.forName("foo.bah.Driver")"
The name of the driver class
In order to use a JDBC program with a specific database server, you must obtain the name of this critical driver class from the database vendor (or from some third party that supports the database server). You must then cause your program to load the class.
This is the class that connects the other classes in the connector package to the Java program. Without it, the Java program would be unable to communicate successfully with the database server.
Loading the driver class
The statement in Listing 4 causes this class to be loaded as described in the Sun documentation quoted above.
(If you are unfamiliar with the use of the forName method of the class named Class, see The Essence of OOP using Java: Static Members for a brief introduction to the class named Class. Then open your Google search engine, set the number of results to 100, and search for all of the keywords java forname richard baldwin. This should point you to several previous lessons that I have published that discuss this topic. If you don't find what you need there, click on the link on the bottom of the last Google page that reads repeat the search with the omitted results included to see even more lessons.)
Specification of Driver class as a String
There are several alternative ways to register the Driver class, only one of which is shown in Listing 4. The statement in Listing 4 makes it possible to specify the Driver class as a String. The primary advantage of this approach is that this string can be obtained by the program in a variety of ways at runtime.
Although the string was hard coded into this simple program, that is not a requirement. For a more general program intended to be used with two or more database servers, this string would most likely be provided as some form of user input.
The URL of the database server
MySQL and other similar database engines behave as servers on a network. They are identified by a URL much as other types of servers (such as HTTP servers and FTP servers) are identified. The next fragment defines the URL for the MySQL database server that I used in this sample program.
The code in Listing 5 defines the URL of the master database named mysql on the MySQL database server residing on localhost and servicing the default port number 3306.
(I could have omitted the default port number from the URL, but I decided to include it to remind me to mention it. Note that the "//" characters shown to the right of "mysql:" form part of the URL. They are not comment indicators.)
String url = |
The URL format is provided in the connector documentation in Section 2.2.2. entitled Driver Class Name and JDBC URL Format. The URL format contains several optional elements. This URL will be referenced in the statement in Listing 6 for the purpose of getting a connection to the database.
Get a connection to the database
The code in Listing 6 implements the second critical step listed earlier (get a connection object).
Listing 6 gets a connection to the database at the specified URL (mysql on localhost port 3306) for a user named root with a blank password. As you are already aware, this user is the default administrator having full privileges to do anything, including creating new databases and registering new users on those databases.
Connection con = |
In effect, Listing 6 logs the JDBC program onto the MySQL database server in a manner that is analogous to the first line in Figures 3, 4, and 5 as well as the statements in the batch files shown in Listings 32, 34, 36, and 38.
The getConnection method
The getConnection method is a static method of the DriverManager class. When getConnection is invoked, the DriverManager will attempt to locate a suitable driver from among those loaded at initialization and those loaded explicitly using the same classloader as the current applet or application.
There are several overloaded versions of the getConnection method. The version used in Listing 6 attempts to establish a connection to the given database URL for a specific user with a specific password.
If the attempt to get a connection to the database server is successful, the method returns an object of type Connection. In this program, a reference to the Connection object is stored in the reference variable named con.
If the attempt is not successful, an exception of type SQLException will be thrown. Information pertinent to the nature of the problem will be encapsulated in the SQLException object.
As you will see later, SQL statements are executed and results are returned within the context of a connection.
Display some information
The code in Listing 7 is not critical to the program. This code simply displays information about the URL and the connection.
System.out.println("URL: " + url); |
Get a Statement object
The code in Listing 8 implements the third critical step listed earlier (get one or more Statement objects).
This code invokes the createStatement method of the Connection interface to get an object of type Statement.
stmt = con.createStatement(); |
Recall that con is a reference to an object of type Connection. A Connection object defines a connection (session) with a specific database. SQL statements are executed and results are returned within the context of a connection.
According to Sun, a Statement object is:
"... used for executing a static SQL statement and returning the results it produces."
The results that are returned
The results, if any, are returned in the form of a ResultSet object. I will have more to say about the ResultSet interface later in conjunction with the discussion of the program named Jdbc10.
(The SQL commands used in this program don't return any results.)
Methods of the Statement interface
The Statement interface declares many methods that can be used to access the database server and to manipulate the data in the database. One of those methods is executeUpdate, which will be used in this program.
The executeUpdate method has a single String parameter. This parameter must be a valid SQL command. The method is used to execute SQL INSERT, UPDATE or DELETE statements. In addition, other SQL statements that return nothing can be executed using this method.
Create the new database
The code in Listing 9 implements the fourth critical step in the list provided earlier, (use of a Statement object to manipulate the database).
Listing 9 invokes the executeUpdate method on the Statement object to create the new database named JunkDB.
stmt.executeUpdate( |
The executeUpdate method
Sun has this to say about the executeUpdate method:
"public int executeUpdate(String sql) throws SQLException
Executes the given SQL statement, ...
Parameters: sql - an SQL INSERT, UPDATE or DELETE statement or an SQL statement that returns nothing
Returns: either the row count for INSERT, UPDATE or DELETE statements, or 0 for SQL statements that return nothing."
The method parameter is an SQL command
Note the similarity of the method parameter in Listing 9 to the interactive input shown in Figure 4 and the contents of the text file shown in Listing 33.
In all three cases, an SQL command is invoked on the database server to cause a new database named JunkDB to be created. This SQL command returns nothing, so it is suitable for use with the executeUpdate method described above.
In all three cases, the SQL command is invoked by the default administrator named root who has the ability to create new databases.
Make a new user
The code in Listing 10 also implements the fourth critical step listed earlier, (use of a Statement object to manipulate the database).
The code in Listing 10 invokes the executeUpdate method once again to make a new user named auser who is capable of accessing the database named JunkDB from localhost using the password drowssap with six different privileges.
stmt.executeUpdate( |
Another SQL command
Once again, note the similarity of the executeUpdate method parameter in Listing 10 to the interactive input shown in Figure 5 and to the contents of the text file shown in Listing 35.
Figure 5, Listing 10, and Listing 35 illustrate three different ways for the default administrator named root to invoke the same SQL command on the database server.
Close the connection and terminate the program
Listing 11 implements the fifth critical step listed earlier, closing the connection and terminating the program.
con.close(); |
The results of running the program named Jdbc11
Once this program has been run successfully, the MySQL database server contains a database named JunkDB, as well as a user named auser, having various privileges relative to that database with a password of drowssap.
At this point, it is possible to execute JDBC programs by which the user named auser manipulates the contents of the database named JunkDB. That will be the purpose of the program named Jdbc10, which I will explain later.
First, however, I am going to show you how to write a JDBC program to remove the user named auser and to delete the database named JunkDB from the MySQL database server.
JDBC12 - Remove a user and delete a database
The purpose of the program named Jdbc12 is to log onto the master database named mysql as the default administrator named root whose password is blank in order to perform the following updates on the MySQL database server:
- Revoke the privileges of and remove a user named auser.
- Delete a database named JunkDB.
The output
These two operations produce no visible output when successful. However, they produce error messages in the output when unsuccessful.
(Print statements in the program do produce output that is unrelated to the operations listed above.)
A reversal
This program is the reverse of the program named Jdbc11, discussed earlier, which creates the database named JunkDB and registers the user named auser on that database.
Server must be running
The MySQL server must be running on localhost before this program is started. Instructions for starting and stopping the database server were provided earlier (see Listing 1, Figure 1, Listing 2, and Figure 2).
Testing
This program was tested using Java SDK 1.4.2 under WinXP, MySQL version 4.0.21-win, and JDBC connector version mysql-connector-java-3.0.15-ga.
Beginning of the Jdbc12 class
Listing 12 shows the beginning of the class definition and the beginning of the main method for the program named Jdbc12. A complete listing of the program is provided in Listing 41
The code in Listing 12 is the same as the code discussed earlier for the program named Jdbc11. Therefore, I won't repeat that discussion.
public class Jdbc12 { |
Revoke privileges and remove user named auser
Listing 13 invokes the executeUpdate method four times in succession to cause the database server to revoke privileges for and to remove the user named auser. The SQL commands required to accomplish this were discussed earlier and illustrated in Listings 36 and 37.
stmt.executeUpdate( |
Delete the database named JunkDB
Listing 14 invokes the executeUpdate method to delete the database named JunkDB, using an SQL command discussed earlier and illustrated in Listings 38 and 39.
stmt.executeUpdate( |
Closing the connection and terminating the program
Listing 15 closes the connection and terminates the program.
con.close(); |
Jdbc10 - Manipulating the data in the database
The behavior of each of the two previous programs has been in the nature of administering or managing the database server. In particular, those programs added and removed databases and users from the database server.
The user perspective
The purpose of the program named Jdbc10 is to illustrate the ability to use JDBC to access a MySQL database server on localhost and to manipulate the data stored in that database.
Server must be running
The MySQL server must be running on localhost before the program named Jdbc10 is started. Instructions for starting and stopping the MySQL database server were provided earlier (see Listing 1, Figure 1, Listing 2, and Figure 2).
Server must be prepared
In addition, a database named JunkDB must have been created on the server and a user named auser must have been registered on that database with a password of drowssap before this program is started.
The user named auser must have privileges that allow for the creation of tables in the database and the insertion of data into the tables. In addition the user must be allowed to perform SELECT queries on the tables in the database.
Creating the database and adding the user
The database may have been created and the user may have been added in at least three different ways:
- Performing manual data entry with the monitor program as illustrated in Figures 4 and 5.
- Using the monitor program coupled with data input derived from a text file as illustrated in Listings 32, 33, 34, and 35.
- Running the program named Jdbc11 discussed earlier.
Behavior of Jdbc10 program
This program:
- Logs in as auser with a password of drowssap.
- Accesses the database named JunkDB.
- Creates a table named myTable.
- Puts five rows of data into the table named myTable.
- Accesses the data in the table named myTable.
- Displays the data.
- Deletes the table named myTable.
Two different approaches are used to display the contents of the table. The first approach displays all of the data in the table. The second approach displays only the data in a specific row in the table.
A precaution
As a precaution, before attempting to create the new table, the program attempts to delete a table having the same name. If a table having the same name already exists as residue from a previous run, it is deleted.
If it doesn't already exist when the attempt is made to delete it, an exception is thrown. This exception is caught, displayed, and ignored.
The program output
This program produces the output shown in Figure 6 under normal conditions where the table named myTable does not exist when the program is started (the specifics regarding the Connection object may vary from one run to the next).
Copyright 2004, R.G.Baldwin Figure 6 |
Testing
This program was tested using Java SDK 1.4.2 under WinXP, MySQL version 4.0.21-win, and JDBC connector version mysql-connector-java-3.0.15-ga.
Beginning of the class definition
The class definition begins in Listing 16. A complete listing of the program is shown in Listing 42 near the end of the lesson.
public class Jdbc10 { |
Except for the declaration of a variable of type ResultSet, the code in Listing 16 is the same as the code in the two previous programs. I will have more to say about the ResultSet type later.
Define the database URL
Listing 17 defines the URL of the database server and the database that will be accessed later by this program.
String url = |
Note that the previous two programs accessed the master database named mysql, whereas this program accesses the user database named JunkDB. Otherwise, the code in Listing 17 is the same as in the two previous programs.
Get a connection to the database
Listing 18 gets a connection to the database.
Connection con = |
Recall that the code in Listing 18 is analogous to a specific user logging onto a specific database using a specific password.
The previous two programs got a connection to the master database named mysql for the default administrator named root whose password was blank.
This program gets a connection to the database named JunkDB for the user named auser whose password is drowssap.
Display some information and get a statement object
As in the previous two programs, Listing 19 displays some information about the URL and the connection, and then gets a Statement object.
//Display URL and connection information |
Delete the table named myTable if it exists
A table named myTable may already exist in the database named JunkDB for some reason such as the premature ending of a previous run of this program. If the table already exists, it will not be possible to create a new empty table having that name. A requirement of this program is to create an empty table named myTable in a particular format.
Therefore, as a precaution, before attempting to create the new table, the code in Listing 20 attempts to delete a table named myTable. If a table having that name already exists, it is deleted.
If it doesn't already exist when the attempt is made to delete it, an exception is thrown. This exception is simply caught, displayed, and ignored.
try{ |
The code in Listing 20 invokes the executeUpdate method, with which you are already familiar. Only the syntax of the SQL command provided as a parameter to the method is new to this program.
Create the new table named myTable
The code in Listing 22 invokes the executeUpdate method to create a new table named myTable. I will refer you to an SQL book for a full understanding of the SQL command that is passed as a parameter to the method.
stmt.executeUpdate( |
Briefly, the new table will have two columns. The first column will be named test_id and will be designed to contain integer data.
The second column will be named test_val and will be designed to contain character data up to 15 characters in length (in Java, we might refer to that as String data, except that there is no limit to the length of a String in Java).
Insert some values into the table
Listing 23 invokes the executeUpdate method to insert one value into each column of the first row in the table.
stmt.executeUpdate( |
The integer value 1 is inserted into the column named test_id. The three characters, One, are inserted into the column named test_val.
Insert values into four more rows
Listing 24 invokes the executeUpdate method four more times in succession to insert values into the columns in rows 2 through 5. You should be able to examine the SQL commands and determine the values inserted into the table.
stmt.executeUpdate( |
The table is populated
At this point, the first five rows in the table named myTable have been populated with data. The remaining code in the program will:
- Access and display all of the data in the table.
- Display the data in a specific row in the table.
- Delete the table from the database.
Get another Statement object
The code in Listing 25 gets a different Statement object, initialized as shown by the parameters that are passed to the createStatement method.
stmt = con.createStatement( |
I won't try to explain the meaning of the initialization parameters. Rather, I will let you look them up in the Sun documentation for the ResultSet interface, and then perhaps do further research in an SQL book.
The ResultSet interface provides about ten symbolic constants that can be used as parameters to this method. The values of the parameters exercise control over the behavior of the ResultSet object returned by a subsequent query based on the Statement object.
Query the database
All of the database operations to this point have been based on the use of the executeUpdate method of the Statement interface. Recall that I told you earlier that the executeUpdate method can be used to execute SQL commands that don't return anything.
We have now reached the point where we want to execute an SQL command that does return something. For this, we will invoke the executeQuery method on the Statement object.
What does Sun have to say?
Here is part of what Sun has to say about this method:
"public ResultSet executeQuery(String sql) throws SQLException
Executes the given SQL statement, which returns a single ResultSet object.
Parameters: sql - an SQL statement to be sent to the database, typically a static SQL SELECT statement
Returns: a ResultSet object that contains the data produced by the given query; never null"
In other words, this method will execute the SQL command on the database and encapsulate the returned values in an object of type ResultSet.
A ResultSet object
A ResultSet object provides access to an encapsulated table of data. The object maintains a cursor pointing to its current row of data. Initially the cursor is positioned before the first row. The next method moves the cursor to the next row (similar to an iterator or an enumerator in Java).
Once the results are encapsulated in a ResultSet object, the ResultSet interface provides several methods that can be used to extract the information from the object.
Methods of a ResultSet object
The get methods (such as getString) retrieve column values for the current row. You can retrieve values using either the index number of the column or the name of the column. I have read that using the column index is more efficient but I can't give you a reference on that.
(Columns are numbered beginning with 1, not with 0.)
For the get methods, the JDBC driver attempts to convert the underlying data to the specified Java type and returns a suitable Java value.
The life of a ResultSet object
A ResultSet object is automatically closed by the Statement object that generated it when that Statement object is closed, re-executed, or used to retrieve the next result from a sequence of multiple results.
Perform the query
Listing 26 invokes the executeQuery method to perform the query on the database selecting all columns in all rows, and sorting the results in order by the values in the column named test_id. The results are encapsulated in the ResultSet object referred to by the reference variable named rs.
rs = stmt.executeQuery("SELECT * " + |
Display all of the results in the ResultSet object
Listing 27 uses a while loop to:
- Iterate on the ResultSet object one row at a time.
- Invoke the getInt method to get and save the value in the column named test_id for each row.
- Invoke the getString method to get and save the value in the column named test_val for each row.
- Display the two values on a new output line on the screen.
System.out.println("Display all results:"); |
The output
The output produced by the code in Listing 27 is shown in Figure 7.
Display all results: Figure 7 |
Display the data in row number 2
The code in Listing 28 invokes the absolute method on the same ResultSet object to get, save, and display the data in the two columns of row number 2.
System.out.println( |
(Note that row numbers begin with 1 and do not begin with 0 as would be the case in most Java contexts. Although not demonstrated here, the same is true for column numbers. Apparently this is the norm in database work.)
The output
Figure 8 shows the output produced by the code in Listing 28.
Display row number 2: Figure 8 |
Delete the table, close the connection, and terminate
Listing 29 invokes the executeUpdate method to delete the table named myTable from the database named JunkDB.
stmt.executeUpdate("DROP TABLE myTable"); |
Then Listing 29 closes the connection (logs off the database) and terminates the program.
Run the Programs
I encourage you to download and install the MySQL database server and the MySQL connector as described in this lesson.
Then copy the code from the listings near the end of this lesson. Execute the batch files. Compile and execute the programs. Experiment with the files and the programs, making changes, and observing the results of your changes.
Summary
I showed you how to download, install, and prepare a MySQL database as a localhost server on a Windows platform for use with JDBC.
Then I showed you how to write three simple JDBC programs to administer the database server and to manipulate data stored in a MySQL database.
Complete Program Listings
C:\mysql\bin\mysqld-opt --console |
Listing 30 Contents of MySqlStart.bat
C:\mysql\bin\mysqladmin -u root shutdown |
Listing 31 Contents of MySqlStop.bat
c:\mysql\bin\mysql --user=root mysql < MySqlCreateDatabase01.txt |
Listing 32 MySqlCreateDatabase01.bat
CREATE DATABASE JunkDB; |
Listing 33 MySqlCreateDatabase01.txt
c:\mysql\bin\mysql --user=root mysql < MySqlMakeUser01.txt |
Listing 34 MySqlMakeUser01.bat
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP |
Listing 35 MySqlMakeUser01.txt
c:\mysql\bin\mysql --user=root mysql < MySqlRemoveUser01.txt |
Listing 36 MySqlRemoveUser01.bat
REVOKE ALL PRIVILEGES ON *.* FROM 'auser'@'localhost'; |
Listing 37 MySqlRemoveUser01.txt
c:\mysql\bin\mysql --user=root mysql < MySqlDropDatabase01.txt |
Listing 38 MySqlDropDatabase01.bat
DROP DATABASE JunkDB; |
Listing 39 MySqlDropDatabase01.txt
/*File Jdbc11.java |
Listing 40 Jdbc11.java
/*File Jdbc12.java |
Listing 41 Jdbc12.java
/*File Jdbc10.java |
Listing 42 Jdbc10.java
Copyright 2004, Richard G. Baldwin. Reproduction in whole or in part in any form or medium without express written permission from Richard Baldwin is prohibited.
About the author
Richard Baldwin is a college professor (at Austin Community College in Austin, TX) and private consultant whose primary focus is a combination of Java, C#, and XML. In addition to the many platform and/or language independent benefits of Java and C# applications, he believes that a combination of Java, C#, and XML will become the primary driving force in the delivery of structured information on the Web.Richard has participated in numerous consulting projects, and he frequently provides onsite training at the high-tech companies located in and around Austin, Texas. He is the author of Baldwin's Programming Tutorials, which have gained a worldwide following among experienced and aspiring programmers. He has also published articles in JavaPro magazine.
Richard holds an MSEE degree from Southern Methodist University and has many years of experience in the application of computer technology to real-world problems.
-end-
