Statement对象与PreparedStatement对象(statement对象的两个常用方法)

  本篇文章为你整理了Statement对象与PreparedStatement对象(statement对象的两个常用方法)的详细内容,包含有statement 对象 statement对象的两个常用方法 statement和preparedstatement statement和prestatement Statement对象与PreparedStatement对象,希望能帮助你了解 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 注入

  
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:///wyh518/

  以上就是Statement对象与PreparedStatement对象(statement对象的两个常用方法)的详细内容,想要了解更多 Statement对象与PreparedStatement对象的内容,请持续关注盛行IT软件开发工作室。

郑重声明:本文由网友发布,不代表盛行IT的观点,版权归原作者所有,仅为传播更多信息之目的,如有侵权请联系,我们将第一时间修改或删除,多谢。

留言与评论(共有 条评论)
   
验证码: