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\',\'24736743@qq.com\',\'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=\'24736743@qq.com\' 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\',\'2584@qq.com\',\'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=\'24736743@qq.com\' 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, \"24736743@qq.com\");//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, \"2584@qq.com\");
            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.cnblogs.com/wyh518/p/16859990.html
本站部分图文来源于网络，如有侵权请联系删除。

Statement对象与PreparedStatement对象

相关推荐

热门文章