Statement对象

Statement对象

jdbc中的statement对象用于向数据库发送SQL语句，想完成对数据库的增删改查，只需要通过这个对象向数据库发送增删改查语句即可。

（Statement对象不安全，会产生SQL注入问题（通过or进行数据库攻击））

常用方法

• executeUpdate方法：用于向数据库发送增、删、改的sql语句（会返回一个整数）
• executeQuery方法：用于向数据库发送查询语句。（返回代表查询结果的ResultSet对象）

具体操作

insert：插入

statement st = conn.createStatement();
String sql = "insert into user(...) values(...)";
int num = st.executeUpdate(sql);
if (num > 0) {
    System.out.println("插入成功！");
}

delete：删除

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

update：修改

statement st = conn.createStatement();
String sql = "update user set name = '' where name = '' ";
int num = st.executeUpdate(sql);
if (num > 0) {
    System.out.println("修改成功！");
}

read：查询（使用executeQuery(String sql)方法完成数据查询操作）

statement st = conn.createStatement();
String sql = "select * from user where id = 1";
ResultSet rs = st.executeQuery(sql);
while (rs.next()) {
    
}

使用方法(增删改)

1. 配置文件：db.properties（在src目录下创建）

   driver=com.mysql.jdbc.Driver
   url=jdbc:mysql://本机:端口号/数据库名?useUnicode=true&characterEncoding=utf8&useSSSl=true
   username=数据库用户名
   password=密码
   

2. 创建工具类(JdbcUtils)用来读取db.properties

   import java.io.IOException;
   import java.io.InputStream;
   import java.sql.*;
   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{
               InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
               Properties properties = new Properties();
               properties.load(in);
   
               driver = properties.getProperty("driver");
               url = properties.getProperty("url");
               username = properties.getProperty("username");
               password = properties.getProperty("password");
   
               //1.驱动只用加载一次
               Class.forName(driver);
           } catch (Exception e) {
               e.printStackTrace();
           }
       }
   
       //获取连接
       public static Connection getConnection() throws SQLException {
           return DriverManager.getConnection(url,username,password);
       }
   
       //释放资源
       public static void release(Connection conn, Statement st, ResultSet rs){
           if (rs != null) {
               try {
                   rs.close();
               } catch (SQLException e) {
                   e.printStackTrace();
               }
           }
           if (st != null) {
               try {
                   st.close();
               } catch (SQLException e) {
                   e.printStackTrace();
               }
           }
           if (conn != null) {
               try {
                   conn.close();
               } catch (SQLException e) {
                   e.printStackTrace();
               }
           }
       }
   }
   
   

3. 测试工具类：插入数据：executeUpdate

   import com.lb.lesson02.utils.JdbcUtils;
   
   import java.sql.Connection;
   import java.sql.ResultSet;
   import java.sql.SQLException;
   import java.sql.Statement;
   
   public class TestInsert {
       public static void main(String[] args) {
   
           Connection conn = null;
           Statement st = null;
           ResultSet re = null;
   
           try {
               conn = JdbcUtils.getConnection();  //获取数据库连接
               st = conn.createStatement();  //获得SQL的执行对象
   
               String sql = "INSERT INTO users(`id`,`NAME`,`PASSWORD`,`email`,`birthday`) " +
                       "VALUES('4','华为','123456','**@qq.com','2020-11-11')";
   
               int i = st.executeUpdate(sql);
               if(i>0) {
                   System.out.println("插入成功！");
               }
           } catch (SQLException e) {
               e.printStackTrace();
           } finally {
               JdbcUtils.release(conn,st,re);
           }
   
       }
   }
   
   

4. 删除、更新数据：只需把sql语句改成：DELETE FROM users WHERE id = 4或UPDATE users SET NAME=‘小米’,email= ‘xm@ina.com’ WHERE id=2

注意：增删改都使用executeUpdate，查询使用executeQuery

使用方法(查询)

查找数据：executeQuery

//executeQuery
import com.lb.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 conn = null;
        Statement st = null;
        ResultSet rs = null;

        try {
            conn = JdbcUtils.getConnection();
            st = conn.createStatement();

            String sql = "select * from `users` where id = 2";

            rs = st.executeQuery(sql);  //查询：executeQuery；会返回一个结果集
            while (rs.next()){  //查询多条使用while，只看一条使用if就可
                System.out.println(rs.getString("NAME"));
                System.out.println(rs.getString("email"));
            }


        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(conn, st, rs);
        }
    }
}