MR与Mysql交互

package testClass20140311;

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

import org.apache.hadoop.io.IntWritable;
import org.apache.hadoop.io.LongWritable;
import org.apache.hadoop.io.Text;
import org.apache.hadoop.io.Writable;
import org.apache.hadoop.mapred.JobClient;
import org.apache.hadoop.mapred.JobConf;
import org.apache.hadoop.mapred.MapReduceBase;
import org.apache.hadoop.mapred.Mapper;
import org.apache.hadoop.mapred.OutputCollector;
import org.apache.hadoop.mapred.Reducer;
import org.apache.hadoop.mapred.Reporter;
import org.apache.hadoop.mapred.lib.db.DBConfiguration;
import org.apache.hadoop.mapred.lib.db.DBInputFormat;
import org.apache.hadoop.mapred.lib.db.DBOutputFormat;
import org.apache.hadoop.mapred.lib.db.DBWritable;

/**
 * Function: 测试 mr 与 mysql 的数据交互，此测试用例将一个表中的数据复制到另一张表中 实际当中，可能只需要从 mysql 读，或者写到
 * mysql 中。
 * 
 * @author june
 */
public class Mysql2Mr {

	public static class GameinfoRecord implements Writable, DBWritable {
		int id;
		int game;
		String player_id;
		int coin;

		public GameinfoRecord() {

		}

		public void readFields(DataInput in) throws IOException {
			this.id = in.readInt();
			this.game = in.readInt();
			this.player_id = in.readUTF();
			this.coin = in.readInt();
			// this.coin = Text.readString(in);
		}

		@Override
		public String toString() {
			return "GameinfoRecord [id=" + id + ", game=" + game
					+ ", player_id=" + player_id + ", coin=" + coin + "]";
		}

		@Override
		public void write(PreparedStatement stmt) throws SQLException {
			stmt.setInt(1, this.id);
			stmt.setInt(2, this.game);
			stmt.setString(3, this.player_id);
			stmt.setInt(4, this.coin);
		}

		@Override
		public void readFields(ResultSet result) throws SQLException {
			this.id = result.getInt(1);
			this.game = result.getInt(2);
			this.player_id = result.getString(3);
			this.coin = result.getInt(4);
		}

		@Override
		public void write(DataOutput out) throws IOException {
			out.writeInt(this.id);
			out.writeInt(this.game);
			out.writeUTF(this.player_id);
			out.writeInt(this.coin);
			// Text.writeString(out, this.name);
		}
	}

	// 记住此处是静态内部类，要不然你自己实现无参构造器，或者等着抛异常：
	public static class DBInputMapper extends MapReduceBase implements
			Mapper<LongWritable, GameinfoRecord, IntWritable, IntWritable> {
		public void map(LongWritable key, GameinfoRecord value,
				OutputCollector<IntWritable, IntWritable> collector,
				Reporter reporter) throws IOException {
			collector.collect(new IntWritable(value.game), new IntWritable(
					value.coin));
		}
	}

	// Combine处理过程
	public static class Combine extends MapReduceBase implements
			Reducer<IntWritable, IntWritable, IntWritable, IntWritable> {
		@Override
		public void reduce(IntWritable key, Iterator<IntWritable> values,
				OutputCollector<IntWritable, IntWritable> output, Reporter reporter)
				throws IOException {
			int sum = 0;
			while (values.hasNext()) {
				sum += values.next().get();
			}
			output.collect(key, new IntWritable(sum));
		}
	}

	// Reduce处理过程
	public static class Reduce extends MapReduceBase implements
			Reducer<IntWritable, IntWritable, ResultGame, Text> {
		@Override
		public void reduce(IntWritable key, Iterator<IntWritable> values,
				OutputCollector<ResultGame, Text> collector, Reporter reporter)
				throws IOException {
			int sum = 0;
			while (values.hasNext()) {
				sum += values.next().get();
			}
			ResultGame wordcount = new ResultGame();
			wordcount.game = key.get();
			wordcount.sum = sum;
			collector.collect(wordcount, new Text());
		}
	}

	public static class ResultGame implements Writable, DBWritable {
		public int game;
		public int sum;

		@Override
		public void readFields(DataInput in) throws IOException {
			this.game =  in.readInt();
			this.sum = in.readInt();
		}

		@Override
		public void write(DataOutput out) throws IOException {
			out.writeInt(this.game);
			out.writeInt(this.sum);
		}

		@Override
		public void readFields(ResultSet result) throws SQLException {
			this.game = result.getInt(1);
			this.sum = result.getInt(2);
		}

		@Override
		public void write(PreparedStatement stmt) throws SQLException {
			stmt.setInt(1, this.game);
			stmt.setInt(2, this.sum);
		}
	}

	public static void main(String[] args) throws IOException {
		JobConf conf = new JobConf(Mysql2Mr.class);
		//如果没加jar包需要加上这两句
//		Path mysqlPath = new Path("/tmp/mysql-connector-java-5.0.8-bin.jar");
//		DistributedCache.addFileToClassPath(mysqlPath, conf);
		conf.setMapOutputKeyClass(IntWritable.class);
		conf.setMapOutputValueClass(IntWritable.class);
		conf.setOutputKeyClass(IntWritable.class);
		conf.setOutputValueClass(IntWritable.class);

		conf.setOutputFormat(DBOutputFormat.class);
		conf.setInputFormat(DBInputFormat.class);

		DBConfiguration.configureDB(conf, "com.mysql.jdbc.Driver",
				"jdbc:mysql://localhost:3306/hadoop", "root", "123456");
		String[] infields = { "id", "game", "player_id", "coin" };
		// 从 t 表读数据
		DBInputFormat.setInput(conf, GameinfoRecord.class, "feiyingames", null,
				"id", infields);
		// mapreduce 将数据输出到 t2 表
		String[] outfields = { "game", "sum" };
		DBOutputFormat.setOutput(conf, "rs_table",outfields );
		// conf.setMapperClass(org.apache.hadoop.mapred.lib.IdentityMapper.class);
		conf.setMapperClass(DBInputMapper.class);
		conf.setCombinerClass(Combine.class);
		conf.setReducerClass(Reduce.class);
		JobClient.runJob(conf);
	}
}