[java] JDBC数据库编程

JDBC（Java Database Connectivity） Java数据库连接技术

官方JDK中提供的与JDBC有关的API都在java.sql包里面

API（Application Programming Interface）

API是三方库中的一系列类和接口中方法的集合

调用API指的是调用三方库中的代码

 注册mysql的驱动

需要让mysql驱动包中的Driver类发生类加载即可

Class.forName（String“类的路径”）；这个方法是jdk提供的专门用于类加载的方法

参数是类的路径的字符串，必须是全路径“包名+类名”

该方法会抛出一个编译时异常，ClassNotFoundException

Class.forName("com.mysql.jdbc.Driver");

 连接数据库

package com.iweb.test;

import org.junit.Test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class JdbcTest {
    //在程序运行过程中只需要做一次的注册驱动的代码放在静态代码块里面
    static {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            System.out.println("mysql驱动注册成功");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            System.out.println("mysql驱动注册失败");
        }
    }
    @Test
    public void tset01(){
        String url="jdbc://mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8";
        String user="root";
        String password="root";
        try {
            Connection conn=DriverManager.getConnection(url,user,password);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

DriverManager.getConnection()方法建立client-server之间的TCP连接的

有三个参数 1.url连接地址字符串  2.用户名  3.密码

url字符串格式

协议//IP：端口/数据库名称？参数名和参数值

1..（必须有）协议部分“jdbc：//mysql：//”

        Client与Server之间的通信协议是TCP/IP

        传输的内容的协议是jdbc：//mysql：//

2..（必须有）ip：端口“localhost：3306”

        ip是服务端所在的机器的ip地址

        服务端在本地可以写localhost，如果不在本地，必须写地方的ip地址

3.（必须有）数据库名称：“test”

        mysql允许一个数据库服务端创建多个数据仓库

        在建立cs连接的时候必须指定具体连接的仓库名称

        如果指定的仓库名称不存在，连接会失败

4.（可选）连接参数

        每一组参数都是kv结构  k=v  ，k是参数名，v是参数值 

        可以有多组，多组之间用&拼接 

参数名	参数值	说明
useUnicode	true	是否采用Unicode编码集传输数据 如果不设置，默认是拉丁文，那么中文传输可能会乱码
characterEncoding	utf-8	使用Unicode中的utf-8这个版本的字符集
useSSL	false	是否使用SSL证书进行安全验证
serverTimezone		如果客户端所在城市与服务端所在城市的时区不一样，可能会出现时间错乱的情况 可以通过这个参数来设置服务端的时区

 

 

 

 

 

 1.整理JDBC编程的流程（看图进行归纳）

        几个重要的接口和方法要能够说出来

        Class.forName()  加载驱动

        DriverManager.getConnection() 建立连接

        Connection conn 接口类，接收建立的连接

        conn.preparedStatement()预编译SQL语句

PreparedStatement ps  预备役sql语句，返回一个执行对象ps

//增删改用executeUpdate（）方法执行，返回int，代表受影响的行数
 //查询用“executeQuery（）方法执行，返回ResultSet” ，代表查询的结果集

String sql="insert into user(id,name,sex,age) values(?,?,?)";

ps.setObject(1,"张飞");//把sql语句第一个？替换为张飞
 ps.setObject(2,"男");//把sql语句第二个？替换为男
ps.setObject(3,"22");//把sql语句第三个？替换为22

2.整理MySQL服务端的几大模块是如何工作的（看图和博客进行归纳）

        1.连接器模块 - 负责客户端连接，用户名和密码校验，权限分配等

1、负责与客户端的通信，是半双工模式，这就意味着某一固定时刻只能由客户端向服务器请求或者服务器向客户端发送数据，而不能同时进行，其中MySQL在与客户端连接TC/IP的。

2、验证请求用户的账户和密码是否正确，如果账户和密码错误，会报错：Access denied for user 'root'@'localhost' (using password: YES)

3、如果用户的账户和密码验证通过，会在MySQL自带的权限表中查询当前用户的权限。

        2.分析器模块  -词法与语法分析

分析器的主要作用是将客户端发过来的SQL语句进行分析，这将包括预处理与解析过程，在这个阶段会解析SQL语句的语义，并进行关键词和非关键词进行提取、解析，并组成一个解析树。具体的关键词包括不限定于以下：select/update/delete/or/in/where/group by/having/count/limit等。如果分析到语法错误，会直接给客户端抛出异常：“ERROR:You have an error in your SQL syntax.”。

比如：select * from user where userId =1234;

在分析器中就通过语义规则器将select from where这些关键词提取和匹配出来，MySQL会自动判断关键词和非关键词，将用户的匹配字段和自定义语句识别出来。这个阶段也会做一些校验：比如校验当前数据库是否存在user表，同时假如user表中不存在userId这个字段同样会报错：“unknown column in field list.”。

        3.优化器模块  -对你的查询语句做出适当的优化

能够进入到优化器阶段表示SQL是符合MySQL的标准语义规则的并且可以执行的，此阶段主要是进行SQL语句的优化，会根据执行计划进行最优的选择，匹配合适的索引，选择最佳的执行方案。比如一个典型的例子是这样的：

表T，对A、B、C列建立联合索引，在进行查询的时候，当SQL查询到的结果是：select xx where B=x and A=x and C=x，很多人会以为是用不到索引的，但其实会用到，虽然索引必须符合最左原则才能使用，但是本质上，优化器会自动将这条SQL优化为：where A=x and B=x and C=X，这种优化会为了底层能够匹配到索引，同时在这个阶段是自动按照执行计划进行预处理，MySQL会计算各个执行方法的最佳时间，最终确定一条执行的SQL交给最后的执行器。

        4.执行器模块 - 操作存储引擎，读写数据

在执行器的阶段，此时会调用存储引擎的API，API会调用存储引擎，主要有一下存储的引擎，不过常用的还是myisam和innodb：

引擎以前的名字叫做：表处理器（其实这个名字我觉得更能表达它存在的意义）负责对具体的数据文件进行操作，对SQL的语义比如select或者update进行分析，执行具体的操作。在执行完以后会将具体的操作记录到binlog中，需要注意的一点是：select不会记录到binlog中，只有update/delete/insert才会记录到binlog中。而update会采用两阶段提交的方式，记录都redolog中。

 

        5.存储引擎模块    -存储数据

MySQL中的数据用各种不同的技术存储在文件（或者内存）中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。通过选择不同的技术，你能够获得额外的速度或者功能，从而改善你的应用的整体功能。

我们常用的存储引擎一般有一下几种：

MyISAM：拥有较高的插入，查询速度，但不支持事务

InnoDB：支持事务的存储引擎，mysql5.5以后将它设置为默认存储引擎。

BDB：事务型数据库的另一种选择，支持COMMIT和ROLLBACK等其他事务特性

Memory：基于内存的存储引擎，将所有的数据都置于内存中，查询、插入、删除效率极高，是一种空间换时间的思想，不过服务重启会导致数据丢失。

Merge：将一部分的MyISAM表联合成的一个整体，适用于大数据存储。

存储引擎五花八门，所以mysql将它设置成了插拔式结构，提高了mysql的整体灵活性。
 

        6.缓存（为什么被弱化了）

MySQL的缓存主要的作用是为了提升查询的效率，缓存以key和value的哈希表形式存储，key是具体的SQL语句，value是结果的集合。如果无法命中缓存，就继续走到分析器的这一步，如果命中缓存就直接返回给客户端。不过需要注意的是在MySQL的8.0版本以后，缓存被官方删除掉了。之所以删除掉，是因为查询缓存的失效非常频繁，如果在一个写多读少的环境中，缓存会频繁的新增和失效。对于某些更新压力大的数据库来说，查询缓存的命中率会非常低，MySQL为了维护缓存可能会出现一定的伸缩性的问题，目前在5.6的版本中已经默认关闭了，比较推荐的一种做法是将缓存放在客户端，性能大概会提升5倍左右。

3.整理CRUD的语法

-- 查询关键字
-- SELECT 查什么列
-- FROM 从哪张表
-- WHERe  查询条件
-- AND 是并且 ： 条件更加严格，需要同时满足多个
-- OR 是或者： 条件更加宽裕，只需要满足一个
-- ！ 是条件取反
-- BETWEEN AND :区间查询
-- IS NULL  和 IS NOT NULL 空值查询;
-- ORDER BY 排序

-- 内连接与外连接的区别
-- 内连接 INNER JOIN
-- 左边的表连接右边的表，如果左边表的某一行记录在右边表中找不到相匹配的记录
-- 则左边表的该行记录不会出现在最终查询结果中
SELECt * 
   FROM emp e INNER JOIN loan_record l
   ON e.id=l.e_id;

-- 外连接 OUTER JOIN
-- 左外连接 LEFT OUTER JOIN
-- 左边的表连接右边的表，如果左边表的某一行记录在右边表中找不到相匹配的记录
-- 则左边表的该行记录至少显示一次，右边表以NULL填充
SELECt * 
   FROM emp e LEFT OUTER  JOIN loan_record l
   ON e.id=l.e_id;

-- 右外连接 RIGHT OUTER JOIN
-- 右边的表连接左边的表，如果右边表的某一行记录在左边表中找不到相匹配的记录
-- 则右边表的该行记录至少显示一次，左边表以NULL填充
SELECt * 
   FROM emp e RIGHT OUTER  JOIN loan_record l
   ON e.id=l.e_id;

//Insert 语法：insert into 表名【字段名列表】 values（值列表）
        //字段名可以省略，如果省略的话，那么就代表全字段插入
        //可以通过指定字段名列表来插入部分字段的值，没有插入值的字段会插入null值，但是非空字段必须插入值
        //values中值列表的个数和类型必须和字段名列表要一一匹配（个数，类型）
        //自增长的主键id，可以给一个null，自动增长

//Update语法：update 表名 set 字段名=值 【，字段名=值。。。】 where 条件
        //单列更新
        //多列更新
        //update语句可以没有where条件，但是通常情况都是要指定where条件防止全表更新

//Delete语法：delete from 表名 where 条件
        //没有where条件=删除整张表的数据
        //delete 对表结构没有影响，删除指定的数据行
        // trunate  会清空表中所有行，但表结构及其约束。索引等保持不变 ， 快速截断（删除）所有数据
        // drop   会删除表的结构和所有数据行，及其所有依赖的约束，索引等

//select查询语法：select 列名列表 from 表名
        //                              【inner join | left join | right join  表名】
        //                              【on 连表条件】
        //                              where 条件
        //                              group by 分组字段
        //                              having 分组的筛选条件
        //                              limit 起始位置，往后偏移量
        //                              order by 排序字段

4.url连接字符串的组成部分和格式

url="jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8";

有三个参数 1.url连接地址字符串  2.用户名  3.密码

url字符串格式

协议//IP：端口/数据库名称？参数名和参数值

1..（必须有）协议部分“jdbc：mysql：//”

        Client与Server之间的通信协议是TCP/IP

        传输的内容的协议是jdbc：mysql：//

2..（必须有）ip：端口“localhost：3306”

        ip是服务端所在的机器的ip地址

        服务端在本地可以写localhost，如果不在本地，必须写地方的ip地址

3.（必须有）数据库名称：“test”

        mysql允许一个数据库服务端创建多个数据仓库

        在建立cs连接的时候必须指定具体连接的仓库名称

        如果指定的仓库名称不存在，连接会失败

4.（可选）连接参数

        每一组参数都是kv结构  k=v  ，k是参数名，v是参数值 

        可以有多组，多组之间用&拼接 

5.今天写的单元测试重写一遍

        static

//在程序运行过程中只需要做一次的注册驱动的代码放在静态代码块里面
    private static final String URL="jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=false";
    private static final String USER="root";
    private static final String PASSWORD="root";
    private static final String DRIVER="com.mysql.jdbc.Driver";
    private Connection conn=null;
    static {
        try {
            Class.forName(DRIVER);
            System.out.println("mysql驱动注册成功");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            System.out.println("mysql驱动注册失败");
        }
    }

        before

@Before
    public void before(){
        try {
            conn=DriverManager.getConnection(URL,USER,PASSWORD);
            System.out.println("musql已连接");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

        after

@After
    public void after(){
        try {
            conn.close();
            System.out.println("mysql已关闭");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

        test

@Test
    public void test01(){
        //此时已建立数据库连接
        //Insert 语法：insert into 表名【字段名列表】 values（值列表）
        //字段名可以省略，如果省略的话，那么就代表全字段插入
        //可以通过指定字段名列表来插入部分字段的值，没有插入值的字段会插入null值，但是非空字段必须插入值
        //values中值列表的个数和类型必须和字段名列表要一一匹配（个数，类型）
        //自增长的主键id，可以给一个null，自动增长
//        String sql="insert into user values(null,?,?,?,?)";
        String sql="insert into user(name,sex,age) values(?,?,?)";
//        String sql="insert into user(id,name,sex,age) values(?,?,?)";
//        String sql="insert into user values(?,?,?,?)";
        try {
            //预备役sql语句，返回一个执行对象ps
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setObject(1,"家俊");//把sql语句第一个？替换为张飞
            ps.setObject(2,"女");//把sql语句第二个？替换为男
            ps.setObject(3,"18");//把sql语句第三个？替换为22
//            ps.setObject(4,"南邮通达学院");//把sql语句第四个？替换为南邮通达学院
            //把sql语句发送到服务的执行，并接收服务端返回的结果（服务端返回受影响的行数）
            int row = ps.executeUpdate();
            Assert.assertEquals(row,1);//断言row=1

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    @Test
    public void test02(){
        //Update语法：update 表名 set 字段名=值 【，字段名=值。。。】 where 条件
        //单列更新
        //多列更新
        //update语句可以没有where条件，但是通常情况都是要指定where条件防止全表更新
//        String sql="update user set school = ? where id =?";
        String sql="update user set school = ? ";
        try {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setObject(1,"南京信息工程大学");
//            ps.setObject(2,3);
            //更新成功
            int row =ps.executeUpdate();
            System.out.println(row);
//            Assert.assertEquals(row,1);
            Assert.assertEquals(row>=1,true);

        } catch (SQLException e) {
            e.printStackTrace();
        }

    }
    @Test
    public void test03(){
        //Delete语法：delete from 表名 where 条件
        //没有where条件=删除整张表的数据
        //delete 对表结构没有影响，删除指定的数据行
        // trunate  会清空表中所有行，但表结构及其约束。索引等保持不变 ， 快速截断（删除）所有数据
        // drop   会删除表的结构和所有数据行，及其所有依赖的约束，索引等

        String sql="delete from user where id = ? ";
        try {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setObject(1,2);
            int row =ps.executeUpdate();
            //没有id=3的行，并不会执行出错，而是返回受影响的行数为0
            System.out.println(row);
            Assert.assertEquals(row>=1,true);

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    @Test
    public void test04(){
        //select查询语法：select 列名列表 from 表名
        //                              【inner join | left join | right join  表名】
        //                              【on 连表条件】
        //                              where 条件
        //                              group by 分组字段
        //                              having 分组的筛选条件
        //                              limit 起始位置，往后偏移量
        //                              order by 排序字段
        String sql="select * from user";
        try {
            PreparedStatement ps = conn.prepareStatement(sql);
            //增删改用executeUpdate（）方法执行，返回int，代表受影响的行数
            //查询用“executeQuery（）方法执行，返回ResultSet” ，代表查询的结果集
            ResultSet rs = ps.executeQuery();
            //迭代结果集res，取出数据
            if (rs!=null){//如果查询不到任何数据返回的结果集对象rs是null
                while (rs.next()){//判断是否还有下一行数据
                    //根据表中列的类型判断调用getXXX（）方法
                    //根据列的序号columnIndex，作为参数取值
//                    int id = rs.getInt(1);
//                    String name = rs.getString(2);
//                    String sex = rs.getString(3);
//                    int age = rs.getInt(4);
//                    String school = rs.getString(5);
                    //根据列的名字columnLabel，作为参数取值
                    int id = rs.getInt("id");
                    String name = rs.getString("name");
                    String sex = rs.getString("sex");
                    int age = rs.getInt("age");
                    String school = rs.getString("school");
                    System.out.println(id+"t"+name+"t"+sex+"t"+age+"t"+school);
                }
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }