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; }