学习MySql数据库和 JDBC

关系型数据库

一、终端登录数据库

一天学会mysql

配置环境变量略

SQL语法

登陆：>mysql -uroot -p********

-u是usr的意思，-p是psw

查询：mysql> show databases; mysql> show tables;

选中/查询：use xxx mysql> select * from admin

退出：mysql> exit

创建数据库：mysql> create database xxx ``

创建数据表(example)：CREATE TABLE pet(name VARCHAR(20),owner VARCHAR(20),sex CHAR(1),birth DATE, death DATE);

描述数据表：describe pet/desc pet;

![截屏2021-03-26 上午9.00.29](/Users/boxizhou/Desktop/md/img/截屏2021-03-26 上午9.00.29.png)

添加数据：INSERT INTO pet VALUES('Puffball','Diane','f','1999-03-30',NULL)

![截屏2021-03-26 上午9.07.26](/Users/boxizhou/Desktop/md/img/截屏2021-03-26 上午9.07.26.png)

删除数据：delete from pet where name='Puffball' (两条都被删掉)

修改数据：update pet set name='Bufferball' where name='Puffball'

增：INSERT

删：DELETE

改：UPDATE

查：SELECT

数据类型：（菜鸟文档）

数值（溢出会报错）
日期/时间
字符串/字符

约束类型

主件约束
自增约束
唯一约束
非空约束
默认约束
外键约束

添加约束

建表的时候添加约束

alter 。。。 add 。。。添加约束

alter。。。modify。。。

删除约束

alter 。。。 drop。。。

外键约束

– 涉及到两个表：主表，副表

e.g.

主表

create table classes(
    id int primary key,
    name varchar(20)
);

副表

create table student(
    id int primary key,
  name varchar(20),
  class_id int,
  foreign key(class_id) references classes(id)
);

⬆️student 表里的 class_id 必须来自于 classes 表中的 id 字段

1 主表 classes 中没有的数据值 ID ，在副表 student 中 class_id 是不可以使用的

![](/Users/boxizhou/Desktop/md/img/截屏2021-03-27 下午3.30.27.png)

2 主表中的记录被副表引用，是不可以被删除的

![](/Users/boxizhou/Desktop/md/img/截屏2021-03-27 下午3.29.24.png)

数据表设计范式

第一范式 1NF

字段尽量拆分，数据原子化储存

第二范式 2NF

满足 1NF 前提下，要求除了主建外每一列都必须完全依赖于主键

第三范式 3NF

满足第二范式前提下，除主建外其他列不能有传递依赖关系

数据查询

典中典：select * from xxx;

查询特定字段 select sno,cno,degree from student

排除重复字段 distinct select distinct * from student

区间查询 where …between ..and .. select * from score where degree between 60 and 80

select * from score where degree > 60 and degree < 80

“in或者“关系查询 inselect * from score where degree in(85,86,88)(查询score表中成绩为85 86 88的记录)

“or或者”关系查询 or：select * from student where class='95031' or ssex='女';（查询班级是95031 或者性别是女的记录）

升序 asc、降序 desc|order by xxx asc/desc：select * from student order by clss desc ;

统计个数 count（）：select count(*) from student where class='95031';

二、JDBC ( Java DataBase Connectivity)

~~电子科技大学~~❌尚硅谷大学✅

JDBC抽象

一、测试数据库连接

方式一：一步步来

properties类

关于url：

jdbc:mysql是协议类型

3306是mysql端口号

test指其中的名为 test 的数据库

@Test
    public void connectionTest1() throws SQLException {
                //1. 获取driver的实现类对象
        Driver driver = new com.mysql.jdbc.Driver();
                //2. 连接路径
        String url = "jdbc:mysql://localhost:3306/test";
          //3. 这个properties用于封装用户名和密码
        Properties info = new Properties();
          info.setProperty("user","root");
        info.setProperty("password","ZHOUboxi");
                //4. 点火起飞
        Connection conn = driver.connect(url,info);

          System.out.println(conn);
    }

![截屏2021-03-27 下午8.19.08](/Users/boxizhou/Desktop/md/img/截屏2021-03-27 下午8.19.08.png)

拿到了数据库，操作成功！

另外那一串红色的提示说com.mysql.jdbc.Driver不推荐使用，最新的是com.mysql.chj.Driver

方式二：反射 增加了可移植性

@Test
    public void connectionTest2() throws SQLException, ClassNotFoundException, IllegalAccessException, InstantiationException {
        //1。获取Driver的实现类对象
        Class clazz = Class.forName("com.mysql.jdbc.Driver");
        Driver driver = (Driver)clazz.newInstance();
        //2.提供数据库地址
        String url = "jdbc:mysql://localhost:3306/test";
        //3.提供用户名密码
        Properties info = new Properties();
        info.setProperty("user","root");
        info.setProperty("password","ZHOUboxi");
        //4.获取连接
        Connection conn = driver.connect(url,info);

        System.out.println(conn);
    }

