JAVA-JDBC: (2) 数据库的粗略的CRUD及SQL注入问题

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的优点：

1. 没有SQL注入的问题。

2. Statement会使数据库频繁编译SQL，可能造成数据库缓冲区溢出。

3. 数据库和驱动可以对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)中。