static void read() throws SQLException, IOException {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
st = conn.createStatement();
rs = st.executeQuery("select big_text from clob_test");
while (rs.next()) {
Clob clob = rs.getClob("big_text"); // 读取大文本，方式1
Reader reader = clob.getCharacterStream();
// reader=rs.getCharacterStream(1); //读取大文本，方式2
// String s=rs.getString(1); //读取大文本，方式3
File file = new File("JdbcUtils.java");
Writer writer = new BufferedWriter(new FileWriter(file));
char[] buff = new char[1024]; // 缓冲区
for (int i = 0; (i = reader.read(buff)) > 0; ) writer.write(buff, 0, i);
writer.close();
reader.close();
}
} finally {
JdbcUtils.free(rs, st, conn);
}
}
static void test() throws SQLException {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
conn.setAutoCommit(false);
conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
st = conn.createStatement();
String sql = "update user set money=money-10 where id=1";
st.executeUpdate(sql);
sql = "select money from user where id=2";
rs = st.executeQuery(sql);
float money = 0.0f;
if (rs.next()) {
money = rs.getFloat("money");
}
if (money > 400) throw new RuntimeException("已经超过最大值！");
sql = "update user set money=money+10 where id=2";
st.executeUpdate(sql);
conn.commit();
} catch (SQLException e) {
if (conn != null) conn.rollback();
throw e;
} finally {
JdbcUtils.free(rs, st, conn);
}
}
@Override
public void insert(User user)
throws DBException, NotUniqueUserLoginException, NotUniqueUserEmailException {
Connection conn = getConnection();
PreparedStatement ps = null;
try {
conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
conn.setAutoCommit(false);
if (existWithLogin0(conn, user.getLogin())) {
throw new NotUniqueUserLoginException("Login '" + user.getLogin() + "' doubled");
}
if (existWithEmail0(conn, user.getEmail())) {
throw new NotUniqueUserEmailException("Email '" + user.getEmail() + "' doubled");
}
ps = conn.prepareStatement(INSERT_SQL);
ps.setString(1, user.getLogin());
ps.setString(2, user.getEmail());
ps.executeUpdate();
conn.commit();
} catch (SQLException e) {
JdbcUtils.rollbackQuietly(conn);
e.printStackTrace();
throw new DBException("Can't execute SQL = '" + INSERT_SQL + "'", e);
} finally {
JdbcUtils.closeQuietly(ps);
JdbcUtils.closeQuietly(conn);
}
}
/**
* Query all records, reflection to a JavaBean List
*
* @param type
* @param sql
* @param args
* @param sqlTypes
* @return
* @throws Exception
*/
public <T> List<T> queryObjectListUseReflection(
Class<T> type, final String sql, Object[] args, int[] sqlTypes) {
if (showSql) {
logger.info(getFinalSql(sql, args, sqlTypes));
}
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
conn = getConnection();
pstmt = conn.prepareStatement(sql);
applyQueryTimeOut(pstmt);
if (args != null) {
if (sqlTypes != null) {
JdbcUtils.setPreparedStatementValue(pstmt, args, sqlTypes);
} else {
JdbcUtils.setPreparedStatementValue(pstmt, args);
}
}
rs = pstmt.executeQuery();
return ResultSetUtils.getObjectListUseReflection(type, rs);
} catch (Exception e) {
throw new RuntimeException(e);
} finally {
JdbcUtils.closeQuietly(conn, pstmt, rs);
}
}
/**
* Execute SQL query and return RowSet List
*
* @param sql
* @param args
* @param sqlTypes
* @return
* @throws SQLException
*/
public List<Row> queryForRowList(final String sql, Object[] args, int[] sqlTypes) {
if (showSql) {
logger.info(getFinalSql(sql, args, sqlTypes));
}
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
conn = getConnection();
pstmt = conn.prepareStatement(sql);
if (args != null) {
if (sqlTypes == null) {
JdbcUtils.setPreparedStatementValue(pstmt, args);
} else {
JdbcUtils.setPreparedStatementValue(pstmt, args, sqlTypes);
}
}
applyQueryTimeOut(pstmt);
rs = pstmt.executeQuery();
if (rs == null) {
return null;
}
return Row.valueOf(rs);
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
JdbcUtils.closeQuietly(conn, pstmt, rs);
}
}
/**
* Query given SQL to create a prepared statement from SQL and a list of arguments to bind to the
* query, mapping each row to a Java object via a RowMapper.
*
* @param sql
* @param args
* @param sqlTypes
* @param rowMapper
* @return
* @throws SQLException
*/
public List query(final String sql, Object[] args, int[] sqlTypes, RowMapper rowMapper) {
if (showSql) {
logger.info(getFinalSql(sql, args, sqlTypes));
}
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
conn = getConnection();
pstmt = conn.prepareStatement(sql);
if (args != null) {
if (sqlTypes == null) {
JdbcUtils.setPreparedStatementValue(pstmt, args);
} else {
JdbcUtils.setPreparedStatementValue(pstmt, args, sqlTypes);
}
}
applyQueryTimeOut(pstmt);
rs = pstmt.executeQuery();
if (rs == null) {
return null;
} else {
List resultList = new ArrayList();
while (rs.next()) {
resultList.add(rowMapper.mapRow(rs, rs.getRow()));
}
return resultList;
}
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
JdbcUtils.closeQuietly(conn, pstmt, rs);
}
}
/**
* Issue a single SQL update operation (such as an insert, update or delete statement) via a
* prepared statement, binding the given arguments.
*
* @param sql
* @param args
* @param argTypes
* @return
* @throws SQLException
*/
public int update(final String sql, Object[] args, int[] argTypes) {
validateSqlParameter(sql);
if (showSql) {
logger.info(getFinalSql(sql, args, argTypes));
}
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = getConnection();
pstmt = conn.prepareStatement(sql);
applyQueryTimeOut(pstmt);
if (args != null) {
if (argTypes != null) {
JdbcUtils.setPreparedStatementValue(pstmt, args, argTypes);
} else {
JdbcUtils.setPreparedStatementValue(pstmt, args);
}
}
return pstmt.executeUpdate();
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
JdbcUtils.closeQuietly(pstmt);
JdbcUtils.closeQuietly(conn);
}
}
/**
* Issue multiple SQL updates on a single JDBC Statement using batching. Will fall back to
* separate updates on a single Statement if the JDBC driver does not support batch updates.
*
* @param sqls
* @return
* @throws SQLException
*/
public int[] batchUpdate(final String[] sqls) {
validateSqlParameter(sqls);
Connection conn = null;
Statement stmt = null;
try {
conn = getConnection();
conn.setAutoCommit(false);
applyQueryTimeOut(stmt);
stmt = conn.createStatement();
for (String sql : sqls) {
if (showSql) {
logger.info(getFinalSql(sql));
}
stmt.addBatch(sql);
}
int[] result = stmt.executeBatch();
conn.commit();
return result;
} catch (SQLException e) {
try {
JdbcUtils.rollback(conn);
} catch (Exception unused) {
}
throw new RuntimeException(e);
} finally {
JdbcUtils.closeQuietly(stmt);
JdbcUtils.closeQuietly(conn);
}
}
// 这两个方法的主要复杂点不再jdbc上，而是在输入输出上的；
static void read() throws SQLException, IOException {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
st = conn.createStatement();
rs = st.executeQuery("select big_bit from blob_test");
while (rs.next()) {
// Blob blob = rs.getBlob("1"); //方法1
// InputStream in = blob.getBinaryStream();
InputStream in = rs.getBinaryStream(1); // 方法2，这个方法更好
File file = new File("COPY.jpg");
OutputStream out = new BufferedOutputStream(new FileOutputStream(file));
byte[] buff = new byte[1024]; // 缓冲区
for (int i = 0; (i = in.read(buff)) > 0; ) out.write(buff, 0, i);
out.close();
in.close();
}
} finally {
JdbcUtils.free(rs, st, conn);
}
}
static void read() throws SQLException, IOException {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
// conn = JdbcUtilsSingle.getInstance().getConnection();
st = conn.createStatement();
rs = st.executeQuery("select text from textdemo");
while (rs.next()) {
Blob blob = rs.getBlob(1);
InputStream in = blob.getBinaryStream();
// reader = rs.getCharacterStream(1);
File file = new File("JdbcUtils_bak.java");
OutputStream out = new BufferedOutputStream(new FileOutputStream(file));
byte[] buff = new byte[1024];
for (int i = 0; (i = in.read(buff)) > 0; ) {
out.write(buff, 0, i);
}
out.close();
in.close();
}
} finally {
JdbcUtils.free(rs, st, conn);
}
}
@Override
public List<User> selectAll() throws DBException {
Connection conn = getConnection();
Statement statement = null;
ResultSet rs = null;
try {
conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
conn.setAutoCommit(false);
statement = conn.createStatement();
rs = statement.executeQuery(SELECT_ALL_SQL);
List<User> result = new ArrayList<User>();
while (rs.next()) {
int id = rs.getInt("id");
String login = rs.getString("login");
String email = rs.getString("email");
User user = new User(id);
user.setLogin(login);
user.setEmail(email);
result.add(user);
}
conn.commit();
return result;
} catch (SQLException e) {
JdbcUtils.rollbackQuietly(conn);
throw new DBException("Can't execute SQL = '" + SELECT_ALL_SQL + "'", e);
} finally {
JdbcUtils.closeQuietly(rs);
JdbcUtils.closeQuietly(statement);
JdbcUtils.closeQuietly(conn);
}
}
public boolean savetomysql(int topicid, List<String[]> list) {
boolean flag = false;
JdbcUtils db = new JdbcUtils();
db.getConnection();
String del = "delete from emotion where topicid=?";
List<Object> delparams = new ArrayList<Object>();
delparams.add(topicid);
try {
db.updateBypreparedPreparedStatement(del, delparams);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
String sql =
"insert into emotion(topicid ,content,emotionalcategory,confidence) values (?,?,?,?)";
for (int i = 0; i < list.size(); i++) {
List<Object> addparams = new ArrayList<Object>();
addparams.add(topicid);
addparams.add(list.get(i)[2]);
addparams.add(list.get(i)[0]);
addparams.add(list.get(i)[1]);
try {
flag = db.updateBypreparedPreparedStatement(sql, addparams);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return flag;
}
@Override
public void run() {
// TODO Auto-generated method stub
final String[] crawlerArgs = {
searchwords, htmlpath, txtpath, xmlpath, ippath + "/plainIPs.txt", pagecount
};
int ifCrawlFlag = 0;
if (ifCrawlFlag == 0) {
try {
Crawler c = new Crawler();
c.excute(crawlerArgs, dir);
} catch (IOException | URISyntaxException | InterruptedException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//
System.out.println("now begin to classify the file：");
emoClassifyUnity emo = new emoClassifyUnity();
List<String[]> list2 = emo.emoClassify("./mycraw/" + dir + "/" + searchwords + ".xml");
// save the topic
JdbcUtils db = new JdbcUtils();
db.getConnection();
String sql = "insert into topic(topic ,topicdescription,pdate) values (?,?,?)";
String deltopicid = "delete from topic where topic=?";
//
List<Object> paramsdel = new ArrayList<Object>();
paramsdel.add(topiclist[0]);
try {
db.updateBypreparedPreparedStatement(deltopicid, paramsdel);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
//
System.out.println(topiclist[0] + "---->>>" + topiclist[1]);
List<Object> params = new ArrayList<Object>();
params.add(topiclist[0]);
params.add(topiclist[1]);
java.sql.Date currentDate = new java.sql.Date(System.currentTimeMillis());
params.add(currentDate);
try {
db.updateBypreparedPreparedStatement(sql, params);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
db.releasecon();
//
savetomysql(gettopicid(searchwords), list2);
//
String info = topiclist[0] + "：结束下载，分词，结果保存在数据库内";
logwrite.write("./timerlog.txt", info);
}
@Override
public String toString() {
if (byte[].class.isInstance(object)) {
return JdbcUtils.toHexString(asBytes());
} else if (Blob.class.isInstance(object)) {
return JdbcUtils.toHexString(JdbcUtils.readBlob((Blob) object));
}
return asString();
}
public List getMessages() throws Exception {
Connection con = null;
try {
con = this.connector.getConnection();
Object[] readParams = JdbcUtils.getParams(getEndpointURI(), this.readParams, null);
Object results =
new QueryRunner().query(con, this.readStmt, readParams, new MapListHandler());
return (List) results;
} finally {
JdbcUtils.close(con);
}
}
@Override
public void deleteAll() throws DaoException {
PreparedStatement statement = null;
try {
statement = JdbcUtils.getConnection().prepareStatement(DELETE);
statement.execute();
} catch (SQLException e) {
logger.warn(e.getMessage());
throw new DaoException(e);
} finally {
JdbcUtils.closeStatement(statement);
}
}
/** {@inheritDoc} */
@Override
public ResultSet executeQuery(String sql) throws SQLException {
ensureNotClosed();
rs = null;
if (sql == null || sql.isEmpty()) throw new SQLException("SQL query is empty");
try {
byte[] packet =
conn.client()
.compute()
.execute(
TASK_NAME,
JdbcUtils.marshalArgument(
JdbcUtils.taskArgument(
conn.nodeId(),
conn.cacheName(),
sql,
timeout,
args,
fetchSize,
maxRows)));
byte status = packet[0];
byte[] data = new byte[packet.length - 1];
U.arrayCopy(packet, 1, data, 0, data.length);
if (status == 1) throw JdbcUtils.unmarshalError(data);
else {
List<?> msg = JdbcUtils.unmarshal(data);
assert msg.size() == 7;
UUID nodeId = (UUID) msg.get(0);
UUID futId = (UUID) msg.get(1);
List<String> tbls = (List<String>) msg.get(2);
List<String> cols = (List<String>) msg.get(3);
List<String> types = (List<String>) msg.get(4);
Collection<List<Object>> fields = (Collection<List<Object>>) msg.get(5);
boolean finished = (Boolean) msg.get(6);
return new JdbcResultSet(
this, nodeId, futId, tbls, cols, types, fields, finished, fetchSize);
}
} catch (GridClientException e) {
throw new SQLException("Failed to query Ignite.", e);
}
}
private void update(Company company) throws DaoException {
PreparedStatement statement = null;
try {
statement = JdbcUtils.getConnection().prepareStatement(UPDATE);
statement.setString(1, company.getName());
statement.setLong(2, company.getId());
statement.execute();
} catch (SQLException e) {
logger.warn(e.getMessage());
throw new DaoException(e);
} finally {
JdbcUtils.closeStatement(statement);
}
}
public int gettopicid(String topic) {
String sql = "select topicid from topic where topic=?";
JdbcUtils db = new JdbcUtils();
db.getConnection();
List<Object> addparams = new ArrayList<Object>();
addparams.add(topic);
try {
return (int) db.getsimpleresult(sql, addparams).get("topicid");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return 0;
}
public static String getFinalSql(String sql, Object[] args, int[] sqlTypes) {
if (sqlTypes == null || sqlTypes.length < 1) {
return getFinalSql(sql, args);
}
StringBuilder sb = new StringBuilder();
char[] sqlArray = sql.toCharArray();
int i = 0;
for (char c : sqlArray) {
if (c == '?') {
Object o = args[i];
int type = sqlTypes[i];
if (JdbcUtils.isNumeric(type)) {
sb.append(o);
} else {
sb.append('\'');
sb.append(o);
sb.append('\'');
}
i++;
} else {
sb.append(c);
}
}
return sb.toString();
}
public List<Map<String, Object>> findKColumnsCNVD(int offset, int num, List<Object> params)
throws SQLException {
String sql =
"SELECT * FROM cnvd_net_device LIMIT "
+ String.valueOf(num)
+ " OFFSET "
+ String.valueOf(offset);
List<Map<String, Object>> list = jdbcUtils.findSimplesResult(sql, params);
Map<String, Object> map = new HashMap<String, Object>();
Date date;
DateFormat dataFormat = new SimpleDateFormat("yyyy-MM-dd");
for (int i = 0; i < list.size(); i++) {
map = list.get(i);
/*
* The timestamps of python and Java are different.
* Java uses millisecond while python uses seconds.
*/
String uploadTime = dataFormat.format((int) map.get("modify_time") * 1000.0);
String publishTime = dataFormat.format((int) map.get("publish_time") * 1000.0);
String modifyTime = dataFormat.format((int) map.get("modify_time") * 1000.0);
list.get(i).put("publish_time", publishTime);
list.get(i).put("upload_time", uploadTime);
list.get(i).put("modify_time", modifyTime);
}
return list;
}
public Connection getConnection() throws Exception {
Transaction tx = TransactionCoordination.getInstance().getTransaction();
if (tx != null) {
if (tx.hasResource(dataSource)) {
logger.debug("Retrieving connection from current transaction: " + tx);
return (Connection) tx.getResource(dataSource);
}
}
logger.debug("Retrieving new connection from data source");
Connection con;
try {
con = dataSource.getConnection();
} catch (Exception e) {
throw new ConnectException(e, this);
}
if (tx != null) {
logger.debug("Binding connection " + con + " to current transaction: " + tx);
try {
tx.bindResource(dataSource, con);
} catch (TransactionException e) {
JdbcUtils.close(con);
throw new RuntimeException("Could not bind connection to current transaction: " + tx, e);
}
}
return con;
}
/*
* (non-Javadoc)
*
* @see org.mule.providers.AbstractMessageDispatcher#doDispatch(org.mule.umo.UMOEvent)
*/
protected void doDispatch(UMOEvent event) throws Exception {
if (logger.isDebugEnabled()) {
logger.debug("Dispatch event: " + event);
}
UMOImmutableEndpoint endpoint = event.getEndpoint();
String writeStmt = endpoint.getEndpointURI().getAddress();
String str;
if ((str = this.connector.getQuery(endpoint, writeStmt)) != null) {
writeStmt = str;
}
if (StringUtils.isEmpty(writeStmt)) {
throw new IllegalArgumentException("Write statement should not be null");
}
if (!"insert".equalsIgnoreCase(writeStmt.substring(0, 6))
&& !"update".equalsIgnoreCase(writeStmt.substring(0, 6))
&& !"delete".equalsIgnoreCase(writeStmt.substring(0, 6))) {
throw new IllegalArgumentException(
"Write statement should be an insert / update / delete sql statement");
}
List paramNames = new ArrayList();
writeStmt = JdbcUtils.parseStatement(writeStmt, paramNames);
Object[] paramValues = JdbcUtils.getParams(endpoint, paramNames, event.getTransformedMessage());
UMOTransaction tx = TransactionCoordination.getInstance().getTransaction();
Connection con = null;
try {
con = this.connector.getConnection();
int nbRows = connector.createQueryRunner().update(con, writeStmt, paramValues);
if (nbRows != 1) {
logger.warn("Row count for write should be 1 and not " + nbRows);
}
if (tx == null) {
JdbcUtils.commitAndClose(con);
}
logger.debug("Event dispatched succesfuly");
} catch (Exception e) {
logger.debug("Error dispatching event: " + e.getMessage(), e);
if (tx == null) {
JdbcUtils.rollbackAndClose(con);
}
throw e;
}
}
/**
* Issue a single SQL execute, typically a DDL statement.
*
* @param sql
* @throws SQLException
*/
public void execute(final String sql) {
if (showSql) {
logger.info(getFinalSql(sql));
}
Connection conn = null;
Statement stmt = null;
try {
conn = getConnection();
stmt = conn.createStatement();
applyQueryTimeOut(stmt);
stmt.execute(sql);
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
JdbcUtils.closeQuietly(stmt);
JdbcUtils.closeQuietly(conn);
}
}
public List<String> findBrands() throws SQLException {
String sql = "SELECT distinct threat_rank FROM vulnerability_info.cnvd_net_device";
List<Map<String, Object>> list = jdbcUtils.findSimplesResult(sql, null);
List<String> ret = new ArrayList<String>();
for (int i = 0; i < list.size(); i++) {
ret.add((String) list.get(i).get("threat_rank"));
}
return ret;
}
private void save(Company company) throws DaoException {
PreparedStatement statement = null;
ResultSet resultKey = null;
try {
statement = JdbcUtils.getConnection().prepareStatement(SAVE, Statement.RETURN_GENERATED_KEYS);
statement.setString(1, company.getName());
statement.execute();
resultKey = statement.getGeneratedKeys();
resultKey.next();
long id = resultKey.getLong(1);
company.setId(id);
} catch (SQLException e) {
logger.warn(e.getMessage());
throw new DaoException(e);
} finally {
JdbcUtils.closeResultSet(resultKey);
JdbcUtils.closeStatement(statement);
}
}
@Override
public int deleteById(int id) throws DBException {
Connection conn = getConnection();
PreparedStatement ps = null;
try {
conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
conn.setAutoCommit(false);
ps = conn.prepareStatement(DELETE_BY_ID_SQL);
ps.setInt(1, id);
int result = ps.executeUpdate();
conn.commit();
return result;
} catch (SQLException e) {
JdbcUtils.rollbackQuietly(conn);
throw new DBException("Can't execute SQL = '" + DELETE_BY_ID_SQL + "'", e);
} finally {
JdbcUtils.closeQuietly(ps);
JdbcUtils.closeQuietly(conn);
}
}
public JdbcMessageReceiver(
UMOConnector connector,
UMOComponent component,
UMOEndpoint endpoint,
String readStmt,
String ackStmt)
throws InitialisationException {
super(
connector,
component,
endpoint,
new Long(((JdbcConnector) connector).getPollingFrequency()));
this.receiveMessagesInTransaction = false;
this.connector = (JdbcConnector) connector;
this.readParams = new ArrayList();
this.readStmt = JdbcUtils.parseStatement(readStmt, this.readParams);
this.ackParams = new ArrayList();
this.ackStmt = JdbcUtils.parseStatement(ackStmt, this.ackParams);
}
@Override
public List<Company> getAll() throws DaoException {
PreparedStatement statement = null;
ResultSet resultSet = null;
List<Company> result = new ArrayList<Company>();
try {
statement = JdbcUtils.getConnection().prepareStatement(GET_ALL);
resultSet = statement.executeQuery();
while (resultSet.next()) {
result.add(companyFromTuple(resultSet));
}
} catch (SQLException e) {
logger.warn(e.getMessage());
throw new DaoException(e);
} finally {
JdbcUtils.closeResultSet(resultSet);
JdbcUtils.closeStatement(statement);
}
return result;
}
@Override
public Company findByName(String companyName) throws DaoException {
PreparedStatement statement = null;
ResultSet resultSet = null;
try {
statement = JdbcUtils.getConnection().prepareStatement(FIND_BY_NAME);
statement.setString(1, companyName);
resultSet = statement.executeQuery();
if (resultSet.next()) {
return companyFromTuple(resultSet);
}
} catch (SQLException e) {
logger.warn(e.getMessage());
throw new DaoException(e);
} finally {
JdbcUtils.closeResultSet(resultSet);
JdbcUtils.closeStatement(statement);
}
return null;
}
