千锋Java&Day41课后作业

于 2020-03-29 17:15:24 发布
阅读量244 收藏
点赞数
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/weixin_44015626/article/details/105181583
版权

综合练习(作业)JDBC的核心六步实现增、删、改

某网上商城数据库表结构如下：

# 创建用户表
create table user(
	 userId int primary key auto_increment,
  	 username varchar(20) not null,
  	 password varchar(18) not null,
     address varchar(100),
     phone varchar(11)
);

#创建分类表
create table category(
  cid varchar(32) PRIMARY KEY ,
  cname varchar(100) not null		#分类名称
);

# 商品表
CREATE TABLE `products` (
  `pid` varchar(32) PRIMARY KEY,
  `name` VARCHAR(40) ,
  `price` DOUBLE(7,2),
   category_id varchar(32),
   constraint foreign key(category_id) references category(cid)
);

#订单表
create table `orders`(
  `oid` varchar(32) PRIMARY KEY ,
  `totalprice` double(12,2), #总计
  `userId` int,
   constraint foreign key(userId) references user(userId) #外键
);

# 订单项表
create table orderitem(
  oid varchar(32),	#订单id
  pid varchar(32),	#商品id
  num int ,         #购买商品数量
  primary key(oid,pid), #主键
  foreign key(oid) references orders(oid),
  foreign key(pid) references products(pid)
);

#-----------------------------------------------
#初始化数据

#用户表添加数据
INSERT INTO USER(username,PASSWORD,address,phone) VALUES('张三','123','北京昌平沙河','13812345678');
INSERT INTO USER(username,PASSWORD,address,phone) VALUES('王五','5678','北京海淀','13812345141');
INSERT INTO USER(username,PASSWORD,address,phone) VALUES('赵六','123','北京朝阳','13812340987');
INSERT INTO USER(username,PASSWORD,address,phone) VALUES('田七','123','北京大兴','13812345687');

#给商品表初始化数据
insert into products(pid,name,price,category_id) values('p001','联想',5000,'c001');
insert into products(pid,name,price,category_id) values('p002','海尔',3000,'c001');
insert into products(pid,name,price,category_id) values('p003','雷神',5000,'c001');
insert into products(pid,name,price,category_id) values('p004','JACK JONES',800,'c002');
insert into products(pid,name,price,category_id) values('p005','真维斯',200,'c002');
insert into products(pid,name,price,category_id) values('p006','花花公子',440,'c002');
insert into products(pid,name,price,category_id) values('p007','劲霸',2000,'c002');
insert into products(pid,name,price,category_id) values('p008','香奈儿',800,'c003');
insert into products(pid,name,price,category_id) values('p009','相宜本草',200,'c003');
insert into products(pid,name,price,category_id) values('p010','梅明子',200,null);


#给分类表初始化数据
insert into category values('c001','电器');
insert into category values('c002','服饰');
insert into category values('c003','化妆品');
insert into category values('c004','书籍');

#添加订单
insert into orders values('o6100',18000.50,1);
insert into orders values('o6101',7200.35,1);
insert into orders values('o6102',600.00,2);
insert into orders values('o6103',1300.26,4);

#订单详情表
insert into orderitem values('o6100','p001',1),('o6100','p002',1),('o6101','p003',1);

15.1 综合练习1-【多表查询】

1>查询所有用户的订单

SELECT o.`userId`, username,oid,totalprice FROM USER AS u
INNER JOIN orders AS o
ON u.`userId` = o.`userId`;

2>查询用户id为 1 的所有订单详情

SELECT o.`userId`, username,oid,totalprice FROM USER AS u
INNER JOIN orders AS o
ON u.`userId` = o.`userId` AND u.`userId`=1;

15.2 综合练习2-【子查询】

1>查看用户为张三的订单

SELECT * FROM orders
WHERE userId IN
(SELECT userId FROM USER WHERE	username='张三')

2>查询出订单的价格大于800的所有用户信息。

SELECT * FROM USER WHERE userId IN (
	SELECT DISTINCT userId FROM orders WHERE totalprice>800);

15.3 综合练习3-【分页查询】

1>查询所有订单信息，每页显示5条数据

SELECT * FROM orders LIMIT 0,5;

15.4 综合练习4-【使用JDBC实现对每张表的增、删、改】

用户表UserTable

public class UserTable {
    public static void main(String[] args) throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        String url = "jdbc:mysql://localhost:3306/WebShop?useUnicode=true&characterEncoding=utf8";
        String user = "root";
        String password = "1234";
        Connection connection = DriverManager.getConnection(url, user, password);
        Statement statement = connection.createStatement();

        //增
        String insertSql = "insert into user(username,password,address,phone) values ('小明','12345','河南','13273713042')";
        int insertResult = statement.executeUpdate(insertSql);
        System.out.print("插入操作");
        check(insertResult);

        //改
        String updateSql = "update user set address='河南郑州' where username = '小明'";
        int updateResult = statement.executeUpdate(updateSql);
        System.out.print("修改操作");
        check(updateResult);

