nodedao.java

用户可以添加商品

package product.db;
import product.form.Node;
import java.sql.*;
import java.util.*;
import java.text.*;
public class NodeDAO {

 //数据库连接	
	String getNode = "SELECT * FROM Product WHERE id=?";
	String createNewNode="INSERT INTO Product(name,parent,size,unit,content,product_factory,product_supply,color,price_in,price_agent,price_sale,price_detail,specification,note,createDay,layer,nextId,flag)values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,now(),?,?,?)";
	String updateNode="UPDATE Product SET name=?,size=?,unit=?,content=?,product_factory=?,product_supply=?,color=?,price_in=?,price_agent=?,price_sale=?,price_detail=?,specification=?,note=? where id=?";

	public NodeDAO() {}

	public Node getNode(int id){
		Node node = null;
		Connection con=null;
		try{
			con = DatabaseConnection.getConnection();
			PreparedStatement pstmt = con.prepareStatement(getNode);
			pstmt.setInt(1,id);
			ResultSet rs = pstmt.executeQuery();
			java.text.DecimalFormat df=new DecimalFormat("##.0");
			if(rs.next()){
				node = new Node();
				node.setId(id);
				node.setName(rs.getString("name").replace(" ","  "));
				node.setParent(rs.getInt("parent"));
				node.setSize(rs.getString("size"));
				node.setUnit(rs.getString("unit"));
				node.setContent(rs.getString("content"));
				node.setProduct_factory(rs.getString("product_factory"));
				node.setProduct_supply(rs.getString("product_supply"));
				node.setColor(rs.getString("color"));
				node.setPrice_in(rs.getFloat("price_in"));
				node.setPrice_agent(rs.getFloat("price_agent"));
				node.setPrice_sale(rs.getFloat("price_sale"));
				node.setPrice_detail(rs.getFloat("price_detail"));
				node.setSpecification(rs.getString("specification"));
				node.setNote(rs.getString("note"));
				node.setCreateDay(rs.getDate("createDay"));	
				node.setLayer(rs.getInt("layer"));
				node.setNextId(rs.getInt("nextId"));
				
					if(Float.compare(rs.getFloat("price_detail"),0.0f)!=0&&Float.compare(rs.getFloat("price_in"),0.0f)!=0)
					{
						
						float t=(100*(rs.getFloat("price_detail")-rs.getFloat("price_in"))/rs.getFloat("price_in"));
						String temp=df.format(t)+"%";
						//System.out.println("temp===="+temp);
						node.setTemp(temp);	
				  }else{
				  	node.setTemp("");
				  	}
				  if(Float.compare(rs.getFloat("price_agent"),0.0f)!=0&&Float.compare(rs.getFloat("price_in"),0.0f)!=0)
					{
						
						float t1=(100*(rs.getFloat("price_agent")-rs.getFloat("price_in"))/rs.getFloat("price_in"));
						String temp1=df.format(t1)+"%";
						//System.out.println("temp===="+temp);
						node.setTemp1(temp1);	
				  }else{
				  	node.setTemp1("");
				  	}
				  if(Float.compare(rs.getFloat("price_sale"),0.0f)!=0&&Float.compare(rs.getFloat("price_in"),0.0f)!=0)
					{
						
						float t2=(100*(rs.getFloat("price_sale")-rs.getFloat("price_in"))/rs.getFloat("price_in"));
						String temp2=df.format(t2)+"%";
						//System.out.println("temp===="+temp);
						node.setTemp2(temp2);	
				  }else{
				  	node.setTemp2("");
				  	}			
			}
		}catch(Exception e){
		}finally{
			try{
				if(con!=null)
				con.close();
			}catch(Exception e2){}
		}
		return node;
	}	
	
	
	public void createNewNode(Node node){
		Connection con=null;
		try{
			con = DatabaseConnection.getConnection();
			PreparedStatement pstmt = con.prepareStatement(createNewNode);
			pstmt.setString(1,node.getName());
			pstmt.setInt(2,node.getParent());
			pstmt.setString(3,node.getSize());
			pstmt.setString(4,node.getUnit());
			pstmt.setString(5,node.getContent());
			pstmt.setString(6,node.getProduct_factory());
			pstmt.setString(7,node.getProduct_supply());
			pstmt.setString(8,node.getColor());
			pstmt.setFloat(9,node.getPrice_in());
			pstmt.setFloat(10,node.getPrice_agent());
			pstmt.setFloat(11,node.getPrice_sale());
			pstmt.setFloat(12,node.getPrice_detail());
			pstmt.setString(13,node.getSpecification());
			pstmt.setString(14,node.getNote());
			pstmt.setInt(15,node.getLayer());
			pstmt.setInt(16,node.getNextId());
			pstmt.setInt(17,node.getFlag());
			pstmt.executeUpdate();	
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			try{
				if(con!=null)
				con.close();
			}catch(Exception e2){}
		}
	}			

