java——JDBC

1.JDBC基本流程:
    把oracle实现jar包拿到项目下  add as lib..
    1.加载驱动  (选择数据库)
    2.建立连接 Connection (与数据库之间建立连接)
    3.准备sql
    4.封装处理块,发送sql
    5.得到结果集
    6.处理结果
    7.关闭资源

public class Class001_JDBC {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1.加载驱动  (选择数据库)
        Class.forName("oracle.jdbc.driver.OracleDriver");
        //2.建立连接 Connection (与数据库之间建立连接)
        Connection conn = DriverManager.getConnection(
                "jdbc:oracle:thin:@localhost:1521:XE",
                "SCOTT",
                "TIGER"
        );
        //3.准备sql
        String sql = "select * from dept";
        //4.封装处理块
        Statement state = conn.createStatement();
        //5.发送sql,得到结果集
        ResultSet reault = state.executeQuery(sql);
        //6.处理结果
        while(reault.next()){
            //字段序号从1开始,每次+1
            int deptno = reault.getInt(1);
            String dname = reault.getString(2);
            String loc = reault.getString(3);
            System.out.println(deptno+"--->"+dname+"--->"+loc);
        }
        //7.关闭资源
        reault.close();
        state.close();
        conn.close();
    }
}

2.优化:
 1.异常 捕获
 2.通过配置文件实现软编码

public class Class002_JDBC {
    public static void main(String[] args){
        //构建 properties对象
        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 (与数据库之间建立连接)
        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.封装处理块
            state = conn.createStatement();
            //5.发送sql,得到结果集
            result = state.executeQuery(sql);
            //6.处理结果
            while(result.next()){
                //字段序号从1开始,每次+1
                int deptno = result.getInt(1);
                String dname = result.getString(2);
                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();
                }
            }

        }

    }
}

3.对用户进行操作
    1.注册用户
    2.登录用户
    3.修改用户信息
    4.注销用户

预处理块的优点 :
    1.防止sql注入
    2.预先编译,可以提高效率
    推荐使用预处理块代替静态处理块

注意:
    在java中操作数据库中修改数据的时候,会自动提交事务

public class Class003_User {
    public static void main(String[] args){
        System.out.println(update("zhangsan","321321"));;

    }

    //修改根据用户名修改用户密码
    public static boolean update(String username,String password){
        //1.获取连接
        Connection conn = null;;
        PreparedStatement ps = null;
        boolean flag = false;

        try {
            conn = DBUtils.getConnection();

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

            //2.构建预处理块
            ps = conn.prepareStatement("update t_user set password=? where username=?");
            //3.为?赋值
            ps.setObject(1,password);
            ps.setObject(2,username);

            //4.执行,得到影响行数
            int rows = ps.executeUpdate();

            //5.判断
            if(rows>0){
                flag = true;
                conn.commit(); //提交
            }else{
                conn.rollback(); //回滚
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DBUtils.close(ps,conn);
        }


        return flag;
    }


    //登录  : 1)根据用户名与密码一起到数据库中查询,查询到了数据登录成功,否则登录失败   2)根据用于名去查询,得到结果的密码值与用户输入的密码比较,相等登录,不等登录失败
    public static boolean login(String username,String password){
        //1.获取连接
        Connection conn = null;
        PreparedStatement state = null;
        ResultSet result = null;
        try {
            conn = DBUtils.getConnection();
            //2.准备sql
            String sql = "select * from t_user where username=? and password=?";

            //3.构建预处理快
            state = conn.prepareStatement(sql);
            //4.需要为sql中的?占位符传递参数
            state.setObject(1,username);
            state.setObject(2,password);

            //5.执行sql,得到结果集
            result = state.executeQuery();  //预处理块新增的方法 executeQuery()  executeUpdate()
           //5.处理结果
            if(result.next()){
                return true;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DBUtils.close(result,state,conn);
        }
        return false;
    }
    //修改
    //注销
    //注册用户
    public static boolean reg(String username,String password){
        //1.获取连接
        Connection conn = null;
        Statement state = null;
        try {
            conn = DBUtils.getConnection();
            //2.准备sql
            String sql = "insert into t_user values('"+username+"',"+password+")";

            //3.构建处理快
            state = conn.createStatement();
            //4.执行sql,得到结果
            int rows  = state.executeUpdate(sql);
            if(rows<=0){
                return false;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DBUtils.close(state,conn);
        }

        return true;
    }
}

4.JDBC工具类
    1.加载驱动
    2.获取连接
    3.关闭资源

public class DBUtils {
    private static Properties pro = new Properties();
    static{
        //1.加载驱动
        //构建 properties对象
        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();
        }
    }

    //2.获取连接
    public static Connection getConnection() throws SQLException {
        Connection conn = null;
        conn = DriverManager.getConnection(
                pro.getProperty("url"),
                pro.getProperty("username"),
                pro.getProperty("password")
        );
        return conn;
    }

    //3.关闭资源
    public static void close(ResultSet result, Statement state,Connection conn){
        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();
            }
        }
    }
    public static void close(Statement state,Connection conn){
        close(null,state,conn);
    }
}

Dept类

package com.yjxxt.entity;

import java.util.Objects;

public class Dept {
    private int deptno;
    private String dname;
    private String loc;

    public Dept() {
    }

    public Dept(int deptno, String dname, String loc) {
        this.deptno = deptno;
        this.dname = dname;
        this.loc = loc;
    }

    public int getDeptno() {
        return deptno;
    }

    public void setDeptno(int deptno) {
        this.deptno = deptno;
    }

    public String getDname() {
        return dname;
    }

    public void setDname(String dname) {
        this.dname = dname;
    }

    public String getLoc() {
        return loc;
    }

    public void setLoc(String loc) {
        this.loc = loc;
    }

    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (o == null || getClass() != o.getClass()) return false;
        Dept dept = (Dept) o;
        return deptno == dept.deptno &&
                Objects.equals(dname, dept.dname) &&
                Objects.equals(loc, dept.loc);
    }

    @Override
    public int hashCode() {
        return Objects.hash(deptno, dname, loc);
    }

    @Override
    public String toString() {
        return "Dept{" +
                "deptno=" + deptno +
                ", dname='" + dname + '\'' +
                ", loc='" + loc + '\'' +
                '}';
    }
}