JDBC技术

1.JDBC（Java DataBase Connectivity[Java数据库连接]）：Java语言操作关系型数据库的一套API。

---

2.操作步骤：

• 下载连接mysql数据库的mysql-connector-java-8.2.0.jar:MySQL :: Download Connector/J
• 利用mysql创建一个关系型数据库作为测试：（包含部门表、员工表、薪资等级表）

  create database test;
  use test;
  DROP TABLE IF EXISTS EMP;
  DROP TABLE IF EXISTS DEPT;
  DROP TABLE IF EXISTS SALGRADE;
  
  CREATE TABLE DEPT
         (DEPTNO int(2) not null ,
  	DNAME VARCHAR(14) ,
  	LOC VARCHAR(13),
  	primary key (DEPTNO)
  	);
  CREATE TABLE EMP
         (EMPNO int(4)  not null ,
  	ENAME VARCHAR(10),
  	JOB VARCHAR(9),
  	MGR INT(4),
  	HIREDATE DATE  DEFAULT NULL,
  	SAL DOUBLE(7,2),
  	COMM DOUBLE(7,2),
  	primary key (EMPNO),
  	DEPTNO INT(2) 
  	)
  	;
  
  CREATE TABLE SALGRADE
        ( GRADE INT,
  	LOSAL INT,
  	HISAL INT );
  
  INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
  10, 'ACCOUNTING', 'NEW YORK'); 
  INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
  20, 'RESEARCH', 'DALLAS'); 
  INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
  30, 'SALES', 'CHICAGO'); 
  INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
  40, 'OPERATIONS', 'BOSTON'); 
  commit;
   
  INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
  DEPTNO ) VALUES ( 
  7369, 'SMITH', 'CLERK', 7902,  '1980-12-17'
  , 800, NULL, 20); 
  INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
  DEPTNO ) VALUES ( 
  7499, 'ALLEN', 'SALESMAN', 7698,  '1981-02-20'
  , 1600, 300, 30); 
  INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
  DEPTNO ) VALUES ( 
  7521, 'WARD', 'SALESMAN', 7698,  '1981-02-22'
  , 1250, 500, 30); 
  INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
  DEPTNO ) VALUES ( 
  7566, 'JONES', 'MANAGER', 7839,  '1981-04-02'
  , 2975, NULL, 20); 
  INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
  DEPTNO ) VALUES ( 
  7654, 'MARTIN', 'SALESMAN', 7698,  '1981-09-28'
  , 1250, 1400, 30); 
  INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
  DEPTNO ) VALUES ( 
  7698, 'BLAKE', 'MANAGER', 7839,  '1981-05-01'
  , 2850, NULL, 30); 
  INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
  DEPTNO ) VALUES ( 
  7782, 'CLARK', 'MANAGER', 7839,  '1981-06-09'
  , 2450, NULL, 10); 
  INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
  DEPTNO ) VALUES ( 
  7788, 'SCOTT', 'ANALYST', 7566,  '1987-04-19'
  , 3000, NULL, 20); 
  INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
  DEPTNO ) VALUES ( 
  7839, 'KING', 'PRESIDENT', NULL,  '1981-11-17'
  , 5000, NULL, 10); 
  INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
  DEPTNO ) VALUES ( 
  7844, 'TURNER', 'SALESMAN', 7698,  '1981-09-08'
  , 1500, 0, 30); 
  INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
  DEPTNO ) VALUES ( 
  7876, 'ADAMS', 'CLERK', 7788,  '1987-05-23'
  , 1100, NULL, 20); 
  INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
  DEPTNO ) VALUES ( 
  7900, 'JAMES', 'CLERK', 7698,  '1981-12-03'
  , 950, NULL, 30); 
  INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
  DEPTNO ) VALUES ( 
  7902, 'FORD', 'ANALYST', 7566,  '1981-12-03'
  , 3000, NULL, 20); 
  INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
  DEPTNO ) VALUES ( 
  7934, 'MILLER', 'CLERK', 7782,  '1982-01-23'
  , 1300, NULL, 10); 
  commit;
   
  INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
  1, 700, 1200); 
  INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
  2, 1201, 1400); 
  INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
  3, 1401, 2000); 
  INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
  4, 2001, 3000); 
  INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
  5, 3001, 9999); 
  commit;

• 分七步利用java语言实现：1.注册驱动 2.获取连接 3.定义sql 4.获取执行sql的对象statement 5.执行sql 6.处理结果 7.释放资源。代码实现过程如下：

  import java.sql.Connection;
  import java.sql.DriverManager;
  import java.sql.Statement;
  
  public class jdbc_demo1 {
      public static void main(String[] args) throws  Exception{
          //1.注册驱动
          Class.forName("com.mysql.jdbc.Driver");
          //2.获取连接
          String url = "jdbc:mysql://127.0.0.1:3306/test";
          String username = "root";
          String password = "sh741115";
          Connection conn = DriverManager.getConnection(url,username, password);
          //3.定义sql
          String sql = "update EMP set SAL = 900.00 where EMPNO = 7369";
          //4.获取执行sql的对象Statement
          Statement stmt = conn.createStatement();
          //5.执行sql
          int count = stmt.executeUpdate(sql);
          //6.处理结果
          System.out.println(count);
          //7.释放资源
          stmt.close();
          conn.close();
      }
  }

---

3.API详解：

• DriverManager（驱动管理类）：1.注册驱动 2.获取数据库连接

  获取数据库连接的参数

  url	连接路径
  user	用户名
  password	密码

  

• Connection（数据库连接对象）：1.获取执行SQL对象 2.管理事务

        Mysql中的事务管理：

         JDBC中的事务管理：Connection接口中定义了3个对应的方法

        利用Connection管理事务具体代码实现：

package sxu.demo1;

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

public class jdbc_demo_connection {
    public static void main(String[] args) throws  Exception{
        //1.注册驱动
        //Class.forName("com.mysql.jdbc.Driver");
        //2.获取连接
        String url = "jdbc:mysql://127.0.0.1:3306/test";
        String username = "root";
        String password = "sh741115";
        Connection conn = DriverManager.getConnection(url,username, password);
        //3.定义sql
        String sql1 = "update SALGRADE set LOSAL = 900 where GRADE = 1";
        String sql2 = "update SALGRADE set LOSAL = 1200 where GRADE = 2";
        //4.获取执行sql的对象Statement
        Statement stmt = conn.createStatement();

        try {
            //开启事务
            conn.setAutoCommit(false);
            //5.执行sql
            int count1 = stmt.executeUpdate(sql1);
            //6.处理结果
            System.out.println(count1);
            //5.执行sql
            int count2 = stmt.executeUpdate(sql2);
            //6.处理结果
            System.out.println(count2);
            //提交事务
            conn.commit();
        } catch (SQLException e) {
            //回滚事务
            conn.rollback();
            e.printStackTrace();
        }

        //提交事务
        //7.释放资源
        stmt.close();
        conn.close();
    }
}

• Statement：1.执行SQL语句

• ResultSet（结果集对象）：1.封装了DQL查询语句的结果

        获取查询结果：

•  PreparedStatement:1.预编译SQL语句并执行，预防SQL注入问题

        notice:SQL注入是通过操作输入来修改事先定义好的SQL语句，以达到执行代码对服务器进行攻击的方法。

PreparedStatement原理：

1.在获取PreparedStatement对象时，将sql语句发送给mysql服务器进行检查，编译（耗时长）

2.执行时不再进行这些步骤，速度更快

3.如果sql模版一样，则只需要进行一次检查、编译