MyBatis练习：统计每日比赛胜负场次

最新推荐文章于 2021-02-19 05:19:02 发布

chenzai1946

最新推荐文章于 2021-02-19 05:19:02 发布

阅读量209

点赞数

文章标签： java 数据库操作系统

原文链接：https://my.oschina.net/Tsybius2014/blog/638464

版权

我的电脑操作系统版本为Win7旗舰版（ServicePack1），Oracle版本为Oracle11g

程序使用的jar包有：mybatis-3.2.2.jar、ojdbc14-10.2.0.2.0.jar

本例中使用的配置文件mybatis-config.xml，可以参见我的另一篇Blog《一个简单的MyBatis连接Oracle数据库的例子》（http://my.oschina.net/Tsybius2014/blog/626206）

现在Oracle数据库中建立一个新表GAME_RECORD并插入一些数据，SQL如下：

CREATE TABLE GAME_RECORD
(
    ID NUMBER(12, 0) PRIMARY KEY,
    GAME_DATE NUMBER(10, 0),
    PLAYER_NAME VARCHAR(10),
    GAME_RESULT VARCHAR(10)
);

INSERT INTO GAME_RECORD (ID, GAME_DATE, PLAYER_NAME, GAME_RESULT) VALUES (1, 20160301, 'Tsybius', 'WIN');
INSERT INTO GAME_RECORD (ID, GAME_DATE, PLAYER_NAME, GAME_RESULT) VALUES (2, 20160301, 'Tsybius', 'LOSE');
INSERT INTO GAME_RECORD (ID, GAME_DATE, PLAYER_NAME, GAME_RESULT) VALUES (3, 20160301, 'Tsybius', 'LOSE');
INSERT INTO GAME_RECORD (ID, GAME_DATE, PLAYER_NAME, GAME_RESULT) VALUES (4, 20160302, 'Tsybius', 'WIN');
INSERT INTO GAME_RECORD (ID, GAME_DATE, PLAYER_NAME, GAME_RESULT) VALUES (5, 20160302, 'Tsybius', 'WIN');
INSERT INTO GAME_RECORD (ID, GAME_DATE, PLAYER_NAME, GAME_RESULT) VALUES (6, 20160302, 'Tsybius', 'LOSE');
INSERT INTO GAME_RECORD (ID, GAME_DATE, PLAYER_NAME, GAME_RESULT) VALUES (7, 20160302, 'Tsybius', 'DRAW');
INSERT INTO GAME_RECORD (ID, GAME_DATE, PLAYER_NAME, GAME_RESULT) VALUES (8, 20160302, 'Tsybius', 'WIN');
INSERT INTO GAME_RECORD (ID, GAME_DATE, PLAYER_NAME, GAME_RESULT) VALUES (9, 20160302, 'Tsybius', 'LOSE');
INSERT INTO GAME_RECORD (ID, GAME_DATE, PLAYER_NAME, GAME_RESULT) VALUES (10, 20160303, 'Tsybius', 'WIN');
INSERT INTO GAME_RECORD (ID, GAME_DATE, PLAYER_NAME, GAME_RESULT) VALUES (11, 20160303, 'Tsybius', 'DRAW');
INSERT INTO GAME_RECORD (ID, GAME_DATE, PLAYER_NAME, GAME_RESULT) VALUES (12, 20160303, 'Quintus', 'WIN');
INSERT INTO GAME_RECORD (ID, GAME_DATE, PLAYER_NAME, GAME_RESULT) VALUES (13, 20160303, 'Quintus', 'WIN');

COMMIT;
/

数据表建立后，从PL/SQL上查询到的结果集如下：

现在我们要做的，是统计出玩家Tsybius每日的胜（WIN）、负（LOSE）和平局（DRAW）场次

在PL/SQL中，我先写了一个SQL：

SELECT DT.GAME_DATE, NVL(WN.WIN_CNT, 0) AS WIN, NVL(LS.LOSE_CNT, 0) AS LOSE, NVL(DRW.DRAW_CNT, 0) AS DRAW
FROM   ((SELECT GAME_DATE
         FROM   GAME_RECORD
         WHERE  PLAYER_NAME = 'Tsybius'
         GROUP  BY GAME_DATE) DT 
         LEFT JOIN (SELECT GAME_DATE, COUNT(*) AS WIN_CNT
                    FROM   GAME_RECORD
                    WHERE  GAME_RESULT = 'WIN' AND PLAYER_NAME = 'Tsybius'
                    GROUP  BY GAME_DATE) WN ON DT.GAME_DATE = WN.GAME_DATE 
         LEFT JOIN (SELECT GAME_DATE, COUNT(*) AS LOSE_CNT
                    FROM   GAME_RECORD
                    WHERE  GAME_RESULT = 'LOSE' AND PLAYER_NAME = 'Tsybius'
                    GROUP  BY GAME_DATE) LS ON DT.GAME_DATE = LS.GAME_DATE 
         LEFT JOIN (SELECT GAME_DATE, COUNT(*) AS DRAW_CNT
                    FROM   GAME_RECORD
                    WHERE  GAME_RESULT = 'DRAW' AND PLAYER_NAME = 'Tsybius'
                    GROUP  BY GAME_DATE) DRW ON DT.GAME_DATE = DRW.GAME_DATE)
