2024年最新后端开发基础-SpringMVC框架学习-011——Springjdbc，实战分析

最后

如果觉得本文对你有帮助的话，不妨给我点个赞，关注一下吧！

本文已被CODING开源项目：【一线大厂Java面试题解析+核心总结学习笔记+最新讲解视频+实战项目源码】收录

需要这份系统化的资料的朋友，可以点击这里获取

index.jsp

default.html

default.htm

default.jsp

encodingFilter

org.springframework.web.filter.CharacterEncodingFilter

encoding

UTF-8

encodingFilter

/*

springmvc

org.springframework.web.servlet.DispatcherServlet

contextConfigLocation

classpath:app.xml

1

springmvc

*.do

app.xml

<?xml version="1.0" encoding="UTF-8"?>

<beans xmlns=“http://www.springframework.org/schema/beans”

xmlns:xsi=“http://www.w3.org/2001/XMLSchema-instance”

xmlns:context=“http://www.springframework.org/schema/context”

xmlns:jdbc=“http://www.springframework.org/schema/jdbc”

xmlns:jee=“http://www.springframework.org/schema/jee”

xmlns:tx=“http://www.springframework.org/schema/tx”

xmlns:aop=“http://www.springframework.org/schema/aop”

xmlns:mvc=“http://www.springframework.org/schema/mvc”

xmlns:util=“http://www.springframework.org/schema/util”

xmlns:jpa=“http://www.springframework.org/schema/data/jpa”

xsi:schemaLocation="

http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.2.xsd

http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.2.xsd

http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-3.2.xsd

http://www.springframework.org/schema/jee http://www.springframework.org/schema/jee/spring-jee-3.2.xsd

http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.2.xsd

http://www.springframework.org/schema/data/jpa http://www.springframework.org/schema/data/jpa/spring-jpa-1.3.xsd

http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-3.2.xsd

http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc-3.2.xsd

http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-3.2.xsd">

<context:component-scan base-package=“com.dk.oss”/>

mvc:annotation-driven/

<util:properties id=“jdbc” location=“classpath:db.properties”/>

<bean id=“ds” class=“org.apache.commons.dbcp.BasicDataSource”

destroy-method=“close”>

mvc:interceptors

mvc:interceptor

<mvc:mapping path=“/**”/>

<mvc:exclude-mapping path=“/toLogin.do”/>

<mvc:exclude-mapping path=“/login.do”/>

</mvc:interceptor>

</mvc:interceptors>

以下配置为本次案例的重点知识点：

db.properties

driverclass=oracle.jdbc.driver.OracleDriver

url=jdbc:oracle:thin:@localhost:1521:orcl

user=learn

password=learn

maxActive=1

initSize=1

---

model层 ：

User.java

package com.dk.oss.entity;

/**

• 实体类

• @author Cher_du

*/

public class User {

private Integer id;

private String name;

private int 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 int getAge() {

return age;

}

public void setAge(int age) {

this.age = age;

}

@Override

public String toString() {

return “User [id=” + id + “, name=” + name + “, age=” + age + “]”;

}

}

Cost.java

package com.dk.oss.entity;

import java.sql.Timestamp;

/**

• 实体类

• @author Cher_du

*/

public class Cost {

private Integer costId;

private String name;

//基本时长

private Integer baseDuration;

//基本费用

private Double baseCost;

//单位费用

private Double unitCost;

//状态：0-启用；1-禁用；

private String status;

//描述

private String descr;

//创建时间

private Timestamp creatime;

//开通时间

private Timestamp startime;

//资费类型：1-包月；2-套餐；3-计时；

private String costType;

@Override

public String toString() {

return “User [costId=” + costId + “, name=” + name + “, baseDuration=” + baseDuration + “, baseCost=” + baseCost

• “, unitCost=” + unitCost + “, status=” + status + “, descr=” + descr + “, creatime=” + creatime

• “, startime=” + startime + “, costType=” + costType + “]”;

}

public Integer getCostId() {

return costId;

}

public void setCostId(Integer costId) {

this.costId = costId;

}

public String getName() {

return name;

}

public void setName(String name) {

this.name = name;

}

public Integer getBaseDuration() {

return baseDuration;

}

public void setBaseDuration(Integer baseDuration) {

this.baseDuration = baseDuration;

}

public Double getBaseCost() {

return baseCost;

}

public void setBaseCost(Double baseCost) {

this.baseCost = baseCost;

}

public Double getUnitCost() {

return unitCost;

}

public void setUnitCost(Double unitCost) {

this.unitCost = unitCost;

}

public String getStatus() {

return status;

}

public void setStatus(String status) {

this.status = status;

}

public String getDescr() {

return descr;

}

public void setDescr(String descr) {

this.descr = descr;

}

public Timestamp getCreatime() {

return creatime;

}

public void setCreatime(Timestamp creatime) {

this.creatime = creatime;

}

public Timestamp getStartime() {

return startime;

}

public void setStartime(Timestamp startime) {

this.startime = startime;

}

public String getCostType() {

return costType;

}

public void setCostType(String costType) {

this.costType = costType;

}

}

