package DBCon;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import beans.beanss;
public class DBConnertion {
    public DBConnertion() {
    }
private static 	DBConnertion dc=new DBConnertion();
 private  Connection con=null;
     PreparedStatement ps=null;
     ResultSet rs=null;
public static DBConnertion newInstance(){
        return dc;
}

public Connection getConnection(){

     String className="com.microsoft.jdbc.sqlserver.SQLServerDriver";
    //className="sun.jdbc.odbc.JdbcOdbcDriver";
                try {
                        Class.forName(className);
                } catch (ClassNotFoundException e1) {
                        // TODO 自動生成 catch 塊
                        e1.printStackTrace();
                }
                String url="jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=myLiuYan";
        //	url="jdbc:odbc:test2";
                try {
                        con=DriverManager.getConnection(url,"sa","sa");
                } catch (SQLException e) {
                        // TODO 自動生成 catch 塊
                        e.printStackTrace();
                }
                return con;
   }



   public void insert(String name,String neirong)
    {
        DBConnertion d=new DBConnertion().newInstance();
        con=d.getConnection();
        String sql="insert into myly (lyren,lyneirong) values (?,?)";
      try {
                 ps=con.prepareStatement(sql);
                 ps.setString(1,name);
                 ps.setString(2,neirong);
                 ps.executeUpdate();
              } catch (SQLException e) {
                      // TODO 自動生成 catch 塊
                      e.printStackTrace();
              }
              finally
              {
                      closers();
                      closeps();
                      closecon();
              }

    }

    public ArrayList Select()
        {
            ArrayList list=new ArrayList();
            String sql="Select * from myly where order by lytime desc ";
            DBConnertion obj=new DBConnertion().newInstance();
            con=obj.getConnection();
            try {
                           ps=con.prepareStatement(sql);
                           rs=ps.executeQuery();
                           while(rs.next())
                           {
                               String name=rs.getString(2);
                               String neirong=rs.getString(3);
                               String time=rs.getString(4);
                                beanss b=new beanss(name,neirong,time)  ;
                                list.add(b);

                           }
                   } catch (SQLException e) {
                           // TODO 自動生成 catch 塊
                           e.printStackTrace();
                   }
            return list;
     }


   public void closecon()
     {
        if(con!=null)
        {
                try {
                               con.close();
                               con=null;
                       } catch (SQLException e) {
                               // TODO 自動生成 catch 塊
                               e.printStackTrace();
                       }
        }
     }
     public void closeps()
     {
        if(ps!=null)
        {
                try {
                               ps.close();
                               ps=null;
                       } catch (SQLException e) {
                               // TODO 自動生成 catch 塊
                               e.printStackTrace();
                       }
        }
     }
     public void closers()
     {
        if(rs!=null)
        {
                try {
                               rs.close();
                               rs=null;
                       } catch (SQLException e) {
                               // TODO 自動生成 catch 塊
                               e.printStackTrace();
                       }
        }
     }

}