servlet使用JDBC访问数据库

5.JavaWeb动态网页 专栏收录该内容
56 篇文章 3 订阅


效果演示

1

挺尴尬的,把电脑播放的音乐也给录进去了。

一、访问步骤

步骤一:将JDBC驱动(.jar文件)放到WEB-INF\lib
原因:ClassLoader找到字节码文件,然后加载到JVM的方法区中,变成一个对象。Tomcat都有自己的类加载器,会去WEB-INF下面lib中找字节码文件。(jar包中的都是字节码文件.)

步骤二、编写JDBC代码,需要注意异常的处理

二、项目搭建

在这里插入图片描述

(1)sql语句

-- 创建t_emp表
CREATE TABLE t_emp(
id INT(4) PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20),
salary DOUBLE(7,2),
age INT (3)
);
INSERT INTO t_emp VALUES(1,'蔡小坤',5000.0,22);
INSERT INTO t_emp VALUES(2,'乔小萝',6000.0,22);

(2)Emp实体类

package entity;
/**
 * Emp实体类
 * 
 * @author QianliangGuo
 */
public class Emp {
	private Integer id;
	private String name;
	private Double salary;
	private Integer age;
	public Emp() {
		super();
		// TODO Auto-generated constructor stub
	}
	public Emp(Integer id, String name, Double salary, Integer age) {
		super();
		this.id = id;
		this.name = name;
		this.salary = salary;
		this.age = age;
	}
	public Integer getId() {
		return id;
	}
	public void setId(Integer id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public Double getSalary() {
		return salary;
	}
	public void setSalary(Double salary) {
		this.salary = salary;
	}
	public Integer getAge() {
		return age;
	}
	public void setAge(Integer age) {
		this.age = age;
	}
	@Override
	public String toString() {
		// TODO Auto-generated method stub
		return super.toString();
	}
	
}

(3)mapper封装结果集

RowMapper接口

package mapper;

import java.sql.ResultSet;
/**
 * RowMapper接口
 * 作用: 将ResultSet结果集封装成对象
 * 
 * @author QianliangGuo
 */
public interface RowMapper<T> {
	// 将ResultSet结果集封装成对象
	public T mappreRow(ResultSet rs);
}

EmpRowMapper.java

package mapper;

import java.sql.ResultSet;
import java.sql.SQLException;

import entity.Emp;
/**
 * 封装处理结果集
 */
public class EmpRowMapper implements RowMapper<Emp> {
	@Override
	public Emp mappreRow(ResultSet rs) {
		Emp emp = new Emp();
		try {
			emp.setId(rs.getInt("id"));
			emp.setName(rs.getString("name"));
			emp.setSalary(rs.getDouble("salary"));
			emp.setAge(rs.getInt("age"));
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return emp;
	}
}

(4)util工具包

JdbcTemplate.java

package util;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import mapper.RowMapper;
/**
 * JdbucTemplate类
 * 作用:封装了增删改查操作
 * 
 * @author QianliangGuo
 */
public class JdbcTemplate<T> {
	/*
	 * 1.单条结果查询select_one
	 * 2.多条结果查询select_more
	 * 3.增删改 updata
	 *
	 */
	static Connection conn = null;
	static PreparedStatement pstm = null;
	static ResultSet rs = null;

	// 1.select_one
	public T queryForObject(String sql, RowMapper<T> rm, Object... args) {
		T t = null;
		try {
			conn = JdbcUtil3.getConnection();
			pstm = conn.prepareStatement(sql);
			if (args.length != 0) {
				for (int i = 0; i < args.length; i++) {
					pstm.setObject(i + 1, args[i]);
				}
			}
			rs = pstm.executeQuery();
			if (rs.next()) {
				t = rm.mappreRow(rs);
			}
		} catch (Exception e) {
			System.out.println("数据库连接异常");
		} finally {
			try {
				JdbcUtil3.release(rs, pstm, conn);
			} catch (Exception e) {
				System.out.println("释放资源出现问题");
			}
		}
		return t;
	}

