《疯狂Java讲义》读书笔记（八）：MySQL数据库与JDBC编程

第十三章：MySQL数据库与JDBC编程

1、程序使用JDBC API以统一的方式来连接不同的数据库，然后通过Statement对象来执行标准的SQL语句，并可以获得SQL语句访问数据库的结果。

2、创建数据库：create database [if not exists] 数据库名； 删除数据库：drop database 数据库名；  建立了数据库，如果想使用该数据库：use 数据库名；   显示该数据库所有的数据表：show tables； 如果想查看指定数据库表的结构，查看多少列，每一列的数据类型： desc 表名。

3、SQL语句基础：SQL全称Structured Query Language，结构化查询语言。标准的SQL语句通常可以分为如下几种类型：

☞查询语句：主要由select关键字完成  ☞DML，Data Manipulation Language数据操作语言，主要由insert、update和delete三个关键字完成；  

☞DDL，Data Definition Language 数据定义语言，主要由create、alter、drop和truncate四个关键字完成。☞DCL，Data Control Language，数据控制语句，主要由grant和revoke两个关键字组成。 

☞事务控制语句：主要由commit、rollback和savepoint三个关键字完成。

truncate是一个特殊的关键字，它相当于先删除指定的数据库的表，然后再重建该数据表，即表里面的数据全都被清空。truncate比delete效率高，该操作会重设自动增长计数器。

DDL语句：表（table），约束（constraint，执行数据校验的规则，用于保证数据完整性规则），视图（view，一个或多个数据表里数据的逻辑显示，视图并不存储数据），索引（index，用于提高查询性能），函数（function，完成一次特定的计算，具有一个返回值），存储过程（procedure，用于完成一次完整的业务处理，没有返回值，但可以通过传出参数将多个值传给调用环境），触发器（trigger，相当于一个事件监听器，当数据库发生特定事件后，触发器被触发，完成相应的处理）。

4、表的基本类型：整型通常用int，小数点用decimal，普通长度varchar，大文本类型text，图片用blob，日期用datetime。

5、删除列：alter table 表名 drop 列名； 

重命名表：alter table 表名 rename to 新表名； 

 字段重命名：alter table 表名 change 旧字段名 新字段名 varchar(n)；  

删除表：drop table 表名；

6、大部分数据库支持5种完整性约束：①not null：非空约束；②unique：唯一约束，指定某列或者几列组合不能重复；③primary key：主键；④foreign key：外键，指定该行记录从属于主表中的某一条记录，主要用于保证参照完整性；⑤check：检查，指定一个布尔表达式，用于指定对应列的值必须满足该表达式。

7、①修改非空约束：alter table 表名 modify 列名 varchar(20) default '' not null;②唯一性约束：只需要在新建的列名后加上unique关键字即可，如：column_name varchar(20)unique；③联合约束：constraint unqui_name unique(col1,col2);

要求的是col1和col2两个组合的值不能重复。④列级主键：直接在列名后加primary key即可，表级主键：是指多列建立组合主键，只能使用表级：在建表的时候primary key(col1,col2)，或者在后期添加：alter table 表名 add primary key(col1,col2)，删除主键：alter table 表名 drop primary key。

8、MySQL支持整型的自动增长：id int auto_increment primary key

9、foreign key约束：外键约束主要用于保证一个或两个数据表之间的参照完整性，主要用来构建两个表或字段之间的参照关系：子（从）表外键列的值必须在主表被参照列的值范围之内，或者为空（也可以通过非空约束来限定不允许为空）。当主表的记录被从表记录参照时，主表的记录是不允许被删除的，必须先把从表里参照该记录的所有记录全部删除后，才可以删除主表的该记录。还有一种方式，删除主表记录时级联删除从表中所有参照该记录的从表记录。

