package com.ata.shoping.servlet;




	import java.sql.*;
import java.util.*;

import com.projiect.shopping.sql.JDBC;
import com.projiect.shopping.sql.Product;

	public class Myjdbc {
		public ArrayList<Category> FindLoginAll()throws Exception{
			
			ArrayList<Category> msg=new ArrayList<Category>();
			msg=Select("select *  from category");
			return msg;
		}

		public static Connection getConnection()throws Exception{
			
			Connection con = null;
			//加載數(shù)據(jù)庫驅(qū)動類
			Class.forName("com.mysql.jdbc.Driver");
			//數(shù)據(jù)庫連接URL
			String url = "jdbc:mysql://192.168.2.187:3306/My_db";
			//取得數(shù)據(jù)庫連接
			con = DriverManager.getConnection(url,"root","root");
			return con;
		}
		
		public static ArrayList<Category> Select(String sql)throws Exception
		{
			Connection con = null;
			Statement st = null;
			ResultSet rs = null;
			ArrayList<Category> al = new ArrayList<Category>();
			con = getConnection();
			st = con.createStatement();
			rs=st.executeQuery(sql);
			
			while(rs.next()){
				Category pro= new Category();
				pro.setCid(rs.getString("cid"));
				pro.setCname(rs.getString("cname"));
				pro.setDescription(rs.getString("description"));
				al.add(pro);
			}
			
			rs.close();
			st.close();
			con.close();
			return al;
		}
		public static void Insert(ArrayList<Category> p,String sql)throws Exception{
			Connection con = null;
			con = getConnection();
			String s= sql;
			ArrayList<Category> np=p;
			Iterator<Category> it = np.iterator();
			Category newp= (Category)it.next();
			PreparedStatement pst = con.prepareStatement(s);
				pst.setString(1, newp.getCid());
				pst.setString(2, newp.getCname());
				pst.setString(3, newp.getDescription());
				pst.executeUpdate();
			                 

		}
		public  static void delete(String sql)throws Exception//根據(jù)傳入的sql語句刪除一條數(shù)據(jù)庫記錄
		{
			Connection conn=null;
			PreparedStatement ps=null;	
			try
			{
				conn=getConnection();
				ps=conn.prepareStatement(sql);
				ps.executeUpdate();
			}
			catch(SQLException sqle)
			{
				throw new Exception("delete data exception:"+sqle.getMessage());
			}
			finally
			{
				try
				{
					if(ps!=null)
					{
						ps.close();
					}
				}
				catch(Exception e)
				{
					throw new Exception("ps close exception:"+e.getMessage());
				}
			}
			try
			{
				if(conn!=null)
				{
					conn.close();
				}
			}
			catch(Exception e)
			{
				throw new Exception("connection close exception:"+e.getMessage());
			}	
		}
	}