package com.ww.service;

import java.lang.reflect.Array;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.mysql.jdbc.PreparedStatement;
import com.ww.db.DBHelper;
import com.ww.entity.Score;
import com.ww.entity.Student;

public class SysService {

	DBHelper dbHelper=new DBHelper();
	//查询所有学生的编号，姓名和分数，并返回
	public ArrayList<Score> getAllInfo(){
		String sql="select * from stu s,sco sc where s.stu_id=sc.stu_id";
		Connection connection=dbHelper.getConnection();
		ArrayList<Score> scores=new ArrayList<Score>();
		try {
			PreparedStatement psd=(PreparedStatement)connection.prepareStatement(sql);
			ResultSet resultSet=psd.executeQuery();
			
			while (resultSet.next()) {
				Student student=new Student();
				Score score=new Score();
				student.setId(resultSet.getInt("stu_id"));
				student.setName(resultSet.getString("stu_name"));
				
				score.setId(resultSet.getInt("sco_id"));
				score.setScos(resultSet.getInt("emg_sco"));
				score.setStudent(student);
				scores.add(score);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return scores;
	}

	//添加
	public boolean addInfo(String name,int a){
		String sql1="insert into stu VALUES(null,?)";
		String sql2="insert into sco VALUES(null,(select MAX(stu_id) from stu),?)";
		Connection connection=dbHelper.getConnection();
		try {
			//执行向主表添加数据
			PreparedStatement psd1=(PreparedStatement)connection.prepareStatement(sql1);
			//执行向从表中添加数据
			PreparedStatement psd2=(PreparedStatement)connection.prepareStatement(sql2);
			psd1.setString(1, name);
			int b=psd1.executeUpdate();
			if (b>0) {
				//向主表中完成添加
				psd2.setInt(1, a);
				int s=psd2.executeUpdate();
				if (s>0) {
					return true;
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return false;
	}
	
	
	//修改
	public boolean update(int id,String name,int sc) {
		String sql="update stu s,sco sc set s.stu_name=?,sc.emg_sco=? where s.stu_id=sc.stu_id and s.stu_id=?";
		Connection connection=dbHelper.getConnection();
		try {
			PreparedStatement psd=(PreparedStatement)connection.prepareStatement(sql);
			psd.setString(1, name);
			psd.setInt(2, sc);
			psd.setInt(3, id);
			if(psd.executeUpdate()>=2){
				return true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return false;
	}
	
	
	
	
	//删除
	public boolean deleteInfo(int id){
		String sql="delete from sco where stu_id=?";
		Connection connection=dbHelper.getConnection();
		try {
			PreparedStatement psd=(PreparedStatement)connection.prepareStatement(sql);
			psd.setInt(1, id);
			if(psd.executeUpdate()>0){
				return true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return false;
	}
}