从表外键参照列只能是主表的主键列或者唯一键列，这样才能保证从表记录可以准确定位到被参照的主表记录。同一个表可以拥有多个外键。增加外键列的表被称为从表，只要为外键列增加唯一约束就可以表示一对一的关联关系了。通常在一对多的情况下，在多的一端增加外键列。建立外键约束同样可以采用列级约束语法和表级约束语法：如果仅对单独的数据列建立外键约束，则使用列级约束语法即可：SQL的语法☞（teacher_table的主键是teacher_id），student_table表是从表，在建表的时候，从表句子如下：java_teacher int references teacher_table(teacher_id)。其中java_teacher是从表的某个列。MySQL的语法：foreign key(java_teacher)references teacher_table(teacher_id)，如果需要显示的指定外键约束的名字，应使用constraint关键字：constraint student_teacher_fk  foreign key(java_teacher)references teacher_table(teacher_id)。

如果需要建立多列组合的外键约束，则必须使用表级语法，SQL的语法☞：foreign key (java_teacher_name，java_teacher_pass) references teacher_table(teacher_name，teacher_pass)。

删除外键约束语法：alter table 表名 drop foreign key student_teacher_fk。增加外键约束使用：alter table 表名 add foreign key(col1，col2)references 表2 (col3，col4)。

☞外键约束不仅可以参照其它表，还可以参照自身，叫做自关联：foreign key (col1) references foreign_name(col2)。

☞如果想定义当删除主表记录时，从表的记录也会随之删除，则需要在建立外键约束后添加：on delete cascade 或添加：on delete set null，第一种是删除主表记录，参照该表的从表记录也会全部级联删除；第二种是指定当删除主表记录时，把参照该主表记录的从表的外键设为null。

check约束：语法如下：check(userName_id > 0)，但是MySQL支持得不够好。

 

索引：索引是存放在模式schema中的一个数据库对象，创建索引的唯一作用就是加速对表的查询，使用快速路径访问方法来快速定位数据，从而减少了磁盘的I/O。索引不能独立存在，必须属于某个表。建索引语句：create index index_name on 表名 (col1,col2...)，删除索引：drop index index_name on 表名。增加索引会增加系统的维护工作，系统开销，还需要消耗一定的磁盘空间。

 

DML语句，由insert into、update和delete from三个命令组成。

①insert into每次只能插入一条记录，表名后可以用括号列出需要插入值的列名，values后括号列出对应需要插入的值。如果省略了表名后面的括号及括号里的列名列表，默认将为所有列都插入值，则需要为每一列都指定一个值。

insert into 表1 (username,password)values('张三','123')；根据前面介绍的外键约束规则，外键列里的值必须是被参照列里已有的值，所以向表中插入记录之前，通常应该先向主表中插入记录，否则从表的外键列只能为null。

☞使用子查询的值插入：insert into 表1(col1) select col2 from 表2。一次可以插入多条记录。

②update语句用于修改记录，每次可修改多条记录。update 表1 set col1=value1,col2=value2...where condition

③delete from语句用于删除记录。delete from 表1 where condition。当主表记录被从表记录参照时，主表记录不能被删除，只有先将从表中参照主表记录的所有记录全部删除后，才可以删除主表记录。还有一种情况是设置了级联的删除on delete cascade或者使用了on delete set null，会执行相应的功能。 

查询语句：select。

①查询的结果还可以先执行函数，再输出：select userid+5 from 表1 where userid*3>10；

②concat函数用来进行字符串连接运算（MySQL支持）select concat(userName,'University')from 表1。

③as关键字使得查询出来的列名可以更改，还可以为表名更改查询的名字。

④MySQL和Oracle都支持dual虚表，比如：select 5*2 from dual。

⑤distinct关键字用来去除重复的行。

⑥between A and B：表示大于等于A，小于等于B，in关键字是在里面的所有范围；like关键字是字符串匹配，通配符；is null要求指定值等于null。SQL语句中可以使用两个通配符：下划线_和百分号%，下划线可以代表一个任意的字符，百分号可以代表任意多个字符。如果需要匹配这2个符号，请使用\作为转义字符。

⑦判断某个值是否为空，不要使用=null，因为null=null返回null，应该使用is null判断：select * from 表1 where userName is null；就查出了所有userName为null的记录。

