jdbc+mysql+eclipse详细增删改查

最新推荐文章于 2024-04-20 10:42:48 发布
互联网老欣
最新推荐文章于 2024-04-20 10:42:48 发布
阅读量1k
点赞数 2
分类专栏： javaweb
本文链接：https://blog.csdn.net/gusucheng/article/details/100785309
版权
javaweb 专栏收录该内容
0 篇文章 1 订阅
订阅专栏
框架:
框架2:
mysql:
注意:主键的自动递增
效果:
增加:
得有jstl的jar包和mysql-connector-java-5.1.7-bin.jar
一开始mysql-connector-java-5.1.7-bin.jar,右键单击,点出Build Path，然后Add to Build Path
成功后，看:
实体类
public class AdminEntity {
           private int id;
           private String username;
           private String userpwd;
           private int sale;
		public int getId() {
			return id;
		}
		public void setId(int id) {
			this.id = id;
		}
		public String getUsername() {
			return username;
		}
		public void setUsername(String username) {
			this.username = username;
		}
		public String getUserpwd() {
			return userpwd;
		}
		public void setUserpwd(String userpwd) {
			this.userpwd = userpwd;
		}
		public int getSale() {
			return sale;
		}
		public void setSale(int sale) {
			this.sale = sale;
		}
}

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 com.entity.AdminEntity;
import com.utils.DbHelper;

public class AdminDao {
   
