随笔- 124  文章- 1  评论- 0  阅读- 472 
web开发记录19

继续实战房产系统
房产基本信息
CREATE TABLE house_basic_info (
house_id VARCHAR(20) PRIMARY KEY,
room_type ENUM('四室两厅', '四室一厅', '三室两厅', '三室一厅', '两室两厅', '两室一厅') NOT NULL,
address VARCHAR(200) NOT NULL,
year INT NOT NULL,
area DECIMAL(10, 2) NOT NULL,
sales DECIMAL(10, 2) NOT NULL,
status ENUM('在售', '待售', '意向', '售出', '停售') NOT NULL DEFAULT '在售',
agent_id VARCHAR(8),
user_id VARCHAR(20)
);
房产经纪人基本信息表
CREATE TABLE agent_basic_info (
agent_id VARCHAR(8) PRIMARY KEY,
agent_name VARCHAR(50) NOT NULL,
agent_address VARCHAR(200) NOT NULL,
phone VARCHAR(11) NOT NULL
);
顾客基本信息表
CREATE TABLE customer_basic_info (
user_id VARCHAR(20) PRIMARY KEY,
user_name VARCHAR(50) NOT NULL,
sex ENUM('男', '女') NOT NULL,
id_number VARCHAR(18) NOT NULL,
phone VARCHAR(11) NOT NULL,
user_address VARCHAR(200) NOT NULL
);
数据库连接配置
db.url=jdbc:mysql://localhost:3306/real_estate_management?useSSL=false&serverTimezone=UTC
db.username=your_username
db.password=your_password
JavaBean 设计
房产信息
public class House {
private String houseId;
private String roomType;
private String address;
private int year;
private double area;
private double sales;
private String status;
private String agentId;
private String userId;

// 生成对应的 getter 和 setter 方法
public String getHouseId() {
    return houseId;
}

public void setHouseId(String houseId) {
    this.houseId = houseId;
}

public String getRoomType() {
    return roomType;
}

public void setRoomType(String roomType) {
    this.roomType = roomType;
}

// 其他属性的 getter 和 setter 方法依次类推
//...

}
房产经纪人信息
public class House {
private String houseId;
private String roomType;
private String address;
private int year;
private double area;
private double sales;
private String status;
private String agentId;
private String userId;

// 生成对应的 getter 和 setter 方法
public String getHouseId() {
    return houseId;
}

public void setHouseId(String houseId) {
    this.houseId = houseId;
}

public String getRoomType() {
    return roomType;
}

public void setRoomType(String roomType) {
    this.roomType = roomType;
}

// 其他属性的 getter 和 setter 方法依次类推
//...

}
顾客信息
public class Customer {
private String userId;
private String userName;
private String sex;
private String idNumber;
private String phone;
private String userAddress;

// 生成对应的 getter 和 setter 方法
public String getUserId() {
    return userId;
}

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

public String getUserName() {
    return userName;
}

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

// 其他属性的 getter 和 setter 方法依次类推
//...

}
DAO 层设计（数据访问对象，用于数据库操作）
房产信息
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

