snaq.db.SQLUpdate Maven / Gradle / Ivy
/*
---------------------------------------------------------------------------
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
{
/** Apache Commons Logging shared instance for writing log entries. */
protected static final Log logger = LogFactory.getLog(SQLUpdate.class);
/** Platform line-separator. */
private final static String LSEP = System.getProperty("line.separator");
/** 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 db) throws IOException
{
cpm = ConnectionPoolManager.getInstance();
}
/**
* Opens a database connection using a specified connection pool.
* @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 SQLException("Please specify the name of a defined connection pool");
try
{
con = cpm.getConnection(poolname);
statement = con.createStatement();
}
catch (SQLException sqlx)
{
try { statement.close(); }
catch (SQLException sqlx2) {}
try { con.close(); }
catch (SQLException sqlx2) {}
}
}
/**
* Closes the current database connection.
*/
private void closeConnection()
{
try { statement.close(); }
catch (SQLException sqlx) { sqlx.printStackTrace(); }
try { con.close(); }
catch (SQLException sqlx) { sqlx.printStackTrace(); }
cpm.release();
}
/**
* Issues a statement to the database.
*/
private void doStatement(String sql) throws SQLException
{
try
{
logger.debug("SQL: " + 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
*/
public static String loadTextFile(File f) throws IOException
{
FileInputStream fis = null;
try
{
ByteArrayOutputStream bao = new ByteArrayOutputStream();
fis = new FileInputStream(f);
byte[] b = new byte[4096];
int n;
while ((n = fis.read(b)) != -1)
bao.write(b, 0, n);
return new String(bao.toByteArray());
}
finally
{
fis.close();
}
}
/**
* 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("#") && !token.equals(""))
list.add(token);
}
}
else
{
StringBuffer sb = new StringBuffer();
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]);
}
/**
* Allows command-line issuing of SQL statements to a database defined
* in the pool manager properties file.
*/
public static void main(String args[])
{
String cn = SQLUpdate.class.getName();
if (args == null || args.length < 2)
{
System.out.println("Usage: java " + cn + " []");
System.exit(0);
}
String db = 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.
SQLUpdate sqlUpdate = null;
try
{
sqlUpdate = new SQLUpdate(db);
sqlUpdate.openConnection(db);
for (int i = 0; i < sql.length; i++)
sqlUpdate.doStatement(sql[i]);
}
catch (IOException iox)
{
System.err.println("Unable to create instance of " + cn);
iox.printStackTrace();
System.exit(1);
}
catch (SQLException sqlx)
{
sqlx.printStackTrace();
}
finally
{
sqlUpdate.closeConnection();
}
System.out.println();
}
}