2021-04-25

Server
BusiHandler

package cn.enjoyedu.nettyhttp.server;

import io.netty.buffer.Unpooled;
import io.netty.channel.ChannelFutureListener;
import io.netty.channel.ChannelHandlerContext;
import io.netty.channel.ChannelInboundHandlerAdapter;
import io.netty.handler.codec.http.*;
import io.netty.util.CharsetUtil;

/**
 * @author Mark老师   享学课堂 https://enjoy.ke.qq.com
 * 往期课程和VIP课程咨询 依娜老师  QQ:2133576719
 * 类说明:
 */
public class BusiHandler extends ChannelInboundHandlerAdapter {

    /**
     * 发送的返回值
     * @param ctx     返回
     * @param context 消息
     * @param status 状态
     */
    private void send(ChannelHandlerContext ctx, String context,
                      HttpResponseStatus status) {
        FullHttpResponse response = new DefaultFullHttpResponse(
                HttpVersion.HTTP_1_1,status,
                Unpooled.copiedBuffer(context,CharsetUtil.UTF_8)
        );
        response.headers().set(HttpHeaderNames.CONTENT_TYPE,"text/plain;charset=UTF-8");
        ctx.writeAndFlush(response).addListener(ChannelFutureListener.CLOSE);
    }

    @Override
    public void channelRead(ChannelHandlerContext ctx, Object msg) throws Exception {
        String result="";
        FullHttpRequest httpRequest = (FullHttpRequest)msg;
        System.out.println(httpRequest.headers());
        try{
            //获取路径
            String path=httpRequest.uri();
            //获取body
            String body = httpRequest.content().toString(CharsetUtil.UTF_8);
            //获取请求方法
            HttpMethod method=httpRequest.method();
            System.out.println("接收到:"+method+" 请求");
            //如果不是这个路径,就直接返回错误
            if(!"/test".equalsIgnoreCase(path)){
                result="非法请求!"+path;
                send(ctx,result,HttpResponseStatus.BAD_REQUEST);
                return;
            }

            //如果是GET请求
            if(HttpMethod.GET.equals(method)){
                //接受到的消息,做业务逻辑处理...
                System.out.println("body:"+body);
                result="GET请求,应答:"+RespConstant.getNews();
                send(ctx,result,HttpResponseStatus.OK);
                return;
            }
            //如果是其他类型请求,如post
            if(HttpMethod.POST.equals(method)){
                //接受到的消息,做业务逻辑处理...
                //....
                return;
            }

        }catch(Exception e){
            System.out.println("处理请求失败!");
            e.printStackTrace();
        }finally{
            //释放请求
            httpRequest.release();
        }
    }

    /*
     * 建立连接时,返回消息
     */
    @Override
    public void channelActive(ChannelHandlerContext ctx) throws Exception {
        System.out.println("连接的客户端地址:" + ctx.channel().remoteAddress());
    }
}

HttpServer

package cn.enjoyedu.nettyhttp.server;

import io.netty.bootstrap.ServerBootstrap;
import io.netty.channel.ChannelFuture;
import io.netty.channel.EventLoopGroup;
import io.netty.channel.nio.NioEventLoopGroup;
import io.netty.channel.socket.nio.NioServerSocketChannel;
import io.netty.handler.ssl.SslContext;
import io.netty.handler.ssl.SslContextBuilder;
import io.netty.handler.ssl.util.SelfSignedCertificate;

/**
 * @author Mark老师   享学课堂 https://enjoy.ke.qq.com
 * 往期课程和VIP课程咨询 依娜老师  QQ:2133576719
 * 类说明:
 */
public class HttpServer {
    public static final int port = 6789; //设置服务端端口
    private static EventLoopGroup group = new NioEventLoopGroup();   // 通过nio方式来接收连接和处理连接
    private static ServerBootstrap b = new ServerBootstrap();
    private static final boolean SSL = false;/*是否开启SSL模式*/

    /**
     * Netty创建全部都是实现自AbstractBootstrap。
     * 客户端的是Bootstrap,服务端的则是ServerBootstrap。
     **/
    public static void main(String[] args) throws Exception {
        final SslContext sslCtx;
        if(SSL){
            SelfSignedCertificate ssc = new SelfSignedCertificate();
            sslCtx = SslContextBuilder.forServer(ssc.certificate(),
                    ssc.privateKey()).build();

        }else{
            sslCtx = null;
        }
        try {
            b.group(group);
            b.channel(NioServerSocketChannel.class);
            b.childHandler(new ServerHandlerInit(sslCtx)); //设置过滤器
            // 服务器绑定端口监听
            ChannelFuture f = b.bind(port).sync();
            System.out.println("服务端启动成功,端口是:"+port);
            // 监听服务器关闭监听
            f.channel().closeFuture().sync();
        } finally {
            group.shutdownGracefully(); //关闭EventLoopGroup,释放掉所有资源包括创建的线程
        }
    }
}

