高級(jí)應(yīng)用--JDBC（一）

  1. 為什么要使用JDBC？
  2. 如何使用JDBC？
  3. 使用JDBC進(jìn)行增刪改查
  4. 使用預(yù)編譯PreparedStatement進(jìn)行改進(jìn)
  5. execute與executeUpdate的區(qū)別
  6. DAO思想與設(shè)計(jì)

1.為什么要使用JDBC？

每個(gè)數(shù)據(jù)庫(kù)廠家使用的數(shù)據(jù)庫(kù)不同，所以Sun公司統(tǒng)一制定了數(shù)據(jù)庫(kù)連接規(guī)范

2.如何使用JDBC？

使用JDBC的基本操作

  1. 導(dǎo)入數(shù)據(jù)庫(kù)驅(qū)動(dòng)包
    需要到網(wǎng)絡(luò)下載這里注意一下MySQL數(shù)據(jù)庫(kù)版本號(hào)
  2. 初始化驅(qū)動(dòng)
Class.forName(com.mysql.cj.jdbc.Driver);
  1. 建立與數(shù)據(jù)庫(kù)的連接
Connection connection = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/test?useSSL=false&serverTimezone=UTC",
"root",
"123456");
  1. 創(chuàng)建Statement
Statement st = connection.createStatement();
  1. 創(chuàng)建并執(zhí)行SQL語句
String addCodeSQL = "insert into test1 values(null,'006')";
st.execute(addCodeSQL);
  1. 關(guān)閉連接
st.close();
connection.close();

完整使用JDBC代碼

package jdbc;
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
 
