categorydao.java

一个可以作为毕业设计的网上商城项目

package com.fang.shopping;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;


import com.fang.shppping.util.DB;



public class CategoryDAO {
	public static void save(Category c){
		Connection conn = null;
		PreparedStatement pstmt = null;
		try{
				conn = DB.getConn();
				String sql = null;
				
				if(c.getId()==-1){
					sql =  "insert into category values(null,?,?,?,?,?)";
				}else{
					sql =  "insert into category values(" + c.getId() + ",?,?,?,?,?)";
				}
			
				pstmt =  DB.getPStmt(conn, sql);
				pstmt.setString(1,c.getName());
				pstmt.setString(2, c.getDescr());
				pstmt.setInt(3, c.getPid());
				pstmt.setInt(4, c.isIslesf() ? 0:1);
				pstmt.setInt(5, c.getGrade());

				
				pstmt.executeUpdate();
			} catch (SQLException e) {
				e.printStackTrace();
			}finally{
			DB.close(pstmt);
			DB.close(conn);
		}
	}
	
	//注意这下面的两个方法，第一个调用了第二个，他这样的目的是为了提高效率和性能！
	
	public static void getCategory(List<Category> list , int id){
		Connection conn = null;
		try {
			conn = DB.getConn();
			getCategory(conn,list,id);
		}finally{
			DB.close(conn);
		}
		
	}
	
	 private static void getCategory(Connection conn, List<Category> list , int id)
	{
		

		ResultSet rs = null;
		
		try {
			conn = DB.getConn();
			String sql = "select * from category where pid =  " + id;
			rs = DB.executeQuery(conn, sql);
			while(rs.next()){
				Category c = new Category();
				c.setId(rs.getInt("id"));
				c.setName(rs.getString("name"));
				c.setDescr(rs.getString("descr"));
				c.setPid(rs.getInt("pid"));
				//注意这样的一种处理方式
				c.setIslesf(rs.getInt("isleaf") == 0 ? true : false);
				c.setGrade(rs.getInt("grade") );
				
				list.add(c);
				
				//采用递归的方式
				if(!c.islesf){
					getCategory(list,c.getId());
				}
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DB.close(rs);
			DB.close(conn);
		}

	}

	public static void addChildenCategory(int pid,String name,String descr)  {

		Connection conn = null;
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		
		try {
			conn = DB.getConn();
			
			conn.setAutoCommit(false);
			
			rs = DB.executeQuery(conn, "select * from category where id = " + pid);
			int parentGrade = 0;
			if(rs.next())
				parentGrade = rs.getInt("grade");
			//首次我们要做的就是存储category
			String sql =  "insert into category values(null,?,?,?,?,?)";
			pstmt =  DB.getPStmt(conn, sql);
			pstmt.setString(1,name);
			pstmt.setString(2,descr);
			pstmt.setInt(3, pid);
			pstmt.setInt(4, 0);
			pstmt.setInt(5, parentGrade +1);
			
			pstmt.executeUpdate();
			
			//然后我们就要更新父节点，设置为非叶子节点
			System.out.println("updata category set isleaf = 1 where id = " + pid);
			DB.executeUpdate(conn, "update category set isleaf = 1 where id = " + pid);
			
			conn.commit();
			conn.setAutoCommit(true);
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		}finally{
			DB.close(rs);
			DB.close(pstmt);
			DB.close(conn);
		}
		
	}

	public static Category loadById(int id) {
		Connection conn = null;
		Category c = null;
		ResultSet rs = null;
		
		try {
			conn = DB.getConn();
			rs = DB.executeQuery(conn, "select * from category where id = " + id);
			if(rs.next()){
				c = new Category();
				c.setId(rs.getInt("id"));
				c.setName(rs.getString("name"));
				c.setDescr(rs.getString("descr"));
				c.setPid(rs.getInt("pid"));
				c.setIslesf(rs.getInt("isleaf") == 0 ? true : false);
				c.setGrade(rs.getInt("grade") );
			}
		
			} catch (SQLException e1) {
				e1.printStackTrace();
			}finally{
			DB.close(rs);
			DB.close(conn);
		}
			return c;
	}

	public static void del(int id, int pid) {
			Connection conn = null;
			PreparedStatement pstmt = null;
			try {
				conn = DB.getConn();
				pstmt = DB.getPStmt(conn, sql);
				if()
				
				
			} catch (SQLException e) {
				e.printStackTrace();
			}finally{
				DB.close(stmt);
				DB.close(conn);
			}
			stmt.executeUpdate("delete from connection where id=" + id);
		}
		
		

	
}