欧美vvv,亚洲第一成人在线,亚洲成人欧美日韩在线观看,日本猛少妇猛色XXXXX猛叫

數(shù)據(jù)庫操作

JDBC簡介

JDBC的全稱是Java數(shù)據(jù)庫連接(Java Data Base )，是Java程序操作數(shù)據(jù)的API,JDBC API主要位于java.sql包中，

該包定義了一系列訪問數(shù)據(jù)庫的接口和類。應用程序可以通過這套API連接到不同的數(shù)據(jù)庫 MySQL ,并通過SQL語句完成對數(shù)據(jù)庫中數(shù)據(jù)的查詢、新增、修改、刪除操作。

JDBC連接數(shù)據(jù)庫

JDBC連接數(shù)據(jù)庫的步驟主要分為以下三步：

注冊數(shù)據(jù)庫驅動

下載相關jar包,官網：

.7和.0的數(shù)據(jù)庫驅動是不一樣的

.7的連接地址是com.mysql.jdbc.

.0的連接地址是com.mysql.cj.jdbc.

加載數(shù)據(jù)庫驅動，注冊到驅動管理器

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

構建數(shù)據(jù)庫連接URL

JDBC URL的標準有三部門組成，各個部分之間用冒號分隔。

jdbc:子協(xié)議:子名稱

三大常用數(shù)據(jù)庫的JDBC URL

MySQL

jdbc:mysql://主機名稱:mysql服務端口號/數(shù)據(jù)庫名稱?參數(shù)=值&參數(shù)=值 jdbc:mysql://:3306/test jdbc:mysql://:3306/test?=true&=utf8 jdbc:mysql://:3306/test?=UTC

jdbc::thin:@主機名稱:服務端口號:數(shù)據(jù)庫名稱

jdbc::thin:@:1521:test

jdbc:://主機名稱:服務端口號:=數(shù)據(jù)庫名稱 jdbc:://:1433:=test

獲取對象

可以調用 類的 (url,,) 方法建立到數(shù)據(jù)庫的連接

    public static void main(String[] args) throws Exception {
        Class.forName("com.mysql.cj.jdbc.Driver");
        String url="jdbc:mysql://localhost:3306/atguigu?serverTimezone=UTC";
        String user="root";
        String password="123456";
        Connection connection= DriverManager.getConnection(url,user,password);
        System.out.println(connection);
        connection.close();
    }

使用完成數(shù)據(jù)庫的增刪改查操作 增加

    public static void main(String[] args) throws Exception {
        String url="jdbc:mysql://localhost:3306/atguigu?serverTimezone=UTC";
        Connection conn = DriverManager.getConnection(url, "root", "123456");
        String sql="insert into t_department values(null,'開發(fā)部','負責開發(fā)工作')";
        PreparedStatement pst= conn.prepareStatement(sql);
        int len = pst.executeUpdate();
        System.out.println(len >0 ? "添加成功" : "添加失敗");
        pst.close();
        conn.close();
    }

修改

    public static void main(String[] args) throws Exception {
        String url="jdbc:mysql://localhost:3306/atguigu?serverTimezone=UTC";
        Connection conn = DriverManager.getConnection(url, "root", "123456");
        String sql="update t_department set description='負責修改操作' where did=9";
        PreparedStatement preparedStatement = conn.prepareStatement(sql);
        int i = preparedStatement.executeUpdate();

        System.out.println(i>0 ? "修改成功" : "修改失敗");
        preparedStatement.close();
        conn.close();
    }

刪除

    public static void main(String[] args) throws Exception {
        String url="jdbc:mysql://localhost:3306/atguigu?serverTimezone=UTC";
        Connection conn = DriverManager.getConnection(url, "root", "123456");
        String sql="delete from t_department where did=9";
        PreparedStatement preparedStatement = conn.prepareStatement(sql);
        int i = preparedStatement.executeUpdate();
        System.out.println(i>0 ? "刪除成功" : "刪除成功");
        preparedStatement.close();
        conn.close();
    }