public class TestJDBC {
        public static void main(String[] args) {
        private static String DRIVER = "com.mysql.cj.jdbc.Driver";
        private static String URL = "jdbc:mysql://localhost:3306/test?useSSL=false&serverTimezone=UTC";
        private static String USERNAME = "root";
        private static String PASSWORD = "123456";
 
        Connection c = null;
        Statement s = null;
        try {
            Class.forName(DRIVER);
 
            c = DriverManager.getConnection(URL,USERNAME,PASSWORD);
 
            s = c.createStatement();
 
            String sql = "insert into hero values(null," 006")";
 
            s.execute(sql);
 
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            // 數(shù)據(jù)庫(kù)的連接時(shí)有限資源，相關(guān)操作結(jié)束后，養(yǎng)成關(guān)閉數(shù)據(jù)庫(kù)的好習(xí)慣
            // 先關(guān)閉Statement
            if (s != null)
                try {
                    s.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            // 后關(guān)閉Connection
            if (c != null)
                try {
                    c.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
 
        }
 
    }
}
  1. MySQL 8.0 以上版本驅(qū)動(dòng)包版本 [mysql-connector-java-8.0.16.jar]
  2. com.mysql.jdbc.Driver 更換為 com.mysql.cj.jdbc.Driver。

3.使用JDBC進(jìn)行增刪改查

3.1 增、刪、改

  • 增、刪、改都是用同一個(gè)方式拼接字符串就可以了
package test;

import java.sql.*;

public class JavaTest {

    private static String DRIVER = "com.mysql.cj.jdbc.Driver";
    private static String URL = "jdbc:mysql://localhost:3306/test?useSSL=false&serverTimezone=UTC";
    private static String USERNAME = "root";
    private static String PASSWORD = "123456";

    public static void main(String[] args) {

        try {
            // 1.創(chuàng)建驅(qū)動(dòng)
            Class.forName(DRIVER);
            System.out.println("數(shù)據(jù)庫(kù)驅(qū)動(dòng)加載成功！");

            //2.連接數(shù)據(jù)庫(kù)
            Connection connection = DriverManager.getConnection(URL,USERNAME,PASSWORD);
            System.out.println("連接成功"+connection);
         
            //3.創(chuàng)建sql語句
            String addCodeSQL = "insert into test1 values(null,'006')";

            //4.執(zhí)行sql語句
            Statement st = connection.createStatement();
            st.execute(addCodeSQL);

            System.out.println("執(zhí)行成功！");

            //5.釋放資源
            st.close();
            connection.close();

        } catch (SQLException | ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
}

3.2 查詢

  • 查詢的結(jié)果是因?yàn)榉祷匾粋€(gè)集合，所以要使用ResultSet結(jié)果集來接收
  • 使用executeQuery()方法來執(zhí)行查詢語句
package test;

import java.sql.*;

public class JavaTest {

    private static String DRIVER = "com.mysql.cj.jdbc.Driver";
    private static String URL = "jdbc:mysql://localhost:3306/test?useSSL=false&serverTimezone=UTC";
    private static String USERNAME = "root";
    private static String PASSWORD = "123456";

    public static void main(String[] args) {

        try {
            // 1.創(chuàng)建驅(qū)動(dòng)
            Class.forName(DRIVER);
            System.out.println("數(shù)據(jù)庫(kù)驅(qū)動(dòng)加載成功！");

            //2.連接數(shù)據(jù)庫(kù)
            Connection connection = DriverManager.getConnection(URL,USERNAME,PASSWORD);

            System.out.println("連接成功"+connection);

            //3.查詢語句
            String searchSQL = "select * from test1";

            //4.執(zhí)行sql語句
            Statement st = connection.createStatement();      

            //4.1查詢結(jié)果集
            ResultSet rs = st.executeQuery(searchSQL);
            while (rs.next()){
                int id = rs.getInt(1);
                String code = rs.getString(2);
                System.out.print("ID: " + id);
                System.out.print(" Code: " + code + "\n");
            }
            rs.close();
            System.out.println("執(zhí)行成功！");


            //5.釋放資源
            st.close();
            connection.close();

        } catch (SQLException | ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
}

4.使用預(yù)編譯PreparedStatement進(jìn)行改進(jìn)

4.1 為什么要使用PreparedStatement？

  • 最基本的Statement方式?jīng)]有辦法防止SQL注入攻擊 OR 1=1
  • 手動(dòng)拼接字符串太容易出錯(cuò)，所以使用占位符?來進(jìn)行占位

4.2 怎樣使用PreparedStatement？

以插入示例

        Class.forName(DRIVER);
        Connection connection = DriverManager.getConnection(URL,USERNAME,PASSWORD);
        String addSQL = "insert into test1 values(null,?)";
        PreparedStatement ps = connection.prepareStatement(addSQL);
        ps.setString(1,"008");
        ps.execute();
        ps.close();
        connection.close();
        System.out.println("執(zhí)行成功！");

6.DAO設(shè)計(jì)思想

package test;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class JavaTest {

    private static String DRIVER = "com.mysql.cj.jdbc.Driver";
    private static String URL = "jdbc:mysql://localhost:3306/test?useSSL=false&serverTimezone=UTC";
    private static String USERNAME = "root";
    private static String PASSWORD = "123456";


    public static void main(String[] args) throws SQLException, ClassNotFoundException {



        //Student student1 = new Student("009");
        //add(student1);

        //Student student2 = new Student(9);
        //delete(student2);

        //Student student3 = new Student(8,"010");
        //update(student3);

        //list();

    }


    //public static void add(Hero h)

    public static void add(Student student) throws SQLException, ClassNotFoundException {
        Class.forName(DRIVER);
        Connection connection = DriverManager.getConnection(URL,USERNAME,PASSWORD);
        String addSQL = "insert into test1 values(null,?)";
        PreparedStatement ps = connection.prepareStatement(addSQL);
        ps.setString(1,student.getCode());
        ps.execute();
        ps.close();
        connection.close();
        System.out.println("執(zhí)行成功！");
    }



    //public static void delete(Hero h)
    public static void delete(Student student) throws SQLException, ClassNotFoundException {

        Class.forName(DRIVER);
        Connection connection = DriverManager.getConnection(URL,USERNAME,PASSWORD);
        String deleteSQL = "delete from test1 where id = ?";
        PreparedStatement ps = connection.prepareStatement(deleteSQL);
        ps.setInt(1,student.getId());
        ps.execute();
        ps.close();
        connection.close();
        System.out.println("執(zhí)行成功！");
    }


    //public static void update(Hero h)
    public static void update(Student student) throws SQLException, ClassNotFoundException {
        Class.forName(DRIVER);
        Connection connection = DriverManager.getConnection(URL,USERNAME,PASSWORD);
        String updateSQL = "update test1 set code = ? where id = ?";
        PreparedStatement ps = connection.prepareStatement(updateSQL);
        ps.setString(1,student.getCode());
        ps.setInt(2,student.getId());
        ps.execute();
        ps.close();
        connection.close();
        System.out.println("執(zhí)行成功！");
    }

    //public static List<Hero> list();
    public static List<Student> list() throws SQLException, ClassNotFoundException {
        List<Student> list= new ArrayList<>();
        Class.forName(DRIVER);
        Connection connection = DriverManager.getConnection(URL,USERNAME,PASSWORD);
        String searchSQL = "select * from test1";
        PreparedStatement ps = connection.prepareStatement(searchSQL);
        ResultSet rs = ps.executeQuery();
        while (rs.next()){
            int id = rs.getInt(1);
            String code = rs.getString(2);
            Student student = new Student(id,code);
            list.add(student);
        }
        list.forEach(a-> System.out.println(a));
        return list;
    }

}
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請(qǐng)結(jié)合常識(shí)與多方信息審慎甄別。
平臺(tái)聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡(jiǎn)書系信息發(fā)布平臺(tái)，僅提供信息存儲(chǔ)服務(wù)。

友情鏈接更多精彩內(nèi)容