SnapshotsJdbcService

package com.tianditu.jdbc;

import java.io.ByteArrayInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.Reader;
import java.io.UnsupportedEncodingException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

/**
* 快照表的jdbc操作
* @author dell
*
*/
public class SnapshotsJdbcService {

private Connection connection = null;
private PreparedStatement pstmt = null;

private ResultSet rs;

private static Logger logger = Logger.getLogger(SnapshotsJdbcService.class
.getClass());

/**
* 全字段写入（不含主键）
* @param snapList
*/
public void addSnapshots(List<Snapshots> snapList) {
String sql = "insert into snapshots (seedId,url,html,fetchTime,segmentName,storageTime,parsed) values (?,?,?,?,?,?,?)";

try {
connection = new GetConnection().getCon();
pstmt = connection.prepareStatement(sql);
connection.setAutoCommit(false);
for(int i = 0; i < snapList.size(); i++) {
Snapshots snap = snapList.get(i);

pstmt.setInt(1, snap.getSeedId());
pstmt.setString(2, snap.getUrl());
String cert_data = snap.getHtml();
byte[] cert_dataBytes = cert_data.getBytes("UTF-8"); //编码要统一，设置为UTF-8
ByteArrayInputStream bais1 = new ByteArrayInputStream(cert_dataBytes);
pstmt.setBinaryStream(3, bais1,cert_dataBytes.length);//使用二进制读取，可以直接写入汉字，否则容易产生乱码

// pstmt.setString(3, snap.getHtml());
pstmt.setTimestamp(4, snap.getFetchTime());
pstmt.setString(5, snap.getSegmentName());
pstmt.setTimestamp(6, snap.getStorageTime());
pstmt.setInt(7, snap.getParsed());

pstmt.execute();
}

connection.commit();

GetConnection.close(pstmt);
GetConnection.close(connection);
} catch (Exception e) {
e.printStackTrace();
logger.info(e.getMessage());
logger.info(e.getStackTrace());
try {
connection.rollback();
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
}
}


//把数据库中blob类型转换成String类型
public String convertBlobToString(java.sql.Blob blob) {
String result = "";
try {
ByteArrayInputStream msgContent =(ByteArrayInputStream) blob.getBinaryStream();
byte[] byte_data = new byte[msgContent.available()];
msgContent.read(byte_data, 0,byte_data.length);
result = new String(byte_data);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return result;
}


//从数据库中以UTF-8取出
public String getBlodToString(InputStream is) {
Reader reader = null;
try {
reader = new InputStreamReader(is, "UTF-8");
StringBuffer sb = new StringBuffer(1024);
int charValue = 0;
while ((charValue = reader.read()) != -1) {
sb.append((char)charValue);
}
return sb.toString();
} catch (UnsupportedEncodingException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return null;
}

/**
* 获取所有的信息
* @return
*/
public List<Snapshots> listAllSnap() {
List<Snapshots> sList = new ArrayList<Snapshots>();

//String sql = "select * from snapshots";
String sql="SELECT snapshots.*,site_info.sitename,site_info.giscode FROM snapshots,site_info WHERE snapshots.seedId=site_info.seedId AND snapshots.parsed=0";
try {
connection = new GetConnection().getCon();
pstmt = connection.prepareStatement(sql);

rs = pstmt.executeQuery();
while(rs.next()) {
Snapshots snap = new Snapshots();
snap.setSnapId(rs.getInt(1));
snap.setSeedId(rs.getInt(2));
snap.setUrl(rs.getString(3));
// snap.setHtml(rs.getString(4));
// snap.setHtml(new SnapshotsJdbcService().convertBlobToString(rs.getBlob(4)));
snap.setHtml(new SnapshotsJdbcService().getBlodToString(rs.getBinaryStream(4)));
snap.setFetchTime(rs.getTimestamp(5));
snap.setSegmentName(rs.getString(6));
snap.setStorageTime(rs.getTimestamp(7));
snap.setParsed(rs.getInt(8));

snap.setSiteName(rs.getString(9));
snap.setGiscode(rs.getString(10));

sList.add(snap);
}

GetConnection.close(pstmt);
GetConnection.close(connection);
} catch (Exception e) {
e.printStackTrace();
logger.info(e.getMessage());
logger.info(e.getStackTrace());
}

return sList;
}

/**
* 获取所有的url（网页地址）
* @return
*/
public List<String> getAllUrl() {
List<String> sList = new ArrayList<String>();

String sql = "select url from snapshots";

try {
connection = new GetConnection().getCon();
pstmt = connection.prepareStatement(sql);

rs = pstmt.executeQuery();
while(rs.next()) {
sList.add(rs.getString("url"));
}

GetConnection.close(pstmt);
GetConnection.close(connection);
} catch (Exception e) {
logger.info("getConnection ERROR"+e.getMessage());
}

return sList;
}

/**
* 修改
* @param snapList
*/
public void updateSnapshots(List<Snapshots> snapList) {
String sql = "update snapshots set seedId=?,url=?,html=?,fetchTime=?,segmentName=?,storageTime=?,parsed=? where snapId=?";

try {
connection = new GetConnection().getCon();
pstmt = connection.prepareStatement(sql);
connection.setAutoCommit(false);

for(int i = 0; i < snapList.size(); i++) {
Snapshots snap = snapList.get(i);

pstmt.setInt(1, snap.getSeedId());
pstmt.setString(2, snap.getUrl());
pstmt.setString(3, snap.getHtml());
pstmt.setTimestamp(4, snap.getFetchTime());
pstmt.setString(5, snap.getSegmentName());
pstmt.setTimestamp(6, snap.getStorageTime());
pstmt.setInt(7, snap.getParsed());
pstmt.setInt(8, snap.getSnapId());

pstmt.execute();
}

connection.commit();

GetConnection.close(pstmt);
GetConnection.close(connection);


} catch (Exception e) {
e.printStackTrace();
try {
connection.rollback();
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
}
}

}