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.Driverurl=jdbc:mysql://localhost:3306/jdbcStudy?useUnicode=true&characterEncoding=utf8&useSSL=falseusername=rootpassword=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/