Java学习 day41_JDBC

JDBC

1. 数据库的访问过程

• 客户端和MySQL服务之间建立网络连接
• 客户端向MySQL服务器发送请求（请求的内容其实就是SQL语句）
• MySQL服务器收到请求，执行命令
• MySQL把SQL语句的执行结果返回给客户端
• 客户端收到返回的响应，解析这个响应
• 释放资源

2.JDBC 是什么

JDBC：Java Database Connection，指Java数据库连接。

具体来说，其实JDBC就是Java为我们去访问数据库制定的这么一套接口。那么Java为什么要为去访问数据库制定一套接口呢？

因为我们有很多不同的数据库产品，Java需要去指定一个规范去连接不同的数据库，方便我们开发者以后去切换数据库。
比如本来使用的是mysql，如果要换oracle，必须要改代码，为了解决这个问题，提供了JDBC的接口

3.JDBC怎么用

3.1 第一个JDBC程序

• 第一步，导入驱动程序包

  导包的网站

  

• 编写应用程序

  package com.cskaoyan;
  
  import com.mysql.jdbc.Driver;
  
  import java.sql.*;
  
  public class JDBCDemo {
      /**
       * - 客户端和MySQL服务之间建立网络连接
       * - 客户端向MySQL服务器发送请求（请求的内容其实就是SQL语句）
       * - MySQL服务器收到请求，执行命令
       * - MySQL把SQL语句的执行结果返回给客户端
       * - 客户端收到返回的响应，解析这个响应
       * - 释放资源
       * @param args
       */
      private static String url = "jdbc:mysql://localhost:3306/33th?useSSL=false";
      private static String username = "root";
      private static String password = "123456";
  
  
      public static void main(String[] args) throws SQLException {
  
          // 加载驱动
          DriverManager.registerDriver(new Driver());
          // 获取连接
          Connection connection = DriverManager.getConnection(url, username, password);
          // 获取statement对象
          // 这个statement对象是用来去封装sql语句，使sql语句变成一个网络请求，然后发送给MySQL服务器
          Statement statement = connection.createStatement();
          // 发送SQL请求
          int affectedRows = statement.executeUpdate("insert into city values (2,'北京市',3)");
          // 获取结果集、解析结果集
          System.out.println("affectedRows:" + affectedRows);
  
          // 关闭资源
          statement.close();
  
          connection.close();
  
  
      }
  //    public static void main(String[] args) throws SQLException {
  //        // 加载驱动
  //        DriverManager.registerDriver(new Driver());
  //        // 获取连接
  //        Connection connection = DriverManager.getConnection(url, username, password);
  //        // 获取statement对象
  //        // 这个statement对象是用来去封装sql语句，使sql语句变成一个网络请求，然后发送给MySQL服务器
  //        Statement statement = connection.createStatement();
  //        // 发送SQL请求
  //        ResultSet resultSet = statement.executeQuery("select * from city");
  //        // 获取结果集、解析结果集
  //        while (resultSet.next()) {
  //            int id = resultSet.getInt("id");
  //            String name = resultSet.getString("name");
  //            int pId = resultSet.getInt("p_id");
  //
  //            System.out.println("id:" + id);
  //            System.out.println("name:" + name);
  //            System.out.println("pId:" + pId);
  //        }
  //
  //        // 关闭资源
  //        resultSet.close();
  //        statement.close();
  //        connection.close();
  //
  //    }
  }
  

3.2 优化(提取出utils)

package com.cskaoyan.utils;

import com.mysql.jdbc.Driver;
import com.sun.org.apache.xalan.internal.lib.ExsltBase;

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

public class JDBCUtils {

    static String url;
    static String username;
    static String password;