ORDER  BY DT.GAME_DATE

这个SQL的查询结果如下：

现在将这个SQL语句移植到MyBatis中，main函数调用代码如下：

import java.io.InputStream;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

/**
 * MyBatis使用测试
 * @author Tsybius2014
 * @date 2016年3月16日
 * @time 下午1:55:28
 * @remark
 *
 */
public class MyBatisTest {
    public static void main(String[] args) {
        
        try {
            String resource = "mybatis-config.xml";
            InputStream inputStream = Resources.getResourceAsStream(resource);
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
            SqlSession session = sqlSessionFactory.openSession();
            try {
                GameRecordMapper mapper = session.getMapper(GameRecordMapper.class);
                
                //获取每日胜、负、平局场次
                List<Map<String, Object>> gameLogList = mapper.getGameLog("Tsybius");
                Iterator<Map<String, Object>> iter = gameLogList.iterator();
                while (iter.hasNext()) {
                    Map<String, Object> gameLog = iter.next();
                    System.out.println(gameLog);
                }
                
            } finally {
                session.close();
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }
}

GameRecordMapper.java代码如下：

import java.util.List;
import java.util.Map;

public interface GameRecordMapper {
    /**
     * 获取指定选手每日胜、负、平局的场次统计信息
     * @param playerName 选手名
     * @return
     */
    List<Map<String, Object>> getGameLog(String playerName);
}

GameRecordMapper.xml代码如下：

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="GameRecordMapper">

    <resultMap id="GameLogMap" type="java.util.Map" >
        <result column="GAME_DATE" property="gameDate" jdbcType="DECIMAL" />
        <result column="WIN" property="win" jdbcType="DECIMAL" />
        <result column="LOSE" property="lose" jdbcType="DECIMAL" />
        <result column="DRAW" property="draw" jdbcType="DECIMAL" />
    </resultMap>
    
    <sql id="Calc_Win_Times" >
        SELECT GAME_DATE, COUNT(*) AS WIN_CNT
        FROM   GAME_RECORD
        WHERE  GAME_RESULT = 'WIN' AND PLAYER_NAME = #{playerName, jdbcType = VARCHAR}
        GROUP  BY GAME_DATE
    </sql>
    <sql id="Calc_Lose_Times" >
        SELECT GAME_DATE, COUNT(*) AS LOSE_CNT
        FROM   GAME_RECORD
        WHERE  GAME_RESULT = 'LOSE' AND PLAYER_NAME = #{playerName, jdbcType = VARCHAR}
        GROUP  BY GAME_DATE
    </sql>
    <sql id="Calc_Draw_Times" >
        SELECT GAME_DATE, COUNT(*) AS DRAW_CNT
        FROM   GAME_RECORD
        WHERE  GAME_RESULT = 'DRAW' AND PLAYER_NAME = #{playerName, jdbcType = VARCHAR}
        GROUP  BY GAME_DATE
    </sql>
    
    <select id="getGameLog" parameterType="java.lang.String" resultMap="GameLogMap" >
        SELECT DT.GAME_DATE, NVL(WN.WIN_CNT, 0) AS WIN, NVL(LS.LOSE_CNT, 0) AS LOSE, NVL(DRW.DRAW_CNT, 0) AS DRAW
        FROM   ((SELECT GAME_DATE
                 FROM   GAME_RECORD
                 WHERE  PLAYER_NAME = #{playerName, jdbcType = VARCHAR}
                 GROUP  BY GAME_DATE) DT 
                 LEFT JOIN (<include refid="Calc_Win_Times" />) WN ON DT.GAME_DATE = WN.GAME_DATE 
                 LEFT JOIN (<include refid="Calc_Lose_Times" />) LS ON DT.GAME_DATE = LS.GAME_DATE 
                 LEFT JOIN (<include refid="Calc_Draw_Times" />) DRW ON DT.GAME_DATE = DRW.GAME_DATE)
        ORDER  BY DT.GAME_DATE
    </select>

</mapper>

程序运行时，输出到控制台中的代码如下：

{gameDate=20160301, lose=2, draw=0, win=1}
{gameDate=20160302, lose=2, draw=1, win=3}
{gameDate=20160303, lose=0, draw=1, win=1}

END

转载于:https://my.oschina.net/Tsybius2014/blog/638464

chenzai1946

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MyBatis练习：统计每日比赛胜负场次

我的电脑操作系统版本为Win7旗舰版（ServicePack1），Oracle版本为Oracle11g 程序使用的jar包有：mybatis-3.2.2.jar、ojdbc14-10.2.0.2.0.jar 本例中使用的配置文件mybatis-config.xml，可以参见我的另一篇Blo...
复制链接

扫一扫