	//增加需要jsp页面中的数据，传到Servlet中，再调用service里的方法
	//再调用这个方法，需要实体类。
	public boolean createAdmin(AdminEntity ad){
		//sun公司发布java.sql包可以连接各个数据库。
		Connection conn=DbHelper.getConnection();
		String sql="insert into admin values(?,?,?,?)";
		
		try {
			//PreparedStatement预编译，速度快，记得后面的少了个d prepareStatement
			PreparedStatement pst=conn.prepareStatement(sql);
			//ad.getId()得到实体类传来的数据，再设置到数据库。
			pst.setInt(1, ad.getId());
			pst.setString(2, ad.getUsername());
			pst.setString(3,ad.getUserpwd());
			pst.setInt(4,ad.getSale());
			
			int count=pst.executeUpdate();
			pst.close();
			return count >0? true:false;//是否添加的判断
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return false;	
	}
	
    //删除。不需要实体类的数据。它只要boolean，判断是否删除和int id根据id 删除。
	public boolean deleteAdmin(int id){//根据id 删除
		Connection conn=DbHelper.getConnection();
		String sql="delete from admin where id=?";
		try {
			PreparedStatement ps=conn.prepareStatement(sql);
			//通过PreparedStatement ps将表单传给servlet，封装到实体类，
			//再调用service方法里的dao方法，得到数据,但是它不用设置到数据库
			//所以少了ad.getId()
			ps.setInt(1,id);
			int count=ps.executeUpdate();
			ps.close();//已经执行完了，所以关闭。
			return count>0?true:false;

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return false;
		
	}
	
	public boolean updateAdminServlet(AdminEntity ae){
		Connection conn=DbHelper.getConnection();
		String sql="update admin set username=?,userpwd=?,sale=? where id=?";
		try {
			PreparedStatement ps=conn.prepareStatement(sql);
			//参数顺序和sql语句中一样。
			ps.setString(1,ae.getUsername());
			ps.setString(2, ae.getUserpwd());
			ps.setInt(3,ae.getSale());
			ps.setInt(4,ae.getId());
			int count=ps.executeUpdate();
			ps.close();
			return count>0?true:false;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return false;
		
	}
	public List<AdminEntity> readAdminEntity(){
		List<AdminEntity> lstae=new ArrayList<AdminEntity>();
		Connection conn=DbHelper.getConnection();
		String sql="select * from admin";
		
		try {
			PreparedStatement ps=conn.prepareStatement(sql);
			ResultSet rs=ps.executeQuery();
			//查询需要Result rs接受结果，存在实体类中
			while(rs.next()){
				AdminEntity ad=new AdminEntity();
				ad.setId(rs.getInt("id"));
				ad.setUsername(rs.getString("username"));
				ad.setUserpwd(rs.getString("userpwd"));
				ad.setSale(rs.getInt("sale"));
				lstae.add(ad);
				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}	
		return lstae;	
	}
	/**
	 * 查询单个就不用List集合了，返回ae就行了，但的设置ps.setInt(1,id),
	 * 因为它按照int id 也就是id查找
	 * @param id
	 * @return
	 */
	public AdminEntity searchOneAdminEntity(int id){
		AdminEntity ae=null;
		Connection conn=DbHelper.getConnection();
		String sql="select * from admin where id=?";
		
		try {
			PreparedStatement ps=conn.prepareStatement(sql);
			ps.setInt(1, id);
			ResultSet rs=ps.executeQuery();
			//查询需要Result rs接受结果，存在实体类中
			while(rs.next()){
				AdminEntity ad=new AdminEntity();
				ad.setId(rs.getInt("id"));
				ad.setUsername(rs.getString("username"));
				ad.setUserpwd(rs.getString("userpwd"));
				ad.setSale(rs.getInt("sale"));
			
				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}	
		return ae;	
	}
}

service类:
import java.util.List;

import com.dao.AdminDao;
import com.entity.AdminEntity;

public class AdminService {
     AdminDao AD=new AdminDao();
     
     //方法声明和AdminDao一样，不过方法体是返回值。return AD.对应的方法。
    public boolean createAdmin(AdminEntity ae){
		return AD.createAdmin(ae);    	
    }
    
    public boolean deleteAdmin(int id){
    	return AD.deleteAdmin(id);
    }
    
    public boolean updateAdmin(AdminEntity ae){
    	return AD.updateAdminServlet(ae);
    }
    public List<AdminEntity> readAdminEntity(){
		return AD.readAdminEntity();  	
    }
    public AdminEntity searchOneAdminEntity(int id){
    	return AD.searchOneAdminEntity(id);
    }
    
}

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 com.entity.AdminEntity;
import com.service.AdminService;


public class AdminServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;
       

    public AdminServlet() {
        super();
       
    }


	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		doPost(request,response);
	}


	protected 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 pw=response.getWriter();
		AdminService aservice=new AdminService();
		String y=request.getParameter("y");//得到表单提交的数据或get方式的数据
		
		if("create".equals(y)){
			//从表单得到数据
			int id=Integer.parseInt(request.getParameter("id"));
			//request.getParameter()方法得到只是字符串，所以得转换
			String username=request.getParameter("username");
			String userpwd=request.getParameter("userpwd");
			int sale=Integer.parseInt(request.getParameter("sale"));
			//将数据封装到实体类里
			AdminEntity ae=new AdminEntity();
			ae.setId(id);
			ae.setUsername(username);
			ae.setUserpwd(userpwd);
			ae.setSale(sale);
			
			//调用service层的添加方法。
			//实体类的数据给添加方法
			boolean flag=aservice.createAdmin(ae);
			if(flag){//添加成功就查询最新数据，放在作用域里，发到前台
				      List<AdminEntity>lstae=aservice.readAdminEntity();
				      request.setAttribute("lstae", lstae);
				      request.getRequestDispatcher("index.jsp").forward(request, response);	
			}
			 else{
				   pw.print("数据添加错误!!<a href='create.jsp'>点我返回</a>");
				   //System.out.print()在Servlet中无效
			        }
			}else if("delete".equals(y)){
				int id=Integer.parseInt(request.getParameter("id"));
				//不用将数据封装到实体类，因为它不需要存在数据库中
				aservice.deleteAdmin(id);
				List<AdminEntity> lstae=aservice.readAdminEntity();
				request.setAttribute("lstae",lstae);				
				//转发到index.jsp因为在indes.jsp中展示效果
				//forward();可以取上个页面的内容，而sendirect不行。
				request.getRequestDispatcher("index.jsp").forward(request, response);				
			}else if("searchOne".equals(y)){
				int id=Integer.parseInt(request.getParameter("id"));				
				AdminEntity ae=aservice.searchOneAdminEntity(id);
				request.setAttribute("ae", ae);
				request.getRequestDispatcher("update.jsp").forward(request, response);
			}
			else if("update".equals(y)){
				//update需要一个查询单独的数据给它。用searchOne
				int id=Integer.parseInt(request.getParameter("id"));
				String username=request.getParameter("username");
				String userpwd=request.getParameter("userpwd");
				int sale=Integer.parseInt(request.getParameter("sale"));
				
				AdminEntity ae=new AdminEntity();
				ae.setId(id);
				ae.setUsername(username);
				ae.setUserpwd(userpwd);
				ae.setSale(sale);
				
				boolean flag=aservice.updateAdmin(ae);
				if(flag){
					List<AdminEntity>lstae=aservice.readAdminEntity();
					request.setAttribute("lstae", lstae);
					request.getRequestDispatcher("index.jsp").forward(request, response);
					pw.print("数据添加成功!!<a href='index.jsp'>点我返回</a>");
				}
			}
		     else if("read".equals(y)){
				 List<AdminEntity>lstae=aservice.readAdminEntity();
			      request.setAttribute("lstae", lstae);
			      request.getRequestDispatcher("index.jsp").forward(request, response);
		
		}
	
	}

}

DbHelper类:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DbHelper {
    
	
	public static Connection getConnection(){
		Connection conn=null;
		
		try {
			Class.forName("com.mysql.jdbc.Driver");//因为是类所以有static 才行，Class
			conn=DriverManager.getConnection("jdbc:mysql://localhost/jdbcmysql?useUnicode=true&characterEncoding=utf-8", "root","root");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return conn;
	}
	
	
	
	public static void main(String[] args) {
	  System.out.print(getConnection());

	}

}

create.jsp:

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core"  prefix="c"%>    
<!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>
           <%--除了index.jsp一开始自动配置了，其它jsp想要访问servlet
           需要在web.xml中配置servlet --%>
           <form action="AdminServelt?y=create"  method="post">
                    id:<input type="text"  name="id">
                    username:<input type="text" name="username">
                    userpwd:<input type="text"  name="userpwd">
                   sale:<input type="text" name="sale">
                       <input type="submit"  value="添加"  >                
           </form>

</body>
</html>



index.jsp:
<%@ page language="java" contentType="text/html; charset=UTF-8"
	pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<!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>
	<%--form标签不能在table里面，而且不能嵌套form --%>
	<form action="AdminServlet?y=read"   method="post">
	<table>
		<tr>
			<th>id</th>
			<th>名字</th>
			<th>密码</th>
			<th>出售</th>
		</tr>
		<c:forEach items="${lstae}"  var="ae">
			<tr>
				<td>${ae.id}</td>
				<td>${ae.username }</td>
				<td>${ae.userpwd }</td>
				<%--eclipse代码对齐:1.ctrl+shift+f 2.sourse到format--%>
				<td><c:if test="${ae.sale==1}">
                         已售出                 
                     </c:if>
					<%--通过在数据库里设int型，后在jsp设c:if 判断是否为1或零决定是否售出 --%>
					   <c:if test="${ae.sale==0}">
					       未售出 
					   </c:if>	
					  <%--servlet需要concat类似服务器才能运行，而jsp本质是serlvet --%> 
	           </td>
	           <%--删除不需要单独的jsp，因为它只要 通过${ae.id}得到id，根据id删除--%>
	           <%--修改需要,单独的jsp，因为它要将修改的数据，存储在数据库。通过${ae.id}得到id，根据id修改 --%>
	          <%-- 添加需要单独的jsp，因为它要将增加的数据，存储在数据库。不需要通过id添加，也不用AdminServlet?y=create这样。
	          直接通过 create.jsp添加，因为它不是按照id号添加--%>
	           <td><a href="AdminServlet?y=delete&id=${ae.id}">删除</a>
	                  <a href="AdminServlet?y=searchOne&id=${ae.id}">修改</a>
	                 <a href="create.jsp">添加</a>
	           </td>              
			</tr>
		</c:forEach>		
			</table>
			<input type="submit" value="查询所有" >
			<%--form表单元素不能在table里 --%>
		</form>
</body>
</html>


update.jsp:
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core"  prefix="c"%>    
<!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>
         <%--记得update.jsp里是需要value="${}值的，不然会报空指针异常" 
         它只要ae就好了，不用items--%>
         <form action="AdminServlet?y=update"  method="post">
         
                    id:<input type="text"  name="id" value="${ae.id}">
                    username:<input type="text" name="username" value="${ae.username }">
                    userpwd:<input type="text"  name="userpwd" value="${ae.userpwd }">
                    sale:<input type="text" name="sale" value="${ae.sale}">
                       <input type="submit"  value="修改"  >                
           </form>

</body>
</html>

web.xml:
<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://java.sun.com/xml/ns/javaee" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd" id="WebApp_ID" version="3.0">
  <display-name>jdbcmysqltest</display-name>
  <welcome-file-list>
    <welcome-file>index.html</welcome-file>
    <welcome-file>index.htm</welcome-file>
    <welcome-file>index.jsp</welcome-file>
    <welcome-file>default.html</welcome-file>
    <welcome-file>default.htm</welcome-file>
    <welcome-file>default.jsp</welcome-file>
  </welcome-file-list>
  <servlet>
         <servlet-name>AdminServlet</servlet-name>
         <servlet-class>com.servlet.AdminServlet</servlet-class>  
 </servlet>
 <servlet-mapping>
           <servlet-name>AdminServlet</servlet-name>
           <url-pattern>/</url-pattern>
 </servlet-mapping>
</web-app>
互联网老欣
关注
2
点赞
踩
15

收藏

觉得还不错? 一键收藏
打赏
1
评论
jdbc+mysql+eclipse详细增删改查

框架:框架2:mysql:注意:主键的自动递增效果:增加:得有jstl的jar包和mysql-connector-java-5.1.7-bin.jar一开始mysql-connector-java-5.1.7-bin.jar,右键单击,点出Build Path，然后Add to Build Path...
复制链接

扫一扫