プログラミング中、あった問題 12
・print_todo機能をまとめました。
実行方法：encodeの部分がSQLの部分に遷移しました。
・edit_todo.jspにもらった値をを二回でエンコードしました。
UserBeansから戻る値の部分を修正しました。
public String getId_edit() {
return id;
}
public void setId_edit(String id) {
this.id = id;
}
public String getPassword_edit() {
return password;
}
public void setPassword_edit(String password) {
this.password = password;
}
・Encode機能を共通関数にします。
package user_todo;
public class CommonBeans {
public static String EscapeHTMLCharacter(String original) {
String newString = original;
newString = newString.replaceAll("(?i)(&)", "&");
newString = newString.replaceAll("(?i)(')", "'");
newString = newString.replaceAll("(?i)(\")", """);
newString = newString.replaceAll("(?i)(<)", "<");
newString = newString.replaceAll("(?i)(>)", ">");
return newString;
}
public static String EscapeSQLCharacter(String original) {
String newString = original;
newString = newString.replaceAll("(?i)(')", "''");
return newString;
}
}
使い方：CommonBeans.EscapeHTMLCharacter(userid)；
・MVC
モデル(Model)：データを保存する場所です。
ビュー(View)：結果が表示されます。
コントローラ(Controller)：データを処理する場所です。
・UserServletsとTodoServletを作成しました。
BeansのSQL指令をServletに遷移しました。
UserServlet
public class UserServlet {
Timestamp timestamp = new Timestamp(System.currentTimeMillis());
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String time = sdf.format(timestamp);
// Add New Account
public static String add_user(String id, String password) {
id = EscapeCharacter.EscapeSQLCharacter(id);
password = EscapeCharacter.EscapeSQLCharacter(password);
String sql = "insert into user(user_id, password) values ('" + id + "','" + password + "')";
return sql;
}
// Delete Account
public static String delete_user(String id) {
id = EscapeCharacter.EscapeSQLCharacter(id);
String sql = "delete from user where user_id=('" + id + "')";
return sql;
}
// Delete user todo
public static String delete_user_todo(String id) {
id = EscapeCharacter.EscapeSQLCharacter(id);
String sql = "delete from todo where user_id=('" + id + "')";
return sql;
}
// Change Password
public static String change_password(String id, String password) {
id = EscapeCharacter.EscapeSQLCharacter(id);
password = EscapeCharacter.EscapeSQLCharacter(password);
String sql = "UPDATE user SET password ='" + password + "' WHERE user_id = '" + id + "'";
return sql;
}
// Select user
public static StringBuffer select_user(String id,String password) {
id = EscapeCharacter.EscapeSQLCharacter(id);
password = EscapeCharacter.EscapeSQLCharacter(password);
StringBuffer sql = new StringBuffer();
sql.append("select * from user where user_id = '" + id + "' and password = '" + password + "'");
return sql;
}
// Account Login Verification
public static StringBuffer verification_user(String id) {
StringBuffer sql = new StringBuffer();
sql.append("SELECT `todo_id`,`todo_text`,`finished`,`upload_date` FROM `todo` WHERE user_id = '" + id + "'");
return sql;
}
// Select user_todo
public static StringBuffer select_todo(String id) {
StringBuffer sql = new StringBuffer();
id = EscapeCharacter.EscapeSQLCharacter(id);
sql.append("SELECT `todo_id`,`todo_text`,`finished`,`upload_date` FROM `todo` WHERE user_id = '" + id + "'");
return sql;
}
// Date_type_convert
public static String date_convert(String upload_date) {
upload_date = upload_date.replaceFirst("(?i)(-)", "年");
upload_date = upload_date.replaceFirst("(?i)(-)", "月");
upload_date = upload_date.replaceFirst("(?i)( )", "日");
upload_date = upload_date.substring(0, 16);
int YEAR = Integer.parseInt(upload_date.substring(0, 4));
int MONTH = Integer.parseInt(upload_date.substring(5, 7));
int DATE = Integer.parseInt(upload_date.substring(8, 10));
MONTH = MONTH - 1;
DATE = DATE - 1;
String week = new String[7];
week[0] = "日";
week[1] = "月";
week[2] = "火";
week[3] = "水";
week[4] = "木";
week[5] = "金";
week[6] = "土";
Calendar YEAR_MONTH_DATE = Calendar.getInstance();
YEAR_MONTH_DATE.set(Calendar.YEAR, YEAR);
YEAR_MONTH_DATE.set(Calendar.MONTH, MONTH);
YEAR_MONTH_DATE.set(Calendar.DAY_OF_MONTH, DATE);
int DAY_OF_WEEK = YEAR_MONTH_DATE.get(Calendar.DAY_OF_WEEK);
upload_date = upload_date.substring(5, 11) + "(" + week[DAY_OF_WEEK] + ")" + upload_date.substring(11, 16);
return upload_date;
}
TodoServlet
public class TodoServlet {
Timestamp timestamp = new Timestamp(System.currentTimeMillis());
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String time = sdf.format(timestamp);
// Add Todo
public static String add_todo(String id, String data, String time) {
id = EscapeCharacter.EscapeSQLCharacter(id);
data = EscapeCharacter.EscapeSQLCharacter(data);
String sql = "INSERT INTO `company_db`.`todo` (`user_id`, `todo_text`, `finished`, `created_date`, `upload_date`) VALUES ('"
+ id + "', '" + data + "', '0', '" + time + "', '" + time + "')";
return sql;
}
// Delete Todo
public static String delete_todo(String todo_array) {
String todo_str = EscapeCharacter.EscapeSQLCharacter(todo_array);
String sql = "DELETE FROM `company_db`.`todo` WHERE `todo_id`='" + todo_str + "'";
return sql;
}
// UPDATE Todo
public static String edit_todo(String todoid, String data, String time) {
todoid = EscapeCharacter.EscapeSQLCharacter(todoid);
data = EscapeCharacter.EscapeSQLCharacter(data);
String sql = "UPDATE `todo` SET `todo_text` ='" + data + "',`upload_date` ='" + time + "' WHERE `todo_id` = '"
+ todoid + "'";
return sql;
}
// Select user_todo
public static StringBuffer select_todo(String id) {
StringBuffer sql = new StringBuffer();
id = EscapeCharacter.EscapeSQLCharacter(id);
sql.append("SELECT `todo_id`,`todo_text`,`finished`,`upload_date` FROM `todo` WHERE user_id = '" + id + "'");
return sql;
}
// Date_type_convert
public static String date_convert(String upload_date) {
upload_date = upload_date.replaceFirst("(?i)(-)", "年");
upload_date = upload_date.replaceFirst("(?i)(-)", "月");
upload_date = upload_date.replaceFirst("(?i)( )", "日");
upload_date = upload_date.substring(0, 16);
int YEAR = Integer.parseInt(upload_date.substring(0, 4));
int MONTH = Integer.parseInt(upload_date.substring(5, 7));
int DATE = Integer.parseInt(upload_date.substring(8, 10));
MONTH = MONTH - 1;
DATE = DATE - 1;
String week = new String[7];
week[0] = "日";
week[1] = "月";
week[2] = "火";
week[3] = "水";
week[4] = "木";
week[5] = "金";
week[6] = "土";
Calendar YEAR_MONTH_DATE = Calendar.getInstance();
YEAR_MONTH_DATE.set(Calendar.YEAR, YEAR);
YEAR_MONTH_DATE.set(Calendar.MONTH, MONTH);
YEAR_MONTH_DATE.set(Calendar.DAY_OF_MONTH, DATE);
int DAY_OF_WEEK = YEAR_MONTH_DATE.get(Calendar.DAY_OF_WEEK);
upload_date = upload_date.substring(5, 11) + "(" + week[DAY_OF_WEEK] + ")" + upload_date.substring(11, 16);
return upload_date;
}
