- 供應(yīng)商管理頁面--->頁面跳轉(zhuǎn)
- ProviderServlet
req.getRequestDispatcher("/jsp/providerlist.jsp").forward(req,resp);
- web.xml
<!--ProviderServlet-->
<servlet>
<servlet-name>ProviderServlet</servlet-name>
<servlet-class>com.vigil.servlet.provider.ProviderServlet</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>ProviderServlet</servlet-name>
<url-pattern>/provider/providerlist.html</url-pattern>
</servlet-mapping>
(一)萝毛、供應(yīng)商管理頁面
1、根據(jù)供應(yīng)商編碼或者供應(yīng)商名稱查詢供應(yīng)商總數(shù)
- 1.1 ProviderDao
//根據(jù)供應(yīng)商編碼或者供應(yīng)商名稱查詢供應(yīng)商總數(shù)
public int getProCount(Connection connection, String proCode, String proName) throws SQLException;
- 1.2 ProviderDaoImpl
public int getProCount(Connection connection, String proCode, String proName) throws SQLException {
PreparedStatement pstm = null;
ResultSet rs = null;
int count = 0;
//通過StringBuffer追加sql語句
if(connection != null) {
StringBuffer sql = new StringBuffer();
ArrayList<String> list = new ArrayList();
sql.append("select count(1) as count from smbms_provider");
if(proCode != null) {//追加 供應(yīng)商編碼的查詢判斷
sql.append(" where proCode like ?");
list.add("%" + proCode + "%");
// 追加 供應(yīng)商名稱的查詢判斷，在供應(yīng)商編碼不為空的情況下
if(proName != null) {
sql.append(" and proName like ?");
list.add("%" + proName + "%");
}
}
if(proCode == null && proName != null) {//追加 供應(yīng)商編碼為空但名稱不為空的情況
sql.append(" where proName like ?");
list.add("%" + proName + "%");
}
Object[] params = list.toArray();
System.out.println("ProviderDaoImpl.getProCount().SQL：" + sql);
System.out.println("List:" + list.toString());
rs = BaseDao.execute(connection,pstm,rs,sql.toString(),params);
if(rs.next()) {//從結(jié)果集中獲取最終的數(shù)量
count = rs.getInt("count");
}
}
BaseDao.closeResource(null,pstm,rs);
return count;
}
- 1.3 ProviderService
//根據(jù)供應(yīng)商編碼或者供應(yīng)商名稱查詢供應(yīng)商總數(shù)
public int getProCount(String proCode, String proName);
- 1.4 ProviderServiceImpl
//業(yè)務(wù)層都會調(diào)用dao層，所以要引入dao層
ProviderDao providerDao = null;
public ProviderServiceImpl() {
providerDao = new ProviderDaoImpl();
}
//根據(jù)供應(yīng)商編碼或者供應(yīng)商名稱查詢供應(yīng)商總數(shù)
public int getProCount(String proCode, String proName) {
Connection connection = BaseDao.getConnection();
int count = 0;
try {
count = providerDao.getProCount(connection,proCode,proName);
} catch (SQLException e) {
e.printStackTrace();
} finally {
BaseDao.closeResource(connection,null,null);
}
return count;
}
@Test
public void test() {
System.out.println(new ProviderServiceImpl().getProCount("1","2"));
}
2糯而、通過條件查詢獲得供應(yīng)商列表
- 2.1 ProviderDao
//通過條件查詢獲得供應(yīng)商列表
public List<Provider> getProList(Connection connection, String proCode, String proName, int currentPageNo, int pageSize) throws SQLException;
- 2.2 ProviderDaoImpl
public List<Provider> getProList(Connection connection, String proCode, String proName, int currentPageNo, int pageSize) throws SQLException {
PreparedStatement pstm = null;
ResultSet rs = null;
List<Provider> proList = new ArrayList<Provider>();
if(connection != null) {
StringBuffer sql = new StringBuffer();
sql.append("select * from smbms_provider");
List list = new ArrayList();
if(proCode != null && proCode.length() > 0) { //追加 供應(yīng)商編碼的查詢判斷
sql.append(" where proCode like ?");
list.add("%" + proCode + "%");
// 追加 供應(yīng)商名稱的查詢判斷峭状，在供應(yīng)商編碼不為空的情況下
if(proName != null && proName.length() > 0) {
sql.append(" and proName like ?");
list.add("%" + proName + "%");
}
} else if(proName != null && proName.length() > 0) {//追加 供應(yīng)商編碼為空但名稱不為空的情況
sql.append(" where proName like ?");
list.add("%" + proName + "%");
}
//在數(shù)據(jù)庫中克滴，分頁使用 limit(傳的兩個數(shù)值分別是開始位和總數(shù)) startIndex，pageSize 优床；總數(shù)
//startIndex = （當(dāng)前頁-1）*頁面大小--->對應(yīng)前端頁面的第幾頁
//0劝赔，5 1 - 1 = 0 * 01234
//5，5 2 - 1 = 1 * 5胆敞，56789
//10望忆，5 3 - 1 = 2 * 10 10，11竿秆，12，13稿壁，14
sql.append(" order by creationDate limit ?,?");
currentPageNo = (currentPageNo - 1) * pageSize;
list.add(currentPageNo);
list.add(pageSize);
Object[] params = list.toArray();
System.out.println("ProviderDaoImpl.getProCount().SQL：" + sql);
System.out.println("List:" + list.toString());
rs = BaseDao.execute(connection, pstm, rs, sql.toString(), params);
while(rs.next()) {
Provider tPro = new Provider();
tPro.setId(rs.getInt("id"));
tPro.setProCode(rs.getString("proCode"));
tPro.setProName(rs.getString("proName"));
tPro.setProDesc(rs.getString("proDesc"));
tPro.setProContact(rs.getString("proContact"));
tPro.setProPhone(rs.getString("proPhone"));
tPro.setProAddress(rs.getString("proAddress"));
tPro.setProFax(rs.getString("proFax"));
tPro.setCreatedBy(rs.getInt("createdBy"));
tPro.setCreationDate(rs.getDate("creationDate"));
tPro.setModifyBy(rs.getInt("modifyBy"));
tPro.setModifyDate(rs.getDate("modifyDate"));
proList.add(tPro);
}
BaseDao.closeResource(null, pstm, rs);
}
return proList;
}
- 2.3 ProviderService
//通過條件查詢獲得供應(yīng)商列表
public List<Provider> getProList(String queryProCode, String queryProName,int currentPageNo, int pagaSize);
- 2.4 ProviderServiceImpl
//通過條件查詢獲得供應(yīng)商列表
public List<Provider> getProList(String queryProCode, String queryProName,int currentPageNo, int pagaSize) {
Connection connection = null;
List<Provider> providers = null;
System.out.println("queryProCode-->" + queryProCode);
System.out.println("queryProName-->" + queryProName);
System.out.println("currentPageNo-->" + currentPageNo);
System.out.println("pagaSize-->" + pagaSize);
try {
connection = BaseDao.getConnection();
providers = providerDao.getProList(connection,queryProCode,queryProName,currentPageNo,pagaSize);
} catch (SQLException e) {
e.printStackTrace();
} finally {
BaseDao.closeResource(connection,null,null);
}
return providers;
}
@Test
public void test() {
ProviderServiceImpl providerService = new ProviderServiceImpl();
List<Provider> proList = providerService.getProList("", "", 1, 5);
for(Provider provider : proList) {
System.out.println(provider);
}
}
- 2.5 ProviderServlet
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
String method = req.getParameter("method");
System.out.println("要執(zhí)行的操作是：" + method);
if("query".equals(method)) {
getProList(req, resp);
} else {
getProList(req, resp);
}
}
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
doGet(req, resp);
}
public void getProList(HttpServletRequest req, HttpServletResponse resp) {
//查詢獲得用戶列表
ProviderService providerService = new ProviderServiceImpl();
List<Provider> providerList = null;
//第一次請求幽钢，是可以固定頁面大寫和頁數(shù)的,還有下拉欄默認(rèn)值，建議把這些數(shù)值寫到配置文件傅是，方便后期修改
int pageSize = 5;
int currentPageNo = 1;
//從前端獲取數(shù)據(jù)
String queryProCode = req.getParameter("queryProCode");
String queryProName = req.getParameter("queryProName");
String pageIndex = req.getParameter("pageIndex");
if(pageIndex != null) {
currentPageNo = Integer.parseInt(pageIndex);
}
System.out.println("queryProName:" + queryProName + " 匪燕，queryProCode: " + queryProCode + " ，pageIndex: " + pageIndex);
//獲得用戶的總數(shù)（分頁：上一頁喧笔、下一頁的情況）
int totalCount = providerService.getProCount(queryProCode, queryProName);
//總頁數(shù)支持
PageSupport pageSupport = new PageSupport();
pageSupport.setCurrentPageNo(currentPageNo);//當(dāng)前頁面
pageSupport.setPageSize(pageSize);//頁面大小
pageSupport.setTotalCount(totalCount);//用戶總數(shù)
int totalPageCount = pageSupport.getTotalPageCount();//總頁數(shù)
//控制首頁和尾頁
//如果頁面要小于1帽驯，就顯示第一頁的東西
if(currentPageNo < 1) {
currentPageNo = 1;
} else if (currentPageNo > totalPageCount) {//當(dāng)前頁大于最后一頁
currentPageNo = totalPageCount;
}
//獲取供應(yīng)商列表展示
providerList = providerService.getProList(queryProCode, queryProName, currentPageNo, pageSize);
req.setAttribute("providerList",providerList);
req.setAttribute("totalCount",totalCount);
req.setAttribute("currentPageNo",currentPageNo);
req.setAttribute("totalPageCount",totalPageCount);
req.setAttribute("queryProCode",queryProCode);
req.setAttribute("queryProName",queryProName);
//返回前端
try {
req.getRequestDispatcher("/jsp/providerlist.jsp").forward(req,resp);
} catch (ServletException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
(二)、供應(yīng)商查看頁面
根據(jù)供應(yīng)商id查詢供應(yīng)商信息书闸，供應(yīng)商管理頁面 下的 查看請求
- ProviderDao
//根據(jù)供應(yīng)商id查詢供應(yīng)商信息
public Provider getProByProId(Connection connection, int proId) throws SQLException;
- ProviderDaoImpl
//根據(jù)供應(yīng)商id查詢供應(yīng)商信息
public Provider getProByProId(Connection connection, int proId) throws SQLException {
PreparedStatement pstm = null;
ResultSet rs = null;
Provider pro = new Provider();
if(connection != null) {
StringBuffer sql = new StringBuffer();
sql.append("select * from smbms_provider where id = ?");
Object[] params = {proId};
rs = BaseDao.execute(connection, pstm, rs, sql.toString(), params);
System.out.println("ProviderDaoImpl.getProByProId().SQL：" + sql);
System.out.println(sql + "---- ?=" + proId);
if(rs.next()) {
pro.setId(rs.getInt("id"));
pro.setProCode(rs.getString("proCode"));
pro.setProName(rs.getString("proName"));
pro.setProDesc(rs.getString("proDesc"));
pro.setProContact(rs.getString("proContact"));
pro.setProPhone(rs.getString("proPhone"));
pro.setProAddress(rs.getString("proAddress"));
pro.setProFax(rs.getString("proFax"));
pro.setCreatedBy(rs.getInt("createdBy"));
pro.setCreationDate(rs.getDate("creationDate"));
pro.setModifyBy(rs.getInt("modifyBy"));
pro.setModifyDate(rs.getDate("modifyDate"));
}
BaseDao.closeResource(null, pstm, rs);
}
return pro;
}
- ProviderService
//根據(jù)供應(yīng)商id查詢供應(yīng)商信息
public Provider getProByProId(int proId);
- ProviderServiceImpl
//根據(jù)供應(yīng)商id查詢供應(yīng)商信息
public Provider getProByProId(int proId) {
Connection connection = null;
Provider pro = null;
try {
connection = BaseDao.getConnection();
pro = providerDao.getProByProId(connection,proId);
} catch (SQLException e) {
e.printStackTrace();
}
return pro;
}
@Test
public void test() {
ProviderServiceImpl providerService = new ProviderServiceImpl();
Provider pro = providerService.getProByProId(1);
System.out.println(pro);
}
- ProviderServlet
//根據(jù)供應(yīng)商id查詢供應(yīng)商信息
public Provider getProByProId(HttpServletRequest req, HttpServletResponse resp) {
//獲得前端數(shù)據(jù)
String id = req.getParameter("proid");
System.out.println(id);
if(id != null) {
int proId = Integer.parseInt(id);
//調(diào)用業(yè)務(wù)層處理
ProviderService providerService = new ProviderServiceImpl();
Provider pro = providerService.getProByProId(proId);
System.out.println(pro.getId());
return pro;
}
return null;
}
//給前端設(shè)置數(shù)據(jù)并跳轉(zhuǎn)頁面-->providerview.jsp
public void view(HttpServletRequest req, HttpServletResponse resp) {
Provider pro = getProByProId(req, resp);
req.setAttribute("provider",pro);
try {
req.getRequestDispatcher("/jsp/providerview.jsp").forward(req,resp);
} catch (ServletException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
- web.xml
<servlet-mapping>
<servlet-name>ProviderServlet</servlet-name>
<url-pattern>/provider/provider.do</url-pattern>
</servlet-mapping>
(三)尼变、供應(yīng)商修改頁面
1. 頁面跳轉(zhuǎn)
根據(jù)供應(yīng)商id 獲取供應(yīng)商信息，并返回前端浆劲，這和查看供應(yīng)商信息的功能一樣嫌术，只是跳轉(zhuǎn)的頁面不一樣，只要修改Servlet就行
- ProviderServlet
public Provider getProByProId(HttpServletRequest req, HttpServletResponse resp) {
//獲得前端數(shù)據(jù)
String id = req.getParameter("proid");
System.out.println(id);
if(id != null) {
int proId = Integer.parseInt(id);
//調(diào)用業(yè)務(wù)層處理
ProviderService providerService = new ProviderServiceImpl();
Provider pro = providerService.getProByProId(proId);
System.out.println(pro.getId());
return pro;
}
return null;
}
//給前端設(shè)置數(shù)據(jù)并跳轉(zhuǎn)頁面-->providermodify.jsp
public void modify(HttpServletRequest req, HttpServletResponse resp) {
Provider pro = getProByProId(req, resp);
//給前端設(shè)置數(shù)據(jù)并跳轉(zhuǎn)頁面
req.setAttribute("provider",pro);
try {
req.getRequestDispatcher("/jsp/providermodify.jsp").forward(req,resp);
} catch (ServletException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
2. 修改供應(yīng)商信息
- 2.1 ProviderDao
//通過Provider中保存要修改的供應(yīng)商信息牌借，在數(shù)據(jù)庫中修改對應(yīng)供應(yīng)商信息
public int updatePro(Connection connection, Provider pro) throws SQLException;
- 2.2 ProviderDaoImpl
//追加set 或者逗號
public boolean appendSet(StringBuffer sql, boolean flag) {
if(flag) {
sql.append(" set");
} else {
sql.append(",");
}
return false;
}
//通過Provider中保存要修改的供應(yīng)商信息度气，在數(shù)據(jù)庫中修改對應(yīng)供應(yīng)商信息
public int updatePro(Connection connection, Provider pro) throws SQLException {
PreparedStatement pstm = null;
int count = 0;
if(connection != null) {
StringBuffer sql = new StringBuffer();
List list = new ArrayList();
sql.append("update smbms_provider");
//set proCode = ?, proName = ?, proAddress = ?, proContact = ?, proDesc = ?,
// proFax = ?, proPhone = ?, modifyDate = ?, modifyBy = ? where id = ?
boolean flag = true;//true : 第一次追加set語句
if(pro.getProCode() != null) {
flag = appendSet(sql,flag);
sql.append(" proCode = ?");
list.add(pro.getProCode());
}
if(pro.getProName() != null) {
flag = appendSet(sql,flag);
sql.append(" proName = ?");
list.add(pro.getProName());
}
if(pro.getProAddress() != null) {
flag = appendSet(sql,flag);
sql.append(" proAddress = ?");
list.add(pro.getProAddress());
}
if(pro.getProContact() != null) {
flag = appendSet(sql,flag);
sql.append(" proContact = ?");
list.add(pro.getProContact());
}
if(pro.getProDesc() != null) {
flag = appendSet(sql,flag);
sql.append(" proDesc = ?");
list.add(pro.getProDesc());
}
if(pro.getProFax() != null) {
flag = appendSet(sql,flag);
sql.append(" proFax = ?");
list.add(pro.getProFax());
}
if(pro.getProPhone() != null) {
flag = appendSet(sql,flag);
sql.append(" proPhone = ?");
list.add(pro.getProPhone());
}
if(pro.getModifyDate() != null) {
flag = appendSet(sql,flag);
sql.append(" modifyDate = ?");
list.add(pro.getModifyDate());
}
if(pro.getModifyBy() != null) {
flag = appendSet(sql,flag);
sql.append(" modifyBy = ?");
list.add(pro.getModifyBy());
}
if(pro.getId() != null) {//id不能為空，否則不進(jìn)行更改Ｅ虮ā磷籍！
sql.append(" where id = ?");
list.add(pro.getId());
Object[] params = list.toArray();
//執(zhí)行更改語句
count = BaseDao.execute(connection, pstm, sql.toString(), params);
}
System.out.println("ProviderDaoImpl.updatePro.sql: " + sql);
System.out.println(list.toString());
}
//關(guān)閉資源
BaseDao.closeResource(null,pstm,null);
return count;
}
- 2.3 ProviderService
//通過Provider中保存要修改的供應(yīng)商信息，在數(shù)據(jù)庫中修改對應(yīng)供應(yīng)商信息
public boolean updatePro(Provider pro);
- 2.4 ProviderServiceImpl
//通過Provider中保存要修改的供應(yīng)商信息现柠，在數(shù)據(jù)庫中修改對應(yīng)供應(yīng)商信息
public boolean updatePro(Provider pro) {
//取當(dāng)前（北京）時間
Date nowdate = new Date();
//轉(zhuǎn)換時間格式
SimpleDateFormat simpleDate = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
//設(shè)置更改時間院领，但是存入數(shù)據(jù)庫會少了8小時？
pro.setModifyDate(Timestamp.valueOf(simpleDate.format(nowdate)));
Connection connection = null;
boolean flag = false;
try {
connection = BaseDao.getConnection();
flag = (providerDao.updatePro(connection, pro)) > 0 ? true : false;
} catch (SQLException e) {
e.printStackTrace();
} finally {//關(guān)閉資源
BaseDao.closeResource(connection,null,null);
}
return flag;
}
@Test
public void test() {
ProviderServiceImpl providerService = new ProviderServiceImpl();
Provider pro = new Provider();
pro.setId(19);
pro.setProName("魚佬大老板");
pro.setModifyBy(1);
boolean b = providerService.updatePro(pro);
System.out.println(b);
}
- 2.5 ProviderServlet
//供應(yīng)商修改頁 提交修改保存請求晒旅，將數(shù)據(jù)保存到Provider實例對象中栅盲，頁面轉(zhuǎn)發(fā)到 供應(yīng)商管理頁面
public void modifyexe(HttpServletRequest req, HttpServletResponse resp) {
Provider pro = new Provider();
//獲得當(dāng)前Session的id，是執(zhí)行修改操作的用戶
Object o = req.getSession().getAttribute(Constants.USER_SESSION);
if(o != null) {
pro.setModifyBy(((User)o).getId());
System.out.println("操作修改的用戶id:" + pro.getModifyBy());
}
//從前端獲取數(shù)據(jù)
String proCode = req.getParameter("proCode");
String proName = req.getParameter("proName");
String proContact = req.getParameter("proContact");
String proPhone = req.getParameter("proPhone");
String proAddress = req.getParameter("proAddress");
String proFax = req.getParameter("proFax");
String proDesc = req.getParameter("proDesc");
String id = req.getParameter("id");
//供應(yīng)商id
pro.setId(Integer.parseInt(id));
System.out.println("供應(yīng)商id:" + id);
//供應(yīng)商編碼
pro.setProCode(proCode);
System.out.println("供應(yīng)商編碼:" + proCode);
//供應(yīng)商名稱
pro.setProName(proName);
System.out.println("供應(yīng)商名稱:" + proName);
//聯(lián)系人
pro.setProContact(proContact);
System.out.println("聯(lián)系人:" + proContact);
//聯(lián)系電話
pro.setProPhone(proPhone);
System.out.println("聯(lián)系電話:" + proPhone);
//聯(lián)系地址
pro.setProAddress(proAddress);
System.out.println("聯(lián)系地址:" + proAddress);
//傳真
pro.setProFax(proFax);
System.out.println("傳真:" + proFax);
//描述
pro.setProDesc(proDesc);
System.out.println("描述:" + proDesc);
//調(diào)業(yè)務(wù)層執(zhí)行方法
ProviderService providerService = new ProviderServiceImpl();
providerService.updatePro(pro);
// //頁面轉(zhuǎn)發(fā)到 供應(yīng)商管理頁面
// getProList(req, resp);
//返回前端
try {
req.getRequestDispatcher("/jsp/providermodify.jsp").forward(req,resp);
} catch (ServletException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
- 2.6 web.xml
<servlet-mapping>
<servlet-name>ProviderServlet</servlet-name>
<url-pattern>/provider/providermodifysave.html</url-pattern>
</servlet-mapping>
(四)废恋、供應(yīng)商添加頁面
1. 添加頁面跳轉(zhuǎn)
- 1.1 ProviderServlet
//跳轉(zhuǎn)頁面-->provideradd.jsp
public void add_jsp(HttpServletRequest req, HttpServletResponse resp) {
try {
req.getRequestDispatcher("/jsp/provideradd.jsp").forward(req,resp);
} catch (ServletException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
- 1.2 web.xml
<servlet-mapping>
<servlet-name>ProviderServlet</servlet-name>
<url-pattern>/provider/provideradd.html</url-pattern>
</servlet-mapping>
2. 查詢供貨商編碼
查詢?nèi)康墓┴浬叹幋a（dao）
確定供貨商編碼是否未被使用谈秫、給前端設(shè)置數(shù)據(jù)（Servlet）
- 2.1 ProviderDao
//查詢?nèi)康墓┴浬叹幋a
public List<String> getAllProCode(Connection connection) throws SQLException;
- 2.2 ProviderDaoImpl
//查詢?nèi)康墓┴浬叹幋a
public List<String> getAllProCode(Connection connection) throws SQLException {
PreparedStatement pstm = null;
ResultSet rs = null;
List<String> list = new ArrayList<String>();
if(connection != null) {
String sql = "select proCode from smbms_provider";
rs = BaseDao.execute(connection, pstm, rs, sql, new Object[]{});
while(rs.next()) {
list.add(rs.getString("proCode"));
}
//關(guān)閉資源
BaseDao.closeResource(null,pstm,rs);
System.out.println("ProviderDaoImpl.getAllProCode.sql:" + sql);
}
return list;
}
- 2.3 ProviderService
//查詢?nèi)康墓┴浬叹幋a
public List<String> getAllProCode();
- 2.4 ProviderServiceImpl
//查詢?nèi)康墓┴浬叹幋a
public List<String> getAllProCode() {
Connection connection = null;
List<String> list = null;
try {
connection = BaseDao.getConnection();
list = providerDao.getAllProCode(connection);
} catch (SQLException e) {
e.printStackTrace();
} finally {//關(guān)閉資源
BaseDao.closeResource(connection,null,null);
}
return list;
}
@Test
public void test() {
ProviderServiceImpl providerService = new ProviderServiceImpl();
System.out.println(providerService.getAllProCode());
}
- 2.5 ProviderServlet
//供應(yīng)商 添加頁 異步請求：確定新增供貨商的 編碼是否可用（未被使用）
public void ucexist(HttpServletRequest req, HttpServletResponse resp) {
String proCode = req.getParameter("proCode");
ProviderService proService = new ProviderServiceImpl();
List<String> list = proService.getAllProCode();
boolean flag = true;//默認(rèn)新供貨商編碼可用
if(proCode != null) {
System.out.println("proCode = " + proCode);
for (int i = 0; i < list.size() && !flag; i++) {
flag = !proCode.equals(list.get(i));//若相同會返回true扒寄，那么取反，說明不可用
}
}
System.out.println("是否存在同名：" + !flag + "拟烫。是否為可用編碼：" + flag);
//給頁面返回json對象的數(shù)據(jù)
Map<String,String> map = new HashMap<String, String>();
if(flag) {
map.put("userCode","noexist");
} else {
map.put("userCode","exist");
}
try {
resp.setContentType("application/json");
PrintWriter writer = resp.getWriter();
/*
JSONArray,工具類该编，轉(zhuǎn)換格式，將map轉(zhuǎn)換為json
*/
writer.write(JSONArray.toJSONString(map));
writer.flush();
writer.close();
} catch (IOException e) {
e.printStackTrace();
}
}
- 2.6 web.xml
<servlet-mapping>
<servlet-name>ProviderServlet</servlet-name>
<url-pattern>/provider/ucexist.html</url-pattern>
</servlet-mapping>
3. 添加用戶
- 3.1 ProviderDao
//獲得所有用戶中最大的id
public Integer getAllProMaxId(Connection connection) throws SQLException;
//添加供應(yīng)商
public int addPro(Connection connection, Provider pro) throws SQLException;
- 3.2 ProviderDaoImpl
//獲得所有用戶中最大的id
public Integer getAllProMaxId(Connection connection) throws SQLException {
int maxId = 0;
PreparedStatement pstm = null;
ResultSet rs = null;
if(connection != null) {
String sql = "select max(id) as max from smbms_provider";
rs = BaseDao.execute(connection,pstm,rs,sql,new Object[]{});
if(rs.next()) {
maxId = rs.getInt("max");
}
//關(guān)閉資源
BaseDao.closeResource(null,pstm,rs);
System.out.println("ProviderDaoImpl.getPorId.sql:" + sql);
System.out.println(maxId);
}
return maxId;
}
//添加供應(yīng)商
public int addPro(Connection connection, Provider pro) throws SQLException {
PreparedStatement pstm = null;
int count = 0;
if(connection != null) {
String sql = "insert into smbms_provider(id, proCode, proName, proDesc, proContact, proPhone, proAddress, proFax, createdBy, creationDate, modifyDate, modifyBy) value (?,?,?,?,?,?,?,?,?,?,?,?)";
List list = new ArrayList();
//供應(yīng)商id
list.add(pro.getId());
//供應(yīng)商proCode
list.add(pro.getProCode());
//供應(yīng)商proName
list.add(pro.getProName());
//供應(yīng)商proDesc
list.add(pro.getProDesc());
//供應(yīng)商proContact
list.add(pro.getProContact());
//供應(yīng)商proPhone
list.add(pro.getProPhone());
//供應(yīng)商proAddress
list.add(pro.getProAddress());
//供應(yīng)商proFax
list.add(pro.getProFax());
//供應(yīng)商createdBy
list.add(pro.getCreatedBy());
//供應(yīng)商creationDate
list.add(pro.getCreationDate());
//供應(yīng)商modifyDate
list.add(pro.getModifyDate());
//供應(yīng)商modifyBd
list.add(pro.getModifyBy());
//如果是null硕淑，且數(shù)據(jù)庫設(shè)置了default值课竣，那么是注入null呢？還是default值
Object[] params = list.toArray();
count = BaseDao.execute(connection, pstm, sql, params);
System.out.println("ProviderDaoImpl.addPro.SQL：" + sql);
System.out.println(list);
//關(guān)閉資源
BaseDao.closeResource(null,pstm,null);
}
return count;
}
- 3.3 ProviderService
//獲得所有用戶中最大的id
public Integer getAllProMaxId();
//添加供應(yīng)商
public boolean addPro(Provider pro);
- 3.4 ProviderServiceImpl
//獲得所有用戶中最大的id
public Integer getAllProMaxId() {
Connection connection = null;
Integer maxId = 0;
try {
connection = BaseDao.getConnection();
maxId = providerDao.getAllProMaxId(connection);
} catch (SQLException e) {
e.printStackTrace();
} finally {//關(guān)閉資源
BaseDao.closeResource(connection,null,null);
}
return maxId;
}
//添加供應(yīng)商
public boolean addPro(Provider pro) {
int flag = 0;
Connection connection = null;
if(pro != null) {
//設(shè)值供貨商id置媳，比最大的大1
pro.setId(getAllProMaxId() + 1);
//取當(dāng)前（北京）時間
Date nowdate = new Date();
//轉(zhuǎn)換時間格式
SimpleDateFormat simpleDate = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
//設(shè)置創(chuàng)建用戶的時間于樟，但是存入數(shù)據(jù)庫會少了8小時？
pro.setCreationDate(Timestamp.valueOf(simpleDate.format(nowdate)));
System.out.println("id =" + pro.getId() + "... date = " +nowdate.toString());
try {
connection = BaseDao.getConnection();
flag = providerDao.addPro(connection,pro);
} catch (SQLException e) {
e.printStackTrace();
} finally {
BaseDao.closeResource(connection,null,null);
}
}
return flag > 0;
}
@Test
public void test() {
ProviderServiceImpl providerService = new ProviderServiceImpl();
System.out.println(providerService.getAllProMaxId());
Provider pro = new Provider();
pro.setProCode("111");
pro.setProName("111");
pro.setProDesc("111");
pro.setProAddress("111");
pro.setCreatedBy(1);
boolean b = providerService.addPro(pro);
System.out.println("是否添加成功:" + b);
}
- 3.5 ProviderServlet
//供應(yīng)商 添加頁 的提交請求拇囊，向數(shù)據(jù)庫添加該供貨商信息
public void add(HttpServletRequest req, HttpServletResponse resp) {
Provider pro = new Provider();
//獲得當(dāng)前Session的id迂曲，是執(zhí)行修改操作的用戶
Object o = req.getSession().getAttribute(Constants.USER_SESSION);
if(o != null) {
pro.setCreatedBy(((User)o).getId());
System.out.println("操作修改的用戶id:" + pro.getCreatedBy());
}
//從前端獲取數(shù)據(jù)
String proCode = req.getParameter("proCode");
String proName = req.getParameter("proName");
String proContact = req.getParameter("proContact");
String proPhone = req.getParameter("proPhone");
String proAddress = req.getParameter("proAddress");
String proFax = req.getParameter("proFax");
String proDesc = req.getParameter("proDesc");
//供應(yīng)商編碼
pro.setProCode(proCode);
System.out.println("供應(yīng)商編碼:" + proCode);
//供應(yīng)商名稱
pro.setProName(proName);
System.out.println("供應(yīng)商名稱:" + proName);
//聯(lián)系人
pro.setProContact(proContact);
System.out.println("聯(lián)系人:" + proContact);
//聯(lián)系電話
pro.setProPhone(proPhone);
System.out.println("聯(lián)系電話:" + proPhone);
//聯(lián)系地址
pro.setProAddress(proAddress);
System.out.println("聯(lián)系地址:" + proAddress);
//傳真
pro.setProFax(proFax);
System.out.println("傳真:" + proFax);
//描述
pro.setProDesc(proDesc);
System.out.println("描述:" + proDesc);
//調(diào)業(yè)務(wù)層執(zhí)行方法
ProviderService providerService = new ProviderServiceImpl();
providerService.addPro(pro);
//跳轉(zhuǎn)頁面-->provideradd.jsp
try {
req.getRequestDispatcher("/jsp/provideradd.jsp").forward(req,resp);
} catch (ServletException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
- 3.6 web.xml
<servlet-mapping>
<servlet-name>ProviderServlet</servlet-name><!--添加用戶信息的提交請求-->
<url-pattern>/provider/addprovider.html</url-pattern>
</servlet-mapping>
(五)、刪除供應(yīng)商
- ProviderDao
//根據(jù)id 刪除供應(yīng)商
public int delPro(Connection connection, int proId) throws SQLException;
//根據(jù)id 獲得該供應(yīng)商的訂單總數(shù)
public int getBillsCount(Connection connection, int proId) throws SQLException;
- ProviderDaoImpl
//根據(jù)id 刪除供應(yīng)商
public int delPro(Connection connection, int proId) throws SQLException {
PreparedStatement pstm = null;
int count = 0;
if(connection != null) {
String sql = "delete from smbms_provider where id = ?";
Object[] params = {proId};
count = BaseDao.execute(connection, pstm, sql,params);
System.out.println("ProviderDaoImpl.delPro.sql:--->" + sql + "?-->id = -->" + proId);
//關(guān)閉資源
BaseDao.closeResource(null,pstm,null);
}
return count;
}
//根據(jù)id 獲得該供應(yīng)商的訂單總數(shù)
public int getBillsCount(Connection connection, int proId) throws SQLException {
PreparedStatement pstm = null;
ResultSet rs = null;
int count = 0;
if(connection != null) {
String sql = "select count(1) as billsCount from smbms_provider sp, smbms_bill sb where sb.providerId = sp.id and sp.id = ?";
Object[] params = {proId};
rs = BaseDao.execute(connection, pstm,rs, sql, params);
System.out.println("ProviderDaoImpl.getBillsCount.sql:--->" + sql + "?-->id = -->" + proId);
if(rs.next()) {
count = rs.getInt("billsCount");
}
//關(guān)閉資源
BaseDao.closeResource(null,pstm,null);
}
return count;
}
- ProviderService
//根據(jù)id 刪除供應(yīng)商
public int delPro(int proId);
//根據(jù)id 獲得該供應(yīng)商的訂單總數(shù)
public int getBillsCount(int proId);
- ProviderServiceImpl
//根據(jù)id 刪除供應(yīng)商
public int delPro(int proId) {
Connection connection = null;
int flag = -1;
try {
connection = BaseDao.getConnection();
//判斷該供應(yīng)商下有多少的訂單
int temp = 0;
if((temp = getBillsCount(proId)) > 0) {//如果大于0寥袭，則是有訂單的路捧，那么就將訂單數(shù)返回
return temp;
}
//調(diào)用dao層方法，刪除供應(yīng)商
flag = providerDao.delPro(connection,proId) - 1;//因為成功影響的行數(shù)為1传黄，為了不返回大于0 的數(shù)杰扫，減一等于0，返回0
} catch (SQLException e) {
e.printStackTrace();
} finally {// 關(guān)閉資源
BaseDao.closeResource(connection,null,null);
}
return flag;//輸出失敗會返回-1
}
//根據(jù)id 獲得該供應(yīng)商的訂單總數(shù)
public int getBillsCount(int proId) {
Connection connection = null;
int count = 0;
try {
connection = BaseDao.getConnection();
//調(diào)用dao層方法膘掰，查詢訂單數(shù)
count = providerDao.getBillsCount(connection,proId);
} catch (SQLException e) {
e.printStackTrace();
} finally {// 關(guān)閉資源
BaseDao.closeResource(connection,null,null);
}
return count;
}
@Test
public void test() {
ProviderServiceImpl providerService = new ProviderServiceImpl();
System.out.println(providerService.getAllProMaxId());
Provider pro = new Provider();
pro.setProCode("111");
pro.setProName("111");
pro.setProDesc("111");
pro.setProAddress("111");
pro.setCreatedBy(1);
boolean b = providerService.addPro(pro);
System.out.println("是否添加成功:" + b);
System.out.println("是否刪除成功:" + providerService.delPro(pro.getId()));
System.out.println("訂單數(shù)為：" + providerService.getBillsCount(1));
System.out.println("是否刪除成功:" + providerService.delPro(1));
}
- ProviderServlet
//刪除指定id的供應(yīng)商
public void delprovider(HttpServletRequest req, HttpServletResponse resp) {
String proid = req.getParameter("proid");
HashMap<String, String> map = new HashMap<String, String>();
if(proid != null) {// id存在
int proId = Integer.parseInt(proid);
System.out.println("delete Provider.Id-->" + proId);
//調(diào)用Service 層的方法處理
ProviderService proService = new ProviderServiceImpl();
int flag = proService.delPro(proId);
System.out.println("delete result--->>" + flag);
if(flag == 0) {//刪除成功
map.put("delResult","true");
} else if(flag == -1) {//刪除失敗
map.put("delResult","false");
} else if(flag > 0) {//刪除失敗章姓，因為存在訂單
map.put("delResult",flag + "");
}
} else {//id不存在
map.put("delResult","notexist");
}
resp.setContentType("application/jsp");
try {
PrintWriter writer = resp.getWriter();
/*
JSONArray,工具類，轉(zhuǎn)換格式炭序，將map轉(zhuǎn)換為json
*/
writer.write(JSONArray.toJSONString(map));
writer.flush();
writer.close();
} catch (IOException e) {
e.printStackTrace();
}
}
- web.xml
<servlet-mapping>
<servlet-name>ProviderServlet</servlet-name>
<url-pattern>/provider/delProvider.html</url-pattern><!--刪除供貨商的異步請求-->
</servlet-mapping>
至此完成了供應(yīng)商管理頁面的全部功能啤覆，以下是ProviderServlet的doGet方法
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
String method = req.getParameter("method");
System.out.println("要執(zhí)行的操作是：" + method);
if("query".equals(method)) {// 查詢供應(yīng)商列表，跳轉(zhuǎn)到 供應(yīng)商管理頁面
getProList(req, resp);
} else if("view".equals(method)) {// 供應(yīng)商管理頁面 下的 查看請求
view(req, resp);
} else if("modify".equals(method)) {// 供應(yīng)商管理頁面 下的 修改請求
modify(req, resp);
} else if("modifyexe".equals(method)) {// 供應(yīng)商 修改頁 提交修改保存請求惭聂，頁面轉(zhuǎn)發(fā)到 供應(yīng)商管理頁面
modifyexe(req, resp);
} else if("add_jsp".equals(method)) {// 供應(yīng)商管理頁面 下的 修改請求窗声，跳轉(zhuǎn)到添加用戶界面
add_jsp(req, resp);
} else if("ucexist".equals(method)) {// 供應(yīng)商 添加頁 異步請求：確定新增供貨商的 編碼是否可用（未被使用），無頁面跳轉(zhuǎn)
ucexist(req,resp);
} else if("add".equals(method)) {// 供應(yīng)商 添加頁 的提交請求辜纲，向數(shù)據(jù)庫添加該供貨商信息
add(req, resp);
} else if("delprovider".equals(method)) {
delprovider(req,resp);
}
// else {// 查詢供應(yīng)商列表笨觅，跳轉(zhuǎn)到供應(yīng)商列表頁面，因為首頁點進(jìn)來的話是超鏈接耕腾，沒有提交屬性
// getProList(req, resp);
// }
}
