logic.java

用jsp做的一个小例子

package com.today.prodect.model;

import java.io.FileNotFoundException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.today.common.DBCon;
import com.today.prodect.vo.Prodect;

public class Logic extends DBCon{
	Connection conn=null;
	Statement stmt=null;
	PreparedStatement pstmt=null;
	ResultSet rs=null;
	public byte[] getByte(int id){
		conn=this.getConn();
		byte[] b=null;
		String sql="select p_img from prodect_table where p_id=?";
		System.out.println(sql);
		try {
			pstmt=conn.prepareStatement(sql);
			pstmt.setInt(1, id);
			rs=pstmt.executeQuery();
		
			if(rs.next()){
				b=rs.getBytes(1);
				try {
					java.io.FileOutputStream in=new java.io.FileOutputStream("d:\\CarC11.jpg");
					
					in.write(b);
					in.close();
				} catch (Exception e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			
				
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			close(rs);
			close(stmt);
			close(conn);
		}
		return b;
		
	}
	public int insertProcedure(Prodect pro){
		int i=0;
		conn=this.getConn();
		String sql="insert into prodect_table (p_name,p_img,p_content,p_price,p_ipath) values (?,?,?,?,?)";
		
		try {
			pstmt=conn.prepareStatement(sql);
			pstmt.setString(1, pro.getP_name());
		    pstmt.setBytes(2, pro.getP_img());
		    pstmt.setString(3, pro.getP_content());
			pstmt.setFloat(4, pro.getP_price());
			pstmt.setString(5, pro.getP_ipath());
			i=pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			close(pstmt);
			close(conn);
		}
		return i;
	}
	public int updateProdect(Prodect pro){
		int i=0;
		conn=this.getConn();
        String sql="update prodect_table set p_name=? , p_content=? , p_price=?  where p_id=? ";
		try {
			pstmt=conn.prepareStatement(sql);
			pstmt.setString(1, pro.getP_name());
		    pstmt.setString(2, pro.getP_content());
			pstmt.setFloat(3, pro.getP_price());
			pstmt.setInt(4, pro.getP_id());
			i=pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			close(pstmt);
			close(conn);
		}
	return i;
	}
	
	public int getCountRows(String table_name){
	    int countRows=0;
		Connection conn=this.getConn();
		String sql=" select count(*) from "+table_name;
		ResultSet rs=null;
		Statement stmt=null;
		try {
			 stmt=conn.createStatement();
			rs=stmt.executeQuery(sql);
			if(rs.next()){
				countRows=rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			close(rs);
			close(stmt);
			close(conn);
		}
		return countRows;
		
		
	}
	public Prodect selectOne(int id){
		Prodect pro=new Prodect();
		conn=this.getConn();
		String sql="select p_id,p_name,p_img,p_content,p_price,p_ipath from prodect_table where p_id=?";
		try {
			pstmt=conn.prepareStatement(sql);
			pstmt.setInt(1, id);
			rs=pstmt.executeQuery();
		
			if(rs.next()){
				pro.setP_id(rs.getInt("p_id"));
				pro.setP_name(rs.getString("p_name"));
				pro.setP_img(rs.getBytes("p_img"));
				pro.setP_content(rs.getString("p_content"));
				pro.setP_price(rs.getFloat("p_price"));
				pro.setP_ipath(rs.getString("p_ipath"));
			}
		} catch (SQLException e) {
			
			e.printStackTrace();
		}finally{
			close(rs);
			close(stmt);
			close(conn);
		}
		return pro;
		
		
	}
	public List listProdect(){
		List list=new ArrayList();
		conn=this.getConn();
		try {
			stmt=conn.createStatement();
			rs=stmt.executeQuery("select p_id,p_name,p_img,p_content,p_price,p_ipath from prodect_table");
			while(rs.next()){
				Prodect pro=new Prodect();
				pro.setP_id(rs.getInt("p_id"));
				pro.setP_name(rs.getString("p_name"));
				pro.setP_img(rs.getBytes("p_img"));
				pro.setP_content(rs.getString("p_content"));
				pro.setP_price(rs.getFloat("p_price"));
				pro.setP_ipath(rs.getString("p_ipath"));
				list.add(pro);
			}
		} catch (SQLException e) {
			
			e.printStackTrace();
		}finally{
			close(rs);
			close(stmt);
			close(conn);
		}
		return list;
	}
	public List pageProdect(int per_page,int length){
		List list=new ArrayList();
		conn=this.getConn();
		String sql="select p_id,p_name,p_img,p_content,p_price,p_ipath from prodect_table";
		try {
			pstmt=conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
		    rs=pstmt.executeQuery();
			if(per_page>1){
				rs.absolute((per_page-1)*length);
				
			}
			
			while(rs.next()&&length-->0){
				Prodect pro=new Prodect();
				pro.setP_id(rs.getInt("p_id"));
				pro.setP_name(rs.getString("p_name"));
				pro.setP_img(rs.getBytes("p_img"));
				pro.setP_content(rs.getString("p_content"));
				pro.setP_price(rs.getFloat("p_price"));
				pro.setP_ipath(rs.getString("p_ipath"));
				list.add(pro);
			}
		} catch (SQLException e) {
			
			e.printStackTrace();
		}finally{
			close(rs);
			close(stmt);
			close(conn);
		}
		return list;
	}
	public void deleteProdect(int id){
		conn=this.getConn();
		String sql="delete from prodect_table where p_id="+id;
		try {
			stmt=conn.createStatement();
			stmt.executeUpdate(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			close(stmt);
			close(conn);
		}
	}

}