jdbc及el表达式

1 jdbc操作数据库步骤：

1.关联驱动包
2.注册驱动
3.获取数据库连接对象
4.定义sql语句，预编译sql
5.设置参数.执行sql
6.处理结果集
7.关闭连接

2 不封装jdbc操作，control层的代码

<%@page import="java.sql.*,cn.yunhe.beans.User,java.util.*"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%
	//设置字符编码
	request.setCharacterEncoding("utf-8");
	//获取当前的请求标识
	String option = request.getParameter("option");
	//操作数据库
	//1、关联驱动包2、注册驱动
	Class.forName("com.mysql.jdbc.Driver");
	//3、获取数据库连接对象
	Connection conn = DriverManager.getConnection("jdbc:mysql:///shop", "root", "77777777");
	PreparedStatement ps = null;
	ResultSet rs = null;
	//判断当前的操作类型
	if("query".equals(option)){
		//4、定义sql，预编译sql
		String sql = "select * from t_user";
		ps = conn.prepareStatement(sql);
		//5、设置参数，执行sql
		rs = ps.executeQuery();
		//定义集合，用于存储user对象
		List<User> userList = new ArrayList<User>();
		//6、处理结果集
		while(rs.next()){
			//将每条数据都封装到一个对象中
			User user = new User();
			user.setUid(rs.getInt(1));
			user.setUname(rs.getString(2));
			user.setUpwd(rs.getString(3));
			user.setAddress(rs.getString(4));
			user.setPhone(rs.getString(5));
			user.setHireDate(rs.getDate(6));
			//将每个对象都添加到集合中进行存储
			userList.add(user);
		}
		//将数据存储到域中，在其他页面可以使用---session
		session.setAttribute("userList", userList);
		//回到数据展示页
		response.sendRedirect("member-list.jsp");
	}else if("add".equals(option)){
		//从表单中获取用户输入的值
		String uname = request.getParameter("uname");
		String upwd = request.getParameter("upwd");
		String address = request.getParameter("address");
		String phone = request.getParameter("phone");
		String hireDate = request.getParameter("hireDate");
		//定义sql语句
		String sql = "insert into t_user(uname,upwd,address,phone,hireDate) values(?,?,?,?,?)";
		ps = conn.prepareStatement(sql);
		//设置值，执行sql
		ps.setString(1, uname);
		ps.setString(2, upwd);
		ps.setString(3, address);
		ps.setString(4, phone);
		ps.setString(5, hireDate);
		int result = ps.executeUpdate();
		response.sendRedirect("member-list.jsp");
	}else if("del".equals(option)){
		//获取要删除的用户ID
		String uid = request.getParameter("uid");
		//定义sql
		String sql = "delete from t_user where uid=?";
		ps = conn.prepareStatement(sql);
		ps.setInt(1, Integer.valueOf(uid));
		ps.executeUpdate();
		response.sendRedirect("member-list.jsp");
	}
	
	//7、关闭连接
	if(null != rs){
		rs.close();
	}
	if(null != ps){
		ps.close();
	}
	if(null != conn){
		conn.close();
	}
	
%>

3 封装jdbc操作到DBUtil.java中

package cn.yunhe.utils;

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

/**
 * 	数据库连接工具类
 */
public class DBUtil {
	
	private static final String DRIVER = "com.mysql.jdbc.Driver";
	private static final String URL = "jdbc:mysql:///shop";
	private static final String USERNAME = "root";
	private static final String PASSWORD = "77777777";
	
	/**
	 * 注册驱动
	 */
	static {
		try {
			Class.forName(DRIVER);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * 获取连接对象
	 * @return
	 * @throws SQLException 
	 */
	public static Connection getConn() throws SQLException {
		return DriverManager.getConnection(URL, USERNAME, PASSWORD);
	}
	
	/**
	 * 增删改通用方法
	 * @param sql
	 * 		sql语句
	 * @param objects
	 * 		需要传递的参数
	 * @return
	 * 		0代表失败
	 * 		1代表成功
	 */
	public static int curd(String sql,Object...objects) {
		//获取连接对象
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = getConn();
			//预编译对象
			ps = conn.prepareStatement(sql);
			if(null != objects) {
				for(int i=0;i<objects.length;i++) {
					ps.setObject(i+1, objects[i]);
				}
			}
			//执行sql
			return ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			close(conn,ps,null);
		}
		return 0;
	}
	
	/**
	 * 关闭数据库连接对象
	 * @param conn
	 * @param st
	 * @param rs
	 * 	如果非查询操作，该参数传递null即可
	 */
	public static void close(Connection conn,Statement st,ResultSet rs) {
		try {
			if(null != rs) {
				rs.close();
			}
			if(null != st) {
				st.close();
			}
			if(null != conn) {
				conn.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}

4 使用DBUtil.java的control层代码

<%@page import="java.sql.*,cn.yunhe.beans.User,java.util.*,cn.yunhe.utils.*"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%
	//设置字符编码
	request.setCharacterEncoding("utf-8");
	//获取当前的请求标识
	String option = request.getParameter("option");
	//操作数据库
	Connection conn = null;
	PreparedStatement ps = null;
	ResultSet rs = null;
	//判断当前的操作类型
	if("query".equals(option)){
		conn = DBUtil.getConn();
		//4、定义sql，预编译sql
		String sql = "select * from t_user";
		ps = conn.prepareStatement(sql);
		//5、设置参数，执行sql
		rs = ps.executeQuery();
		//定义集合，用于存储user对象
		List<User> userList = new ArrayList<User>();
		//6、处理结果集
		while(rs.next()){
			//将每条数据都封装到一个对象中
			User user = new User();
			user.setUid(rs.getInt(1));
			user.setUname(rs.getString(2));
			user.setUpwd(rs.getString(3));
			user.setAddress(rs.getString(4));
			user.setPhone(rs.getString(5));
			user.setHireDate(rs.getDate(6));
			//将每个对象都添加到集合中进行存储
			userList.add(user);
		}
		//将数据存储到域中，在其他页面可以使用---session
		session.setAttribute("userList", userList);
		DBUtil.close(conn,ps,rs);
		//回到数据展示页
		response.sendRedirect("member-list.jsp");
	}else if("add".equals(option)){
		//从表单中获取用户输入的值
		String uname = request.getParameter("uname");
		String upwd = request.getParameter("upwd");
		String address = request.getParameter("address");
		String phone = request.getParameter("phone");
		String hireDate = request.getParameter("hireDate");
		//定义sql语句
		String sql = "insert into t_user(uname,upwd,address,phone,hireDate) values(?,?,?,?,?)";
		Object[] objects = {uname,upwd,address,phone,hireDate};
		DBUtil.curd(sql,objects);
		response.sendRedirect("member-list.jsp");
	}else if("del".equals(option)){
		//获取要删除的用户ID
		String uid = request.getParameter("uid");
		//定义sql
		String sql = "delete from t_user where uid=?";
		DBUtil.curd(sql, uid);
		response.sendRedirect("member-list.jsp");
	}
	
%>