⑧and、or关键字：where A>1 and B<9 ；where not userName like '\_%'查出名字不以下划线开头的所有记录；where (userid >3 or userName >'张')and java_teacher>1 使用括号强制先计算or运算。

⑨asc、desc：asc是升序，desc是降序。记法：a在前面，后面都会比a大，从小到大排序，升序。

 

分组和组函数：常用5个函数：①avg ([distinct|all]expr)：计算多行expr的平均值，必须是数值类型，使用distinct修饰表示不计算重复值，all使用与否效果都一样。②count({*|[distinct|all]expr})：计算多行expr的总数，星号*表示统计所有的行数，distinct表示不计算重复值。③max(expr)：计算多行的最大值。④min(expr)：计算多行的最小值。⑤sum([distinct|all]expr)：计算多行expr的总和，distinct表示不计算重复的。类型必须是数值类型。

例子：计算userName列总共有多少个值：select count(distinct userName)from 表1。

注：distinct和*不同时使用，否则出错。

对于大多数的数据库而言，分组计算有严格的规则：如果查询列表中使用了组函数，或者select语句中使用了group by分组子句，则要求出现在select列表中的字段，要么使用组函数包装起来，要么必须出现在group by子句中。

having：后面也会有一个表达式，只有满足该条件的表达式的分组才会被选出来。过滤组必须使用having子句，不能在where子句中使用组函数，having子句才可以使用组函数。select * from 表1 group by userName having count(*)>2：查询userName这个字段为一组且数量大于2的所有记录，所有列。select userId from 表1 where userId>5 group by userId having count(userId)>1：查询userId大于5的重复的userId。

 

子查询：

使用子查询注意：①子查询要用括号括起来；②把子查询当成数据表时（出现在from之后），可以为该子查询起别名，尤其是作为前缀来限定数据列时，必须给子查询起别名；③把子查询当成过滤条件时，将子查询放在比较运算符的右边，这样可以增加可读性；④把子查询当成过滤条件时，单行子查询使用单行运算符，多行子查询使用多行运算符。

1、select * from (select * from student_table)  t  where t.java_teacher_id>1 //子查询创建了一个临时视图t，别名是t。

2、把子查询当成where条件中的值，如果子查询返回单行、单列值，则被当成一个标量值使用，也就可以使用单行记录比较运算符：select * from student_table where java_teacher > (select teacher_id from teacher_table where teacher_name ='biandan');粗体部分将返回一个单行、单列值。如果子查询返回多个值，则需要使用in、any和all关键字，in可以单独使用：select * from 表1 where student_id in (select teacher_id from 表2);只要student_id 与该值列表中的任意一个值相等，就可以选出这条记录。any关键字，如果 “=any”与in关键字一样，<any小于任何一个都行，>any只要大于任何一个都行：select * from 表1 where student_id > any(select teacher_id from 表2)。关键字all，<all 要求值列表的最小，就是比最小的还要小，>all 要求大于值列中的最大值，就是必须大于里面最大的那个值：select * from 表1 where student_id >all(select teacher_id from teacher_id)。其实还可以使用多行、多列的组合，中间用逗号隔开。

 

集合运算：select语句查询的结果是一个包含多条数据的结果集，类似于数据里的集合，可以进行并union、交intersect和差minus运算。两个集合的运算必须满足：①两个结果集所包含的数据列的数量必须相等；②两个结果集所包含的数据列的类型必须一一对应。

1、union运算：select 语句1 union select 语句2。

2、minus运算：select 语句1 minus select 语句2。MySQL并不支持minus运算，因此只能使用子查询来完成了。

3、intersect运算：select 语句1 intersect select 语句2。MySQL并不支持intersect 运算，因此只能使用子查询来完成了。

 

JDBC的典型用法：

☞DriverManager：用于管理JDBC驱动的服务类，该类主要获取Connection对象，包含的方法是：public static synchronized Connection getConnection(String url,String user,String password)throws SQLException

