/*This class is used to operate data base.
 *The interface do_state value 0 is null pointer,-1 is data base error,1 is victory.
 *Member variable indication:cur_page,total_page,size_page
 *Member function indication:ArrayList dataQuery(String sql,int pagesize,String curpage)
 */
package bookutil;
import java.sql.*;
import java.util.ArrayList;
public class DataDo {
  private ArrayList fields=new ArrayList();
  private Connection conn=null;
  private Statement stm=null;
  public int do_state=0;
  public int cur_page=1;
  public int total_page=1;
  public int size_page=5;
  //----------connection data base
  public DataDo() {
    try {//---------change connection attributes here !
      String conn_str="jdbc:microsoft:sqlserver://192.168.0.111:1433;dataName=test_yang";
      Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
      conn=DriverManager.getConnection(conn_str,"test","test");
      stm=conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
      do_state=1;
    }
    catch (Exception e) {
      System.out.print(e.toString());
      do_state=-1;
    }
  }
  //-----------process query
  public ArrayList dataQuery(String sql,int pagesize,String curpage){
    ResultSet rs=null;
    int rec_pos=1;
    ArrayList al=new ArrayList();
    size_page=pagesize;
    int col_count=1;
    if(sql==null||sql.length()==0||pagesize<=0){
      return al;
    }
    if(curpage==null)curpage="1";
    try{
      rs = stm.executeQuery(sql);
      ResultSetMetaData md=rs.getMetaData();
      do_state=1;
      //----------table head process
      col_count=md.getColumnCount();
      for(int i=1;i<=col_count;i++)
      fields.add(md.getColumnName(i));
      //----------total page
      rs.last();
      total_page=(rs.getRow()+size_page-1)/size_page;
    }catch(Exception e){
      System.out.println(e.toString());
      do_state=-1;
      return al;
    }
    //check reference curpage
    try{cur_page=Integer.parseInt(curpage);}
    catch(Exception e){
      System.out.println(e.toString());
      cur_page=1;
    }
    if(cur_page<=0)cur_page=1;
    if(cur_page>total_page)cur_page=total_page;
    //orientation record position in rs
    rec_pos=(cur_page-1)*pagesize+1;
    try{
      rs.absolute(rec_pos);
      for(int j=0;j<size_page;j++){
        ArrayList in_al=new ArrayList();
        for(int i=1;i<=col_count;i++){
          in_al.add(rs.getString(i));
        }
        al.add(in_al);
        if(!rs.next())break;
      }
    }catch(Exception e){
      System.out.println(e.toString());
      return al;
    }
    return al;
    }
  //----------process update return >=0:victory  -1:database error -2:null pointer
  public int dataUpdate(String sql){
    int st=0;
    if(sql==null||conn==null||stm==null||sql.length()==0){
      do_state=0;
      return st=-2;
    }
    try{
      st=stm.executeUpdate(sql);
      do_state=1;
    }catch(Exception e){
      System.out.print(e.toString());
      do_state=-1;
      st=-1;
    }
    return st;
  }
  //---------close connection
  public void dataClose(){
    try{
      if(stm!=null)stm.close();
      if(conn!=null)conn.close();
      do_state=1;
    }catch(Exception e){
      System.out.print(e.toString());
      do_state=-1;
    }
  }
  //---------mapping fields name to number
  public int getColumn(String name){
    int col_num=0;
    if(fields==null||name==null||fields.isEmpty()||name.length()==0){
      do_state=0;
      return col_num;
    }
    for(int i=0;i<fields.size();i++){
      if(fields.get(i).toString().equals(name)){
        col_num=i;
        break;
      }
    }
    return col_num;
  }
}