Statement物件與PreparedStatement物件-有解無憂

一、Statement物件

Jdbc中的statement物件用于向資料庫發送SQL陳述句，想完成對資料庫的增刪改查，只需要通過這個物件向資料庫發送增刪改查陳述句即可，

Statement物件的executeUpdate方法，用于向資料庫發送增、刪、改的sql陳述句，executeUpdate執行完后，將會回傳一個整數（即增刪改陳述句導致了資料庫幾行資料發生了變化），

Statement.executeQuery方法用于向資料庫發送查詢陳述句，executeQuery方法回傳代表查詢結果的 ResultSet物件，

CRUD操作-create

使用executeUpdate(String sql)方法完成資料添加操作，示例操作：

Statement st = conn.createStatement();
String sql = "insert into users(id,name,password,email,birthday)" +
                    "values(4,'fubai','123','[email protected]','2020-01-01')";
int num = st.executeUpdate(sql);
if(num>0){
    System.out.println("插入成功！！！");
}

CRUD操作-delete

使用executeUpdate(String sql)方法完成資料洗掉操作，示例操作：

Statement st = conn.createStatement();
String sql = "delete from user where id=1";
int num = st.executeUpdate(sql);
if(num>0){
    System.out.println(“洗掉成功！！！");
}

CRUD操作-update

使用executeUpdate(String sql)方法完成資料修改操作，示例操作：

Statement st = conn.createStatement();
String sql = "update users set name='fubai',email='[email protected]' where id=3";
int num = st.executeUpdate(sql);
if(num>0){
    System.out.println(“修改成功！！！");
}

CRUD操作-read

使用executeQuery(String sql)方法完成資料查詢操作，示例操作：

Statement st = conn.createStatement();
String sql = "select * from user where id=1";
ResultSet rs = st.executeQuery(sql);
while(rs.next()){
    //根據獲取列的資料型別，分別呼叫rs的相應方法映射到java物件中,如：
　　System.out.println("name:" + rs.getString("name"));
　　System.out.println("email:" + rs.getString("email"));

自定義工具類和組態檔對資料庫增刪改查

1、新建一個 lesson02 的包

2、在src目錄下創建一個db.properties檔案，如下所示：

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcStudy?useUnicode=true&characterEncoding=utf8&useSSL=false
username=root
password=123456

3、在lesson02 下新建一個 utils 包，新建一個類 JdbcUtils

package com.fubai.lesson02.utils;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class JdbcUtils {
    private static String driver = null;
    private static String url = null;
    private static String username = null;
    private static String password = null;

    static {
        try {
            //讀取db.properties檔案中的資料庫連接資訊
            InputStream in =
                    JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
            Properties prop = new Properties();
            prop.load(in);
            //獲取資料庫連接驅動
            driver = prop.getProperty("driver");
            //獲取資料庫連接URL地址
            url = prop.getProperty("url");
            //獲取資料庫連接用戶名
            username = prop.getProperty("username");
            //獲取資料庫連接密碼
            password = prop.getProperty("password");
            //加載資料庫驅動
            Class.forName(driver);
        } catch (Exception e) {
            throw new ExceptionInInitializerError(e);
        }
    }

    // 獲取資料庫連接物件
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url, username, password);
    }

    // 釋放資源，要釋放的資源包括Connection資料庫連接物件，負責執行SQL命令的Statement物件，存盤查詢結果的ResultSet物件
    public static void release(Connection conn, Statement st, ResultSet rs) {
        if (rs != null) {
            try {
                //關閉存盤查詢結果的ResultSet物件
                rs.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
            rs = null;
        }
        if (st != null) {
            try {
                //關閉負責執行SQL命令的Statement物件
                st.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        if (conn != null) {
            try {
                //關閉Connection資料庫連接物件
                conn.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
}

使用statement物件完成對資料庫的CRUD操作

1、插入一條資料

package com.fubai.lesson02.utils;

import com.fubai.lesson02.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

public class TestInsert {
    public static void main(String[] args) {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            //獲取一個資料庫連接
            conn = JdbcUtils.getConnection();
            //通過conn物件獲取負責執行SQL命令的Statement物件
            st = conn.createStatement();
            //要執行的SQL命令
            String sql = "insert into users(id,name,password,email,birthday) " +
                    "values(4,'fubai','123','[email protected]','2020-01-01')";
            //執行插入操作，executeUpdate方法回傳成功的條數
            int num = st.executeUpdate(sql);
            if (num > 0) {
                System.out.println("插入成功！！");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //SQL執行完成之后釋放相關資源
            JdbcUtils.release(conn, st, rs);
        }
    }
}

2、洗掉一條資料

package com.fubai.lesson02;

import com.fubai.lesson02.utils.JdbcUtils;

import java.sql.*;

public class TestDelete {
    public static void main(String[] args) {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;

        try {
            connection = JdbcUtils.getConnection();
            statement = connection.createStatement();

            String sql = "delete from users where id=4";
            int i = statement.executeUpdate(sql);

            if (i > 0) {
                System.out.println("洗掉成功");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JdbcUtils.release(connection, statement, resultSet);
        }
    }
}

3、更新一條資料

package com.fubai.lesson02;

import com.fubai.lesson02.utils.JdbcUtils;

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

public class TestUpdate {
    public static void main(String[] args) {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;

        try {
            connection = JdbcUtils.getConnection();
            statement = connection.createStatement();

            String sql = "update users set name='fubai',email='[email protected]' where id=3";
            int i = statement.executeUpdate(sql);

            if (i > 0) {
                System.out.println("更改成功");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JdbcUtils.release(connection, statement, resultSet);
        }
    }
}

4、查詢資料

package com.fubai.lesson02;

import com.fubai.lesson02.utils.JdbcUtils;

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

public class TestSelect {
    public static void main(String[] args) {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;

        try {
            connection = JdbcUtils.getConnection();
            statement = connection.createStatement();

            String sql = "select * from users where id=3";
            resultSet = statement.executeQuery(sql);

            while (resultSet.next()) {
                System.out.println("name:" + resultSet.getString("name"));
                System.out.println("email:" + resultSet.getString("email"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JdbcUtils.release(connection, statement, resultSet);
        }
    }
}

SQL 注入問題

通過巧妙的技巧來拼接字串，造成SQL短路，從而獲取資料庫資料

package com.fubai.lesson02;

import com.fubai.lesson02.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

public class SqlInject {
    public static void main(String[] args) {
        // login("zhangsan","123456"); // 正常登陸
        login("'or '1=1", "123456"); // SQL 注入
    }

    public static void login(String username, String password) {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtils.getConnection();
            // select * from users where name='' or '1=1' and password ='123456'
            String sql = "select * from users where name='" + username + "' and password = '" + password + "' ";
            st = conn.createStatement();
            rs = st.executeQuery(sql);
            while (rs.next()) {
                System.out.println(rs.getString("name"));
                System.out.println(rs.getString("password"));
                System.out.println("==============");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(conn, st, rs);
        }
    }
}

二、PreparedStatement物件

PreperedStatement是Statement的子類，它的實體物件可以通過呼叫

Connection.preparedStatement()方法獲得，相對于Statement物件而言：PreperedStatement可以避免SQL注入的問題，

Statement會使資料庫頻繁編譯SQL，可能造成資料庫緩沖區溢位，

PreparedStatement可對SQL進行預編譯，從而提高資料庫的執行效率，并且PreperedStatement對于 sql中的引數，允許使用占位符的形式進行替換，簡化sql陳述句的撰寫，

使用PreparedStatement物件完成對資料庫的CRUD操作

1、插入資料

package com.fubai.lesson03;

import com.fubai.lesson03.utils.JdbcUtils;

import java.sql.Connection;
import java.util.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class TestInsert {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        try {
            //獲取一個資料庫連接
            conn = JdbcUtils.getConnection();
            //要執行的SQL命令，SQL中的引數使用?作為占位符
            String sql = "insert into users(id,name,password,email,birthday) values(?,?,?,?,?)";
            //通過conn物件獲取負責執行SQL命令的prepareStatement物件
            st = conn.prepareStatement(sql);
            //為SQL陳述句中的引數賦值，注意，索引是從1開始的
            st.setInt(1, 4);//id是int型別的
            st.setString(2, "kuangshen");//name是varchar(字串型別)
            st.setString(3, "123");//password是varchar(字串型別)
            st.setString(4, "[email protected]");//email是varchar(字串型別)
            st.setDate(5, new java.sql.Date(new Date().getTime()));//birthday是date型別
            //執行插入操作，executeUpdate方法回傳成功的條數
            int num = st.executeUpdate();
            if (num > 0) {
                System.out.println("插入成功！！");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //SQL執行完成之后釋放相關資源
            JdbcUtils.release(conn, st, rs);
        }
    }
}

2、洗掉一條資料

package com.fubai.lesson03;

import com.fubai.lesson03.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class TestDelete {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;

        try {
            connection = JdbcUtils.getConnection();

            String sql = "delete from users where id=?";
            preparedStatement = connection.prepareStatement(sql);

            preparedStatement.setInt(1, 4);

            int i = preparedStatement.executeUpdate();
            if (i > 0) {
                System.out.println("洗掉成功");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JdbcUtils.release(connection, preparedStatement, null);
        }
    }
}

3、更新一條資料

package com.fubai.lesson03;

import com.fubai.lesson03.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class TestUpdate {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;

        try {
            connection = JdbcUtils.getConnection();

            String sql = "update users set name=?,email=? where id=?";
            preparedStatement = connection.prepareStatement(sql);

            preparedStatement.setString(1, "wyh");
            preparedStatement.setString(2, "[email protected]");
            preparedStatement.setInt(3, 3);

            int i = preparedStatement.executeUpdate();
            if (i > 0) {
                System.out.println("修改成功");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JdbcUtils.release(connection, preparedStatement, null);
        }
    }
}

4、查詢一條資料

package com.fubai.lesson03;

import com.fubai.lesson03.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class TestSelect {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = JdbcUtils.getConnection();

            String sql = "select * from users where id = ?";
            preparedStatement = connection.prepareStatement(sql);

            preparedStatement.setInt(1, 1);
            resultSet = preparedStatement.executeQuery();

            while (resultSet.next()) {
                System.out.println("name:" + resultSet.getString("name"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JdbcUtils.release(connection, preparedStatement, resultSet);
        }
    }
}

避免SQL 注入

package com.fubai.lesson03;

import com.fubai.lesson03.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class SqlInject {
    public static void main(String[] args) {
// login("zhangsan","123456"); // 正常登陸
        login("'or '1=1", "123456"); // SQL 注入
    }

    public static void login(String username, String password) {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtils.getConnection();
            // select * from users where name='' or '1=1' and password ='123456'
            String sql = "select * from users where name=? and password=?";
            st = conn.prepareStatement(sql);
            st.setString(1, username);
            st.setString(2, password);
            rs = st.executeQuery();
            while (rs.next()) {
                System.out.println(rs.getString("name"));
                System.out.println(rs.getString("password"));
                System.out.println("==============");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(conn, st, rs);
        }
    }
}

原理：執行的時候引數會用引號包起來，并把引數中的引號作為轉義字符，從而避免了引數也作為條件的一部分

本文來自博客園，作者：腹白，轉載請注明原文鏈接：https://www.cnblogs.com/wyh518/

轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/527973.html

標籤：Java

上一篇：我看誰還不懂多執行緒之間的通信+基礎入門+實戰教程+詳細介紹+附原始碼

下一篇：遞回