userdao.java

JSP网上书店系统,有完整的程序,可以直接使用,无需修改

package com.tsinghua.dao;

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 com.tsinghua.db.DB;
import com.tsinghua.vo.Revert;
import com.tsinghua.vo.Words;
import com.tsinghua.vo.user;

public class UserDAO {
    
	//删除用户
	 public static int getdelete(int userid){
    	 Connection conn=null;
    	 PreparedStatement pstmt=null;
    	 int i=0;
    	     try {
			     conn=DB.getconn();
				 String sql="delete from users where userid="+userid+"";
				 pstmt=DB.getpset(conn, sql);
				 i=pstmt.executeUpdate();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			finally{
   	    	 try {
				pstmt.close();
				 conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
   	     }
             return i;
    }
	 //查看用户
	 public static List getselect(){
		  Connection conn=null;
		  ResultSet rs=null;
		  Statement set=null;
		  List list=null;
		     try {
		    	 list=new ArrayList();
				conn=DB.getconn();
				 set=DB.getset(conn);
				 String sql="select * from users";
				 rs=DB.getrs(set, sql);
				 while(rs.next()){
					user u=new user();
					u.setUserid(rs.getInt(1));
					u.setUsername(rs.getString(2));
					u.setPassword(rs.getString(3));
					u.setBrithday(rs.getString(4));
					u.setPhone(rs.getString(5));
					u.setPost(rs.getString(6));
					u.setEmail(rs.getString(7));
					u.setAddress(rs.getString(8));
					list.add(u);
				 }
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally{
			  try {
				set.close();
				  rs.close();
				  conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			}
		 return list;
	 }
//	用户注册
		public int geterion(user u){
			Connection conn=null;
			ResultSet rs=null;
			PreparedStatement pset=null;
		    int i=0;
			   try {
				conn =DB.getconn();
				   String sql="insert into users values(?,?,?,?,?,?,?)";
				   pset =DB.getpset(conn, sql);
				   pset.setString(1, u.getUsername());
				   pset.setString(2, u.getPassword());
				   pset.setString(3, u.getBrithday());
				   pset.setString(4, u.getPhone());
				   pset.setString(5, u.getPost());
				   pset.setString(6, u.getEmail());
				   pset.setString(7, u.getAddress());
				   i=pset.executeUpdate();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally{
			     try {
					pset.close();

					 conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			     
			}
			return i;
		}
//		用户登录
		public user erion(user u){
			Connection conn=null;
			ResultSet rs=null;
			Statement semt=null;
			 user  us=null;
			int i=0;
			   try {
				conn=DB.getconn();
				   semt=DB.getset(conn);
				   String sql="select * from users "+"where username='"+u.getUsername()+"'  and password='"+u.getPassword()+"'";
				   rs=DB.getrs(semt, sql);
				   while(rs.next()){
					  us=new user();
					  us.setUserid(rs.getInt(1));
					  us.setUsername(rs.getString(2));
					  us.setPassword(rs.getString(3));
					
				   }
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			  finally{
				   try {
					semt.close();
					   rs.close();
					   conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			   }
			return us;
		}
		//用户留言 
		public static int getmotif(Words w){
			Connection conn=null;
			ResultSet rs=null;
			PreparedStatement psemt=null;
			int i=0;
			  try {
				conn=DB.getconn();
				  String sql="insert into words(username,wordsmotif,wordsrow) values(?,?,?)";
				  psemt=DB.getpset(conn, sql);
				  psemt.setString(1, w.getUserName());
				  psemt.setString(2, w.getWordsMotif());
				  psemt.setString(3, w.getWordRow());
				  i=psemt.executeUpdate();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		  finally{
				 try {
					psemt.close();
					 conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			
		  }
			return i;
		}
	 //显示留言
		public static List getseeword(){
			Connection conn=null;
			ResultSet rs=null;
			Statement met=null;
			List list=null;
			try {
				conn=DB.getconn();
				met=DB.getset(conn);
				String sql="select * from  words";
				rs=DB.getrs(met, sql);
				list = new ArrayList();
				while(rs.next()){
				Words w=new Words();
				   w.setId(rs.getInt(1));
				   w.setUserName(rs.getString(2));
				   w.setWordsMotif(rs.getString(3));
				   w.setWordRow(rs.getString(4));
				   w.setRevert(rs.getString(5));
				   list.add(w);
				  }
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally{
				try {
					met.close();
					rs.close();
					conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			return list;
		}
		//显示管理员回复留言
		public static int update(Words words){
			int i=0;
			Connection conn=null;
			PreparedStatement pstmt=null;
			
			  try {
				conn=DB.getconn();
				 String sql="update words set revert=? where id=?";
				 pstmt=DB.getpset(conn, sql);
				 pstmt.setString(1, words.getRevert());
				 pstmt.setInt(2, words.getId());
				 
				i=pstmt.executeUpdate();
				 
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			 finally{
			      try {
					pstmt.close();
					  conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			   
			  }
			 
			return i;
		}
		//插入管理员的回复留言
		public static int gethuifu(){
		    Connection  conn=null;
		    PreparedStatement pmet=null;
		    int i=0;
		     try {
				conn=DB.getconn();
				 String sql="insert into revert values(?)";
				 pmet=DB.getpset(conn, sql);
				 Revert r=new Revert();
				 pmet.setString(1, r.getRevert());
				 i=pmet.executeUpdate();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		     finally{ 
		    	 try {
					pmet.close();
					 conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		     }
			return i;
		}
		//删除留言
		public static int deleteword(int id){
			Connection conn=null;
            PreparedStatement pmet=null;
             int i=0;
			try {
				conn=DB.getconn();
				 String sql="delete from words where id="+id+"";
				 pmet=DB.getpset(conn, sql);
				 i = pmet.executeUpdate();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			finally{
				try {
					pmet.close();
					conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				
			}
			return i;
		}
}