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