使用JDBC连接数据库（MySQL）的源代码

JDBC 访问数据库的步骤

1. 将 jdbc 驱劢程序相关的 jar 包 copy 到 WEB-INF/lib 下
2. 在 servlet 代码当中，使用 jdbc 访问数据库，要注意如何处理异常
3. 配置错误处理页面

使用 JDBC 访问数据库的演示代码 使用 PreparedStatement 对象 查询

public class LoadEmpServlet extends HttpServlet { 
	public void service(HttpServletRequest request,HttpServletResponse response) throws ServletException,IOException {
		long id = Long.parseLong(request.getParameter("id"));
		// 访问数据库 
		Connection conn = null; 
		try {
        	Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jd1109db2", "root","root");
			PreparedStatement prep = conn.prepareStatement("select * from t_emp where id = ?");
			prep.setLong(1, id);
			ResultSet rst = prep.executeQuery(); 
			response.setContentType("text/ htm l;charset=utf-8"); 
			PrintWriter out = response.getWriter();
			if (rst.next()) {
				String name = rst.getString("name");
				double salary = rst.getDouble("salary");
				int age = rst.getInt("age");
				out.println("");
				out.println("id:" + id + "");
				out.println("姓名:"); 
 				out.println("薪水:"); 
				out.println("年龄:");
				out.println("");
 
				out.println( "");
				out.close();
			} 
		} catch (Exception e) {
			e.printStackTrace();
			throw new ServletException(e); 
		} finally {
			if(conn!=null) { 
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace(); 
				}
			} 
		}
	}
}

插入

public class AddEmpServlet extends HttpServlet {
	public void service(HttpServletRequest request, HttpServletResponse response) throws ServletException,IOException { 
		//这行代码要放在 getParameter()执行之前
		request.setCharacterEncoding("utf-8");
		String name = request.getParameter("name");
		double salary = Double.parseDouble(request.getParameter("salary"));
		int age = Integer.parseInt(request.getParameter("age")); 
		System.out.println("name:" + name);
		System.out.println("salary:" + salary); 
		System.out.println("age:" + age);
	
		//访问数据库 
		Connection conn = null; 
		try {
			Class.forName("com.mysql.jdbc.Driver"); 
			conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jd1109db2", "root","root");
			PreparedStatement prep = conn.prepareStatement("insert into t_emp(name,salary,age) values(?,?,?)");
			prep.setString(1, name);
			prep.setDouble(2, salary); 
			prep.setInt(3, age); 
			prep.executeUpdate();
 			response.setContentType("text/html;charset=utf-8");
			PrintWriter out = response.getWriter(); 
			out.println( "添加雇员成功");
			out.close();
		} catch (Exception e) {
			e.printStackTrace();
			throw new ServletException(e); 
		} finally {
			if(conn!=null) { 
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				} 
			}
		}
 	}
}

更新

public class ModifyEmpServlet extends HttpServlet { 
	public void service(HttpServletRequest request,HttpServletResponse response) throws ServletException,IOException { 		request.setCharacterEncoding("utf-8"); 
		long id = Long.parseLong(request.getParameter("id")); 
		String name = request.getParameter("name"); 
		double salary = Double.parseDouble(request.getParameter("salary"));
		int age = Integer.parseInt(request.getParameter("age"));
		// 访问数据库
		Connection conn = null; 
		try {
			Class.forName("com.mysql.jdbc.Driver"); 
			conn = DriverManager.getConnection( "jdbc:mysql://localhost:3306/jd1109db2","root","root");
			PreparedStatement prep = conn.prepareStatement("update t_emp " +
														   "set name=?,salary=?,age=? " + 
														   "where id=?");
			prep.setString(1, name);
 			prep.setDouble(2, salary); 
 			prep.setLong(3, age); 
 			prep.setLong(4, id); 
 			prep.executeUpdate();
			response.sendRedirect("list"); 
		} catch (Exception e) {
			e.printStackTrace();
			throw new ServletException(e); 
		} finally {
 			if(conn!=nul l) { 
 				try {
					conn.close();
				} catch (SQLException e) { 
					e.printStackTrace();
				}
			} 
		}
	}
 }

删除

public class DelEmpServlet extends HttpServlet {
	public void service(HttpServletRequest request,HttpServletResponse response) throws ServletException,IOException {
		long id = Long.parseLong(request.getParameter("id"));
		Connection conn = null;
		// 访问数据库
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager .getConnection("jdbc:mysql://localhost:3306/jd1109db2","root","root");
			PreparedStatement prep = conn.prepareStatement("delete from t_emp where id = ?"); 
			prep.setLong(1, id);
			prep.executeUpdate();
 			//重定向 
 			response.sendRedirect("list");
		} catch (Exception e) { 
			//step1 先记录日志
			e.printStackTrace(); //step2 抛出
			throw new ServletException(e); 
		} finally {
			if(conn!=nul l) { 
				try {
					conn.close();
				} catch (SQLException e) { 
					e.printStackTrace();
				}
			} 
		}
	}
 }
		

使用 Statement 对象 查询

利用 JDBC 访问数据库 , 显示所有雇员信息

public class ListEmpServlet extends HttpServlet{
	public void service(HttpServletRequest request,HttpServletResponse response) throws ServletException,IOException {
		//访问数据库 
		Connection conn = null; 
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jd1109db2","root","root"); 
			Statement stat = conn.createStatement();
			ResultSet rst = stat.executeQuery("select * from t_emp");
			//使用查询得到的结果，生成一个表格 
			response.setContentType("text/html;charset=utf-8"); 
			PrintWriter out = response.getWriter(); 
			out.println("");
			out.println("" +
						"id" + 
						"姓名" +
						"薪水" + 
						"年龄");
			while (rst.next()) {
				long id = rst.getLong("id");
				String name = rst.getString("name");
				double salary = rst.getDouble("salary");
 				int age = rst.getInt("age");
				out.println("" + 
							id + 
							" " + 
							name + 
							"" + 
							salary + 
							" " + 
							age + 
							"");
			}
			out.println( "");
			out.close();
 
		} catch (Exception e) { 
			e.printStackTrace();
			throw new ServletException(e);
		} finally { 
			if(conn!=nul l) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace(); 
				}
			} 
		}
	}
 }
			

删除

待续