方式三：使用DriverManager替换Driver

@Test
    public void connectionTest3() throws SQLException, ClassNotFoundException, IllegalAccessException, InstantiationException {
        //1.注册驱动
        Class clazz = Class.forName("com.mysql.jdbc.Driver");
        Driver driver = (Driver)clazz.newInstance();
        //2.提供三个基本信息
        String url = "jdbc:mysql://localhost:3306/test";
        String user = "root";
        String pswd = "ZHOUboxi";
        //3.注册驱动管理
        DriverManager.registerDriver(driver);
        //4.获取连接
        Connection conn = DriverManager.getConnection(url,user,pswd);

        System.out.println(conn);
    }

方式四：优化

@Test
    public void connectionTest4() throws SQLException, ClassNotFoundException, IllegalAccessException, InstantiationException {
        //1.加载驱动
/*        Class clazz = **/ Class.forName("com.mysql.jdbc.Driver");
//        Driver driver = (Driver)clazz.newInstance();
        //2.提供三个基本信息
        String url = "jdbc:mysql://localhost:3306/test";
        String user = "root";
        String pswd = "ZHOUboxi";
//        //3.注册驱动管理
//        DriverManager.registerDriver(driver);
        //4.获取连接
        Connection conn = DriverManager.getConnection(url,user,pswd);

        System.out.println(conn);
    }

和三相比，没有去注册driver，因为在加载“com.mysql.jdbc.Driver”类的时候，自动加载了Driver

Driver实现类代码如下：

![截屏2021-03-27 下午9.18.13](/Users/boxizhou/Desktop/md/img/截屏2021-03-27 下午9.18.13.png)

看看这个static代码块，随着类的加载而加载

方式五：最终版本，使用配置文件

@Test
    //todo：把4个基本信息放在配置文件中，读取配置文件获取连接
    public void connectionTest5() throws IOException, ClassNotFoundException, SQLException {
        //1。读取配置文件信息
        InputStream is = ConnectionTest.class.getClassLoader().getResourceAsStream("jdbc.properties");

        Properties pros = new Properties();
        pros.load(is);

        String user = pros.getProperty("user");
        String password = pros.getProperty("password");
        String url = pros.getProperty("url");
        String driverClass = pros.getProperty("driverClass");
        //2.加载驱动
        Class.forName(driverClass);
        //3.获取连接
        Connection conn = DriverManager.getConnection(url,user,password);

        System.out.println(conn);
    }

配置文件：jdbc.properties

#获取4个基本信息
user=root
password=ZHOUboxi
url=jdbc:mysql://localhost:3306/test
driverClass=com.mysql.jdbc.Driver

好就好在把代码无关的配置解耦合了，以后修改时不需要重新打包

二、PreparedStatement对数据库进行CRUD操作

三体文明发来信息：不要用Statement！不要用Statement！！不要用Statement！！！

因为可能会出现sql注入问题，使用更先进的，有预编译功能的PreparedStatement

statement翻译为“信使”

实际上一个数据库连接就是一个Socket连接

为了方便已经把connection操作和最后的资源回收操作封装在JDBCutils类中

INSERT

//todo:增加一条数据
    @Test
    public void insertTest(){
        Connection conn = null;
        PreparedStatement ps = null;
        try {
              //1.建立连接
            conn = JDBCutils.getConnection();
            //2.预编译sql语句，返回PreparedStatement实例
            String sql = "insert into user_table(user,password) values(?,?)";// 其中"?"是占位符
            ps = conn.prepareStatement(sql);
            //3.填充占位符
            ps.setString(1, "耐克");
            ps.setString(2,"gaisi");
            //4.执行sql操作
            ps.execute();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
          //5.关闭Statement和连接
            JDBCutils.closeResuorses(conn,ps);
        }

    }

DELETE、UPDATE操作类似不再赘述

下面把增删改放在一起实现

JAVA可变形参的使用：数据类型 ...形参名

只要在形参的数据类型与参数名之间加上三个“.”，就可以让他和个数不确定的实参想匹配。不过有一点需要注意，每个方法只能有一个这样的参数个数可变的形参，且这个形参必须是形参列表的最后一个

>public class TestAgrs {
   public static void main(String[] args) {
       TestAgrs t = new TestAgrs();
       t.sayHello1("g","f","fg");
   }

   //如下三个方法构成重载
   public void sayHello() {
       System.out.println("hello  word");
   }

   public void sayHello(String str) {
       System.out.println("hello" + str);
   }

   public void sayHello1(String... args) {
       for (int i = 0; i < args.length; i++) {
           System.out.println(args[i]);
       }
   }

   public void sayHello(String[] args) {
       for (int i = 0; i < args.length; i++) {
           System.out.println(args[i]);
       }
   }
>}

