userdaojdbcimpl.java

shoppingCar 购物车

package com.tarena.shoppingcar.dao;

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.tarena.shoppingcar.entity.User;
import com.tarena.shoppingcar.util.ConnectionFactory;
import com.tarena.shoppingcar.util.JdbcUtil;

public class UserDaoJdbcImpl implements UserDao {

	public User find(String userName, String password) {
		User user = new User();
		user.setUserName(userName);
		user.setPassword(password);
		List<User> users = findAll(user);
		if(users.size() == 0){
			return null;
		}else {
			return users.iterator().next();
		}
	}

	public List<User> findAll() {
		return findAll(new User());
	}

	public List<User> findAll(User user) {
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<User> users = null;
		try {
			con = ConnectionFactory.getConnection();
			StringBuffer sb = new StringBuffer();
			sb.append("select id,userName,password,address,postCode,email,homePhone,cellPhone,officePhone");
			sb.append(" from user where 1=1");
			if(user.getId() != 0){
				sb.append(" and id=?");
			}
			if(user.getUserName() != null && user.getUserName().trim().length() > 0){
				sb.append(" and userName=?");
			}
			if(user.getPassword() != null && user.getPassword().trim().length() > 0){
				sb.append(" and password=?");
			}
			int index=1;
			pstmt = con.prepareStatement(sb.toString());
			if(user.getId() != 0){
				pstmt.setInt(index++, user.getId());
			}
			if(user.getUserName() != null && user.getUserName().trim().length() > 0){
				pstmt.setString(index++, user.getUserName());
			}
			if(user.getPassword() != null && user.getPassword().trim().length() > 0){
				pstmt.setString(index++, user.getPassword());
			}
			rs = pstmt.executeQuery();
			users = getUsers(rs);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JdbcUtil.close(con,pstmt,rs);
		}
		return users;
	}
	public List<User> getUsers(ResultSet rs) throws SQLException{
		List<User> users = new ArrayList<User>();
		while(rs.next()){
			User user = new User();
			user.setId(rs.getInt(1));
			user.setUserName(rs.getString(2));
			user.setPassword(rs.getString(3));
			user.setAddress(rs.getString(4));
			user.setPostCode(rs.getString(5));
			user.setEmail(rs.getString(6));
			user.setHomePhone(rs.getString(7));
			user.setCellPhone(rs.getString(8));
			user.setOfficePhone(rs.getString(9));
			users.add(user);
		}
		return users;
	}
	
	public void modify(User user) {
		Connection con = null;
		PreparedStatement pstmt = null;
		try {
			con = ConnectionFactory.getConnection();
			String sql = "update user set address=?,postCode=?,email=?,homePhone=? where id=?";
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, user.getAddress());
			pstmt.setString(2, user.getPostCode());
			pstmt.setString(3, user.getEmail());
			pstmt.setString(4, user.getHomePhone());
			pstmt.setInt(5, user.getId());
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public static void main(String[] args) {
		UserDaoJdbcImpl dao = new UserDaoJdbcImpl();
		List<User> users = dao.findAll(new User());
		System.out.print(users.size());
	}

	public void save(User user) {
		Connection con = null;
		PreparedStatement pstmt = null;
		try {
			con = ConnectionFactory.getConnection();
			String sql = "insert into user(userName,password,address,postCode,email,homePhone,cellPhone,officePhone) values(?,?,?,?,?,?,?,?)";
			pstmt = con.prepareStatement(sql);
			int index = 1;
			pstmt.setString(index++, user.getUserName());
			pstmt.setString(index++, user.getPassword());
			pstmt.setString(index++, user.getAddress());
			pstmt.setString(index++, user.getPostCode());
			pstmt.setString(index++, user.getEmail());
			pstmt.setString(index++, user.getHomePhone());
			pstmt.setString(index++, user.getCellPhone());
			pstmt.setString(index++, user.getOfficePhone());
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JdbcUtil.close(con, pstmt);
		}
	}

	public List<User> findByPage(int StartRow, int rowPerPage) {
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<User> users = null;
		try {
			con = ConnectionFactory.getConnection();
			String sql = "select id,userName,password,address,postCode,email,homePhone," +
					"cellPhone,officePhone from user limit ?,?";
			pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, StartRow);
			pstmt.setInt(2, rowPerPage);
			rs = pstmt.executeQuery();
			users = getUsers(rs);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JdbcUtil.close(con, pstmt, rs);
		}
		return users;
	}
	public int findNum(){
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		int num = -1;
		try {
			con = ConnectionFactory.getConnection();
			String sql = "select count(id) from user";
			pstmt = con.prepareStatement(sql);
			rs = pstmt.executeQuery();
			num = rs.getInt(1);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			JdbcUtil.close(con, pstmt, rs);
		}
		
		return num;
	}

	public User findById(int id) {
		User user = new User();
		user.setId(id);
		List<User> users = findAll(user);
		if(users.size() == 0){
			return null;
		}else {
			return users.iterator().next();
		}
	}

	public List<User> findByName(String userName) {
		List<User> users = new ArrayList<User>();
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			con = ConnectionFactory.getConnection();
			String sql = "select id,userName,password,address,postCode,email,homePhone," +
					"cellPhone,officePhone from user where userName=?";
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, userName);
			rs = pstmt.executeQuery();
			User user = new User();
			rs.next();
//			System.out.println(rs.next());
			user.setId(rs.getInt(1));
			user.setUserName(rs.getString(2));
			user.setPassword(rs.getString(3));
			user.setAddress(rs.getString(4));
			user.setPostCode(rs.getString(5));
			user.setEmail(rs.getString(6));
			user.setHomePhone(rs.getString(7));
			user.setCellPhone(rs.getString(8));
			user.setOfficePhone(rs.getString(9));
			users.add(user);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return users;
	}
}