☞Connection：代表数据库连接对象，每个Connection代表一个物理连接会话，想要访问数据库，必须获得连接，该接口的常用方法：

①Statement createStatement()throws SQLException：该方法返回一个Statement对象；

②PreparedStatement prepareStatement(String sql)throws SQLException：该方法返回预编译的Statement对象，即将SQL语句提交到数据库进行预编译；

③CallableStatement prepareCall(String sql)：该方法返回一个CallableStatement 对象，用于调用存储过程。②③是①的子类。

☞除此之外，Connection还有如下几个用于控制事务的方法：

①Savepoint setSavepoint()：创建一个保存点；②Savepoint setSavepoint(String name)：以指定名字来创建一个保存点；③void setTransactionIsolation(int level)：设置事务的隔离级别；④void rollback()：回滚事务；⑤void rollback(Savepoint savepoint)：将事务回滚到指定的保存点；⑥void setAutoCommit(boolean autoCommit)：关闭自动提交，打开事务；⑦void commit()：提交事务。Java 7为Connection新增了setSchema(String schema)、getSchema()两个方法，用来控制该Connection访问的数据库Schema。同时还新增了setNetworkTimeout(Executor executor，int millisecondes)、getNetworkTimeout()两个方法来控制数据库连接的超时行为。

☞Statement：用于执行SQL语句的工具接口。该对象既可以执行DDL、DCL语句，还可以执行DML语句和SQL语句。当执行SQL语句时，放回结果集，方法如下：①ResultSet executeQuery(String sql)：该方法用来执行查询语句，并返回查询结果对应的ResultSet对象，该方法只能用于查询语句。②int executeUpdate(String sql)：该方法用来执行DML语句（数据操作语句，由insert into、update和delete from三个命令组成），用来返回影响的行数，该方法执行DDL（数据定义语句，数据定义语言，主要由create、alter、drop和truncate四个关键字完成）将返回0。③boolean execute(String sql)：该方法可执行任何SQL语句。如果执行后第一个结果集为ResultSet对象，则返回true；如果执行后第一个结果为受影响的行数或没有任何结果，则返回false。Java 7为Statement新增了closeOnCompletion()方法，如果Statement执行了该方法，则当所有依赖于该Statement的ResultSet关闭时，该Statement会自动关闭。

☞PreparedStatement：预编译的Statement对象。性能较好，它比Statement多了如下方法：void setXXX(int parameterIndex，XXX value)：该方法根据传入参数值的类型不同，需要使用不同的方法，如int、String类型，传入的值根据索引传给SQL语句中的指定位置的参数，index从1开始计算，而不是0，PreparedStatement同样有Statement的三个方法。

☞ResultSet：结果集对象。ResultSet可以通过索引或者列名获得列数据。它包含了如下常用方法来移动记录指针：①void close()：释放ResultSet对象；②boolean next()：将ResultSet的记录指针定位到下一行，如果移动后的记录指针指向一条有效记录，则该方法返回true。还有很多方法。。。ResultSet可以通过getXXX(int columnIndex)或getXXX(String name)方法来获取当前行、指定列的值，前者根据列索引获取值，后者根据列名获取值。

 

☞JDBC编程步骤：

①加载数据库驱动。通常使用Class类的forName静态方法来加载驱动：Class.forName(driverClass)，实际例子是：Class.forName("com.mysql.jdbc.Driver");如果是Oracle驱动，则改为：oracle.jdbc.driver.OracleDriver；实际上，只是使用数据库驱动类名的字符串而已。

②通过DriverManager获取数据库连接。DriverManager.getConnection(String url,String user,String pass)；数据库的URL通常遵循如下写法：jdbc:subprotocol:other stuff，比如MySQL的：jdbc:mysql://localhost:port/dataBaseName，Oracle写法：jdbc:oracle:thin:@hostname:port:dataBaseName。

③通过Connection对象创建Statement对象。写法有三个：1、createStatement()：创建基本的Statement对象；2、preparedStatement(String sql)：根据传入的SQL语句创建预编译的Statement对象；3、prepareCall(String sql)：根据传入的SQL语句创建CallableStatement对象。

