dbcontrol.java

j2ee工程项目

package com.webalbum2.bean;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Calendar;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

public class dbcontrol {
	Connection conn = null;
    Statement stmt = null;
    ResultSet rs = null;
    DataSource ds = null;
    PreparedStatement pstmt = null;
    String sql;
    

    //获取分类分人相册的结果集
    public ResultSet getalbumoftypeoruser(String type_name,String user_id)
    {
    	sql="select * from album where type_name='"+type_name+"'";
    	if(user_id!=null)
    	{
    		int id =Integer.parseInt(user_id); 
    		sql = sql + "and user_id = "+id+"";
    	}
    	sql = sql + " 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;
    }
    ////获取分类分人相册的结果集结束
    
    //获取搜索结果相片的结果集
    public ResultSet getsearchphoto(String photo_name,String type_name,String photo_time)
    {
		int t=Integer.parseInt(photo_time); 
    	sql="select * from album,photo where photo.album_id=album.album_id and photo_name like '%"+photo_name+"%'";
    	if(!"所有类型".equals(type_name))
    	{
    		sql = sql +" and type_name='"+type_name+"'";
    	
    	}
    	if(t!=0)
    	{
    		sql = sql + "and to_days(now())-to_days(photo_uploadtime) < "+t+"";
    	}
    	try {
			stmt =conn.createStatement();
			rs = stmt.executeQuery(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	return rs;
    }
    ////获取搜索结果相片的结果集结束
    

    //获取搜索结果相册的结果集
    public ResultSet getsearchalbum(String album_name,String type_name,String album_time)
    {
		int t=Integer.parseInt(album_time); 
    	sql="select * from album where album_name like '%"+album_name+"%'";
    	if(!"所有类型".equals(type_name))
    	{
    		sql = sql +" and type_name='"+type_name+"'";
    	
    	}
    	if(t!=0)
    	{
    		sql = sql + "and to_days(now())-to_days(album_creatdate) < "+t+"";
    	}
    	try {
			stmt =conn.createStatement();
			rs = stmt.executeQuery(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	return rs;
    }
    ////获取搜索结果相册的结果集结束
    
    
    //根据相片id更新编辑相片
    public void editphoto(int photo_id,String photo_des,int photo_isdefault,String photo_name) throws SQLException
    {
    	sql= "update photo set photo_name = '"+photo_name+"',photo_des = '"+photo_des+"',photo_isdefault ="+photo_isdefault+" where photo_id = "+photo_id+"";
    	stmt = conn.createStatement();
    	stmt.executeUpdate(sql);
    	if(photo_isdefault==1)
    		this.setwallpaper(photo_id);
    }
    //根据相片id更新编辑相片结束
    
    
    //根据相片id将其设为对应相册的墙纸
    public void setwallpaper(int photo_id) throws SQLException
    {
    	int album_id=0;
    	ResultSet rr = this.getonephoto(photo_id);
    	if(rr.next())
    		album_id=rr.getInt("album_id");
    	sql="update photo set photo_isdefault = 0 where photo_isdefault = 1 and album_id = "+album_id+"";
    	stmt= conn.createStatement();
    	stmt.executeUpdate(sql);
    	sql="update photo set photo_isdefault = 1 where photo_id= "+photo_id+"";
    	stmt.executeUpdate(sql);	
    }
    //根据相片id将其设为对应相册的墙纸
    
    
    //根据相片id获得相片结果集
    public ResultSet getonephoto(int photo_id)
    {
    	sql="select * from photo where photo_id="+photo_id+"";
    	try {
			stmt =conn.createStatement();
			rs = stmt.executeQuery(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	return rs;
    	
    }
    //根据相片id获得相片结果集
    
    //利用相片id删除相片
    public void delphoto(int photo_id)
    {
    	sql="delete from photo where photo_id="+photo_id+"";
    	try {
			stmt = conn.createStatement();
			stmt.executeUpdate(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}    	
    }
    //利用相片id删除相片结束
    
    
    //根据相册id获得拥有者用户的信息
    public ResultSet getuserfromalbum(int album_id) throws SQLException
    {
    	int user_id = 0;
    	rs = this.getonealbum(album_id);
    	if(rs.next())
    		 user_id = rs.getInt("user_id");
    	rs = this.getoneuser(user_id);
    	return rs;
    	
    }
    //根据相册id获得拥有者用户的信息结束
    
    //根据用户id获得用户信息
    public ResultSet getoneuser(int user_id)
    {
    	sql="select * from user where user_id = '"+user_id+"'";
    	try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return rs;    	
    }
    //根据用户id获得用户信息结束
    
    //根据相册id获得所有相片结果集
    public ResultSet getphotoofalbum(int album_id)
    {
    	sql="select * from photo where album_id="+album_id+"";
    	try {
			stmt =conn.createStatement();
			rs = stmt.executeQuery(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	return rs;
    }
    //根据相册id获得所有相片结果集
    
    //更新编辑相册
    public void editalbum(int album_id,String album_name,String type_name,String album_des,int album_ispublic)
    {
    	sql="update album set album_name='"+album_name+"',type_name='"+type_name+"',album_des= '"+album_des+"',album_ispublic="+album_ispublic+" where album_id = "+album_id+"";
    	try {
			stmt = conn.createStatement();
			stmt.executeUpdate(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	
    }
    //更新编辑相册
    
    
    //利用相册id获取一个相册
    public ResultSet getonealbum(int album_id)
    {
    	sql="select * from album where album_id='"+album_id+"'";
    	try {
			stmt = conn.createStatement();
			rs=stmt.executeQuery(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	return rs;
    	
    }
    //利用相册id获取一个相册结束
    
    //删除相册
    public void delonealbum(int album_id)
    {
    	sql="delete from album where album_id="+album_id+"";
    	try {
			stmt= conn.createStatement();
			stmt.executeUpdate(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	
    }
    //删除相册结束
    
    //获取某人相册的结果集
    public ResultSet getuseralbum(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;
    }
    ////获取某人的相册的结果集结束
    
    
    
    //插入相册
    public  void insertalbum(int user_id,String album_name,String type_name,String album_des,int album_ispublic)
    {
		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); //将日期时间格式化