JavaWeb~02.JDBC(Java Database Connectivity) 資料庫連線技術

JDBC

       是一種用於執行SQL語句的Java API，為多種關聯式資料庫提供統一存取,它由一組用Java語言編寫的類和介面組成。有了JDBC，程式設計師只需用JDBC API寫一個程式，就可存取所有資料庫。

本文資料庫表

【宣告:本文資料庫內的所有內容純屬學習所用，如有雷同，純屬巧合!】

JDBC查詢所有資訊操作

public class Test{
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        String driver = "com.mysql.cj.jdbc.Driver";
        String url = "jdbc:mysql://127.0.0.1:3306/mybatis?useSSL=false&useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai";
        String username = "root";
        String pwd = "root";
        //載入一個Driver驅動
        Class.forName(driver);
        //建立資料庫的連線
        Connection connection = DriverManager.getConnection(url,username,pwd);
        //建立SQL命令傳送器
        Statement statement = connection.createStatement();
        //向資料庫傳送sql語句然後返回引數
        String sql = "select * from student";
        ResultSet resultSet = statement.executeQuery(sql);
        //遍歷輸出內容
        while(resultSet.next()){
            //getInt後面的引數值和資料庫裡面的欄位相對應
            //欄位的型別也要對應
            Integer son = resultSet.getInt("son");
            String realname = resultSet.getString("realname");
            String password = resultSet.getString("password");
            String classname = resultSet.getString("classname");
            Double score = resultSet.getDouble("score");
            System.out.println("["+ son + " " + realname + " " + password + " " +
                    classname + " " + score + "]");
        }
        //回收資源
        connection.close();
        statement.close();
        resultSet.close();
    }
}

JDBC存取資料庫的步驟:

       載入一個Driver驅動

       建立資料庫連線（Connection）

       建立SQL命令傳送器Statement

       通過Statement傳送SQL命令並得到結果

       處理結果（select語句）

       關閉資料庫資源ResultSet Statement Connection

載入Driver驅動

       使用反射載入驅動,其實就是獲得一個類的位元組碼,在獲得類的位元組碼的過程中,一定會載入類進入記憶體,一旦進入記憶體會執行程式碼中的靜態程式碼塊,一執行程式碼塊,就會自動的向DriverManager中註冊一個驅動

        mysql8 之前的資料庫驅動名 com.mysql.jdbc.Driver

        mysql8 開始的資料庫驅動 com.mysql.cj.jdbc.Driver

通過DriverManager獲得連結

        url                             同一資源定位符

       協定                            jdbc:mysql:

        ip地址                       127.0.0.1/localhost

        url                             同一資源定位符

        埠號                      3306

        具體的資源路徑       mybatis

        mysql8之前:             jdbc:mysql://127.0.0.1:3306/mybatis

        mysql8開始:             jdbc:mysql://127.0.0.1:3306/mybatis?useSSL=false&useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai

       useSSL=false            不使用SSL加密機制

       &useUnicode=true     使用unicode字元集

       &characterEncoding=utf8       使用utf8作為通訊字元集

       &serverTimezone=Asia/Shanghai       確定時區為 Asia/Shanghai

       其中的mybatis指的是資料庫名,可以根據需求修改

剛才的操作呢,是JDBC非常原始的一個操作,我們寫起來會感覺非常的麻煩。假如我們要寫多個查詢的操作,難道我們也要重複寫很多遍嘛?所以,這個時候,我們就需要對剛才的操作進行微微調整。

