public boolean payout(HttpServletRequest newrequest) throws Exception {
request = newrequest;
session = request.getSession(false);
if (session == null)
{
return false;
}
String Userid = (String) session.getAttribute("userid"); //取得用户ID号
long userid=0;
if (Userid==null || Userid.equals(""))
{
isLogin = false;
return false;
}else {
try
{
userid = Long.parseLong(Userid);
}
catch (NumberFormatException e)
{
return false;
}
} purchaselist = (Vector)session.getAttribute("shopcar");
if (purchaselist==null || purchaselist.size()<0)
{
return false;
}
String Content = request.getParameter("content");
if (Content==null)
{
Content="";
}
Content = getGbk(Content);
String IP = request.getRemoteAddr();
String TotalPrice = request.getParameter("totalprice"); sqlStr = "select max(id) from My_indent";
rs = stmt.executeQuery(sqlStr);
if (rs.next())
{
IndentNo = "HYD" + userid + "" + rs.getString(1);
} else {
IndentNo = "HYD" + userid + "0";
}
rs.close(); sqlStr = "insert into My_indent (IndentNo,UserId,SubmitTime,ConsignmentTime,TotalPrice,content,IPAddress,IsPayoff,IsSales) values ('";
sqlStr = sqlStr + IndentNo + "','";
sqlStr = sqlStr + userid + "',getdate(),getdate()+7,'";
sqlStr = sqlStr + TotalPrice + "','";
sqlStr = sqlStr + strFormat.toSql(Content) + "','";
sqlStr = sqlStr + IP + "',1,1)";
try
{
stmt.execute(sqlStr);
sqlStr= "select max(id) from My_indent where UserId = " + userid;
rs = stmt.executeQuery(sqlStr);
long indentid = 0;
while (rs.next())
{
indentid = rs.getLong(1);
}
rs.close();
for (int i=0; i<purchaselist.size() ;i++ )
{
indentlist iList = (indentlist) purchaselist.elementAt(i);
sqlStr = "insert into My_indentlist (IndentNo,BookNo,Amount) values (";
sqlStr = sqlStr + indentid + ",'";
sqlStr = sqlStr + iList.getBookNo() + "','";
sqlStr = sqlStr + iList.getAmount() + "')";
stmt.execute(sqlStr);
sqlStr = "update My_book set leav_number=leav_number - " + iList.getAmount() + " where id = " + iList.getBookNo();
stmt.execute(sqlStr);
}
return true;
}
catch (SQLException e)
{
return false;
}
}
request = newrequest;
session = request.getSession(false);
if (session == null)
{
return false;
}
String Userid = (String) session.getAttribute("userid"); //取得用户ID号
long userid=0;
if (Userid==null || Userid.equals(""))
{
isLogin = false;
return false;
}else {
try
{
userid = Long.parseLong(Userid);
}
catch (NumberFormatException e)
{
return false;
}
} purchaselist = (Vector)session.getAttribute("shopcar");
if (purchaselist==null || purchaselist.size()<0)
{
return false;
}
String Content = request.getParameter("content");
if (Content==null)
{
Content="";
}
Content = getGbk(Content);
String IP = request.getRemoteAddr();
String TotalPrice = request.getParameter("totalprice"); sqlStr = "select max(id) from My_indent";
rs = stmt.executeQuery(sqlStr);
if (rs.next())
{
IndentNo = "HYD" + userid + "" + rs.getString(1);
} else {
IndentNo = "HYD" + userid + "0";
}
rs.close(); sqlStr = "insert into My_indent (IndentNo,UserId,SubmitTime,ConsignmentTime,TotalPrice,content,IPAddress,IsPayoff,IsSales) values ('";
sqlStr = sqlStr + IndentNo + "','";
sqlStr = sqlStr + userid + "',getdate(),getdate()+7,'";
sqlStr = sqlStr + TotalPrice + "','";
sqlStr = sqlStr + strFormat.toSql(Content) + "','";
sqlStr = sqlStr + IP + "',1,1)";
try
{
stmt.execute(sqlStr);
sqlStr= "select max(id) from My_indent where UserId = " + userid;
rs = stmt.executeQuery(sqlStr);
long indentid = 0;
while (rs.next())
{
indentid = rs.getLong(1);
}
rs.close();
for (int i=0; i<purchaselist.size() ;i++ )
{
indentlist iList = (indentlist) purchaselist.elementAt(i);
sqlStr = "insert into My_indentlist (IndentNo,BookNo,Amount) values (";
sqlStr = sqlStr + indentid + ",'";
sqlStr = sqlStr + iList.getBookNo() + "','";
sqlStr = sqlStr + iList.getAmount() + "')";
stmt.execute(sqlStr);
sqlStr = "update My_book set leav_number=leav_number - " + iList.getAmount() + " where id = " + iList.getBookNo();
stmt.execute(sqlStr);
}
return true;
}
catch (SQLException e)
{
return false;
}
}
boolean flag = false;
try
{
request = newrequest;
String ID = request.getParameter("id");
long bookid = 0;
try
{
bookid = Long.parseLong(ID);
}
catch (Exception e)
{
}
abooks.setId(bookid);
String bookname = request.getParameter("bookname");
if (bookname==null || bookname.equals(""))
{
bookname = "";
sqlflag = false;
}
abooks.setBookName(getGbk(bookname));
String author = request.getParameter("author");
if (author==null || author.equals(""))
{
author = "";
sqlflag = false;
}
abooks.setAuthor(getGbk(author));
String publish = request.getParameter("publish");;
if (publish==null)
{
publish = "";
}
abooks.setPublish(getGbk(publish));
String bookclass = request.getParameter("bookclass");
int bc = Integer.parseInt(bookclass);
abooks.setBookClass(bc);
String bookno = request.getParameter("bookno");
if (bookno == null)
{
bookno = "";
}
abooks.setBookNo(getGbk(bookno));
float price;
try {
price =new Float(request.getParameter("price")).floatValue();
}
catch (Exception e)
{
price = 0;
sqlflag = false;
}
abooks.setPrince(price);
int amount;
try
{
amount = new Integer(request.getParameter("amount")).intValue();
}
catch (Exception e)
{
sqlflag = false;
amount = 0;
}
abooks.setAmount(amount);
String content = request.getParameter("content");
if (content == null)
{
content = "";
}
abooks.setContent(getGbk(content));
if (sqlflag)
{
flag = true;
}
return flag;
}
catch (Exception e)
{
return flag;
}
}
request = res;
String PAGE = request.getParameter("page"); //页码
String classid = request.getParameter("classid"); //分类ID号
String keyword = request.getParameter("keyword"); //查询关键词
if (classid==null) classid="";
if (keyword==null) keyword = "";
keyword = getGbk(keyword).toUpperCase();
try
{
page = Integer.parseInt(PAGE);
}
catch (NumberFormatException e)
{
page = 1;
}
//取出记录数
if (!classid.equals("") && keyword.equals("") ) {
sqlStr = "select count(*) from my_book where bookclass='" + classid + "'";
} else if (!keyword.equals("")) {
if (classid.equals(""))
{
sqlStr = "select count(*) from my_book where upper(bookname) like '%" +keyword+ "%' or upper(content) like '%" + keyword + "%'";
} else {
sqlStr = "select count(*) from my_book where bookclass='" + classid + "' and (upper(bookname) like '%" +keyword+ "%' or upper(content) like '%" + keyword + "%')";
}
} else {
sqlStr = "select count(*) from my_book";
} int rscount = pageSize;
try
{
ResultSet rs1 = stmt.executeQuery(sqlStr);
if (rs1.next()) recordCount = rs1.getInt(1);
rs1.close();
}
catch (SQLException e)
{
return false;
}
//设定有多少pageCount
if (recordCount < 1)
pageCount = 0;
else
pageCount = (int)(recordCount - 1) / pageSize + 1;
//检查查看的页面数是否在范围内
if (page < 1)
page = 1;
else if (page > pageCount)
page = pageCount;
rscount = (int) recordCount % pageSize; // 最后一页记录数 //sql为倒序取值
sqlStr = "select top " + pageSize + " a.id,a.bookname,a.bookclass,b.classname,a.author,a.publish,a.bookno,a.content,a.prince,a.amount,a.Leav_number,a.regtime from My_book a,My_bookclass b where a.Bookclass = b.Id ";
if (!classid.equals("") && keyword.equals("") ){ //如果类别不为空,非查询
if (page == 1)
{
sqlStr = sqlStr + " and a.bookclass='" + classid + "' order by a.Id desc";
} else {
sqlStr = sqlStr + " and a.bookclass='" + classid + "' and a.Id not in ( select TOP " + (recordCount-pageSize * page ) +" Id from My_book order by Id ) and a.Id in " +
"(select TOP " + (recordCount - pageSize * (page-1)) + " Id from My_book ORDER BY Id ) order by a.Id desc";
}
} else if (!keyword.equals("")) { //如果是查询资料
if (page == 1)
{
if (!classid.equals("")) //查询某一类
{
sqlStr = sqlStr + "and a.Bookclass='" + classid + "' and (upper(a.bookname) like '%" +keyword+ "%' or upper(a.content) like '%" + keyword + "%') order by a.Id desc";
} else { //查询所有类
sqlStr = sqlStr + " and (upper(a.bookname) like '%" +keyword+ "%' or upper(a.content) like '%" + keyword + "%') order by a.Id desc";
}
} else {
if (!classid.equals(""))
{
sqlStr = sqlStr + " and a.Bookclass='" + classid + "' and (upper(a.bookname) like '%" +keyword+ "%' or upper(a.content) like '%" + keyword + "%') and a.Id not in ( select TOP " + (recordCount-pageSize * page) + " Id from My_book ORDER BY Id ) and a.Id in " +
"(select TOP " + (recordCount - pageSize * (page-1)) + " Id from My_book ORDER BY Id ) " + " order by a.Id desc";
} else {
sqlStr = sqlStr + " and (upper(a.bookname) like '%" +keyword+ "%' or upper(a.content) like '%" + keyword + "%') and a.Id not in ( select TOP " + (recordCount-pageSize * page) + " Id from My_book ORDER BY Id ) and a.Id in " +
"(select TOP " + (recordCount - pageSize * (page-1)) + " Id from My_book ORDER BY Id ) " + " order by a.Id desc";
}
} } else { //非查询,也非分类浏览
if (page == 1)
{
sqlStr = sqlStr + " order by a.Id desc";
} else {
sqlStr = sqlStr + " and a.Id not in ( select TOP " + (recordCount-pageSize * page ) + " Id from My_book ORDER BY Id ) and a.Id in " +
"(select TOP " + (recordCount - pageSize * (page-1)) + " Id from My_book ORDER BY Id) order by a.Id desc";
}
} try
{
rs = stmt.executeQuery(sqlStr);
booklist = new Vector(rscount);
while (rs.next())
{
books book = new books();
book.setId(rs.getLong("id"));
book.setBookName(rs.getString("bookname"));
book.setBookClass(rs.getInt("bookclass"));
book.setClassname(rs.getString("classname"));
book.setAuthor(rs.getString("author"));
book.setPublish(rs.getString("publish"));
book.setBookNo(rs.getString("Bookno"));
book.setContent(rs.getString("content"));
book.setPrince(rs.getFloat("prince"));
book.setAmount(rs.getInt("amount"));
book.setLeav_number(rs.getInt("leav_number"));
book.setRegTime(rs.getString("regtime"));
booklist.addElement(book);
}
rs.close();
return true;
}
catch (SQLException e)
{
System.out.println(e);
return false;
}
} public boolean insert() throws Exception {
sqlStr = "insert into my_book (Bookname,Bookclass,Author,Publish,Bookno,Content,Prince,Amount,Leav_number,Regtime) values ('";
sqlStr = sqlStr + strFormat.toSql(abooks.getBookName()) + "','";
sqlStr = sqlStr + abooks.getBookClass() + "','";
sqlStr = sqlStr + strFormat.toSql(abooks.getAuthor()) + "','";
sqlStr = sqlStr + strFormat.toSql(abooks.getPublish()) + "','";
sqlStr = sqlStr + strFormat.toSql(abooks.getBookNo()) + "','";
sqlStr = sqlStr + strFormat.toSql(abooks.getContent()) + "','";
sqlStr = sqlStr + abooks.getPrince() + "','";
sqlStr = sqlStr + abooks.getAmount() + "','";
sqlStr = sqlStr + abooks.getAmount() + "',";
sqlStr = sqlStr + "getdate())";
try
{
stmt.execute(sqlStr);
return true;
}
catch (SQLException sqle)
{
return false;
}
} public boolean update() throws Exception {
sqlStr = "update my_book set ";
sqlStr = sqlStr + "bookname = '" + strFormat.toSql(abooks.getBookName()) + "',";
sqlStr = sqlStr + "bookclass = '" + abooks.getBookClass() + "',";
sqlStr = sqlStr + "Author = '" + strFormat.toSql(abooks.getAuthor()) + "',";
sqlStr = sqlStr + "publish = '" + strFormat.toSql(abooks.getPublish()) + "',";
sqlStr = sqlStr + "bookno = '" + strFormat.toSql(abooks.getBookNo()) + "',";
sqlStr = sqlStr + "content = '" + strFormat.toSql(abooks.getContent()) + "',";
sqlStr = sqlStr + "prince = '" + abooks.getPrince() + "',";
sqlStr = sqlStr + "Amount = '" + abooks.getAmount() + "',";
sqlStr = sqlStr + "leav_number = '" + abooks.getAmount() + "' ";
sqlStr = sqlStr + "where id = '" + abooks.getId() + "'";
System.out.println(sqlStr);
try
{
stmt.execute(sqlStr);
return true;
}
catch (SQLException e)
{
return false;
}
}
DAO的操作直接调用就可以操作数据库建议不要一口气吃成胖子