package com;

import java.sql.*;


public class JDBCUtil {
	private static String strDrive = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
	private static String url = "jdbc:sqlserver://127.0.0.1:1433;DatabaseName=ShopSystem";
	private static String uid = "sa";
	private static String pwd = "123";
	private static Connection conn = null;

	// 注册驱动
	static {
		try {
			Class.forName(strDrive);
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	// 建立连接
	public static Connection getConnection() {

		try {
			conn = DriverManager.getConnection(url, uid, pwd);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return conn;
	}

	// Statement查询
	public static ResultSet doQuery(String sql) {
		Statement st = null;
		ResultSet rs = null;
		try {
			st = conn.createStatement();
			rs = st.executeQuery(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return rs;
	}
	
	
	// PreparedStatement查询
	public static ResultSet doParamQuery(String sql,Object[] params){
		PreparedStatement pst=null;
		ResultSet rs=null;
		try{
			pst=conn.prepareStatement(sql);
			for(int i=0;i<params.length;i++)
			{
				pst.setObject(i+1,params[i]);
			}
			rs=pst.executeQuery();
		}catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return rs;
	}
	
	
	
	//Statement插入，删除，修改
	public static int doUpdate(String sql) {
		int num=0;
		Statement st=null;
		try {
			st = conn.createStatement();
			num=st.executeUpdate(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			num=-1;
		}
		return num;
	}
	
	
	
	//PreparedStatement插入，删除，修改
	public static int doParamUpdate(String sql,Object[] params){
		int num=0;
		PreparedStatement pst=null;
		try{
			pst=conn.prepareStatement(sql);
			for(int i=0;i<params.length;i++)
			{
				pst.setObject(i+1,params[i]);
			}
			num=pst.executeUpdate();
		}catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			num=-1;
		}
		return num;
	}
	

	// 关闭连接
	public static void closeAll(ResultSet rs, Statement st, Connection conn) {
		try {
			if (rs != null)
				rs.close();
		} catch (SQLException e) {
		} finally {
			try {
				if (st != null)
					st.close();
			} catch (SQLException e) {
			} finally {
				if (conn != null)
					try {
						conn.close();
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
			}
		}
	}
}