querydao.java

数学网

package math.users.query.dao;

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

import javax.sql.DataSource;

import sun.net.InetAddressCachePolicy;

import math.dao.DAO;
import math.users.query.model.Query;
import math.users.query.model.QueryList;

public class QueryDao extends DAO {

	public QueryDao(DataSource ds) {
		super(ds);
	}
	public List find(int grade,String username,
			         int offset, int limit)
	{Connection con=null;
	 PreparedStatement ps=null;
	 ResultSet rs=null;
	 ArrayList list=new ArrayList();
	 String sql="";
	 if(grade<=0)
	 {
		sql="select id,username,real_name,users_grade,users_date from users where users_grade>10 and users_grade<999 "; 
	 }
	 else sql="select id,username,real_name,users_grade,users_date from users where users_grade<999 and users_grade=? ";
	  if(username==null||username.trim().length()<=0)
	  {}
	  else{
		  
	    sql=sql+"and username like ?";
	  }
	 try {
		con=ds.getConnection();
		ps=con.prepareStatement(sql,
				ResultSet.TYPE_SCROLL_INSENSITIVE,
                ResultSet.CONCUR_READ_ONLY);
		if(grade==0)
		{}
		else ps.setInt(1,grade);
		if(username==null||username.equals(""))
		{}
		else{if(grade==0)
		     {ps.setString(1,"%"+username+"%");}
		    else ps.setString(2,"%"+username+"%");
		}
		rs=ps.executeQuery();
		if(offset>0)
			rs.absolute(offset);
		int count=0;
		while(count++<limit&&rs.next())
		{  QueryList ql=new QueryList();
 	       ql.setId(rs.getInt("id"));
	       ql.setUsername(rs.getString("username"));
	       ql.setReal_name(rs.getString("real_name"));
	       ql.setUsers_grade(rs.getInt("users_grade"));
	       ql.setUsers_date(rs.getDate("users_date"));
	       list.add(ql);
			
		}
		close(rs);
 	    close(ps);
	} catch (SQLException e) {
		close(rs);
 	    close(ps);
 	   rollback(con);
		e.printStackTrace();
	}finally {
 	   close(con);
	}
	 return list;
		
	}
	public List userlist(int offset, int limit) {
       Connection con=null;
       PreparedStatement ps=null;
       ResultSet rs=null;
	   ArrayList list=new ArrayList();
       try{con=ds.getConnection();
       String sql=
    	   "select id,username,real_name,users_grade,users_date from users where users_grade>10 and users_grade<999";
       ps=con.prepareStatement(sql,
    		   ResultSet.TYPE_SCROLL_INSENSITIVE,
               ResultSet.CONCUR_READ_ONLY);
       rs=ps.executeQuery();
       if(offset>0)
       {
    	   rs.absolute(offset);
       }
       int count=0;
       while(count++<limit&&rs.next())
       {
    	   QueryList ql=new QueryList();
    	   ql.setId(rs.getInt("id"));
    	   ql.setUsername(rs.getString("username"));
    	   ql.setReal_name(rs.getString("real_name"));
    	   ql.setUsers_grade(rs.getInt("users_grade"));
    	   ql.setUsers_date(rs.getDate("users_date"));
    	   list.add(ql);
    	  
       }
       close(rs);
	   close(ps);
       }catch (SQLException e) {
    	   close(rs);
    	   close(ps);
    	   rollback(con);
    	   e.printStackTrace();
    	}
    	finally {
    	   close(con);
    	}

       
    return list;
	}
	public void del(String id)
	{Connection con=null;
     PreparedStatement ps=null;
    try {
		con=ds.getConnection();
		String sql=
			"update users set users_grade=1,users_date=null where id=?";
		ps=con.prepareStatement(sql);
		ps.setInt(1,Integer.parseInt(id));
		ps.executeUpdate();
		close(ps);
	} catch (SQLException e) {
		close(ps);
		e.printStackTrace();
	}finally {
 	   close(con);
 	}
	
	}
	public Query sel(String id)
	{ Connection con=null;
      PreparedStatement ps=null;
      ResultSet rs=null;
      Query ql=new Query();
      try {
		con=ds.getConnection();
		String sql=
			" select id,username,real_name,users_grade,users_date from users where id=? ";
		ps=con.prepareStatement(sql); 
		ps.setInt(1,Integer.parseInt(id));
		rs=ps.executeQuery();
		
		if(rs.next())
		{
			ql.setId(rs.getInt("id"));
			ql.setUsername(rs.getString("username"));
			ql.setReal_name(rs.getString("real_name"));
			ql.setUsers_grade(rs.getInt("users_grade"));
			ql.setUsers_date(rs.getDate("users_date"));
		}
		else ql=null;
		close(rs);
		close(ps);
	    }catch (SQLException e) {
	    	   close(rs);
	    	   close(ps);
	    	   rollback(con);
	    	   e.printStackTrace();
	    }
	    finally {
	    	close(con);
	    }
	    return ql;
	}
	public void update(Query ql)
	{Connection con=null;
    PreparedStatement ps=null;
    ResultSet rs=null;
    try {
		con=ds.getConnection();
		String sql=
			"update users set users_grade=? where id=?";
		ps=con.prepareStatement(sql);
		ps.setInt(1,ql.getUsers_grade());
		ps.setInt(2,ql.getId());
		ps.executeUpdate();
		close(ps);
	    }catch (SQLException e) {
	    	   close(ps);
	    	   rollback(con);
	    	   e.printStackTrace();
	    }
	    finally {
	    	close(con);
	    }
		
	}
}