MySQL+JDBC知识点梳理

2017-05-20 本文已影响56人明天你好向前奔跑

MySQL+JDBC回顾

1. MySQL:

1.1 mysql数据库

由DataBase：数据库和DataBase Management System：数据库管理系统组成

一般我们说的数据库指的是存储数据的集合和数据库管理系统。即存储,维护和管理数据。

1.2 sql语句

DDL:DataBase Definition language:用于定义数据库对象：数据库，表，列，create,alter,show,drop
DML:Data Manipulation language:用于对数据库中的记录进行更新，insert,update,delete
DQL:Data Query language：用于查询数据库中表的记录，select,from,where
DCL:Data Control language:用于定义数据库的访问权限和安全级别及创建用户

2 MySQL语句操作

2.1 sql语句数据类型

int ：整型
char ：指定长度的字符串类型
varchar：可变长度的字符串类型
blob：字节类型
date：日期类型
time：时间类型(hh:mm:ss)
timestamp:时间戳类型(yyyy-MM-dd hh:mm:ss) 自动赋值
datetime：日期时间类型
...

2.2 数据库

增
- create database 库名 [character set gbk collate gbk_chiense_ci]；
删
- drop database 库名;
改
- alter database 库名 character set utf8 [collate...]；
查
- show databases；
- show create database 库名；
其它
- use 库名
- select database();

2.3 表格

增
- create table 表名(字段名1 类型 [约束]，字段名2 类型 [约束] ...);
删
- alter table 表名 drop 字段名；
- drop table 表名;
改
- alter table 表名 add 字段名类型 [约束];
- alter table 表名 drop 字段名；
- alter table 表名 modify 字段名类型(长度) [约束];
- alter table 表名 change 旧字段名新字段名类型(长度) [约束];
- alter table 表名 character set 字符集；修改表的字符集
- rename 旧表名 to 新表名;
查
- show tables; 查询所有表
- show create table 表名；查询指定表的建表语句
- desc 表名；查询表结构
- select * from 表名；查询表中的数据

2.4 表中的数据

增
- insert into 表名(字段1，字段2，字段3...) values(值1，值2，值3...);
- insert into 表名 values(所有的字段的值);
删
- delete from 表名 [where]; 一行一行的删除,保留auto_increment值，可恢复
- truncate table 表名：直接删除表，重建表结构，删除auto_inrement值，不可恢复
改
- update 表名 set ... [where]；
查
- select * from 表名；

3 DQL查询语句

3.1 简单查询

select * from 表名 [where]; 查询所有列数据
select 字段1，字段2... from 表名 [where]; 查询指定列的数据
select [distinct] */字段 from 表名 [where]; 去重查询

3.2 条件查询

用于where条件后

,<,>=,<=,<> 基本逻辑运算符
between...and.. 在..和..之间
in 在某些指定的值中
and 及
or 或
like 模糊查询。%,0个或以上。_,一个

3.3 聚合函数

count()：统计个数
max()：最大值
min()：最小值
avg()：平均值
sum()：求和

聚合函数前不能跟字段名，除非是分组的字段

聚合函数不能跟在where条件后，一般用于select语句中筛选或having后

3.4 分组

group by 字段名；按照指定字段名分组，取得该字段的所有值

单独使用没有意义，一般和聚合函数或者group_concat使用

3.5 having

haiving和where的区别：

where用于分组前的条件筛选
having用于分组后的条件筛选

3.6 排序

order by 字段名 [asc/desc]

排序默认asc升序排列

3.7 limit限制

limit m,n :m表示从第几行开始，n表示显示几行数据
分页查询: limt (current-1)*n,n；

sql查询语句顺序：

 select */字段 from 表名 where条件 group by 字段 having条件 order by [asc/desc] limit;

select语句书写顺序：select - from - where - group by - having - order by - limit;

select语句执行顺序：from - where -group by - having - select - order by -limit ；

4 数据完整性

作用：保证用户输入的数据保存到数据库中是正确的。