    static {


        Properties properties = new Properties();
        FileInputStream fileInputStream = null;
        try {
            fileInputStream = new FileInputStream("jdbc.properties");
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }
        try {
            properties.load(fileInputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }

        url = properties.getProperty("url");
        username = properties.getProperty("username");
        password = properties.getProperty("password");

    }


    // 获取连接
    public static Connection getConnection(){
        Connection connection = null;
        try {
            // 加载驱动 SPI
            DriverManager.registerDriver(new Driver());

            // 获取连接
            connection = DriverManager.getConnection(url, username, password);


        }catch (Exception ex){
            ex.printStackTrace();
        }

        return connection;
    }

    // 关闭资源
    public static void closeSources(Connection connection, Statement statement, ResultSet resultSet){
        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();
            }
        }
    }
}

注意：一般用execute()执行DDL，executeQuery()执行DQL，executeUpdate()执行DML

作业

package homework;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class JDBC_Demo {
    public static void main(String[] args) throws SQLException {
        Connection connection = JDBC_utils.getConnection();

        Statement statement = connection.createStatement();

        ResultSet res1 = statement.executeQuery("select t.name, group_concat(g.name) as s_name from tutors as t left join graduates as g on t.id = g.t_id group by t.id;");
        while (res1.next()) {
            String t_name = res1.getString("t.name");
            String s_name = res1.getString("s_name");
            System.out.println(t_name + " \t" + s_name);
        }

        System.out.println("---------------------------------");
        ResultSet res2 = statement.executeQuery("select t.name, group_concat(g.name) as s_name from tutors as t left join graduates as g on t.id = g.t_id group by t.id having t.name = '李明';");
        while (res2.next()) {
            String t_name = res2.getString("t.name");
            String s_name = res2.getString("s_name");
            System.out.println(t_name + " \t" + s_name);
        }

        System.out.println("---------------------------------");
        ResultSet res3 = statement.executeQuery("select t.name, count(*) as num from tutors as t left join graduates as g on t.id = g.t_id group by t.id;");
        while (res3.next()) {
            String t_name = res3.getString("t.name");
            String num = res3.getString("num");
            System.out.println(t_name + " \t" + num);
        }

        System.out.println("---------------------------------");
        ResultSet res4 = statement.executeQuery("select t.id, t.name, count(*) as num from tutors as t left join graduates as g on t.id = g.t_id where g.gender = '男' group by t.id;");
        while (res4.next()) {
            String t_id = res4.getString("t.id");
            String t_name = res4.getString("t.name");
            String num = res4.getString("num");
            System.out.println(t_id + " \t" + t_name + " \t" + num);
        }

        System.out.println("---------------------------------");
        ResultSet res5 = statement.executeQuery("select field, count(*) as num from tutors group by field order by num desc limit 1;");
        while (res5.next()) {
            String field = res5.getString("field");
            String num = res5.getString("num");
            System.out.println(field + " \t" + num);
        }

        System.out.println("---------------------------------");
        ResultSet res6 = statement.executeQuery("select title, count(*) as num from tutors group by title;");
        while (res6.next()) {
            String title = res6.getString("title");
            String num = res6.getString("num");
            System.out.println(title + " \t" + num);
        }


        JDBC_utils.closeConnection(connection, statement, res6);
    }
}

package homework;

import com.mysql.jdbc.Driver;

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

public class JDBC_utils {
    static String url;
    static String username;
    static String password;

    static {
        Properties properties = new Properties();

        FileInputStream fs = null;
        try {
            fs = new FileInputStream("jdbc.properties");
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }

        try {
            properties.load(fs);
        } catch (IOException e) {
            e.printStackTrace();
        }

        url = properties.getProperty("url");
        username = properties.getProperty("username");
        password = properties.getProperty("password");
    }



    public static Connection getConnection(){
        Connection connection = null;
        try {
            DriverManager.registerDriver(new Driver());
            connection = DriverManager.getConnection(url, username, password);

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

    public static void closeConnection(Connection connection, Statement statement, ResultSet resultSet){
        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();
            }
        }

    }

}
// -------------------------------
// 配置文件
url=jdbc:mysql://localhost:3306/basic?useSSL=false
username=root
password=123465