Description
Load and run a SQL script.
License
Open Source License
Parameter
| Parameter | Description |
|---|
| setupScriptPath | Full path or relative path to the SQL script filename |
Exception
| Parameter | Description |
|---|
| FileNotFoundException | if file not found |
| IOException | if any other IO error occurs |
| SQLException | if any unexpected database problem |
Declaration
private static void runSetupScript(final String setupScriptPath)
throws FileNotFoundException, IOException, SQLException
Method Source Code
//package com.java2s;
/**//from ww w . j av a 2s . c om
* Java Settlers - An online multiplayer version of the game Settlers of Catan
* Copyright (C) 2003 Robert S. Thomas <thomas@infolab.northwestern.edu>
* Portions of this file Copyright (C) 2009-2010,2012 Jeremy D Monin <jeremy@nand.net>
*
* 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/>.
*
* The maintainer of this program can be reached at jsettlers@nand.net
**/
import java.io.BufferedReader;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
public class Main {
/**
* The db driver instance, if we dynamically loaded its JAR.
* Otherwise null, use {@link #dbURL} to connect instead.
*<P>
* Used because {@link DriverManager#registerDriver(Driver)} won't work
* if the classloader is different, which it will be for dynamic loading.
*<P>
* Set in {@link #initialize(String, String, Properties)}.
* Used in {@link #connect(String, String, String)}.
* @since 1.1.15
*/
private static Driver driverinstance = null;
/**
* db connection, or <tt>null</tt> if never initialized or if cleaned up for shutdown.
* If this is non-null but closed, most queries will try to recreate it via {@link #checkConnection()}.
* Set in {@link #connect(String, String, String)}, based on the {@link #dbURL}
* from {@link #initialize(String, String, Properties)}.
* Cleared in {@link #cleanup(boolean) cleanup(true)}.
*/
private static Connection connection = null;
/**
* Retain the URL (default, or passed via props to {@link #initialize(String, String, Properties)}).
* Used in {@link #connect(String, String, String)}.
*<P>
* If {@link #driverinstance} != null, go through it to connect to dbURL.
* @since 1.1.09
*/
private static String dbURL = null;
/**
* This flag indicates that the connection should be valid, yet the last
* operation failed. Methods will attempt to reconnect prior to their
* operation if this is set.
*/
private static boolean errorCondition = false;
/** Cached username used when reconnecting on error */
private static String userName;
/** Cached password used when reconnecting on error */
private static String password;
private static String CREATE_ACCOUNT_COMMAND = "INSERT INTO users VALUES (?,?,?,?,?);";
private static String RECORD_LOGIN_COMMAND = "INSERT INTO logins VALUES (?,?,?);";
private static String USER_PASSWORD_QUERY = "SELECT password FROM users WHERE ( users.nickname = ? );";
private static String HOST_QUERY = "SELECT nickname FROM users WHERE ( users.host = ? );";
private static String LASTLOGIN_UPDATE = "UPDATE users SET lastlogin = ? WHERE nickname = ? ;";
private static String SAVE_GAME_COMMAND = "INSERT INTO games VALUES (?,?,?,?,?,?,?,?,?,?);";
private static String ROBOT_PARAMS_QUERY = "SELECT * FROM robotparams WHERE robotname = ?;";
private static PreparedStatement createAccountCommand = null;
private static PreparedStatement recordLoginCommand = null;
private static PreparedStatement userPasswordQuery = null;
private static PreparedStatement hostQuery = null;
private static PreparedStatement lastloginUpdate = null;
private static PreparedStatement saveGameCommand = null;
/** Query all robot parameters for a bot name; {@link #ROBOT_PARAMS_QUERY}.
* Used in {@link #retrieveRobotParams(String)}.
*/
private static PreparedStatement robotParamsQuery = null;
/**
* Load and run a SQL script.
* Typically DDL commands to create or alter tables, indexes, etc.
* @param setupScriptPath Full path or relative path to the SQL script filename
* @throws FileNotFoundException if file not found
* @throws IOException if any other IO error occurs
* @throws SQLException if any unexpected database problem
* @since 1.1.15
*/
private static void runSetupScript(final String setupScriptPath)
throws FileNotFoundException, IOException, SQLException {
if (!checkConnection())
return; // also may throw SQLException
final boolean isSqlite = (dbURL.startsWith("jdbc:sqlite:"));
FileReader fr = new FileReader(setupScriptPath);
BufferedReader br = new BufferedReader(fr);
List<String> sqls = new ArrayList<String>();
// Read 1 line at a time, with continuations; build a list
try {
StringBuilder sb = new StringBuilder();
for (String nextLine = br.readLine(); nextLine != null; nextLine = br
.readLine()) {
// Reminder: java String.trim removes ascii whitespace (including tabs) but not unicode whitespace.
// Character.isWhitespace is true for both ascii and unicode whitespace, except non-breaking spaces.
if ((nextLine.length() == 0)
|| (nextLine.trim().length() == 0))
continue; // <-- skip empty lines --
if (nextLine.startsWith("--"))
continue; // <-- skip comment lines with no leading whitespace --
if (isSqlite && nextLine.toLowerCase().startsWith("use "))
continue; // <-- sqlite doesn't support "USE"
// If starts with whitespace, append it to sb (continue previous line).
// Otherwise, add previous sb to the sqls list, and start a new sb containing nextLine.
if (Character.isWhitespace(nextLine.codePointAt(0))) {
if (sb.length() > 0)
sb.append("\n"); // previous line's readLine doesn't include the trailing \n
} else {
sqls.add(sb.toString());
sb.delete(0, sb.length());
}
sb.append(nextLine);
}
// don't forget the last command
sqls.add(sb.toString());
// done reading the file
try {
br.close();
} catch (IOException eclose) {
}
try {
fr.close();
} catch (IOException eclose) {
}
} catch (IOException e) {
try {
br.close();
} catch (IOException eclose) {
}
try {
fr.close();
} catch (IOException eclose) {
}
throw e;
}
// No errors: Run the built list of SQLs
for (String sql : sqls) {
if (sql.trim().length() == 0)
continue;
Statement cmd = connection.createStatement();
cmd.executeUpdate(sql);
cmd.close();
}
}
/**
* Checks if connection is supposed to be present and attempts to reconnect
* if there was previously an error. Reconnecting closes the current
* {@link #connection}, opens a new one, and re-initializes the prepared statements.
*
* @return true if the connection is established upon return
*/
private static boolean checkConnection() throws SQLException {
if (connection != null) {
try {
return (!errorCondition)
|| connect(userName, password, null);
} catch (IOException ioe) {
// will not occur, connect script is null
return false;
}
}
return false;
}
/**
* Opens a new connection and initializes the prepared statements.
* {@link #initialize(String, String, Properties)} and {@link #checkConnection()} use this to get ready.
* Uses {@link #dbURL} and {@link #driverinstance}.
*<P>
* If <tt>setupScriptPath</tt> != null, it will be ran before preparing statements.
* That way, it can create tables used by the statements.
*
* @param user DB username
* @param pswd DB user password
* @param setupScriptPath Full path or relative path to SQL script to run at connect, or null
* @throws IOException if <tt>setupScriptPath</tt> wasn't found, or if any other IO error occurs reading the script
* @throws SQLException if any connect error, missing table, or SQL error occurs
* @return true on success; will never return false, instead will throw a sqlexception
*/
private static boolean connect(final String user, final String pswd,
final String setupScriptPath) throws SQLException, IOException {
if (driverinstance == null) {
connection = DriverManager.getConnection(dbURL, user, pswd);
} else {
Properties props = new Properties();
props.put("user", user);
props.put("password", pswd);
connection = driverinstance.connect(dbURL, props);
}
errorCondition = false;
userName = user;
password = pswd;
if (setupScriptPath != null)
runSetupScript(setupScriptPath); // may throw IOException, SQLException
// prepare PreparedStatements for queries
createAccountCommand = connection
.prepareStatement(CREATE_ACCOUNT_COMMAND);
recordLoginCommand = connection
.prepareStatement(RECORD_LOGIN_COMMAND);
userPasswordQuery = connection
.prepareStatement(USER_PASSWORD_QUERY);
hostQuery = connection.prepareStatement(HOST_QUERY);
lastloginUpdate = connection.prepareStatement(LASTLOGIN_UPDATE);
saveGameCommand = connection.prepareStatement(SAVE_GAME_COMMAND);
robotParamsQuery = connection.prepareStatement(ROBOT_PARAMS_QUERY);
return true;
}
}
Related
- getSQLiteConnection()
- getUniqLabels()
- modify(String sql)
- query(String sql)
- removeLabel()