西科美食评

whisper-h

已于 2023-07-06 11:10:03 修改

阅读量282

点赞数 3

文章标签：数据库

于 2023-07-04 10:32:43 首次发布

本文链接：https://blog.csdn.net/m0_57248752/article/details/131529819

版权

# 创建数据库
create database cate_db character set utf8mb4 collate utf8mb4_general_ci;
use cate_db;
create table user(
	id bigint primary key auto_increment comment '用户id，自增',
    username varchar(50)unique not null comment'用户名',
    password varchar(50) not null comment'密码',
    filepath varchar(255) not null comment'图片地址'
);

create table business(
	id bigint primary key auto_increment comment'商家id,自增',
    username varchar(50)unique not null comment'商家名',
    password varchar(50) not null comment'密码',
    busTime varchar(50) not null comment'营业时间',
    filepath varchar(255) not null comment'图片地址'
);
select id,username,password,filepath from business;


create table food(
	id bigint primary key auto_increment comment'菜品id,自增',
    businessId bigint not null comment'商家Id',
    foodname varchar(50) not null comment'菜品名称',
    price decimal(4,1) not null comment'菜品价格',
    filepath varchar(255) not null comment'图片地址',
    businesspath varchar(255) not null comment'图片地址'
);

create table estimation(
	id bigint primary key auto_increment comment'评价id',
    userId bigint not null comment'用户id',
    businessId bigint not null comment'商家Id',
    foodId bigint not null comment'菜品id',
    content varchar(100) comment'点评内容',
    score  bigint comment'评价分数',
    commentTime datetime not null comment'评价时间',
    filepath1 varchar(255) not null comment'图片地址1',
    filepath2 varchar(255) not null comment'图片地址2',
    filepath3 varchar(255) not null comment'图片地址3',
    filepath4 varchar(255) not null comment'图片地址4',
    filepath5 varchar(255) not null comment'图片地址5'
);
select id,userId,businessId,foodId,content,score,commentTime,filepath1,filepath2,filepath3,filepath4,filepath5 from estimation where businessId=1 order by commentTime desc;
show tables;

insert into user values(null,'温温','111','D:\Desktop\文件\cate\cate_db\photo_user\温简言.png');
insert into estimation values(null,1,1,1,'非常好吃，大家都快来吃呀',5,now(),'1','1','1','1','1');
insert into food values(null,1,'黑椒鸭茶泡饭',12.5,'D:\Desktop\文件\cate\cate_db\photo_food\商家1_1','D:\Desktop\文件\cate\cate_db\photo_business\商家1');
insert into business values(null,'稻炬茶泡饭','111','10:00-20:00','D:\Desktop\文件\cate\cate_db\photo_business\商家1');


drop table user;
drop table food;
drop table estimation;
drop table business;


select * from business;
select * from estimation;
select * from user;
select * from food;

package org.example.dao;

