15-MySQL-JDBC-Statement对象详解

MySQL -> JDBC -> Statement对象详解

• Statement对象：

  • JDBC中的statement对象用于向数据库发送SQL语句，想完成对数据库的增删改查，只需要通过这个对象向数据库发送增删改查语句即可；
  • Statement对象的executeUpdate方法，用于向数据库发送增、删、改的sql语句，executeUpdate执行完后，将会返回一个整数，即增删改语句导致了数据库几行数据发生了改变；
  • Statement对象的executeQuery方法用于向数据库发送查询语句，executeQuery方法代表查询结果的ResultSet对象。

• 代码实现：

  • 1、提取工具类

  db.properties

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

  JdbcUtils.java

  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 (IOException e){
              e.printStackTrace();
          } catch (ClassNotFoundException e) {
              e.printStackTrace();
          }
      }
  
      //2.获取连接
      public static Connection getConnection() throws SQLException {
          return DriverManager.getConnection(url, username, password);
      }
      //3.释放资源
      public static void release(Connection conn, Statement st, ResultSet rs){
          if(conn!=null){
              try {
                  conn.close();
              } catch (SQLException throwables) {
                  throwables.printStackTrace();
              }
          }
          if(st!=null){
              try {
                  st.close();
              } catch (SQLException throwables) {
                  throwables.printStackTrace();
              }
          }
          if(rs!=null){
              try {
                  rs.close();
              } catch (SQLException throwables) {
                  throwables.printStackTrace();
              }
          }
      }
  }
  

  • 2、增删改

    TestInsert.java

  import com.anobabe.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 rs = null;
          try {
              conn = JdbcUtils.getConnection(); //获取数据库连接
              st = conn.createStatement();//获得SQL的执行对象
              String sql = "INSERT INTO users(`id`,`name`,`password`,`email`,`birthday`)" +
                      "VALUES(4,'ano','123456','ano@sina.com','1998-12-4')";
              int i = st.executeUpdate(sql);
              if(i>0){
                  System.out.println("数据插入成功！");
  
              }
          } catch (SQLException throwables) {
              throwables.printStackTrace();
          }finally {
              JdbcUtils.release(conn,st,rs);
          }
      }
  }
  

  ​

  TestDelete.java

  import com.anobabe.lesson02.utils.JdbcUtils;
  
  import java.sql.Connection;
  import java.sql.ResultSet;
  import java.sql.SQLException;
  import java.sql.Statement;
  
  public class TestDelete {
      public static void main(String[] args) {
          Connection conn = null;
          Statement st = null;
          ResultSet rs = null;
          try {
              conn = JdbcUtils.getConnection();
              st = conn.createStatement();
              String sql = "DELETE FROM users WHERE id = 4";
              int i = st.executeUpdate(sql);
              if(i>0){
                  System.out.println("删除成功！");
              }
          } catch (SQLException throwables) {
              throwables.printStackTrace();
          } finally {
              JdbcUtils.release(conn,st,rs);
          }
      }
  }
  

​

TestUpdate.java

 import com.anobabe.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 conn = null;
          Statement st = null;
          ResultSet rs = null;
          try {
              conn = JdbcUtils.getConnection();
              st = conn.createStatement();
              String sql = "UPDATE users SET `password`='12345678' WHERE id = 2";
              int i = st.executeUpdate(sql);
              if(i>0){
                  System.out.println("更新成功！");
              }
          } catch (SQLException throwables) {
              throwables.printStackTrace();
          }finally {
              JdbcUtils.release(conn,st,rs);
          }
      }
  }

• 3、查

  TestSelect.java

import com.anobabe.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";
            rs = st.executeQuery(sql);
            while(rs.next()){
                System.out.println("id=" + rs.getInt("id"));
                System.out.println("name=" + rs.getString("name"));
                System.out.println("password=" + rs.getString("password"));
                System.out.println("email=" + rs.getString("email"));
                System.out.println("birthday=" + rs.getDate("birthday"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);

        }
    }
}