夯基础：涮一遍Java JDBC

JDBC是Java连接数据库的标准，为了兼容大部分数据库，Java提出了JDBC标准，通过这个标准，让各个数据库提供实现支持，这样实现一处编码，处处运行的Java特性。

习惯了ORM框架，却忘记了原本的JDBC，所以我觉得有必要复习来夯实一下基础。

0x00 JDBC 历史

JDBC是Sun公司为了能够让SQL访问统一的一套纯JAVA API设计的一套接口，这种接口是遵循了微软的ODBC API模式。其驱动实现是各家数据库供应商编写的，通过JDBC API可以通过驱动实现数据库通信。

0x01 链接数据库回顾

基本Web常用的数据库都是有供Java链接的驱动，

三层结构

那么如何使用JDBC？
写个Demo

import java.io.FileInputStream;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;

/**
 * Created by zing on 2017/3/7.
 */
public class JDBCDemo {

  private  void testJDBC() throws ClassNotFoundException, IOException, SQLException {
        //注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        //JDBC使用类似URL的数据源描述
        String url = "jdbc:mysql://localhost:3306/demo";//忽略
        //但是我们一般不会直接这样写死。而是使用配置来描述数据源，用户名，密码
        Properties props = new Properties();
        FileInputStream propertiesFile = new FileInputStream("JDBC.properties");
        props.load(propertiesFile);
        propertiesFile.close();

        String DriverStr = props.getProperty("jdbc.Driver");
        String urlStr = props.getProperty("jdbc.url");
        String userName = props.getProperty("jdbc.name");
        String passcode = props.getProperty("jdbc.passworld");

        //打开数据库链接
        Connection connection = DriverManager.getConnection(urlStr,userName,passcode);
        //执行SQL
        Statement sta = connection.createStatement();
        //executeUpdate可以返回数据库更新的行数
        int efactRow = sta.executeUpdate("UPDATE USER SET Permition = 'admin' WHERE username = 'Zing'");
        //executeQuery可以返回一个查询的结果集，这个集合的迭代器略有不同Iterator,没有hasNext方法，初始是，指针在数据前，必须调用next方法才能读取第一行数据
        ResultSet resultSet = sta.executeQuery("SELECT * FROM USER ;");
        while (resultSet.next()){
            //当前行获取第一栏的值，具体类型需要看数据库实现
            resultSet.getString(1);
        }
        //关闭语句
        sta.close();
        //关闭结果集
        resultSet.close();
        //关闭数据库连接
        connection.close();

        /*
        一般情况下，关闭的操作会放在catch语句的finally块中，catch处理数据库异常，finally来关闭连接
        */
    }
}

上面写的是大杂烩，一般会将获取连接抽取成一个方法，异常也会捕获，并在try/catch/finally中的finally块中，关闭数据库连接。

API用法可以看看java.sql.Connection，java.sql.Statement，java.sql.ResultSet，这样，基本的操作就可以了然了。

boolean execute(String sql) throws SQLException;这个方法可以执行任何SQL，返回执行是否成功。

0x02 预编译SQL

PrepareStatement，一个可以让数据库预编译SQL的API。
并不是所有的SQL都是写死的，例如：

  SELECT * FROM UserAccount Where Name =

根据名称来查找用户，这里的名字自然是用户自己定义的，如果用Statement，则应该这么写

public void findUserByName(String name){
  Statement  sta = connection.createStatement();
  String findByName = "SELECT * FROM USER WHERE Name=' "+name+" ';";
  ResultSet resultSet = sta.executeQuery(findByName);
}

如果将name交给普通用户来输入，则没什么问题，但是 如果交给黑客，name他会输入 小明' OR '1' = '1,这样语句拼接后就会变成

SELECT * FROM USER WHERE Name=' 小明' OR '1' = '1';

这一句就会把数据库所有的用户全部查出来了，很严重的注入漏洞，基本就会被脱库了。

所以Java JDBC定义的预编译SQL的API。
上例子：


import java.io.FileInputStream;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;

public class JDBCDemo {

    private void testJDBC() {

        Connection connection = null;
        Statement sta = null;
        ResultSet resultSet = null;
        PreparedStatement preSta = null;
        try {
            connection = getConnection();
            //执行SQL
            
            String findByName = "SELECT * FROM USER WHERE Name=?;";
            preSta = connection.prepareStatement(findByName);
            preSta.setString(1,"Zing");
            resultSet = preSta.executeQuery();


        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            /*
                一般情况下，关闭的操作会放在catch语句的finally块中，catch处理数据库异常，finally来关闭连接
            */
            //关闭语句
            try {
                preSta.close();
                //关闭结果集
                resultSet.close();
                //关闭数据库连接
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }


    }

    public Connection getConnection() throws ClassNotFoundException, IOException, SQLException {
        //注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        //JDBC使用类似URL的数据源描述
        //但是我们一般不会直接这样写死。而是使用配置来描述数据源，用户名，密码
        Properties props = new Properties();
        FileInputStream propertiesFile = new FileInputStream("JDBC.properties");
        props.load(propertiesFile);
        propertiesFile.close();

        String DriverStr = props.getProperty("jdbc.Driver");
        String urlStr = props.getProperty("jdbc.url");
        String userName = props.getProperty("jdbc.name");
        String passcode = props.getProperty("jdbc.passworld");
        return DriverManager.getConnection(urlStr, userName, passcode);

    }
}

顺便重构了之前的代码。
我们用 ?占位，留下可变参数的位置，后来再用setString(int parameterIndex, String x)这个方法将数据填充进SQL，这样，如果参数含有SQL关键字时，就不能通过编译，查不到结果。可以避免SQL注入。

preSta.setString(1,"Zing");表示，在第一个?处设置参数为Zing
当然参数是数字，日期时，可以使用，
void setDouble(int parameterIndex, double x) throws SQLException
setDate(int parameterIndex, java.sql.Date x) throws SQLException;

等方法，根据不同类型设置参数。

0x03 数据库类型与转义

数据库类型和Java类型是有一点不一样的，但是JDBC定义了其中的大部分类型，这里不一一列举

MySQL部分类型对照表，有兴趣可以查一查

JDBC中的转义是为了让Java访问数据库时，得到普遍的支持。一般用于下列特性

  • 时间日期的字面常亮
  • 标量函数调用
  • 存储过程调用
  • 外连接查询
  • LIKE子句中转义字符

数据库的日期转换成Java的日期，是通过ISO8601标准衡定并相互转换的

d表示DATE、t表示TIME、ts表示TIMESTANP

{d '2017-01-22'}
{t '19:30:29'}
{ts '2017-01-22 19:30:29.989'}

标量函数是获取一个数值的函数，一般调用时嵌入标准函数名和参数，这个很少见到有人使用的，就不举例了。

存储过程，是数据库自建的存储方式，不同的数据库存储过程基本不一样，要调用存储过程，需要用call来进行转义

{call PROC01(?,?)}
{call PROC02}

如果你不明白什么存储过程，可以看看数据库相关的资料。

外连接，就是Outter Join，借用核心卷II中的例子

SELECT * FROM {oj Books LEFT OUTER JOIN Publishers ON Books.Publish_ID = Publishers.Publish_ID }

这条语句表示查询找不到出版商的书，相反如果是RIGHT OUTER JOIN则会查询出没有出版书的出版商，如果需要查到全部，则用FULL OUTER JOIN
。这里用转义是因为有些数据库实现不太统一。

Like子句转义，是因为下划线和百分号在Like条件里是特殊的含义，需要用转义来表示

SELECT * FROM User WHERE Name LIKE %!_%ming {escape '!'}

{escape '!'}表示将！定义为转义符号，!_表示字面量下划线

0x04 事务

为了保证数据和业务逻辑的完整性，我们可以将一系列的SQL语句构建成一个事物，当所有语句都顺利执行的时候，事务可以被提交。但是如果中途被阻碍，则数据会被回滚，将数据恢复成执行前的样子。

首先需要关闭数据库自动提交

connection.setAutoCommit(false);

然后根据实际业务执行多条UPDATE INSERT DELETE语句

statement.executeUpdate("SQL1");
statement.executeUpdate("SQL2");
statement.executeUpdate("SQL3");

当所有语句顺利执行后，调用

connection.commit();

如果遇到异常或错误，则可以调用

connection.rollback();

其中JDBC支持事务保存点和批量更新
保存点：将事务的某一阶段设置为保存点后，可以控制回滚时，恢复到这个保存点的数据。从而更加精确的控制回滚操作
批量更新就是将大量数据一次性存入，或修改大量数据时使用的。两个🌰：

statement.executeUpdate("SQL1");
Savepoint step1 = connection.setSavepoint();
statement.executeUpdate("SQL2");
if(something==false){
  connection.rollback(step1);
}

String updateSQL = "……";
statement.addBatch(updateSQL);
while(needUpdate){
  command = "……"+"updateSQL2"
  statement.addBatch(updateSQL);
}
//批量执行
int effectRows = statement.executeBatch();

批量执行中一定不能有查询语句，否则会抛出异常。

0x05 文件查询和存入数据库

不建议这么搞，数据库存入太多大文件会导致数据库庞大，备份和恢复的成本将增加。
在数据库中，二进制大对象称为Blob，字符型大对象为Clob
这里演示一下查询和存储

