一、SQL 语句基础
SQL 的全称是 Structured Query Language，也就是结构化查询语言，使用 SQL 语句，程序员和数据库管理员（DBA）可以完成如下的任务：
- 在数据库中检索需要的信息
- 对数据库的信息进行更新
- 改变数据库的结构
- 更改系统的安全设置
- 增加用户对数据库或表的许可权限
上面 5 个任务中，一般程序员可以管理前 3 个任务，但后面 2 个任务通常由 DBA 来完成。
标准 SQL 语句通常可分为如下几种类型：
- 查询语句 select
- DML 语句（Data Manipulation Language，即数据库操作语言）：insert、update、delete
- DDL 语句（Data Definition Language，即数据定义语言）：create、alert、drop、truncate
- DCL 语句（Data Control Languate，即数据库控制语言）：grant、revoke
- 事务控制语言：commit、rollback、savepoint
1、DDL 语句
create、drop、alert
创建表的语法：
create table [模式名.]表名
(
columnName1 datatype [default expr],
……
)
MySql 建表语句示例：
DROP TABLE IF EXISTS `cm_qyinfo`;
CREATE TABLE `cm_qyinfo` (
`id` varchar(32) NOT NULL COMMENT '主键',
`code` varchar(50) DEFAULT NULL COMMENT '与企业代码表的dmkey相关联',
`qymc` varchar(100) DEFAULT NULL COMMENT '企业名称',
`qydm` varchar(50) DEFAULT NULL COMMENT '企业代码',
`qyjc` varchar(100) DEFAULT NULL COMMENT '企业简称',
`nsrsbh` varchar(64) DEFAULT NULL,
`lxr` varchar(50) DEFAULT NULL,
`txdz` varchar(200) DEFAULT NULL COMMENT '通信地址',
`lxdh` varchar(50) DEFAULT NULL COMMENT '联系电话',
`email` varchar(100) DEFAULT NULL,
`post` varchar(10) DEFAULT NULL,
`cwzj` varchar(32) DEFAULT NULL COMMENT '财务总监',
`cwzg` varchar(32) DEFAULT NULL COMMENT '财务主管',
`fatherId` varchar(32) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
上面这种常见的建表语句只能创建一个空表，该表中没有任何数据。如果使用子查询建表语句则可以在建表的同时插入数据。
create table [模式名.]表名
[column(, column...)]
as subsquery;
上面语法中新表的字段列表必须与子查询中的字段列表数量匹配，创建新表时的字段列表可以省略，如果省略了该字段列表，则新表的列名与选择结果完全相同。例如：
create table test2 as select * from resc;
修改表结构的语法
增加列：
alter table 表名
add
(
column_name1 datatype [default expr],
……
);
修改列：
alter table 表名
modify column_name datatype [default expr] [first | after col_name];
删除列：
alter table 表名
drop column_name;
上面介绍的这些增加、修改和删除列的语法是标准的 SQL 语法，MySql 还提供了两种特殊的语法：重命名数据表和完全改变列定义。
重命名数据表的语法如下：
alert table 表名
rename 新表名;
完全改变列定义：
change old_column_name new_column_name type [default expr] [first | after col_name]
删除表的语法：
drop table 表名;
删除数据表的效果如下：
- 表结构被删除，表对象不再存在
- 表里所有的数据也被删除
- 该表所有相关的索引、约束也被删除
truncate 表
对于大多数数据库而言，truncate 都被当做 DDL 处理，truncate 被称为“截断”某个表——它的作用是删除该表里的全部数据，但保留表结构。truncate 相对于 DML 里的 delete 命令而言，truncate 的速度要快得多，而且 truncate 不像 delete 可以删除指定的记录，truncate 只能一次性删除整个表的全部记录。truncate 命令的语法如下：
truncate 表名;
约束概述：
大多数数据库支持下面五类完整性约束：
- NOT NULL：非空约束 ，指定某列不能为空。
- UNIQUE：唯一约束，指定某列或者几列组合不能重复。
- PRIMARY KEY：主键，指定该列的值可以唯一地标识该条记录。
- FOREIGN KEY：外键，指定该行记录从属于主表中的一条记录，主要用于保证参照完整性。
- CHECK：检查，指定一个布尔表达式，用于指定对应列的值必须满足该表达式。
根据约束对数据列的限制，可分为两列：
- 单列约束
- 多列约束
NOT NULL 非空约束
在建表示指定非空约束：
CREATE TABLE `cm_ztlink`
(
`ztdm` varchar(32) NOT NULL COMMENT '帐套代码',
`ztmc` varchar(200) NULL COMMENT '帐套名称'
) ;
在修改表时指定或者修改非空约束：
-- 增加非空约束
alter table cm_ztlink
modify ztmc varchar(200) NOT NULL;
-- 删除非空约束
alter table cm_ztlink
modify ztmc varchar(200) NULL;
UNIQUE 约束
唯一性约束用于保证指定列或者指定列的组合不允许出现重复值，虽然唯一性约束的列不允许出现重复值，但可以出现多个null。
唯一约束既可以在列级约束语法建立，也可以在表级约束约束语法上建立。
列级语法：
create table unique_test
(
test_name varchar(255) unique
);
如果需要为多列组合建立唯一约束，或者想自行指定约束名则需要使用表级约束语法：
[constraint 约束名] 约束定义
示例：
create table unique_test2
(
test_id int not null,
test_name varchar(255),
test_pass varchar(255),
-- 使用表级约束语法建唯一约束
unique (test_name),
-- 使用表级约束语法建唯一约束，而且指定约束名
constraint test2_uk unique(test_pass)
);
为两列组合建立唯一约束：
create table unique_test3
(
test_id int not null,
test_name varchar(255),
test_pass varchar(255),
-- 使用表级约束语法建立唯一约束，指定两列组合不能重复
constraint test3_uk unique(test_name, test_pass)
);
也可以在修改表结构时使用 add 关键字来增加唯一约束
alter table unique_test3
add unique(test_name, test_pass);
也可以在修改表时使用 modify 关键字、为单列采用列级约束语法来增加唯一约束
alter table haha
modify test_name varchar(255) unique;
对于大多数数据库而言，删除约束都是在 alter table 语句后使用 drop constraint 约束名的语法来删除约束，但 MySql 是使用 drop index 约束名的方式来删除约束。
alter table unique_test3
drop index test3_uk;
PRIMARY KEY 约束
主键约束相当于非空约束和唯一约束功能的集合，当创建主键约束时，MySql 在主键约束所在列或列组合上建立对应的唯一索引。
列级语法建立主键约束
create table primary_test
(
test_id int primary key,
test_name varchar(255)
);
表级约束语法建立主键索引
create table primary_test2
(
test_id int not null,
test_name varchar(255),
constraint test2_pk primary key(test_id)
);
使用 add 指令为表增加表级约束语法增加主键约束
alter table primary_test3
add primary key(test_name, test_pass);
使用 modify 指令使用列级约束语法在单独一列上增加主键约束
alter table primary_test3
modify test_name varchar(255) primary key;
表级约束建立组合主键
create table primary_test3
(
test_name varchar(255),
test_pass varchar(255),
primary key(test_name, test_pass)
);
删除主键约束
alter table primary_test3
drop primary key;
将主键列设置为自增列：
create table primary_test4
(
test_id int auto_increment primary key,
test_name varchar(255),
test_pass varchar(255)
);
FOREIGN KEY 约束
外键约束主要用于保证一个或者两个表之间的参照完整性。
从表外键参照的只能是主表主键列或者唯一键列，这样才能保证从表记录可以准确定位到被参照的主表记录。同一个表内可以有多个外键。
使用列级约束语法建立外键约束直接使用 references 关键字，reference 指定该列参照的哪个主表以及参照主表的哪一列。示例：
create table teacher_table1
(
teacher_id int auto_increment,
teacher_name varchar(255),
constraint teacher_table1_pk primary key(teacher_id)
);
create table student_table1
(
student_id int auto_increment primary key,
student_name varchar(255),
java_teacher int references teacher_table1(teacher_id)
);
表级约束语法创建外键约束：
create table teacher_table
(
teacher_id int auto_increment,
teacher_name varchar(255),
constraint teacher_table1_pk primary key(teacher_id)
);
create table student_table
(
student_id int auto_incretment primary key,
student_name varchar(255),
java_teacher int,
foreign key(java_teacher) references teacher_table(teacher_id)
);
使用 constraint 指令指定自定义外键名的外键约束
create table teacher_table2
(
teacher_id int auto_increment,
teacher_name varchar(255),
constraint teacher_table1_pk primary key(teacher_id)
);
create table student_table2
(
student_id int auto_increment primary key,
student_name varchar(255),
java_teacher int,
constraint student_teacher_fk foreign key(java_teacher) references teacher_table2(teacher_id)
);
如果需要建立多个列组合的外键约束，则必须使用表级约束语法，例如：
create table teacher_table3
(
teacher_name
varchar(255),
teacher_pass
varchar(255),
constraint twopk primary key(teacher_name, teacher_pass)
);
create table student_table3
(
student_id
int auto_increment primary key,
student_name
varchar(255),
java_teacher_name varchar(255),
java_teacher_pass
varchar(255),
foreign key(java_teacher_name, java_teacher_pass)
references teacher_table3(teacher_name, teacher_pass)
);
删除外间的语法：
alert table student_table3
drop foreign key student_table3_ibfk_1;
增加外键约束
alter table student_table3
add foreign key(java_teacher_name, java_teacher_pass) references teacher_table3(teacher_name, teacher_pass)
外键约束不仅可以参照其它表，也可以参照自身，通常被称为“自关联”。例如：
create table foreign_test
(
foreign_id
int
auto_increment
primary key,
foreign_name
varchar(255),
refer_id
int,
foreign key(refer_id) references foreign_test(foreign_id)
);
如果想定义当删除主表记录时，从表记录也会随之删除，则需要在建立外键约束后添加 on delete cascade 或添加 on delete set null，第一种是删除主表记录时，把参照该主表记录的从表记录全部级联删除；第二种是指定当删除主表记录时，把参照该主表记录的从表记录的外键设为 null。例如：
create table teacher_table4
(
teacher_id
int
auto_increment,
teacher_name
varchar(255),
primary key(teacher_id)
);
create table student_table4
(
student_id
int
auto_increment
primary key,
student_name
varchar(255),
java_teacher
int,
foreign key(java_teacher) references teacher_table4(teacher_id) on delete cascade
);
CHECK 约束
例：
create table check_test
(
emp_id
int
auto_increment,
emp_name
varchar(255),
emp_salary
decimal,
primary key(emp_id),
-- 建立 CHECK 约束
check (emp_salary > 0)
);
索引
创建索引有两种方式：
- 自动：当在表上定义主键约束、唯一约束和外键约束时，系统会为该数据列自动创建对应的索引
- 手动：用户可以手动创建索引来加速查询
删除索引也有两种方式：
- 自动：数据表被删除时，该表上的索引被自动删除
- 手动：用户可以手动删除指定数据表上的指定索引
创建索引的语法：
create index index_name on table_name (column[, column]……);
下面的索引将会提高对 EMPLOYEES 表基于 LAST_NAME 字段的查询速度
create index emp_last_name_index on emplyees(last_name);
也可以同时对多列建立索引：
create index emp_last_name_index2 on employees(firstname, last_name);
删除指定索引：
drop index 索引名 on 表名
视图
视图看上去非常像一个数据表，但它不是数据表，因为它并不能存储数据。视图只是一个或多个数据表中数据的逻辑显示。视图的好处：
- 可以限制对数据的访问
- 可以使复杂的查询变得简单
- 提供了数据的独立性
- 提供了对相同数据的不同显示
建立视图的语法 ：
create or replace view 视图名
as
subquery
为了强制不允许改变视图的数据，大多数数据库允许在创建视图时使用 with check option 子句（Oracle 数据使用 with read only），使用该子句创建的视图不允许修改。例如：
create or replace view view_test
as
select teacher_name from teacher_table with check option;
删除视图：
drop view 视图名;
2、DML 语句语法
- 插入新数据
- 修改已有数据
- 删除不需要的数据
DML 主要由 insert into、update 和 delete from 三个命令组成。
insert into 语句
update 语句
delete from 语句
3、select 语句和 SQL 函数
当使用 select 语句进行查询时，还可以在 select 语句中使用算术运算符（+、-、*、/），从而形成算术表达式
- 对数值型数据列、变量、常量可以使用算术操作符（+、-、*、/）创建表达式
- 对日期型数据列、变量、常量可以使用部分算术操作符创建表达式（+、-），两个日期之间可以进行减法运算，日期和数值之间可进行加、减运算。
- 运算符不仅可以在列和常量、变量之间进行运算，也可以在两列之间进行运算。
示例：
select teacher_id + 5 from teacher_table;
select * from teacher_table where teacher_id * 3 > 4
从上面的 SQL 语句可以看出，select 后的不仅可以使数据列，也可以是表达式，甚至可以是变量常量等。
MySql 无法使用（+）将字符串常量、字符串变量或字符串列连接起来。MySql 使用 concat 函数进行字符串连接运算。
例如：select concat(teacher_name, 'XX') from teacher_table;
distinct 关键字。
一些特殊的比较运算符
运算符 含义
betwween val1 and val2 大于等于 val1，且小于等于 val2
in(list) 等于括号里多个值的任意之一
like 字符串匹配，like 后的字符串支持通配符
is null 要求指定值等于 null
使用 in 比较符时，必须在 in 后的括号里列出一个或多个值，它要求指定列必须与 in 括号里任意一个值相等。
示例：select * from student_table where student_id in (2, 4);
与之类似的是，in 括号里的值既可以是常量，也可以是变量或列名，
示例：select * from student_table where 2 in (student_id, java_teacher);
like 运算符主要用于进行模糊查询，SQL 语句中可以使用两个通配符：下划线（_）和百分号（%），下划线可以代表一个任意字符，百分号可以代表任意多个字符。
在某些情况下，我们需要查询的条件里需要使用下划线或百分号，不希望 SQL 把下划线和百分号当成通配符使用，MySql 使用反斜线（\）作为转义字符，例如：
select * from student_table where student_name like '\_%';
select * from student_table where student_name like '\_%' escape '\';
SELECT * FROM clubuser
WHERE (clubuser_name LIKE 'tes\_t' escape '\') and (clubuser_nickname like 't\_est' escape '\');
is null 用于判断某些值是否为空，判断是否为空不要用 = null 来判断，因为 SQL 中 null = null 返回 null。
select * from student_table where student_name is null;
SQL 中比较运算符、逻辑运算符的优先级
运算符 优先级（优先级小的优先）
所有比较运算符 1
not 2
and
3
or 4
例如：select * from student_table where not student_name like '\_%';
select * from student_table where not id < 10000;
排序：order by
数据库函数：多行函数、单行函数
MySql 数据库的数据类型大致可以分为数值型、字符型和日期时间型。
MySql 数据库的单行函数主要分为：
- 日期时间函数
- 数值函数
- 字符函数
- 转换函数
- 其他函数
转换函数主要负责完成类型转换，其他函数又可以大致分为如下几类：
- 位函数
- 流程控制函数
- 加密解密函数
- 信息函数
示例：
-- 选出 teacher_table 中 teacher_name 列的字符长度
select char_length(teacher_name) from teacher_table;
-- 计算 teacher_name 列的字符长度的 sin 值。
select sin(char_length(teacher_name)) from teacher_table;
-- 计算 1.57 的 sin 值，约等于 1
select sin(1.57);
-- 为指定日期加上一定的时间
-- 在这种用法下 interval 是关键字，需要一个数值，还需要一个单位
select date_add('1981-10-01', interval 2 month);
-- 这种用法更简单
select adddate('1981-01-01', 3);
-- 获取当前日期
select curdate();
-- 获取当前时间
select curtime();
-- 下面的 md5 是 md5 的加密函数
select MD5('testing');
MySql 提供了三个处理 null 的函数：
- ifnull(expr1, expr2): 如果 expr1 为 null，返回 expr2，否则返回 expr1.
- nullif(expr1, expr2): 如果 expr1 和 expr2 相等，则返回 null，否则返回 expr1.
- if(expr1, expr2, expr3): 有点类似于 ? : 三目运算符，如果 expr1 为 true，不等于 0，且不等于 null，返回 expr2，否则返回 expr3.
- isnull(expr1): 判断 expr1 是否为 null，如果为 null 返回 true，否则返回 false。
MySql 还提供了一个 case 函数，它是一个流程控制函数，case 函数的语法如下：
case value
when compare_value1 then result1
when compare_value2 then result2
……
else result
end
示例：
-- 如果学生的 java teacher 为 1，则返回 'Java 老师'，为 2 则返回 'Ruby 老师'，否则返回 '其他老师'
select student_name, case java_teacher
when 1 then 'Java 老师'
when 2 then 'Ruby 老师'
else '其他老师'
end
from student_table;
4、分组和组函数
组函数就是前面提到的多行函数，常用的组函数有：
- avg([distinct|all]expr)
- count({*|[distinct|all]expr})
- max([distinct]expr)
- sum([distinct]expr)
- min([distinct]expr)
- max([distinct]expr)
示例：
-- 计算 student_table 表中的记录条数
select count(*) from student_table;
-- 计算 Java_teacher 列总共有多少个值
select count(distinct java_teacher) from student_table;
-- 统计所有 student_id 的总和
select sum(student_id) from student_table;
-- 计算的结果是 20 * 记录的行数
select sum(20) from student_table;
-- 选出 student_table 中 student_id 的最大值
select max(student_id) from student_table;
-- 因为 sum 里的 expr 是常量 34，所有每行的值相同，使用 distinct 强制不计算重复值，所以下面的计算结果是 34
select sum(distinct 34) from student_table;
-- 使用 count 统计记录行数时， null 不会被算在内
select count(student_name) from student_table;
-- 对于可能出现 null 的列，可以使用 ifnull 函数来处理该列
-- 计算 java_teacher 列所有记录的平均值
select avg(ifnull(java_teacher, 0)) from student_table;
二、JDBC 的典型用法
DriverManager:
- public static synchronized Connection getConnection(String url, String user, String pass) throws SQLException
Connection:
- Statement createStatement() throws SQLException
- PreparedStatement prepareStatement(String sql) throws SQLException
- CallableStatement prepareCall(String sql) throws SQLException
- Savepoint setSavepoint()
- Savepoint setSavepoint(String name)
- void setTransacionIsolation(int level)
- void rollback()
- void rollback(Savepoint savepoint)
- void setAutoCommit(boolean autoCommit)
- void commit()
Statement:
- ResultSet executeQuery(String sql) throws SQLException
- int executeUpdate(String sql) throws SQLException
- boolean execute(String sql) throws SQLException
PreparedStatement
- void setXxx(int parameterIndex, Xxx value)
ResultSet
- void close() throws SQLException
- boolean absolute(int row)
- void beforeFirst()
- boolean first()
- boolean previous()
- boolean next()
- boolean last()
- void afterLast()
- 加载数据库驱动
// 加载驱动
Class.forName(driverClass)
- 通过 DriverManager 获取数据库连接
// 获取数据库连接
DriverManager.getConnection(String url, String user, String pass)
- 通过 Connection 对象创建 Statement 对象，Connection 创建 Statement 的方法有如下三个：
1）createStatement()
2）prepareStatement(String sql)
3）prepareCall(String sql)
- 使用 Statement 执行 SQL 语句
1）execute
2）executeUpdate
3）executeQuery
- 操作结果集
1）next、previous、first、last、beforeFirst、afterLast、absolute
2）getXxx 获取记录指针指向行，特定列的值。该方法既可使用列索引作为参数，也可使用列名作为参数。使用列索引作为参数性能更好，使用列名作为参数可读性更好。
- 回收数据库资源，包括关闭 ResultSet、Statement 和 Connection 等资源
示例：
import java.sql.*;
public class ConnMySql
{
public static void main(String[] args) throws Exception
{
//1.加载驱动，使用反射的知识，现在记住这么写。
Class.forName("com.mysql.jdbc.Driver");
//2.使用DriverManager获取数据库连接,
//其中返回的Connection就代表了Java程序和数据库的连接
//不同数据库的URL写法需要查驱动文档知道，用户名、密码由DBA分配
Connection conn = DriverManager.getConnection(
"jdbc:mysql://127.0.0.1:3306/select_test" ,
"root" , "32147");
//3.使用Connection来创建一个Statment对象
Statement stmt = conn.createStatement();
//4.执行SQL语句。
/*
Statement有三种执行sql语句的方法：
1 execute 可执行任何SQL语句。- 返回一个boolean值，
如果执行后第一个结果是ResultSet，则返回true，否则返回false
2 executeQuery 执行Select语句 － 返回查询到的结果集
3 executeUpdate 用于执行DML语句。－ 返回一个整数，代表被SQL语句影响的记录条数
*/
ResultSet rs = stmt.executeQuery("select s.* , teacher_name from student_table s , "
+ "teacher_table t where t.teacher_id = s.java_teacher");
//ResultSet有系列的getXxx(列索引 | 列名)，用于获取记录指针指向行、特定列的值
//不断地使用next将记录指针下移一行，如果依然指向有效行，则指针指向行的记录
while(rs.next())
{
System.out.println(rs.getInt(1) + "\t"
+ rs.getString(2) + "\t"
+ rs.getString(3) + "\t"
+ rs.getString(4));
}
if (rs != null)
{
rs.close();
}
if (stmt != null)
{
stmt.close();
}
if (conn != null)
{
conn.close();
}
}
}
四、执行 SQL 语句的方式
1、使用 executeUpdate 执行 DDL 和 DML 语句
示例：
mysql.ini:
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/select_test
user=root
pass=32147
ExecuteDDL:
import java.sql.*;
import java.util.*;
import java.io.*;
public class ExecuteDDL
{
private String driver;
private String url;
private String user;
private String pass;
Connection conn;
Statement stmt;
public void initParam(String paramFile)throws Exception
{
//使用Properties类来加载属性文件
Properties props = new Properties();
props.load(new FileInputStream(paramFile));
driver = props.getProperty("driver");
url = props.getProperty("url");
user = props.getProperty("user");
pass = props.getProperty("pass");
}
public void createTable(String sql)throws Exception
{
try
{
//加载驱动
Class.forName(driver);
//获取数据库连接
conn = DriverManager.getConnection(url , user , pass);
//使用Connection来创建一个Statment对象
stmt = conn.createStatement();
//执行DDL,创建数据表
stmt.executeUpdate(sql);
}
//使用finally块来关闭数据库资源
finally
{
if (stmt != null)
{
stmt.close();
}
if (conn != null)
{
conn.close();
}
}
}
public static void main(String[] args) throws Exception
{
ExecuteDDL ed = new ExecuteDDL();
ed.initParam("mysql.ini");
ed.createTable("create table jdbc_test "
+ "( jdbc_id int auto_increment primary key, "
+ "jdbc_name varchar(255), "
+ "jdbc_desc text);");
System.out.println("---------建表成功--------");
}
}
使用 executeUpdate 执行 DML 与执行 DDL 基本相似，区别是 executeUpdate 执行 DDL 返回 0，而执行 DML 后返回受影响的记录条数。
示例：ExecuteDML
import java.sql.*;
import java.util.*;
import java.io.*;
public class ExecuteDML
{
private String driver;
private String url;
private String user;
private String pass;
Connection conn;
Statement stmt;
public void initParam(String paramFile)throws Exception
{
//使用Properties类来加载属性文件
Properties props = new Properties();
props.load(new FileInputStream(paramFile));
driver = props.getProperty("driver");
url = props.getProperty("url");
user = props.getProperty("user");
pass = props.getProperty("pass");
}
public int insertData(String sql)throws Exception
{
try
{
//加载驱动
Class.forName(driver);
//获取数据库连接
conn = DriverManager.getConnection(url , user , pass);
//使用Connection来创建一个Statment对象
stmt = conn.createStatement();
//执行DML,返回受影响的记录条数
return stmt.executeUpdate(sql);
}
//使用finally块来关闭数据库资源
finally
{
if (stmt != null)
{
stmt.close();
}
if (conn != null)
{
conn.close();
}
}
}
public static void main(String[] args) throws Exception
{
ExecuteDML ed = new ExecuteDML();
ed.initParam("mysql.ini");
int result = ed.insertData("insert into jdbc_test(jdbc_name,jdbc_desc)"
+ "select s.student_name , t.teacher_name "
+ "from student_table s , teacher_table t "
+ "where s.java_teacher = t.teacher_id;");
System.out.println("------系统中共有" + result + "条记录受影响------");
}
}
2、使用 execute 方法执行 SQL 语句
Statement 的 execute 方法几乎可以执行任何 SQL 语句，但它执行 SQL 语句时比较麻烦，通常使用 executeQuery 和 executeUpdate 更合适。但如果再不清楚 SQL 语句的类型时，则只能使用 execute 方法。
当使用 execute 方法执行了 SQL 语句后返回值只是 boolean 值，表明执行该 SQL 语句是否返回了 ResultSet 对象，如果返回了 ResultSet 对象，则可以使用 Statement 提供的如下两个方法获取结果集：
- getResultSet()
- getUpdateCount()
示例：ExecuteSQL
import java.sql.*;
import java.util.*;
import java.io.*;
public class ExecuteSQL
{
private String driver;
private String url;
private String user;
private String pass;
Connection conn;
Statement stmt;
ResultSet rs;
public void initParam(String paramFile)throws Exception
{
//使用Properties类来加载属性文件
Properties props = new Properties();
props.load(new FileInputStream(paramFile));
driver = props.getProperty("driver");
url = props.getProperty("url");
user = props.getProperty("user");
pass = props.getProperty("pass");
}
public void executeSql(String sql)throws Exception
{
try
{
//加载驱动
Class.forName(driver);
//获取数据库连接
conn = DriverManager.getConnection(url , user , pass);
//使用Connection来创建一个Statment对象
stmt = conn.createStatement();
//执行SQL,返回boolean值表示是否包含ResultSet
boolean hasResultSet = stmt.execute(sql);
//如果执行后有ResultSet结果集
if (hasResultSet)
{
//获取结果集
rs = stmt.getResultSet();
//ResultSetMetaData是用于分析结果集的元数据接口
ResultSetMetaData rsmd = rs.getMetaData();
int columnCount = rsmd.getColumnCount();
//迭代输出ResultSet对象
while (rs.next())
{
//依次输出每列的值
for (int i = 0 ; i < columnCount ; i++ )
{
System.out.print(rs.getString(i + 1) + "\t");
}
System.out.print("\n");
}
}
else
{
System.out.println("该SQL语句影响的记录有" + stmt.getUpdateCount() + "条");
}
}
//使用finally块来关闭数据库资源
finally
{
if (rs != null)
{
rs.close();
}
if (stmt != null)
{
stmt.close();
}
if (conn != null)
{
conn.close();
}
}
}
public static void main(String[] args) throws Exception
{
es.initParam("mysql.ini");
System.out.println("------执行删除表的DDL语句-----");
es.executeSql("drop table if exists my_test");
System.out.println("------执行建表的DDL语句-----");
es.executeSql("create table my_test"
+ "(test_id int auto_increment primary key, "
+ "test_name varchar(255))");
System.out.println("------执行插入数据的DML语句-----");
es.executeSql("insert into my_test(test_name) "
+ "select student_name from student_table");
System.out.println("------执行查询数据的查询语句-----");
es.executeSql("select * from my_test");
}
}
3、使用 PreparedStatement 执行 SQL 语句
JDBC 的 PreparedStatement 接口可以用来执行使用问号占位符的 SQL 语句，PreparedStatement 接口是 Statement 接口的子接口，它可以预编译 SQL 语句，预编译后的 SQL 语句被存储在 PreparedStatement 对象中，然后可以使用该对象多次高效地执行该语句，所以 PreparedStatement 比使用 Statement 的效率高。
创建 PreparedStatement 对象使用 Connection 的 prepareStatement 方法，该方法需要传入一个 SQL 字符串，该字符串可以包含占位符参数：
// 创建一个 PreparedStatement 对象
pstmt = conn.prepareStatement("insert into student_table values(null, ?, 1)");
PreparedStatement 提供了系列的 setXxx(int index, Xxx value) 方法来传入参数值。
示例：
import java.sql.*;
import java.util.*;
import java.io.*;
public class PreparedStatementTest
{
private String driver;
private String url;
private String user;
private String pass;
Connection conn;
Statement stmt;
PreparedStatement pstmt;
public void initParam(String paramFile)throws Exception
{
//使用Properties类来加载属性文件
Properties props = new Properties();
props.load(new FileInputStream(paramFile));
driver = props.getProperty("driver");
url = props.getProperty("url");
user = props.getProperty("user");
pass = props.getProperty("pass");
}
public void insertUseStatement()throws Exception
{
long start = System.currentTimeMillis();
try
{
//使用Connection来创建一个Statment对象
stmt = conn.createStatement();
//需要使用100条SQL语句来插入100条记录
for (int i = 0; i < 100 ; i++ )
{
stmt.executeUpdate("insert into student_table values(null,'姓名"
+ i + "',1)");
}
System.out.println("使用Statement费时:" + (System.currentTimeMillis() - start));
}
//使用finally块来关闭数据库资源
finally
{
if (stmt != null)
{
stmt.close();
}
}
}
public void insertUsePrepare()throws Exception
{
long start = System.currentTimeMillis();
try
{
//使用Connection来创建一个PreparedStatment对象
pstmt = conn.prepareStatement("insert into student_table values(null,?,1)");
//100次为PreparedStatement的参数设置，就可以插入100条记录
for (int i = 0; i < 100 ; i++ )
{
pstmt.setString(1 , "姓名" + i);
pstmt.executeUpdate();
}
System.out.println("使用PreparedStatement费时:" + (System.currentTimeMillis() - start));
}
//使用finally块来关闭数据库资源
finally
{
if (pstmt != null)
{
pstmt.close();
}
}
}
//定义打开连接的方法
public void getConn()throws Exception
{
if (conn == null)
{
//加载驱动
Class.forName(driver);
//获取数据库连接
conn = DriverManager.getConnection(url , user , pass);
}
}
//定义关闭连接的方法
public void closeConn()throws Exception
{
if (conn != null)
{
conn.close();
}
}
public static void main(String[] args) throws Exception
{
PreparedStatementTest pt = null;
try
{
pt = new PreparedStatementTest();
pt.initParam("mysql.ini");
pt.getConn();
pt.insertUseStatement();
pt.insertUsePrepare();
}
finally
{
pt.closeConn();
}
}
}
4、管理结果集
1）可滚动、可更新的结果集
以默认的方式打开的 ResultSet 是不可更新的，如果希望创建可更新的 ResultSet，必须在创建 Statement 或 PreparedStatement 的时候传入额外的参数。Connection 在创建 Statement 或 PreparedStatement 时还可传入两个参数。
- resultSetType：控制 ResultSet 的类型，该参数可以是如下三个值：
ResultSet.TYPE_FORWARD_ONLY：该常量控制记录指针只能向前移动
ResultSet.TYPE_SCROLL_INSENSITIVE：该常量控制记录指针可以自由移动（可滚动结果集），但是底层数据的改变不会影响 ResultSet 的内容。
ResultSet.TYPE_SCROLL_SENCITIVE：该常量控制记录指针可以自由移动（可滚动结果集），并且底层数据的改变会影响 ResultSet 的内容。
- ResultSetConcurrency：控制 ResultSet 的并发类型，该参数可接受如下两个值：
ResultSet.CONCUR_READ_ONLY：该常量指示 ResultSet 是只读的并发模式（默认）
ResultSet.CONCUR_UPDATABLE：该常量指示 ResultSet 是可更新的并发模式
下面代码通过这两个参数创建了一个 PreparedStatement 对象，由该对象生成的 ResultSet 对象将是可滚动、可更新的结果集：
// 使用 Connection 来创建一个 PreparedStatement 对象
// 传入控制结果可滚动、可更新的参数
pstmt = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENCITIVE, ResultSet.CONCUR_UPDATABLE);
程序可以通过调用 ResultSet 的 updateXxx(int columnIndex, Xxx value) 来修改记录指针所指记录的特定列的值，最后调用 ResultSet 的 updateRow 来提交修改。