	public void updateNode(Node node,int id){
		Connection con=null;
		try{
			con = DatabaseConnection.getConnection();
			PreparedStatement pstmt = con.prepareStatement(updateNode);
			pstmt.setString(1,node.getName());
			pstmt.setString(2,node.getSize());
			pstmt.setString(3,node.getUnit());
			pstmt.setString(4,node.getContent());
			pstmt.setString(5,node.getProduct_factory());
			pstmt.setString(6,node.getProduct_supply());
			pstmt.setString(7,node.getColor());
			pstmt.setFloat(8,node.getPrice_in());
			pstmt.setFloat(9,node.getPrice_agent());
			pstmt.setFloat(10,node.getPrice_sale());
			pstmt.setFloat(11,node.getPrice_detail());
			pstmt.setString(12,node.getSpecification());
			pstmt.setString(13,node.getNote());
			pstmt.setInt(14,id);
			pstmt.executeUpdate();	
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			try{
				if(con!=null)
				con.close();
			}catch(Exception e2){}
		}
	}
	
				
	public Node searchNode(int layer,int parent){
		Connection con=null;
		Node node = null;
		String searchNode="SELECT * FROM Product WHERE layer="+layer+" and parent="+parent+" and nextId=-2 ORDER BY ID DESC LIMIT 1";
		try{
			con = DatabaseConnection.getConnection();
			PreparedStatement pstmt = con.prepareStatement(searchNode);
			ResultSet rs = pstmt.executeQuery();
			if(rs.next()){
				//System.out.println("hello");
				node = new Node();
				node.setId(rs.getInt("id"));
				node.setName(rs.getString("name"));
				node.setParent(rs.getInt("parent"));
				node.setSize(rs.getString("size"));
			    node.setUnit(rs.getString("unit"));
			    node.setContent(rs.getString("content"));
				node.setProduct_factory(rs.getString("product_factory"));
				node.setProduct_supply(rs.getString("product_supply"));
				node.setColor(rs.getString("color"));
				node.setPrice_in(rs.getFloat("price_in"));
				node.setPrice_agent(rs.getFloat("price_agent"));
				node.setPrice_sale(rs.getFloat("price_sale"));
				node.setPrice_detail(rs.getFloat("price_detail"));
				node.setSpecification(rs.getString("specification"));
				node.setNote(rs.getString("note"));
				node.setCreateDay(rs.getDate("createDay"));	
				node.setLayer(rs.getInt("layer"));
				node.setNextId(rs.getInt("nextId"));	
				}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			try{
				if(con!=null)
				con.close();
			}catch(Exception e2){}
		}
		return node;
	}	
	
	public int updateNextId(int nextId,int nodeId){
		Connection con=null;
		int i=0;
		String updateNextId="UPDATE Product SET nextId="+nextId+" where id="+nodeId;
		try{
			con = DatabaseConnection.getConnection();
			PreparedStatement pstmt = con.prepareStatement(updateNextId);
			i=pstmt.executeUpdate();	
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			try{
				if(con!=null)
				con.close();
			}catch(Exception e2){}
		}
		return i;
	}
}