drop table MUSER ; create table MUSER ( USERID CHAR(20), USERNAME TEXT, PASSWORD TEXT, MAILADDRESS VARCHAR(100), SEXKBN CHAR(1), CITYCD CHAR(2), primary key (USERID) ); drop table MUSERHOBBY ; create table MUSERHOBBY ( USERID CHAR(20), HOBBYCD CHAR(4), primary key (USERID,HOBBYCD) ); drop table MCITY ; create table MCITY( CITYCD CHAR(4), CITYNM CHAR(20), primary key (CITYCD) ); insert into MCITY values('01','東京都'); insert into MCITY values('02','神奈川県'); insert into MCITY values('03','千葉県'); insert into MCITY values('04','埼玉県'); drop table MHOBBY ; create table MHOBBY( HOBBYCD CHAR(4), HOBBYNM CHAR(20), primary key (HOBBYCD) ); insert into MHOBBY values('0001','読書'); insert into MHOBBY values('0002','料理'); insert into MHOBBY values('0003','ドライブ'); insert into MHOBBY values('0004','スポーツ');
package tutorial.user; import tutorial.common.DataDictionary; import tutorial.common.Factory; import tutorial.common.OptionsConstants; import wisdom.core.application.IRequestHandler; import wisdom.core.application.IRequestParser; import wisdomx.logic.formtemplate.AbstractFormObject; import wisdomx.ui.builder.IOptionsBuilder; import wisdomx.ui.object.Checkbox; import wisdomx.ui.object.Select; public class UserFo extends AbstractFormObject { public static final long serialVersionUID = 1L; private String userid = ""; private String username = ""; private String password = ""; private String mailaddress = ""; private Select sexkbn = null; private Select citycd = null; private Checkbox hobbycd = null; public UserFo(IRequestHandler rh) throws Exception { IOptionsBuilder ob = Factory.getOptionsBuilder(rh); sexkbn = ob.build(OptionsConstants.OC_SEXKBN, DataDictionary.SEXKBN.getId()); citycd = ob.build(OptionsConstants.OC_CITYCD, DataDictionary.CITYCD.getId()); hobbycd = new Checkbox(DataDictionary.HOBBYCD.getId(), ob.build(OptionsConstants.OC_HOBBYCD, DataDictionary.HOBBYCD.getId()).getOptions() ); } @Override public void set(IRequestHandler rh, IRequestParser rp) throws Exception { clearMessageList(); userid = rp.getString("userid"); username = rp.getString("username"); password = rp.getString("password"); mailaddress = rp.getString("mailaddress"); sexkbn.setSelected(rp.getString("sexkbn","")); citycd.setSelected(rp.getString("citycd")); hobbycd.clearChecked(); hobbycd.setChecked(rp.getStringArray("hobbycd")); } public String getUserid() { return userid; } public void setUserid(String userid) { this.userid = userid; } public String getUsername() { return username; } public void setUsername(String username) { this.username = username; } public String getPassword() { return password; } public void setPassword(String password) { this.password = password; } public String getMailaddress() { return mailaddress; } public void setMailaddress(String mailaddress) { this.mailaddress = mailaddress; } public Select getSexkbn() { return sexkbn; } public void setSexkbn(Select sexkbn) { this.sexkbn = sexkbn; } public Select getCitycd() { return citycd; } public void setCitycd(Select citycd) { this.citycd = citycd; } public Checkbox getHobbycd() { return hobbycd; } public void setHobbycd(Checkbox hobbycd) { this.hobbycd = hobbycd; } }
package tutorial.user; import java.sql.PreparedStatement; import java.sql.ResultSet; import wisdom.core.application.IRequestHandler; import wisdomx.logic.formtemplate.AbstractFormObject; import wisdomx.logic.formtemplate.AbstractFormObjectGenerator; import wisdomx.logic.formtemplate.ParameterInputForm; public class UserFog extends AbstractFormObjectGenerator { public static final long serialVersionUID = 1L; public static final String SQL_SEL_MUSER = "select USERID,USERNAME,PASSWORD,MAILADDRESS,SEXKBN,CITYCD from MUSER where USERID = ? "; public static final String SQL_SEL_MUSERHOBBY = "select HOBBYCD from MUSERHOBBY where USERID = ? "; public UserFog(ParameterInputForm inputForm, IRequestHandler rh) throws Exception { super(inputForm, rh); } @Override protected AbstractFormObject _generate() throws Exception { UserFo fo = new UserFo(rh); fo.setUserid(inputForm.getString("userid")); fo.toUpdate(); PreparedStatement ps = rh.getConnection().prepareStatement(SQL_SEL_MUSER); ps.setString(1, fo.getUserid()); ResultSet rs = ps.executeQuery(); if (rs.next()) { fo.setUserid(rs.getString(1).trim()); fo.setUsername(rs.getString(2).trim()); fo.setPassword(rs.getString(3).trim()); fo.setMailaddress(rs.getString(4).trim()); fo.getSexkbn().setSelected(rs.getString(5).trim()); fo.getCitycd().setSelected(rs.getString(6).trim()); } rs.close(); ps.close(); ps = rh.getConnection().prepareStatement(SQL_SEL_MUSERHOBBY); ps.setString(1, fo.getUserid()); rs = ps.executeQuery(); while (rs.next()) { fo.getHobbycd().setChecked(rs.getString(1).trim()); } rs.close(); ps.close(); return fo; } }
package tutorial.user; import java.sql.PreparedStatement; import java.sql.ResultSet; import wisdom.core.application.IRequestHandler; import wisdom.core.util.StringUtil; import wisdomx.logic.formtemplate.AbstractFormObject; import wisdomx.logic.formtemplate.AbstractFormObjectPersistance; import wisdomx.logic.formtemplate.StringChecker; import wisdomx.ui.object.Checkbox; import static tutorial.common.DataDictionary.*; public class UserFop extends AbstractFormObjectPersistance { public static final long serialVersionUID = 1L; public static final String SQL_INS_MUSER = "insert into MUSER (USERID,USERNAME,PASSWORD,MAILADDRESS,SEXKBN,CITYCD)values(?,?,?,?,?,?)"; public static final String SQL_INS_MUSERHOBBY = "insert into MUSERHOBBY (USERID,HOBBYCD)values(?,?)"; public static final String SQL_SEL_USER = "select USERID from MUSER where USERID = ?"; public static final String SQL_UPD_MUSER = "update MUSER set USERNAME = ? ,PASSWORD = ? ,MAILADDRESS = ?, SEXKBN = ? , CITYCD = ? where userid = ?"; public static final String SQL_DEL_MUSER = "delete from MUSER where userid = ?"; public static final String SQL_DEL_MUSERHOBBY = "delete from MUSERHOBBY where userid = ?"; private UserFo fo = null; public UserFop(AbstractFormObject formObject, IRequestHandler rh) throws Exception { super(formObject, rh); fo = (UserFo)formObject; } @Override protected void _cleanup() throws Exception {} @Override protected void _delete() throws Exception { PreparedStatement ps = rh.getConnection().prepareStatement(SQL_DEL_MUSER); ps.setString(1, fo.getUserid()); ps.executeUpdate(); ps.close(); deleteHobbdy(); } @Override protected void _insert() throws Exception { PreparedStatement ps = rh.getConnection().prepareStatement(SQL_INS_MUSER); int x = 1; ps.setString(x++, fo.getUserid()); ps.setString(x++, fo.getUsername()); ps.setString(x++, fo.getPassword()); ps.setString(x++, fo.getMailaddress()); ps.setString(x++, fo.getSexkbn().getSelected()); ps.setString(x++, fo.getCitycd().getSelected()); ps.executeUpdate(); ps.close(); insertHoddy(); } private void insertHoddy() throws Exception { PreparedStatement ps = rh.getConnection().prepareStatement(SQL_INS_MUSERHOBBY); Checkbox hobbycd = fo.getHobbycd().getCheckedOptions(); for (int i = 0 ; i < hobbycd.size(); i++ ) { int x = 1; ps.setString(x++, fo.getUserid()); ps.setString(x++, hobbycd.get(i).getKey()); ps.executeUpdate(); } ps.close(); } private void deleteHobbdy() throws Exception { PreparedStatement ps = rh.getConnection().prepareStatement(SQL_DEL_MUSERHOBBY); ps.setString(1, fo.getUserid()); ps.executeUpdate(); ps.close(); } @Override protected void _prepare(AbstractFormObject form) throws Exception {} @Override protected void _update() throws Exception { PreparedStatement ps = rh.getConnection().prepareStatement(SQL_UPD_MUSER); int x = 1; ps.setString(x++, fo.getUsername()); ps.setString(x++, fo.getPassword()); ps.setString(x++, fo.getMailaddress()); ps.setString(x++, fo.getSexkbn().getSelected()); ps.setString(x++, fo.getCitycd().getSelected()); ps.setString(x++, fo.getUserid()); ps.executeUpdate(); ps.close(); deleteHobbdy(); insertHoddy(); } @Override protected void _validate() throws Exception { StringChecker sc = new StringChecker(); int result = 0; result += sc.checkNotEmpty(USERID, fo.getUserid(),fo); result += sc.checkNotEmpty(USERNAME, fo.getUsername(),fo); result += sc.checkNotEmpty(PASSWORD, fo.getPassword(),fo); result += sc.checkNotEmpty(MAILADDRESS, fo.getMailaddress(),fo); result += sc.checkNotEmpty(SEXKBN, fo.getSexkbn().getSelected(),fo); result += sc.checkNotEmpty(CITYCD, fo.getCitycd().getSelected(),fo); if (fo.isInsert() && StringUtil.notEmpty(fo.getUserid())) { PreparedStatement ps = rh.getConnection().prepareStatement(SQL_SEL_USER); ps.setString(1, fo.getUserid()); ResultSet rs = ps.executeQuery(); if (rs.next()) { // USM0011E=入力されたユーザーIDはすでに登録されています。 fo.addErrorMessage(messageGenerator.getMessage("USM0011E")); } rs.close(); ps.close(); } } }
package tutorial.user; import java.sql.PreparedStatement; import tutorial.common.Factory; import wisdom.core.MessageList; import wisdom.core.application.AbstractRequestCommand; import wisdom.core.util.StringUtil; import wisdomx.logic.formtemplate.ParameterInputForm; import wisdomx.ui.builder.StatementBuilder; import wisdomx.ui.builder.StatementPageListBuilder; import wisdomx.ui.object.IPageList; public class UserMaintCmd extends AbstractRequestCommand { public static final String[] PAGES = new String[] {"/tutorial/user/userlist.jsp", "/tutorial/user/userentry.jsp", "/tutorial/user/userentryconfirm.jsp", "/tutorial/user/userdeleteconfirm.jsp", }; public static final String SQL_SEL_USER = " select " + "USERID," + "USERNAME," + "CASE SEXKBN WHEN '1' THEN '男性' WHEN '2' THEN '女性' END," + " ( select CITYNM from MCITY where MUSER.CITYCD = MCITY.CITYCD)" +" from MUSER" + " order by USERID "; private MessageList ml = null; private UserFo user = null; @Override protected void _execute() throws Exception { String op = rp.getString("op",""); ml = new MessageList(); if (StringUtil.isEmpty(op) || "list".equals(op)) { list(); } else if ("entry".equals(op)) { entry(); } else if ("update".equals(op)) { update(); } else if ("entryconfirm".equals(op)) { entryconfirm(); } else if ("deleteconfirm".equals(op)) { deleteconfirm(); } else if ("doentry".equals(op)) { doentry(); } else if ("dodelete".equals(op)) { dodelete(); } else if ("backentry".equals(op)) { backentry(); } rh.setAttribute(getName()+"#ml", ml); } private void list() throws Exception{ // 一覧表示するためのPreparedStatementを作成します。 PreparedStatement ps = new StatementBuilder(SQL_SEL_USER,rh.getConnection()).buildStatement(); // 現在表示中のページ番号を取得します。現在ページはhiddenパラメータを使用し、パラメータがない場合は一ページ目とします。 int page = rp.getInt("page",1); // 一覧ページのオブジェクトを作成します。表示する行数は20行を指定しています。 IPageList pageList = new StatementPageListBuilder(ps,Factory.getPageDecorator()).buildPerPageRows(5, page); // カテゴリーが無い場合はメッセージを表示します。 if (pageList.size() == 0) { // USM0010I=登録されているユーザーはいません。 ml.addMessage(messageGenerator.getMessage("USM0010I")); } //Requestへ一覧ページのオブジェクト、メッセージをsetAttributeして遷移先ページを指定します。 rh.setAttribute(getName()+"#pageList", pageList); setPage(PAGES[0]); } private void entry() throws Exception{ user = new UserFo(rh); rh.setSessionAttribute(getName()+"#user", user); setPage(PAGES[1]); } private void update() throws Exception{ ParameterInputForm pif = new ParameterInputForm(); pif.addString("userid", rp.getString("userid")); UserFog fog = new UserFog(pif,rh); rh.setSessionAttribute(getName()+"#user", fog.generate()); setPage(PAGES[1]); } private void entryconfirm() throws Exception{ user = (UserFo)rh.getSessionAttribute(getName()+"#user"); user.set(rh, rp); UserFop fop = new UserFop(user,rh); user = (UserFo)fop.validate(); if (user.isError()) { setPage(PAGES[1]); } else { setPage(PAGES[2]); } } private void deleteconfirm() throws Exception{ setPage(PAGES[3]); } private void doentry() throws Exception{ user = (UserFo)rh.getSessionAttribute(getName()+"#user"); UserFop fop = new UserFop(user,rh); fop.setIgnoreValidate(true); user = (UserFo)fop.execute(); if (user.isError()) { setPage(PAGES[1]); } else { list(); } } private void dodelete() throws Exception{ user = (UserFo)rh.getSessionAttribute(getName()+"#user"); user.toDelete(); UserFop fop = new UserFop(user,rh); fop.setIgnoreValidate(true); user = (UserFo)fop.execute(); list(); } private void backentry() throws Exception{ setPage(PAGES[1]); } }