jdbc笔记

数据库连接

  //   1.加载驱动
        Class.forName("com.mysql.jdbc.Driver");
        //   2.获取用户信息和url
        String url="jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&useSSL=true";
        String username= "root";
        String password = "123456";
 
        //  3.连接成功   数据库对象
        Connection connection = DriverManager.getConnection(url,username,password);
        //  4.执行SQL的对象   执行SQL
        Statement statement = connection.createStatement();
//        statement.executeQuery();   查询操作  返回 ResultSet
//        statement.execute();   执行任何SQL
//        statement.executeUpdate();   更新 插入，删除 ，都是这个，返回一个受影响的行数
 
        //  5.执行SQL    可能存在结果    查看返回结果
        String sql="select * from user";
        ResultSet resultSet = statement.executeQuery(sql);
 
//        resultSet.getObject();
//        resultSet.getString();
//        resultSet.getInt();
//        resultSet.getDouble();
//        resultSet.beforeFirst();移动到最前面
//        resultSet.afterLast();  移动到最后面
//        resultSet.next();    移动到下一个数据
//        resultSet.previous(); 移动到前一行
//        resultSet.absolute(row);移动到制定行
 
 
        while (resultSet.next()){
            System.out.println("id=" +resultSet.getObject("id"));
            System.out.println("name=" +resultSet.getObject("name"));
            System.out.println("age=" +resultSet.getObject("age"));
 
 
        }
 
        //  6.释放连接
        resultSet.close();
        statement.close();
        connection.close();
//        释放资源必须做

utils 工具类

public class JdbcUtils {
    private static final String driver = "com.mysql.cj.jdbc.Driver";
    private static final String username = "root";
    private static final String password = "123456";
    private static final String url = "jdbc:mysql://localhost:3306/rbac?useUnicode=true&&characterEncoding=utf-8";
 
    static {
        try {
//             驱动只需要加载一次
            Class.forName(driver);
 
        } catch (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();
            }
        }
 
    }
}

数据库CROD

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 employee set name='anie2' where name='annie'";
 
            int i=st.executeUpdate(sql);
            if (i>0){
                System.out.println("update 成功");
 
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release( conn,st,rs);
 
        }
 
 
    }
 
}

防止sql 注入

   public static void main(String[] args) {
//        login("mikasa", "22");
        login(" '' or 1=1 "  , "22");
 
    }
    public static void login(String username ,String password){
        Connection conn=null;
        PreparedStatement st= null;
        ResultSet rs=null;
        try {
            conn = JdbcUtils.getConnection();
            String sql="select * from user where 'name =? and 'password' =?   ";
 
         st =conn.prepareStatement(sql);
         st.setString(1, username);
         st.setString(2, password);
 
         rs = st.executeQuery();
 
            // PreparedStatement 防止SQL 注入的本质，把传递进来的参数当做字符
            //假设其中存在转义字符，比如说  '  会被直按转义
            //   String sql =”" select from users where、NAME =? and、 PASSWORD =?”; 
            // Mybatis
            while (rs.next()){
                System.out.println(rs.getString("name"));
                System.out.println(rs.getString("password"));
            }
 
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
 
    }