警务大数据之表创建
第1关:Phoenix在Springboot应用
package com.educodeer.police1.case1;
import java.sql.Connection;
import java.sql.SQLException;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
@Service
public class Case1 {
//获取hikariCP连接池
@Autowired
DataSource dataSource;
/**
* HBase 表创建
* @throws SQLException
*/
public void createTable() throws SQLException {
/********** Begin *********/
Connection connection = dataSource.getConnection();
connection.createStatement().execute("CREATE TABLE TEST (ID INTEGER not null primary key, NAME VARCHAR)");
//spring.datasource.url=jdbc:phoenix:127.0.0.1:2181
//spring.datasource.driverClassName=org.apache.phoenix.jdbc.PhoenixDriver
/********** end *********/
}
}
第2关:通过POI事件读取Excel内容
package com.educodeer.police1.case1;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.hssf.eventusermodel.FormatTrackingHSSFListener;
import org.apache.poi.hssf.eventusermodel.HSSFEventFactory;
import org.apache.poi.hssf.eventusermodel.HSSFListener;
import org.apache.poi.hssf.eventusermodel.HSSFRequest;
import org.apache.poi.hssf.eventusermodel.MissingRecordAwareHSSFListener;
import org.apache.poi.hssf.record.LabelSSTRecord;
import org.apache.poi.hssf.record.Record;
import org.apache.poi.hssf.record.SSTRecord;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
/**
* POI的事件模式处理
*
*/
public class Case2 implements HSSFListener {
// Records we pick up as we process
private SSTRecord sstRecord;
// 存储行记录的容器
private List<String> rowlist = new ArrayList<String>();
public void process(InputStream stream) throws IOException {
POIFSFileSystem fs = new POIFSFileSystem(stream);
// 添加监听记录的事件
MissingRecordAwareHSSFListener listener = new MissingRecordAwareHSSFListener(this);
// 监听代理
FormatTrackingHSSFListener formatListener = new FormatTrackingHSSFListener(listener);
// 创建事件工厂
HSSFEventFactory factory = new HSSFEventFactory();
// 注册监听器
HSSFRequest request = new HSSFRequest();
request.addListenerForAllRecords(formatListener);
// 处理基于时间文档流(循环获取每一条Record进行处理)
factory.processWorkbookEvents(request, fs);
}
//记录处理
@Override
public void processRecord(Record record) {
/********** Begin *********/
int thisColumn = -1;
String value = null;
switch (record.getSid()) {
//所有Sheet的文本单元格的文本内容
case SSTRecord.sid:
sstRecord = (SSTRecord) record;
break;
case LabelSSTRecord.sid: // 单元格为字符串类型
LabelSSTRecord lsrec = (LabelSSTRecord) record;
//获取列数
thisColumn = lsrec.getColumn();
//sstRecord为null,则说明单元格为空
if (sstRecord == null) {
rowlist.add(thisColumn, " ");
} else {
//获取sstRecord对应位置的值
value = sstRecord.getString(lsrec.getSSTIndex()).toString().trim();
value = value.equals("") ? " " : value;
//把列名和对应的值存到rowlist里
rowlist.add(thisColumn, value);
}
break;
default:
break;
}
// 行结束时的操作
if (record.getSid() == -1) {
System.out.println(rowlist);
// 清空容器
rowlist.clear();
}
/********** end *********/
}
}