RespConstant

package cn.enjoyedu.nettyhttp.server;

import java.util.Random;

/**
 * @author Mark老师   享学课堂 https://enjoy.ke.qq.com
 * 往期课程和VIP课程咨询 依娜老师  QQ:2133576719
 * 类说明:
 */
public class RespConstant {
    private static final String[] NEWS = {
            "她那时候还太年轻,不知道所有命运赠送的礼物,早已在暗中标好了价格。——斯蒂芬·茨威格《断头皇后》",
            "这是一个最好的时代,也是一个最坏的时代;这是一个智慧的年代,这是一个愚蠢的年代;\n" +
                    "这是一个信任的时期,这是一个怀疑的时期;这是一个光明的季节,这是一个黑暗的季节;\n" +
                    "这是希望之春,这是失望之冬;人们面前应有尽有,人们面前一无所有;\n" +
                    "人们正踏上天堂之路,人们正走向地狱之门。 —— 狄更斯《双城记》",
            };

    private static final Random R = new Random();

    public static String getNews(){
        return NEWS[R.nextInt(NEWS.length)];
    }

}

ServerHandleInit

package cn.enjoyedu.nettyhttp.server;

import io.netty.channel.ChannelInitializer;
import io.netty.channel.ChannelPipeline;
import io.netty.channel.socket.SocketChannel;
import io.netty.handler.codec.http.HttpContentCompressor;
import io.netty.handler.codec.http.HttpObjectAggregator;
import io.netty.handler.codec.http.HttpRequestDecoder;
import io.netty.handler.codec.http.HttpResponseEncoder;
import io.netty.handler.ssl.SslContext;

/**
 * @author Mark老师   享学课堂 https://enjoy.ke.qq.com
 * 往期课程和VIP课程咨询 依娜老师  QQ:2133576719
 * 类说明:
 */
public class ServerHandlerInit extends ChannelInitializer<SocketChannel> {

    private final SslContext sslCtx;

    public ServerHandlerInit(SslContext sslCtx) {
        this.sslCtx = sslCtx;
    }

    @Override
    protected void initChannel(SocketChannel ch) throws Exception {
        ChannelPipeline ph = ch.pipeline();
        if(sslCtx!=null){
            ph.addLast(sslCtx.newHandler(ch.alloc()));
        }
        /*把应答报文 编码*/
        ph.addLast("encoder",new HttpResponseEncoder());
        /*把请求报文 解码*/
        ph.addLast("decoder",new HttpRequestDecoder());


        /*聚合http为一个完整的报文*/
        ph.addLast("aggregator",
                new HttpObjectAggregator(10*1024*1024));
        /*把应答报文 压缩,非必要*/
        ph.addLast("compressor",new HttpContentCompressor());
        ph.addLast(new BusiHandler());


    }
}

Client
HttpClient

package cn.enjoyedu.nettyhttp.client;

import cn.enjoyedu.nettyhttp.server.HttpServer;
import io.netty.bootstrap.Bootstrap;
import io.netty.buffer.Unpooled;
import io.netty.channel.ChannelFuture;
import io.netty.channel.ChannelInitializer;
import io.netty.channel.ChannelOption;
import io.netty.channel.EventLoopGroup;
import io.netty.channel.nio.NioEventLoopGroup;
import io.netty.channel.socket.SocketChannel;
import io.netty.channel.socket.nio.NioSocketChannel;
import io.netty.handler.codec.http.*;

import java.net.URI;

/**
 * @author Mark老师   享学课堂 https://enjoy.ke.qq.com
 * 往期课程和VIP课程咨询 依娜老师  QQ:2133576719
 * 类说明:
 */
public class HttpClient {
    public static final String HOST  = "127.0.0.1";
    private static final boolean SSL = false;
    public void connect(String host, int port) throws Exception {
        EventLoopGroup workerGroup = new NioEventLoopGroup();
        try {
            Bootstrap b = new Bootstrap();
            b.group(workerGroup);
            b.channel(NioSocketChannel.class);
            b.option(ChannelOption.SO_KEEPALIVE, true);
            b.handler(new ChannelInitializer<SocketChannel>() {
                @Override
                public void initChannel(SocketChannel ch) throws Exception {
                    ch.pipeline().addLast(new HttpClientCodec());
                    /*聚合http为一个完整的报文*/
                    ch.pipeline().addLast("aggregator",
                            new HttpObjectAggregator(10*1024*1024));
                    /*解压缩*/
                    ch.pipeline().addLast("decompressor",
                            new HttpContentDecompressor());
                    ch.pipeline().addLast(new HttpClientInboundHandler());
                }
            });

            // Start the client.
            ChannelFuture f = b.connect(host, port).sync();
            f.channel().closeFuture().sync();
        } finally {
            workerGroup.shutdownGracefully();
        }
    }

