snaq.db.SQLUpdate Maven / Gradle / Ivy
Show all versions of dbpool Show documentation
/*
---------------------------------------------------------------------------
DBPool : Java Database Connection Pooling
* Usage: java snaq.db.SQLUpdate <poolnames> <input file> [<separator>]
*
* where {@code pool} is the name of the connection pool as defined in
* the dbpool.properties file, {@code input file} is the name of the text
* file containing the SQL statements to be issued to the defined database,
* and {@code separator} is an optional parameter to specify a delimiter
* for the SQL statements in the file. If the separator is not specified then
* each line of the file is assumed to be a separate statement.
*
* Note: comments are allowed in the input file by starting the line with
* either "#" or "--".
*
* @see snaq.db.ConnectionPoolManager
* @author Giles Winstanley
*/
public class SQLUpdate implements AutoCloseable
{
/** SLF4J shared instance for writing log entries. */
protected static final Logger logger = LoggerFactory.getLogger(SQLUpdate.class);
/** Pool manager for defining database connections. */
private ConnectionPoolManager cpm;
/** Database connection for issuing SQL statements. */
private Connection con;
/** Statement to use for executing SQL. */
private Statement statement;
public SQLUpdate(String poolname) throws IOException, SQLException
{
cpm = ConnectionPoolManager.getInstance();
openConnection(poolname);
}
public SQLUpdate(Connection con) throws SQLException
{
openConnection(con);
}
/**
* Opens a database connection to create a statement for SQL updates.
* @param con connection to use
*/
private void openConnection(Connection con) throws SQLException
{
if (con == null || con.isClosed())
throw new IllegalArgumentException("Please specify a valid connection");
try
{
this.con = con;
this.statement = con.createStatement();
}
catch (SQLException sqlx)
{
try { statement.close(); }
catch (SQLException sqlx2) {}
try { con.close(); }
catch (SQLException sqlx2) {}
}
}
/**
* Opens a database connection using a specified connection pool
* to create a statement for SQL updates.
* @param poolname name of the connection pool from which to get a connection
*/
private void openConnection(String poolname) throws SQLException
{
if (poolname == null || poolname.equals(""))
throw new IllegalArgumentException("Please specify the name of a defined connection pool");
try
{
this.con = cpm.getConnection(poolname);
this.statement = con.createStatement();
}
catch (SQLException sqlx)
{
try { statement.close(); }
catch (SQLException sqlx2) {}
try { con.close(); }
catch (SQLException sqlx2) {}
}
}
/**
* Closes the current database connection.
*/
@Override
public void close()
{
try { statement.close(); }
catch (SQLException sqlx) { sqlx.printStackTrace(); }
try { con.close(); }
catch (SQLException sqlx) { sqlx.printStackTrace(); }
if (cpm != null)
cpm.release();
}
/**
* Issues a statement to the database.
* @param sql SQL command to issue
* @throws SQLException
*/
public void doStatement(String sql) throws SQLException
{
try
{
logger.trace(sql);
statement.executeUpdate(sql);
}
catch (SQLException sqlx)
{
logger.info(sqlx.getMessage(), sqlx);
throw sqlx;
}
}
/**
* Loads a text file into a string.
* @param f {@link File} containing text to load
* @return {@link String} containing the text loaded from the specified file
* @throws IOException
*/
public final static String loadTextFile(File f) throws IOException
{
try(FileInputStream fis = new FileInputStream(f);
ByteArrayOutputStream bao = new ByteArrayOutputStream())
{
byte[] b = new byte[4096];
int n;
while ((n = fis.read(b)) != -1)
bao.write(b, 0, n);
return new String(bao.toByteArray());
}
}
/**
* Splits the specified text input into separate SQL statements.
* @param text string containing the text to be processed
* @param separator string specifying the separator between SQL statements
* @return {@link String} array containing the SQL statements parsed
*/
public static String[] splitSQL(String text, String separator)
{
// Create list to hold SQL statements.
List list = new ArrayList<>();
if (separator == null)
{
StringTokenizer st = new StringTokenizer(text, "\n\r");
while (st.hasMoreTokens())
{
String token = st.nextToken().trim();
if (!token.startsWith("#") && !"".equals(token))
list.add(token);
}
}
else
{
StringBuilder sb = new StringBuilder();
StringTokenizer st = new StringTokenizer(text, "\n\r");
while (st.hasMoreTokens())
{
// Get next line.
String line = st.nextToken();
// If line is a comment...ignore it.
if (line.startsWith("#") || line.startsWith("--"))
{
sb.setLength(0);
}
else
{
int pos = line.indexOf(separator);
if (pos >= 0)
{
sb.append(line.substring(0, pos));
list.add(sb.toString());
sb.setLength(0);
}
else
sb.append(line);
}
}
}
return list.toArray(new String[0]);
}
public static void main(String args[]) throws Exception
{
String cn = SQLUpdate.class.getName();
if (args == null || args.length < 2)
{
System.out.println("Usage: java " + cn + " []");
System.exit(0);
}
String poolName = args[0];
String file = args[1];
String separator = args.length < 3 ? null : args[2];
if (separator != null)
System.out.println("Separator: " + separator);
// Load file.
String contents = null;
try
{
contents = loadTextFile(new File(file));
}
catch (IOException iox)
{
System.out.println("I/O error with file " + file);
iox.printStackTrace();
System.exit(1);
}
// Split loaded text into SQL statements.
String[] sql = splitSQL(contents, separator);
// Open database connection, issue SQL, then close connection.
try(SQLUpdate sqlUpdate = new SQLUpdate(poolName))
{
for (String s : sql)
sqlUpdate.doStatement(s);
}
catch (IOException iox)
{
System.err.println("Unable to create instance of " + cn);
iox.printStackTrace();
System.exit(1);
}
catch (SQLException sqlx)
{
sqlx.printStackTrace();
}
System.out.println();
}
}