	//2.select_more
	public List<T> queryForList(String sql,RowMapper<T> rm,Object...args){
		List<T> list = null;
		try {
			conn = JdbcUtil3.getConnection();
			pstm = conn.prepareStatement(sql);
			if (args.length != 0) {
				for (int i = 0; i < args.length; i++) {
					pstm.setObject(i + 1, args[i]);
				}
			}
			rs = pstm.executeQuery();
			list = new ArrayList();
			while(rs.next()) {
				T t = rm.mappreRow(rs);
				list.add(t);
			}
		} catch (Exception e) {
			System.out.println("数据库连接异常");
		} finally {
			try {
				JdbcUtil3.release(rs, pstm,conn);
			} catch (Exception e) {
				System.out.println("释放资源出现问题");
			}
		}
		return list;
	}
	
	//3.updata操作
	public void update(String sql, Object... args) {

		try {
			//开启一个连接
			conn = JdbcUtil3.getConnection();
			pstm = conn.prepareStatement(sql);
			// args的长度如果不是0,就说明有参数,那它就是个半成品
			if (args.length != 0) {
				for (int i = 0; i < args.length; i++) {
					pstm.setObject(i + 1, args[i]);
				}
			}
			pstm.executeUpdate();
		} catch (Exception e) {
			System.out.println("数据库异常!");
		} finally {
			try {
				JdbcUtil3.release(null, pstm, conn);
			} catch (Exception e) {
				System.out.println("关闭连接异常!");
			}
		}
	}
}

JdbcUtil3.java

package util;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;

import javax.sql.DataSource;

import org.apache.commons.dbcp.BasicDataSourceFactory;

/**
 * Jdbc工具类
 * 1:properties配置文件 封装获取连接 释放资源 提高代码复用性√
 * 2:类加载时加载驱动√
 * 3:ThreadLocal控制事务√
 * 4:连接池,提高资源利用率√
 * 5:rowmapper封装 减少代码冗余×
 * 6:template封装 减少dao层代码冗余×
 * @author 郭乾亮1998
 *
 */
public class JdbcUtil3 {
	//声明连接池
	static DataSource pool = null;
	
	//创建properties
	static Properties pro = new Properties();
	//创建ThreadLocal<Connection>,可以为同一个线程保存同一个连接,为不同线程保存不同的连接
	private static ThreadLocal<Connection> tl = new ThreadLocal<Connection>();
	