public class Test{
    public static void main(String[] args){
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            //建立資料庫連線
            connection = Util.getConnection();
            //建立SQL命令傳送器
            statement = connection.createStatement();
            //向資料庫傳送sql語句然後返回引數
            String sql = "select * from student";
            resultSet = statement.executeQuery(sql);
            //遍歷輸出內容
            while (resultSet.next()) {
                //getInt後面的引數值和資料庫裡面的欄位相對應
                //欄位的型別也要對應
                Integer son = resultSet.getInt("son");
                String realname = resultSet.getString("realname");
                String password = resultSet.getString("password");
                String classname = resultSet.getString("classname");
                Double score = resultSet.getDouble("score");
                Student student = new Student(son, realname, password, classname, score);
                System.out.println(student.toString());
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            Util.CloseAll(connection,statement,resultSet);
        }
    }
}
//建立工具類
class Util{
    private static String driver = "com.mysql.cj.jdbc.Driver";
    private static String url = "jdbc:mysql://127.0.0.1:3306/mybatis?useSSL=false&useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai";
    private static String username = "root";
    private static String pwd = "root";
    //將前面的操作封裝在一個方法裡面,這樣就可以重複呼叫了
    public static Connection getConnection(){
        Connection connection = null;
        try {
            //載入一個Driver驅動
            Class.forName(driver);
            //建立資料庫的連線
            connection = DriverManager.getConnection(url,username,pwd);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return connection;
    }

    public static void CloseAll(Connection connection,Statement statement,ResultSet resultSet){
        if(connection != null){
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if(statement != null){
            try {
                statement.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if(resultSet != null){
            try {
                resultSet.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}
//建立學生類
class Student implements Serializable {
    private Integer son;
    private String realname;
    private String password;
    private String classname;
    private Double score;

    public Student(Integer son, String realname, String password, String classname, Double score) {
        this.son = son;
        this.realname = realname;
        this.password = password;
        this.classname = classname;
        this.score = score;
    }

    @Override
    public String toString() {
        return "Student{" +
                "son=" + son +
                ", realname='" + realname + '\'' +
                ", password='" + password + '\'' +
                ", classname='" + classname + '\'' +
                ", score=" + score +
                '}';
    }

    public Student() {
        super();
    }
}

       這樣一來的話,我們後面不管寫多少次JDBC的操作,都可以省略很多行程式碼了~

JDBC常見異常

       Exception in thread "main"java.lang.ClassNotFoundException: com.mysql.jdbc2.Driver

       原因：沒有新增jar包或者com.mysql.jdbc2.Driver路徑錯誤

       Exception in thread "main" java.sql.SQLException: No suitable driver found for jbdc:mysql://127.0.0.1:3306/stumgr

       url錯誤

       Exception in thread "main" java.sql.SQLException: Access denied for user 'root'@'localhost' (using password: YES)

       原因：使用者名稱或者密碼錯誤

       Exception in thread "main" com.mysql.jdbc.exceptions .jdbc4.MySQLIntegrityConstraintViolationException:Duplicate entry '90' for key 'PRIMARY'

       原因：主鍵衝突

JDBC增刪查改操作

需求:新增學生資訊

    public static void Insert(String sql){
        Connection connection = null;
        Statement statement = null;
        try {
            //建立資料庫連線
            connection = Util.getConnection();
            //建立SQL命令傳送器
            statement = connection.createStatement();
            int n = statement.executeUpdate(sql);
            //返回結果
            String str = n > 0 ? "新增成功!" : "新增失敗!";
            System.out.println(str);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            Util.CloseAll(connection,statement,null);
        }
    }

需求:根據學號修改密碼

public static void main(String[] args){
			//問號 預留位置
        Update("update student set password = ? where son = ?","123456",666666);
    }
    //修改學生資訊
    public static void Update(String sql,String password,Integer son){
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try{
            //建立資料庫連線
            connection = Util.getConnection();
            //建立SQL命令傳送器
            preparedStatement = connection.prepareStatement(sql);
            //返回結果   前面的數位表示第幾個問號
            preparedStatement.setString(1,password);
            preparedStatement.setInt(2,son);
            int n = preparedStatement.executeUpdate();
            String str = n > 0 ? "修改成功!" : "修改失敗!";
            System.out.println(str);
        }catch (SQLException sqlException){
            sqlException.printStackTrace();
        }finally {
            Util.CloseAll(connection,preparedStatement,null);
        }
    }

需求:根據學號刪除學生資訊

 public static void Del(String sql,Integer son){
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try{
            //建立資料庫連線
            connection = Util.getConnection();
            //建立SQL命令傳送器
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,son);
            int n = preparedStatement.executeUpdate();
            String str = n > 0 ? "刪除成功!" : "刪除失敗!";
            System.out.println(str);
        }catch (SQLException sqlException){
            sqlException.printStackTrace();
        }finally {
            Util.CloseAll(connection,preparedStatement,null);
        }
    }

需求:通過學生的學號和密碼實現登入

  public static void Login(Integer son,String password){
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Student student = null;
        String sql = "select * from student where son = ? and password = ?";
        try{
            //建立資料庫連線
            connection = Util.getConnection();
            //建立SQL命令傳送器
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,son);
            preparedStatement.setString(2,password);
            resultSet = preparedStatement.executeQuery();
            //判斷是否為空
            if(resultSet.next()){
                String realname = resultSet.getString("realname");
                String classname = resultSet.getString("classname");
                Double score = resultSet.getDouble("score");
                student = new Student(son,realname,password,classname,score);
            }
            String str = student != null ? "登入成功!" : "登入失敗!";
            System.out.println(str);
        }catch (SQLException sqlException){
            sqlException.printStackTrace();
        }finally {
            Util.CloseAll(connection,preparedStatement,resultSet);
        }
    }

       從上述操作中,我們還發現一個弊端。就是很多程式碼都重複了很多遍,那麼,我們就再來完善一下工具類~

 //用於增刪改
    public static int executeUpdate(String sql,Object [] params) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        int n = 0;//新增失敗
        try {
            //建立和資料庫的連線
            conn = getConnection();
            //建立一個SQL命令傳送器

            pstmt = conn.prepareStatement(sql);
            //準備好SQL語句，通過SQL命令傳送器傳送給資料庫，並得到結果
            for (int i = 0; i <params.length ; i++) {
                pstmt.setObject(i+1, params[i]);
            }
            n = pstmt.executeUpdate();
            System.out.println(n);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //關閉資源
            CloseAll(conn,pstmt,null);
        }
        return n;
    }

    //用於查詢
    public static <T> List<T> baseQuery(T t, String sql, Object ... args){
        // 獲取list集合中要裝的物件的位元組碼
        Class aClass = t.getClass();
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet set = null;
        List<T> list = null;
        try {
            connection = Util.getConnection();
            statement = connection.prepareStatement(sql);
            // 設定引數的過程
            for (int i = 0; i < args.length; i++) {
                statement.setObject(i + 1, args[i]);
            }
            set = statement.executeQuery();
            // 獲取全部的欄位
            Field[] fs = aClass.getDeclaredFields();
            // 先設定屬性可以存取
            for(Field f:fs){
                f.setAccessible(true);
            }
            list=new ArrayList<>();
            while(set.next()){
                // 建立物件
                T  element = (T)aClass.newInstance();
                // 從結果集的一條資料中取出每個欄位的資訊,放入element物件上去
                // 遍歷fs 通過屬性名 去結果集中獲取資料
                for(Field f:fs){
                    String name = f.getName();
                    Object value=null;
                    // 判斷實體類屬性的資料型別,選擇對應的get方法
                    if(f.getType()==int.class){
                        value = set.getInt(name);
                    }else if(f.getType()==double.class){
                        value = set.getDouble(name);
                    }else if(f.getType()==boolean.class){
                        value = set.getBoolean(name);
                    }else{
                        value= set.getObject(name);
                    }
                    f.set(element,value);
                }
                list.add(element);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            CloseAll(connection,statement,set);
        }
        return list;
    }

完善了剛剛的工具類以後,我們就來修改下前面的登入操作~

注意:在此之前!記得Student類要補上 get set 方法

    public static void Login(Integer son,String password){
        String sql = "select * from student where son = ? and password = ?";
        List<Student> list = Util.baseQuery(new Student(),sql,son,password);
        String str = list.isEmpty() != true ? "登陸成功!" : "登陸失敗!";
        System.out.println(str);
    }

       這裡我們可以看到,程式碼已經明顯簡潔很多了~那麼,增刪改呢?

需求:增加操作完善

    public static void main(String[] args){
        Object[]objects = {653221,"張橋","998554","機電工程學院",566,"alvin"};
        Insert("insert into student value(?,?,?,?,?,?)",objects);
    }

    //新增學生資訊
    public static void Insert(String sql,Object []object){
        int n = Util.executeUpdate(sql,object);
        String str = n > 0 ? "新增成功!" : "新增失敗!";
        System.out.println(str);
    }

       修改和刪除就不在這裡囉嗦了~

使用JDBC結合Socket實現遠端登入

Student還是剛才那個Student,資料庫表也依然是剛剛的資料庫表

public class Student implements Serializable {
    private Integer son;
    private String realname;
    private String password;
    private String classname;
    private Double score;
}//get set那些就不寫在文章裡面了,但是實際操作需要新增。

完整的Util

public class Util {
    private static String driver = "com.mysql.cj.jdbc.Driver";
    private static String url = "jdbc:mysql://127.0.0.1:3306/mybatis?useSSL=false&useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai";
    private static String username = "root";
    private static String pwd = "root";
    //將前面的操作封裝在一個方法裡面,這樣就可以重複呼叫了
    public static Connection getConnection(){
        Connection connection = null;
        try {
            //載入一個Driver驅動
            Class.forName(driver);
            //建立資料庫的連線
            connection = DriverManager.getConnection(url,username,pwd);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return connection;
    }

    public static void CloseAll(Connection connection, Statement statement, ResultSet resultSet){
        if(connection != null){
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if(statement != null){
            try {
                statement.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if(resultSet != null){
            try {
                resultSet.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
    //用於增刪改
    public static int executeUpdate(String sql,Object [] params) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        int n = 0;//新增失敗
        try {
            //2.建立和資料庫的連線
            conn = getConnection();
            //3.建立一個SQL命令傳送器

            pstmt = conn.prepareStatement(sql);
            //4.準備好SQL語句，通過SQL命令傳送器傳送給資料庫，並得到結果
            for (int i = 0; i <params.length ; i++) {
                pstmt.setObject(i+1, params[i]);
            }
            n = pstmt.executeUpdate();
            //System.out.println(n);

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //6.關閉資源
            CloseAll(conn,pstmt,null);
        }
        return n;
    }

    //用於查詢
    public static <T> List<T> baseQuery(T t, String sql, Object ... args){
        // 獲取list集合中要裝的物件的位元組碼
        Class aClass = t.getClass();
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet set = null;
        List<T> list = null;
        try {
            connection = Util.getConnection();
            statement = connection.prepareStatement(sql);
            // 設定引數的過程
            for (int i = 0; i < args.length; i++) {
                statement.setObject(i + 1, args[i]);
            }
            set = statement.executeQuery();
            // 獲取全部的欄位
            Field[] fs = aClass.getDeclaredFields();
            // 先設定屬性可以存取
            for(Field f:fs){
                f.setAccessible(true);
            }
            list=new ArrayList<>();
            while(set.next()){
                // 建立物件
                T  element = (T)aClass.newInstance();
                // 從結果集的一條資料中取出每個欄位的資訊,放入element物件上去
                // 遍歷fs 通過屬性名 去結果集中獲取資料
                for(Field f:fs){
                    String name = f.getName();
                    Object value=null;
                    // 判斷實體類屬性的資料型別,選擇對應的get方法
                    if(f.getType()==int.class){
                        value = set.getInt(name);
                    }else if(f.getType()==double.class){
                        value = set.getDouble(name);
                    }else if(f.getType()==boolean.class){
                        value = set.getBoolean(name);
                    }else{
                        value= set.getObject(name);
                    }
                    f.set(element,value);
                }
                list.add(element);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            CloseAll(connection,statement,set);
        }
        return list;
    }
}

ClientTest使用者端

public class ClientTest {
    public static void main(String[] args) throws IOException {
        System.out.println("----------這裡是使用者端----------");
        Socket client = new Socket("127.0.0.1",8888);
        //獲取輸出流
        ObjectOutputStream objectOutputStream = new ObjectOutputStream(client.getOutputStream());
        //獲得使用者輸入的資料
        Student student = Login();
        //傳送給伺服器,讓伺服器判斷是否登陸成功
        objectOutputStream.writeObject(student);
        //接收伺服器發過來的反饋
        DataInputStream dataInputStream = new DataInputStream(client.getInputStream());
        System.out.println(dataInputStream.readUTF());
        //關閉
        client.close();
        objectOutputStream.close();
        dataInputStream.close();
    }
    public static Student Login(){
        Scanner scanner = new Scanner(System.in);
        System.out.print("請輸入學號: ");
        int son = scanner.nextInt();
        System.out.print("請輸入密碼: ");
        String password = scanner.next();
        return new Student(son,null,password,null,null);
    }
}

ServerTest伺服器端

public class ServerTest {
    public static void main(String[] args) throws IOException, ClassNotFoundException {
        System.out.println("----------這裡是伺服器端----------");
        //建立ServerSocket物件
        ServerSocket serverSocket = new ServerSocket(8888);
        //監聽使用者端是否連結
        Socket server = serverSocket.accept();
        //獲得使用者端傳送過來的資料
        ObjectInputStream objectInputStream = new ObjectInputStream(server.getInputStream());
        Student student = (Student) objectInputStream.readObject();
        //輸入sql語句,進入資料庫內查詢
        String sql = "select * from student where son = ? and password = ?";
        List<Student> list = Util.baseQuery(new Student(),sql,student.getSon(),student.getPassword());
        //判斷是否登陸成功,然後告訴使用者端
        String str = list.isEmpty() != true ? "登陸成功!" : "登陸失敗!";
        //傳送結果給使用者端
        DataOutputStream dataOutputStream = new DataOutputStream(server.getOutputStream());
        dataOutputStream.writeUTF(str);
        //關閉
        server.close();
        objectInputStream.close();
        dataOutputStream.close();
    }
}