jsp_project_/store/src/conn/ConnectionDatabase

package conn;
import java.util.*;
import java.sql.*;

import javax.servlet.http.HttpServletRequest;

public   class ConnectionDatabase {
 private Connection conn=null;
 private Statement st = null;
 private ResultSet rs = null;
 CallableStatement cmt = null;
 private String url="jdbc:jtds:sqlserver://localhost:1433/shoppingcartDataBase";
 private String user="sa";
 private String pass="";
 private int total=0;
 private String filePath;

 
    //获取查询数据
 public Vector getQueryDate(String sql)
 {
  try
  {
   Class.forName("net.sourceforge.jtds.jdbc.Driver");
   conn = DriverManager.getConnection(url,user,pass);
   st = conn.createStatement();
   rs = st.executeQuery(sql);
    
   ResultSetMetaData rsmd = rs.getMetaData();
   int column = rsmd.getColumnCount();
   Vector content = new Vector();
   while(rs.next())
   {
    Vector v = new Vector();
    for(int i=1; i <=column;i++ )
    {
     v.add(rs.getString(i));
    }
    content.add(v);
   }
   return content;
  }catch(Exception eGetQueryDate)
  {
   System.out.print("ConnectionDatabase::getQueryDate"+eGetQueryDate.getMessage());
   return null;
  }finally
  {
   try{
    if(rs!= null)
    {
     rs.close();
    }
    if(st!=null)
    {
     st.close();
    }
    if(conn != null)
    {
     conn.close();
    }
   }catch(Exception eGetQueryDateClose)
   {
    System.out.print("ConnectionDatabase::getQueryDate"+eGetQueryDateClose.getMessage());
   }
  }
 }
 //判断有没有结构集合
 public boolean isNull(String sql)
 {
  try
  {
   Class.forName("net.sourceforge.jtds.jdbc.Driver");
   conn = DriverManager.getConnection(url,user,pass);
   st = conn.createStatement();
   rs = st.executeQuery(sql);
   return rs.next();
  }catch(Exception eisNull)
  {
   System.out.print("ConnectionDatabase::isNull"+eisNull.getMessage());
   return false;
  }finally
  {
   try{
    if(rs!= null)
    {
     rs.close();
    }
    if(st!=null)
    {
     st.close();
    }
    if(conn != null)
    {
     conn.close();
    }
   }catch(Exception eisNullClose)
   {
    System.out.print("ConnectionDatabase::isNull"+eisNullClose.getMessage());
   }
  }
 }
 //执行sql的增删改synchronized
  public int updateBase(String sql)
 {
  try
  {
   Class.forName("net.sourceforge.jtds.jdbc.Driver");
   conn = DriverManager.getConnection(url,user,pass);
   st = conn.createStatement();
   return  st.executeUpdate(sql);   
  }catch(Exception eupdateBase)
  {
   System.out.print("ConnectionDatabase::updateBase"+eupdateBase.getMessage());
   return 0;
  }finally
  {
   try{
    
    if(st!=null)
    {
     st.close();
    }
    if(conn != null)
    {
     conn.close();
    }
   }catch(Exception eupdateBaseClose)
   {
    System.out.print("ConnectionDatabase::updateBase"+eupdateBaseClose.getMessage());
   }
  }
 }
 
 //获取定单ID
 public String getOrderID(String sql)
 {
  try
  {
   Class.forName("net.sourceforge.jtds.jdbc.Driver");
   conn = DriverManager.getConnection(url,user,pass);
   CallableStatement cmt=conn.prepareCall(sql);
   cmt.registerOutParameter(1,Types.VARCHAR);
   cmt.execute();
   return cmt.getString(1);
  }catch(Exception egetOrderID)
  {
   System.out.print("ConnectionDatabase::getOrderID"+egetOrderID.getMessage());
   return "";
  }finally
  {
   try{
        
    if(cmt!=null)
    {
     cmt.close();
    }
    if(conn != null)
    {
     conn.close();
    }
   }catch(Exception egetOrderID1)
   {
    System.out.print("ConnectionDatabase::updateBase"+egetOrderID1.getMessage());
   }
  }
 }
 //实现分页
 public Vector getPage(String sql,HttpServletRequest req,String pageN)//
 {
  Vector content = new Vector();
  try{
   Class.forName("net.sourceforge.jtds.jdbc.Driver");
   String sqlPart;
   int begin;
   begin = sql.indexOf("FROM");
   sqlPart ="SELECT COUNT(*) " +sql.substring(begin,sql.length());
   conn = DriverManager.getConnection(url,user,pass);
   st = conn.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_UPDATABLE);
   
   
   filePath = req.getRequestURI();
  
   rs = st.executeQuery(sqlPart);
 
   if(rs.next())
   {
    total = rs.getInt(1);
   }else
   {
    total = 0;
   }
   
   if(total>0)
   {
    rs = null;
    rs = st.executeQuery(sql); 
    ResultSetMetaData rsmt = rs.getMetaData();
    int col = rsmt.getColumnCount();   
    rs.absolute(Integer.parseInt(pageN)+1);
    rs.previous();
    int i=0;
    while(rs.next() && i<12)
    {
     Vector vcol = new Vector();
     for(int j=1;j<=col;j++)
     {
      vcol.add(rs.getString(j));
     }
     content.add(vcol);
     i++;
    }
    return content;
   }  
  }catch(Exception e1){
   e1.printStackTrace();
   return content;
  }finally{
   try{
    if(rs!=null)
    {
     rs.close();
    }
    if(st!=null)
    {
     st.close();
    }
    if(conn!=null)
    {
     conn.close();
    }
   }catch(Exception e11)
   {
    e11.printStackTrace();
   }
  }
  return null;
 }
 public int getTotal()
 {
  return total;
 }
 public String pageLenged(int pagen)
 {
  String str="";
  int prev,next;
  prev = pagen-12;
  next = pagen+12;
  if(prev >= 0)
  {
   str+="<a href="+filePath+"?offset="+prev+">上一页</a>";
  }else
  {
   str+="上一页";
  }
  str+="   ";
  if((next < total))
  {
   str+="<a href="+filePath+"?offset="+next+">下一页</a>";
  }else
  {
   str+="下一页";
  }
  return str;
 }
 public static void main(String args[])
 {
  ConnectionDatabase conn = new ConnectionDatabase();
  System.out.print(conn.getOrderID("{ call getordID(?) }"));
 }
}