import org.example.model.Business;
import org.example.model.Food;
import org.example.utils.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class BusinessDao {

    public List<Business> selectAll(){
        //进行数据库操作，定义数据库访问的相关对象
        Connection connection=null;
        PreparedStatement statement=null;
        ResultSet resultSet=null;

        try {
            //获取数据库连接
            connection= DBUtil.getConnection();
            //定义sql语句
            String sql="select id,username,password,filepath from business;\n";
            //预处理sql语句
            statement=connection.prepareStatement(sql);
            //执行sql并获取结果
            resultSet=statement.executeQuery();
            List<Business>businessList=new ArrayList<>();
            while(resultSet.next()){
                Business business=new Business();
                business.setId(resultSet.getLong(1));
                business.setUsername(resultSet.getString(2));
                business.setPassword(resultSet.getString(3));
                business.setFilepath(resultSet.getString(4));
                businessList.add(business);
                return businessList;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(resultSet,statement,connection);
        }

        return null;
    }

}

commentDao

package org.example.Dao;

import org.example.model.Comment;
import org.example.utils.DButil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;


public class CommentDao {
    /**
     * 通过商家id获取商家的全部评论
     * @param id
     * @return
     */
    public List<Comment> selectAll(Long id){
        Connection connection=null;
        PreparedStatement statement=null;
        ResultSet resultSet=null;

        try {
            connection= DButil.getConnection();
            String sql="select id,userId,businessId,foodId,content,score,commentTime,filepath1,filepath2,filepath3,filepath4,filepath5 from estimation where businessId=? order by commentTime desc";
            //3.预处理SQL语句
            statement=connection.prepareStatement(sql);
            //4.设置占位符的值
            statement.setLong(1,id);
            //5.执行SQL并获取结果
            resultSet = statement.executeQuery();
            List<Comment>commentList=null;
            while(resultSet.next()){
                if(commentList==null){
                    commentList=new ArrayList<>();
                }
                Comment comment=new Comment();
                comment.setId(resultSet.getLong(1));
                comment.setUserId(resultSet.getLong(2));
                comment.setBusinessId(resultSet.getLong(3));
                comment.setFoodId(resultSet.getLong(4));
                comment.setContent(resultSet.getString(5));
                comment.setScore(resultSet.getInt(6));
                comment.setCommentTime(resultSet.getTimestamp(7));
                comment.setFilePath1(resultSet.getString(8));
                comment.setFilePath2(resultSet.getString(9));
                comment.setFilePath3(resultSet.getString(10));
                comment.setFilePath4(resultSet.getString(11));
                comment.setFilePath5(resultSet.getString(12));
                commentList.add(comment);
            }
            return commentList;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DButil.close(resultSet,statement,connection);
        }
        return null;
    }


}

model:Comment

package org.example.model;

import java.sql.Timestamp;

public class Comment {
    private Long id;
    private Long userId;
    private Long businessId;
    private Long foodId;
    private String content;
    private int score;
    private Timestamp commentTime;
    private String filePath1;
    private String filePath2;
    private String filePath3;
    private String filePath4;
    private String filePath5;

    public Comment(){};

    @Override
    public String toString() {
        return "Comment{" +
                "id=" + id +
                ", userId=" + userId +
                ", businessId=" + businessId +
                ", foodId=" + foodId +
                ", content='" + content + '\'' +
                ", score=" + score +
                ", commentTime=" + commentTime +
                ", filePath1='" + filePath1 + '\'' +
                ", filePath2='" + filePath2 + '\'' +
                ", filePath3='" + filePath3 + '\'' +
                ", filePath4='" + filePath4 + '\'' +
                ", filePath5='" + filePath5 + '\'' +
                '}';
    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public Long getUserId() {
        return userId;
    }

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

    public Long getBusinessId() {
        return businessId;
    }

    public void setBusinessId(Long businessId) {
        this.businessId = businessId;
    }

    public Long getFoodId() {
        return foodId;
    }

    public void setFoodId(Long foodId) {
        this.foodId = foodId;
    }

    public String getContent() {
        return content;
    }

    public void setContent(String content) {
        this.content = content;
    }

    public int getScore() {
        return score;
    }

    public void setScore(int score) {
        this.score = score;
    }

    public Timestamp getCommentTime() {
        return commentTime;
    }

    public void setCommentTime(Timestamp commentTime) {
        this.commentTime = commentTime;
    }

    public String getFilePath1() {
        return filePath1;
    }

    public void setFilePath1(String filePath1) {
        this.filePath1 = filePath1;
    }

    public String getFilePath2() {
        return filePath2;
    }

    public void setFilePath2(String filePath2) {
        this.filePath2 = filePath2;
    }

    public String getFilePath3() {
        return filePath3;
    }

    public void setFilePath3(String filePath3) {
        this.filePath3 = filePath3;
    }

    public String getFilePath4() {
        return filePath4;
    }

    public void setFilePath4(String filePath4) {
        this.filePath4 = filePath4;
    }

    public String getFilePath5() {
        return filePath5;
    }

    public void setFilePath5(String filePath5) {
        this.filePath5 = filePath5;
    }
}