查詢

    public static void main(String[] args) throws Exception {
        String url="jdbc:mysql://localhost:3306/atguigu?serverTimezone=UTC";
        Connection conn = DriverManager.getConnection(url, "root", "123456");
        String sql="select * from t_department ";
        PreparedStatement preparedStatement = conn.prepareStatement(sql);
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()){
            int did = resultSet.getInt("did");
            String name=resultSet.getString("dname");
            String desc=resultSet.getString("description");
            System.out.println(did+"\t"+name+"\t"+desc);
        }
        resultSet.close();
        preparedStatement.close();
        conn.close();
    }

 public static void main(String[] args) throws Exception {
        String url="jdbc:mysql://localhost:3306/atguigu?serverTimezone=UTC";
        Connection conn = DriverManager.getConnection(url, "root", "123456");
        String sql="select * from t_department ";
        PreparedStatement preparedStatement = conn.prepareStatement(sql);
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()){
            int did = resultSet.getInt(1);
            String name=resultSet.getString(2);
            String desc=resultSet.getString(3);
            System.out.println(did+"\t"+name+"\t"+desc);
        }
        resultSet.close();
        preparedStatement.close();
        conn.close();
    }

SQL拼接注入問題

新增SQL語句拼接問題

  public static void main(String[] args) throws SQLException {
        Scanner input = new Scanner(System.in);
        System.out.print("請輸入姓名：");
        String ename = input.next();//李四

        System.out.print("請輸入薪資：");
        double salary = input.nextDouble();//15000
        System.out.print("請輸入出生日期：");
        String birthday = input.next();//1990-1-1
        System.out.print("請輸入性別：");
        char gender = input.next().charAt(0);//男
        System.out.print("請輸入手機號碼：");
        String tel = input.next();//13578595685
        System.out.print("請輸入郵箱：");
        String email = input.next();//zhangsan@atguigu.com
        input.close();
        String url="jdbc:mysql://localhost:3306/atguigu?serverTimezone=UTC";
        Connection conn = DriverManager.getConnection(url, "root", "123456");
        String sql="INSERT INTO t_employee(ename,salary,birthday,gender,tel,email,hiredate) VALUES('"+ename+"','"+salary+"','"+birthday+"','"+gender+"','"+tel+"','"+email+"',curdate());";
        PreparedStatement preparedStatement = conn.prepareStatement(sql);
        int i = preparedStatement.executeUpdate();
        System.out.println(i>0 ? "添加成功" : "添加失敗");
        preparedStatement.close();
        conn.close();
    }

以上代碼拼接sql太麻煩，使用？

  public static void main(String[] args) throws SQLException {
        Scanner input = new Scanner(System.in);
        System.out.print("請輸入姓名：");
        String ename = input.next();//李四
        System.out.print("請輸入薪資：");
        double salary = input.nextDouble();//15000
        System.out.print("請輸入出生日期：");
        String birthday = input.next();//1990-1-1
        System.out.print("請輸入性別：");
        String gender=input.next();
        System.out.print("請輸入手機號碼：");
        String tel = input.next();//13578595685
        System.out.print("請輸入郵箱：");
        String email = input.next();//zhangsan@atguigu.com
        input.close();
        String url="jdbc:mysql://localhost:3306/atguigu?serverTimezone=UTC";
        Connection conn = DriverManager.getConnection(url, "root", "123456");
        String sql="INSERT INTO t_employee(ename,salary,birthday,gender,tel,email,hiredate) VALUES(?,?,?,?,?,?,?);";
        PreparedStatement pst = conn.prepareStatement(sql);
        pst.setObject(1,ename);
        pst.setObject(2,salary);
        pst.setObject(3,birthday);
        pst.setObject(4,gender);
        pst.setObject(5,tel);

        pst.setObject(6,email);
        pst.setObject(7,new Date());
        int i = pst.executeUpdate();
        System.out.println(i>0 ? "添加成功" : "添加失敗");
        pst.close();
        conn.close();
    }

