package database;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import vo.putong;
 




public class MySQL{
	
	private String url="jdbc:mysql://localhost:3306/mobile";
	private String driver="com.mysql.jdbc.Driver";
	private Connection con;
	
	public MySQL(){
		try {
			Class.forName(driver);
			con=DriverManager.getConnection(url,"root","root");
		} catch (ClassNotFoundException e) {
			System.out.println("the driver not fund!");
			e.printStackTrace();
		} catch (SQLException e) {
			System.out.println("connecte database ocure exception!");
			e.printStackTrace();
		}
	}
	private Connection getCon(){
		return this.con;
	}
	
	public int Update(String sql){
		Statement st=null;
		Connection con=this.getCon();
		try {
			st=con.createStatement();
			int colCount=st.executeUpdate(sql);
			st.close();
			con.close();
			return colCount;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return 0;
		}
		}
	
	public int Update1(String username,String passwd){
		String sql="insert into putong(username,passwd) values('" + username
				+ "','" + passwd + "'";
		//ResultSet rs=null;
		Statement st=null;
		Connection con=this.getCon();
		try{
			st=con.createStatement();
			int colCount=st.executeUpdate(sql);
			st.close();
			con.close();
			return colCount;
		}catch(SQLException e){
			e.printStackTrace();
			return 0;
		}
	}
	
	public List<putong> getPuTong(){
		String sql="select * from putong";
		List<putong> list=new ArrayList<putong>();
		Statement st=null;
		ResultSet rs=null;
		Connection con=this.getCon();
		try {
			st=con.createStatement();
			rs=st.executeQuery(sql);
			while(rs.next()){
				putong p=new putong();
			p.setUsername(rs.getString("username"));
			p.setPasswd(rs.getString("passwd"));
			list.add(p);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				rs.close();
				st.close();
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return list;
	}
	
	 public ArrayList<String[]> selectToArrayList(String sql,int perPage,int curPage) {
	        ArrayList<String[]> result = new ArrayList<String[]>();
	        Connection con = null;
	        try {
	            Class.forName(driver);
	            con = DriverManager.getConnection(url);
	            //
	            Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
	            ResultSet rs = stmt.executeQuery(sql);
	            //處理數據
	            int colCount = rs.getMetaData().getColumnCount();
	            //跳到這一頁的數據
	            rs.absolute(curPage*perPage-perPage);
	            //取這一頁的數據
	            while (result.size()<perPage && rs.next()) {
	                String[] record = new String[colCount];
	                //每一列
	                for (int i = 0; i < colCount; i++) {
	                    record[i] = rs.getString(i + 1);
	                }
	                //將當前記錄添加倒ArrayList中
	                result.add(record);
	            }
	            //釋放內存
	            rs.close();
	            stmt.close();
	        } catch (ClassNotFoundException e) {
	        } catch (SQLException e) {
	        } finally {
	            try {
	                con.close();
	            } catch (SQLException e) {
	            }
	        }
	        return result;
	    }   
	
	 public String[] selectToArray(String sql) {
		  String[] result = {};
	    	Statement st=null;
			ResultSet rs=null;
			Connection con=this.getCon();
	        try {
	            st = con.createStatement();
	            rs = st.executeQuery(sql);
                int colCount = rs.getMetaData().getColumnCount();
                result = new String[colCount];
	            while (rs.next()) {
                     for (int i = 0; i < colCount; i++) {
	                    result[i] = rs.getString(i + 1);
	                }
                }
	            rs.close();
	            st.close();
	            con.close();
	            
            } catch (SQLException e) {
            	e.printStackTrace();
    		} 
            return result;
	    }
	 
	 public String[][] select(String sql) {
		 //System.out.println("..........");   
		 String[][] result = {};
	        Statement st=null;
			ResultSet rs=null;
			Connection con=this.getCon();
	        try {
	             st = con.createStatement();
	             rs = st.executeQuery(sql);
	             int colCount = rs.getMetaData().getColumnCount();
	            //創建一個元素為一維字符串數組的鏈表     
	            ArrayList<String[]> arrayList = new ArrayList<String[]>();
	            //當結果集中還有元素時
	            while (rs.next()) {
	               String[] record = new String[colCount];
	                for (int i = 0; i < colCount; i++) {
	                    record[i] = rs.getString(i + 1);
	                }
	               arrayList.add(record);
	            }
	            rs.close();
	            st.close();
	           result = new String[arrayList.size()][];
	            for (int i = 0; i < arrayList.size(); i++) {
	                result[i] = arrayList.get(i);
	            }
             } catch (SQLException e) {
	        } // 關閉SQL
	        finally {
	            try {
	            	con.close();
	            } catch (SQLException e) {
	            }
	        }
	        return result;
	    }

}