Admin.java

package com.dk.oss.entity;

import java.sql.Timestamp;

/**

• 实体类

• 注：

• 与要操作的表保持一致。

• @author Cher_du

*/

public class Admin {

private Integer adminId;

private String adminCode;

private String password;

private String name;

private String telephone;

private String email;

private Timestamp enrolldate;

public Integer getAdminId() {

return adminId;

}

public void setAdminId(Integer adminId) {

this.adminId = adminId;

}

public String getAdminCode() {

return adminCode;

}

public void setAdminCode(String adminCode) {

this.adminCode = adminCode;

}

public String getPassword() {

return password;

}

public void setPassword(String password) {

this.password = password;

}

public String getName() {

return name;

}

public void setName(String name) {

this.name = name;

}

public String getTelephone() {

return telephone;

}

public void setTelephone(String telephone) {

this.telephone = telephone;

}

public String getEmail() {

return email;

}

public void setEmail(String email) {

this.email = email;

}

public Timestamp getEnrolldate() {

return enrolldate;

}

public void setEnrolldate(Timestamp enrolldate) {

this.enrolldate = enrolldate;

}

}

---

DAO层：

UserDAO.java

package com.dk.oss.dao;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.ArrayList;

import java.util.List;

import javax.annotation.Resource;

import org.springframework.jdbc.core.JdbcTemplate;

import org.springframework.jdbc.core.RowMapper;

import org.springframework.stereotype.Repository;

import com.dk.oss.entity.User;

/**

• 使用JdbcTemplate实现数据库访问

• @author Cher_du

*/

@Repository(“userDAO”)

public class UserDAO {

@Resource(name=“jt”)

private JdbcTemplate jt;

public void save(User user){

jt.update("INSERT INTO user_dk "+

“VALUES(1,?,?)”,

new Object[]{user.getName(),user.getAge()});

}

/*

• 需要写一个实现了RowMapper接口的类，

• 该类规定了如何将结果集中的一条记录

• 转换成相应的java对象（比如User对象）。

*/

public List findAll(){

List users = new ArrayList();

String sql = “SELECT * FROM user_dk”;

users = jt.query(sql, new UserRowMapper());

return users;

}

/*

• queryForObject方法：

• 如果找不到相应的记录，会抛出异常。

*/

public User findById(int id){

User user = null;

String sql = "SELECT * FROM user_dk "

+“WHERE ID=?”;

user = jt.queryForObject(sql, new Object[]{id}, new UserRowMapper());

return user;

}

public User findById2(int id){

User user = null;

String sql = "SELECT * FROM user_dk "

+“WHERE id=?”;

List users = jt.query(sql, new Object[]{id}, new UserRowMapper());

if(users !=null && users.size() >0){

return users.get(0);

}

return user;

}

public void update(User user){

String sql = "UPDATE user_dk "

+“SET name=?,age=? WHERE id=?”;

jt.update(sql,new Object[]{user.getName(),user.getAge(),user.getId()});

}

public void delete(int id){

String sql =“DELETE FROM user_dk WHERE id=?”;

jt.update(sql,new Object[]{id});

}

/*

• 获得总的记录数。

• 建议使用queryForObject方法。

• 不建议使用queryForInt方法。

*/

public int getTotalRow(){

String sql = “SELECT COUNT(*) FROM user_dk”;

return jt.queryForObject(sql, Integer.class);

}

class UserRowMapper implements RowMapper{

/*

• rst:结果集。

• rowNum:记录的下标，从0开始。

*/

public User mapRow(ResultSet rst, int rowNum)

throws SQLException {

User user = new User();

user.setId(rst.getInt(“id”));

user.setName(rst.getString(“name”));

user.setAge(rst.getInt(“age”));

return user;

}

}

}

