package ajax;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class DataExchange {
	private int java,net,c,pb,pascal;
	
	//創(chuàng)建連接
	public Connection createCon(){
		String conn = "jdbc:odbc:driver={Microsoft Access Driver (*.mdb)};DBQ=G:\\java\\WorkSpace\\WebChart\\webchart.mdb";
		Connection con=null;
		try {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            con = DriverManager.getConnection(conn);
        } catch (Exception e) {
        	e.printStackTrace();
        }
        return con;
	}
	
	//獲得書
	public String[] getBooks(){
		String []books={"java","net","c","pb","pascal"};
		return books;
	}
	
	//獲得數(shù)據(jù)，書的銷售量
	public int[] getData(int month) {
		Connection con = createCon();
		String sql="select * from data where month=?";
		try {
	        PreparedStatement ps=con.prepareStatement(sql);
	        ps.setInt(1,month);
	        ResultSet rs = ps.executeQuery();
	        while(rs.next()){
	        	java = rs.getInt("java");
	        	net = rs.getInt("net");
	        	c = rs.getInt("c");
	        	pb = rs.getInt("pb");
	        	pascal = rs.getInt("pascal");
	        }
	        con.close();
	    } catch (Exception e) {
	    	e.printStackTrace();
	    }
	    int[] data = new int[]{month,java, net, c, pb, pascal};
		return data;
	}
	
	//更新數(shù)據(jù)
	public int updateData(int[] data, int month) {
		Connection con = createCon();
		String sql="update data set java=?,net=?,c=?,pb=?,pascal=? where month="+month;
		try {
			PreparedStatement ps = con.prepareStatement(sql);
			ps.setInt(1, data[0]);
			ps.setInt(2, data[1]);
			ps.setInt(3, data[2]);
			ps.setInt(4, data[3]);
			ps.setInt(5, data[4]);
			ps.executeUpdate();
			con.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return month;
	}
	
	//插入數(shù)據(jù)
	public int insertData(int[] data, int month) {
		Connection con = createCon();
		String sql="insert into data values(?,?,?,?,?,?)";
		try{
			PreparedStatement ps = con.prepareStatement(sql);
			ps.setInt(1, month);
			ps.setInt(2, data[0]);
			ps.setInt(3, data[1]);
			ps.setInt(4, data[2]);
			ps.setInt(5, data[3]);
			ps.setInt(6, data[4]);
			ps.execute();
			con.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return month;
	}
	
	public int[] getMonthData(String month) {
		return getData(Integer.valueOf(month));
	}
}