MapReduce读取数据库操作

• MapReduce
• • MapReduce 实体类
  • MapReduce DBMapper类（读数据库操作）
  • MapReduce DBReduce类（读数据库操作）
  • MapReduce DBDriver类（读数据库操作驱动类）
  • MapReduce 数据库表

读取数据库操作，首先创建实体类。 实体类需要继承 Writable，DBWritable两个类。 重写Writable的 write（序列化方法），readFields（反序列化方法）方法。重写 DBWritable的 write（序列化方法），readFields（反序列化方法）。两个继承类的重写的方法名一样 但是参数不同。
下面是GoodsBean实体类：

import org.apache.hadoop.io.Writable;
import org.apache.hadoop.mapreduce.lib.db.DBWritable;

import java.io.DataInput;
import java.io.DataOutput;
import java.io.IOException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class GoodsBean implements Writable, DBWritable {


    private long goodsId;//商品ID
    private String goodsSn;//商品编号
    private String goodsName;//商品名称
    private double marketPrice;//市场价
    private double shopPrice;//门店价
    private long saleNum;//总销售量


    public long getGoodsId() {
        return goodsId;
    }

    public void setGoodsId(long goodsId) {
        this.goodsId = goodsId;
    }

    public String getGoodsSn() {
        return goodsSn;
    }

    public void setGoodsSn(String goodsSn) {
        this.goodsSn = goodsSn;
    }

    public String getGoodsName() {
        return goodsName;
    }

    public void setGoodsName(String goodsName) {
        this.goodsName = goodsName;
    }

    public double getMarketPrice() {
        return marketPrice;
    }

    public void setMarketPrice(double marketPrice) {
        this.marketPrice = marketPrice;
    }

    public double getShopPrice() {
        return shopPrice;
    }

    public void setShopPrice(double shopPrice) {
        this.shopPrice = shopPrice;
    }

    public long getSaleNum() {
        return saleNum;
    }

    public void setSaleNum(long saleNum) {
        this.saleNum = saleNum;
    }

    
    public GoodsBean() {
    }

    
    public GoodsBean(long goodsId, String goodsSn, String goodsName, double marketPrice, double shopPrice, long saleNum) {
        this.goodsId = goodsId;
        this.goodsSn = goodsSn;
        this.goodsName = goodsName;
        this.marketPrice = marketPrice;
        this.shopPrice = shopPrice;
        this.saleNum = saleNum;
    }

    
    public void set(long goodsId, String goodsSn, String goodsName, double marketPrice, double shopPrice, long saleNum) {
        this.goodsId = goodsId;
        this.goodsSn = goodsSn;
        this.goodsName = goodsName;
        this.marketPrice = marketPrice;
        this.shopPrice = shopPrice;
        this.saleNum = saleNum;
    }

    //此处省略setter getter方法

    
    @Override
    public void write(DataOutput out) throws IOException {
        out.writeLong(goodsId);
        out.writeUTF(goodsSn);
        out.writeUTF(goodsName);
        out.writeDouble(marketPrice);
        out.writeDouble(shopPrice);
        out.writeLong(saleNum);
    }

    
    @Override
    public void readFields(DataInput in) throws IOException {
        this.goodsId = in.readLong();
        this.goodsSn = in.readUTF();
        this.goodsName = in.readUTF();
        this.marketPrice = in.readDouble();
        this.shopPrice = in.readDouble();
        this.saleNum = in.readLong();
    }

    
    @Override
    public void write(PreparedStatement ps) throws SQLException {
        ps.setLong(1, goodsId);
        ps.setString(2, goodsSn);
        ps.setString(3, goodsName);
        ps.setDouble(4, marketPrice);
        ps.setDouble(5, shopPrice);
        ps.setLong(6, saleNum);
    }

    
    @Override
    public void readFields(ResultSet rs) throws SQLException {
        this.goodsId = rs.getLong(1);
        this.goodsSn = rs.getString(2);
        this.goodsName = rs.getString(3);
        this.marketPrice = rs.getDouble(4);
        this.shopPrice = rs.getDouble(5);
        this.saleNum = rs.getLong(6);
    }

    @Override
    public String toString() {
        return goodsId + "t" + goodsSn + "t" + goodsName + "t" + marketPrice + "t" + shopPrice + "t" + saleNum;
    }

}

