userdao.java

整个系统采用MVC开发模式

package bean;

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

import java.sql.Statement;





public class UserDAO {
	// 取得一个数据库连接
	public Connection getConnection()throws SQLException,InstantiationException,IllegalAccessException,
	       ClassNotFoundException{
		
		Connection conn = null;
		Class.forName("com.mysql.jdbc.Driver").newInstance();
//		String url = "jdbc.mysql://192.168.2.164:3306/shopping";
//		String user = "D6";
//		String password = "123";
		String url = "jdbc:mysql://localhost/shopping?user=admin&password=admin";
		conn = DriverManager.getConnection(url);
		return conn;
		
	}
	// 根据传入的SQL语句，返回一个对象的链表
	public ArrayList userSelect(String sql)throws Exception{
		ArrayList<User> result = new ArrayList<User>();
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		try{
			conn = getConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			while(rs.next()){
				User user = new User();
				user.setUserid(rs.getInt("userid"));
				user.setUsername(rs.getString("username"));
				user.setPassword(rs.getString("password"));
				user.setEmail(rs.getString("email"));
				user.setNickname(rs.getString("nickname"));
				user.setSex(rs.getString("sex"));
				user.setBirthday(rs.getString("birthday"));
				user.setBlog(rs.getString("blog"));
				user.setInterest(rs.getString("interest"));
				user.setIntroduce(rs.getString("introduce"));
				user.setAddress(rs.getString("address"));
				user.setPhone(rs.getString("phone"));
				user.setPostalcode(rs.getInt("postalcode"));
				result.add(user);
				
			}
			
		}catch (SQLException  sqle){
			throw new SQLException("select data exception: "+ sqle.getMessage());
			
			
		}catch(Exception e){
			throw new Exception("System e exception: "+e.getMessage());
			
		}finally{
			try{
				if(rs != null){
					rs.close();
				}
			}catch(Exception e){
				throw new Exception("statement close exception: "+ e.getMessage());
			}
			try{
				if(stmt !=null){
					stmt.close();
				}
			}catch (Exception e){
				throw new Exception("statement close exception: "+e.getMessage());
			}
			try{
				if(conn != null){
					conn.close();
				}
			}catch(Exception e){
				throw new Exception("cennection close exception: "+ e.getMessage());
			}
		}
		return result;
		
	}
	// 根据传入的对象向数据库插入一条记录
	public void userInsert(User user) throws Exception{
		Connection conn = null;
		PreparedStatement ps = null;
		// 根据user 对象的属性生成SQL语句
		String sql = "insert into user(username,password,email,nickname,sex,birthday,blog,interest,introduce,address,phone,postalcode) values('"+ user.getUsername()+"','"+ user.getPassword()+"','"+user.getEmail()+"','"+user.getNickname()+"','"+user.getSex()+"','"+user.getBirthday()+"','"+user.getBlog()+"','"+user.getInterest()+"','"+user.getIntroduce()+"','"+user.getAddress()+"','"+user.getPhone()+"','"+user.getPostalcode()+"')";
		try{
			conn = getConnection();
			ps = conn.prepareStatement(sql);
			ps.executeUpdate();
		}catch(SQLException sqle){
			throw new Exception ("insert data exception "+ sqle.getMessage());
			
		}finally{
			try{
				if(ps != null){
					ps.close();
				}
			}catch(Exception e){
				throw new Exception("statement close exception: "+ e.getMessage());
			}
			
			try{
				if(conn != null){
					conn.close();
				}
			}catch(Exception e){
				throw new Exception("cennection close exception: "+ e.getMessage());
			}
					
		}
		
	}
	// 根据传入的对象更新数据库记录
	public void userUpdate(User user) throws Exception{
		Connection conn = null;
		PreparedStatement ps =null;
		// 根据user对象的属性生成SQL语句
		String sql = "update user set password='"+user.getPassword()+"',email='"+user.getEmail()+"',nickname='"+user.getNickname()+"',sex='"+user.getSex()+"',birthday='"+user.getBirthday()+"',blog='"+user.getBlog()+"',interest='"+user.getInterest()+"',introduce='"+user.getIntroduce()+"',address='"+user.getAddress()+"',phone='"+user.getPhone()+"',postalcode='"+user.getPostalcode()+"'where username='"+user.getUsername()+"'";
		try{
			conn = getConnection();
			ps = conn.prepareStatement(sql);
			ps.executeUpdate();
		}catch(SQLException sqle){
			throw new Exception ("update data exception "+ sqle.getMessage());
			
		}finally{
			try{
				if(ps != null){
					ps.close();
				}
			}catch(Exception e){
				throw new Exception("statement close exception: "+ e.getMessage());
			}
			
			try{
				if(conn != null){
					conn.close();
				}
				}catch(Exception e){
				throw new Exception("cennection close exception: "+ e.getMessage());
			}
			}
		
}
	public void userDelete(User user) throws Exception{
		Connection conn = null;
		PreparedStatement ps =null;
		try{
			conn = getConnection();
		    String sql="delete from user where username='"+user.getUsername()+"'";
		    ps = conn.prepareStatement(sql);
		    ps.executeUpdate();
		}catch(SQLException sqle){
			throw new Exception("delet data exception: "+ sqle.getMessage());
		}finally{
			try{
				if(ps != null){
					ps.close();
				}
			}catch(Exception e){
				throw new Exception("statement close exception: "+ e.getMessage());
			}
			
			try{
				if(conn != null){
					conn.close();
				}
				}catch(Exception e){
				throw new Exception("cennection close exception: "+ e.getMessage());
			}
		
	}
}
}