Day1_2--数据库crud操作的封装（使用PreparedStatement）

 本节主要内容：

1.  PreparedStatement预编译处理
2. properties配置文件内容的读取
3. 字符串的拼接
4. properties配置文件

#键值对存储 
driver=com.mysql.cj.jdbc.Driver 
url=jdbc:mysql://localhost:3306/zy45?useSSL=false&serverTimezone=UTC 
user=root 
pw=123456

1. k2_crud代码

import org.junit.Test;

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

/**
 * @author nanzhi
 * @date 2024/7/15 15:04
 * 使用预编译处理（PreparedStatement）可以避免Statement中使用拼接后可能发生的sql注入
 * 优点：1.不再使用+拼接sql语句，减少语句错误
 * 2.有效解决sql注入问题
 * 3.大大减少了编译次数，效率较高
 */
public class k2_crud {
    //    变量赋值-->在getConn方法中给出了properties文件配置读取的方法
    //    private String db = "zy45";
    //    private String user = "root";
    //    private String pw = "123456";
    Connection conn;
    PreparedStatement stmt;

    public void getConn(String sql) throws ClassNotFoundException, SQLException, IOException {
        //properties配置文件
        Properties properties = new Properties();
        properties.load(new FileInputStream("Day1//config.properties"));
        String driver = properties.getProperty("driver");
        String url = properties.getProperty("url");
        String user = properties.getProperty("user");
        String pw = properties.getProperty("pw");
        Class.forName(driver);
        conn = DriverManager.getConnection(url, user, pw);
        stmt = conn.prepareStatement(sql);
    }

    public void end() throws SQLException {
        int i = stmt.executeUpdate();
        System.out.println("影响的行数i = " + i);
        conn.close();
        stmt.close();

    }

    @Test
    public void insert() throws SQLException, ClassNotFoundException, IOException {
        String sql = "insert into students values(?,?,?,?,?)";
        getConn(sql);
        stmt.setString(1, "188");
        stmt.setString(2, "李同学");
        stmt.setString(3, "女");
        stmt.setString(4, "2004-02-02");
        stmt.setString(5, "8899");
        end();
    }

    @Test
    public void delete() throws SQLException, ClassNotFoundException, IOException {
        String sql = "delete from students where sname=?";
        getConn(sql);
        stmt.setString(1, "李同学");
        end();
    }

    @Test
    public void update() throws SQLException, ClassNotFoundException, IOException {
        String sql = "update students set sname='改名字l'  where sno=?";
        getConn(sql);
        stmt.setString(1, "188");
        end();
    }

    @Test
    public void select() throws SQLException, ClassNotFoundException, IOException {
        String sql = "select * from students";
        getConn(sql);
        ResultSet rs = stmt.executeQuery();
        int count = 0;
        while (rs.next()) {
            System.out.println("sno" + '\t' + "sname" + '\t' + "ssex" + '\t' + "sbirthday" + '\t' + "class");
            System.out.println(
                    rs.getString("sno") + '\t' +
                            rs.getString("sname") + '\t' +
                            rs.getString("ssex") + '\t' +
                            rs.getDate("sbirthday") + '\t' +
                            rs.getInt("class"));
            count++;
        }
        System.out.println("查询了" + count + "行数据");
    }

    /**
     * 不推荐使用Statement
     * 注意这里的字符串拼接方法
     */
    @Test
    public void NoUpdate() throws SQLException, ClassNotFoundException, IOException {
        Integer sno = 188;
        String sql = "update students set sname='改名字了'  where sno=" + sno;
        //    String sql = "delete from students where sname='" + name + "'";
        getConn(sql);
        end();
    }

}