today

package com.fnst.spif.sepg.dao; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import com.fnst.spif.database.DBTable; import com.fnst.spif.exception.SPIFException; import com.fnst.spif.sepg.bean.ModelItem; import com.fnst.spif.sepg.exception.DuplicateException; import com.fnst.spif.sepg.exception.NotExistException; public class ModelDao { /** * Add a new Software Life Circle Mode to database * @param model Software Life Circle Model entity * @throws DuplicateException * @throws SPIFException * @throws SQLException */ public static void addModelItem(ModelItem model,String[] phases) throws DuplicateException, SQLException, SPIFException { if(modelExists(model.getSubId())) { throw new DuplicateException(); } else { DBTable table = new DBTable(); try { table.connect(); String sql = "INSERT INTO spif_dictionary(id,sub_id,name_cn,name_jp,name_en,moo,remark,status)" +"VALUES(5,'"+model.getSubId()+"','"+model.getName_cn()+"','"+model.getName_jp()+"','"+model.getName_en() +"',"+model.getMoo()+",'"+model.getRemark()+"',1)"; table.updateRecord(sql); PhaseDao.addPhasesByModelId(model.getSubId(), phases); } finally { table.disconnect(); } } } /** * Update a Software Life Circle Mode in the database * @param model * @throws SPIFException * @throws DuplicateException * @throws SQLException * @throws NotExistException */ public static void updateModelItem(ModelItem model,String[] phases) throws SQLException, SPIFException, NotExistException { if(!modelExists(model.getSubId())) { throw new NotExistException(); } else { DBTable table = new DBTable(); try { table.connect(); String sql = "UPDATE spif_dictionary SET name_cn = '" + model.getName_cn() + "', name_jp = '" + model.getName_jp() + "', name_en = '" + model.getName_en() + "', moo = " + model.getMoo() + ",remark = '" + model.getRemark() + "' WHERE id = 5 AND sub_id = '" + model.getSubId() + "'"; // update model_phase // remove legacy value first PhaseDao.removePhasesByModelId(model.getSubId()); // and then, add new phases PhaseDao.addPhasesByModelId(model.getSubId(), phases); } finally { table.disconnect(); } } } /** * Check if there already exists a model according to subid * @param id * @return * @throws SPIFException * @throws SQLException */ public static boolean modelExists(String subId) throws SPIFException, SQLException { DBTable table = new DBTable(); table.connect(); String sql = "SELECT COUNT(*) FROM spif_dictionary WHERE sub_id = '" + subId + "'"; try { ResultSet rs = table.findRecord(sql); if(rs.next()) { if(rs.getInt(1) > 0) return true; else return false; } } finally { table.disconnect(); } return false; } /** * Search models by name and status * @param name Search condition: cn, jp or en * @param status 2:all, 1:valid, 0:invalid * @return List of selected models * @throws SQLException * @throws SPIFException */ public static List searchModel(String name, int status) throws SQLException, SPIFException { DBTable table = new DBTable(); table.connect(); String sql = ""; //sql syntax of selecting only by name if(status == 2) //2 stands for all { System.out.println("status1"); sql = "SELECT sub_id,moo,name_cn,name_jp,name_en,status FROM spif_dictionary WHERE id = 5" +" AND (name_cn LIKE '%" + name + "%' OR name_jp LIKE '%" + name + "%' OR name_en LIKE '%" + name + "%') ORDER BY moo"; } //sql syntax of selecting by status and name else { System.out.println("status2"); sql = "SELECT sub_id,moo,name_cn,name_jp,name_en,status FROM spif_dictionary WHERE id = 5" +" AND status = " + status +" AND (name_cn LIKE '%" + name + "%' OR name_jp LIKE '%" + name + "%' OR name_en LIKE '%" + name + "%') ORDER BY moo"; } System.out.println(sql); //list that contains models List models = null; try { ResultSet rs = table.findRecord(sql); models = new ArrayList(); ModelItem item = null; while(rs.next()) { item = new ModelItem(); item.setSubId(rs.getString("sub_id")); item.setMoo(rs.getInt("moo")); item.setName_cn(rs.getString("name_cn")); item.setName_jp(rs.getString("name_jp")); item.setName_en(rs.getString("name_en")); item.setStatus(rs.getInt("status")); models.add(item); } return models; } finally { table.disconnect(); } } /** * Get all models with no condition * @return List of all models * @throws SQLException * @throws SPIFException */ public static List getModelList() throws SQLException, SPIFException { DBTable table = new DBTable(); table.connect(); String sql = "SELECT sub_id,moo,name_cn,name_jp,name_en,status FROM spif_dictionary WHERE id = 5"; try { ResultSet rs = table.findRecord(sql); List models = new ArrayList(); ModelItem item = null; while(rs.next()) { item = new ModelItem(); item.setSubId(rs.getString("sub_id")); item.setMoo(rs.getInt("moo")); item.setName_cn(rs.getString("name_cn")); item.setName_jp(rs.getString("name_jp")); item.setName_en(rs.getString("name_en")); item.setStatus(rs.getInt("status")); models.add(item); } return models; } finally { table.disconnect(); } } public static ModelItem getModelById(String id) throws SPIFException, SQLException { DBTable table = new DBTable(); table.connect(); String sql = "SELECT name_cn,name_jp,name_en,moo,remark FROM spif_dictionary WHERE sub_id = '"+id+"'"; try { ResultSet rs = table.findRecord(sql); if(rs.next()) { ModelItem model = new ModelItem(); model.setSubId(id); model.setName_cn(rs.getString("name_cn")); model.setName_jp(rs.getString("name_jp")); model.setName_en(rs.getString("name_en")); model.setMoo(rs.getInt("moo")); model.setRemark(rs.getString("remark")); return model; } else { return null; } } finally { table.disconnect(); } } public static void main(String[] args) throws SQLException, SPIFException { ModelItem model = new ModelItem(); model.setName_cn("中文"); model.setName_jp("日本语"); model.setName_en("English"); model.setMoo(12); model.setRemark("some remarks"); model.setSubId("general"); String sql = "UPDATE spif_dictionary SET name_cn = '" + model.getName_cn() + "', name_jp = '" + model.getName_jp() + "', name_en = '" + model.getName_en() + "', moo = "+model.getMoo()+",remark = '" + model.getRemark() + "' WHERE id = 5 AND sub_id = '" + model.getSubId() + "'"; System.out.println(sql); } }