PLSQL数据库JDBC学习Day01

/*
     JDBC基本流程：
        1.加载驱动(选择数据库)
        2.获取连接(与数据库建立连接)
        3.准备SQL
        4.构建处理块(封装发送SQL)
        5.发送SQL,得到结果
        6.处理结果
        7.连接关闭
 */

public class Class001_JDBC {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1.加载驱动(选择数据库)
        Class.forName("oracle.jdbc.driver.OracleDriver");
        //2.获取连接(与数据库建立连接)
        Connection conn = DriverManager.getConnection(
                "jdbc:oracle:thin:@localhost:1521:XE",
                "SCOTT",
                "TIGER"
        );
        //3.准备SQL
        String sql = "SELECT * FROM DEPT";
        //4.构建处理块(封装发送SQL)
        Statement state = conn.createStatement();
        //5.发送SQL,得到结果
        ResultSet result = state.executeQuery(sql);
        //6.处理结果
        while(result.next()){
            int deptno = result.getInt(1);
            String dname = result.getString("dname");
            String loc = result.getString(3);
            System.out.println(deptno+"-->"+dname+"-->"+loc);
        }
        //7.连接关闭
        conn.close();
    }
}

/*
     JDBC基本流程优化
        1.异常的捕获
        2.软编码方式定义数据库的参数信息
 */

public class Class002_JDBC {
    public static void main(String[] args) {
        Properties pro = new Properties();
        try {
            pro.load(Thread.currentThread().getContextClassLoader().getResourceAsStream("db.properties"));
        } catch (IOException e) {
            e.printStackTrace();
        }

        //1.加载驱动(选择数据库)
        try {
            Class.forName(pro.getProperty("driver"));
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }



        //2.获取连接(与数据库建立连接)
        Connection conn = null;
        Statement state = null;
        ResultSet result = null;
        try {
            conn = DriverManager.getConnection(
                    pro.getProperty("url"),
                    pro.getProperty("username"),
                    pro.getProperty("password")
            );
            //3.准备SQL
            String sql = "SELECT * FROM DEPT";
            //4.构建处理块(封装发送SQL)
            state = conn.createStatement();
            //5.发送SQL,得到结果
            result = state.executeQuery(sql);
            //6.处理结果
            while(result.next()){
                int deptno = result.getInt(1);
                String dname = result.getString("dname");
                String loc = result.getString(3);
                System.out.println(deptno+"-->"+dname+"-->"+loc);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //7.关闭
            if(result!=null){
                try {
                    result.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if(state!=null){
                try {
                    state.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if(conn!=null){
                try {
                    conn.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }

    }
}

 db.properties

driver=oracle.jdbc.driver.OracleDriver
url=jdbc:oracle:thin:@localhost:1521:XE
username=SCOTT
password=TIGER

/*
    JDBC封装工具类
        加载驱动
        获取连接
        资源关闭
 */

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("username"),
                pro.getProperty("password")
        );
        return conn;
    }

    //关闭资源
    public static void close(Connection conn, Statement state){
        close(conn,state,null);
    }
    public static void close(Connection conn, Statement state, ResultSet result){
        if(result!=null){
            try {
                result.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if(state!=null){
            try {
                state.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if(conn!=null){
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}

/*
    用户基本操作:
        用户注册  insert
        用户登录  select
            根据用户名与密码同时查找,找到结果返回true,没找到结果返回false
            根据用户名查找,找到密码与用户号输入的密码比较是否相等,返回true|false
        修改用户数据 update
        注销用户 delete

    注意: 事务默认自动提交

    预处理块:
        1.预先编译,提高效率
        2.防止SQL注入
            sql语句不是通过字符串的连接符直接拼接,而是通过预处理块内部拼接,拼接之前检查传入的数据
        注意: 建议使用预处理块代替静态处理块
 */

public class Class003_User {
    public static void main(String[] args) {
        System.out.println(login("zhangsan","1234")?"成功":"失败");;
    }

    //登录
    //预处理块 PreparedStatement
    public static boolean login(String username,String password){
        boolean flag = false;
        Connection conn = null;
        PreparedStatement state = null;
        ResultSet result = null;
        //1.获取连接
        try {
            conn = JDBCUtils.getConnection();
            //2.准备sql
            String sql = "select * from t_user where username=? and password =?";
            //3.构建预处理块
            state = conn.prepareStatement(sql);
            //4.为?赋值
            state.setString(1,username);
            state.setObject(2,password);

            //5.执行,得到结果
            result = state.executeQuery();
            //5.处理结果
            if(result.next()){
                flag = true;
            }
        }catch(SQLSyntaxErrorException e){
            System.out.println("遇到SQL注入了....");
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //6.关闭资源
            JDBCUtils.close(conn,state,result);
        }
        //7.返回结果
        return flag;
    }


    //静态处理块 Statement
    /*public static boolean login(String username,String password){
        boolean flag = false;
        Connection conn = null;
        Statement state = null;
        ResultSet result = null;
        //1.获取连接
        try {
            conn = JDBCUtils.getConnection();
            //2.准备sql
            String sql = "select * from t_user where username='"+username+"' and password = "+password;
            //3.封装处理块
            state = conn.createStatement();
            //4.执行,得到结果
            result = state.executeQuery(sql);
            //5.处理结果
            if(result.next()){
                flag = true;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //6.关闭资源
            JDBCUtils.close(conn,state,result);
        }
        //7.返回结果
        return flag;
    }*/

    //注册
    public static boolean reg(String username,String password){
        boolean flag = false;
        Connection conn = null;
        Statement state = null;
        //1.获取连接
        try {
            conn = JDBCUtils.getConnection();
            //2.准备sql
            String sql = "insert into t_user values('"+username+"',"+password+")";
            //3.封装处理块
            state = conn.createStatement();
            //4.执行,得到结果
            int rows = state.executeUpdate(sql);
            //5.处理结果
            if(rows>0){
                flag = true;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //6.关闭资源
            JDBCUtils.close(conn,state);
        }
        //7.返回结果
        return flag;
    }
}

/*
    转账操作
       A --> SAL-500
       B --> SAL+500
       需要控制在同一个事务中,设置手动提交,校验之后提交或者回滚
 */

public class Class004_Transfer {
    public static void main(String[] args) {
        System.out.println(transfer());;
    }

    //预处理块 PreparedStatement
    public static boolean transfer(){
        boolean flag = false;
        Connection conn = null;
        PreparedStatement state1 = null;
        PreparedStatement state2 = null;
        //1.获取连接
        try {
            conn = JDBCUtils.getConnection();

            //设置手动提交
            conn.setAutoCommit(false);

            //2.准备sql
            String sql1 = "update emp set sal=sal-500 where empno =?";
            String sql2 = "update emp set sal=sal+500 where empno =?";
            //3.构建预处理块
            state1 = conn.prepareStatement(sql1);
            state2 = conn.prepareStatement(sql2);
            //4.为?赋值
            state1.setObject(1,7900);
            state2.setObject(1,7369);
            //5.执行,得到结果
            int rows1 = state1.executeUpdate();
            int rows2 = state2.executeUpdate();
            //5.处理结果
            if(rows1>0 && rows2>0){
                flag = true;
                conn.commit();
            }else{
                conn.rollback();
            }
        }catch(SQLSyntaxErrorException e){
            System.out.println("遇到SQL注入了....");
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        //7.返回结果
        return flag;
    }
}