数据完整性类型：

实体完整性
域完整性
引用完整性

4.1 实体完整性

实体完整性的作用：标识每一行数据不重复

主键约束：primary key(相当于unique和notnull的组合)

  添加主键约束方式：
  1：创建表格字段时添加,例:create table user(id int primary key);
  2:创建表格时字段后添加，例：
      create table user(
          id int,username varchar(40),
          primary key(id,username)
      );
  好处：可以添加联合主键
  3：sql语句添加，例：
      alter table user add constraint pk_id primary key(id);

auto_increment：自动增长列
unique：唯一约束

4.2 域完整性

域完整性的作用：限制此列数据正确

not null：非空约束
default：默认

4.3 引用完整性

要有外键必须先有主键，主键和外键的类型必须一致。

用于添加外表的引用到当前表格。

foreign key：

    
    添加外键的二种方式：
    方式1：创建表格时添加
    create table stu(
        id int,username varchar(50),sex varchar(10),
    );
    create table score(
        id int,score int,sid int,
        constraint fk_score_sid foreign key(sid) references stu(id);
    );

    方式2：sql语句添加
     alter table score add constraint fk_score_sid foreign key(sid) references stu(id);

5 多表查询

5.1 表与表之间的关系

一对多

    
    在多的表格添加外键列，外键类型与主键一致，引用指向一的主键。代表一引用多的表格。

多对多


    创建一个第三方表格，至少有两列作为外键列，分别指向两个表格的主键。

5.2 多表查询

多表查询的类型：

连接查询
子查询
联合查询

5.2.1 连接查询

select * from 表名1,表名2；

连接查询将两个表格的数据笛卡儿积到了一个表格，但是产生了大量的无用数据，因此需要通过条件剔除无用数据得到有用数据。

例：select * from emp,deptno where emp.deptno = deptno.deptno;

内连接


    1：隐式内连接
        select * from emp,deptno where...;

    2：显式内连接
        select * from emp [inner] join deptno on...;
        在左边的表格作为主表，内连接查询条件不符合就不显示。

外连接


    1：左外连接
        select * from emp left [outer] join deptno on...;
        
    2：右外连接
        select * from emp right [outer] join deptno on...;

    外连接查询，主表的表格会显示，如果连接条件不符合，右表显示为null

5.2.2 子查询

即select语句中嵌套select语句

子查询语句出现的位置：

where条件语句中：作为被查询语句的一部分
from后：作为表格

当子查询在where后作为条件时，可以使用any和all作为关键字

5.2.3 联合查询

作用：将两个表格的selct查询结果合并到一起

union：
- 去除重复记录
  - select * from t1 union select * from t2;
unionAll：
- 不去除重复记录
  - select * from t1 unionAll select * from t2;

2 JDBC

2.1 JDBC概述

JDBC是用于规定java代码连接数据库的一种规范。

2.2 JDBC连接数据库的步骤：

准备条件：

数据库驱动

2.2.1 注册驱动

DriverManager类注册驱动，DriverManager.registeDriver(new Driver())

  通过查看源码，发现这种方式new Driver()时还要注册一次驱动，相当于注册两次。

改进为用，Class.forName("com.mysql.jdbc.Driver");加载驱动

2.2.2 获得数据库连接对象

DriverManger.getConnction()获取连接对象


    String url = "jdbc:mysql://localhost:3306/mydb1";
    String user = "root";
    String password = "root";
    Connction connection = DriverManager.getConnection(url,user,password);

2.2.3 创建执行sql语句的对象Statement

Statement

这种方式有数据库注入的隐患,因此改用PreparedStatement来预编译sql语句

PreparedStatement：预编译sql语句对象


    String sql = "select * from star where name = ?"; 
    //预编译sql语句
    PreparedStatement prepareStatement = connection.prepareStatement(sql);
    prepareStatement.setString("宝宝");//设置占位符的值

2.2.4 执行sql语句