查詢SQL語句注入問題

   public static void main(String[] args) throws SQLException {
        Scanner input = new Scanner(System.in);
        System.out.print("請輸入eid：");
        String eid = input.next();
        input.close();
        String url="jdbc:mysql://localhost:3306/atguigu?serverTimezone=UTC";
        Connection conn = DriverManager.getConnection(url, "root", "123456");
        String sql="select * from  t_employee where eid=?";
        PreparedStatement pst = conn.prepareStatement(sql);
        pst.setObject(1,eid);
        ResultSetMetaData metaData = pst.getMetaData();
        int columnCount = metaData.getColumnCount();
        ResultSet rs = pst.executeQuery();
        while (rs.next()){
            for(int i=1;i<=columnCount;i++){
                System.out.print(rs.getObject(i)+"  ");
            }
        }
        rs.close();
        pst.close();
        conn.close();
    }

MySQL操作BLOB類型字段

MySQL中，BLOB是二進制對象，可存儲圖片。

插入BLOB大數(shù)據(jù)類型

 public static void main(String[] args) throws Exception {
        String url="jdbc:mysql://localhost:3306/atguigu?serverTimezone=UTC";
        Connection conn = DriverManager.getConnection(url, "root", "123456");
        String sql="insert into t_user values(?,?,?)";
        PreparedStatement pst = conn.prepareStatement(sql);
        pst.setObject(1,"呆瓜");
        pst.setObject(2,"123456");
        pst.setObject(3,new FileInputStream("D:\\1\\0.jpg"));
        int i = pst.executeUpdate();
        System.out.println(i>0 ? "添加成功" : "添加失敗");
        pst.close();
        conn.close();
    }

插入圖片失敗的問題

解決方案一：修改my.ini配置文件 變量的值

先停止服務，然后修改my.ini文件，再重啟服務

解決方案二：修改字段的數(shù)據(jù)類型

讀取BLOB大數(shù)據(jù)類型

 public static void main(String[] args) throws Exception {

        String url="jdbc:mysql://localhost:3306/atguigu?serverTimezone=UTC";
        Connection conn = DriverManager.getConnection(url, "root", "123456");
        String sql="select * from t_user where username='呆瓜'";
        PreparedStatement pst = conn.prepareStatement(sql);
        ResultSet rs = pst.executeQuery();
        while (rs.next()){
            String username = rs.getString(1);
            String password = rs.getString(2);
            Blob photo = rs.getBlob(3);
            InputStream is = photo.getBinaryStream();
            OutputStream os = new FileOutputStream("D:\\1\\2.jpg");
            byte [] buffer = new byte[1024];
            int len = 0;
            while((len = is.read(buffer)) != -1){
                os.write(buffer, 0, len);
            }
            System.out.print(username+"  "+password+"  "+photo);
        }
        pst.close();
        conn.close();
    }

批處理

 public static void main(String[] args) throws Exception{
        String url="jdbc:mysql://localhost:3306/atguigu?serverTimezone=UTC";
        Connection conn = DriverManager.getConnection(url, "root", "123456");
        String sql="insert into t_department values(null,?,?)";
        PreparedStatement pst = conn.prepareStatement(sql);
        for(int i=1;i<=100;i++){
            pst.setObject(1,"部門"+i);
            pst.setObject(2,"簡介"+i);
            pst.addBatch();
        }
        int[] len = pst.executeBatch();
        System.out.println("添加成功,共:"+len.length+"條");
        pst.close();
        conn.close();
    }

數(shù)據(jù)庫連接池

1. 資源重用

由于數(shù)據(jù)庫連接得以重用，避免了頻繁創(chuàng)建，釋放連接引起的大量性能開銷。在減少系統(tǒng)消耗的基礎上，另一方面也增加了系統(tǒng)運行環(huán)境的平穩(wěn)性。

2. 更快的系統(tǒng)反應速度

數(shù)據(jù)庫連接池在初始化過程中，往往已經創(chuàng)建了若干數(shù)據(jù)庫連接置于連接池中備用。此時連接的初始化工作均已完成。對于業(yè)務請求處理而言，直接利用現(xiàn)有可用連接，避免了數(shù)據(jù)庫連接初始化和釋放過程的時間開銷，從而減少了系統(tǒng)的響應時間

3. 新的資源分配手段

對于多應用共享同一數(shù)據(jù)庫的系統(tǒng)而言數(shù)據(jù)庫增刪查改關鍵詞，可在應用層通過數(shù)據(jù)庫連接池的配置，實現(xiàn)某一應用最大可用數(shù)據(jù)庫連接數(shù)的限制，避免某一應用獨占所有的數(shù)據(jù)庫資源

4. 統(tǒng)一的連接管理，避免數(shù)據(jù)庫連接泄漏

在較為完善的數(shù)據(jù)庫連接池實現(xiàn)中，可根據(jù)預先的占用超時設定，強制回收被占用連接，從而避免了常規(guī)數(shù)據(jù)庫連接操作中可能出現(xiàn)的資源泄露

DBCP

DBCP 是提供的數(shù)據(jù)庫連接池。 服務器自帶dbcp數(shù)據(jù)庫連接池。速度相對c3p0較快，但因自身存在BUG，已不再提供支持。

C3P0

C3P0 是一個開源組織提供的一個數(shù)據(jù)庫連接池數(shù)據(jù)庫增刪查改關鍵詞，**速度相對較慢，穩(wěn)定性還可以。官方推薦使用

Druid

Druid 是阿里提供的數(shù)據(jù)庫連接池，據(jù)說是集DBCP 、C3P0 、 優(yōu)點于一身的數(shù)據(jù)庫連接池，但是速度不確定是否有快

使用德魯伊數(shù)據(jù)庫連接池

(1)引入jar包
    和引入mysql驅動jar方式一樣
（2）編寫配置文件
    src下加一個druid.properties文件

（3）創(chuàng)建數(shù)據(jù)庫連接池對象
（4）獲取連接

    public static void main(String[] args) throws Exception {
        Properties pro = new Properties();
        pro.load(DruidTest.class.getClassLoader().getResourceAsStream("druid.properties"));
        DataSource ds = DruidDataSourceFactory.createDataSource(pro);
        for(int i=1; i<=10; i++){
            //多線程，每一個線程代表一個用戶來獲取連接
            new Thread(){
                public void run(){
                    try {
                        Connection conn = ds.getConnection();
                        System.out.println(conn);
                        conn.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
            }.start();
        }
    }

-實現(xiàn)CRUD操作 類 插入 批處理 查詢

    public static void main(String[] args) throws Exception {
        QueryRunner runner = new QueryRunner();
        String url="jdbc:mysql://localhost:3306/atguigu?serverTimezone=UTC";
        Connection conn = DriverManager.getConnection(url, "root", "123456");
        String sql="insert into t_department values(null,?,?)";
        int len = runner.update(conn, sql, "銷售部", "負責銷售操作");
        System.out.println("添加了第"+len+"條數(shù)據(jù)");
        conn.close();
    }

接口及實現(xiàn)類

接口的主要實現(xiàn)類：

   public static void main(String[] args) throws Exception {
   	   QueryRunner runner = new QueryRunner();
       String url="jdbc:mysql://localhost:3306/atguigu?serverTimezone=UTC";
       Connection conn = DriverManager.getConnection(url, "root", "123456");
       String sql="select * from t_department  where did=?";
       BeanHandler<Department> handler = new BeanHandler<>(Department.class);
       Department  department  = runner.query(conn, sql, handler, 9);
       System.out.println(department);	
       conn.close();
   }

    public static void main(String[] args) throws Exception {
    	QueryRunner runner = new QueryRunner();
        String url="jdbc:mysql://localhost:3306/atguigu?serverTimezone=UTC";
        Connection conn = DriverManager.getConnection(url, "root", "123456");
        String sql="select * from t_department  where did;
        BeanListHandler<Department> handler = new BeanListHandler<>(Department.class);
        List<Department>  department  = runner.query(conn, sql, handler, 9);
        department.forEach(System.out::println);
        conn.close();
    }