第十六章 DAO模式(分层)

1.练习

学生表:学号，姓名，性别，生日

①完成BaseDAO所有内容；

②使用BaseDAO实现增删改查。

 BaseDAO工具类：

public class BaseDAO {
    private static final String DRIVER = "com.mysql.cj.jdbc.Driver";
    private static final String URL = "jdbc:mysql://127.0.0.1:3306/school_db?useSSL=false&characterEncoding=utf8&serverTimezone=Asia/Shanghai";
    private static final String USER = "root";
    private static final String PASSWORD = "123456";

    private static Connection con = null;
    private static PreparedStatement pst = null;
    private static ResultSet rs = null;
    
    private static void getConnection(){
        try {
            Class.forName(DRIVER);
            con = DriverManager.getConnection(URL,USER,PASSWORD);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
    public static void setPst(String sql,Object[] params){
        getConnection();
        if(params==null){
            try {
                pst = con.prepareStatement(sql);
                return;
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

        try {
            pst = con.prepareStatement(sql);
            for (int i = 0; i < params.length; i++) {
                pst.setObject(i+1,params[i]);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
    public static int executeUpdate(){
        int count = -1;
        try {
            count = pst.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        closeAll();
        return count;
    }

    public static List<Map<String,Object>> executeQuery(){
        try {
            rs = pst.executeQuery();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        List<Map<String,Object>> rows = new ArrayList<>();
        try {
            ResultSetMetaData rsMd = rs.getMetaData();
            int colCount = rsMd.getColumnCount();
            while (rs.next()){
                Map map = new HashMap();
                for (int i = 1; i <= colCount; i++) {
                    String colName = rsMd.getColumnLabel(i);
                    Object colVal = rs.getObject(i);
                    map.put(colName,colVal);
                }
                rows.add(map);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        closeAll();
        return rows;
    }
    private static void closeAll(){
        if(rs!=null){
            try {
                rs.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if(pst!=null){
            try {
                pst.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if(con!=null){
            try {
                con.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}

测试:

public class MyTest {
    @Test
    public void myTest1(){
        String sql = "insert into stu " +
                "   values" +
                "   (?,?,?,?)";
        Object[] params = {1002,"wen","man","2009-1-1"};

        BaseDAO.setPst(sql,params);
        int count = BaseDAO.executeUpdate();
        System.out.println(count);
    }
    @Test
    public void test2(){
        String sql = "select * from stu";
        BaseDAO.setPst(sql,null);

        List<Map<String,Object>> rows = BaseDAO.executeQuery();

        for (Map map:rows) {
            System.out.println(map);
        }
    }
}

2. IDEA  Database 的使用

 

 

 3. DAO模式

dao:database access object 数据访问对象

开发过程中的项目的架构：(分层模式)

 显示层：jsp，显示数据，用户交换

控制层：用来控制前端的请求，交给后端的业务层去处理

业务层：具体的业务逻辑(Bussiness)，服务层(Service)，在实现业务的过程中可以调用一系列数据层的数据操作

数据层：原子级别的数据操作(增删改查CRUD)

实体层：封装数据，在各层之间传递

3.1 实体层

实体类：数据的载体

3.2 数据访问层

BaseDao：封装过了通用的数据操作

dao接口：提供的CRUD的标准

dao实现类：实现接口的方法

3.3 业务层

service接口：定义业务接口方法

service实现类：实现业务接口的方法

3.4 dao的实现步骤

确定业务功能(图书管理)

①建库建表: Book
②创建java项目
③添加jar包: 数据库jar包，并引用
④创建util包，创建BaseDAO工具类
⑤创建实体包，创建实体类
  实体包: entity,
  实体类: User(实体类一般和表名一致)

⑥创建数据访问层包/实现包,创建数据访问层接口,创建数据访问层实现类
  数据访问层包:    dao
  数据访问层接口: IUserDAO
  数据访问层实现包: impl
  数据访问层实现类: UserDAO
⑦创建业务层包/实现包,业务层接口,业务层实现类
  业务层包: service
  业务层层接口: IUserService
  业务层实现包: impl
  业务层实现类: UserServiceImpl
⑧创建程序入口，进行测试

①util>BaseDAO

②entity>User

public class User {
    private Integer userId;
    private String userName;
    private String password;
    private String nickName;
    private String sex;
    private String phone;

    public String getPhone() {
        return phone;
    }

    public void setPhone(String phone) {
        this.phone = phone;
    }

    public User(){}

    public User(String userName, String password, String nickName, String sex,String phone) {
        this.userName = userName;
        this.password = password;
        this.nickName = nickName;
        this.sex = sex;
        this.phone = phone;
    }

    public Integer getUserId() {
        return userId;
    }

    public void setUserId(Integer userId) {
        this.userId = userId;
    }

    public String getUserName() {
        return userName;
    }

    public void setUserName(String userName) {
        this.userName = userName;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getNickName() {
        return nickName;
    }

    public void setNickName(String nickName) {
        this.nickName = nickName;
    }

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    @Override
    public String toString() {
        return "Users{" +
                "userId=" + userId +
                ", userName='" + userName + '\'' +
                ", password='" + password + '\'' +
                ", nickName='" + nickName + '\'' +
                ", sex='" + sex + '\'' +
                '}';
    }
}

③dao>IUserDAO

public interface IUserDAO {
    /**
     * 根据用户和密码查询用户账号
     * @param userName
     * @param password
     * @return
     */
    User getByUserNameAndPassword(String userName,String password);

    /**
     * 判读用户名是否存在
     * @param userName
     * @return
     */
    boolean getByUserName(String userName);

    /**
     * 查询手机号是否存在
     * @param phone
     * @return
     */
    boolean getByPhone(String phone);

    /**
     * 新用户注册
     * @param user
     * @return
     */
    int insert(User user);
}

dao>impl>UserDAO

public class UserDAO implements IUserDAO {
    private List<Map<String,Object>> row = null;
    private Object[] params;
    @Override
    public User getByUserNameAndPassword(String userName, String password) {
        String sql = "select user_id,user_name,user_password,user_nickname,user_sex,user_phone" +
                "   from users  " +
                "   where user_name = ? and user_password = ? ";
        params = new Object[]{userName, password};
        BaseDAO.setPst(sql,params);
        row = BaseDAO.executeQuery();
        if(row.size()>0){
            Map map = row.get(0);
            User user = new User();
            user.setUserId((Integer) map.get("user_id"));
            user.setUserName((String)map.get("user_name"));
            user.setPassword((String)map.get("user_password"));
            user.setNickName((String)map.get("user_nickname"));
            user.setSex((String)map.get("user_sex"));
            user.setPhone((String)map.get("user_phone"));

            return user;
        }
        return null;
    }

    @Override
    public boolean getByUserName(String userName) {
        String sql = "select user_id from users where user_name = ?";
        params = new Object[]{userName};
        BaseDAO.setPst(sql,params);
        row = BaseDAO.executeQuery();
        return row.size()>0;
    }

    @Override
    public boolean getByPhone(String phone) {
        String sql = "select user_id from users where user_phone = ?";
        params = new Object[]{phone};
        BaseDAO.setPst(sql,params);
        row = BaseDAO.executeQuery();
        return row.size()>0;
    }

    @Override
    public int insert(User user) {
        String sql = "insert into users(user_name, user_password, user_nickname, user_sex, user_phone)" +
                "   value " +
                "   (?,?,?,?,?)";
        params = new Object[]{
                user.getUserName(),
                user.getPassword(),
                user.getNickName(),
                user.getSex(),
                user.getPhone()
        };
        BaseDAO.setPst(sql,params);
        return BaseDAO.executeUpdate();
    }
}

④service>IUserService

public interface IUserService {
    /**
     * 用户登录
     * @return
     */
    boolean login();

    /**
     * 注册
     * @return
     */
    boolean register();
}

service>impl>UserServiceImpl

public class UserServiceImpl implements IUserService {
    Scanner scanner = new Scanner(System.in);
    IUserDAO userDAO = new UserDAO();
    User user =null;

    public User getUser() {
        return user;
    }

    @Override
    public boolean login() {
        while(true){
            System.out.println("请输入用户名");
            String user_name = scanner.next();
            System.out.println("请输入密码");
            String user_password = scanner.next();

            if(StringUtils.isNullOrEmpty(user_name)){
                System.out.println("账号不能为空!");
            }else if(StringUtils.isNullOrEmpty(user_password)){
                System.out.println("密码不能为空");
            }else {

                user = userDAO.getByUserNameAndPassword(user_name,user_password);
                if(user==null){
                    System.out.println("账号或密码错误!");
                    return false;
                }
                System.out.println("登录成功!");
                return true;
            }
        }
    }

    @Override
    public boolean register() {
        String[] sts = {"用户名:","密码:","昵称:","性别:","手机号:"};
        List<String> list = new ArrayList<>();
        IUserDAO userDAO = new UserDAO();
        for (int i = 0; i < sts.length; i++) {
            System.out.println("请输入你的"+sts[i]);
            list.add(scanner.next());
        }
        if(userDAO.getByUserName(list.get(0))){
            System.out.println("用户名不可用(已存在)");
            return false;
        }
        if(userDAO.getByPhone(list.get(4))){
            System.out.println("手机号已存在");
            return false;
        }
        User user = new User(list.get(0),list.get(1),list.get(2),list.get(3),list.get(4));
        if(userDAO.insert(user)>0){
            return true;
        }
        System.out.println("未知原因注册失败");
        return false;
    }
}

⑤app>App  测试

public class App {
    public static void main(String[] args) {
        System.out.println("===========");
        System.out.println("=图书馆里系统=");
        System.out.println("===========");
        Scanner scanner = new Scanner(System.in);
        while (true){
            System.out.println("请选择:1.管理员登录 2.用户登录 3.用户注册 4.退出");
            IUserService iUserService = new UserServiceImpl();
            switch (scanner.nextInt()){
                case 1:
                    System.out.println("管理员你好,请登录!");
                    break;
                case 2:
                    System.out.println("用户你好，请登录");
                    if(iUserService.login()){

                    }
                    break;
                case 3:
                    System.out.println("欢迎注册!");
                    if(iUserService.register()){
                        System.out.println("注册成功!");
                    }
                    break;
                case 4:
                    System.out.println("感谢使用!");
                    return;
                default:
                    System.out.println("输入错误!");
                    break;
            }
        }
    }
}