accountservice.java

使用jsp+Servlet,采用MVC模式,实现了一典型小型电子商务网站的全过程.包括前台和后台的全部功能.适合于初学者学习使用.

/**
 * 
 */
package com.eshop.service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.eshop.util.DBManager;
import com.eshop.vo.Account;
import com.eshop.vo.Product;

/**
 * @author jonson
 *
 */

public class AccountService {
	String selectById="select * from product where userid=?";
	/**
	 * 创建新帐用户
	*/
	public static boolean accountSave(Account account){
		boolean success=false;
		DBManager dbManager=new DBManager();
		Connection conn=dbManager.GetConnection();
		try {
//			Statement stm=conn.createStatement();
			PreparedStatement ps=conn.prepareStatement("INSERT INTO account(userid,password,email,name,status,addr1,addr2,city,state,zip,country,phone)values(?,?,?,?,?,?,?,?,?,?,?,?)");
			ps.setString(1, account.getUserid());
			ps.setString(2, account.getPassword());
			ps.setString(3, account.getEmail());
			ps.setString(4,account.getName());
			ps.setString(5, account.getStatus());
			ps.setString(6, account.getAddr1());
			ps.setString(7, account.getAddr2());
			ps.setString(8,account.getCity());
			ps.setString(9, account.getState());
			ps.setString(10, account.getZip());
			ps.setString(11,account.getCountry());
			ps.setString(12,account.getPhone());
			ps.executeUpdate();
			ps.close();
			conn.commit();
			success=true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			
			e.printStackTrace();
			try{
				conn.rollback();
			}catch(SQLException ex){
				ex.printStackTrace();				
			}
		}
		return success;
	
	} 
	/**
	 * 用户登录，进行用户名和密码验证
	 */
	public boolean signon(String userid,String password){
		boolean success=false;
		DBManager dbManager=new DBManager();
		Connection conn=dbManager.GetConnection();
		try {
			PreparedStatement ps=conn.prepareStatement("select * from account where userId=? and password=?");
			ps.setString(1, userid);
			ps.setString(2, password);
			ResultSet rs=ps.executeQuery();
			if(rs.next()){
				success=true;
			}
			conn.close();
						
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return success;		
	}
	/**
	 * 获得所有用户信息
	 * @return
	 */
	public List getAllUserInfo(){
		List list=new ArrayList();
		DBManager dbManager= new DBManager();
		Connection conn=dbManager.GetConnection();
		try {
			PreparedStatement ps=conn.prepareStatement("select * from account where status='1' order by userid desc");
			ResultSet rs=ps.executeQuery();
			while (rs.next()){
				Account accountForm=new Account();
				accountForm.setUserid(rs.getString("userid"));
				accountForm.setPassword(rs.getString("password"));
				accountForm.setEmail(rs.getString("email"));
				accountForm.setName(rs.getString("name"));
				accountForm.setStatus(rs.getString("status"));
				accountForm.setAddr1(rs.getString("addr1"));
				accountForm.setAddr2(rs.getString("addr2"));
				accountForm.setCity(rs.getString("city"));
				accountForm.setState(rs.getString("state"));
				accountForm.setZip(rs.getString("zip"));
				accountForm.setCountry(rs.getString("country"));
				accountForm.setPhone(rs.getString("phone"));				
				list.add(accountForm);
			}
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
	/**
	 * 获得所有用户信息
	 * @return
	 */
	public List getAllCommonUserInfo(){
		List list=new ArrayList();
		DBManager dbManager= new DBManager();
		Connection conn=dbManager.GetConnection();
		try {
			PreparedStatement ps=conn.prepareStatement("select * from account where status='0' order by userid desc");
			ResultSet rs=ps.executeQuery();
			while (rs.next()){
				Account accountForm=new Account();
				accountForm.setUserid(rs.getString("userid"));
				accountForm.setPassword(rs.getString("password"));
				accountForm.setEmail(rs.getString("email"));
				accountForm.setName(rs.getString("name"));
				accountForm.setStatus(rs.getString("status"));
				accountForm.setAddr1(rs.getString("addr1"));
				accountForm.setAddr2(rs.getString("addr2"));
				accountForm.setCity(rs.getString("city"));
				accountForm.setState(rs.getString("state"));
				accountForm.setZip(rs.getString("zip"));
				accountForm.setCountry(rs.getString("country"));
				accountForm.setPhone(rs.getString("phone"));				
				list.add(accountForm);
			}
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
	public boolean deleteUser(Account account){
		boolean success=false;
		DBManager dbManager= new DBManager();
		Connection conn=dbManager.GetConnection();
		try {
//			Statement stm=conn.createStatement();
			PreparedStatement ps=conn.prepareStatement
			("delete from account where userid='"+account.getUserid()+"'");
			ps.executeUpdate();
			ps.close();
			success=true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block			
			e.printStackTrace();
		}
		return success;
		
	}
	public Account getAccountById(String userid){
		Account form=new Account();		
		DBManager db=new DBManager();
		Connection conn=db.GetConnection();
		PreparedStatement ps;
		try {
			ps = conn.prepareStatement("select userid,password,email,name,status,addr1,addr2,city,state,zip,country,phone from Account where userid=?");
			ps.setString(1, userid);
			
			ResultSet rs=ps.executeQuery();
			while(rs.next()){
				form.setUserid(rs.getString("userid"));
				form.setPassword(rs.getString("password"));
				form.setEmail(rs.getString("email"));
				form.setName(rs.getString("name"));
				form.setStatus(rs.getString("status"));
				form.setAddr1(rs.getString("addr1"));
				form.setAddr2(rs.getString("addr2"));
				form.setCity(rs.getString("city"));
				form.setState(rs.getString("state"));
				form.setZip(rs.getString("zip"));
				form.setCountry(rs.getString("country"));
				form.setPhone(rs.getString("phone"));
	
			}
			ps.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
				
		return form;
	}
	public boolean updateAccount(Account form){
		boolean success=false;
		DBManager db=new DBManager();
		Connection conn=db.GetConnection();
		PreparedStatement ps;
		try {
			ps = conn.prepareStatement("update account set userid=?,password=?,email=?," +
					"name=?,status=?," +
					"addr1=?,addr2=?,city=?,state=?,zip=?,country=?,phone=?" +
					" where userid=?");
			ps.setString(1, form.getUserid());
			ps.setString(2, form.getPassword());
			ps.setString(3, form.getEmail());
			ps.setString(4,form.getName());
			ps.setString(5,form.getStatus());
			ps.setString(6, form.getAddr1());
			ps.setString(7, form.getAddr2());
			ps.setString(8,form.getCity());
			ps.setString(9, form.getState());
			ps.setString(10, form.getZip());
			ps.setString(11,form.getCountry());
			ps.setString(12,form.getPhone());
			ps.setString(13,form.getUserid());
			ps.executeUpdate();
			ps.close();
			conn.commit();
			success=true;
			ps.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}				
		return success;
		
	}
	/**
	 * 获得用户状态，以判断为普通用户或是管理员用户
	 * @param userid
	 * @param password
	 * @return
	 */
	public int getUserStatus(String userid,String password){
		String success="0";
		DBManager dbManager=new DBManager();
		Connection conn=dbManager.GetConnection();
		PreparedStatement ps;
		try {
			ps = conn.prepareStatement("select status from account where userId=? and password=?");
			ps.setString(1, userid);
			ps.setString(2, password);
			ResultSet rs=ps.executeQuery();
			if(rs.next()){
				success=rs.getString("status").trim();
			}
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		int value=Integer.parseInt(success);
		return value;
		
	}
	/**
	 * 根据产品ID查询商品信息
	 * */
	public Account getById(String userid){
		Account account=null;
		DBManager dbManager= new DBManager();
		Connection conn=dbManager.GetConnection();
		try
		{
			PreparedStatement pstmt=conn.prepareStatement(selectById);
			pstmt.setString(1,userid);
			ResultSet rs=pstmt.executeQuery();
          
			while(rs.next())
			{
				
				account=new Account();
				account.setUserid(rs.getString("userid"));
				//account.setCategory(rs.getString("category"));
				account.setName(rs.getString("name"));
				//account.setDescn(rs.getString("descn"));
			}
				conn.close();
		}
		catch(SQLException e)
		{
			e.printStackTrace();
		}				
		return account;
	}

	

}