member_dao.java

这是一个Struts网上商城(图书)的管理系统源代码.

package com.bookshop.dao;
import com.bookshop.db.*;
import com.bookshop.dto.member_dto;
import java.sql.*;
import java.util.*;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpSession;
public class member_dao {
	//添加用户
	public int member_insert(member_dto new_member)
	{
		Connection  conn=DBConnection.getConnection();
		StringBuffer sb=new StringBuffer();
		sb.append("insert into tb_member(username,truename,password,city,address,postcode,cardno,cardtype,grade,amount,tel,email,freeze)values(?,?,?,?,?,?,?,?,?,?,?,?,?)");
		PreparedStatement ps=null;
		int re=0;
		try{
			ps=conn.prepareStatement(sb.toString());
			ps.setString(1, new_member.getUsername());
			ps.setString(2, new_member.getTruename());
			ps.setString(3, new_member.getPassword());
			ps.setString(4, new_member.getCity());
			ps.setString(5, new_member.getAddress());
			ps.setString(6, new_member.getPostcode());
			ps.setString(7, new_member.getCardno());
			ps.setString(8, new_member.getCardtype());
			ps.setString(9, "0");
			ps.setFloat(10, 0);
			ps.setString(11, new_member.getTel());
			ps.setString(12, new_member.getEmail());
			ps.setString(13,"0");
			
			
			conn.setAutoCommit(false);
			re=ps.executeUpdate();
			conn.commit();
			
			return re;
			
		}catch(SQLException e){
			e.printStackTrace();
			System.out.print(e.getMessage());
		return 0;
		}
		finally {
			try{
			ps.close();
			conn.close();
			}catch(SQLException ex){ex.printStackTrace();}
		}
	}
	//用户登陆
	public int mem_login(String username,String password,HttpServletRequest request)
	{
		Connection conn=DBConnection.getConnection();
		int n=0;
		ResultSet rs=null;
		Statement st=null;
		String sql="";
		try{
			st=conn.createStatement();
			sql="select * from tb_member where username='"+username+"' and password='"+password+"' and freeze=0";
			rs=st.executeQuery(sql);
			HttpSession session=request.getSession();
			if(rs.next())
			{
				
				session.setAttribute("username",rs.getString("username"));
				session.setAttribute("password",rs.getString("password"));
				session.setAttribute("amount",Float.parseFloat(rs.getString("amount")));
				
				n=1;
				
			}
		}catch(SQLException e){e.printStackTrace();}
		finally{
			try{
				rs.close();
				st.close();
				conn.close();
		
			}catch(SQLException ex){ex.printStackTrace();}
			
		}
		return n;
		
	}
	//检测用户
	public int test(String username)
	{
	    Connection conn=DBConnection.getConnection();
	    ResultSet rs=null;
	    Statement st=null;
	    String sql="";
	    int n=0;
	    try{
	    	sql="select * from tb_member where username='"+username+"'";
	    	st=conn.createStatement();
	    	rs=st.executeQuery(sql);
	    	if(rs.next())
	    	n=1;
	    }catch(SQLException e){e.printStackTrace();return 0;}
		finally{
			try{
				rs.close();
				st.close();
				conn.close();
			}catch(SQLException ex){ex.printStackTrace();}
		}
		return n;
	}
	//修改查询
	public member_dto sel(String username)
	{
		Connection conn=DBConnection.getConnection();
		Statement st=null;
		ResultSet rs=null;
		member_dto dto=new member_dto();
		String sql="";
		try{
			
			sql="select * from tb_member where username='"+username+"'";
			st=conn.createStatement();
			rs=st.executeQuery(sql);
			
			if(rs.next())
			{
				dto.setUsername(rs.getString("username"));
				dto.setTruename(rs.getString("truename"));
				dto.setPassword(rs.getString("password"));
				dto.setCardtype(rs.getString("cardtype"));
				dto.setCardno(rs.getString("cardno"));
				dto.setTel(rs.getString("tel"));
				dto.setCity(rs.getString("city"));
				dto.setEmail(rs.getString("email"));
				dto.setPostcode(rs.getString("postcode"));
				dto.setAddress(rs.getString("address"));
				dto.setId(rs.getString("id"));
			}	
			
		}catch(SQLException e){e.printStackTrace();}
		finally{
			try{
				rs.close();
				st.close();
				conn.close();
			}catch(SQLException ex){ex.printStackTrace();}
		}
		
		return dto;
		
	}
	//修改用户信息
	public int upd(member_dto dto)
	{
		
		Connection conn=DBConnection.getConnection();
		PreparedStatement ps=null;
		StringBuffer sb=new StringBuffer();
		int n=0; 
		sb.append("update tb_member set truename=?,password=?,city=?,address=?,postcode=?,cardno=?,grade=?,amount=?,tel=?,email=?,cardtype=? where id=?");
		try{
			ps=conn.prepareStatement(sb.toString());
			
			ps.setString(1, dto.getTruename());
			ps.setString(2, dto.getPassword());
			ps.setString(3, dto.getCity());
			ps.setString(4, dto.getAddress());
			ps.setString(5, dto.getPostcode());
			ps.setString(6, dto.getCardno());
			ps.setString(7, dto.getGrade());
			ps.setFloat(8, dto.getAmount());
			ps.setString(9, dto.getTel());
			ps.setString(10, dto.getEmail());
			ps.setString(11, dto.getCardtype());
			ps.setString(12,dto.getId());

			
			conn.setAutoCommit(false);
			n=ps.executeUpdate();
			conn.commit();
			System.out.print(sb);
			
		}catch(SQLException e){System.out.print(e.getMessage());}
		finally{
			try{
				
				ps.close();
				conn.close();
			}catch(SQLException ex){ex.printStackTrace();}
		}
		return n;
	}
}