JDBC笔记+面试题

最新推荐文章于 2024-09-14 18:46:05 发布

路远greedy

最新推荐文章于 2024-09-14 18:46:05 发布

阅读量171

点赞数

文章标签： java jdbc

本文链接：https://blog.csdn.net/qq_44960769/article/details/119974129

版权

本文详细介绍了JDBC的基本操作步骤，包括加载驱动、建立连接、执行SQL等，并展示了如何通过JDBCUtils工具类简化这些操作。同时，通过对比静态处理块与预处理块的使用，强调了预处理块在防止SQL注入和提升效率上的优势。通过预处理块的示例代码，展示了如何安全地执行带参数的SQL语句。

摘要由CSDN通过智能技术生成

一.JDBC的创建过程

1.加载驱动

2.建立连接

3.封装处理块

4.创建sql

5.执行sql，获取结果集

6.执行结果集

7.关闭

@Test
public void test(){

    //加载驱动
    Connection conn = null;
    Statement statement = null;
    ResultSet resultSet = null;
    try {
        Class.forName("oracle.jdbc.OracleDriver");
        //获取连接
        conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE", "SCOTT ", "TIGER");
        //创建sql
        String sql = "select * from emp";
        //封装处理块
        statement = conn.createStatement();
        //执行sql，获取结果集
        resultSet = statement.executeQuery(sql);
        //处理结果集
        while (resultSet.next()){
            int empNo = resultSet.getInt(1);
            String ename = resultSet.getString(2);
            String job = resultSet.getString(3);
            int mgr = resultSet.getInt(4);
            Date date = resultSet.getDate(5);
            double sal = resultSet.getDouble(6);
            double comm = resultSet.getDouble(7);
            int deptNo = resultSet.getInt(8);

            System.out.println(empNo + "-->" + ename + "-->" + job + "-->" + mgr + "-->" + date + "-->" + sal + "-->" + comm + "-->" + deptNo );

        }
    } catch (ClassNotFoundException e) {
        e.printStackTrace();
    } catch (SQLException throwables) {
        throwables.printStackTrace();
    } finally {
        //关闭
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}

二、将JDBC封装为JDBCUtils

public class JDBCUtils {

    private static Properties pro = new Properties();

    static {
        try {
            pro.load(Thread.currentThread().getContextClassLoader().getResourceAsStream("db.properties"));
        } catch (IOException e) {
            e.printStackTrace();
        }
        //加载驱动
        try {
            Class.forName(pro.getProperty("driver"));
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    //获取连接
    public static Connection getConnection() throws SQLException {

        Connection conn = null;
        conn = DriverManager.getConnection(pro.getProperty("url"), pro.getProperty("user"), pro.getProperty("password"));
        return conn;

    }

    //关闭连接
    public static void close(Statement statement,Connection conn){ close(null,statement,conn); }
    public static void close(ResultSet resultSet, Statement statement,Connection conn){

        if(conn != null) {
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

        if (statement != null){
            try {
                statement.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

        if (resultSet != null){
            try {
                resultSet.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

    }

}

二.使用静处理块和预处理块的区别

使用静态处理块可能会产生sql注入的问题

@Test
public void test07(){

    Connection conn = null;
    Statement sta = null;
    try {
        conn = JDBCUtils.getConnection();
        String str = "insert into emp(empno,ename,job) values(8888,'Tom','job')";
        String str1 = "delete from emp where deptno = 10";//如果传参给deptno可能会产生sql注入问题
        sta = conn.createStatement();
        int update = sta.executeUpdate(str);
        int delete = sta.executeUpdate(str1);
        if (update > 0 && delete > 0){
            conn.commit();
        }else{
            conn.rollback();
        }
    } catch (SQLException throwables) {
        throwables.printStackTrace();
    }finally {
        JDBCUtils.close(sta,conn);
    }
}

使用预处理块可以解决sql注入问题，还能对sql语句进行预编译

public void getMessage(int n){

    Connection conn = null;
    PreparedStatement ps = null;//使用预处理块需要先将sql作为参数传入
    ResultSet resultSet = null;
    try {
        conn = JDBCUtils.getConnection();
        String sql = "select * from emp where deptno = ?";
        ps = conn.prepareStatement(sql);
        ps.setObject(1,n);
        resultSet = ps.executeQuery();
        while (resultSet.next()){

            int empNo = resultSet.getInt(1);
            String ename = resultSet.getString(2);
            String job = resultSet.getString(3);
            int mgr = resultSet.getInt(4);
            Date date = resultSet.getDate(5);
            double sal = resultSet.getDouble(6);
            double comm = resultSet.getDouble(7);
            int deptNo = resultSet.getInt(8);

            System.out.println(empNo + "-->" + ename + "-->" + job + "-->" + mgr + "-->" + date + "-->" + sal + "-->" + comm + "-->" + deptNo );
        }
    } catch (SQLException throwables) {
        throwables.printStackTrace();
    } finally {
        JDBCUtils.close(resultSet,ps,conn);
    }
}