    public static void main(String[] args) throws Exception {
        HttpClient client = new HttpClient();
        client.connect("127.0.0.1", HttpServer.port);
    }
}

HttpClientInboundHandler

package cn.enjoyedu.nettyhttp.client;

import io.netty.buffer.ByteBuf;
import io.netty.buffer.Unpooled;
import io.netty.channel.ChannelHandlerContext;
import io.netty.channel.ChannelInboundHandlerAdapter;
import io.netty.handler.codec.http.*;
import io.netty.util.CharsetUtil;

import java.net.URI;

/**
 * @author Mark老师   享学课堂 https://enjoy.ke.qq.com
 * 往期课程和VIP课程咨询 依娜老师  QQ:2133576719
 * 类说明:
 */
public class HttpClientInboundHandler extends ChannelInboundHandlerAdapter {

    public void channelRead(ChannelHandlerContext ctx, Object msg) throws Exception {
        FullHttpResponse httpResponse = (FullHttpResponse)msg;
        System.out.println(httpResponse.status());
        System.out.println(httpResponse.headers());
        ByteBuf buf = httpResponse.content();
        System.out.println(buf.toString(CharsetUtil.UTF_8));
        httpResponse.release();
    }

    @Override
    public void channelActive(ChannelHandlerContext ctx) throws Exception {
        URI uri = new URI("/test");
        String msg = "Hello";
        DefaultFullHttpRequest request =
                new DefaultFullHttpRequest(HttpVersion.HTTP_1_1,
                        HttpMethod.GET,
                        uri.toASCIIString(),
                        Unpooled.wrappedBuffer(msg.getBytes("UTF-8")));

        // 构建http请求
        request.headers().set(HttpHeaderNames.HOST, HttpClient.HOST);
        request.headers().set(HttpHeaderNames.CONNECTION,
                HttpHeaderValues.KEEP_ALIVE);
        request.headers().set(HttpHeaderNames.CONTENT_LENGTH,
                request.content().readableBytes());
        // 发送http请求
        ctx.writeAndFlush(request);
    }
}

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
使用python中的pymsql完成如下:表结构与数据创建 1. 建立 `users` 表和 `orders` 表。 `users` 表有用户ID、用户名、年龄字段,(id,name,age) `orders` 表有订单ID、订单日期、订单金额,用户id字段。(id,order_date,amount,user_id) 2 两表的id作为主键,`orders` 表用户id为users的外键 3 插入数据 `users` (1, '张三', 18), (2, '李四', 20), (3, '王五', 22), (4, '赵六', 25), (5, '钱七', 28); `orders` (1, '2021-09-01', 500, 1), (2, '2021-09-02', 1000, 2), (3, '2021-09-03', 600, 3), (4, '2021-09-04', 800, 4), (5, '2021-09-05', 1500, 5), (6, '2021-09-06', 1200, 3), (7, '2021-09-07', 2000, 1), (8, '2021-09-08', 300, 2), (9, '2021-09-09', 700, 5), (10, '2021-09-10', 900, 4); 查询语句 1. 查询订单总金额 2. 查询所有用户的平均年龄,并将结果四舍五入保留两位小数。 3. 查询订单总数最多的用户的姓名和订单总数。 4. 查询所有不重复的年龄。 5. 查询订单日期在2021年9月1日至9月4日之间的订单总金额。 6. 查询年龄不大于25岁的用户的订单数量,并按照降序排序。 7. 查询订单总金额排名前3的用户的姓名和订单总金额。 8. 查询订单总金额最大的用户的姓名和订单总金额。 9. 查询订单总金额最小的用户的姓名和订单总金额。 10. 查询所有名字中含有“李”的用户,按照名字升序排序。 11. 查询所有年龄大于20岁的用户,按照年龄降序排序,并只显示前5条记录。 12. 查询每个用户的订单数量和订单总金额,并按照总金额降序排序。
最新发布
06-03
这里是使用 Python 的 PyMySQL 完成上述任务的代码: ```python import pymysql # 连接数据库 conn = pymysql.connect(host='localhost', port=3306, user='root', password='123456', database='test') # 创建游标 cur = conn.cursor() # 创建 users 表 cur.execute(''' CREATE TABLE users ( id INT PRIMARY KEY, name VARCHAR(20), age INT ) ''') # 创建 orders 表 cur.execute(''' CREATE TABLE orders ( id INT PRIMARY KEY, order_date DATE, amount INT, user_id INT, FOREIGN KEY (user_id) REFERENCES users(id) ) ''') # 插入数据 users_data = [ (1, '张三', 18), (2, '李四', 20), (3, '王五', 22), (4, '赵六', 25), (5, '钱七', 28) ] orders_data = [ (1, '2021-09-01', 500, 1), (2, '2021-09-02', 1000, 2), (3, '2021-09-03', 600, 3), (4, '2021-09-04', 800, 4), (5, '2021-09-05', 1500, 5), (6, '2021-09-06', 1200, 3), (7, '2021-09-07', 2000, 1), (8, '2021-09-08', 300, 2), (9, '2021-09-09', 700, 5), (10, '2021-09-10', 900, 4) ] for user in users_data: cur.execute('INSERT INTO users VALUES (%s, %s, %s)', user) for order in orders_data: cur.execute('INSERT INTO orders VALUES (%s, %s, %s, %s)', order) # 提交事务 conn.commit() # 查询语句 # 1. 查询订单总金额 cur.execute('SELECT SUM(amount) FROM orders') print(cur.fetchone()[0]) # 2. 查询所有用户的平均年龄,并将结果四舍五入保留两位小数。 cur.execute('SELECT ROUND(AVG(age), 2) FROM users') print(cur.fetchone()[0]) # 3. 查询订单总数最多的用户的姓名和订单总数。 cur.execute(''' SELECT users.name, COUNT(*) AS total_orders FROM users JOIN orders ON users.id = orders.user_id GROUP BY users.id ORDER BY total_orders DESC LIMIT 1 ''') print(cur.fetchone()) # 4. 查询所有不重复的年龄。 cur.execute('SELECT DISTINCT age FROM users') print([row[0] for row in cur.fetchall()]) # 5. 查询订单日期在2021年9月1日至9月4日之间的订单总金额。 cur.execute('SELECT SUM(amount) FROM orders WHERE order_date BETWEEN "2021-09-01" AND "2021-09-04"') print(cur.fetchone()[0]) # 6. 查询年龄不大于25岁的用户的订单数量,并按照降序排序。 cur.execute(''' SELECT users.name, COUNT(*) AS total_orders FROM users JOIN orders ON users.id = orders.user_id WHERE age <= 25 GROUP BY users.id ORDER BY total_orders DESC ''') print(cur.fetchall()) # 7. 查询订单总金额排名前3的用户的姓名和订单总金额。 cur.execute(''' SELECT users.name, SUM(amount) AS total_amount FROM users JOIN orders ON users.id = orders.user_id GROUP BY users.id ORDER BY total_amount DESC LIMIT 3 ''') print(cur.fetchall()) # 8. 查询订单总金额最大的用户的姓名和订单总金额。 cur.execute(''' SELECT users.name, SUM(amount) AS total_amount FROM users JOIN orders ON users.id = orders.user_id GROUP BY users.id ORDER BY total_amount DESC LIMIT 1 ''') print(cur.fetchone()) # 9. 查询订单总金额最小的用户的姓名和订单总金额。 cur.execute(''' SELECT users.name, SUM(amount) AS total_amount FROM users JOIN orders ON users.id = orders.user_id GROUP BY users.id ORDER BY total_amount ASC LIMIT 1 ''') print(cur.fetchone()) # 10. 查询所有名字中含有“李”的用户,按照名字升序排序。 cur.execute('SELECT * FROM users WHERE name LIKE "%李%" ORDER BY name ASC') print(cur.fetchall()) # 11. 查询所有年龄大于20岁的用户,按照年龄降序排序,并只显示前5条记录。 cur.execute('SELECT * FROM users WHERE age > 20 ORDER BY age DESC LIMIT 5') print(cur.fetchall()) # 12. 查询每个用户的订单数量和订单总金额,并按照总金额降序排序。 cur.execute(''' SELECT users.name, COUNT(*) AS total_orders, SUM(amount) AS total_amount FROM users JOIN orders ON users.id = orders.user_id GROUP BY users.id ORDER BY total_amount DESC ''') print(cur.fetchall()) # 关闭游标和连接 cur.close() conn.close() ``` 注意:在运行代码之前,需要先安装 PyMySQL 模块,可以使用以下命令进行安装: ``` pip install pymysql ```
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值