④使用Statement执行SQL语句。有3个方法：1、execute()方法：可以执行任何的SQL语句，但是比较麻烦；2、executeUpdate()：主要用于执行DML和DDL语句，执行DDL语句返回0，执行DML语句返回影响的行数；3、executeQuery()：只能执行查询语句，执行后返回结果集ResultSet。

⑤操作结果集。ResultSet对象主要提供如下两类方法：1、next()、previous()、first()、last()..等移动记录指针的方法。2、getXXX()方法获取记录指针指向行、特定列的值。该方法可以使用列索引作为参数，也可以使用列名作为参数。使用列索引作为参数性能更好，使用列名的可读性更好，PS：如果查询的列数量比较多，建议使用列名作为参数，可读性好！

⑥回收数据库资源。包括关闭ResultSet、Statement和Connection等资源。

例子：Class.forName("com.mysql.jdbc.Driver");String url = "jdbc:mysql://localhost:3306/ctdsql";

try {Connection connection = DriverManager.getConnection(url, "root","123");

// 使用Statement创建一个Statement对象

Statement statement = connection.createStatement();

String sqlString = "select * from ctdUser";

ResultSet resultSet = statement.executeQuery(sqlString);

while (resultSet.next()) {

System.out.println(resultSet.getInt("id") + "\t"+ resultSet.getString("username") + "\t"+ resultSet.getString("password") + "\t"+ resultSet.getString("address") + "\t"+ resultSet.getString("phone"));

System.out.println("=======================");}} catch (Exception e) {System.out.println(e.getMessage());}

 

没有把数据库连接信息写在程序里，而是使用一个mysql.ini文件（就是一个properties配置文件）来保存数据库连接信息，这是比较成熟的做法：当需要把应用程序从开发环境移植到生产环境时，无须改动源代码，只需要改动配置文件即可。介绍读取配置文件的方法（前面有写过类似）：

Properties p=new Properties();

p.setProperty("张三", "123");

p.setProperty("李斯", "520");

p.setProperty("王五", "2016");

p.setProperty("Johnson", "mynameis");

p.store(new FileOutputStream("myTest.ini"), "headLine");//会在当前目录下生成一个myTest.ini文件，效果如下：

#headLine

#Mon Oct 31 23:19:20 GMT+08:00 2016

Johnson=mynameis  “换行”  \u738B\u4E94=2016   “换行”  \u5F20\u4E09=123   “换行”  \u674E\u65AF=520

Properties p2=new Properties();p2.setProperty("小猪", "2017");p2.load(new FileInputStream("myTest.ini"));

System.out.println(p2);//输出：{王五=2016, Johnson=mynameis, 张三=123, 小猪=2017, 李斯=520}，获取特定值：

string url=p2.getProperty("url");

 

使用execute方法来执行SQL语句：execute可以执行任何SQL语句，但是用起来麻烦，通常没必要使用，使用executeUpdate和executeQuery方法足够。如果在不清楚SQL语句的类型情况下，则需要使用execute方法了：

boolean hasResultSet=stmt.execute(sql);  if(hasResultSet){ResultSet rs=stmt.getResultSet()...}

 

使用PreparedStatement执行SQL语句：如果需要经常反复执行一条结构相似的SQL语句，考虑使用预编译PreparedStatement，可以使用带占位符（？）参数的SQL语句来完成。如果不清楚类型参数，可以使用setObject()方法来传入参数，由PreparedStatement来负责类型的转换。例子：

Connection connection = DriverManager.getConnection(url, username,password);

PreparedStatement preparedStatement = connection.prepareStatement("insert into ctdddl values(null,?,?)");

for (int i = 0; i < 100; i++) {preparedStatement.setString(1, "Prepared姓名" + i);

preparedStatement.setString(2, "222pass");preparedStatement.executeUpdate();}

注：index的下标从1开始而不是0。使用PreparedStatement还可以防止SQL注入。一个SQL注入的例子：

用户执行登陆时，如果账户和密码正确则能够登陆：