* execute()：可执行任意sql语句，返回boolean类型
    * 当且仅当执行select语句并且有返回结果时为true，其余均为faulse
    
* executeUpdate()：执行除select语句以外的sql语句，返回改变行数的int值

* executeQuery()：执行select语句返回结果集
    * //prepareStatement已经进行了预编译，因此执行时不需要再传入sql语句
    * ResultSet resultSet = prepareStatement.executeQuery();

2.2.5 如果有结果集，操作结果集

next()：返回boolean类型，如果是true说明下一行有数据
getObject()
getString()
getInt()
....

while(result.next()) {
    int id = result.getInt("id");
    String username = result.getString("username");
}

2.2.6 释放资源

后执行的先释放(close()方法)。

if(resultSet != null) {
    resultSet.close();
}
if(prepareStatement != null) {
    prepareStatement.close();
}
if(connection != null) {
    connection.close();
}

2.3 加载配置文件

将代码中用的常量存入到配置文件中，这样在修改常量时只需要修改配置文件即可，而不需要修改代码，那么如何加载配置文件到java代码中。

配置文件有properties与xml，格式有严格要求：properties前后不能有空格

IO流加载

配置文件可放于任意位置。

  Properties prop = new Properties(); //可加载流中的数据
  InputStream is = new FileInputStream(new File("src/jdbc.properties"));
  Properties.load(is);
  String url = prop.getProperties("url");
  ......

类加载器

    
    配置文件必须放在项目的src位置，可以读取properties和xml文件。效率高
    Properties prop = new Properties();
    InputStream is = JDBCUtils.class.getClassLoader().getResourceAsStream("jdbc.properties");
    prop.load(is);
    String url = prop.getProperties("url");
    ......

ResourceBundle加载


    配置文件必须放在src目录下，只能读取properties文件，读取时不需properties后缀。效率高。
    ResourceBundle bundle = ResourceBundle.getBundle("jdbc");
    String url = bundle.getString("url");
    ......

3 JDBC连接池

3.1 为什么要有连接池

每一次应用程序申请访问服务器都要获取连接和销毁连接，而这是一个很耗时间和资源的动作，因此通过连接池，先将一部分连接放入连接池中，需要连接使用时从连接池中取出，使用完放回。这样就省去了大量的获取连接和销毁连接的动作，提高了性能。

3.2 自定义连接池

模拟连接池的取出放入方式，用LinkedList存放连接
事先放入部分连接至连接池
给出getConnction方法和close()方法。
- 这里的close方法要区分要close的连接的来源，从而进行不同的close，因此需要用装饰者模式对连接池中的连接进行包装

自定义简易连接池

List<Connection> pool = new LinkedList<>();
static {
    for(int i=0;i<5;i++) {
        pool.add(JDBCUtils.getConnection);
    }
}

public static Connection getConnction() {
    if(pool.size()>0) {     //这篇总结是直接写在MD中的，因此都忽略了try、catch
        return pool.removeFirst();
    }else {
        return JDBCUtils.getConnection();
    }
}

public static void close(Connection connection) {
    pool.addLast(connection);
}

这里将连接放回连接池，但是要解决一个问题，如何区分是连接池的连接还是新创建的池子，使得池子中拿出的Connection放回去。其他的Connection直接销毁。因此，需要对连接池中初始化的Conneciton进行包装。

3.3 装饰者模型

目的：改写已存在的类的某个方法或某些方法，装饰设计模式（包装模式）

条件:1.包装类和被包装类实现的是同一个接口
    2.包装类里面要拿到被包装类的引用

实际上就是将要包装的这个接口作为参数传递到另一个实现相同接口的实现类中，这样这个实现类就具有和其意义的功能，将想要重写的方法重写即可。

3.4 常用的连接池DataSource

我们完成了自定义的连接池，但是java为了统一连接池的书写规范，提供了一个接口DataSource，数据库厂商编写的连接池都必须实现DataSource接口，这样连接池的方法和命名就有了统一的规范，这也使得应用程序能够方便的切换连接池使用，也让程序员的工作大大减轻。

