40.Statement对象详解

Statement对象

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

CRUD操作-create

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

CRUD操作-delete

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

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

CRUD操作-read

Statement st = conn.createStatement();
String sql="select * from user";
ResultSet rs = st.executeQuery(sql);
while(rs.next()){
   //获取列的数据类型，分别调用rs的相应方法映射到java对象中
}

代码实现

1.提取工具类

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

//工具类
package com.faq.lesson2.utils;

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 | ClassNotFoundException e) { //(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();
            }
        }
    }
}

2、增删改的实现

package com.faq.lesson2;

import com.faq.lesson2.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,'kuangshen','123456','37178338@qq.com','2020-01-01')";
            //删 //String sql = "delete from users where id =4";
            //改
            String sql = "update users set NAME = 'kuangshen'";

            int i = st.executeUpdate(sql);
            if(i>0){
                System.out.println("插入成功");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {

            JdbcUtils.release(conn,st,rs);
        }
    }
}

查的实现：

package com.faq.lesson2;

import com.faq.lesson2.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();//获得SQL的执行对象

            String sql = "select * from users";

            rs = st.executeQuery(sql);
            while(rs.next()){
                System.out.println("id="+rs.getObject("id"));
                System.out.println("name="+rs.getObject("NAME"));
                System.out.println("pwd="+rs.getObject("PASSWORD"));
                System.out.println("email="+rs.getObject("email"));
                System.out.println("birthday="+rs.getObject("birthday"));
                System.out.println("======================");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {

            JdbcUtils.release(conn,st,rs);
        }
    }
}