String sql="select * from 表1 where username='"+userName+"' and password='"+password+"'";然后通过连接数据库、执行查询，如果有结果集，则表明用户可以登录成功。现在说如何注入：如果用户输入的用户名是 'or true or'，然后直接登陆，结果是：执行的SQL语句是：select * from 表1 where username=''or true or'' and password='' ，实际上，就遍历数据库的所有用户名和密码了，即结果集就有东西了，表明可以验证登陆成功。

PreparedStatement有如下好处：①性能好②无须拼接SQL语句，编程更加简单③可以防止SQL注入，安全性更好。注意占位符只能代替普通值，不能代替表名、列名，更不能代替SQL语句。 

补充：触发器tigger 2017年2月7日15:25:51

触发器实际是一种特殊的存储过程。一般的存储过程是通过调用存储过程名直接调用，而触发器主要是通过事件（增、删、改）进行触发而被执行的。常见的触发器有两种：after（for）、instead of，用于insert、update和delete事件。

其中，after|for 表示执行代码后，执行触发器；instead of 表示执行代码前，用已经写好的触发器代替你的操作。

触发器语法：SQL Server版本的

create trigger tigName（触发器名） on tableName（表名） for|after 或 instead of   update|insert|delete  as sql语句。

Mysql数据库版本的：

create trigger tigName（触发器名） after|before insert on tableName（表名） for each row delete from 表名 where id=(select id from inserted)

比较Mysql数据库和SQL Server：语法大致相同，只是位置顺序不同，还有Mysql必须加上for each row且没有as关键字。

例子1：禁止用户插入数据（实际先插入，后立即删除）

create trigger tig_name on sTable for|after  insert as delete from sTable where id=(select id from inserted)

上面的触发器能让用户无法插入数据：insert into sTable values(1,500)无法插入到数据库。

例子2：删除谁就让谁的账户+10

create trigger tig_name2 on sTable instead of delete as update bank set balance=balance+10 where id=(select id from deleted)

  

使用CallableStatement调用存储过程：

创建MySQL存储过程：create procedure pro_Name(a int,b int,out sum int)  set sum=a+b;

调用存储过程的方法是：prepareCall()，SQL语句总是这个格式：{call 存储名 (?,?,?...)}，其中问号作为存储过程参数的占位符。存储过程的参数既有传入参数也有传出参数，可以通过setXXX方法为传入参数设置值，传出参数是获取存储过程的值，CallableStatement需要调用registerOutParameter()方法来注册该参数：cstmt.registerOutParameter(3,Types.INTEGER)。执行结束后通过getXXX(int index)方法来获取指定传出参数的值。

例子：CallableStatement cstmt=conn.prepareCall("{call add_pro(?,?,?)}");cstmt.setInt(1,4);cstmt.setInt(2,5);//注册CallableStatement的第三个参数是int类型  cstmt.registerOutParameter(3,Types.INTEGER);  //执行存储过程

cstmt.execute();  //获取结果： int abc=cstmt.getInt(3);

或者实践过的：create procedure testProce(in loginuser varchar(50),in loginpass varchar(50),out usercount int)

sql security definer  //这行可以省略

begin

select count(*) as havecount from testtable  //这句代码如果改为：select * from testtable，则可以查询所有字段

where username=loginuser and userpass=loginpass;  //注意有分号结尾

select FOUND_ROWS() into usercount;   //分号结尾

end

cst = conn.prepareCall("{call testProce(?,?,?)}");cst.setString(1, "biandan");cst.setString(2, "123");