结果

1
2
3

>g
>f
>fg

增删改 UPDATE 针对于user_table的通用化操作

//todo：通用的增删改操作
    @Test
    public void update(String sql, Object ...args){
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            //1.获取连接
            conn = JDBCutils.getConnection();
            //2.预编译sql语句，返回PreparedStatement实例
            ps = conn.prepareStatement(sql);
            //3.填充占位符
            for(int i = 0; i < args.length;i++){
                ps.setObject(i+1,args[i]);
            }
            //4.执行sql操作
            ps.execute();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //5.关闭Statement和Connection
            JDBCutils.closeResuorses(conn,ps);
        }
    }

SELECT

//todo:查询操作
    @Test
    public void selectTest(){
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            //1.获取连接
            conn = JDBCutils.getConnection();
            //2.预编译sql语句，返回PreparedStatement实例
            String sql = "select * from user where user=?";
            ps = conn.prepareStatement(sql);
            //3.填充占位符
            ps.setObject(1,"ZHA");
            //4.执行sql操作,并且返回结果集
            ResultSet resultSet = ps.executeQuery();
            //5.处理结果集
            if(resultSet.next()){//判断结果集下一条是否有数据，如果有，获取字段值
                int id = resultSet.getInt(1);
                String user = resultSet.getString(2);
                String password = resultSet.getString(3);
//                System.out.println("id="+id+",user="+user+",password="+password);
                User user1 = new User(id,user,password);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //6.关闭Statement和Connection
            JDBCutils.closeResuorses(conn,ps);
        }
    }

ORM编程思想

一个表对应一个Java类

表中的一条记录对应一个对象

一个字段对应一个属性

SELECT 针对于user_table表的通用化操作

