jsp+servlet的增删改查

最近写了很多jsp+servlet的增删改查，都是一些小的作业，不是写的很规范，但是总结下，都是一些常用的。

数据库：

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

public class JDBCUtils {
	
	private static final String  connectionURL="jdbc:mysql://localhost:3306/order?serverTimezone=UTC&useUnicode=true&characterEncoding=UTF8";
	private static final String username="root";
	private static final String password="123456";
	
	//建立连接的工具类
	public static Connection getConnection()
	{
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");//注册驱动,使用什么驱动连接数据库
			return DriverManager.getConnection(connectionURL,username,password);//建立连接
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return null;
	}
	
	
	//关闭资源
	public static void close(ResultSet rs,Statement stmt, Connection con)
	{
		try {
			if(rs!=null)
			rs.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		try {
			if(stmt!=null)
			stmt.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	
		try {
			if(con!=null)
			con.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}
	
	
}

将数据库写入工具类。

登陆：

登陆首先要获取前台传入username和password，通过request.getParameter(“username”);来获取。

		boolean isexist=false;
		
		 isexist = JDBCFDemo.selectUser(username, password);
		
		
		if(isexist)
		{
			 request.getRequestDispatcher("/success.jsp").forward(request, response);
			 return;
		}
		else
		{
			 request.setAttribute("message", "<font color='red'>登录失败,用户名或密码错误！</font>");
			 request.getRequestDispatcher("/fail.jsp").forward(request, response);
			 return;
		}

servlet里的处理，查询是否存在这个用户，存在，到登陆成功页面， 不存在，传入request登陆失败的msg,返回登陆失败页面。

	<%
	Object msg=request.getAttribute("message");
	if(msg!=null)
	out.println(msg);
	%>

页面通过这个信息来给出提示。
数据库

查询user

		public static boolean selectUser(String username,String password) {
			Connection con = null;
			PreparedStatement pstmt = null;
			ResultSet rs = null;
			boolean isexit=false;
			
			try {

				con = JDBCUtils.getConnection();

				
				String sql = "select * from user where username = ? and password = ?";
				pstmt = con.prepareStatement(sql);

				pstmt.setString(1, username);
				pstmt.setString(2, password);
				rs = pstmt.executeQuery();
				if (rs.next()) {
					return true;
				} else {
					return false;
				}
			

			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} finally {
				JDBCUtils.close(rs, pstmt, con);
			}
			return false;
		}

查询所有信息，存入列表中：

	public List<Notice> FindAllNotice() {
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		List<Notice> list = new ArrayList<Notice>();

		try {

			con = JDBCUtils.getConnection();
			stmt = con.createStatement();// 发起查询请求
			String sql = "select * from notice";
			rs = stmt.executeQuery(sql);// 查询返回结果集

			while (rs.next()) {
				Integer id;
				String time;
				String content;
				String type;
				id = rs.getInt("id");
				time = rs.getString("time");
				content = rs.getString("content");
				type = rs.getString("type");
				Notice notice = new Notice(id, time, content, type);

				list.add(notice);

			}
			return list;

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JDBCUtils.close(rs, stmt, con);
		}
		return list;
	}

		public List<Notice> FindAllNoticeLimit() {
			Connection con = null;
			Statement stmt = null;
			ResultSet rs = null;
			List<Notice> list = new ArrayList<Notice>();

			try {

				con = JDBCUtils.getConnection();
				stmt = con.createStatement();// 发起查询请求
				String sql = "select * from notice where type='学校'";
				rs = stmt.executeQuery(sql);// 查询返回结果集

				while (rs.next()) {
					Integer id;
					String time;
					String content;
					String type;
					id = rs.getInt("id");
					time = rs.getString("time");
					content = rs.getString("content");
					type = rs.getString("type");
					Notice notice = new Notice(id, time, content, type);

					list.add(notice);

				}
				return list;

			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} finally {
				JDBCUtils.close(rs, stmt, con);
			}
			return list;
		}

删除某个信息：

<a  class="btn btn-danger"  onclick="clickMe()" href="${pageContext.request.contextPath }/deleteNotice?id=${notice.id}" >删除</a> 

利用查询得到的列表，给每个商品后面的删除按钮不同的id，这样点击删除的时候，就可以判断是哪一个商品的删除了。

public  void Delete(int id) {
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		boolean isexit=false;
		
		try {

			con = JDBCUtils.getConnection();

			
			String sql = "delete from notice where id =?";
			pstmt = con.prepareStatement(sql);

			pstmt.setInt(1, id);
			pstmt.executeUpdate();

		

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JDBCUtils.close(rs, pstmt, con);
		}
	}

servlet里根据id删除

	int id=Integer.parseInt(request.getParameter("id"));
		deleteService.Delete(id);
request.getRequestDispatcher("/ShowNoticeServlet").forward(request, response);

注册添加

		public void AddContacts(int id,String name, String phone, String email, String relation) {
			Connection con = null;
			PreparedStatement pstmt = null;
			ResultSet rs = null;
			boolean isexit=false;
			
			try {

				con = JDBCUtils.getConnection();

				
				String sql = "insert into contacts(id,name,phone,email,relation) values(?,?,?,?,?)";
				pstmt = con.prepareStatement(sql);

				pstmt.setInt(1, id);
				pstmt.setString(2, name);
				pstmt.setString(3, phone);
				pstmt.setString(4, email);
				pstmt.setString(5, relation);
				pstmt.executeUpdate();

			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} finally {
				JDBCUtils.close(rs, pstmt, con);
			}
			
		}