使用DBWritable完成同mysql交互

1.准备数据库
  create database big4 ;
  use big4 ;
  create table words(id int primary key auto_increment , name varchar(20) , txt varchar(255));
  
  insert into words(name,txt) values('tomas','hello world tom');
  insert into words(txt) values('hello tom world');
  insert into words(txt) values('world hello tom');
  insert into words(txt) values('world tom hello');
 
 2.编写hadoop MyDBWritable.
  package com.it18zhang.hdfs.mr.mysql;

  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;

  /**
   * MyDBWritable
   */
  public class MyDBWritable implements DBWritable,Writable {
   private int id ;
   private String name ;
   private String txt ;

   public int getId() {
    return id;
   }

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

   public String getName() {
    return name;
   }

   public void setName(String name) {
    this.name = name;
   }

   public String getTxt() {
    return txt;
   }

   public void setTxt(String txt) {
    this.txt = txt;
   }

   public void write(DataOutput out) throws IOException {
    out.writeInt(id);
    out.writeUTF(name);
    out.writeUTF(txt);
   }

   public void readFields(DataInput in) throws IOException {
    id = in.readInt();
    name = in.readUTF();
    txt = in.readUTF();
   }

   /**
    * 写入db
    */
   public void write(PreparedStatement ppst) throws SQLException {
    ppst.setInt(1,id);
    ppst.setString(2,name);
    ppst.setString(3,txt);
   }

   /**
    * 从db读取
    */
   public void readFields(ResultSet rs) throws SQLException {
    id = rs.getInt(1);
    name = rs.getString(2);
    txt = rs.getString(3);
   }
  }

 3.WcMapper
  public class WCMapper extends Mapper<LongWritable,MyDBWritable,Text,IntWritable> {

   protected void map(LongWritable key, MyDBWritable value, Context context) throws IOException, InterruptedException {
    System.out.println(key);
    String line = value.getTxt();
    System.out.println(value.getId() + "," + value.getName());
    String[] arr = line.split(" ");
    for(String s : arr){
     context.write(new Text(s),new IntWritable(1));
    }
   }
  }

 4.WCReducer
  protected void reduce(Text key, Iterable<IntWritable> values, Context context) throws IOException, InterruptedException {
   int count = 0 ;
   for(IntWritable w : values){
    count = count + w.get() ;
   }
   context.write(key,new IntWritable(count));
  }

 5.WCApp
  public static void main(String[] args) throws Exception {
   Configuration conf = new Configuration();
   Job job = Job.getInstance(conf);

   //设置job的各种属性
   job.setJobName("MySQLApp");                        //作业名称
   job.setJarByClass(WCApp.class);                 //搜索类

   //配置数据库信息
   String driverclass = "com.mysql.jdbc.Driver" ;
   String url = "jdbc:mysql://localhost:3306/big4" ;
   String username= "root" ;
   String password = "root" ;
   //设置数据库配置
   DBConfiguration.configureDB(job.getConfiguration(),driverclass,url,username,password);
   //设置数据输入内容
   DBInputFormat.setInput(job,MyDBWritable.class,"select id,name,txt from words","select count(*) from words");

   //设置输出路径
   FileOutputFormat.setOutputPath(job,new Path("d:/mr/sql/out"));

   //设置分区类
   job.setMapperClass(WCMapper.class);             //mapper类
   job.setReducerClass(WCReducer.class);           //reducer类

   job.setNumReduceTasks(3);                       //reduce个数

   job.setMapOutputKeyClass(Text.class);           //
   job.setMapOutputValueClass(IntWritable.class);  //

   job.setOutputKeyClass(Text.class);
   job.setOutputValueClass(IntWritable.class);     //

   job.waitForCompletion(true);
  }

 6.pom.xml增加mysql驱动
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.17</version>
        </dependency>
 

 7.将mr的统计结果写入mysql数据库
  a)准备表
   create table stats(word varchar(50),c int);
  b)设置App的DBOutputFormat类
   com.WCApp