DBUtil和一些简单的数据库操作

---

一、DBUtil

package util;

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


public class DBUtil {

	public static final String URL="jdbc:mysql://localhost:3306/text";
	public static final String USERNAME="root";
	public static final String PWD="yangzi588888";
	
	public static Connection connection=null;
	public static PreparedStatement pstmt=null;
	public static ResultSet rs=null;

	public static boolean executeUpdate(String sql,Object[] params){
		try {
			getPrepareStatement(sql, params);
			int count=pstmt.executeUpdate();
			if(count>0){
			  return true;
			}else {
				System.out.print("");
				return false;
			}	
		}catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}finally {
			closeAll();
		}
	
	}
	public static ResultSet executeQuery(String sql,Object[] params){
		try {
			
			rs=getPrepareStatement(sql, params).executeQuery();
			return rs;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}
	}
	public static int getTotalCount(String sql){
		int count=-1;
		try {
			PreparedStatement pstmt=getPrepareStatement(sql, null);
			rs=pstmt.executeQuery();
			if(rs.next()){
				count=rs.getInt(1);
				System.out.println("1");
			}
			System.out.println("2");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			System.out.println("3");
			e.printStackTrace();
			return count;
		}finally {
			closeAll();
		}
		
		return count;
		
	}
	/**
	 *鍒涘缓Connection
	 * @return Connection
	 */
	public static Connection getConnection(){
		try {
			Class.forName("com.mysql.jdbc.Driver");
			connection=DriverManager.getConnection(URL,USERNAME,PWD);
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return connection;
	}
	
	
	/**
	 *鍒涘缓PreparedStatement
	 * @param sql 
	 * @param params 
	 * @return PreparedStatement
	 * @throws SQLException
	 */
	public static PreparedStatement getPrepareStatement(String sql,Object[] params) throws SQLException {
		pstmt=getConnection().prepareStatement(sql);
		if(params!=null){
			for(int i=0;i<params.length;i++){
				pstmt.setObject((i+1),params[i]);
			}
		}
		return pstmt;
	}
	/**
	 *鍏抽棴
	 */
	public static void closeAll(){
		try {
			if(pstmt!=null){
				pstmt.close();
			}
			if(connection!=null){
				connection.close();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	

}

二、增删查改简单使用

1.Dao层

public class PictureDao {

	static Picture picture;
	
	public boolean isExist(String url){
		return queryPictureUrl(url)==null?false:true;
	}
	public boolean addPicture(Picture picture){
			String sql="insert into pictures(name,url) values(?,?)";
			Object[] params={picture.getName(),picture.getUrl()};
			return DBUtil.executeUpdate(sql, params);
	}


	public List<Picture> queryAllPicture() {
		ResultSet rs;
		Picture picture;
		List<Picture> pictures=new ArrayList<>();
		String sql = "select * from pictures limit 1,5";
		rs = DBUtil.executeQuery(sql, null);
		try {
			while (rs.next()) {
				String name = rs.getString("name");
				String url = rs.getString("url");
				picture=new Picture(name,url);
				pictures.add(picture);
			}
			return pictures;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			
		}finally {
			DBUtil.closeAll();
		}

		return null;

	}
	/**
	 *�õ�����
	 * @return
	 */
	public int getTotalCount(){
		String sql="select count(*) from pictures";
		return DBUtil.getTotalCount(sql);
	}
	
	public Picture queryPictureUrl(String url){
		ResultSet rs;
		Picture picture;
		try {
			String sql="select * from pictures where url =? ";
			Object[] params={url};
			rs = DBUtil.executeQuery(sql, params);
			if(rs.next()){
				String name1 = rs.getString("name");
				String url1 = rs.getString("url");
				picture=new Picture(name1,url1);
		
				return picture;
			}else {
				System.out.print("�Ѵ���");
				return null;
			}	
		}catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		
		}finally {
			DBUtil.closeAll();
		}
	}
	public List<Picture> queryPictureByPage(int page,int pageSize) {
		String sql="select * from pictures limit ?,?";
		Object[] params={page,pageSize};
		
		List<Picture> pictures=new ArrayList<>();
		ResultSet rsResultSet=DBUtil.executeQuery(sql, params);
		
		try {
			while (rsResultSet.next()) {
				Picture picture=new Picture(rsResultSet.getString("name"),rsResultSet.getString("url"));
				pictures.add(picture);
			}
			return pictures;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;

	}

}

2.service层

代码如下（示例）：

public class PictureService {
	static PictureDao pictureDao=new PictureDao();
	
	public static List<Picture> queryAllStudent(){
		return pictureDao.queryAllPicture();
	}
	
	public static boolean addPicture(Picture picture){

		  pictureDao.addPicture(picture);
		  return true;

	}
	public List<Picture> queryStudentByPage(int page,int pageSize){
		return pictureDao.queryPictureByPage(page, pageSize);
	}
	
	public int getTotalCount(){
		return pictureDao.getTotalCount();
	}
	
	public 	Picture queryById(String url ){
		return pictureDao.queryPictureUrl(url);
	}
	
	

}

2.servlet层

 	@WebServlet("/QueryPagePictureServet")
public class QueryPagePictureServet extends HttpServlet {

	@Override
	protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		PictureService service=new PictureService();
		int count=service.getTotalCount();
		int pageSize=5;
		int begin=0;
		String cPage=req.getParameter("currentPage");
		if(cPage==null){
			cPage="0";
		}
		int currentPage=Integer.parseInt(cPage);
		
	
		
		
		
		Page page=new Page();
		page.setTotalCount(count);
		
		page.setPageSize(pageSize);
		
		if(currentPage<0){
			currentPage=0;
		}else if(currentPage>page.getTotalPage()){
			currentPage=page.getTotalPage();
		}
		page.setCurrentPage(currentPage);
		List<Picture> pictures=service.queryStudentByPage(currentPage*pageSize, pageSize);
		page.setPictures((ArrayList<Picture>)pictures);
		
		
		System.out.println(page.getTotalCount()+"----");
		System.out.println(pageSize+"----");
		System.out.println(page.getTotalPage()+"----");
		System.out.println(currentPage);
		req.setAttribute("page", page);
		
		
		req.getRequestDispatcher("displayByPage.jsp").forward(req, resp);
		
	}

	@Override
	protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		// TODO Auto-generated method stub
		doGet(req, resp);
	}
	

}

三、jsp表格

<%@page import="java.util.List"%>
<%@page import="entity.Picture"%>
<%@page import="entity.Page"%>


<%@ page language="java" contentType="text/html; charset=utf-8"
    pageEncoding="utf-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>Insert title here</title>
</head>
<body>
<table border="1px">
	<tr>
		<th>名字</th>
		<th>图片</th>
	</tr>
	
	<%
	Page page1=(Page)request.getAttribute("page");
	for(Picture picture:page1.getPictures()){
	String url="/image/"+picture.getUrl();
	System.out.println();
	System.out.println(url);
	
	%>
		<tr>
		<td><a href="QueryByUrlServlet?url=<%=picture.getUrl()%>"><%=picture.getName() %></a></td>
		<td><img src="<%=url %>" width="50px" ;height="50px"/></td>
		</tr>
	<%
	}
	%>
<a href="QueryPagePictureServet?currentPage=0" >首页 </a>
<a href="QueryPagePictureServet?currentPage=<%=page1.getCurrentPage()-1 %>" > 上一页</a>
<a href="QueryPagePictureServet?currentPage=<%=page1.getCurrentPage()+1 %>" > 下一页</a>
<a href="QueryPagePictureServet?currentPage=<%=page1.getTotalPage() %>" > 尾页</a>
</body>
</html>

四.org.apache.commons.fileupload使用

@WebServlet("/DealPictureServlet")
public class DealPictureServlet extends HttpServlet {
	List<FileItem> fileItems;
	 public static String filename = null;
	 private static final long serialVersionUID = 1L;
	 @Override
	 public void doGet(HttpServletRequest request,HttpServletResponse response){
	 }
	 @Override
	 public void doPost(HttpServletRequest request,HttpServletResponse response) throws IOException, ServletException{
		 DiskFileItemFactory factory = new DiskFileItemFactory();
		  ServletFileUpload upload = new ServletFileUpload(factory);
		  factory.setSizeThreshold(1024 * 1024);
		  List items = null;
		  String value="";
		  
		 
		  try {
		   items = upload.parseRequest(request);
		  } catch (FileUploadException e) {
		   e.printStackTrace();
		  }
		  Iterator iter = items.iterator();
		  while(iter.hasNext()){
		   FileItem item = (FileItem) iter.next();
		   if(!item.isFormField()){
		    // 根据时间戳创建头像文件
		    filename = System.currentTimeMillis() + ".jpg";
		    System.out.println(request.getContextPath());
		    /*File f = new File(getServletContext().getRealPath("upload"));*/
		    File f = new File("D://image");
		    if (!f.exists()) { 
		     f.mkdir(); 
		    } 
		    String imgsrc = f + "/" + filename;
		    // 复制文件
		    InputStream is = item.getInputStream();    
		    FileOutputStream fos = new FileOutputStream(imgsrc);
	
		    byte b[] = new byte[1024 * 1024];
		    int length = 0;
		    while (-1 != (length = is.read(b))) {
		     fos.write(b, 0, length);     
		    }
		    fos.flush();
		    fos.close();
		    }else {
		    value = item.getString();
		    value = new String(value.getBytes("ISO-8859-1"), "UTF-8");
		    System.out.print(value);
		  
		   }
		 
		   response.sendRedirect("PictureAddServlet?filename="+filename + "&name="+value);  
		  }   
		 } 	

	 }