	//加载驱动
	static{
		InputStream is = null;
		try {
			is = JdbcUtil3.class.getResourceAsStream("/conf/dbcp.properties");
			//加载文件
			pro.load(is);
			//Class.forName(pro.getProperty("driverClassName"));
			//properties配置文件创建连接池
			pool = BasicDataSourceFactory.createDataSource(pro);
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			try {
				is.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}
	//获取连接
	public static Connection getConnection() throws Exception{
		Connection conn = tl.get();//获得当前线程中的连接
		if(conn == null){//如果当前线程中没有连接
			String url = pro.getProperty("url");
			String user = pro.getProperty("username");
			String password = pro.getProperty("password");
			//创建连接
			//conn = DriverManager.getConnection(url,user,password);
			//通过连接池对象获得connection
			conn = pool.getConnection();
			//将连接保存到当前线程
			tl.set(conn);
		}
		return conn;
	}
	//释放资源
	public static void release(ResultSet rs,PreparedStatement pstm,Connection conn) throws Exception{
		if(rs!=null){
			rs.close();
		}
		if(pstm!=null){
			pstm.close();
		}
		if(conn!=null){
			conn.close();
			tl.remove();//将连接从当前线程中移除
		}
	}
}

(5)conf配置

dbcp.properties

driverClassName=com.mysql.jdbc.Driver
url=jdbc\:mysql\://localhost\:3306/mybase?useUnicode\=true&characterEncoding\=utf-8
username=root
password=Hudie
initialSize=10
maxActive=50
maxIdle=20
minIdle=5
maxWait=60000
connectionProperties=useUnicode\=true;characterEncoding\=utf-8
defaultAutoCommit=true
defaultTransactionIsolation=READ_COMMITTED

三、服务器端代码编写

(1)查询QueryEmpServlet.java

package web;


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.EmpDao;
import dao.EmpDaoImpl;
import entity.Emp;
/**
 * 查询emp
 * 
 * @author QianliangGuo
 */

public class QueryEmpServlet extends HttpServlet {
	EmpDao empDao = new EmpDaoImpl();

	@Override
	protected void service(HttpServletRequest request,HttpServletResponse response) throws ServletException, IOException {
		// 查询所有员工记录
		List<Emp> list = empDao.queryAllEmp();
		//设置服务器端解码
		response.setContentType("text/html;charset=utf-8");
		
		PrintWriter out = response.getWriter();
		out.println("<table border='1' width='60%' cellspacing='0' cellpadding='0' align='center'> ");
		out.println("<tr><td>ID</td><td>姓名</td><td>薪水</td><td>年龄</td><td>操作</td></tr>");
		for (Emp emp : list) {
			out.println("<tr>");		
			out.println("<td>" + emp.getId() + "</td>");
			out.println("<td>" + emp.getName() + "</td>");
			out.println("<td>" + emp.getSalary() + "</td>");
			out.println("<td>" + emp.getAge() + "</td>");
			out.println("<td>" + "<a href ='deleteEmp?id="+emp.getId()+"' οnclick=\"return confirm('是否确认删除"+emp.getName()+"')\">删除</a>");
			out.println("&nbsp;&nbsp;");
			out.println("<a href ='loadEmp?id="+emp.getId()+"'>修改</a>"+"</td>");					
			out.println("</tr>");
		}
		out.println("</table>");
		out.println("<br/><br/>");
		out.println("<div style='text-align:center'><a href='addEmp.html' target='_blank'>添加员工</a></div>");
	}
}

(2)添加AddEmpServlet.java

package web;

import java.io.IOException;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import dao.EmpDao;
import dao.EmpDaoImpl;
import entity.Emp;
/**
 * 添加emp
 * 
 * @author QianliangGuo
 */
public class AddEmpServlet extends HttpServlet {
	EmpDao empDao = new EmpDaoImpl();
	@Override
	protected void service(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		request.setCharacterEncoding("utf-8");
		String name = request.getParameter("name");
		String salary = request.getParameter("salary");
		String age = request.getParameter("age");
		Emp emp = new Emp(null,name,Double.parseDouble(salary),Integer.parseInt(age));
		empDao.addEmp(emp);
		
		//添加成功,重定向到queryEmp中
		response.sendRedirect("queryEmp");
	}
}

(3)加载LoadEmpServlet.java

package web;

/**
 * 加载
 */
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;

import dao.EmpDao;
import dao.EmpDaoImpl;
import entity.Emp;

public class LoadEmpServlet extends HttpServlet {
	EmpDao empDao = new EmpDaoImpl();
	@Override
	protected void service(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		//依据想要修改的id,查询这个员工原有信息
		String id = request.getParameter("id");
		Emp emp = empDao.selectEmpById(Integer.parseInt(id));
		//将员工信息回显到修改表单的页面
		response.setContentType("text/html;charset=utf-8");
		PrintWriter out = response.getWriter();
		out.println("<form method='post' action='updateEmp'>");
		out.println("<input type='hidden' name='id' value='"+emp.getId()+"'/><br/>");
		out.println("姓名:<input type='text' name='name' value='"+emp.getName()+"'/><br/>");
		out.println("薪水:<input type='text' name='salary' value='"+emp.getSalary()+"'/><br/>");
		out.println("年龄:<input type='text' name='age' value='"+emp.getAge()+"'/><br/>");
		out.println("<input type='submit' value='提交'/>");	
		out.println("</form>");
	}
}

(4)删除DeleteEmpServlet.java

package web;

/**
 * 加载
 */
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;

import dao.EmpDao;
import dao.EmpDaoImpl;
import entity.Emp;

public class LoadEmpServlet extends HttpServlet {
	EmpDao empDao = new EmpDaoImpl();
	@Override
	protected void service(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		//依据想要修改的id,查询这个员工原有信息
		String id = request.getParameter("id");
		Emp emp = empDao.selectEmpById(Integer.parseInt(id));
		//将员工信息回显到修改表单的页面
		response.setContentType("text/html;charset=utf-8");
		PrintWriter out = response.getWriter();
		out.println("<form method='post' action='updateEmp'>");
		out.println("<input type='hidden' name='id' value='"+emp.getId()+"'/><br/>");
		out.println("姓名:<input type='text' name='name' value='"+emp.getName()+"'/><br/>");
		out.println("薪水:<input type='text' name='salary' value='"+emp.getSalary()+"'/><br/>");
		out.println("年龄:<input type='text' name='age' value='"+emp.getAge()+"'/><br/>");
		out.println("<input type='submit' value='提交'/>");	
		out.println("</form>");
	}
}

(5)查询QueryEmpServlet.java

package web;


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.EmpDao;
import dao.EmpDaoImpl;
import entity.Emp;
/**
 * 查询emp
 * 
 * @author QianliangGuo
 */

public class QueryEmpServlet extends HttpServlet {
	EmpDao empDao = new EmpDaoImpl();

	@Override
	protected void service(HttpServletRequest request,HttpServletResponse response) throws ServletException, IOException {
		// 查询所有员工记录
		List<Emp> list = empDao.queryAllEmp();
		//设置服务器端解码
		response.setContentType("text/html;charset=utf-8");
		
		PrintWriter out = response.getWriter();
		out.println("<table border='1' width='60%' cellspacing='0' cellpadding='0' align='center'> ");
		out.println("<tr><td>ID</td><td>姓名</td><td>薪水</td><td>年龄</td><td>操作</td></tr>");
		for (Emp emp : list) {
			out.println("<tr>");		
			out.println("<td>" + emp.getId() + "</td>");
			out.println("<td>" + emp.getName() + "</td>");
			out.println("<td>" + emp.getSalary() + "</td>");
			out.println("<td>" + emp.getAge() + "</td>");
			out.println("<td>" + "<a href ='deleteEmp?id="+emp.getId()+"' οnclick=\"return confirm('是否确认删除"+emp.getName()+"')\">删除</a>");
			out.println("&nbsp;&nbsp;");
			out.println("<a href ='loadEmp?id="+emp.getId()+"'>修改</a>"+"</td>");					
			out.println("</tr>");
		}
		out.println("</table>");
		out.println("<br/><br/>");
		out.println("<div style='text-align:center'><a href='addEmp.html' target='_blank'>添加员工</a></div>");
	}
}

(6)修改UpdateEmpServlet.java

package web;

/**
 * 修改
 */
import java.io.IOException;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import dao.EmpDao;
import dao.EmpDaoImpl;
import entity.Emp;

public class UpdateEmpServlet extends HttpServlet {
	EmpDao empDao = new EmpDaoImpl();
	@Override
	protected void service(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		//设置服务端解码
		request.setCharacterEncoding("utf-8");
		//获得修改之后的Emp信息
		String id = request.getParameter("id");
		String name = request.getParameter("name");
		String salary = request.getParameter("salary");
		String age = request.getParameter("age");
		Emp emp = new Emp(Integer.parseInt(id),name,Double.parseDouble(salary),Integer.getInteger(age));
		//修改用户信息
		empDao.updateEmp(emp);
		//修改成功后重定向到员工列表queryEmp
		response.sendRedirect("queryEmp");
	}
}

四、优化servlet(合并servlet)

(1)优化servlet的方法

通过request.getRequestURI()方法获取请求资源路径

可以将所有操作请求处理合并到一个Servlet中,实现请求的分发。

String action = url.substring(url.lastIndexOf("/")+1,url.lastIndexOf("."));

修改web.xml

<servlet-mapping>
		<servlet-name>actionServlet</servlet-name>
		<url-pattern>*.do</url-pattern>
</servlet-mapping>

(2)ActionServlet.java

package web;


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.EmpDao;
import dao.EmpDaoImpl;

import entity.Emp;

public class ActionServlet extends HttpServlet {
	//创建dao层实例
	EmpDao empDao = new EmpDaoImpl();
	@Override
	protected void service(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		//设置服务器端对客户端解码
		request.setCharacterEncoding("utf-8");
		//设置客户端编码
		response.setContentType("text/html;charset=utf-8");
		//创建输出流
		PrintWriter out = response.getWriter();
		//获取请求资源路径/ems/.do
		String url = request.getRequestURI();
		//切分url地址,保留addEmp
		String uri = url.substring(url.lastIndexOf("/")+1,url.lastIndexOf("."));
		
		
		if(uri.equals("queryEmp")){
			/**
			 * 查询
			 */
			List<Emp> list = empDao.queryAllEmp();
			out.println("<table border='1' width='60%' cellspacing='0' cellpadding='0' align='center'> ");
			out.println("<tr><td>ID</td><td>姓名</td><td>薪水</td><td>年龄</td><td>操作</td></tr>");
			for (Emp emp : list) {
				out.println("<tr>");		
				out.println("<td>" + emp.getId() + "</td>");
				out.println("<td>" + emp.getName() + "</td>");
				out.println("<td>" + emp.getSalary() + "</td>");
				out.println("<td>" + emp.getAge() + "</td>");
				out.println("<td>" + "<a href ='deleteEmp.do?id="+emp.getId()+"' οnclick=\"return confirm('是否确认删除"+emp.getName()+"')\">删除</a>");
				out.println("&nbsp;&nbsp;");
				out.println("<a href ='loadEmp.do?id="+emp.getId()+"'>修改</a>"+"</td>");					
				out.println("</tr>");
			}
			out.println("</table>");
			out.println("<br/><br/>");
			out.println("<div style='text-align:center'><a href='addEmp.html' target='_blank'>添加员工</a></div>");
		}else if(uri.equals("loadEmp")){
			/**
			 *	加载
			 */
			//根据想要修改的id,查询这个员工原有信息
			String id = request.getParameter("id");
			Emp emp = empDao.selectEmpById(Integer.parseInt(id));
			out.println("<form method='post' action='updateEmp'>");
			out.println("<input type='hidden' name='id' value='"+emp.getId()+"'/><br/>");
			out.println("姓名:<input type='text' name='name' value='"+emp.getName()+"'/><br/>");
			out.println("薪水:<input type='text' name='salary' value='"+emp.getSalary()+"'/><br/>");
			out.println("年龄:<input type='text' name='age' value='"+emp.getAge()+"'/><br/>");
			out.println("<input type='submit' value='提交'/>");	
			out.println("</form>");
		}else if(uri.equals("updateEmp")){
			/**
			 * 修改
			 */
			//获得修改之后的Emp信息
			String id = request.getParameter("id");
			String name = request.getParameter("name");
			String salary = request.getParameter("salary");
			String age = request.getParameter("age");
			Emp emp = new Emp(Integer.parseInt(id),name,Double.parseDouble(salary),Integer.getInteger(age));
			//修改用户信息
			empDao.updateEmp(emp);
			//修改成功后重定向到员工列表queryEmp
			response.sendRedirect("queryEmp.do");
		}else if(uri.equals("addEmp")){
			/**
			 * 添加
			 */
			String name = request.getParameter("name");
			String salary = request.getParameter("salary");
			String age = request.getParameter("age");
			Emp emp = new Emp(null,name,Double.parseDouble(salary),Integer.parseInt(age));
			empDao.addEmp(emp);
			//添加成功,重定向到queryEmp中
			response.sendRedirect("queryEmp.do");
		}else if(uri.equals("deleteEmp")){
			/**
			 * 删除
			 */
			//获得需要删除的id
			String id = request.getParameter("id");
			empDao.deleteEmp(Integer.parseInt(id));
			//删除成功后重定向到员工列表
			response.sendRedirect("queryEmp.do");
		}
	}
	
}

  • 1
    点赞
  • 0
    评论
  • 6
    收藏
  • 打赏
    打赏
  • 扫一扫,分享海报

©️2022 CSDN 皮肤主题:撸撸猫 设计师:马嘣嘣 返回首页

打赏作者

Hudie.

不要打赏!不要打赏!不要打赏!

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值