userservice.java

基于mysql5.0完成的shopping网站后台

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.shopping.model;

import com.shopping.util.DBUtil;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Administrator
 */
public class UserService {

    ///新建;删除；浏览;;
    //由于我们希望，对象一个够;
    private static UserService instance = null;

    private UserService() {
    }

    public static UserService getInstance() {
        if (instance == null) {
            instance = new UserService();
        }
        return instance;
    }

    //.................................
    public boolean save(User u) {
        Connection conn = null;
        Statement stmt = null;
        PreparedStatement pstmt = null;
        int rs = -1;
        String sql = "insert into user values(null,?,?,?,?,now())";


        try {
            conn = DBUtil.getConnection();
            // stmt = DBUtil.getPStatement(conn, sql);
            pstmt = DBUtil.getPStatement(conn, sql);
            pstmt.setString(1, u.getUsername());
            pstmt.setString(2, u.getPassword());
            pstmt.setString(3, u.getPhone());
            pstmt.setString(4, u.getAddr());

            rs = pstmt.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            DBUtil.close(conn, stmt, null);

        }
        if (rs == 0) {
            return false;
        } else {
            return true;
        }
    }

    public List<User> getUsers() {

        Connection conn = null;
        Statement stmt = null;

        ResultSet rs = null;
        String sql = "select * from user";
        List<User> list = new ArrayList<User>();
        try {
            conn = DBUtil.getConnection();
            stmt = DBUtil.getStatement(conn);
            rs = DBUtil.query(stmt, sql);
            while (rs.next()) {
                User u = new User();
                u.setId(rs.getInt("id"));
                u.setUsername(rs.getString("username"));
                u.setPassword(rs.getString("password"));
                u.setPhone(rs.getString("phone"));
                u.setRdate(rs.getTimestamp("rdate"));
                u.setAddr(rs.getString("addr"));
                list.add(u);
            }

        } catch (Exception e) {
            e.printStackTrace();

        } finally {
            DBUtil.close(conn, stmt, null);

        }
        return list;
    }

    public boolean dalete(int id) {


        Connection conn = null;
        Statement stmt = null;
        boolean rs = false;
        String sql = "delete from  user where id = " + id;
        System.out.println(sql);
        try {
            conn = DBUtil.getConnection();
            stmt = DBUtil.getStatement(conn);
            int is = stmt.executeUpdate(sql);
            if (is == 0) {
                rs = false;
            } else {
                rs = true;
            }
        } catch (SQLException ex) {
            Logger.getLogger(UserService.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            DBUtil.close(null, stmt, null);
        }
        return rs;

    }

//    public List<User> getUsers(int pageNo,int pageSize) {
//    
//    }

    public static int getTotalPage(int pageSize) {
    

            Connection conn = null;
            Statement stmt = null;
            ResultSet rs = null;
            String sql = "select count(*) from user";
            int num = -1;


            conn = DBUtil.getConnection();
            stmt = DBUtil.getStatement(conn);
            rs = DBUtil.query(stmt, sql);
    try {
            rs.next();
            num = rs.getInt(1);

            if (num % pageSize == 0) {
                num = num / pageSize;
            } else {
                num = num / pageSize + 1;
            }

            return num;
        } catch (SQLException ex) {
            Logger.getLogger(UserService.class.getName()).log(Level.SEVERE, null, ex);
        }finally{
        DBUtil.close(conn, stmt, rs);
        }
              return num;
    }
    
    
    
       public static List<User> getUser(int pageNo,int pageSize) {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        pageNo = (pageNo - 1) * pageSize;
        
        String sql = "select * from user  limit "+pageNo+" , "+pageSize;
System.out.println(sql);
        List<User> list = new ArrayList<User>();

        try {
            conn = DBUtil.getConnection();
            stmt = DBUtil.getStatement(conn);
            rs = DBUtil.query(stmt, sql);

            while (rs.next()) {
                User u = new User();
                u.setId(rs.getInt("id"));
                u.setUsername(rs.getString("username"));
                u.setPassword(rs.getString("password"));
                u.setPhone(rs.getString("phone"));
                u.setRdate(rs.getTimestamp("rdate"));
                u.setAddr(rs.getString("addr"));
                list.add(u);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, stmt, rs);
        }

        return list;

    }

    //validate(User u)
    
     public void validate(String uName,String uPwd)
     throws UserNotFoundException,PasswordNotCorrectException{
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        String sql = "select password from user where username = '"+uName+"'";

        conn = DBUtil.getConnection();
        stmt = DBUtil.getStatement(conn);
        rs = DBUtil.query(stmt, sql);
        
        try{
        
        if(!rs.next()){
          throw new UserNotFoundException("用户不存在");
        }
        
        String pwd = rs.getString(1);
        
        if(!uPwd.equals(pwd)){
          throw new PasswordNotCorrectException("密码错误");
        }
        
        }catch(SQLException e){
            e.printStackTrace();;
        }finally{
          DBUtil.close(conn, stmt, rs);
        }

   }
   public User loadById(int id){
   
        Connection conn = null;
        Statement stmt = null;

        ResultSet rs = null;
        String sql = "select * from user where id ="+id;
         User u = new User();
        try {
            conn = DBUtil.getConnection();
            stmt = DBUtil.getStatement(conn);
            rs = DBUtil.query(stmt, sql);
            while (rs.next()) {
               
                u.setId(rs.getInt("id"));
                u.setUsername(rs.getString("username"));
                u.setPassword(rs.getString("password"));
                u.setPhone(rs.getString("phone"));
                u.setAddr(rs.getString("addr"));
            }

        } catch (Exception e) {
            e.printStackTrace();

        } finally {
            DBUtil.close(conn, stmt, null);

        }
        return u;
   }
    
    
    
    //public boolean save(String username,String password,String phone,String addr){
    //}
    public static void main(String[] args) {
        UserService us = null;
        us = UserService.getInstance();
      User u = new User();
      u.setUsername("tom");
      u.setPassword("tom");
      u.setPhone("234234");
      u.setAddr("china");
      
     System.out.println(us.save(u));
//        
//    
//    
//        System.out.println(us.getUsers());
//            us.dalete(1);
//      System.out.print(us.getTotalPage(2));
//        System.out.print(us.loadById(2));
//        System.out.print(us.getUser(1, 3));
    }
}