ICode9

精准搜索请尝试: 精确搜索
首页?>?bet356客服邮箱> 文章详细

使用JDBC连接MySQL数据库操作增删改查

2019-09-06 22:09:30??阅读:58??来源: 互联网

标签:JDBC?改查?throws?request?response?MySQL?import?servlet?public


更多精彩内容欢迎访问我的个人博客皮皮家园:http://www.zhsh666.xyz或者http://www.zh66.club期待您的光临哦!我是皮皮猪,感谢各位光临,能为您排忧解难小站深感荣幸!祝您生活愉快!

文章目录

1.首先这个Myeclipse的包名以及一些实现的类(这样子写是我的习惯)

[外链图片转存失败(img-33fh5Nqc-1567778008409)(https://cdn.jsdelivr.net/gh/Zevs6/blogimg/img/201803292248062741)]

2.接下来我们创建数据库(MySQL)

3.在数据库里面添加数据

[外链图片转存失败(img-gaEzca3V-1567778008410)(https://cdn.jsdelivr.net/gh/Zevs6/blogimg/img/201803292305071371)]

4.首先是BaseDao,这个是重中之重,注意那个数据库的名字,强调所在的包名

package dao;
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
 
public class BaseDao {
 
	/***
	 * 
	 * @author 数据库连接类
	 *
	 */
		private String driver ="com.mysql.jdbc.Driver";
		private String url="jdbc:mysql://localhost:3306/表名";        ---------自己写数据库表名,只要数据库的表名跟这里的一样就行
		private String name="数据库名称";      ----------你自己数据库的名称
		private String pwd="密码";        -----------你自己数据库的密码
	      Connection conn=null;
	      /***
	       * 
	       * @return 打开连接
	       */
	    /*  public Connection getconn(){
	  		Connection conn=null;
	  		Context ctx;
	  		try {
	  			ctx = new InitialContext();
	  			DataSource ds=(DataSource)ctx.lookup("java:comp/env/jdbc/news");	
	  	    	conn=ds.getConnection();
	  		}catch (Exception e) {
	  			e.printStackTrace();
	  		}
	  		return conn;
	  	}     */
		protected  Connection getconn(){
			conn=null;	
			try {
				Class.forName(driver);
				conn=DriverManager.getConnection(url,name,pwd);
			} catch (ClassNotFoundException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}		
			return conn;
		}
		
		/****
		 * 
		 * @param 关闭数据库连接
		 */
		protected void closeAll(Connection conn ,PreparedStatement ps,ResultSet rs){		
			if(rs!=null)
				try {
					if(rs!=null)
					rs.close();
					if(ps!=null)
					ps.close();
					if(conn!=null)
					conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}				
		}
		/***
		 * 
		 * @param 增删改方法
		 * @param 接受 参数为 SQL语句 和 对象数组
		 * @return 返回受影响行数
		 */
		public int executeUpdate(String sql ,Object []ob){
			conn=getconn();
			PreparedStatement ps=null;
			try {
				ps=prepareStatement(conn,sql,ob);
				int i=ps.executeUpdate();
				return i;	
			} catch (SQLException e) {
				// TODO Auto-generated catch block
			    //	e.printStackTrace();
				return 0;
			}finally{			
				closeAll(conn, ps, null);
			}
		
		}	
		/***
		 * 查询方法
		 */
		protected PreparedStatement prepareStatement(Connection conn,String sql,Object []ob){		
			PreparedStatement ps=null;
					try {
						int index=1;
						ps = conn.prepareStatement(sql);
							if(ps!=null&&ob!=null){
								for (int i = 0; i < ob.length; i++) {			
										ps.setObject(index, ob[i]);	
										index++; 
								}
							}
					} catch (SQLException e1) {
						e1.printStackTrace();
					}
			 return ps;
		}
	
}

5.这个是实体类,我相信大家都会写,注意所在的包名

package entity;
 
public class Booking {
	private int id;
	private int categoryId;
	private String title;
	private String summary;
	private String uploaduser;
	private String createdate;
	
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public int getCategoryId() {
		return categoryId;
	}
	public void setCategoryId(int categoryId) {
		this.categoryId = categoryId;
	}
	public String getTitle() {
		return title;
	}
	public void setTitle(String title) {
		this.title = title;
	}
	public String getSummary() {
		return summary;
	}
	public void setSummary(String summary) {
		this.summary = summary;
	}
	public String getUploaduser() {
		return uploaduser;
	}
	public void setUploaduser(String uploaduser) {
		this.uploaduser = uploaduser;
	}
	public String getCreatedate() {
		return createdate;
	}
	public void setCreatedate(String createdate) {
		this.createdate = createdate;
	}
	
	
}

6.接下来我们写BookingDao

package dao;
 
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import entity.Booking;
 
public class BookingDao extends BaseDao{
	
	public List search(String sql,Object...params){
		List list =new ArrayList();
		Connection conn=this.getconn();
		PreparedStatement pst=null;
		ResultSet rs=null;
		try {
			pst=this.prepareStatement(conn, sql, params);
			rs=pst.executeQuery();
			while(rs.next()){
				Booking wor=new Booking();
				wor.setId(rs.getInt(1));
				wor.setCategoryId(rs.getInt(2));
				wor.setTitle(rs.getString(3));
				wor.setSummary(rs.getString(4));
				wor.setUploaduser(rs.getString(5));
				wor.setCreatedate(rs.getString(6));
				list.add(wor);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			closeAll(conn, pst, rs);
		}
		return list;
	}
	
	//查询表
	public List findAll(){
		String sql="SELECT * FROM `Book`";
		return search(sql);
	}
	
	//添加方法
	public int insert(Booking t){
		String str="INSERT INTO `book`(categoryId,title,summary,uploaduser,createdate) VALUE(?,?,?,?,?)";
		return executeUpdate(str, new Object[]{t.getCategoryId(),t.getTitle(),t.getSummary(),t.getUploaduser(),t.getCreatedate()});
	}
	
	//修改方法
	public int update(Booking r){
		String sql="UPDATE `book` SET `categoryId`=?,`title`=?,`summary`=?,`uploaduser`=?,`createdate`=? WHERE id=?";
		return executeUpdate(sql, new Object[]{r.getCategoryId(),r.getTitle(),r.getSummary(),r.getUploaduser(),r.getCreatedate(),r.getId()});
	}
	
	//删除方法
	public int delete(Booking e){
		String sql="DELETE FROM `book` WHERE id=?";
		return executeUpdate(sql, new Object[]{e.getId()});
	}
	
	
}

7.下面我们写Servlet

1.查询的Servlet

package servlet;
 
import java.io.IOException;
import java.io.PrintWriter;
import java.util.List;
 
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
 
import dao.BookingDao;
import entity.Booking;
 
public class selectServlet extends HttpServlet {
 
	/**
	 * Constructor of the object.
	 */
	public selectServlet() {
		super();
	}
 
	/**
	 * Destruction of the servlet. 
*/ public void destroy() { super.destroy(); // Just puts "destroy" string in log // Put your code here } /** * The doGet method of the servlet.
* * This method is called when a form has its tag value method equals to get. * * @param request the request send by the client to the server * @param response the response send by the server to the client * @throws ServletException if an error occurred * @throws IOException if an error occurred */ public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { doPost(request, response); } /** * The doPost method of the servlet.
* * This method is called when a form has its tag value method equals to post. * * @param request the request send by the client to the server * @param response the response send by the server to the client * @throws ServletException if an error occurred * @throws IOException if an error occurred */ public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { request.setCharacterEncoding("utf-8"); response.setCharacterEncoding("utf-8"); response.setContentType("text/html"); PrintWriter out = response.getWriter(); String opr=request.getParameter("opr"); if(opr==null||opr.equals("list")){ //刷新 BookingDao goodsDao=new BookingDao(); List list=goodsDao.findAll(); request.getSession().setAttribute("list", list); response.sendRedirect("index.jsp"); } out.flush(); out.close(); } /** * Initialization of the servlet.
* * @throws ServletException if an error occurs */ public void init() throws ServletException { // Put your code here } }

2.添加的Servlet

package servlet;
 
import java.io.IOException;
import java.io.PrintWriter;
import java.util.List;
 
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
 
import dao.BookingDao;
import entity.Booking;
 
public class insertServlet extends HttpServlet {
 
	/**
	 * Constructor of the object.
	 */
	public insertServlet() {
		super();
	}
 
	/**
	 * Destruction of the servlet. 
*/ public void destroy() { super.destroy(); // Just puts "destroy" string in log // Put your code here } /** * The doGet method of the servlet.
* * This method is called when a form has its tag value method equals to get. * * @param request the request send by the client to the server * @param response the response send by the server to the client * @throws ServletException if an error occurred * @throws IOException if an error occurred */ public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { doPost(request, response); } /** * The doPost method of the servlet.
* * This method is called when a form has its tag value method equals to post. * * @param request the request send by the client to the server * @param response the response send by the server to the client * @throws ServletException if an error occurred * @throws IOException if an error occurred */ public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { request.setCharacterEncoding("utf-8"); response.setCharacterEncoding("utf-8"); response.setContentType("text/html;charset=utf-8"); PrintWriter out = response.getWriter(); BookingDao rms=new BookingDao(); int categoryId=Integer.parseInt(request.getParameter("categoryId")); String title=request.getParameter("title"); String summary=request.getParameter("summary"); String uploaduser=request.getParameter("uploaduser"); String createdate=request.getParameter("createdate"); Booking rm=new Booking(); rm.setCategoryId(categoryId); rm.setTitle(title); rm.setSummary(summary); rm.setUploaduser(uploaduser); rm.setCreatedate(createdate); int i=rms.insert(rm); if(i>0){ out.print("true"); //刷新 List listrm=rms.findAll(); request.getSession().setAttribute("list", listrm); }else{ out.print("false"); } out.flush(); out.close(); } /** * Initialization of the servlet.
* * @throws ServletException if an error occurs */ public void init() throws ServletException { // Put your code here } }

3.修改的Servlet

package servlet;
 
import java.io.IOException;
import java.io.PrintWriter;
import java.util.List;
 
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
 
import dao.BookingDao;
import entity.Booking;
 
public class updateServlet extends HttpServlet {
 
	/**
	 * Constructor of the object.
	 */
	public updateServlet() {
		super();
	}
 
	/**
	 * Destruction of the servlet. 
*/ public void destroy() { super.destroy(); // Just puts "destroy" string in log // Put your code here } /** * The doGet method of the servlet.
* * This method is called when a form has its tag value method equals to get. * * @param request the request send by the client to the server * @param response the response send by the server to the client * @throws ServletException if an error occurred * @throws IOException if an error occurred */ public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { doPost(request, response); } /** * The doPost method of the servlet.
* * This method is called when a form has its tag value method equals to post. * * @param request the request send by the client to the server * @param response the response send by the server to the client * @throws ServletException if an error occurred * @throws IOException if an error occurred */ public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { response.setContentType("text/html"); PrintWriter out = response.getWriter(); BookingDao booking=new BookingDao(); int id=Integer.parseInt(request.getParameter("id")); int categoryId=Integer.parseInt(request.getParameter("categoryId")); String title=request.getParameter("title"); String summary=request.getParameter("summary"); String uploaduser=request.getParameter("uploaduser"); String createdate=request.getParameter("createdate"); Booking rm=new Booking(); rm.setId(id); rm.setCategoryId(categoryId); rm.setTitle(title); rm.setSummary(summary); rm.setUploaduser(uploaduser); rm.setCreatedate(createdate); int i=booking.update(rm); if(i>0){ //刷新 List listrm=booking.findAll(); request.getSession().setAttribute("list", listrm); out.print(""); }else{ out.print(""); } out.flush(); out.close(); } /** * Initialization of the servlet.
* * @throws ServletException if an error occurs */ public void init() throws ServletException { // Put your code here } }

4.删除的Servlet

package servlet;
 
import java.io.IOException;
import java.io.PrintWriter;
import java.util.List;
 
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
 
import dao.BookingDao;
import entity.Booking;
 
public class deleteServlet extends HttpServlet {
 
	/**
	 * Constructor of the object.
	 */
	public deleteServlet() {
		super();
	}
 
	/**
	 * Destruction of the servlet. 
*/ public void destroy() { super.destroy(); // Just puts "destroy" string in log // Put your code here } /** * The doGet method of the servlet.
* * This method is called when a form has its tag value method equals to get. * * @param request the request send by the client to the server * @param response the response send by the server to the client * @throws ServletException if an error occurred * @throws IOException if an error occurred */ public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { doPost(request, response); } /** * The doPost method of the servlet.
* * This method is called when a form has its tag value method equals to post. * * @param request the request send by the client to the server * @param response the response send by the server to the client * @throws ServletException if an error occurred * @throws IOException if an error occurred */ public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { response.setContentType("text/html"); PrintWriter out = response.getWriter(); BookingDao rms=new BookingDao(); Booking rm=new Booking(); int id=Integer.parseInt(request.getParameter("id")); rm.setId(id); int i=rms.delete(rm); if(i>0){ List listrm=rms.findAll(); request.getSession().setAttribute("list", listrm); out.print(""); }else{ out.print(""); } out.flush(); out.close(); } /** * Initialization of the servlet.
* * @throws ServletException if an error occurs */ public void init() throws ServletException { // Put your code here } }

8.配置web.xml



  
    This is the description of my J2EE component
    This is the display name of my J2EE component
    selectServlet
    servlet.selectServlet
  
  
    This is the description of my J2EE component
    This is the display name of my J2EE component
    insertServlet
    servlet.insertServlet
  
  
    This is the description of my J2EE component
    This is the display name of my J2EE component
    updateServlet
    servlet.updateServlet
  
  
    This is the description of my J2EE component
    This is the display name of my J2EE component
    deleteServlet
    servlet.deleteServlet
  
  
    This is the description of my J2EE component
    This is the display name of my J2EE component
    idServlet
    servlet.idServlet
  
 
  
    selectServlet
    /selectServlet
  
  
    insertServlet
    /insertServlet
  
  
    updateServlet
    /updateServlet
  
  
    deleteServlet
    /deleteServlet
  
  
    idServlet
    /idServlet
  
  
    index.jsp
  
 


需要注意的是下面段,在上面是web.xml中比较下面,

标签声明了与该servlet相应的匹配规则,每个标签代表1个匹配规则。含了两个子元素和,元素给出的Servlet名字必须是 在元素中声明过的Servlet的名字。元素指定对应于Servlet的URL路 径,该路径是相对于Web应用程序上下文根的路径。


   
   


9.再次写JSP页面

1.index.jsp 初始界面

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%@ taglib  uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
 


  
    
    
    
	
	
	    
	
	
	
  
  
  
  
  	
     
    
    
    
    
          
电子图书列表
书品编号书名摘要上传人上传时间操作
${gd.categoryId} ${gd.title} ${gd.summary} ${gd.uploaduser} ${gd.createdate }

2.insert.jsp 添加页面

?

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
 


  
    
    
    
    
	
	
	    
	
	
	
 
	
	
 
  
  
  
  	

新增书本

书本编号:
书本名称:
摘要:
上传人:
上传时间:

?

3.Update.jsp 修改页面

? (1)这个是修改的主要

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
 


  
    
    
    
    
	
	
	    
	
	
	
 
  
  
  
    	

修改书本

书本编号:
书本名称:
摘要:
上传人:
上传时间:

(2)这个idServlet是我后面出现的BUG,我现在只能给又加了一个Servlet

package servlet;
 
import java.io.IOException;
import java.io.PrintWriter;
 
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
 
public class idServlet extends HttpServlet {
 
	/**
	 * Constructor of the object.
	 */
	public idServlet() {
		super();
	}
 
	/**
	 * Destruction of the servlet. 
*/ public void destroy() { super.destroy(); // Just puts "destroy" string in log // Put your code here } /** * The doGet method of the servlet.
* * This method is called when a form has its tag value method equals to get. * * @param request the request send by the client to the server * @param response the response send by the server to the client * @throws ServletException if an error occurred * @throws IOException if an error occurred */ public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { doPost(request, response); } /** * The doPost method of the servlet.
* * This method is called when a form has its tag value method equals to post. * * @param request the request send by the client to the server * @param response the response send by the server to the client * @throws ServletException if an error occurred * @throws IOException if an error occurred */ public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { response.setContentType("text/html"); PrintWriter out = response.getWriter(); int id=Integer.parseInt(request.getParameter("id")); request.getSession().setAttribute("idid", id); response.sendRedirect("Update.jsp"); out.flush(); out.close(); } /** * Initialization of the servlet.
* * @throws ServletException if an error occurs */ public void init() throws ServletException { // Put your code here } }

10. 最后这个是在浏览器中显示的效果,这个是查询效果,查询全表如下

1.查询

[外链图片转存失败(img-wKfi8YM4-1567778008411)(https://cdn.jsdelivr.net/gh/Zevs6/blogimg/img/201803292306105211)]

2.增加

3.修改(接下里我们把刚才我们添加的数据修改)

4.删除(这个效果可能不是很明显,但为了界面我还是把它给截图下来了)

? 我把刚才我们上面操作的添加和修改给删除掉看看效果!

[外链图片转存失败(img-UJdB1eFD-1567778008412)(https://cdn.jsdelivr.net/gh/Zevs6/blogimg/img/201803292353239561)]

好了,这个项目也算了做完了,是个完整的,虽然在往数据库传数据的时候出现了中文乱码,但是我会去调这个BUG的,但至少功能实现了,以上就是这个项目了,虽然在有些地方大家看到的效果不是跟明显,如果本篇文章看了对大家有帮助,麻烦大家点个赞,谢谢,如果对本篇有疑问或者是想要我一步一步做给你看,加我QQ3506346737(备注:CSDN博客+QQ名字),很期待您的好友申请,也很期待大家对本篇文章的一些改进建议,谢谢

附源码获取下载地址:百度网盘。下载代码后遇到问题及时联系本人,尽快修正错误。

链接:https://pan.baidu.com/s/1nlOSG10a0dW72qeeUKrS2g
提取码:ycqr



标签:JDBC,改查,throws,request,response,MySQL,import,servlet,public

专注分享技术,共同学习,共同进步。侵权联系[admin#icode9.com]

Copyright (C)ICode9.com, All Rights Reserved.

ICode9版权所有