文章目录
写在前面
小demo:用来练习和巩固所学到的知识
包含的知识点:Java基础、Http传输协议、MySQL、JDB C、Tomcat、html、css、js、Servlet、jsp、EL表达式、JSTL标签库
开发工具:IDEA 2019
数据库:MySQL
连接池:Druid连接池
Github仓库地址:点我
jar包下载:点我!提取码:y4ef
参考:Javaweb实现增删改查操作操作
用JavaWeb部分知识实现对数据库的增删改查
只展示Student和Teacher的相关代码
一、效果图
二、数据库实现
1. 建立数据库表
文件名:lms2.sql
/*!40101 SET NAMES utf8 */;
/*!40101 SET SQL_MODE=''*/;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`lms2` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `lms2`;
/*Table structure for table `course` */
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
`id` varchar(10) NOT NULL COMMENT '课程号',
`name` varchar(20) NOT NULL COMMENT '姓名',
`teacherId` varchar(20) NOT NULL COMMENT '任课教师号',
`creditHour` int(3) NOT NULL COMMENT '学时',
`creditPoint` int(2) NOT NULL COMMENT '学分',
`schoolTime` datetime NOT NULL COMMENT '上课时间',
`classPlace` varchar(50) NOT NULL COMMENT '上课地点',
`examTime` datetime NOT NULL COMMENT '考试时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*Data for the table `course` */
/*Table structure for table `department` */
DROP TABLE IF EXISTS `department`;
CREATE TABLE `department` (
`depNo` varchar(20) NOT NULL COMMENT '系号',
`name` varchar(20) NOT NULL COMMENT '系名称',
`intro` varchar(128) DEFAULT NULL COMMENT '系的简介',
PRIMARY KEY (`depNo`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*Data for the table `department` */
/*Table structure for table `student` */
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`id` varchar(20) NOT NULL COMMENT '学号',
`name` varchar(20) NOT NULL COMMENT '姓名',
`sex` varchar(2) NOT NULL DEFAULT '男' COMMENT '性别',
`bornDate` datetime DEFAULT NULL COMMENT '出生日期',
`scores` int(3) DEFAULT NULL COMMENT '入学成绩',
`depNo` varchar(20) NOT NULL COMMENT '所在系号',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*Data for the table `student` */
/*Table structure for table `teacher` */
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
`id` varchar(20) NOT NULL COMMENT '职工号',
`name` varchar(20) NOT NULL COMMENT '姓名',
`sex` varchar(2) NOT NULL DEFAULT '男' COMMENT '性别',
`bornDate` datetime DEFAULT NULL COMMENT '出生日期',
`depNo` varchar(20) NOT NULL COMMENT '所在系号',
`jobTitle` varchar(20) NOT NULL COMMENT '职称',
`major` varchar(12) NOT NULL COMMENT '专业及教学方向',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*Data for the table `teacher` */
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
2. 数据库连接工具druid和dbutils
文件名:jdbc.properties
代码如下:
username=root
password=123456
url=jdbc:mysql://localhost:3306/lms2?useUnicode=true&characterEncoding=UTF8&useSSL=false
driverClassName=com.mysql.jdbc.Driver
initialSize=5
maxActive=10
JDBC工具类用来加载配置文件、创建数据库连接池、获取连接和关闭连接释放内存
代码如下:
文件名:JdbcUtils.java
package com.makonike.utils;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;
/**
* JDBC工具类 使用Druid连接池
*
* @author Makonike
**/
public class JdbcUtils {
/** 数据源 **/
private static DruidDataSource dataSource = null;
public static void main(String[] args) {
}
static {
try{
//加载配置文件
Properties properties = new Properties();
//使用ClassLoader加载配置文件,获取字节输入流in
InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("jdbc.properties");
properties.load(in);
//创建数据库连接池,初始化连接池对象
dataSource = (DruidDataSource) DruidDataSourceFactory.createDataSource(properties);
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 获取连接Connection对象
* @param
* @return java.sql.Connection
* @author Makonike
* @date 2021/2/23 10:49
*/
public static Connection getConnection() throws SQLException {
return dataSource.getConnection();
}
/**
* 关闭连接,释放内存
* @param conn
* @return void
* @author Makonike
* @date 2021/2/23 10:50
*/
public static void close(Connection conn){
if(conn!=null){
try{
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
三、jar包和架构解析
1.常用jar包分析
jar包下载:点我!提取码:y4ef
2.三层架构分析
dao 数据持久化层,主要用于和数据库进行交互,返回和传递数据
service 处理业务逻辑层,完成其相关的业务逻辑的具体实现
servlet和jsp 前端页面展示和服务器运行程序,让用户能够直接看到现象
entity 实体类,这里特别注意 druid连接池中的查询实现需要属性名与数据库表的字段名一致
四、主体代码 - 实现简单增删改查
1.dao层
dao层实现数据的持久化操作,更多的是实现对数据的前后台交互,数据能够来回之间相互传递
接口层
文件名:StudentDao.java
package com.makonike.dao;
import com.makonike.entity.Student;
import java.util.List;
/**
* 持久化接口 - Student
*
* @author Makonike
* @date 2021-02-23 16:04
**/
public interface StudentDao {
/**
* 显示所有学生
* @param
* @return java.util.List<com.makonike.entity.Student>
* @author Makonike
* @date 2021/3/10 22:51
*/
List<Student> findAllStudent();
/**
* 根据学号查询学生信息
* @param id 需要查询的学号
* @return 如果是null就没有找到,反之则找到,返回学生信息
* @author Makonike
* @date 2021/2/23 16:06
*/
Student queryStudentById(String id);
/**
* 保存学生信息
* @param student 新的student对象
* @return int
* @author Makonike
* @date 2021/2/23 16:18
*/
int saveStudent(Student student);
/**
* 根据id删除
* @param id
* @return boolean
* @author Makonike
* @date 2021/2/24 0:52
*/
int deleteStudent(String id);
/**
* 根据id删除
* @param student
* @return boolean
* @author Makonike
* @date 2021/2/24 0:55
*/
int updateStudent(Student student);
}
文件名:TeacherDao.java
package com.makonike.dao;
import com.makonike.entity.Teacher;
import java.util.List;
/**
* 持久化接口 - Teacher
*
* @author Makonike
* @date 2021-02-24 2:19
**/
public interface TeacherDao {
/**
* 查询所有教师
* @param
* @return 返回一个Teacher的List集合
* @author Makonike
* @date 2021/3/12 19:08
*/
List<Teacher> findAllTeacher();
/**
* 通过教职工工号查询教师信息
* @param id
* @return com.makonike.entity.Teacher
* @author Makonike
* @date 2021/3/13 10:27
*/
Teacher queryTeacherById(String id);
/**
* 教师基本信息的输入
* @param teacher
* @return int
* @author Makonike
* @date 2021/2/24 2:23
*/
int saveTeacher(Teacher teacher);
/**
* 教师基本信息的删除
* @param id
* @return int
* @author Makonike
* @date 2021/2/24 2:23
*/
int deleteTeacher(String id);
/**
* 教师基本信息的修改
* @param teacher
* @return int
* @author Makonike
* @date 2021/2/24 2:23
*/
int updateTeacher(Teacher teacher);
}
接口实现层
接口实现层:重点关注BaseDao.java
文件名:BaseDao.java
是所有dao实现类的超类
package com.makonike.dao.impl;
import com.makonike.utils.JdbcUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
/**
* Dao持久层的超类
*
* @author Makonike
* @date 2021-02-23 11:35
**/
public abstract class BaseDao {
private QueryRunner queryRunner = new QueryRunner();
/**
* 增、删、改
* @param sql 数据库操作语句
* @param args ?占位符 参数
* @return int
* @author Makonike
* @date 2021/2/23 11:41
*/
public int update(String sql, Object... args){
Connection connection = null;
try {
connection = JdbcUtils.getConnection();
return queryRunner.update(connection,sql,args);
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtils.close(connection);
}
return -1;
}
/**
* 查询返回一个javaBean的sql语句
* @param type 返回的对象类型
* @param sql 数据库操作语句
* @param args ?占位符 参数
* @return <T> 返回的类型的泛型
* @author Makonike
* @date 2021/2/23 15:51
*/
public <T> T queryForOne(Class<T> type, String sql, Object...args){
Connection con = null;
try {
con = JdbcUtils.getConnection();
return queryRunner.query(con,sql,new BeanHandler<T>(type),args);
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.close(con);
}
return null;
}
/**
* 查询返回多个javaBean的sql语句
* @param type 返回的对象类型
* @param sql 数据库操作语句
* @param args ?占位符 参数
* @return <T> 返回的类型的泛型
* @author Makonike
* @date 2021/2/23 15:51
*/
public <T>List<T> queryForList(Class<T> type, String sql, Object... args){
Connection con = null;
try {
con = JdbcUtils.getConnection();
return queryRunner.query(con,sql, new BeanListHandler<T>(type),args);
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.close(con);
}
return null;
}
文件名:StudentDaoImpl.java
package com.makonike.dao.impl;
import com.makonike.dao.StudentDao;
import com.makonike.entity.Student;
import java.util.List;
/**
* 持久化dao接口的实现类 - Student
*
* @author Makonike
* @date 2021-02-23 16:17
**/
public class StudentDaoImpl extends BaseDao implements StudentDao {
/**
* 显示所有学生
* @param
* @return 返回一个学生List集合
* @author Makonike
* @date 2021/3/10 22:52
*/
@Override
public List<Student> findAllStudent() {
//language=MySQL
String sql = "select `id`, `name`, `sex`,`bornDate`, `scores`, `depNo` from lms2.student order by `id` asc ";
return queryForList(Student.class, sql);
}
/**
* 根据学号查询一个学生的信息
* @param id
* @return com.makonike.pojo.Student 找到则返回查找到的学生信息,若找不到则返回null
* @author Makonike
* @date 2021/2/23 16:37
*/
@Override
public Student queryStudentById(String id) {
//language=MySQL
String sql = "select `id`, `name`, `sex`, `bornDate`, `scores`, `depNo` from lms2.student where id = ?";
return queryForOne(Student.class,sql,id);
}
/**
* 录入/保存学生信息
* @param student 需要录入的学生对象
* @return int int>0表示录入成功,反之则录入失败
* @author Makonike
* @date 2021/2/23 16:51
*/
@Override
public int saveStudent(Student student) {
//language=MySQL
String sql = "insert into lms2.student(`id`, `name`, `sex`, `bornDate`, `scores`, `depNo`) value (?,?,?,?,?,?)";
return update(sql,student.getId(),student.getName(),student.getSex(),student.getBornDate(),student.getScores(),student.getDepNo());
}
/**
* 根据学号删除学生信息
* @param id
* @return int int>0表示删除成功,反之则删除失败
* @author Makonike
* @date 2021/2/24 1:33
*/
@Override
public int deleteStudent(String id) {
//language=MySQL
String sql = "delete from lms2.student where `id` = ?" ;
return update(sql, id);
}
/**
* 更改学生信息
* @param student
* @return int 返回值大于0表示更改成功,反之则更改失败
* @author Makonike
* @date 2021/2/24 1:33
*/
@Override
public int updateStudent(Student student) {
//language=MySQL
String sql = "update lms2.student set `id`=?,`name`=?,`sex`=?,`bornDate`=?,`scores`=?,`depNo`=? where `id` = ? ";
return update(sql,student.getId(),student.getName(),student.getSex(),student.getBornDate(),student.getScores(),student.getDepNo(),student.getId());
}
}
文件名:TeacherDaoImpl.java
package com.makonike.dao.impl;
import com.makonike.dao.TeacherDao;
import com.makonike.entity.Teacher;
import java.util.List;
/**
* 持久化dao接口的实现类 - Teacher
*
* @author Makonike
* @date 2021-02-24 2:24
**/
public class TeacherDaoImpl extends BaseDao implements TeacherDao {
/**
* 显示所有教师
* @param
* @return 返回一个教师对象的List集合>
* @author Makonike
* @date 2021/3/12 19:08
*/
@Override
public List<Teacher> findAllTeacher() {
//language=MySQL
String sql = "select `id`, `name`, `sex`, `bornDate`, `depNo`, `jobTitle`, `major` from lms2.teacher order by `id` asc ";
return queryForList(Teacher.class, sql);
}
/**
* 通过教职工号查询教师信息
* @param id
* @return 如果存在则返回查询到的教师对象,若没有则返回null
* @author Makonike
* @date 2021/3/13 10:28
*/
@Override
public Teacher queryTeacherById(String id) {
//language=MySQL
String sql = "select `id`, `name`, `sex`, `bornDate`, `depNo`, `jobTitle`, `major` from lms2.teacher where id = ?";
return queryForOne(Teacher.class,sql,id);
}
/**
* 教职工信息的输入
* @param teacher
* @return int
* @author Makonike
* @date 2021/2/24 13:42
*/
@Override
public int saveTeacher(Teacher teacher) {
//language=MySQL
String sql = "insert into lms2.teacher(`id`, `name`, `sex`, `bornDate`, `depNo`, `jobTitle`, `major`) value (?,?,?,?,?,?,?)";
return update(sql,teacher.getId(),teacher.getName(),teacher.getSex(),teacher.getBornDate(),teacher.getDepNo(),teacher.getJobTitle(),teacher.getMajor());
}
/**
* 根据教职工号删除教职工的信息
* @param id
* @return int
* @author Makonike
* @date 2021/2/24 13:44
*/
@Override
public int deleteTeacher(String id) {
//language=MySQL
String sql = "delete from lms2.teacher where `id` = ?";
return update(sql,id);
}
/**
* 修改教职工信息
* @param teacher
* @return int
* @author Makonike
* @date 2021/2/24 14:03
*/
@Override
public int updateTeacher(Teacher teacher) {
//language=MySQL
String sql = "update lms2.teacher set `name`=?,`sex`=?,`bornDate`=?,`depNo`=?,`jobTitle`=?,`major`=?";
return update(sql,teacher.getName(),teacher.getSex(),teacher.getBornDate(),teacher.getDepNo(),teacher.getJobTitle(),teacher.getMajor());
}
}
2.entity实体类
entity 实体类,这里特别注意 druid连接池中的查询实现需要属性名与数据库表的字段名一致
文件名:Student.java
package com.makonike.entity;
import java.util.Date;
/**
* 学生实体类
*
* @author Makonike
* @date 2021-02-23 10:25
**/
public class Student {
private String id;
private String name;
private String sex;
private Date bornDate;
private int scores;
private String depNo;
public Student() {
}
public Student(String id, String name, String sex, Date bornDate, int scores, String depNo) {
this.id = id;
this.name = name;
this.sex = sex;
this.bornDate = bornDate;
this.scores = scores;
this.depNo = depNo;
}
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public Date getBornDate() {
return bornDate;
}
public void setBornDate(Date bornDate) {
this.bornDate = bornDate;
}
public int getScores() {
return scores;
}
public void setScores(int scores) {
this.scores = scores;
}
public String getDepNo() {
return depNo;
}
public void setDepNo(String depNo) {
this.depNo = depNo;
}
@Override
public String toString() {
return "Student{" +
"id='" + id + '\'' +
", name='" + name + '\'' +
", sex='" + sex + '\'' +
", bornDate='" + bornDate + '\'' +
", scores=" + scores +