/*
|
* BaseLink - Generic object relational mapping
|
* Copyright (C) 2024 Ulrich Hilger, http://uhilger.de
|
*
|
* This program is free software: you can redistribute it and/or modify
|
* it under the terms of the GNU General Public License as published by
|
* the Free Software Foundation, either version 3 of the License, or
|
* (at your option) any later version.
|
*
|
* This program is distributed in the hope that it will be useful,
|
* but WITHOUT ANY WARRANTY; without even the implied warranty of
|
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
|
* GNU General Public License for more details.
|
*
|
* You should have received a copy of the GNU General Public License
|
* along with this program. If not, see http://www.gnu.org/licenses/
|
*/
|
package de.uhilger.baselink;
|
|
import java.sql.Connection;
|
import java.sql.PreparedStatement;
|
import java.sql.SQLException;
|
import java.sql.Statement;
|
import java.util.List;
|
import java.util.Map;
|
import java.util.logging.Level;
|
import java.util.logging.Logger;
|
|
/**
|
* Methoden zum Ausfuehren von SQL-Skripten
|
*
|
* @author Copyright (c) Ulrich Hilger, <a href="http://uhilger.de">http://uhilger.de</a>
|
* @author Published under the terms and conditions of
|
* the <a href="http://www.gnu.org/licenses/" target="_blank">GNU General Public License</a>
|
* @version 1, January 22, 2024
|
*/
|
public class Script extends DBActor {
|
private static final Logger logger = Logger.getLogger(Script.class.getName());
|
|
public Script(PersistenceManager pm) {
|
this.pm = pm;
|
}
|
/**
|
* Execute an SQL statement and return keys generated in the database
|
* @param sql the statement to execute
|
* @return a list of generated keys
|
*/
|
public List<Map<String, Object>> executeWithKeys(String sql) {
|
List<Map<String, Object>> keys = null;
|
Connection c = null;
|
try {
|
c = pm.getConnection();
|
keys = executeWithKeys(c, sql);
|
c.close();
|
c = null;
|
} catch (SQLException ex) {
|
logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
|
} finally {
|
pm.closeConnectionFinally(c);
|
}
|
return keys;
|
}
|
|
/**
|
* Execute an SQL statement and return keys generated in the database
|
* @param c database connection to use
|
* @param sql the statement to execute
|
* @return a list of generated keys
|
*/
|
public List<Map<String, Object>> executeWithKeys(Connection c, String sql) {
|
List<Map<String, Object>> keys = null;
|
Statement s = null;
|
try {
|
s = c.createStatement();
|
s.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
|
keys = pm.toList(s.getGeneratedKeys());
|
s.close();
|
s = null;
|
} catch (SQLException ex) {
|
logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
|
} finally {
|
pm.closeStatementFinally(s);
|
}
|
return keys;
|
}
|
|
/**
|
* Execute an SQL statement
|
* @param sql the statement to execute
|
* @return the number of records affected by this statement or -1 if none
|
*/
|
public int execute(String sql) {
|
int numRows = -1;
|
Connection c = null;
|
try {
|
c = pm.getConnection();
|
numRows = execute(c, sql);
|
c.close();
|
c = null;
|
} catch (SQLException ex) {
|
logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
|
} finally {
|
pm.closeConnectionFinally(c);
|
}
|
return numRows;
|
}
|
|
/**
|
* Execute an SQL statement
|
* @param c database connection to use
|
* @param sql the statement to execute
|
* @return the number of records affected by this statement or -1 if none
|
*/
|
public int execute(Connection c, String sql) {
|
int numRows = -1;
|
Statement s = null;
|
try {
|
s = c.createStatement();
|
numRows = s.executeUpdate(sql);
|
s.close();
|
s = null;
|
} catch (SQLException ex) {
|
logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
|
} finally {
|
pm.closeStatementFinally(s);
|
}
|
return numRows;
|
}
|
|
/**
|
* Execute an SQL statement
|
* @param sql the SQL string with ? at the position of params
|
* @param params list of parameters in the order they appear in the SQL string
|
* @return the number of records affected by this statement or -1 if none
|
*/
|
public int execute(String sql, Object... params) {
|
int numRows = -1;
|
Connection c = null;
|
try {
|
c = pm.getConnection();
|
numRows = execute(c, sql, params);
|
c.close();
|
c = null;
|
} catch (SQLException ex) {
|
logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
|
} finally {
|
pm.closeConnectionFinally(c);
|
}
|
return numRows;
|
}
|
|
/**
|
* Execute an SQL statement
|
* @param c database connection to use
|
* @param sql the SQL string with ? at the position of params
|
* @param params list of parameters in the order they appear in the SQL string
|
* @return the number of records affected by this statement or -1 if none
|
*/
|
public int execute(Connection c, String sql, Object... params) {
|
int numRows = -1;
|
Statement s = null;
|
try {
|
//s = c.createStatement();
|
s = pm.buildQuery(c, sql, params);
|
if(s != null && s instanceof PreparedStatement) {
|
try (PreparedStatement ps = (PreparedStatement) s) {
|
numRows = ps.executeUpdate();
|
}
|
}
|
s = null;
|
} catch (Exception ex) {
|
logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
|
} finally {
|
pm.closeStatementFinally(s);
|
}
|
return numRows;
|
}
|
|
/**
|
* Execute an SQL script
|
* @param sqlScript the SQL script to execute
|
* @return an array of row counts referring to the number of affected rows of
|
* each sql statement in the script in the order of SQL commands in the script
|
* Statement.EXECUTE_FAILED indicates failure of single steps in the script
|
* Statement.SUCCESS_NO_INFO indicates successful execution without information
|
* about the number of affected rows
|
*/
|
public int[] executeScript(String sqlScript) {
|
int[] ergebnisse = null;
|
Connection c = null;
|
Statement s = null;
|
try {
|
c = pm.getConnection();
|
s = c.createStatement();
|
String[] sqlKommandos = sqlScript.split(";");
|
for (String sqlKommando : sqlKommandos) {
|
s.addBatch(sqlKommando);
|
}
|
ergebnisse = s.executeBatch();
|
} catch(SQLException ex) {
|
logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
|
} finally {
|
pm.closeStatementFinally(s);
|
pm.closeConnectionFinally(c);
|
}
|
return ergebnisse;
|
}
|
|
/**
|
* Execute an SQL script
|
* @param c the Connection object to use
|
* @param sqlScript the SQL script to execute
|
* @return an array of row counts referring to the number of affected rows of
|
* each sql statement in the script in the order of SQL commands in the script
|
* Statement.EXECUTE_FAILED indicates failure of single steps in the script
|
* Statement.SUCCESS_NO_INFO indicates successful execution without information
|
* about the number of affected rows
|
*/
|
public int[] executeScript(Connection c, String sqlScript) {
|
int[] ergebnisse = null;
|
Statement s = null;
|
try {
|
s = c.createStatement();
|
String[] sqlKommandos = sqlScript.split(";");
|
for (String sqlKommando : sqlKommandos) {
|
s.addBatch(sqlKommando);
|
}
|
ergebnisse = s.executeBatch();
|
} catch(SQLException ex) {
|
logger.log(Level.SEVERE, ex.getLocalizedMessage(), ex);
|
} finally {
|
pm.closeStatementFinally(s);
|
}
|
return ergebnisse;
|
}
|
|
}
|
