JDBC_part2_DML以及预编译_编写DBUtil工具类

本文为博主辛苦总结,希望自己以后返回来看的时候理解更深刻,也希望可以起到帮助初学者的作用.

转载请注明 出自 : luogg的博客园 谢谢配合!

jdbc day02

DML语法

• 比起插叙语句,没有ResultSet结果集,stmt.executeUpdate(sql)返回的是改变几行的int类型.

前几步和day01一致
//3.写sql语句
        String sql = "insert into copy_emp(last_name) values("+"'洛哥哥')";
        //4.创建发送对象
        Statement stmt = conn.createStatement();
        stmt.executeUpdate(sql);
        //7.关闭资源

SQL注入

sql注入就是用户输入的值,当做了sql语言的一部分,然后攻破密码等重要信息.
为了防止sql注入,就要将含有单引号的采用预编译处理.

比如 :

String USERNAME = "admin";
String PASSWORD = "123 OR '1' = '1"; (sql注入,使破解后的密码恒成立)

打印输入密码之后的sql语言

select count(*) from login where USERNAME= 'admin' and PASSWORD= '123' OR '1'='1';

预编译

• 采用?占位符, 将需要输入单引号的sal语法用?表示.
• 书写简单,而且防止sql注入问题.
• PreparedStatement继承Statement.
• 预编译中的问号,pre.setString(1,"哈哈");
  1代表第一个问号,不代表第一个参数.

预编译中和普通编译区别在于:

普通编译:

    //4.创建发送对象
    Statement stmt = conn.createStatement();
    //5.发送sql语句
    ResultSet rs = stmt.executeQuery(sql);

预编译

    //4.创建预编译对象
    PreparedStatement pstmt = conn.prepareStatement(sql);
    //5.填入数据
    pstmt.setString(1, myUsername);
    pstmt.setString(2, myPassword);
    //6.创建结果集
    ResultSet rs = pstmt.executeQuery();

---

        前两步与day01一致,查看用户输入的用户名和密码是否是已经存在数据库的
        String myUsername = "admin";
        String myPassword = "admin";
        //3.写sql语句(判断输入的用户名密码在数据库中有几条记录)
        String sql = "select count(*) count from login1 where username = ? and password = ?";
        //4.创建预编译对象
        PreparedStatement pstmt = conn.prepareStatement(sql);
        //5.填入数据
        pstmt.setString(1, myUsername);
        pstmt.setString(2, myPassword);
        //6.创建结果集
        ResultSet rs = pstmt.executeQuery();
        //7.查询select结果
        if(rs.next()) {
            String flag = rs.getString("count");
            if("1".equals(flag)) {
                System.out.println("登录成功");
            } else {
                System.out.println("登录失败");
            }
        }
        //8.关闭资源
        

DBUtil

• 将属性和方法设置为static静态,方便以后直接用类名调用.

public class DBUtil {
    private final static String DriverName = "oracle.jdbc.OracleDriver";
    private final static String URL = "jdbc:oracle:thin:@localhost:1521:orcl";
    private final static String USERNAME = "scott";
    private final static String PASSWORD = "luogg";
    private static Connection conn ;
    private static Statement stmt ;
    //private static PreparedStatement pstmt;
    
    /**
     * 连接数据库方法
     * @return Connection
     * @param conn 数据库连接对象
     */
    
    public static Connection getConnection() {
        try {
            Class.forName(DriverName);
            conn = DriverManager.getConnection(URL,USERNAME,PASSWORD);
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return conn;
    }
    
    /**
     * 查询语句关闭
     * @param rs 结果集
     * @param stmt 发送对象
     * @param conn 连接对象
     */
    public static void close(ResultSet rs,Statement stmt,Connection conn) {
        if(rs != null){
            try {
                rs.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        if(stmt != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        if(conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
    }
    
    /**
     * 非查询语句关闭资源
     * @param stmt 发送对象
     * @param conn 连接对象
     */
    public static void close(Statement stmt,Connection conn) {
        close(null,stmt,conn);
    }
    
    /**
     * 查询语句
     * @param sql
     * @return ResultSet
     */
    public static ResultSet selectSql(String sql) {
        ResultSet rs = null;
        try {
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return rs;
    }
    
    /**
     * 增删改的SQL语句
     * @param sql
     * @return
     */
    public static int runSql(String sql) {
        int result = 0;
        try {
            Statement stmt = conn.createStatement();
            result = stmt.executeUpdate(sql);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return result;
    }

转载于:https://www.cnblogs.com/luogg/p/5950781.html