public class HouseDAO {
private static final String DB_URL;
private static final String DB_USERNAME;
private static final String DB_PASSWORD;

static {
    Properties properties = new Properties();
    try {
        properties.load(HouseDAO.class.getClassLoader().getResourceAsStream("db.properties"));
        DB_URL = properties.getProperty("db.url");
        DB_USERNAME = properties.getProperty("db.username");
        DB_PASSWORD = properties.getProperty("db.password");
    } catch (Exception e) {
        throw new RuntimeException("数据库配置加载失败", e);
    }
}

// 新增房产信息
public void addHouse(House house) {
    try (Connection connection = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);
         PreparedStatement preparedStatement = connection.prepareStatement("INSERT INTO house_basic_info (house_id, room_type, address, year, area, sales, status, agent_id, user_id) VALUES (?,?,?,?,?,?,?,?,?)")) {
        preparedStatement.setString(1, house.getHouseId());
        preparedStatement.setString(2, house.getRoomType());
        preparedStatement.setString(3, house.getAddress());
        preparedStatement.setInt(4, house.getYear());
        preparedStatement.setDouble(5, house.getArea());
        preparedStatement.setDouble(6, house.getSales());
        preparedStatement.setString(7, house.getStatus());
        preparedStatement.setString(8, house.getAgentId());
        preparedStatement.setString(9, house.getUserId());
        preparedStatement.executeUpdate();
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

// 根据房产编号查询房产信息
public House getHouseById(String houseId) {
    try (Connection connection = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);
         PreparedStatement preparedStatement = connection.prepareStatement("SELECT * FROM house_basic_info WHERE house_id =?")) {
        preparedStatement.setString(1, houseId);
        ResultSet resultSet = preparedStatement.executeQuery();
        if (resultSet.next()) {
            House house = new House();
            house.setHouseId(resultSet.getString("house_id"));
            house.setRoomType(resultSet.getString("room_type"));
            house.setAddress(resultSet.getString("address"));
            house.setYear(resultSet.getInt("year"));
            house.setArea(resultSet.getDouble("area"));
            house.setSales(resultSet.getDouble("sales"));
            house.setStatus(resultSet.getString("status"));
            house.setAgentId(resultSet.getString("agent_id"));
            house.setUserId(resultSet.getString("user_id"));
            return house;
        }
        return null;
    } catch (SQLException e) {
        e.printStackTrace();
        return null;
    }
}

// 查询所有房产信息（可根据不同角色权限筛选，如顾客只看在售等）
public List<House> getHousesByCondition(String role, String... conditions) {
    List<House> houses = new ArrayList<>();
    String sql = "SELECT * FROM house_basic_info";
    if ("customer".equals(role)) {
        sql += " WHERE status = '在售'";
    } else if ("agent".equals(role)) {
        // 需结合房产经纪人授权情况添加筛选条件，此处省略具体实现
        sql += " WHERE...";
    }
    // 根据传入的其他查询条件（户型、地址等）拼接 SQL 语句
    if (conditions!= null && conditions.length > 0) {
        sql += " AND (";
        for (int i = 0; i < conditions.length; i++) {
            if (i > 0) {
                sql += " AND ";
            }
            sql += conditions[i];
        }
        sql += ")";
    }
    try (Connection connection = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);
         PreparedStatement preparedStatement = connection.prepareStatement(sql)) {
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            House house = new House();
            // 设置房产信息属性，同 getHouseById 中从 ResultSet 取值并设置属性的过程
            houses.add(house);
        }
        return houses;
    } catch (SQLException e) {
        e.printStackTrace();
        return null;
    }
}

// 更新房产信息（如修改销售状态等）
public void updateHouse(House house) {
    try (Connection connection = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);
         PreparedStatement preparedStatement = connection.prepareStatement("UPDATE house_basic_info SET room_type =?, address =?, year =?, area =?, sales =?, status =?, agent_id =?, user_id =? WHERE house_id =?")) {
        preparedStatement.setString(1, house.getRoomType());
        preparedStatement.setString(2, house.getAddress());
        preparedStatement.setInt(3, house.getYear());
        preparedStatement.setDouble(4, house.getArea());
        preparedStatement.setDouble(5, house.getSales());
        preparedStatement.setString(6, house.getStatus());
        preparedStatement.setString(7, house.getAgentId());
        preparedStatement.setString(8, house.getUserId());
        preparedStatement.setString(9, house.getHouseId());
        preparedStatement.executeUpdate();
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

// 删除房产信息
public void deleteHouse(String houseId) {
    try (Connection connection = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);
         PreparedStatement preparedStatement = connection.prepareStatement("DELETE FROM house_basic_info WHERE house_id =?")) {
        preparedStatement.setString(1, houseId);
        preparedStatement.executeUpdate();
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

}
房产经纪人信息
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

public class AgentDAO {
private static final String DB_URL;
private static final String DB_USERNAME;
private static final String DB_PASSWORD;

static {
    Properties properties = new Properties();
    try {
        properties.load(AgentDAO.class.getClassLoader().getResourceAsStream("db.properties"));
        DB_URL = properties.getProperty("db.url");
        DB_USERNAME = properties.getProperty("db.username");
        DB_PASSWORD = properties.getProperty("db.password");
    } catch (Exception e) {
        throw new RuntimeException("数据库配置加载失败", e);
    }
}

// 新增房产经纪人信息
public void addAgent(Agent agent) {
    try (Connection connection = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);
         PreparedStatement preparedStatement = connection.prepareStatement("INSERT INTO agent_basic_info (agent_id, agent_name, agent_address, phone) VALUES (?,?,?,?)")) {
        preparedStatement.setString(1, agent.getAgentId());
        preparedStatement.setString(2, agent.getAgentName());
        preparedStatement.setString(3, agent.getAgentAddress());
        preparedStatement.setString(4, agent.getPhone());
        preparedStatement.executeUpdate();
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

// 根据工号查询房产经纪人信息
public Agent getAgentById(String agentId) {
    try (Connection connection = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);
         PreparedStatement preparedStatement = connection.prepareStatement("SELECT * FROM agent_basic_info WHERE agent_id =?")) {
        preparedStatement.setString(1, agentId);
        ResultSet resultSet = preparedStatement.executeQuery();
        if (resultSet.next()) {
            Agent agent = new Agent();
            agent.setAgentId(resultSet.getString("agent_id"));
            agent.setAgentName(resultSet.getString("agent_name"));
            agent.setAgentAddress(resultSet.getString("agent_address"));
            agent.setPhone(resultSet.getString("phone"));
            return agent;
        }
        return null;
    } catch (SQLException e) {
        e.printStackTrace();
        return null;
    }
}

// 查询所有房产经纪人信息
public List<Agent> getAllAgents() {
    List<Agent> agents = new ArrayList<>();
    try (Connection connection = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);
         PreparedStatement preparedStatement = connection.prepareStatement("SELECT * FROM agent_basic_info")) {
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            Agent agent = new Agent();
            // 设置房产经纪人信息属性，同 getAgentById 中从 ResultSet 取值并设置属性的过程
            agents.add(agent);
        }
        return agents;
    } catch (SQLException e) {
        e.printStackTrace();
        return null;
    }
}

