1. 数据库的CRUD操作
对数据的增删改查操作变化最大的莫过于对SQL语句的改变、首先我们简单的看下ORACLE中增删改查的SQL语句的简单写法:
1.1 C (Create) 对应 oracle中的 insert语句。
(1)、所有字段都插入:
insert into student values('A001','张三','男','01-5月-05',10);
注释: oracle中默认的日期格式’DD-MON-YY’修改日期的默认格式:
alter session set nls_date_format = 'yyyy-mm-dd'
(2)、插入部分字段:
insert into student(xh,xm,sex) values('2','八路','男‘);
(3)、插入空值:
insert into student(xh,xm,sex,birthday) values('3','Jhone','男',null)
1.2 R(Read) 对应 oracle中的 select 语句。
select查询SQL格式:
select···from···where···group by···having···order by···;
SELECT子句 --指定查询结果集的列
FROM子句 --指定查询来自哪个表或者试图
[WHERE 子句] --指定查询的条件
[GROUP BY 子句] --指定查询结果集的分组的条件
[HAVING 子句] --指定分组或者集合的查询条件
[ORDERBY 子句] --对查询的排列顺序
[UNION 子句] --多个SELET语句组合,得到结果集的并集
(1)、查询所有的用户:
select * from t_user;
★: 在oracle中这里的表名用别名时不能加as关键字 如:
select * from t_user u; 正确
select * from t_user as u; 错误
(2)、查询指定的列:
select username, sex from t_user;
(3)、as给列以别名显示:
select username as 用户名 from t_user;(这里的as关键字可以省略)
(4)、去掉重复的行 distinct:
select distinct username from t_user;
(5)、使用运算符:
select age+10, sex from t_user; (给每个年龄加10岁)
(6)、连接字符串:
select '用户名:' || username from t_user;(Oracle用||做连接字符串操作符);
(7)、where 子句:
select * from t_user where username = '宝宝';
(8)、在什么之间 between….and:
select * from t_user where userid between 9 and 10;
select * from t_user where userid >=9 and userid <=10;
(9)、in匹配集合中的任意值:
select * from t_user where username in('马文涛','宝宝');
(10)、like模糊查询:
select * from t_user where username like '%涛%';
注释 通配符含义:
%:包含零个或者多个任意字符的字符串
_:任意单个字符
?:任意单个字符 (啥时候用?在LIKE子句中怎么用?)
#:表示0-9的数字(啥时候用?在LIKE子句中怎么用?)
[]:指定范围或者集合中的任意单个字符,例如[a-f]表示a~f中的一个字符
(11)、null判断某列为空:
select * from t_user where sex is null;
(这里用is,不能用=,如果要返回不为null的记录就可以用is not null)
(12)、order by排序:ASC: 升序排列(可以省略),DESC: 降序排列:
select u.userid,u.username from t_user u order by u.userid;
select u.userid,u.username from t_user u order by u.userid desc;
(13)、group by分组 (分组了就不能直接返回*,经常和聚合函数count(age)一起使用)
按姓名分组,并统计每组人数:select count(*),username from t_user group by username;
根据多个字段分组: select username,age,count(*) from t_user group by username,age;
注释: group by有一个原则,就是 select 后面的所有列中,没有使用聚合函数的列,必须出现在 group by 后面
(14)、having 过滤分组:
select username from t_user group by username having count(*)>2;
(15)、子查询:
<1>子查询放在select后面,作为其中的一个字段返回。
select u.username,(select d.departname from t_depart d where d.departid = u.departid) from t_user u;
<2>子查询放在from后面,作为一张临时表。
select * from (select username,sex s from t_user where departid=1) where s = '男';
<3>子查询放在where后面,作为条件的一部分。
select * from t_user where departid = (select departid from t_depart where departname = '财务部');
(16)、联合查询:
<1>等值连接(内连接):
select u.username,d.departname from t_user u,t_depart d where u.departid = d.departid;
<2>外连接:即把不满足条件的记录也返回,用个+就行了,
(+)操作符在哪边就代表另外一边不满足联合条件的记录可以被输出。这个感觉不太常用。
select b.book_id,b.book_name from book_info as b,book_click_num as c where b.book_id = c.book_id(+);
1.3 U (Update)对应oracle中的 update 语句
(1)、修改一个字段:
update student set sex ='女' where xh = '2';
(2)、修改多个字段:
update student set set ='男', birthday ='1998-09-19' where xh ='2';
update student set name = '争伟',sex = '男';
(更新时不加条件表中所有行记录的姓名都被修改了!)
1.4 D (Delete) 对应oracle 中的 delete 和 drop 语句
delete from student;
只删除所有记录,表结构还在,写日志可以恢复,速度慢,例如:
第一步 早上工作时候建立一个保存点
savapoint first;
第二步:不小心出错,或者用了delete from student
第三步:回滚
roolback to first;
drop table student; 删除表的结构和数据;
delete from student where xh ='2'; 删除一行记录
truncate table student; 删除表中的所有记录,表的结构还在 但是不写日志,无法找回数据;速度快;
2. CRUD对应JDBC语句:
增、删、改用Statement.executeUpdate来完成,返回整数(匹配的记录数),这类操作相对简单。
查询用Statement.executeQuery来完成,返回的是ResultSet对象,ResultSet中包含了查询的结果;查询相对与增、删、改要复杂一些,因为有查询结果要处理。
3. SQL注入问题:
在SQL中包含 特殊字符 或 SQL的关键字(如:’ or 1 or ‘)时Statement将出现不可预料的结果(出现异常或查询的结果不正确),可用PreparedStatement来解决。
PreperedStatement(从Statement扩展而来)相对Statement的优点:
没有SQL注入的问题。
Statement会使数据库频繁编译SQL,可能造成数据库缓冲区溢出。
数据库和驱动可以对PreperedStatement进行优化(只有在相关联的数据库连接没有关闭的情况下有效)。
具体示例,可参考:sql注射示例
4. CRUD相应的粗略JDBC代码
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JDBCCRUD {
/**
* 应该 int create(Stu student) throw SQLException{}
* 这里简化不封装对象了;
* @return
* @throws SQLException
*/
static int create() throws SQLException{
int numOfResult =0;
Connection conn = null;
PreparedStatement ps = null;
String sql;
try{
//注册驱动建立连接
conn = JDBCUtils.getConnection();
sql = "insert into stu values('S151208', 'wangwu', 'nan', 79, null)";
ps = conn.prepareStatement(sql);
numOfResult = ps.executeUpdate();
}finally{
JDBCUtils.free(null, ps, conn);
}
return numOfResult;
}
static void read(String stuId) throws SQLException{
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
String sql;
try{
//注册驱动建立连接
conn = JDBCUtils.getConnection();
sql = "select * from stu where stuid = ?";
ps = conn.prepareStatement(sql);
ps.setString(1, stuId);
rs = ps.executeQuery();
while(rs.next()){
System.out.println(rs.getObject("STUID")+"\t"
+ rs.getObject("STUNAME")+"\t"
+rs.getObject("SEX"));
}
}finally{
JDBCUtils.free(rs, ps, conn);
}
}
static int update(int score, String stuId) throws SQLException{
int numOfResult =0;
Connection conn = null;
PreparedStatement ps = null;
String sql;
try{
//注册驱动建立连接
conn = JDBCUtils.getConnection();
sql = "update stu set score = ? where STUID= ?";
ps = conn.prepareStatement(sql);
ps.setInt(1, score);
ps.setString(2, stuId);
numOfResult = ps.executeUpdate();
}finally{
JDBCUtils.free(null, ps, conn);
}
return numOfResult;
}
static int delete(String stuId) throws SQLException{
int numOfResult =0;
Connection conn = null;
PreparedStatement ps = null;
String sql;
try{
//注册驱动建立连接
conn = JDBCUtils.getConnection();
sql = "delete from stu where stuid = ?";
ps = conn.prepareStatement(sql);
ps.setString(1, stuId);
numOfResult = ps.executeUpdate();
}finally{
JDBCUtils.free(null, ps, conn);
}
return numOfResult;
}
static void readSQLInject(String stuId) throws SQLException{
Connection conn = null;
Statement st = null;
ResultSet rs = null;
String sql;
try{
//注册驱动建立连接
conn = JDBCUtils.getConnection();
sql = "select * from stu where stuid = '"+stuId+"'";
System.out.println(sql);
st = conn.createStatement();
rs = st.executeQuery(sql);
while(rs.next()){
System.out.println(rs.getObject("STUID")+"\t"
+ rs.getObject("STUNAME")+"\t"
+rs.getObject("SEX"));
}
}finally{
JDBCUtils.free(rs, st, conn);
}
}
public static void main(String[] args) throws SQLException {
int num = create();
System.out.println("num :"+ num);
read("S151208");
int num2 = update(100,"S151201");
System.out.println("num :"+ num2);
delete("S151208");
readSQLInject("' or 1=1 or stuid='");
}
}
注释: 上面涉及到JDBCUtils类 见博客JAVA-JDBC:(1)中。