        //删
        String deleteSql = "delete from user where username='小明'";
        int deleteResult = statement.executeUpdate(deleteSql);
        System.out.print("删除操作");
        check(deleteResult);
    }

    //检查操作是否成功
    public static void check(int result) {
        if (result > 0) {
            System.out.println("成功");
        } else {
            System.out.println("失败");
        }
    }
}

分类表CategoryTable

public class CategoryTable {
    public static void main(String[] args) throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        String url = "jdbc:mysql://localhost:3306/WebShop?useUnicode=true&characterEncoding=utf8";
        String user = "root";
        String password = "1234";
        Connection connection = DriverManager.getConnection(url, user, password);
        Statement statement = connection.createStatement();

        //增
        String insertSql = "insert into category(cid,cname) values ('c005','百草味')";
        int insertResult = statement.executeUpdate(insertSql);
        System.out.print("插入操作");
        check(insertResult);

        //改
        String updateSql = "update category set cname='三只松鼠' where cid = 'c005'";
        int updateResult = statement.executeUpdate(updateSql);
        System.out.print("修改操作");
        check(updateResult);

        //删
        String deleteSql = "delete from category where cid = 'c005'";
        int deleteResult = statement.executeUpdate(deleteSql);
        System.out.print("删除操作");
        check(deleteResult);
    }

    //检查操作是否成功
    public static void check(int result) {
        if (result > 0) {
            System.out.println("成功");
        } else {
            System.out.println("失败");
        }
    }
}

商品表ProductsTable

public class ProductsTable {
    public static void main(String[] args) throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        String url = "jdbc:mysql://localhost:3306/WebShop?useUnicode=true&characterEncoding=utf8";
        String user = "root";
        String password = "1234";
        Connection connection = DriverManager.getConnection(url, user, password);
        Statement statement = connection.createStatement();

        //增
        String insertSql = "insert into products(pid,name,price,category_id) values ('p011','ROG',14999,'c004')";
        int insertResult = statement.executeUpdate(insertSql);
        System.out.print("插入操作");
        check(insertResult);

        //改
        String updateSql = "update products set price=15999 where pid = 'p011'";
        int updateResult = statement.executeUpdate(updateSql);
        System.out.print("修改操作");
        check(updateResult);

        //删
        String deleteSql = "delete from products where pid = 'p011'";
        int deleteResult = statement.executeUpdate(deleteSql);
        System.out.print("删除操作");
        check(deleteResult);
    }

    //检查操作是否成功
    public static void check(int result) {
        if (result > 0) {
            System.out.println("成功");
        } else {
            System.out.println("失败");
        }
    }
}

订单表OrdersTable

public class OrdersTable {
    public static void main(String[] args) throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        String url = "jdbc:mysql://localhost:3306/WebShop?useUnicode=true&characterEncoding=utf8";
        String user = "root";
        String password = "1234";
        Connection connection = DriverManager.getConnection(url, user, password);
        Statement statement = connection.createStatement();

        //增
        String insertSql = "insert into orders(oid,totalprice,userId) values ('o6104',1550.00,'3')";
        int insertResult = statement.executeUpdate(insertSql);
        System.out.print("插入操作");
        check(insertResult);

        //改
        String updateSql = "update orders set totalprice=1650 where oid='o6104'";
        int updateResult = statement.executeUpdate(updateSql);
        System.out.print("修改操作");
        check(updateResult);

        //删
        String deleteSql = "delete from orders where oid='o6104'";
        int deleteResult = statement.executeUpdate(deleteSql);
        System.out.print("删除操作");
        check(deleteResult);
    }

    //检查操作是否成功
    public static void check(int result) {
        if (result > 0) {
            System.out.println("成功");
        } else {
            System.out.println("失败");
        }
    }
}

订单项表 OrderItemTable

public class OrderItemTable {
    public static void main(String[] args) throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        String url = "jdbc:mysql://localhost:3306/WebShop?useUnicode=true&characterEncoding=utf8";
        String user = "root";
        String password = "1234";
        Connection connection = DriverManager.getConnection(url, user, password);
        Statement statement = connection.createStatement();

        //增
        String insertSql = "insert into orderitem(oid,pid,num) values ('o6102','p002',1)";
        int insertResult = statement.executeUpdate(insertSql);
        System.out.print("插入操作");
        check(insertResult);

        //改
        String updateSql = "update orderitem set num='2' where oid = 'o6102'";
        int updateResult = statement.executeUpdate(updateSql);
        System.out.print("修改操作");
        check(updateResult);

        //删
        String deleteSql = "delete from orderitem where oid='o6102'";
        int deleteResult = statement.executeUpdate(deleteSql);
        System.out.print("删除操作");
        check(deleteResult);
    }

    //检查操作是否成功
    public static void check(int result) {
        if (result > 0) {
            System.out.println("成功");
        } else {
            System.out.println("失败");
        }
    }
}
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值