Hotel_service_system/AccountDaoImp.java

package com.lsy.dao.impl;

import com.lsy.dao.AccountDao;
import com.lsy.model.Account;


import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class AccountDaoImp implements AccountDao {


    @Override
    public List<Account> findAll() {
        Account account;
        List<Account> list = new ArrayList<>();
        String sql = "select * from accountlsy ";
        try (
                //1.创建数据库连接
                Connection conn = DBHelper.getConnection();
                //2.创建语句对象
                PreparedStatement pstm = conn.prepareStatement(sql);
                //3.执行查询
                ResultSet rs = pstm.executeQuery();) {
            // 6.遍历结果集
            while (rs.next()) {
                account = new Account();

                account.setUserid(rs.getString("userid"));
                account.setPassword(rs.getString("password"));
                account.setIDcard(rs.getString("IDcard"));
                account.setName(rs.getString("name"));
                account.setPhone(rs.getString("phone"));
                account.setRoomN(rs.getString("roomN"));

            }
        } catch (Exception e) {
            e.printStackTrace();
        }

        return list;
    }

    @Override
    public Account findById(String userid) {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        Account account = null;
        try {
            conn = DBHelper.getConnection();
            String sql = "select * from accountlsy where userid = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, userid);
            rs = pstmt.executeQuery();
            if (rs.next()) {
                account = new Account();

                account.setUserid(rs.getString("userid"));
                account.setPassword(rs.getString("password"));
                account.setIDcard(rs.getString("IDcard"));
                account.setName(rs.getString("name"));
                account.setPhone(rs.getString("phone"));
                account.setRoomN(rs.getString("roomN"));

                    }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                }
            }
            if (pstmt != null) {
                try {
                    pstmt.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }

        }
        return account;
    }

    @Override
    public int create(Account account) {

        String sql = "insert into accountlsy values(?,?,?,?,?,?)";
        try (
                Connection connection = DBHelper.getConnection();
                PreparedStatement pstm = connection.prepareStatement(sql);
        ) {
            // 6.遍历结果集
            pstm.setString(1, account.getUserid());
            pstm.setString(2, account.getPassword());
            pstm.setString(5, account.getRoomN());
            pstm.setString(4, account.getName());
            pstm.setString(6, account.getPhone());
            pstm.setString(3, account.getIDcard());
            int a = pstm.executeUpdate();
            System.out.printf("成功插入%d数据\n", a);
        } catch (Exception e) {
            e.printStackTrace();
        }

        return 0;

    }

    @Override
    public int delect(Account account) {

        String sql = "DELETE  from accountlsy where userid= ?";

        try (
                Connection connection = DBHelper.getConnection();

                PreparedStatement pstm = connection.prepareStatement(sql)) {

            pstm.setString(1, account.getUserid());

            int affectedRows = pstm.executeUpdate();
            System.out.printf("成功删除%d条数据。\n", affectedRows);


        } catch (Exception e) {
            e.printStackTrace();
        }
        return 0;
    }




}