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

public class SQLConnection {
	Connection con;

	public SQLConnection() {// 初始化加载
		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
			con = DriverManager.getConnection("jdbc:sqlserver://localhost:1433;Database=studentDB", "sa", "2548892503");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public boolean SQLCon(String uid, String password) {// 连接数据库
		// TODO Auto-generated constructor stub
		try {
			String string = String.format("select * from users where uid='%s' and password='%s'", uid, password);
			Statement statement = con.createStatement();
			ResultSet resultSet = statement.executeQuery(string);
			if (resultSet.next()) {
				return true;
			}
		} catch (Exception e) {
			// TODO: handle exception
		}
		return false;
	}

	public double SQLshow(int uid) {// 查询余额
		// TODO Auto-generated constructor stub
		try {
			String string = String.format("select cash from users where uid='%s'" , uid);
			Statement statement = con.createStatement();
			ResultSet resultSet = statement.executeQuery(string);
			if (resultSet.next()) {
				double cash=resultSet.getDouble(1);
				return cash;
			}
		} catch (Exception e) {
			// TODO: handle exception
		}
		return 0;
	}
	
	public int SQLdep(int inmoneoy) {//存钱
		// TODO Auto-generated constructor stub
		try {
			String string = String.format("update users set cash=cash+'%s' where uid=110  uid=''" , inmoneoy);
			PreparedStatement statement = con.prepareStatement(string);
			int count = statement.executeUpdate();
			if (count!=0) {
				return count;
			}
		} catch (Exception e) {
			// TODO: handle exception
		}
		return 0;
	}
	
	public int SQLtake(int outmoneoy) {//取钱
		// TODO Auto-generated constructor stub
		try {
			String string = String.format("update users set cash=cash-'%s' where uid=110  uid=''" , outmoneoy);
			PreparedStatement statement = con.prepareStatement(string);
			int count = statement.executeUpdate();
			if (count!=0) {
				return count;
			}
		} catch (Exception e) {
			// TODO: handle exception
		}
		return 0;
	}
	
	
	public boolean SQLtran(int uidOth,double money) {//转账
		 try {//防止异常情况
	            con.setAutoCommit(false);//不自动提交
	            String string[]=new String[2];
	             string[0] = "update users set cash=cash+'%s' where uid=110  uid=''";
	             string[1]= "update users set cash=cash+'%s' where uid=110  uid=''";
	            for(int x=0;x<2;x++){
	            	PreparedStatement statement = con.prepareStatement(string[x]);
	 	            int dbResult = statement.executeUpdate();	
	            }
	            con.commit();//执行成功，提交
	            return true;
	        } catch (SQLException e) {
	            // TODO Auto-generated catch block
	            try {
	                con.rollback();//执行失败，回滚
	            } catch (SQLException e1) {
	                e1.printStackTrace();
	            }
	            e.printStackTrace();
	            return false;
	        }
	}
}
