dbcontrol.java

j2ee工程项目

    	sql="insert into album values(null,"+user_id+",'"+album_name+"','"+type_name+"','"+album_des+"',"+album_ispublic+",'"+t+"')";
    	try {
			stmt = conn.createStatement();
			stmt.executeUpdate(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    }
    //插入相册结束
    
    
   //获得类型结果集
    public ResultSet getalltype()
    {
    	sql="select * from type";
    	try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	return rs;
    }
    //获得类型结果集结束
    
    ///构造函数，获得数据库数据库连接并创建状态
   public dbcontrol()
    {
		Context context;
		try {
			context = new InitialContext();
			ds = (DataSource)context.lookup("java:/comp/env/jdbc/mysql");
		} catch (NamingException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		try {
			conn = ds.getConnection();
			stmt = conn.createStatement();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    }
    ///////构造结束
    
    
    //////连接和状态的关闭
    public void close()
    {
        if (rs != null) 
        {
            try 
            {
                rs.close();
            } catch (SQLException sqlEx) 
            { // ignore 
            	
            }
            rs = null;
        }
    	if (stmt != null) 
        {
            try 
            {
                stmt.close();
            } catch (SQLException sqlEx) 
            { // ignore 
            	
            }
            stmt = null;
        }
    	if (pstmt != null) 
        {
            try 
            {
                pstmt.close();
            } catch (SQLException sqlEx) 
            { // ignore 
            	
            }
            pstmt = null;
        }
        if (conn != null) 
        {
            try 
            {
                conn.close();
            } 
            catch (SQLException sqlEx) 
            { // ignore 
            	
            }
            conn = null;
        }
    }
    //close结束
    
    //登入检查
    public int logincheck(String user_name,String user_pass)
    {
    	sql="select * from user where user_name = ? and user_pass = ?";
    	try {
			pstmt = conn.prepareStatement(sql);
	    	pstmt.setString(1, user_name);
	    	pstmt.setString(2, user_pass);
	    	rs = pstmt.executeQuery();
	    	if(rs.next()==false)//判断有没有数据
	    		return 0;
	    	else
	    		return rs.getInt("user_id");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return 0;

    }
	//登入检查结束
    
    //用户注册 1用户名有了，2邮箱有了，0好了
    public int insertuser(String user_name,String user_pass,String user_realname,String user_email,String user_sex,String user_address,String user_phone,String user_des)
    {
    	sql="select * from user where user_name='"+user_name+"'";
    	try {
    		sql="select * from user where user_name='"+user_name+"'";
    		stmt= conn.createStatement();
			rs=stmt.executeQuery(sql);
			if(rs.next())
			{
				return 1;
			}
			else
			{
				sql="select * from user where user_name='"+user_email+"'";
				rs=stmt.executeQuery(sql);
				if(rs.next())
					return 2;
			}
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		
    	try {
    		sql="insert into user(user_name,user_pass,user_realname,user_email,user_sex,user_address,user_phone,user_des) values(?,?,?,?,?,?,?,?)";
    		pstmt = conn.prepareStatement(sql);
	    	pstmt.setString(1, user_name);
	    	pstmt.setString(2, user_pass);
	    	pstmt.setString(3, user_realname);
	    	pstmt.setString(4, user_email);
	    	pstmt.setString(5, user_sex);
	    	pstmt.setString(6, user_address);
	    	pstmt.setString(7, user_phone);
	    	pstmt.setString(8, user_des);
	    	pstmt.execute();
	    	
	    	sql="select max(user_id) as a from user";
	    	rs=stmt.executeQuery(sql);
	    	
	    	int id = 0;
	    	if(rs.next())
	    		id = rs.getInt("a");
	    	
	    	SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
	    	String time=formatter.format(Calendar.getInstance().getTime());
	    	//Date d =(Date) Calendar.getInstance().getTime();
	    	sql="insert into album values(null,"+id+",'我的相册','人物','我的相册',1,'"+time+"')";
	    	//pstmt = conn.prepareStatement(sql);
	    	//pstmt.setDate(1,(java.sql.Date)(Calendar.getInstance().getTime()));
	    	stmt.executeUpdate(sql);
	    	return 0;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return 0;
		
		
    }
	//用户注册结束
    
    //注册用户，返回结果集
    public ResultSet latestuser()
    {
    	sql="select * from user order by user_id desc";
    	try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return rs;
    }
    //用户集返回结束
    
    //插入照片
    public void insertphoto(int album_id,String photo_des,String photo_path,String photo_name) throws SQLException
    {
		java.text.SimpleDateFormat formatter = new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); 
		java.util.Date currentTime = new java.util.Date();//得到当前系统时间 
		String t = formatter.format(currentTime); //将日期时间格式化 
    	sql="insert into photo values(null,"+album_id+",'"+photo_des+"','"+t+"',0,'"+photo_path+"','"+photo_name+"')";
    	stmt =conn.createStatement();
    	stmt.executeUpdate(sql);
    }
    //插入照片结束
    
    //选择登入者拥有的相册及id
    public ResultSet getalbum(int user_id)
    {
    	sql="select * from album where user_id='"+user_id+"' order by album_id desc";
    	try {
			stmt = conn.createStatement();
			rs=stmt.executeQuery(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	return rs;
    	
    }
    //选择登入者拥有的相册及id结束
    
    //获取相册的结果集
    public ResultSet getalbum()
    {
    	sql="select * from album order by album_id desc";
    	try {
			stmt =conn.createStatement();
			rs = stmt.executeQuery(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	return rs;
    }
    ////获取相册的结果集结束
    
    //根据相册id返回相册的墙纸
    public String getwallpaper(int album_id)
    {
    	sql="select * from photo where album_id="+album_id+" and photo_isdefault = 1";
    	try {
			stmt=conn.createStatement();
			rs=stmt.executeQuery(sql);
			if(rs.next())
				return rs.getString("photo_path");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		sql="select * from photo where album_id="+album_id+"";
    	try {
			rs=stmt.executeQuery(sql);
			if(rs.next())
				return rs.getString("photo_path");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	return "";
    }
    //根据相册id返回相册的墙纸结束
    
}