3.4.1 常用连接池DBCP

DBCP：Apache推出的 DataBase Connection Pool。数据库连接池。

目的：使用DBCP创建连接池，getConnection()，release()，getDataSource()方法

准备工作：

数据库驱动包。
两个DBCP的jar包，dbcp和pool.jar
配置文件，DBCP的配置文件有命名，和书写都严格的格式。
根据配置文件编写DBCPUtils工具包

DBCP的两个核心对象

BasicDataSource
BasicDataSourceFactory
- BasicDataSourceFactory(加载的配置文件Properties)


    private static DataSource ds;
    static {
        Properties prop = new Properties();
        InputStream is = DBCPUtils.class.getClassLoader().getResourceAsStream("dbcpconfig.properties");
        try {
            prop.load(is);
            ds = BasicDataSourceFactory.createDataSource(prop);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    
    //获取连接的方法
    public static Connection getConenction() {
        Connection connection = null;
        try {
            connection = ds.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    } 
    
    //释放资源的方法
    public static void release(ResultSet resultSet,Statement statement,Connection connection) {
        if(resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        
        if(statement != null) {
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        
        if(connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    
    //获得连接池的方法
    public static DataSource getDataSource() {
        return ds;
    }

3.4.2 常用连接池C3P0

C3P0开源免费的连接池！目前使用它的开源项目有：Spring、Hibernate等。

目的：使用C3P0创建连接池，提供getConnection()，release()，getDataSource()方法

准备工作：

数据库驱动*.jar
C3P0的jar包。
C3P0的配置文件。c3p0-config.xml
1. 有严格的要求，以c3p0-config.xml命名放入src目录下，C3P0默认解析配置文件
根据配置文件编写C3P0Utils工具类

C3P0的核心对象：

ComboPooledDataSource():自动解析默认的配置文件


    public class C3P0Utils {
            //创建连接池
            private static DataSource ds = new ComboPooledDataSource();
            
            //获的连接对象Connection
            public static Connection getConnection() {
                Connection connection = null;
                try {
                    connection = ds.getConnection();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                return connection;
            }
            
            //释放资源
            public static void release(ResultSet resultSet,Statement statement,Connection connection) {
                if(resultSet != null) {
                    try {
                        resultSet.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
                
                if(statement != null) {
                    try {
                        statement.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
                
                if(connection != null) {
                    try {
                        connection.close();  //底层包装了连接池中的Conenction对象,直接close放回连接池，新建的就删除
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
            }
        }

4 DBUtils

DBUtils是java编程中的数据库操作实用工具，小巧简单实用。第一个操作数据库框架(jar),
DBUtils封装了JDBC的操作，简化了JDBC操作，可以少写很多代码

DBUtils三个核心功能的介绍：

 DBUtils类：
     它就是一个工具类，定义了关闭资源和事务处理的方法
 
 QueryRunner类：
     提供了对sql语句操作的方法，query():查询，update():修改

 ResultSetHandler接口:
     用于定义select操作后，如何封装结果集

4.1 QueryRunner类

QueryRunner是执行sql代码的对象

new QueryRunner(DataSource ds)：提供数据源(连接池)DataSource,DBUtils底层自动维护和连接Connection数据库
update()：queryRunner执行select语句以外的sql语句。里面传入两个参数
query()：queryRunner执行select语句，里面传入三个参数：要执行的sql语句，对结果要进行的操作，占位符数组

4.2 ResultSetHandler接口

| Handler类型          | 说明                                     |
| ------------------- | ---------------------------------------- |
| **BeanHandler**     | 将结果集中第一条记录封装到一个指定的javaBean中。             |
| **BeanListHandler** | 将结果集中每一条记录封装到指定的javaBean中，将这些javaBean在封装到List集合中 |
| **ScalarHandler**   | 它是用于单个数据。例如select  count(*) from 表操作。    |