CostDAO.java

package com.dk.oss.dao;

import java.util.List;

import com.dk.oss.entity.Cost;

/**

• DAO接口

*/

public interface CostDAO {

public List findAll();

}

CostDAOJdbcImpl.java

package com.dk.oss.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 javax.annotation.Resource;

import javax.sql.DataSource;

import org.springframework.stereotype.Repository;

import com.dk.oss.entity.Cost;

/**

• DAO实现类

*/

@Repository(“costDAO”)

public class CostDAOJdbcImpl implements

CostDAO{

//注入DataSource

@Resource(name=“ds”)

private DataSource ds;

//查询出所有资费信息

public List findAll() {

List costs =

new ArrayList();

Connection conn = null;

PreparedStatement prep = null;

ResultSet rst = null;

try {

conn = ds.getConnection();

String sql = "SELECT * FROM cost "

• “ORDER BY cost_id”;;

prep =

conn.prepareStatement(sql);

rst = prep.executeQuery();

while(rst.next()){

Cost c = createCost(rst);

costs.add©;

}

} catch (SQLException e) {

e.printStackTrace();

throw new RuntimeException(e);

}finally{

if(conn != null){

try {

conn.close();

} catch (SQLException e) {

e.printStackTrace();

}

}

}

return costs;

}

//将结果集中的记录转换成一个对象。

private Cost createCost(ResultSet rst) throws SQLException {

Cost c = new Cost();

c.setCostId(rst.getInt(“cost_id”));

c.setName(rst.getString(“name”));

c.setBaseDuration(rst.getInt(“base_duration”));

c.setBaseCost(rst.getDouble(“base_cost”));

c.setUnitCost(rst.getDouble(“unit_cost”));

c.setStatus(rst.getString(“status”));

c.setDescr(rst.getString(“descr”));

c.setCreatime(rst.getTimestamp(“creatime”));

c.setStartime(rst.getTimestamp(“startime”));

c.setCostType(rst.getString(“cost_type”));

return c;

}

}

AdminDAO.java

package com.dk.oss.dao;

import com.dk.oss.entity.Admin;

/**

• DAO接口

*　注：

*　　任何接口，都不要涉及任何具体的实现。

*/

public interface AdminDAO {

public Admin findByCode(String code);

}

AdminDAOJdbcImpl.java

package com.dk.oss.dao;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import javax.annotation.Resource;

import javax.sql.DataSource;

import org.springframework.stereotype.Repository;

import com.dk.oss.entity.Admin;

/**

• DAO实现类

• @author Cher_du

*/

@Repository(“adminDAO”)

public class AdminDAOJdbcImpl implements AdminDAO{

//注入datasource。

@Resource(name=“ds”)

private DataSource ds;

public Admin findByCode(String code) {

Admin admin = null;

Connection conn = null;

PreparedStatement prep = null;

ResultSet rst = null;

try {

conn = ds.getConnection();

String sql = "SELECT * FROM admin_info "

+“WHERE admin_code=?”;

prep = conn.prepareStatement(sql);

prep.setString(1, code);

rst = prep.executeQuery();

if(rst.next()){

admin = new Admin();

admin.setAdminId(rst.getInt(“admin_id”));

admin.setAdminCode(rst.getString(“admin_code”));

admin.setPassword(rst.getString(“password”));

admin.setName(rst.getString(“name”));

admin.setTelephone(rst.getString(“telephone”));

admin.setEmail(rst.getString(“email”));

admin.setEnrolldate(rst.getTimestamp(“enrolldate”));

}

} catch (SQLException e) {

//记日志（保留现场）

e.printStackTrace();

/*

• 异常是否恢复，如果不能够恢复

• （发生了系统异常，比如数据库服务

• 暂停，网络中断），则提示用户稍后

• 重试。如果能够恢复，则立即恢复。

*/

throw new RuntimeException(e);

}finally{

if(conn != null){

try {

conn.close();

} catch (SQLException e) {

e.printStackTrace();

}

}

}

return admin;

}

}

