public static void main(String[] args) { conn = getConnection(); QueryRunner qr = new QueryRunner(); try { conn.setAutoCommit(false); List<Syuser> al = (List) qr.query( conn, "SELECT syuser.* FROM syuser", new BeanListHandler<Syuser>(Syuser.class)); for (Syuser u : al) { List<Syrole> rl = qr.query( conn, "SELECT syrole.* FROM syrole JOIN syuser_syrole ON syuser_syrole.SYROLE_ID = syrole.ID WHERE syuser_syrole.SYUSER_ID = ?", new BeanListHandler<Syrole>(Syrole.class), u.getId()); u.getSyroles().addAll(rl); System.out.println(u.getLoginname()); System.out.println(JSON.toJSONString(u.getSyroles())); } } catch (SQLException e) { e.printStackTrace(); try { DbUtils.rollback(conn); } catch (SQLException e1) { e1.printStackTrace(); } } finally { try { DbUtils.commitAndClose(conn); } catch (SQLException e) { e.printStackTrace(); } } }
@Override public void execute(final Context context) throws SQLException { final Date now = new Date(system.now()); Connection connection = null; PreparedStatement pstmt = null; try { connection = openConnection(); pstmt = connection.prepareStatement("SELECT p.uuid as uuid FROM projects p WHERE p.kee=?"); MassUpdate massUpdate = context.prepareMassUpdate(); massUpdate.select( "SELECT f.id, f.data FROM issue_filters f WHERE f.data like '%componentRoots=%'"); massUpdate.update("UPDATE issue_filters SET data=?, updated_at=? WHERE id=?"); final PreparedStatement finalPstmt = pstmt; massUpdate.execute( new MassUpdate.Handler() { @Override public boolean handle(Select.Row row, SqlStatement update) throws SQLException { Long id = row.getNullableLong(1); String data = row.getNullableString(2); if (data == null) { return false; } update.setString(1, convertData(finalPstmt, data)); update.setDate(2, now); update.setLong(3, id); return true; } }); } finally { DbUtils.closeQuietly(connection); DbUtils.closeQuietly(pstmt); } }
private String getContentFromDB(String itemID) throws Exception { StringBuilder dbContent = new StringBuilder(""); PreparedStatement pstmt = null; ResultSet rs = null; try { Connection connexion = ConnectionFactory.getConnection(); pstmt = connexion.prepareStatement( "select SR_SEQ_NUM, SR_TEXT from SR_SCRIPTS where SR_ITEM_ID = ? order by 1"); pstmt.setString(1, itemID); rs = pstmt.executeQuery(); while (rs.next()) { dbContent = dbContent.append(rs.getString("SR_TEXT")); } } catch (Exception e) { throw new Exception( "\r\n***ERROR RETURNED BY THE JVM : " + e.getMessage() + "\r\n(select SR_SEQ_NUM, SR_TEXT from SR_SCRIPTS where SR_ITEM_ID = '" + itemID + "' order by 1)"); } finally { DbUtils.closeQuietly(rs); DbUtils.closeQuietly(pstmt); } return dbContent.toString(); }
public <S> void execute(InputLoader<S> inputLoader, InputConverter<S> converter) { long count = 0; Connection readConnection = null; Statement stmt = null; ResultSet rs = null; Connection writeConnection = null; PreparedStatement writeStatement = null; try { writeConnection = db.getDataSource().getConnection(); writeConnection.setAutoCommit(false); writeStatement = writeConnection.prepareStatement(converter.updateSql()); readConnection = db.getDataSource().getConnection(); readConnection.setAutoCommit(false); stmt = readConnection.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); stmt.setFetchSize(GROUP_SIZE); if (db.getDialect().getId().equals(MySql.ID)) { stmt.setFetchSize(Integer.MIN_VALUE); } else { stmt.setFetchSize(GROUP_SIZE); } rs = stmt.executeQuery(convertSelectSql(inputLoader.selectSql(), db)); int cursor = 0; while (rs.next()) { if (converter.convert(inputLoader.load(rs), writeStatement)) { writeStatement.addBatch(); cursor++; count++; } if (cursor == GROUP_SIZE) { writeStatement.executeBatch(); writeConnection.commit(); cursor = 0; } } if (cursor > 0) { writeStatement.executeBatch(); writeConnection.commit(); } } catch (SQLException e) { SqlUtil.log(LOGGER, e); throw processError(e); } catch (Exception e) { throw processError(e); } finally { DbUtils.closeQuietly(writeStatement); DbUtils.closeQuietly(writeConnection); DbUtils.closeQuietly(readConnection, stmt, rs); LOGGER.info("{} rows have been updated", count); } }
// Cache en BD via JDBC une séquence de désinstallation // le paramètre est la liste des valeurs à insérer dans la table // SR_UNINSTITEMS public void cacheIntoDB( Connection connexion, String _package, int _itemOrder, String _pieceType, String _delimiter, Integer _keepDelimiter, String _dbProcName) throws Exception { setConnection(connexion); PreparedStatement pstmt = null; // insertion SR_UNINSTITEMS long theLong = System.currentTimeMillis(); String itemID = String.valueOf(theLong) + '-' + increment.incrementAndGet(); try { pstmt = connexion.prepareStatement( "insert into SR_UNINSTITEMS(SR_ITEM_ID, " + "SR_PACKAGE, SR_ACTION_TAG, SR_ITEM_ORDER, SR_FILE_NAME, SR_FILE_TYPE, SR_DELIMITER, " + "SR_KEEP_DELIMITER, SR_DBPROC_NAME) values ( ?, ?, ?, ?, ?, ?, ?, ?, ?)"); pstmt.setString(1, itemID); pstmt.setString(2, _package); pstmt.setString(3, actionName); pstmt.setInt(4, _itemOrder); pstmt.setString(5, pieceName); pstmt.setString(6, _pieceType); pstmt.setString(7, _delimiter); pstmt.setInt(8, _keepDelimiter); pstmt.setString(9, _dbProcName); pstmt.executeUpdate(); } catch (Exception ex) { throw new Exception("\n\t\t***ERROR RETURNED BY THE RDBMS : " + ex.getMessage() + '\n', ex); } finally { DbUtils.closeQuietly(pstmt); } try { // insertion SR_SCRIPTS final String[] subS = getSubStrings(content); pstmt = connexion.prepareStatement( "insert into SR_SCRIPTS(SR_ITEM_ID, SR_SEQ_NUM, SR_TEXT) " + "values (?, ?, ? )"); for (int i = 0; i < subS.length; i++) { pstmt.setString(1, itemID); pstmt.setInt(2, i); pstmt.setString(3, subS[i]); pstmt.executeUpdate(); } } catch (Exception ex) { throw new Exception("\n\t\t***ERROR RETURNED BY THE RDBMS : " + ex.getMessage() + '\n', ex); } finally { DbUtils.closeQuietly(pstmt); } }
/** * {@inheritDoc} * * <p>此操作会清空 Cache 中 authorities 对应的 value,下次请求时装载。 * * @see #getAll() */ public void delete(Authority auth) throws JibuException { Connection conn = null; try { conn = ConnectionUtils.getConnection(); authDAO.delete(conn, auth); DbUtils.commitAndClose(conn); Cache cache = CacheUtils.getAuthCache(); cache.remove("authorities"); } catch (SQLException e) { DbUtils.rollbackAndCloseQuietly(conn); throw new JibuException(e.getMessage()); } }
public String loadSchema() { save(); loadSchemaResult = false; Connection conn = null; String url = "jdbc:mysql://" + dbconfig.getHost() + ":" + dbconfig.getPort() + "/" + dbconfig.getDatabase_name(); String sql_tables = String.format( "select TABLE_SCHEMA, TABLE_NAME, TABLE_COMMENT from information_schema.tables where TABLE_SCHEMA=\"%s\";", dbconfig.getDatabase_name()); String sql_columns = String.format( "select TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_TYPE, COLUMN_KEY, COLUMN_COMMENT from information_schema.columns where TABLE_SCHEMA=\"%s\";", dbconfig.getDatabase_name()); String sql_keys = String.format( "select CONSTRAINT_NAME, TABLE_NAME, COLUMN_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME from information_schema.KEY_COLUMN_USAGE where TABLE_SCHEMA=\"%s\";", dbconfig.getDatabase_name()); try { DbUtils.loadDriver(driver); conn = DriverManager.getConnection(url, dbconfig.getUsername(), dbconfig.getPassword()); QueryRunner qr = new QueryRunner(); List<Map<String, Object>> tables = qr.query(conn, sql_tables, new MapListHandler()); List<Map<String, Object>> columns = qr.query(conn, sql_columns, new MapListHandler()); List<Map<String, Object>> keys = qr.query(conn, sql_keys, new MapListHandler()); dbconfig.setTables_json(JSONUtil.serialize(tables, false)); dbconfig.setColumns_json(JSONUtil.serialize(columns, false)); dbconfig.setKeys_json(JSONUtil.serialize(keys, false)); DbUtils.closeQuietly(conn); } catch (Exception ex) { return ERROR; } loadSchemaResult = true; SaveLog.saveLog( new Users((long) 1), "11", "数据库映射", new Date(), "数据库-企业数据库-模板映射—导入数据库配置方案", Constant.DATABASE_OPERATION, this.getClass().getName()); return SUCCESS; }
List<MeasureFilterRow> execute(Connection connection) throws SQLException { PreparedStatement statement = connection.prepareStatement(sql); ResultSet rs = null; try { for (int index = 0; index < dateParameters.size(); index++) { statement.setLong(index + 1, dateParameters.get(index)); } rs = statement.executeQuery(); return process(rs); } finally { DbUtils.closeQuietly(rs); DbUtils.closeQuietly(statement); } }
@SuppressWarnings({"unchecked", "rawtypes"}) public List<MenuItemDTO> listeGetir() { List<MenuItemDTO> liste = new ArrayList<MenuItemDTO>(); Connection con = null; try { con = DAOBase.instance().getConnection(); } catch (CreateException e) { // TODO Auto-generated catch block e.printStackTrace(); } try { con.setAutoCommit(false); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } QueryRunner runner = new QueryRunner(); try { liste = (List<MenuItemDTO>) runner.query(con, Sorgular._MENULIST_.qry, new BeanListHandler(MenuItemDTO.class)); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { DbUtils.closeQuietly(con); } return liste == null ? new ArrayList<MenuItemDTO>() : liste; }
private Queue<long[]> initGroupOfViolationIds(Database database) throws SQLException { Connection connection = database.getDataSource().getConnection(); Statement stmt = null; ResultSet rs = null; try { connection.setAutoCommit(false); stmt = connection.createStatement(); stmt.setFetchSize(10000); rs = stmt.executeQuery("select id from rule_failures"); Queue<long[]> queue = new ConcurrentLinkedQueue<>(); totalViolations = 0; long[] block = new long[VIOLATION_GROUP_SIZE]; int cursor = 0; while (rs.next()) { block[cursor] = rs.getLong(1); cursor++; totalViolations++; if (cursor == VIOLATION_GROUP_SIZE) { queue.add(block); block = new long[VIOLATION_GROUP_SIZE]; cursor = 0; } } if (cursor > 0) { queue.add(block); } return queue; } finally { DbUtils.closeQuietly(connection, stmt, rs); } }
private void joinWorldAssembly(Connection conn, int nationId) throws SQLException { PreparedStatement endorsements = conn.prepareStatement("UPDATE assembly.nation SET wa_member = 1 WHERE id = ?"); endorsements.setInt(1, nationId); endorsements.executeUpdate(); DbUtils.closeQuietly(endorsements); }
private void relocateNation(Connection conn, int nationId, String nation, String happening) throws SQLException { Matcher match = Utils.REGION_PATTERN.matcher(happening); String prevRegion = null; String newRegion = null; if (match.find()) { String title = happening.substring(match.start() + 2, match.end() - 2); prevRegion = Utils.sanitizeName(title); } if (match.find()) { String title = happening.substring(match.start() + 2, match.end() - 2); newRegion = Utils.sanitizeName(title); } Logger.info("Relocating " + nation + " from " + prevRegion + " to " + newRegion); if (prevRegion != null && newRegion != null) { // Double check they are still at their prev region before setting their new region! int newRegionId = getOrCreateRegion(conn, nation, newRegion); PreparedStatement update = conn.prepareStatement( "UPDATE assembly.nation SET region = ?, wa_member = 2 WHERE id = ? AND region = ?"); update.setInt(1, newRegionId); update.setInt(2, nationId); update.setInt(3, getOrCreateRegion(conn, nation, prevRegion)); update.executeUpdate(); DbUtils.closeQuietly(update); } }
private int getRegionOfNation(Connection conn, int nationId) throws SQLException { PreparedStatement select = null; ResultSet result = null; try { select = conn.prepareStatement("SELECT region FROM assembly.nation WHERE id = ?"); select.setInt(1, nationId); result = select.executeQuery(); if (result.next()) { return result.getInt(1); } return -1; } finally { DbUtils.closeQuietly(result); DbUtils.closeQuietly(select); } }
@SuppressWarnings({"unchecked", "rawtypes"}) public List<AnaMenu> anaListeGetir() throws ReadException { List<AnaMenu> liste = new ArrayList<AnaMenu>(); Connection con = null; try { con = DAOBase.instance().getConnection(); con.setAutoCommit(false); QueryRunner runner = new QueryRunner(); liste = (List<AnaMenu>) runner.query(con, Sorgular._ANAMENULIST_.qry, new BeanListHandler(AnaMenu.class)); for (AnaMenu s : liste) { s.setAltMenuler(altListeGetir(con, s.getMenuitemId())); } } catch (CreateException e) { throw new ReadException(Messages._SQL_500_.getMesaj(), null); } catch (SQLException e) { throw new ReadException(Messages._SQL_500_.getMesaj(), null); } finally { DbUtils.closeQuietly(con); } return liste == null ? new ArrayList<AnaMenu>() : liste; }
private List<Project> fetchAllActiveProjects(Connection connection) throws ProjectManagerException { QueryRunner runner = new QueryRunner(); ProjectResultHandler handler = new ProjectResultHandler(); List<Project> projects = null; try { projects = runner.query(connection, ProjectResultHandler.SELECT_ALL_ACTIVE_PROJECTS, handler); for (Project project : projects) { List<Triple<String, Boolean, Permission>> permissions = fetchPermissionsForProject(connection, project); for (Triple<String, Boolean, Permission> entry : permissions) { if (entry.getSecond()) { project.setGroupPermission(entry.getFirst(), entry.getThird()); } else { project.setUserPermission(entry.getFirst(), entry.getThird()); } } } } catch (SQLException e) { throw new ProjectManagerException("Error retrieving all projects", e); } finally { DbUtils.closeQuietly(connection); } return projects; }
/** * Preloads the data * * @param request request from the client * @param response response to the client * @throws ServletException if any error occurs while executing the servlet * @throws IOException if any error occurs in the client/server communication. */ private void preload(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { List<AreaBean> areaDomain = null; List<LinguaBean> languageDomain = null; List<UniversitaBean> universityDomain = null; Connection conn = null; Message m = null; try { conn = DS.getConnection(); languageDomain = GetLinguaValues.getLinguaDomain(conn); areaDomain = GetAreaValues.getAreaDomain(conn); universityDomain = GetUniversitaValues.getDomain(conn); } catch (SQLException ex) { m = new Message("Error while getting the classes list.", "XXX", "Please, contact the admin."); req.setAttribute("message", m); errorForward(req, resp); return; } finally { DbUtils.closeQuietly(conn); // always closes the connection } // Send data to the view req.setAttribute("languageDomain", languageDomain); req.setAttribute("areaDomain", areaDomain); req.setAttribute("universities", universityDomain); /* Forward to the Search JSP page */ getServletContext().getRequestDispatcher("/jsp/search_class.jsp").forward(req, resp); }
private static List<FirmaDTO> getUpdateFirmalar() throws ReadException { QueryRunner runner = new QueryRunner(); Connection con = null; List<FirmaDTO> list = new ArrayList<FirmaDTO>(); try { con = dao.getConnection(); con.setAutoCommit(false); list = (List<FirmaDTO>) runner.query( con, "select kod, unvan, adsoyad, gsm_telefon, il, ilce, adres, durumu, tescil_no," + "uygunluk_belgesi, gecerlilik_suresi, servis_sozlesme, sozlesme_tarihi," + "tse_belge_no, eposta,monte_eden as monteEden," + "yetkili_servis as yetkiliServis," + "tse_belgesi as tseBelgesi," + "telefon_no as telefonNo," + "telefon_no_dahili as dahili," + "ce_belge_tipi as ceBelgeTipi FROM " + "akm.bakimci_firma WHERE durumu='1'", new BeanListHandler<FirmaDTO>(FirmaDTO.class)); } catch (Exception e) { throw new ReadException(e.getMessage(), null); } finally { DbUtils.closeQuietly(con); } return list; }
/** * Forwards the pre-loaded data to the insert form * * @param request request from the client * @param response response to the client * @throws ServletException if any error occurs while executing the servlet * @throws IOException if any error occurs in the client/server communication. */ protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { List<LinguaBean> languageDomain = null; List<AreaBean> areaDomain = null; Connection conn = null; Message m = null; try { conn = DS.getConnection(); languageDomain = GetLinguaValues.getLinguaDomain(conn); areaDomain = GetAreaValues.getAreaDomain(conn); } catch (SQLException ex) { m = new Message("Error while getting the class.", "XXX", "Please, contact the admin."); } finally { DbUtils.closeQuietly(conn); // always closes the connection } if (m == null) { // forward to the insert FORM req.setAttribute("languageDomain", languageDomain); req.setAttribute("areaDomain", areaDomain); getServletContext().getRequestDispatcher("/jsp/insert_class.jsp").forward(req, resp); } else { req.setAttribute("message", m); getServletContext().getRequestDispatcher("/jsp/error.jsp").forward(req, resp); } }
public void executeSingleUpdate(String currentInstruction) throws Exception { if (traceMode) { String printableInstruction = StringUtil.sReplace("\r\n", " ", currentInstruction); printableInstruction = StringUtil.sReplace("\t", " ", printableInstruction); if (printableInstruction.length() > 147) { printableInstruction = printableInstruction.substring(0, 146) + "..."; } console.printMessage("\t\t>" + printableInstruction); } Statement stmt = connection.createStatement(); try { stmt.executeUpdate(currentInstruction); } catch (Exception e) { throw new Exception( "\r\n***ERROR RETURNED BY THE RDBMS : " + e.getMessage() + "\r\n***STATEMENT ON ERROR IS : " + currentInstruction + " " + pieceName, e); } finally { DbUtils.closeQuietly(stmt); } }
public static void updateRegionHappenings( Connection conn, DatabaseAccess access, int nationId, int happeningId, String happening, HappeningType type) throws SQLException, ExecutionException { String region1Happening = type.transformToRegion1Happening(happening); String region2Happening = type.transformToRegion2Happening(happening); List<Integer> regionIds = new ArrayList<Integer>(2); Matcher regions = Utils.REGION_PATTERN.matcher(happening); while (regions.find()) { regionIds.add( access .getRegionIdCache() .get(happening.substring(regions.start() + 2, regions.end() - 2))); } if (regionIds.size() == 0 && nationId > -1) { PreparedStatement select = conn.prepareStatement("SELECT region FROM assembly.nation WHERE id = ?"); select.setInt(1, nationId); ResultSet result = select.executeQuery(); if (result.next()) { regionIds.add(result.getInt(1)); } DbUtils.closeQuietly(result); DbUtils.closeQuietly(select); } PreparedStatement insert = conn.prepareStatement( "INSERT INTO assembly.regional_happenings (global_id, region, happening) VALUES (?, ?, ?)"); if (region1Happening != null && regionIds.size() > 0) { insert.setInt(1, happeningId); insert.setInt(2, regionIds.get(0)); insert.setString(3, region1Happening); insert.executeUpdate(); } if (region2Happening != null && regionIds.size() > 1) { insert.setInt(1, happeningId); insert.setInt(2, regionIds.get(1)); insert.setString(3, region2Happening); insert.executeUpdate(); } DbUtils.closeQuietly(insert); }
private void addEndorsement(Connection conn, int endorsed, int endorser) throws SQLException { PreparedStatement selectDuplicates = conn.prepareStatement( "SELECT endorsed FROM assembly.endorsements WHERE endorsed = ? AND endorser = ?"); selectDuplicates.setInt(1, endorsed); selectDuplicates.setInt(2, endorser); if (!selectDuplicates.executeQuery().next()) { PreparedStatement endorsements = conn.prepareStatement( "INSERT INTO assembly.endorsements (endorser, endorsed) VALUES (?, ?)"); endorsements.setInt(1, endorser); endorsements.setInt(2, endorsed); endorsements.executeUpdate(); DbUtils.closeQuietly(endorsements); } DbUtils.closeQuietly(selectDuplicates); }
@Override public List<Schedule> loadSchedules() throws ScheduleManagerException { logger.info("Loading all schedules from db."); Connection connection = getConnection(); QueryRunner runner = new QueryRunner(); ResultSetHandler<List<Schedule>> handler = new ScheduleResultHandler(); List<Schedule> schedules; try { schedules = runner.query(connection, SELECT_ALL_SCHEDULES, handler); } catch (SQLException e) { logger.error(SELECT_ALL_SCHEDULES + " failed."); DbUtils.closeQuietly(connection); throw new ScheduleManagerException("Loading schedules from db failed. ", e); } // filter the schedules for (Schedule sched : schedules) { if (!sched.updateTime()) { logger.info( "Schedule " + sched.getScheduleName() + " was scheduled before azkaban start, skipping it."); schedules.remove(sched); removeSchedule(sched); } else { try { updateNextExecTime(sched); } catch (Exception e) { DbUtils.closeQuietly(connection); throw new ScheduleManagerException("Update next execution time failed.", e); } logger.info("Schedule " + sched.getScheduleName() + " loaded and updated."); } } DbUtils.closeQuietly(connection); logger.info("Loaded " + schedules.size() + " schedules."); return schedules; }
public static Connection getConnection() { // 用于获得数据库连接的工具方法 try { DbUtils.loadDriver(DRIVER); // 加载驱动 conn = DriverManager.getConnection(URL, USERNAME, PASSWORD); // 建立连接 } catch (SQLException e) { e.printStackTrace(); } return conn; }
private void removeEndorsement(Connection conn, int endorsed, int endorser) throws SQLException { PreparedStatement endorsements = conn.prepareStatement( "DELETE FROM assembly.endorsements WHERE endorsed = ? AND endorser = ?"); endorsements.setInt(1, endorsed); endorsements.setInt(2, endorser); endorsements.executeUpdate(); DbUtils.closeQuietly(endorsements); }
private void resignFromWorldAssembly(Connection conn, int nationId, boolean banned) throws SQLException { PreparedStatement endorsements = conn.prepareStatement( "UPDATE assembly.nation SET wa_member = " + (banned ? "0" : "2") + " WHERE id = ?"); endorsements.setInt(1, nationId); endorsements.executeUpdate(); DbUtils.closeQuietly(endorsements); }
private synchronized void setRegionUpdateTime(Connection conn, int nationId, long timestamp) throws SQLException { final int region = getRegionOfNation(conn, nationId); if (region != -1) { PreparedStatement select = null, update = null, insert = null; ResultSet result = null; try { select = conn.prepareStatement( "SELECT id, start, end FROM assembly.region_updates WHERE region = ? AND start BETWEEN ? AND ?"); select.setInt(1, region); select.setLong(2, timestamp - Duration.standardHours(1).getMillis()); select.setLong(3, timestamp + Duration.standardHours(1).getMillis()); result = select.executeQuery(); if (result.next()) { final int id = result.getInt(1); final long start = result.getLong(2); final long end = result.getLong(3); update = conn.prepareStatement( "UPDATE assembly.region_updates SET start = ?, end = ? WHERE id = ?"); update.setLong(1, Math.min(start, timestamp)); update.setLong(2, Math.max(end, timestamp)); update.setInt(3, id); update.executeUpdate(); } else { insert = conn.prepareStatement( "INSERT INTO assembly.region_updates (region, start, end) VALUES (?, ?, ?)"); insert.setLong(1, region); insert.setLong(2, timestamp); insert.setLong(3, timestamp); insert.executeUpdate(); } } finally { DbUtils.closeQuietly(result); DbUtils.closeQuietly(insert); DbUtils.closeQuietly(update); DbUtils.closeQuietly(select); } } else { Logger.info("Can not set region update time for nation [" + nationId + "], unknown region!"); } }
/** 부분 커밋하면서 입력한다. ex) batchInsert(dataSource,"QQ", params, 1000); */ public static void batchInsert( DataSource dataSource, String tableName, List<Object[]> params, int commitInterval) { Connection conn = null; try { conn = dataSource.getConnection(); conn.setAutoCommit(false); QueryRunner runner = new QueryRunner(); StringAppender appender = new StringAppender(); appender.appendLine( "SELECT a.TABLE_NAME,a.COLUMN_NAME,COMMENTS,DATA_TYPE,DATA_LENGTH,DATA_PRECISION,DATA_SCALE"); appender.appendLine("FROM user_tab_columns a JOIN USER_COL_COMMENTS b"); appender.appendLine("ON a.COLUMN_NAME = b.COLUMN_NAME AND a.TABLE_NAME = b.TABLE_NAME"); appender.appendLine("WHERE a.TABLE_NAME = ? "); appender.appendLine("ORDER BY a.TABLE_NAME, COLUMN_ID "); List<String> columnNames = Lists.newArrayList(); // 걍 한번 쓸거라 일케 함 List<Map<String, Object>> result = runner.query(conn, appender.toString(), LIST_MAP_HANDLER, tableName); for (Map<String, Object> value : result) { columnNames.add(value.get("COLUMN_NAME").toString()); } String sql = "INSERT INTO " + tableName + " (" + StringUtil.join(columnNames, ",") + ") values (" + StringUtil.iterateStr("?", ",", columnNames.size()) + ")"; List<List<Object[]>> splited = CollectionUtil.splitBySize(params, commitInterval); for (List<Object[]> inputList : splited) { Object[][] convertedList = new Object[inputList.size()][]; for (int i = 0; i < convertedList.length; i++) { Object[] input = inputList.get(i); Object[] converted = new Object[input.length]; for (int j = 0; j < input.length; j++) { converted[j] = CONVERTER.convert(input[j]); } convertedList[i] = converted; } runner.batch(conn, sql, convertedList); conn.commit(); } } catch (SQLException e) { throw new SQLRuntimeException(e); } finally { DbUtils.closeQuietly(conn); } }
/** * 批处理 * * @throws Exception */ @Test public void testBatch() throws Exception { String sql = "insert into admin (userName,pwd) values(?,?)"; conn = DbUtil.getConnection(); QueryRunner qr = new QueryRunner(); // 批量插入 qr.batch(conn, sql, new Object[][] {{"jack1", "888"}, {"jack2", "999"}}); // 关闭 DbUtils.close(conn); }
public static void main(String[] args) { Connection conn = null; try { String schema = args[0]; String table = args[1]; String fullClassName = args[2]; conn = DbConnection.getConnection(); DtoCreater fks = new DtoCreater(conn, schema, table); fks.execute(schema, table, fullClassName); } catch (Exception e) { DbUtils.rollbackAndCloseQuietly(conn); return; } finally { DbUtils.closeQuietly(conn); } }
private Connection getConnection() throws ScheduleManagerException { Connection connection = null; try { connection = dataSource.getConnection(); } catch (Exception e) { DbUtils.closeQuietly(connection); throw new ScheduleManagerException("Error getting DB connection.", e); } return connection; }