此处为读取数据库操作 需要继承Mapper重写map方法
KeyIn:一般为行数坐标 LongWritable
ValueIn ：为读取数据库的行数据 GoodBean
KeyOut：输出的key LongWritable
ValueOut：输出的值，将数据库写入文档当中 所以用字符串格式 Text

import org.apache.hadoop.io.LongWritable;
import org.apache.hadoop.io.Text;
import org.apache.hadoop.mapreduce.Mapper;

import java.io.IOException;

public class DBMapper extends Mapper {

    Text outValue = new Text();
    @Override
    protected void map(LongWritable key, GoodsBean value, Mapper.Context context) throws IOException, InterruptedException {
        outValue.set(value.toString());
        //此处的输出key‘和输入key一样 所以直接使用输入的key
        context.write(key,outValue);

    }
}

因为需求简单 没有进行Reduce合并的需求 可以不写此类

import org.apache.hadoop.conf.Configuration;
import org.apache.hadoop.fs.Path;
import org.apache.hadoop.io.LongWritable;
import org.apache.hadoop.io.Text;
import org.apache.hadoop.mapreduce.Job;
import org.apache.hadoop.mapreduce.lib.db.DBConfiguration;
import org.apache.hadoop.mapreduce.lib.db.DBInputFormat;
import org.apache.hadoop.mapreduce.lib.output.FileOutputFormat;

import java.io.IOException;

public class DBDriver {

    public static void main(String[] args) throws IOException, InterruptedException, ClassNotFoundException {

        Configuration conf = new Configuration();
        //数据库配置
        DBConfiguration.configureDB(conf, "com.mysql.cj.jdbc.Driver", "jdbc:mysql://localhost/shop?serverTimezone=GMT%2B8&Unicode=true&characterEncoding=utf-8&useSSL=false", "root", "123456");
        //创建作业的job类
        Job job = Job.getInstance(conf, DBDriver.class.getSimpleName());
		//驱动类
        job.setJarByClass(DBDriver.class);
        //mapper类
        job.setMapperClass(DBMapper.class);
		//输出key和输出value的类型
        job.setOutputKeyClass(LongWritable.class);
        job.setOutputValueClass(Text.class);
		//输出的文件路径
        FileOutputFormat.setOutputPath(job, new Path("E://mysqlOut"));
		
		//因为没有写Reduce类 所以设置ReduceTask数为0
        job.setNumReduceTasks(0);
		//sql语句
        job.setInputFormatClass(DBInputFormat.class);
        DBInputFormat.setInput(job, GoodsBean.class, "select goodsId,goodsSn,goodsName,marketPrice,shopPrice,saleNum from itheima_goods", "select count(*) from itheima_goods");
        boolean b = job.waitForCompletion(true);
		//退出
        System.exit(b ? 0 : 1);

    }
}

下一篇: 写入数据库操作链接.

CREATE TABLE `goods` (
  `goodsId` bigint NOT NULL AUTO_INCREMENT COMMENT '商品id',
  `goodsSn` varchar(20) NOT NULL COMMENT '商品编号',
  `goodsName` varchar(200) NOT NULL COMMENT '商品名称',
  `marketPrice` decimal(11,2) NOT NULL DEFAULT '0.00' COMMENT '市场价',
  `shopPrice` decimal(11,2) NOT NULL DEFAULT '0.00' COMMENT '门店价',
  `saleNum` int NOT NULL DEFAULT '0' COMMENT '总销售量',
  PRIMARY KEY (`goodsId`)
) ENGINE=InnoDB AUTO_INCREMENT=115909 DEFAULT CHARSET=utf8mb3;

附上100条数据