---

Service层:

AdminService.java

package com.dk.oss.service;

import com.dk.oss.entity.Admin;

/**

• 业务层接口

*/

public interface AdminService {

//用于登录处理

public Admin checkLogin(String code,

String pwd);

}

AdminServiceImpl.java

package com.dk.oss.service;

import javax.annotation.Resource;

import org.springframework.stereotype.Service;

import com.dk.oss.dao.AdminDAO;

import com.dk.oss.entity.Admin;

/**

• 业务层实现

*/

@Service(“adminService”)

public class AdminServiceImpl implements AdminService{

@Resource(name=“adminDAO”)

private AdminDAO adminDAO;

public Admin checkLogin(String code, String pwd) {

//调用AdminDAO访问数据库

Admin admin = adminDAO.findByCode(code);

if(admin == null){

//账户不存在，需要提示用户。

//抛出一个应用异常（用户在使用系统

//的过程当中，做了一些错误的操作，比如

//输出入错误的账户或密码）。

throw new AppException(“账户错误”);

}

if(!admin.getPassword().equals(pwd)){

//密码错误，需要提示用户

throw new AppException(“密码错误”);

}

//登录成功

return admin;

}

}

AppException.java

package com.dk.oss.service;

/**

• 应用异常类。

*/

public class AppException

extends RuntimeException {

public AppException() {

}

public AppException(String message) {

super(message);

}

}

CostService.java

package com.dk.oss.service;

import java.util.List;

import com.dk.oss.entity.Cost;

public interface CostService {

public List list();

}

CostServiceImpl.java

package com.dk.oss.service;

import java.util.ArrayList;

import java.util.List;

import javax.annotation.Resource;

import org.springframework.stereotype.Service;

import com.dk.oss.dao.CostDAO;

import com.dk.oss.entity.Cost;

@Service(“costService”)

public class CostServiceImpl implements CostService{

@Resource(name=“costDAO”)

private CostDAO dao;

public List list() {

List costs = new ArrayList();

costs = dao.findAll();

return costs;

}

}

---

Controller层：

CostController.java

package com.dk.oss.controller;

import java.util.List;

import javax.annotation.Resource;

import javax.servlet.http.HttpServletRequest;

import org.springframework.stereotype.Controller;

import org.springframework.web.bind.annotation.RequestMapping;

import com.dk.oss.entity.Cost;

import com.dk.oss.service.CostService;

@Controller

public class CostController {

@Resource(name=“costService”)

private CostService costService;

@RequestMapping(“/cost.do”)

public String cost(HttpServletRequest request){

try{

List costs = costService.list();

request.setAttribute(“costs”, costs);//Integer.valueOf(“w”);

}catch(Exception e){

e.printStackTrace();

//系统异常,提示用户稍后重试。

return “error”;

}

return “cost”;

}

}

LoginController.java

package com.dk.oss.controller;

import javax.annotation.Resource;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpSession;

import org.springframework.stereotype.Controller;

import org.springframework.web.bind.annotation.RequestMapping;

import com.dk.oss.entity.Admin;

import com.dk.oss.service.AdminService;

import com.dk.oss.service.AppException;

@Controller

public class LoginController {

@Resource(name=“adminService”)

private AdminService adminService;

@RequestMapping(“/toLogin.do”)

public String toLogin(){

return “login”;

}

@RequestMapping(“/login.do”)

public String checkLogin(HttpServletRequest request,

HttpSession session){

//读取请求参数值

String adminCode = request.getParameter(“adminCode”);

String pwd = request.getParameter(“pwd”);

System.out.println(“adminCode:”+adminCode+" pwd:"+pwd);

//调用业务层模块来进行登录处理

Admin admin = null;

try{

admin = adminService.checkLogin(adminCode, pwd);

System.out.println(admin);

}catch(Exception e){

if(e instanceof AppException ){

//应用异常，需要明确提示用户。

request.setAttribute(“errorMsg”, e.getMessage());

//转发到登录页面。

return “login”;

}else{

//系统异常，提示用户稍后重试

return “error”;

}

}

//登录成功，重定向到首页。

//将一些数据绑定到session对象上，用于

//session验证。

session.setAttribute(“admin”, admin);

return “redirect:index.do”;

}

@RequestMapping(“/index.do”)

public String toIndex(){

return “index”;

}

}