ResultSet rs = cst.executeQuery();while (rs.next()) {//havecount  if(rs.getInt("havecount")>0){//登陆成功}

//select *  String username=rs.getString("username");String sersex=rs.getString("usersex");}conn.close();

 

管理结果集：

1、可以滚动、可以更新的结果集：

Connection在创建Statement或PreparedStatement时还可以额外传入如下两个参数：①resultSetType：控制ResultSet的类型，有三个值1、ResultSet.TYPE_FORWARD_ONLY：该常量控制记录指针只能向前移动，这是JDK1.4以前的默认值。2、ResultSet.TYPE_SCOLL_INSENSITIVE：该常量控制记录指针可以自由移动，但底层的数据的改变不会影响ResultSet的内容。3、ResultSet.TYPE_SCROLL_SENSITIVE：该常量控制记录指针可以自由移动，但底层的数据改变会影响ResultSet的内容。对于有些数据库来说，这2个常量没有太大的区别。

②resultSetConcurrency：控制ResultSet的并发类型，接收2个值：1、ResultSet.CONCUR_READ_ONLY：该常量指示ResultSet是只读的并发模式（默认）。2、ResultSet.CONCUR_UPDATABLE：该常量指示ResultSet是可以更新的并发模式。可以更新的结果集还需要满足2个条件：所有的数据都来自一个表，选出的数据集必须包含主键。通过该PreparedStatement创建的ResultSet就是可以滚动、可更新的，程序可以调用ResultSet的updateXXX(int colIndex,XXX value)方法来修改记录指针的所指记录、特定列的值，最后调用ResultSet的updateRow()方法来提交修改。例子：

PreparedStatement pstmt=conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);

ResultSet rs=pstmt.executeQuery(); rs.last();//将指针移动到最后  int rowCount=rs.getRow();//获得行数量

