2023.8.1作业

1、使用Statement对象实现单表CRUD

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class D1 {
    public static void main(String[] args) throws Exception{
        Class.forName("com.mysql.jdbc.Driver");
        String url="jdbc:mysql://localhost:3306/db_classics";
        String user="root";
        String password="chen0220";
        Connection connection= DriverManager.getConnection(url,user,password);
        //增
        String sql1="insert into t_hero values (default,'陈文吉','帅哥',20,'男');";
        Statement statement1=connection.createStatement();
        statement1.executeUpdate(sql1);
        //删
        String sql2="delete from t_hero where id=1;";
        Statement statement2=connection.createStatement();
        statement2.executeUpdate(sql2);
        //改
        String sql3="update t_hero set age=99 where id=1;";
        Statement statement3=connection.createStatement();
        statement3.executeUpdate(sql3);
        //查
        String sql4="select * from t_hero;";
        Statement statement4=connection.createStatement();
        ResultSet resultSet=statement4.executeQuery(sql4);
        while (resultSet.next()){
            System.out.println(resultSet.getInt(1));
            System.out.println(resultSet.getString(2));
        }
        //关闭
        statement1.close();
        statement2.close();
        statement3.close();
        statement4.close();
        resultSet.close();
        connection.close();
    }
}

2、使用PreParedStatement实现对象的查询和更新

import java.sql.*;

public class D2 {
    public static void main(String[] args) throws Exception{
        Class.forName("com.mysql.jdbc.Driver");
        String url="jdbc:mysql://localhost:3306/db_classics";
        String user="root";
        String password="chen0220";
        Connection connection= DriverManager.getConnection(url,user,password);
        //查
        String sql1="select id, name from t_hero where id=?;";
        PreparedStatement preparedStatement1= connection.prepareStatement(sql1);
        preparedStatement1.setInt(1,3);
        ResultSet resultSet1=preparedStatement1.executeQuery();
        while (resultSet1.next()){
            System.out.println(resultSet1.getInt(1));
            System.out.println(resultSet1.getString(2));
        }
        //更新
        String sql2="update t_hero set age=99 where id=?;";
        PreparedStatement preparedStatement2= connection.prepareStatement(sql2);
        preparedStatement2.setInt(1,4);
        preparedStatement2.executeUpdate();
        //关闭
        preparedStatement1.close();
        resultSet1.close();
        preparedStatement2.close();
        connection.close();![请添加图片描述](https://img-blog.csdnimg.cn/a3ea2908d3654e90b1891bc2d0dd02f2.png)

    }
}

3、封装出一个公共类（JDBCUtils）和尝试着封装一下CRUD
配置Properties文件：

url=jdbc:mysql://localhost:3306/db_classics
user=root
password=chen0220
driver=com.mysql.cj.jdbc.Driver

封装公共类：

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class JDBCUtils {
    public static Connection getConnection() {
        try {
            Properties properties = loadProp();
            String driverClass = properties.getProperty("driver");
            String user = properties.getProperty("user");
            String password = properties.getProperty("password");
            String url = properties.getProperty("url");
            Class.forName(driverClass);
            Connection connection = DriverManager.getConnection(url, user, password);
            return connection;

        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
    private static Properties loadProp() {
        try {
            InputStream is = JDBCUtils.class.getClassLoader().getResourceAsStream("JDBC.properties");
            Properties properties = new Properties();
            properties.load(is);
            return properties;
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }
    public static void closeResource(Connection connection, ResultSet resultSet, PreparedStatement preparedStatement, Statement statement) {
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        if (preparedStatement != null) {
            try {
                preparedStatement.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }

    public static void closeResource(Connection connection, ResultSet resultSet, PreparedStatement preparedStatement) {
        closeResource(connection, resultSet, preparedStatement, null);
    }

    public static void closeResource(Connection connection, ResultSet resultSet) {
        closeResource(connection, resultSet, null, null);
    }

    public static void closeResource(Connection connection) {
        closeResource(connection, null, null, null);
    }
    //增
    public static void add(Connection connection,String tableName,String name,String nickname,int age,String sex)throws Exception{
        String sql="insert into '"+tableName+"' values (default,'"+name+"','"+nickname+"','"+age+"','"+sex+"');";
        Statement statement=connection.createStatement();
        statement.executeUpdate(sql);
        statement.close();
    }
    //删
    public static void delete(Connection connection,String tableName,int id)throws Exception{
        String sql="delete from '"+tableName+"' where id='"+id+"';";
        Statement statement=connection.createStatement();
        statement.executeUpdate(sql);
        statement.close();
    }
    public static void update(Connection connection,String tableName,int age,int id)throws Exception{
        String sql="update '"+tableName+"' set age='"+age+"' where id='"+id+"';";
        Statement statement=connection.createStatement();
        statement.executeUpdate(sql);
        statement.close();
    }
    public static void show(Connection connection,String tableName)throws Exception{
        String sql="select * from '"+tableName+"';";
        Statement statement=connection.createStatement();
        ResultSet resultSet=statement.executeQuery(sql);
        while (resultSet.next()){
            System.out.println(resultSet.getInt(1));
            System.out.println(resultSet.getString(2));
        }
        resultSet.close();
        statement.close();
    }
    public static void main(String[] args) throws Exception {
        Connection connection=JDBCUtils.getConnection();
        JDBCUtils.add(connection,"t_hero","王思聪","有钱人",30,"男");
        JDBCUtils.delete(connection,"t_hero",13);
        JDBCUtils.update(connection,"t_hero",77,7);
        JDBCUtils.show(connection,"t_hero");
        closeResource(connection);
    }
}

4、使用控制台实现一个SQL注入漏洞的案例