/**
* Read from a Mysql database named mvd which has a table called files and a field called name and
* another called contents. Yes, it's rigid but do you want to add yet more parameters to the
* MvdTool?
*
* @param file the name of the file (key of table)
* @param props database properties file
* @return a char array with database contents
* @throws Exception
*/
private static char[] readFromDatabase(String file, Properties props) throws Exception {
char[] data = null;
Connection conn = null;
ResultSet rs = null;
Statement stmt = null;
String name = (file.endsWith(".mvd")) ? file.substring(0, file.length() - 4) : file;
String username = (String) props.get("username");
String password = (String) props.get("password");
String url = (String) props.get("jdbc-url");
String jdbcClass = (String) props.get("jdbc-class");
String dbName = (String) props.get("mvd-db-name");
String nameField = (String) props.get("mvd-name-field");
String descField = (String) props.get("mvd-desc-field");
String dataField = (String) props.get("mvd-data-field");
String tableName = (String) props.get("mvd-table-name");
if (username == null) username = "******";
if (password == null) password = "******";
if (url == null) url = "jdbc:mysql://localhost:3306/";
if (jdbcClass == null) jdbcClass = "com.mysql.jdbc.Driver";
if (dbName == null) dbName = "mvd";
if (nameField == null) nameField = "name";
if (descField == null) descField = "description";
if (dataField == null) dataField = "data";
if (tableName == null) tableName = "works";
try {
Class.forName(jdbcClass).newInstance();
conn = DriverManager.getConnection(url, username, password);
stmt = conn.createStatement();
String query =
"select " + dataField + " from " + dbName + "." + tableName + " where " + nameField + "='"
+ name + "';";
rs = stmt.executeQuery(query);
if (rs.first()) {
String result = rs.getString(dataField);
data = new char[result.length()];
result.getChars(0, result.length(), data, 0);
} else throw new Exception("No results for: " + query);
} catch (Exception e) {
System.err.println(
"Error accessing database. Message="
+ e.getMessage()
+ " with password="******" and username="
+ username);
} finally {
if (rs != null) {
try {
rs.close();
} catch (SQLException sqlEx) {
} // ignore
rs = null;
}
if (stmt != null) {
try {
stmt.close();
} catch (SQLException sqlEx) {
} // ignore
stmt = null;
if (conn != null) {}
try {
conn.close();
} catch (Exception e) {
/* ignore close errors */
}
}
}
return data;
}
/**
* Write to a Mysql database named mvd which has a table called works with fields name,
* description and data.
*
* @param file the name of the file (key of table)
* @param description the description of the mvd
* @param data a String with the MVD data encoded in base64
* @param folderId id of the containing folder
* @param properties property file containing db connection
* @throws Exception
*/
private static void writeToDatabase(
String file, String data, String description, int folderId, Properties props)
throws Exception {
Connection conn = null;
ResultSet rs = null;
Statement stmt = null;
String username = (String) props.get("username");
String password = (String) props.get("password");
String url = (String) props.get("jdbc-url");
String jdbcClass = (String) props.get("jdbc-class");
String dbName = (String) props.get("mvd-db-name");
String nameField = (String) props.get("mvd-name-field");
String descField = (String) props.get("mvd-desc-field");
String dataField = (String) props.get("mvd-data-field");
String tableName = (String) props.get("mvd-table-name");
String folderField = (String) props.get("mvd-folder-field");
/* // debug
Set<Object> keys = props.keySet();
Iterator<Object> iter = keys.iterator();
while ( iter.hasNext() )
{
String key = (String) iter.next();
String value = (String)props.getProperty(key);
}
// end debug
*/
String name = (file.endsWith(".mvd")) ? file.substring(0, file.length() - 4) : file;
description = description.replace("\"", "\\\"");
if (username == null) username = "******";
if (password == null) password = "******";
if (url == null) url = "jdbc:mysql://localhost:3306/";
if (jdbcClass == null) jdbcClass = "com.mysql.jdbc.Driver";
if (dbName == null) dbName = "dv";
if (nameField == null) nameField = "name";
if (descField == null) descField = "description";
if (dataField == null) dataField = "data";
if (tableName == null) tableName = "works";
if (folderField == null) folderField = "folder_id";
try {
Class.forName(jdbcClass).newInstance();
conn = DriverManager.getConnection(url + dbName, username, password);
if (conn != null) {
stmt = conn.createStatement();
if (stmt != null) {
String query;
rs =
stmt.executeQuery(
"select * from "
+ dbName
+ "."
+ tableName
+ " where "
+ nameField
+ "=\""
+ name
+ "\"");
if (rs.first()) {
query =
"update "
+ dbName
+ "."
+ tableName
+ " set "
+ dbName
+ "."
+ tableName
+ "."
+ descField
+ "=\""
+ description
+ "\","
+ dbName
+ "."
+ tableName
+ "."
+ dataField
+ "=\""
+ data
+ "\","
+ folderField
+ "=\""
+ folderId
+ "\" where "
+ nameField
+ "=\""
+ name
+ "\"";
} else {
query =
"insert into "
+ dbName
+ "."
+ tableName
+ " ("
+ nameField
+ ","
+ descField
+ ","
+ dataField
+ ","
+ folderField
+ ") values(\""
+ name
+ "\",\""
+ description
+ "\",\""
+ data
+ "\",\""
+ folderId
+ "\");";
}
stmt = conn.createStatement();
stmt.executeUpdate(query);
}
conn.close();
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (rs != null) {
try {
rs.close();
} catch (SQLException sqlEx) {
} // ignore
rs = null;
}
if (stmt != null) {
try {
stmt.close();
} catch (SQLException sqlEx) {
} // ignore
stmt = null;
if (conn != null) {}
try {
conn.close();
} catch (Exception e) {
/* ignore close errors */
}
}
}
}