//todo:查询操作通用化
    @Test
    public void testCommonSelect(){
        String sql = "select * from user_table where user=?";
        User myUser = commonSelect(sql,"ZHA");
        System.out.println("id="+myUser.getId()+",user="+myUser.getUser()+",password="+myUser.getPassword());
    }

    public User commonSelect(String sql,Object ...args){
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        try {
            //1.获取连接
            conn = JDBCutils.getConnection();
            //2.预编译sql语句，返回PreparedStatement实例
            ps = conn.prepareStatement(sql);
            //3.填充占位符
            for(int i = 0;i < args.length; i++){
                ps.setObject(i+1,args[i]);
            }
            //4.执行sql操作,并且返回结果集
            resultSet = ps.executeQuery();
            //5.处理结果集
                //获取结果集的元数据(metaData,元数据，即"修饰数据的数据"
            ResultSetMetaData metaData = resultSet.getMetaData();
                //通过metaData获取结果集列数（字段数）
            int columnCount = metaData.getColumnCount();
            if(resultSet.next()){//判断结果集下一条是否有数据，如果有，获取字段值

                User user2 = new User();
                //处理一行结果集中的每一列
                for (int i = 0; i < columnCount; i++){
                    Object columnValue = resultSet.getObject(i + 1);

                    //获取每一列的列名（字段名）
                    String columnName = metaData.getColumnName(i + 1);

                    //todo:难点。给user对象对应字段的属性赋值（通过反射***
                    Field field = User.class.getDeclaredField(columnName);
                    field.setAccessible(true);
                    field.set(user2,columnValue);
                }
                return user2;
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //6.关闭Statement和Connection
            JDBCutils.closeResuorses(conn,ps,resultSet);
        }
        return null;
    }

注意 NoSuchFeildException ，即数据库中列名和 JavaBean 不一样

![截屏2021-03-31 下午10.19.22](/Users/boxizhou/Desktop/md/img/截屏2021-03-31 下午10.19.22.png)

此时可以在结果集中给列取别名

举个例子：

数据库中列名为order_id,但是Java类属性是orderId,直接反射会报错。
1
2
SELECT order_id orderId,order_name oderName, order_date orderDate
FROM 'order' WHERE id=1;
order表中列名本来是 “order_id”,但是返回的结果集中字段却为 “orderId”

不过还没结束
1
2
//获取每一列的列名（字段名）
                    String columnName = metaData.getColumnName(i + 1);
这一条代码是直接从数据库中读列名，我们要获取列的“别名”
1
2
//获取每一列的列名（字段名）
                    String columnName = metaData.getColumnLable(i + 1);
getColumnLable()可以从结果集中获取列的别名,没有别名时就是原名。

小结

![截屏2021-03-31 下午10.32.56](/Users/boxizhou/Desktop/md/img/截屏2021-03-31 下午10.32.56.png)

SELECT针对不同表的通用查询操作

大的要来了

针对所有表的通用化主要就是类的反射、泛型使用

@Test
    /**
     * 针对所有表的通用化主要就是类的反射、泛型使用
     *
     *
     */
    //todo:查询操作针对不同表的通用化
    public void testCommonSelect(){
        String sql = "select id,user,password from user_table where id>?";
        ArrayList<User> users = commonSelect(User.class,sql,1);
        users.forEach(System.out::println);
    }
    public <T> ArrayList<T> commonSelect(Class<T> clazz, String sql, Object...args){//泛型参数
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        try {
            //1.获取连接
            conn = JDBCutils.getConnection();
            //2.预编译sql语句，返回PreparedStatement实例
            ps = conn.prepareStatement(sql);
            //3.填充占位符
            for(int i = 0;i < args.length; i++){
                ps.setObject(i+1,args[i]);
            }
            //4.执行sql操作,并且返回结果集
            resultSet = ps.executeQuery();
            //5.处理结果集
            //获取结果集的元数据(metaData,元数据，即"修饰数据的数据"
            ResultSetMetaData metaData = resultSet.getMetaData();
            //通过metaData获取结果集列数（字段数）
            int columnCount = metaData.getColumnCount();
              //创建ArrayList存储对象
            ArrayList<T> list = new ArrayList<T>();
            while(resultSet.next()){//判断结果集下一条是否有数据，如果有，获取字段值

//                User user2 = new User();
                T t = clazz.newInstance();
                //处理一行结果集中的每一列
                for (int i = 0; i < columnCount; i++){
                    //获取这一列的内容
                    Object columnValue = resultSet.getObject(i + 1);

                    //获取每一列的列名（字段名）
                    String columnName = metaData.getColumnLabel(i + 1);

                    //todo:难点。给user对象对应字段的属性赋值（通过反射***
                    Field field = User.class.getDeclaredField(columnName);
                    field.setAccessible(true);
                    field.set(t,columnValue);
                }
                list.add(t);
            }
            return list;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //6.关闭Statement和Connection
            JDBCutils.closeResuorses(conn,ps,resultSet);
        }
        return null;
    }

小结

两种思想
- 面向接口编程
- ORM思想
  - 一个数据表对应一个Java类
  - 一条记录对应一个Java对象
  - 一个字段对应Java类的一个属性
两种技术
- JDBC结果集元数据
  - 获取列数：getColumnCount()
  - 获取列的别名getColumnLable()
- 反射：创建指定类的对象，给对象的属性进行查改

三、PreparedStatement操作blob类型数据

关于blob类型

![截屏2021-04-01 下午7.32.08](/Users/boxizhou/Desktop/md/img/截屏2021-04-01 下午7.32.08.png)

Blob INSERT

@Test
    //todo：INESERT
    public void blobInsertTest() throws Exception {
        Connection conn = JDBCutils.getConnection();
        String sql = "insert into photo_table(id,name,photo) values(?,?,?)";
        PreparedStatement ps = conn.prepareStatement(sql);

        ps.setObject(1,1);
        ps.setObject(2,"ZHA");
        FileInputStream is = new FileInputStream(new File("/Users/boxizhou/Desktop/TsetPic.png"));
        ps.setBlob(3,is);

        ps.execute();

        JDBCutils.closeResuorses(conn,ps);
    }

Blob SELECT

@Test
   //todo:SELECT
   public void blobSelectTest() throws Exception {
       Connection conn = null;
       PreparedStatement ps = null;
       FileOutputStream fos = null;
       InputStream is = null;
       try {
           conn = JDBCutils.getConnection();
           String sql = "select id,name,photo from photo_table where id=?";
           ps = conn.prepareStatement(sql);
           ps.setObject(1,1);

           ResultSet resultSet = ps.executeQuery();
           if(resultSet.next()){
               int id = resultSet.getInt("id");
               String name = resultSet.getString("name");
               //将Blob类型文件保存在本地
               Blob photo = resultSet.getBlob("photo");
               is = photo.getBinaryStream();
               fos = new FileOutputStream("/Users/boxizhou/Desktop/PicFromBlob.png");
               byte[] buffer = new byte[1024];
               int len;
              //循环读流输出到文件
               while (true){
                   len = is.read(buffer);
                   fos.write(buffer,0,len);
                   if(len < 1024){//当流不能装满buffer，视为读完
                       break;
                   }
               }
           }
       } catch (Exception throwables) {
           throwables.printStackTrace();
       }finally {
           is.close();
           fos.close();
           JDBCutils.closeResuorses(conn, ps);
       }
   }

Mysql & JDBC noob

学习MySql数据库 和 JDBC