cataitemdb.java

网上购物系统 管理员模块和用户模块

package db;



import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import m.CatalogItem;

public class CataItemDB {
	Connection con;
	PreparedStatement pstm;
	ResultSet rs=null;
	public CataItemDB()
	{
		try {
			Class.forName("com.mysql.jdbc.Driver");
			con=DriverManager.getConnection("jdbc:mysql://localhost:3306/t227","root","1234");
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	public CatalogItem ser(String ID)
	{
		CatalogItem item;
		try {
			pstm=con.prepareStatement("select * from catalog where ID=?");
			pstm.setString(1, ID);
			
			rs=pstm.executeQuery();
			if(rs.next())
			{
				item=new CatalogItem(rs.getInt("ID"),rs.getString("name"),rs.getDouble("cost"),rs.getInt("number"));
				return item;
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	public ArrayList<CatalogItem> serall()
	{
		ArrayList<CatalogItem> aa=new ArrayList<CatalogItem>();
		try {
			pstm=con.prepareStatement("select * from catalog");

			rs=pstm.executeQuery();
			while(rs.next())
			{
				aa.add(new CatalogItem(rs.getInt("ID"),rs.getString("name"),rs.getDouble("cost"),rs.getInt("number")));
			}
			return aa;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	public ArrayList<CatalogItem> serall(String name)
	{
		ArrayList<CatalogItem> aa=new ArrayList<CatalogItem>();
		try {
			pstm=con.prepareStatement("select * from catalog where name like ?");
			pstm.setString(1,"%"+name+"%");
			
			rs=pstm.executeQuery();
			while(rs.next())
			{
				aa.add(new CatalogItem(rs.getInt("ID"),rs.getString("name"),rs.getDouble("cost"),rs.getInt("number")));
			}
			return aa;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	/*public void updateNumber(CatalogItem item,ItemOrder order)
	{
		try {
			pstm=con.prepareStatement("select * from catalog where ID=? ");
			pstm.setString(1,item.getID());
			
			
			rs=pstm.executeQuery();
			if(rs.next())
			{
				updateNM(item,order);
				
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	
		
	}
	public void updateNM(CatalogItem item,ItemOrder order)
	{
		try {
			pstm=con.prepareStatement("update catalog set number=?");
			pstm.setInt(1, (item.getItemNumber()-order.getNumber()));
			pstm.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}*/
	public ResultSet serall1()
	{
		ResultSet rs = null;
		try {
			pstm=con.prepareStatement("select * from catalog");
			rs=pstm.executeQuery();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return rs;
		
	}
	public void insertItem(String ID,String name,double cost,int number)
	{
		try {
			pstm=con.prepareStatement("insert into catalog (ID,name,cost,number) values(?,?,?,?)");
			pstm.setString(1, ID);
			pstm.setString(2, name);
			pstm.setDouble(3, cost);
			pstm.setInt(4, number);
			pstm.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	public void del(String ID)
	{
		try {
			pstm=con.prepareStatement("delete from catalog where ID=?");
			pstm.setString(1, ID);
			pstm.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}
	
		public void update(String ID,String name,double cost)
		{
			try {
				pstm=con.prepareStatement("update catalog set name=?,cost=? where ID=?");
				pstm.setString(1, name);
				pstm.setDouble(2, cost);
				pstm.setString(3, ID);
				pstm.executeUpdate();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	
}