数据库JDBC(Java DataBase Connectivity)--＞三层架构

可以使用JDBC完成数据库的访问

java程序不能直接访问到某个具体的数据库,这种强耦合的方式不符合开闭原则

• 
• JDBC都是接口,具体JDBC的实现由数据库厂商来实现
• 将来数据库发生变化,只需要更改驱动

模拟数据库连接

• 
• 通过反射创建对象
• 加载类,内部实现
• 
• 
• 
• 
• steatement.close();
• connection.close();

 Connection connection = DriverManager.getConnection
("jdbc:mysql://127.0.0.1:3306/test", "root", "root");
//获取数据库连接
        Class.forName("com.mysql.jdbc.Driver");
//获取字节码对象,加载了 DriverManager 的 regesit (new Driver)
        Statement statement = null;
        try {
            String sql = "INSERT INTO employees values
(null,'礼物','男','1999-9-10',2)";
            statement = (Statement) connection.createStatement();
            int row = statement.executeUpdate(sql);
            if (row>0)
            {
                System.out.println("添加成功");
            }
            else
                System.out.println("失败");
        } catch (Exception throwable) {
            throwable.printStackTrace();
        }finally {
            statement.close();
            connection.close();
        }

 遍历sql语句的方式 

• ResultSet rs = statement.executeQuery(sql);
• //与iterator相似的;但是只有rs.next( )//调用了rs.getnext()方法获取下一个表数据

• rs以结果表为查询表

 查询二部门的员工信息

  public static void main(String[] args)throws  Exception {
//        Driver driver = new com.mysql.jdbc.Driver();
//        DriverManager.registerDriver(driver);
//        Class.forName("com.mysql.jdbc.Driver");
//        //获取服务器连接
   Connection connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test", "root", "root");

       //创建字节码对象并注册驱动
        Class.forName("com.mysql.jdbc.Driver");//mysql的
        DriverManager.getConnection("jdbc:mysql:///test", "root", "root");//jdbc提供的
        Statement statement = null;
        try {
            String sql = "select e.* from departments d \n" +
                    "join employees e on\n" +
                    "d.depid=e.deptid where \n" +
                    "depname='学术部'";
            statement = (Statement) connection.createStatement();

            //int row = statement.executeUpdate(sql);
           ResultSet rs = statement.executeQuery(sql);
           while (rs.next())
           {
               int id = rs.getInt("id");
               String name = rs.getString("name");
               String gender = rs.getString("gender");
               String birth = rs.getString("birth");
               String deptId = rs.getString("deptid");
               System.out.print("name"+name);
               System.out.print("gender"+gender);
               System.out.print("birth"+birth);
               System.out.print("deptif"+deptId);
               System.out.println("   ");

           }


        } catch (Exception throwable) {
            throwable.printStackTrace();
        }finally {
            statement.close();
            connection.close();
        }

封装获取连接方法

  //提供返回对象的方法
    public  static Connection getConnection() throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");//mysql的
        //注册驱动
        java.sql.Connection connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test", "root", "root");
        //获取连接
        return (Connection) connection;
    }
    public static  void  release(Connection con, PreparedStatement pstm) throws Exception {
                con.close(); pstm.close();
                //释放资源
    }

封装获取properties的方法

public class Testnew {
    private  static  String driver;
    private  static  String urll;
    private  static  String users;
    private  static  String passwordd;
    static {
        try {
            Properties prop = null;
            InputStream inputStream = Testnew.class.getClassLoader().getResourceAsStream("db.properties");
            driver = prop.getProperty("jdbc.driver");
            urll = prop.getProperty("jdbc:mysql://127.0.0.1:3306/test");
            users = prop.getProperty("root");
            passwordd = prop.getProperty("root");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

}

properties

jdbc.driver=com.mysql.jdbc.Driver
url = jdbc:mysql://127.0.0.1:3306/test
user = root
password=root

业务实现

 public static void main(String[] args) throws Exception {
        Scanner sc = new Scanner(System.in);
        String next1 = sc.next();
        int next2 = sc.nextInt();
        Connection con = JDBCUtils.getConnection();
        String sql = "SELECT ?,?  FROM account ";
        PreparedStatement pstatement = (PreparedStatement) con.prepareStatement(sql);
        pstatement.setString(1,next1);
        pstatement.setInt(2,next2);
        pstatement.executeQuery();
        System.out.println("完成");
        JDBCUtils.release(con,pstatement);

    }

分层开发(三层架构)

• 将每个类的功能分开,一个类只处理一定的业务;

 

 三层架构例子

代码顺序:DAO ->SERVICES->UI ->login

登录操作:login->UI ->SERVICE -.>DAO ->DB

• dao层:数据访问层 

• public String queryPasswordByUsername(String username) throws Exception {
          //获取连接
          Connection connection =JDBCUtils.getConnection();
          String sql = "select password from user where username=? ";
          PreparedStatement pstatement = (PreparedStatement) connection.prepareStatement(sql);
          pstatement.setString(1,username);
           ResultSet set=pstatement.executeQuery();
  
          String password = null;
          if (set.next()){
              password= set.getString(1);
  
          }
          return password;
      }

  services

public class UserService {
  private   UserDao userDao = new UserDao();
    public boolean checkUserLogin(String username,String password)
    {
        try {
            String realpass= userDao.queryPasswordByUsername(username);
                if (password.equals(realpass))
                {
                    return true;
                }
                else
                    return false;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }

    }
}

ui层

public class UserUi {
    private UserService userService = new UserService();
    public  void  login(){
        String username,password;
        Scanner scanner =new Scanner(System.in);
        System.out.println("输入用户");
        username = scanner.next();
        System.out.println("输入密码");
        password = scanner.next();
        boolean b =userService.checkUserLogin(username,password);
        if (b){
            System.out.println("登录成功");
        }else
            System.out.println("登录失败");
    }
}

JDBCUtils工具类

public class JDBCUtils {
    public  static Connection getConnection() throws Exception {
        Class.forName("com.mysql.jdbc.Driver");//mysql的
        //注册驱动
        java.sql.Connection connection = DriverManager.getConnection("jdbc:mysql:///companydb", "root", "root");
        //获取连接
        return (Connection) connection;
    }
    public static  void  release(Connection con, PreparedStatement pstm) throws Exception {
        con.close(); pstm.close();
        //释放资源
    }
}

login界面

public class login {
    public static void main(String[] args) throws Exception {
        UserUi ui = new UserUi();
        ui.login();

    }
}