JDBC的使用以及預(yù)處理的重要性

JDBC 入門

1.創(chuàng)建JDBC與數(shù)據(jù)庫的鏈接
    0）導入
        import java.sql.Connection;
        import java.sql.DriverManager;
        import java.sql.ResultSet;
        import java.sql.SQLException;
        import java.sql.Statement;

    public class JDBCDemo1 {

        @Test
        // JDBC入門程序
        public void demo1(){
            /**
             * 1.注冊驅(qū)動
             * 2.獲得連接
             * 3.執(zhí)行sql
             * 4.釋放資源
             */
            Connection conn = null;
            Statement stmt = null;
            ResultSet rs = null;
            try{
                // 1.注冊驅(qū)動
                Class.forName("com.mysql.jdbc.Driver");
                // DriverManager.registerDriver(new Driver());
                // 2.獲得連接
                conn = DriverManager.getConnection("jdbc:mysql:///day03", "root", "123");
                // 3.執(zhí)行SQL語句
                // 3.1創(chuàng)建執(zhí)行sql語句的對象:
                stmt = conn.createStatement();
                // 3.2執(zhí)行sql語句
                rs = stmt.executeQuery("select * from user");
                // 3.3遍歷結(jié)果集
                while(rs.next()){
                    System.out.println(rs.getInt("id")+"   "+rs.getString("username")+"   "+rs.getString("password")+"   "+rs.getString("nickname")+"   "+rs.getDouble("salary"));
                }
            }catch(Exception e){
                e.printStackTrace();
            }finally{
                // 4.釋放資源
                if(rs != null){
                    try {
                        rs.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                    rs = null;
                }
                if(stmt != null){
                    try {
                        stmt.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                    stmt = null;
                }
                if(conn != null){
                    try {
                        conn.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                    conn = null;
                }
            }
        }
}

02 使用Statement連接 并作增刪改查操作

/**
 * 完成JDBC的CRUD的操作
 * @author jt
 *
 */
public class JDBCDemo2 {
    @Test
    // 查詢一條記錄
    public void demo4(){
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try{
            // 注冊驅(qū)動
            Class.forName("com.mysql.jdbc.Driver");
            // 獲得連接
            conn = DriverManager.getConnection("jdbc:mysql:///day03", "root", "123");
            // 創(chuàng)建執(zhí)行SQL對象
            stmt = conn.createStatement();
            String sql = "select * from user where id = 2";
            rs = stmt.executeQuery(sql);
            // 不用遍歷結(jié)果集
            if(rs.next()){
                System.out.println(rs.getInt("id")+"   "+rs.getString("username")+"   "+rs.getString("password")+"   "+rs.getString("nickname")+"  "+rs.getDouble("salary"));
            }
        }catch(Exception e){
            e.printStackTrace();
        }finally{
            // 4.釋放資源
            if(rs != null){
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                rs = null;
            }
            if(stmt != null){
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                stmt = null;
            }
            if(conn != null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                conn = null;
            }   
        }
    }
    
    @Test
    // 刪除操作
    public void demo3(){
        Connection conn = null;
        Statement stmt = null;
        try{
            // 1.注冊驅(qū)動
            Class.forName("com.mysql.jdbc.Driver");
            // 2.獲得連接
            conn = DriverManager.getConnection("jdbc:mysql:///day03", "root", "123");
            // 3.創(chuàng)建執(zhí)行SQL對象:
            stmt = conn.createStatement();
            String sql = "delete from user where id = 3";
            int num = stmt.executeUpdate(sql);
            if(num > 0){
                System.out.println("刪除成功!");
            }
        }catch(Exception e){
            e.printStackTrace();
        }finally{
            // 4.釋放資源
            if(stmt != null){
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                stmt = null;
            }
            if(conn != null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                conn = null;
            }
        }
    }
    
    @Test
    // 修改操作
    public void demo2(){
        Connection conn = null;
        Statement stmt = null;
        try{
            // 1.注冊驅(qū)動
            Class.forName("com.mysql.jdbc.Driver");
            // 2.獲得連接
            conn = DriverManager.getConnection("jdbc:mysql:///day03", "root", "123");
            // 3.執(zhí)行SQL
            stmt = conn.createStatement();
            String sql = "update user set username='qwe',password='zxc',nickname='王yy',salary=4800 where id = 6";
            int num = stmt.executeUpdate(sql);
            if(num > 0){
                System.out.println("修改成功！");
            }
        }catch(Exception e){
            e.printStackTrace();
        }finally{
            // 4.釋放資源
            if(stmt != null){
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                stmt = null;
            }
            if(conn != null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                conn = null;
            }
        }
    }

    @Test
    // 保存操作
    public void demo1(){
        Connection conn = null;
        Statement stmt = null;
        try{
            // 1.注冊驅(qū)動
            Class.forName("com.mysql.jdbc.Driver");
            // 2.獲得連接
            conn = DriverManager.getConnection("jdbc:mysql:///day03", "root", "123");
            // 3.創(chuàng)建執(zhí)行SQL對象，執(zhí)行SQL
            stmt = conn.createStatement();
            String sql = "insert into user values (null,'fff','abc','劉xx',3800)";
            int num = stmt.executeUpdate(sql);
            if(num > 0){
                System.out.println("保存成功!");
            }
        }catch(Exception e){
            e.printStackTrace();
        }finally{
            // 4.釋放資源
            if(stmt != null){
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                stmt = null;
            }
            if(conn != null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                conn = null;
            }
        }
    }
}

03預(yù)處理SQL語句（常用以上了解）PreparedStatement 關(guān)鍵類

/**

  • 解決了SQL注入漏洞的登錄

  • @author jt

    */
    public class UserDao2 {

         public boolean login(String username,String password){
       boolean flag = false;
 
       Connection conn = null;
       PreparedStatement pstmt = null;
       ResultSet rs = null;
       try{
         // 獲得連接:
           conn = JDBCUtils.getConnection();
         // 編寫SQL:
           String sql = "select * from user where username = ? and password = ?";
         // 預(yù)處理sql:
           pstmt = conn.prepareStatement(sql);
         // 設(shè)置參數(shù)
           pstmt.setString(1, username);
           pstmt.setString(2, password);
         // 執(zhí)行sql:
           rs = pstmt.executeQuery();
           if(rs.next()){
              flag = true;
             }
     }catch(Exception e){
             e.printStackTrace();
         }finally{
             JDBCUtils.release(rs, pstmt, conn);
          }
       
         return flag;
       }
}

04PreparedStatement的CRUD的操作 （關(guān)鍵）

    /**
     * PreparedStatement的CRUD的操作
     * @author jt
     *
     */
    public class JDBCDemo1 {
          @Test
          // 查詢一條
          public void demo4(){
                Connection conn = null;
                PreparedStatement pstmt = null;
                ResultSet rs = null;
                User user  = new User();
      try{
        // 獲得連接:
        conn = JDBCUtils.getConnection();
        // 編寫sql:
        String sql = "select * from user where id = ?";
        // 預(yù)編譯sql
        pstmt = conn.prepareStatement(sql);
        // 設(shè)置參數(shù)
        pstmt.setInt(1, 2);
        // 執(zhí)行sql:
        rs = pstmt.executeQuery();
        if(rs.next()){
            user.setId(rs.getInt("id"));
            user.setUsername(rs.getString("username"));
            user.setPassword(rs.getString("password"));
            user.setNickname(rs.getString("nickname"));
            user.setSalary(rs.getDouble("salary"));;
        }
    }catch(Exception e){
        e.printStackTrace();
    }finally{
        JDBCUtils.release(rs, pstmt, conn);
    }
    
    System.out.println(user);
}

@Test
// 刪除操作
public void demo3(){
    Connection conn = null;
    PreparedStatement pstmt = null;
    try{
        // 獲得連接:
        conn = JDBCUtils.getConnection();
        // 編寫SQL:
        String sql = "delete from user where id=?";
        // 預(yù)編譯SQL:
        pstmt = conn.prepareStatement(sql);
        // 設(shè)置參數(shù):
        pstmt.setInt(1, 7);
        // 執(zhí)行SQL:
        int num = pstmt.executeUpdate();
        if(num > 0){
            System.out.println("刪除成功!");
        }
    }catch(Exception e){
        e.printStackTrace();
    }finally{
        JDBCUtils.release( pstmt, conn);
    }
}

@Test
// 修改操作
public void demo2(){
    Connection conn = null;
    PreparedStatement pstmt = null;
    try{
        // 獲得連接:
        conn = JDBCUtils.getConnection();
        // 編寫SQL:
        String sql = "update user set username =?,password=?,nickname=?,salary=? where id=?";
        // 預(yù)編譯SQL:
        pstmt = conn.prepareStatement(sql);
        // 設(shè)置參數(shù):
        pstmt.setString(1, "abc");
        pstmt.setString(2, "yui");
        pstmt.setString(3, "王老八");
        pstmt.setDouble(4, 6600);
        pstmt.setInt(5, 7);
        // 執(zhí)行SQL:
        int num = pstmt.executeUpdate();
        if(num > 0){
            System.out.println("修改成功!");
        }
    }catch(Exception e){
        e.printStackTrace();
    }finally{
        JDBCUtils.release( pstmt, conn);
    }
}

@Test
// 保存操作
public void demo1(){
    Connection conn = null;
    PreparedStatement pstmt = null;
    try{
        // 獲得連接:
        conn = JDBCUtils.getConnection();
        // 編寫SQL:
        String sql = "insert into user values (null,?,?,?,?)";
        // 預(yù)編譯SQL:
        pstmt = conn.prepareStatement(sql);
        // 設(shè)置參數(shù):
        pstmt.setString(1, "hhh");
        pstmt.setString(2, "123456");
        pstmt.setString(3, "王老五");
        pstmt.setDouble(4, 9000);
        // 執(zhí)行SQL:
        int num = pstmt.executeUpdate();
        if(num > 0){
            System.out.println("保存成功!");
        }
    }catch(Exception e){
        e.printStackTrace();
    }finally{
        JDBCUtils.release(pstmt, conn);
    }
}

}

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

相關(guān)閱讀更多精彩內(nèi)容

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