MVC分层+JDBC+数据库+Servlet实现JSP文件上传和显示+模糊查询+分页

本文章主要介绍了一个简单的实例：用简单的MVC分层思想，使用数据库在web界面进行增删改查，以及上传，模糊查询+分页的实现。

1.util层（数据库连接管理） ：连接数据库需要导包：mysql-connector-java-5.1.22-bin.jar

/**
 * Copyright (C), 2001-2015
 * This program is protected by copyright laws.
 * Project:			SMS
 * Comments:	Connection Maintain
 * JDK version: 	1.7
 * Author:			ybgong,23665701@qq.com
 * Create Date: 	2015/1/1
 * Modified By:
 * Modified Date:
 * Version  1.0
 */
package com.fruits.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

//数据库连接管理 
public class ConnectionManager {

	private static final String DRIVER_CLASS = "com.mysql.jdbc.Driver";
	private static final String DATABASE_URL = "jdbc:mysql://localhost:3306/fruits?useUnicode=true&characterEncoding=UTF-8";
	private static final String DATABASE_USRE = "root";
	private static final String DATABASE_PASSWORD = "123456";

	// 返回连接
	public static Connection getConnection() {
		Connection dbConnection = null;
		try {
			Class.forName(DRIVER_CLASS);
			dbConnection = DriverManager.getConnection(DATABASE_URL,
					DATABASE_USRE, DATABASE_PASSWORD);
		} catch (Exception e) {
			e.printStackTrace();
		}

		return dbConnection;
	}

	// 关闭连接
	public static void closeConnection(Connection dbConnection) {
		try {
			if (dbConnection != null && (!dbConnection.isClosed())) {
				dbConnection.close();
			}
		} catch (SQLException sqlEx) {
			sqlEx.printStackTrace();
		}

	}