for(int i=rowCount;i>0;i--){rs.absolute(i)//将结果集的记录指针移动到第i行  rs.updateString(2,"学生名："+i);//修改记录指针所指记录、第2列的值   rs.updateRow();//提交修改。}

 

处理Blob类型数据：

Blob列通常用于存储大文件，典型的Blob内容是一张图片或一个声音文件，由于特殊性，必须使用特殊方式存储。将Blob数据插入数据库需要使用PreparedStatement，该对象有一个方法：setBinaryStream(int paramIndex,InputStream x)；该方法可以为指定参数传入二进制输入流，从而实现将Blob数据保存到数据库。当需要从ResultSet里取出Blob数据时，可以调用ResultSet的getBlob(int index)方法，该方法返回一个Blob对象，Blob对象提供了getBinaryStream方法来获取该Blob数据的输入流。

 

离线的RowSet：离线的RowSet会直接将底层数据读入内存中，封装成RowSet对象，而RowSet对象则完全可以当成JavaBean使用。CachedRowSet是所有离线RowSet的父接口。例子：这是一个方法，返回CachedRowSet对象：

public CachedRowSet query(String sql) throws Exception {

Class.forName(driver);Connection connection = DriverManager.getConnection(url, username,password);

Statement statement = connection.createStatement();ResultSet resultSet = statement.executeQuery(sql);

// 使用RowSetProvider创建RowSetFactory  

RowSetFactory factory = RowSetProvider.newFactory();

// 创建默认的CachedRowSet实例

CachedRowSet cachedRowSet = factory.createCachedRowSet();

// 使用ResultSet装填RowSet，让该RowSet直接包装给定的RowSet对象

cachedRowSet.populate(resultSet);

// 关闭资源

resultSet.close();statement.close();connection.close();return cachedRowSet;}

在main函数中：

CachedRowSet rs = test.query("select * from ctduser");

rs.afterLast();// 将光标移动到最后

while (rs.previous()) {System.out.println(rs.getInt(1) + "\t" + rs.getString(2) + "\t"+ rs.getString(3) + "\t" + rs.getString(4) + "\t"+ rs.getString(5));

if (rs.getInt(1) == 3) {// 修改指定记录的行

rs.updateString("username", "这是修改后的名字");

rs.updateRow();}}

// 重新获取数据库的连接

Connection conn = DriverManager.getConnection(url, username, password);

conn.setAutoCommit(false);//设置不自动提交

//把对RowSet所做的修改同步到底层数据库，调用方法使得更新写入到数据库

rs.acceptChanges(conn);//必须传入Connection

 

离线RowSet的查询分页：如果SQL查询返回的记录过大，CachedRowSet将会占用大量的内存，在某些极端情况下，甚至会导致内存溢出。为此，CachedRowSet提供了分页功能，所谓分页功能就是一次只装载ResultSet里的某几条记录，这样就可以避免CachedRowSet占用大量内存问题。CachedRowSet提供如下方法来控制分页：①populate（ResultSet rs，int startRow）：使用给定的ResultSet装填RowSet，从ResultSet的第startRow条记录开始装填；②setPageSize（int pageSize）：设置CachedRowSet每次返回多少条记录；③previousPage（）：在底层ResultSet可用的情况下，让CachedRowSet读取上一页的记录；④nextPage（）：在底层ResultSet可用的情况下，让CachedRowSet读取下一页记录。

cachedRowSet.setPageSize(2);cachedRowSet.populate(resultSet, 1);

 

事务处理：对于任何的数据库应用而言，事务是非常重要的，事务是保证底层数据完整的重要手段，没有事务支持的数据库应用，显得非常脆弱。事务具备4个特性：原子性、一致性、隔离性和持续性，简称ACID性。原子性：事务是应用中最小的执行单位，就如原子时自然界的最小颗粒，事务是应用中不可再分的最小逻辑执行体。一致性：事务执行的结果，必须使得数据库从一个一致性状态，变到另一个一致性状态。隔离性：各个事务的执行互不干扰，任意一个事务的内部操作对其他并发的事务都是隔离的。也就是说，并发执行的事务之间不能看到对方的中间状态。持续性：持续性也称为持久性，事务一旦提交，对数据所做的任何改变都要记录到永久的存储器中。

事务的提交分为：显示提交和自动提交。显示提交试用commit，自动提交执行DDL或DCL语句，或者程序正常退出。显示回滚：使用rollback，自动回滚：系统错误或者强行退出。自动提交和开启事务恰好相反，如果开启自动提交就是关闭事务，关闭自动提交就是开启事务。

MySQL还提供了savepoint来设置事务的中间点，通过使用savepoint设置事务的中间点可以让事务回滚动到指定中间点，而不是回滚全部事务。

JDBC的事务支持：JDBC提供了事务支持，JDBC事务支持由Connection提供，默认打开自动提交，即关闭事务。这种情况下，每条SQL语句一旦执行，便会立即提交到数据库，无法回滚。可以调用Connection的setAutoCommit()方法来关闭自动提交，开启事务，如下代码：conn.setAutoCommit(false);提交事务使用☞conn.commit();回滚事务用☞conn.rollback();

实际上，当Connection遇到一个未知的SQLException异常，系统将会非正常退出，事务也会自动回滚。如果程序捕捉了该异常，需要在异常处理中显示回滚事务。

 

使用连接池管理连接：数据库的建立及关闭是极其消耗系统资源的操作，在多层结构的应用环境下，这种资源的消耗对系统性能影响尤其的明显。数据库连接池的解决方案是：当应用程序启动时，系统自动建立足够的数据库连接，并将这些连接组成一个连接池。每次应用程序请求数据库连接时，无须重新打开连接，而是从连接池中取出已有的连接使用，使用完之后不再关闭数据库连接，而是直接归还给连接池。数据库连接池的常用参数如下：数据库的连接数，连接池的最大连接数，最小连接数和每次的增加容量。

JDBC的数据库连接池使用javax.sql.DataSource来表示，DataSource只是一个接口。

C3P0数据源：Hibernate就推荐这个连接池，C3P0连接池不仅可以自动清理不再使用的Connection，还可以自动清理Statement和ResultSet。C3P0使用的jar包是：c3p0-0.9.1.2.jar。

ComboPooledDataSource dSource = new ComboPooledDataSource();

dSource.setDriverClass(driver);dSource.setJdbcUrl(url);dSource.setUser(username);dSource.setPassword(password);

dSource.setMaxPoolSize(10);// 最大连接数

dSource.setMinPoolSize(2);// 最小连接数

dSource.setInitialPoolSize(3);// 初始连接数

dSource.setMaxStatements(5);// 缓存Statement的最大数