       //读取
        PreparedStatement preparedStatement01 = connection.prepareStatement("SELECT picture FROM PictureTab WHERE picName=?;");
        preparedStatement01.setString(1,"superman");
        ResultSet rs = preparedStatement01.executeQuery();
        if(rs.next()){
            Blob picBlob = rs.getBlob(1);
            Image pic = ImageIO.read(picBlob.getBinaryStream());
        }

        //存储
        Blob pictureBlob = connection.createBlob();
        int offset = 0;
        OutputStream outStram = pictureBlob.setBinaryStream(offset);
        ImageIO.write(pictureBlob,"PNG",outStram);
        PreparedStatement preparedStatement02 = connection.prepareStatement("INSERT INTO PictureTab VALUE (?,?);");
        preparedStatement02.setString(1, "SuperMan");
        preparedStatement02.setBlob(2,pictureBlob);
        preparedStatement02.executeUpdate();

0x06 其他一些概念

  • 元数据：数据库的结构和表信息等描述数据库结构和组成部分的数据
  • 多结果集：一次查询，使用多个Select SQL语句是，会得到一个多结果集
  • 可滚动结果集：可以向前，向后查询的结果集，之前的只能用Next向后查询，使
Statement stat = Connection.createStatement(ResultSet.TYPE_SCROLL_INSENSTIVE , ResultSet.CONCUR_READ_ONLY )

在获取结果集的时候，会变成一个可滚动集。

  • 获取数据库生成键值statemwnt.getGeneratedKeys();
  • 行集 RowSet接口继承了ResultSet，但不需要长时间占用数据库链接。

love&peace
我的博客：https://micorochio.github.io/
转载请注明出处:https://micorochio.github.io/2017/03/10/basic-of-java-JDBC/。

最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 212,542评论 6赞 493
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 90,596评论 3赞 385
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 158,021评论 0赞 348
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 56,682评论 1赞 284
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 65,792评论 6赞 386
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 49,985评论 1赞 291
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 39,107评论 3赞 410
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 37,845评论 0赞 268
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 44,299评论 1赞 303
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 36,612评论 2赞 327
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 38,747评论 1赞 341
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 34,441评论 4赞 333
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 40,072评论 3赞 317
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 30,828评论 0赞 21
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 32,069评论 1赞 267
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 46,545评论 2赞 362
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 43,658评论 2赞 350

推荐阅读更多精彩内容