INSERT INTO `goods` VALUES ('100101', '155083444927602', '四川果冻橙6个约180g/个', '45.00', '38.00', '0');
INSERT INTO `goods` VALUES ('100102', '155083493976803', '鲜丰水果秭归脐橙中华红橙9斤家庭装单果130g—220g4500g', '70.00', '59.90', '0');
INSERT INTO `goods` VALUES ('100103', '155083608286049', '红颜奶油草莓约重500g/15-20颗新鲜水果', '60.00', '49.90', '0');
INSERT INTO `goods` VALUES ('100104', '155083629928386', '智利进口车厘子J级1磅装果径约26-28mm新鲜水果', '100.00', '79.00', '0');
INSERT INTO `goods` VALUES ('100105', '155083696154773', 'Zespri佳沛新西兰阳光金奇异果6个装经典36-39号果单果重约80-100g新鲜水果', '700.00', '59.90', '0');
INSERT INTO `goods` VALUES ('100106', '155084645314975', '花果山云南甜玉米水果玉米约2.5kg6-9根烧烤食材新鲜蔬菜', '35.00', '26.80', '0');
INSERT INTO `goods` VALUES ('100107', '155084660300312', '福建六鳌红薯蜜薯地瓜2.5kg红蜜薯单果重约150g-500g新鲜蔬菜', '30.00', '26.90', '0');
INSERT INTO `goods` VALUES ('100108', '155084691120340', '花果山17.5°橙3kg装铂金果新鲜水果礼盒', '50.00', '45.90', '0');
INSERT INTO `goods` VALUES ('100109', '155084738764170', '花果山甜鸭梨6-8个净重约1.8kg新鲜水果', '35.00', '24.90', '0');
INSERT INTO `goods` VALUES ('100110', '155084761584559', '花果山烟台红富士苹果12个净重2.1kg以上单果160-190g一二级混装自营水果', '41.00', '34.90', '0');
INSERT INTO `goods` VALUES ('100111', '155084782094837', '黄冠梨4个单果约250g-300g新鲜水果', '35.00', '299.00', '0');
INSERT INTO `goods` VALUES ('100112', '155084798412816', '花果山球生菜约400g火锅食材新鲜蔬菜', '13.00', '10.80', '0');
INSERT INTO `goods` VALUES ('100113', '155084813931944', '上海青小油菜小青菜约400g火锅食材新鲜蔬菜', '10.00', '7.90', '0');
INSERT INTO `goods` VALUES ('100114', '155084830858516', '花果山香菇蘑菇约400g火锅食材新鲜蔬菜', '20.00', '15.80', '0');
INSERT INTO `goods` VALUES ('100115', '155084851974657', '花果山农家新鲜大蒜2500g装蒜头', '22.00', '20.50', '0');
INSERT INTO `goods` VALUES ('100116', '155084880330968', '花果山杭椒约300g自营蔬菜', '10.00', '8.50', '0');
INSERT INTO `goods` VALUES ('100117', '155084916882945', '广西荔浦砂糖桔砂糖橘甜橘子2.5kg礼盒装新鲜水果', '58.00', '49.90', '0');
INSERT INTO `goods` VALUES ('100118', '155084929348686', '红旗坡新疆阿克苏苹果果径80mm-85mm约5kg新鲜水果', '120.00', '99.00', '0');
INSERT INTO `goods` VALUES ('100119', '155084948775196', '进口牛油果6个装单果重约130-180g新鲜水果', '50.00', '39.90', '0');
INSERT INTO `goods` VALUES ('100120', '155084971919785', '九家原越南进口大玉芒4斤单果约180-230g', '25.00', '19.90', '0');
INSERT INTO `goods` VALUES ('100121', '155085021605526', '三只松鼠开口松子休闲零食坚果炒货东北手剥红松子100g', '45.00', '39.90', '0');
INSERT INTO `goods` VALUES ('100122', '155085043559648', '三只松鼠草莓干蜜饯果干果脯办公室休闲零食水果干106g/袋', '30.00', '25.90', '0');
INSERT INTO `goods` VALUES ('100123', '155085057770371', '三只松鼠坚果炒货零食特产每日坚果开心果100g/袋', '45.00', '39.90', '0');
INSERT INTO `goods` VALUES ('100124', '155085070063201', '满199减120三只松鼠黄桃干106g休闲零食特产果脯蜜饯水果干桃肉红色', '25.00', '19.90', '0');
INSERT INTO `goods` VALUES ('100125', '155085092481916', '三只松鼠无核白葡萄干280gx2袋零食蜜饯果干新疆特产提子干', '50.00', '42.70', '0');
INSERT INTO `goods` VALUES ('100126', '155085103438739', '三只松鼠休闲零食特产混合什锦果蔬菜干蔬果干田园果蔬脆100g/袋', '25.00', '21.90', '0');
INSERT INTO `goods` VALUES ('100127', '155085120796574', '三只松鼠黄秋葵脆秋葵干健康营养休闲零食清新即食嘎嘣清脆蔬菜干40g/袋', '20.00', '16.90', '0');
INSERT INTO `goods` VALUES ('100128', '155085134595386', '三只松鼠凤梨干菠萝干蜜饯果干零食果脯106g/袋', '23.00', '19.80', '0');
INSERT INTO `goods` VALUES ('100129', '155085152523279', '三只松鼠坚果特产休闲零食核桃肉琥珀核桃仁165g/袋', '33.00', '29.90', '0');
INSERT INTO `goods` VALUES ('100130', '155085164041384', '三只松鼠肩扛大零食巨型坚果零食大礼包3195g30包抖音一箱零食猪饲料送女友年货礼盒混合口味', '200.00', '189.00', '0');
INSERT INTO `goods` VALUES ('100131', '155085192227531', '三只松鼠坚果炒货零食特产即食板栗开口甘栗120g/袋', '24.00', '16.90', '0');
INSERT INTO `goods` VALUES ('100132', '155085237245488', '同仁堂（TRT）【同仁堂旗舰店】同仁堂红糖姜茶10g*12袋*1盒速溶姜茶大姨妈可饮用', '100.00', '89.90', '0');
INSERT INTO `goods` VALUES ('100133', '155085249050414', '同仁堂（TRT）阿胶糕225g礼盒装即食阿胶糕原味阿胶固元膏北京同仁堂', '130.00', '106.00', '0');
INSERT INTO `goods` VALUES ('100134', '155085262909301', '同仁堂TRT玫瑰花玫瑰花茶花草茶80g/瓶', '40.00', '35.00', '0');
INSERT INTO `goods` VALUES ('100135', '155085275777627', '同仁堂牛蒡茶170g', '50.00', '45.00', '0');
INSERT INTO `goods` VALUES ('100136', '155085295660683', '同仁堂(TRT)玫瑰花茶45g', '40.00', '35.00', '0');
INSERT INTO `goods` VALUES ('100137', '155085311727405', '北京同仁堂中华老字号黑果枸杞袋泡茶（代用茶）2g/袋*30', '170.00', '159.00', '0');
INSERT INTO `goods` VALUES ('100138', '155085316644196', '同仁堂（TRT）黑苦荞茶273g(7g*39袋)', '68.00', '57.00', '0');
INSERT INTO `goods` VALUES ('100139', '155085327982623', '同仁堂陈皮茶120g', '30.00', '27.90', '0');
INSERT INTO `goods` VALUES ('100140', '155085358959309', '芙丽芳丝freeplus洗面奶女深层清洁泡沫氨基酸温和洁面乳男专柜特价净润洗面霜100g敏感肌适用', '140.00', '120.00', '0');
INSERT INTO `goods` VALUES ('100141', '155085380178651', '资生堂珊珂（SENKA）绵润泡沫洁面乳120g（日本进口洗面奶深层清洁清爽滋养男女适用）', '56.00', '45.00', '0');
INSERT INTO `goods` VALUES ('100142', '155085391329574', '资生堂水之密语（AQUAIR）净澄水活(倍润型)洗护套装(洗发水600ml*2+护发素600ml*2)', '145.00', '139.00', '0');
INSERT INTO `goods` VALUES ('100143', '155085403436449', '夏依女性专用洗液237ml（敏感肌肤）*2支装护理液私处私密护理美国原装进口', '100.00', '89.00', '0');
INSERT INTO `goods` VALUES ('100144', '155085421243310', '妇炎洁妇科洗液女性私处护理液伊斯蜜粉嫩缩阴紧致私密护理凝胶润滑油剂洁阴洗阴内道冲洗器洗阴液私密洗液200ml+洗液380ml送冲洗器2个', '89.00', '75.00', '0');
INSERT INTO `goods` VALUES ('100145', '155085444960210', '蜜丝佛陀（MaxFactor）无暇持妆隔离霜30mlSPF20（彩妆妆前乳防晒裸妆控油持久）', '110.00', '99.00', '0');
INSERT INTO `goods` VALUES ('100146', '155085452741648', '谜尚（MISSHA）魅力润颜修容霜SPF42/PA+++[21号]50ml（气垫bb霜粉底遮瑕保湿防晒）（新老包装随机发货）', '120.00', '99.00', '0');
INSERT INTO `goods` VALUES ('100147', '155085467032901', '欧莱雅（L'OREAL）男士水能护肤品洗面奶套装面霜补水保湿滋润肤乳液擦脸油护脸霜保湿强润霜+酷爽水凝露120ml', '120.00', '89.00', '0');
INSERT INTO `goods` VALUES ('100148', '155085509783922', '五粮液52度普五500ML（新老包装随机发货）', '1200.00', '1050.00', '0');
INSERT INTO `goods` VALUES ('100149', '155085531427345', '泸州老窖国窖157352度浓香型白酒500ml（百年品牌泸州老窖荣誉出品）', '1100.00', '969.00', '0');
INSERT INTO `goods` VALUES ('100150', '155085549028442', '剑南春水晶剑52度单瓶装白酒500ml口感浓香型', '1000.00', '898.00', '0');
INSERT INTO `goods` VALUES ('100151', '155085560443863', '长城（GreatWall）红酒特选5年橡木桶解百纳干红葡萄酒整箱装750ml*6瓶', '500.00', '399.00', '0');
INSERT INTO `goods` VALUES ('100152', '155085570890755', '法国进口红酒拉菲（LAFITE）传奇波尔多干红葡萄酒整箱装750ml*6瓶（ASC）', '650.00', '498.00', '0');
INSERT INTO `goods` VALUES ('100153', '155085593740896', '法国原瓶进口红酒凯旋干红葡萄酒礼盒750ml整箱6支装', '305.00', '279.00', '0');
INSERT INTO `goods` VALUES ('100154', '155085607093157', '送2香槟杯尚尼酒庄魔幻云星空酒起泡葡萄酒4支组合整箱火焰酒女士钟爱的配制甜红酒气泡果酒4*750ml', '200.00', '158.00', '0');
INSERT INTO `goods` VALUES ('100155', '155088550521760', '劲牌劲酒中国劲酒35度1.5L', '125.00', '99.00', '0');
INSERT INTO `goods` VALUES ('100156', '155088572544924', '劲牌劲酒中国劲酒35度125ml*12瓶礼盒装', '200.00', '179.00', '0');
INSERT INTO `goods` VALUES ('100157', '155088588416731', 'HE小酒JOY纪念版洋河+习酒+古井贡酒+劲牌+黄金酒大牌畅饮盒', '310.00', '279.00', '0');
INSERT INTO `goods` VALUES ('100158', '155088605646015', '洋河蓝色经典梦之蓝M352度礼盒装500ml*2瓶白酒口感绵柔浓香型', '15000.00', '1098.00', '0');
INSERT INTO `goods` VALUES ('100159', '155088623897765', '郎酒郎牌郎酒酱香型53度整箱装500ml*6瓶', '1300.00', '1128.00', '0');
INSERT INTO `goods` VALUES ('100160', '155088647094285', '优露清（Explution）油烟机清洗剂瓷砖浴室清洁剂厨房油污浴室玻璃清洗液厨卫清洁套装1L', '90.00', '89.70', '0');
INSERT INTO `goods` VALUES ('100161', '155088662493170', '优露清（Explution）顽固污渍干洗剂羽绒服清洗剂衣服去油渍布艺沙发免水洗去污清洁剂1瓶装', '87.50', '93.00', '0');
INSERT INTO `goods` VALUES ('100162', '155088679433830', '优露清（Explution）便携式去污笔去渍笔衣服免水洗去油渍干洗剂应急去污魔法笔随身携带一盒装', '35.00', '26.90', '0');
INSERT INTO `goods` VALUES ('100163', '155088696774534', '优露清【榜单商品】油污清洁剂油烟机清洗剂厨房去油污强力清洁剂瓷砖去油污净500ml', '55.00', '49.90', '0');
INSERT INTO `goods` VALUES ('100164', '155088710371201', '威猛先生（MrMuscle）【领券满65减50】家用厨卫清洁组合套装灶台重油污净油烟', '55.00', '46.90', '0');
INSERT INTO `goods` VALUES ('100165', '155088726129516', '立白茶籽洗洁精双瓶省心装1.45kg*2瓶', '29.00', '23.90', '0');
INSERT INTO `goods` VALUES ('100166', '155088744403257', '立白柠檬去油洗洁精（清新柠檬）1.5kg/瓶', '15.00', '13.50', '0');

下一篇: 写入数据库操作链接.