Statement对象

MySQL 数据库学习笔记（续）

十、JDBC 重点！！！

10.5 Statement对象

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

Statement对象的executeUpdate方法，用于向数据库发送增、删、改的sql语句，executeUpdate执行完后，将会返回一个整数（即增删改语句导致了数据库几行数据发生了变化）。

Statement对象的executeQuery方法，用于向数据库发送查询语句，executeQuery方法返回值代表查询结果的ResultSet对象。

---

代码实现

1. 提取工具类

package com.guo.lesson02.utils;

import com.mysql.jdbc.Driver;

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) {
            throw new RuntimeException(e);
        }
    }

    //获取连接
    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) {
                throw new RuntimeException(e);
            }
        }

        if (st!=null){
            try {
                st.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }

        if (conn!=null){
            try {
                conn.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }
}

2. 编写增删改的方法，executeUpdate

增

package com.guo.lesson02;

import com.guo.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,'chenguo','123456','2981367375@qq.com','2026-04-15')";

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

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

删

package com.guo.lesson02;

import com.guo.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();//获得SQl的执行对象
            String sql = "DELETE FROM users WHERE id = 4";

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

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

改

package com.guo.lesson02;

import com.guo.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();//获得SQl的执行对象
            String sql = "UPDATE users SET `NAME`='chenguo' ,`email` = '2981367375@qq.com' WHERE id = 1";

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

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

3. 查询 executeQuery

package com.guo.lesson02;

import com.guo.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();

            //SQL
            String sql = "SELECT * FROM users WHERE id = 1";

            rs = st.executeQuery(sql); //查询完毕会返回一个结果集

            while (rs.next()){
                System.out.println(rs.getString("NAME"));
            }


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

---

总结

1. Statement 用于执行 SQL，executeUpdate 处理增删改，返回受影响行数
2. executeQuery 用于查询，返回 ResultSet 结果集
3. 抽取 JdbcUtils 工具类可以简化连接、释放资源操作
4. 所有操作最后必须调用 release() 释放资源，避免连接泄漏