	// 关闭结果集
	public static void closeResultSet(ResultSet res) {
		try {
			if (res != null) {
				res.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally{
			res = null;
			
		}
	}

	/**
	 * 关闭语句
	 * 
	 * @param pStatement
	 *            PreparedStatement
	 */
	public static void closeStatement(PreparedStatement pStatement) {
		try {
			if (pStatement != null) {
				pStatement.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally{

			pStatement = null;
		}
	}
	
	public static void closeSt(Statement Statement) {
		try {
			if (Statement != null) {
				Statement.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally{
			
			Statement = null;
		}
	}
}

2.mode（实体）层：

package com.fruits.model;
/**
 * 
 * gId:		商品id；
 * gName：商品名称；
 * gPrice：	商品标价；
 * tId：	类型ID；
 * sId：	商店ID；
 * gStocks：库存量；
 * gImageUrl：	商品图片路径；
 * gIntroduce	：	商品介绍；
 * @author UC
 *
 */
public class Goods {
	private  String gId;
	private String gName;
	private double gPrice;
	private String tId;
	private	String sId;
	private double gStocks;
	private String gImageUrl;
	private String gIntroduce;
	
	
	
	public Goods() {
		super();
	}
	public Goods(String gId, String gName,  double gPrice,String tId,
			String sId, double gStocks, String gImageUrl, String gIntroduce) {
		super();
		this.gId = gId;
		this.gName = gName;
		this.gPrice = gPrice;
		this.tId = tId;
		this.sId = sId;
		this.gStocks = gStocks;
		this.gImageUrl = gImageUrl;
		this.gIntroduce = gIntroduce;
	}
	public String getgId() {
		return gId;
	}
	public void setgId(String gId) {
		this.gId = gId;
	}
	public String getgName() {
		return gName;
	}
	public void setgName(String gName) {
		this.gName = gName;
	}
	public String gettId() {
		return tId;
	}
	public void settId(String tId) {
		this.tId = tId;
	}
	public double getgPrice() {
		return gPrice;
	}
	public void setgPrice(double gPrice) {
		this.gPrice = gPrice;
	}
	public String getsId() {
		return sId;
	}
	public void setsId(String sId) {
		this.sId = sId;
	}
	public double getgStocks() {
		return gStocks;
	}
	public void setgStocks(double gStocks) {
		this.gStocks = gStocks;
	}
	public String getgImageUrl() {
		return gImageUrl;
	}
	public void setgImageUrl(String gImageUrl) {
		this.gImageUrl = gImageUrl;
	}
	public String getgIntroduce() {
		return gIntroduce;
	}
	public void setgIntroduce(String gIntroduce) {
		this.gIntroduce = gIntroduce;
	}		
	
}

<span style="font-size:10px;">3.dao（接口)层：</span>

package com.fruits.dao;

import java.util.List;

import com.fruits.model.Goods;

public interface IGoods {
	//增加商品
	int addGoods(Goods goods);
	//删除商品
	int deleteGoods(String gid);
	//查看商品（模糊查询）
	List<Goods>getGoodsByName(String gname);
	//查看商品（通过gid查询）
	List<Goods>getGoodsById(String gid);
	//得到总页数
	int allPage(int row,String gname);
	//根据分页显示
	
	List<Goods>getGoodsPage(int page,int pagesize);
	//分页显示（模糊查询）
	List<Goods> getGoods(int page, int pagesize,String gname);
	//修改商品
	int updateGoods(Goods goods);
}

4.dao.impl(实现)层：

package com.fruits.dao.impl;

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

import com.fruits.dao.IGoods;
import com.fruits.model.Goods;
import com.fruits.util.ConnectionManager;

public class GoodsImpl  implements IGoods{
	private Connection connection;
	private PreparedStatement pst;
	private ResultSet results;
	/**
	 * 添加商品
	 */
	@Override
	public int addGoods(Goods goods) {
		// TODO Auto-generated method stub
		try {
			String sql = "insert into Goods(gid,gname,gprice,tid,sid,gstocks,gimageurl,gintroduce)value(?,?,?,?,?,?,?,?)";
			//String sql = "insert into Goods value('123', '1', 1.0, '101', '111', 1,'1', '1')";
			connection = ConnectionManager.getConnection();
			pst=connection.prepareStatement(sql);
			pst.setString(1, goods.getgId());
		pst.setString(2, goods.getgName());
			pst.setDouble(3, goods.getgPrice());
			pst.setString(4, goods.gettId());
			pst.setString(5, goods.getsId());
			pst.setDouble(6,goods.getgStocks() );
			pst.setString(7, goods.getgImageUrl());
			pst.setString(8, goods.getgIntroduce());
			return pst.executeUpdate();
			
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return 0;
		}
		finally{
			ConnectionManager.closeStatement(pst);
			ConnectionManager.closeConnection(connection);
		}
	}
	/**
	 * 删除商品
	 */
	@Override
	public int deleteGoods(String gid) {
		// TODO Auto-generated method stub
		try {
			String sql = "delete from Goods where gid=?";
			connection = ConnectionManager.getConnection();
			pst = connection.prepareStatement(sql);
			pst.setString(1, gid);
			return pst.executeUpdate();
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return  0;
		}
		finally{
			ConnectionManager.closeStatement(pst);
			ConnectionManager.closeConnection(connection);
		}
	}
	
	/**
	 * 通过gid查询商品
	 * 
	 */
	@Override
	
	public List<Goods>getGoodsById(String gid){
		List<Goods> goodsList = new ArrayList<Goods>();
		String sql = "select * from Goods where gid =?";
		try {
			connection = ConnectionManager.getConnection();
			pst = connection.prepareStatement(sql);
			pst.setString(1, gid);
			results = pst.executeQuery();
			while(results.next()){
				goodsList.add(new Goods(results.getString(1),results.getString(2),
						results.getDouble(3),results.getString(4),results.getString(5),
						results.getDouble(6),results.getString(7),results.getString(8)));
			}
			
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			ConnectionManager.closeResultSet(results);
			ConnectionManager.closeStatement(pst);
			ConnectionManager.closeConnection(connection);
		}
		
		return goodsList;
	}
	
	/**
	 * 查询商品（通过商品名模糊查询）
	 */
	@Override
	public List<Goods> getGoodsByName(String gname) {
		// TODO Auto-generated method stub
		String sql = "select * from goods where gname like  '%"+gname+"%' ";
		List<Goods> list = new ArrayList<Goods>();
		try {
			
			connection = ConnectionManager.getConnection();
			pst = connection.prepareStatement(sql);
			results = pst.executeQuery();
			while(results.next()){
				list.add(new Goods(results.getString(1),results.getString(2),results.getDouble(3),results.getString(4),
						results.getString(5),results.getDouble(6),results.getString(7),
						results.getString(8)));
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		finally{
			ConnectionManager.closeResultSet(results);
			ConnectionManager.closeStatement(pst);
			ConnectionManager.closeConnection(connection);
		}
		return list;
	}
	/**
	 * 更新商品
	 */
	@Override
	public int updateGoods(Goods goods) {
		// TODO Auto-generated method stub
		String sql = "UPDATE goods SET gname=?,gprice=?,tid=?,sid=?,gstocks=? ,gimageurl =? ,gintroduce=? WHERE gid=?";
		try {
			connection = ConnectionManager.getConnection();
			pst = connection.prepareStatement(sql);
			pst.setString(1, goods.getgName());
			pst.setDouble(2, goods.getgPrice());
			pst.setString(3, goods.gettId());
			pst.setString(4, goods.getsId());
			pst.setDouble(5,goods.getgStocks() );
			pst.setString(6, goods.getgImageUrl());
			pst.setString(7, goods.getgIntroduce());
			pst.setString(8, goods.getgId());
			return pst.executeUpdate();
			
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return 0;
		}
		finally{
			ConnectionManager.closeStatement(pst);
			ConnectionManager.closeConnection(connection);
		}
	}
	/*
	 * 
	 * 获取总页数用于分页
	 * row：每页显示数量
	 */
	@Override
	public int allPage(int row,String gname) {
		// TODO Auto-generated method stub
		List<Goods> goodsList = new ArrayList<Goods>();
		int allp=0;
		try {
			connection = ConnectionManager.getConnection();
			pst = connection.prepareStatement("select * from Goods where gname like'%"+gname+"%' ");
			results = pst.executeQuery();
			while(results.next()){
				goodsList.add(new Goods(results.getString(1),results.getString(2),results.getDouble(3),results.getString(4),
						results.getString(5),results.getDouble(6),results.getString(7),
						results.getString(8)));
			}
		/*	results = pst.getResultSet();
			results.next();*/
			int all = goodsList.size();
//			int all  = results.getInt(1);
			allp &