SessionInterceptor.java

package com.dk.oss.interceptors;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

import javax.servlet.http.HttpSession;

import org.springframework.web.servlet.HandlerInterceptor;

import org.springframework.web.servlet.ModelAndView;

/**

• 拦截器，用于session验证。

• @author Cher_du

*/

public class SessionInterceptor implements HandlerInterceptor{

public boolean preHandle(HttpServletRequest request,

总结

我个人认为，如果你想靠着背面试题来获得心仪的offer，用癞蛤蟆想吃天鹅肉形容完全不过分。想必大家能感受到面试越来越难，想找到心仪的工作也是越来越难，高薪工作羡慕不来，却又对自己目前的薪资不太满意，工作几年甚至连一个应届生的薪资都比不上，终究是错付了，错付了自己没有去提升技术。

这些面试题分享给大家的目的，其实是希望大家通过大厂面试题分析自己的技术栈，给自己梳理一个更加明确的学习方向，当你准备好去面试大厂，你心里有底，大概知道面试官会问多广，多深，避免面试的时候一问三不知。

大家可以把Java基础，JVM，并发编程，MySQL，Redis，Spring，Spring cloud等等做一个知识总结以及延伸，再去进行操作，不然光记是学不会的，这里我也提供一些脑图分享给大家：

希望你看完这篇文章后，不要犹豫，抓紧学习，复习知识，准备在明年的金三银四拿到心仪的offer，加油，打工人！

本文已被CODING开源项目：【一线大厂Java面试题解析+核心总结学习笔记+最新讲解视频+实战项目源码】收录

需要这份系统化的资料的朋友，可以点击这里获取

wd);

//调用业务层模块来进行登录处理

Admin admin = null;

try{

admin = adminService.checkLogin(adminCode, pwd);

System.out.println(admin);

}catch(Exception e){

if(e instanceof AppException ){

//应用异常，需要明确提示用户。

request.setAttribute(“errorMsg”, e.getMessage());

//转发到登录页面。

return “login”;

}else{

//系统异常，提示用户稍后重试

return “error”;

}

}

//登录成功，重定向到首页。

//将一些数据绑定到session对象上，用于

//session验证。

session.setAttribute(“admin”, admin);

return “redirect:index.do”;

}

@RequestMapping(“/index.do”)

public String toIndex(){

return “index”;

}

}

SessionInterceptor.java

package com.dk.oss.interceptors;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

import javax.servlet.http.HttpSession;

import org.springframework.web.servlet.HandlerInterceptor;

import org.springframework.web.servlet.ModelAndView;

/**

• 拦截器，用于session验证。

• @author Cher_du

*/

public class SessionInterceptor implements HandlerInterceptor{

public boolean preHandle(HttpServletRequest request,

总结

我个人认为，如果你想靠着背面试题来获得心仪的offer，用癞蛤蟆想吃天鹅肉形容完全不过分。想必大家能感受到面试越来越难，想找到心仪的工作也是越来越难，高薪工作羡慕不来，却又对自己目前的薪资不太满意，工作几年甚至连一个应届生的薪资都比不上，终究是错付了，错付了自己没有去提升技术。

这些面试题分享给大家的目的，其实是希望大家通过大厂面试题分析自己的技术栈，给自己梳理一个更加明确的学习方向，当你准备好去面试大厂，你心里有底，大概知道面试官会问多广，多深，避免面试的时候一问三不知。

大家可以把Java基础，JVM，并发编程，MySQL，Redis，Spring，Spring cloud等等做一个知识总结以及延伸，再去进行操作，不然光记是学不会的，这里我也提供一些脑图分享给大家：

[外链图片转存中…(img-AgFej5si-1715241510633)]

[外链图片转存中…(img-RETXQmzc-1715241510633)]

[外链图片转存中…(img-rpQCKes2-1715241510634)]

希望你看完这篇文章后，不要犹豫，抓紧学习，复习知识，准备在明年的金三银四拿到心仪的offer，加油，打工人！

本文已被CODING开源项目：【一线大厂Java面试题解析+核心总结学习笔记+最新讲解视频+实战项目源码】收录

需要这份系统化的资料的朋友，可以点击这里获取