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;
|
|
/**
|
*
|
* @author Ulrich Hilger
|
*/
|
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;
|
}
|
|
}
|