// 更新房产经纪人信息（如修改密码等）
public void updateAgent(Agent agent) {
    try (Connection connection = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);
         PreparedStatement preparedStatement = connection.prepareStatement("UPDATE agent_basic_info SET agent_name =?, agent_address =?, phone =? WHERE agent_id =?")) {
        preparedStatement.setString(1, agent.getAgentName());
        preparedStatement.setString(2, agent.getAgentAddress());
        preparedStatement.setString(3, agent.getPhone());
        preparedStatement.setString(4, agent.getAgentId());
        preparedStatement.executeUpdate();
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

// 删除房产经纪人信息
public void deleteAgent(String agentId) {
    try (Connection connection = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);
         PreparedStatement preparedStatement = connection.prepareStatement("DELETE FROM agent_basic_info WHERE agent_id =?")) {
        preparedStatement.setString(1, agentId);
        preparedStatement.executeUpdate();
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

}
顾客信息
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

public class CustomerDAO {
private static final String DB_URL;
private static final String DB_USERNAME;
private static final String DB_PASSWORD;

static {
    Properties properties = new Properties();
    try {
        properties.load(CustomerDAO.class.getClassLoader().getResourceAsStream("db.properties"));
        DB_URL = properties.getProperty("db.url");
        DB_USERNAME = properties.getProperty("db.username");
        DB_PASSWORD = properties.getProperty("db.password");
    } catch (Exception e) {// 查询所有顾客信息

public List getAllCustomers() {
List customers = new ArrayList<>();
try (Connection connection = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);
PreparedStatement preparedStatement = connection.prepareStatement("SELECT * FROM customer_basic_info")) {
ResultSet resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {
Customer customer = new Customer();
customer.setUserId(resultSet.getString("user_id"));
customer.setUserName(resultSet.getString("user_name"));
customer.setSex(resultSet.getString("sex"));
customer.setIdNumber(resultSet.getString("id_number"));
customer.setPhone(resultSet.getString("phone"));
customer.setUserAddress(resultSet.getString("user_address"));
customers.add(customer);
}
return customers;
} catch (SQLException e) {
e.printStackTrace();
return null;
}
}

// 根据用户 ID 查询顾客信息
public Customer getCustomerById(String userId) {
try (Connection connection = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);
PreparedStatement preparedStatement = connection.prepareStatement("SELECT * FROM customer_basic_info WHERE user_id =?")) {
preparedStatement.setString(1, userId);
ResultSet resultSet = preparedStatement.executeQuery();
if (resultSet.next()) {
Customer customer = new Customer();
// 设置顾客信息属性，同 getAllCustomers 中从 ResultSet 取值并设置属性的过程
return customer;
}
return null;
} catch (SQLException e) {
e.printStackTrace();
return null;
}
}

// 更新顾客信息（如审核通过等）
public void updateCustomer(Customer customer) {
try (Connection connection = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);
PreparedStatement preparedStatement = connection.prepareStatement("UPDATE customer_basic_info SET user_name =?, sex =?, id_number =?, phone =?, user_address =? WHERE user_id =?")) {
preparedStatement.setString(1, customer.getUserName());
preparedStatement.setString(2, customer.getSex());
preparedStatement.setString(3, customer.getIdNumber());
preparedStatement.setString(4, customer.getPhone());
preparedStatement.setString(5, customer.getUserAddress());
preparedStatement.setString(6, customer.getUserId());
preparedStatement.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
}
}

 posted on   敝屣  阅读(0)  评论(0)  编辑  收藏  举报
相关博文：
·  12.24随笔 java实战2019年考题（1）
·  12.25随笔 java实战2019年试题（2）
·  房产信息管理系统-准备工作
·  房产信息管理系统-新增房产信息/新增房产经纪人信息/用户注册
·  房产信息管理系统--房产交易
阅读排行：
· 25岁的心里话
· 闲置电脑爆改个人服务器（超详细） #公网映射 #Vmware虚拟网络编辑器
· 零经验选手，Compose 一天开发一款小游戏！
· 因为Apifox不支持离线，我果断选择了Apipost！
· 通过 API 将Deepseek响应流式内容输出到前端
点击右上角即可分享
微信分享提示