学生管理系统需求:
库表:
学生表(t_student),字段:
id、int 主键、自增
name、 字符串
password、 字符串
sex、 字符串
borndate、date
Phone 字符串
(所有字段不允许为空)
功能:
学生信息添加功能(学号自动生成)
学生信息删除功能
学生信息修改功能(除学号以外,其他均可修改)
学生登录功能
学生信息查询功能(单个学生、所有学生)
以上功能都应具有操作后的提示消息(如:登录成功, 添加成功 、 删除成功、 修改成功、未查询到该学生信息)
数据库连接:
数据库驱动、数据库URL、用户名、密码,应从配置文件中读取
开发项目的步骤:
1.库表设计
2.新建项目
3.导入项目相关Jar包
4.创建各个职责代码所在的包结构
0.utils
1.entity
2.dao
3.dao.impl
4.service
5.service.impl
6.test
5.准备db.properties配置文件,DBUtils
6.工具类
7.开发Entity
8.开发DAO接口、实现类
9.开发Service接口、实现类
10.开发Test
提示:基本应用到所有 JDBC 的知识内容、还有日期工具类
第二个作业是 HTML 的综合案例
学生管理系统
table
CREATE TABLE t_student(
id INT PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(20) NOT NULL,
`password` VARCHAR(20) NOT NULL,
sex VARCHAR(20) NOT NULL,
borndate DATE NOT NULL,
phone VARCHAR(11) NOT NULL
)CHARSET=utf8;
SELECT * FROM t_student;
database.properties
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/account?useUnicode=true&characterEncoding=utf8
username=root
password=1234
#初始化连接
initialSize=10
#最大连接数量
maxActive=30
#最小空闲连接
minIdle=5
#超时等待时间以毫秒为单位
maxWait=5000
utils
package 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.util.Properties;
/**
* 连接池工具类
*/
public class DBUtils {
private static DruidDataSource dataSource;
static {
Properties properties = new Properties();
InputStream is = DBUtils.class.getResourceAsStream("/database.properties");
try {
properties.load(is);
dataSource = (DruidDataSource) DruidDataSourceFactory.createDataSource(properties);
} catch (IOException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}
// 返回一个数据源
public static DataSource getDataSource(){
return dataSource;
}
}
//-------------------------------------
package utils;
import java.text.ParseException;
import java.text.SimpleDateFormat;
public class DateUtils {
private static final SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
//String转util.date
public static java.util.Date strToUtilDate(String str) {
try {
return simpleDateFormat.parse(str);
} catch (ParseException e) {
e.printStackTrace();
}
return null;
}
//util转sql
public static java.sql.Date utilToSql(java.util.Date date) {
return new java.sql.Date(date.getTime());
}
}
entity
package entity;
import java.util.Date;
public class Student {
private int id;
private String name;
private String password;
private String sex;
private Date borndate;
private String phone;
public Student(){};
public Student(int id, String name, String password, String sex, Date borndate, String phone) {
this.id = id;
this.name = name;
this.password = password;
this.sex = sex;
this.borndate = borndate;
this.phone = phone;
}
@Override
public String toString() {
return "Student{" +
"id=" + id +
", name='" + name + '\'' +
", password='" + password + '\'' +
", sex='" + sex + '\'' +
", borndate=" + borndate +
", phone='" + phone + '\'' +
'}';
}
public int getId() {
return id;
}
public void setId(int id){
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
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 String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
}
###### dao
```java
package dao;
import entity.Student;
import java.util.List;
public interface StudentDao {
public int insert(Student student);
public int update(Student student);
public int delete(int id);
public Student select(int id);
public List<Student> selectAll();
}
daoImpl
package daoImpl;
import dao.StudentDao;
import entity.Student;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import utils.DBUtils;
import utils.DateUtils;
import java.sql.SQLException;
import java.util.List;
public class StudentDaoImpl implements StudentDao {
private QueryRunner queryRunner = new QueryRunner(DBUtils.getDataSource());
@Override
public int insert(Student student) {
Object[] params = {student.getName(), student.getPassword(), student.getSex(), DateUtils.utilToSql(student.getBorndate()), student.getPhone()};
try {
return queryRunner.update("insert into t_student (name,password,sex,borndate,phone) values(?,?,?,?,?);", params);
} catch (SQLException e) {
e.printStackTrace();
}
return 0;
}
@Override
public int update(Student student) {
Object[] params = {student.getName(), student.getPassword(), student.getSex(), DateUtils.utilToSql(student.getBorndate()), student.getPhone(), student.getId()};
try {
return queryRunner.update("update t_student set name = ?,password = ?,sex = ?,borndate = ?,phone = ? where id = ?;", params);
} catch (SQLException e) {
e.printStackTrace();
}
return 0;
}
@Override
public int delete(int id) {
try {
return queryRunner.update("delete from t_student where id = ?", id);
} catch (SQLException e) {
e.printStackTrace();
}
return 0;
}
@Override
public Student select(int id) {
try {
return queryRunner.query("select * from t_student where id = ?;", new BeanHandler<Student>(Student.class), id);
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
@Override
public List<Student> selectAll() {
try {
return queryRunner.query("select * from t_student;", new BeanListHandler<Student>(Student.class));
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
}
service
package service;
import entity.Student;
public interface StudentService {
void add();
void delete();
void update();
boolean login();
void select();
void SelectAll();
}
serviceImpl
package serviceImpl;
import daoImpl.StudentDaoImpl;
import entity.Student;
import service.StudentService;
import utils.DateUtils;
import java.sql.SQLOutput;
import java.util.Scanner;
public class StudentServiceImpl implements StudentService {
private StudentDaoImpl studentDao = new StudentDaoImpl();
private Scanner input = new Scanner(System.in);
@Override
public void add() {
int result = 0;
System.out.println("输入学生姓名:");
String name = input.next();
System.out.println("输入密码:");
String password = input.next();
System.out.println("输入性别:");
String sex = input.next();
System.out.println("输入生日(yyyy-MM-dd):");
String borndate = input.next();
System.out.println("输入手机号码:");
String phone = input.next();
Student student = new Student(1,name,password,sex, DateUtils.strToUtilDate(borndate),phone);
result = studentDao.insert(student);
if(result == 0){
System.out.println("添加失败");
}else {
System.out.println("添加成功");
}
}
@Override
public void delete() {
int result = 0;
System.out.println("输入要删除的学生id:");
int id = input.nextInt();
result = studentDao.delete(id);
if(result == 0){
System.out.println("删除失败");
}else {
System.out.println("删除成功");
}
}
@Override
public void update() {
int result = 0;
System.out.println("输入修改的学生id:");
int id = input.nextInt();
System.out.println("输入修改的学生姓名:");
String name = input.next();
System.out.println("输入修改的密码:");
String password = input.next();
System.out.println("输入修改的性别:");
String sex = input.next();
System.out.println("输入修改的生日(yyyy-MM-dd):");
String borndate = input.next();
System.out.println("输入修改的手机号码:");
String phone = input.next();
Student student = new Student(id,name,password,sex, DateUtils.strToUtilDate(borndate),phone);
result = studentDao.update(student);
if(result == 0){
System.out.println("修改失败");
}else {
System.out.println("修改成功");
}
}
@Override
public boolean login() {
System.out.println("输入学生id:");
int id = input.nextInt();
Student student = studentDao.select(id);
if(student == null){
System.out.println("学生不存在");
return false;
}
System.out.println("输入密码:");
String password = input.next();
if(password.equals(student.getPassword())){
System.out.println("登录成功");
return true;
}
return false;
}
@Override
public void select() {
System.out.println("输入查询的学生id:");
int id = input.nextInt();
System.out.println(studentDao.select(id));
}
@Override
public void SelectAll() {
studentDao.selectAll().forEach(System.out::println);
}
}
test
package test;
import serviceImpl.StudentServiceImpl;
import java.util.Scanner;
public class Test {
public static void main(String[] args) {
StudentServiceImpl studentService = new StudentServiceImpl();
System.out.println("欢迎使用学生系统");
boolean result = studentService.login();
if(result){
System.out.println("1.学生信息添加 2.学生信息删除 3.学生信息修改 4.单个学生信息查询 5.所有学生信息查询 0.退出");
Scanner input = new Scanner(System.in);
int choice = input.nextInt();
switch (choice){
case 1:
studentService.add();
break;
case 2:
studentService.delete();
break;
case 3:
studentService.update();
break;
case 4:
studentService.select();
break;
case 5:
studentService.SelectAll();
break;
case 0:
return;
}
}
}
}
案例
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>综合案例</title>
</head>
<body>
<!--头部-->
<div>
<table width="100%" align="center">
<tr>
<td align="left">
千锋教育-稀有的坚持全程面授品质的大型IT教育机构
</td>
<td align="right">
<a>好程序员特训营 </a>
<a>JavaEE分布式开发 </a>
<a>JavaSE核心基础 </a>
<a>加入我们</a>
</td>
</tr>
<tr>
<td>
<img src="img/new_logo.png" />
</td>
<td align="right">
<img src="img/nav_r_ico.png" />
</td>
</tr>
<tr>
<td colspan="2" align="center">
<hr/>
<span>首页 </span>
<span>课程培训 </span>
<span>教学保障 </span>
<span>免费视频 </span>
<span>公开课 </span>
<span>企业合作 </span>
<span>就业喜报 </span>
<span>学员天地 </span>
<span>关于千锋 </span>
<span>加入我们</span>
<hr />
</td>
</tr>
<tr>
<td colspan="2" align="right">
首页>课程培训>JavaEE列表
</td>
</tr>
</table>
</div>
<!--中间部分-->
<div>
<table>
<tr>
<td>
<h3>课程培训</h3>
<h4>共108种课程内容</h4>
</td>
</tr>
<tr>
<td>
<hr />
<img src="img/001.png" />
</td>
</tr>
</table>
<table align="center" width="100%">
<tr align="center">
<td>
<img src="img/002.png" /><br />
<div align="center">书名:XXX</div>
<div align="center">售价:180</div>
</td>
<td>
<img src="img/003.png" /><br />
<div align="center">书名:XXX</div>
<div align="center">售价:180</div>
</td>
<td>
<img src="img/004.png" /><br />
<div align="center">书名:XXX</div>
<div align="center">售价:180</div>
</td>
<td>
<img src="img/005.png" /><br />
<div align="center">书名:XXX</div>
<div align="center">售价:180</div>
</td>
<td>
<img src="img/006.png" /><br />
<div align="center">书名:XXX</div>
<div align="center">售价:180</div>
</td>
</tr>
<tr align="center">
<td>
<img src="img/007.png" /><br />
<div align="center">书名:XXX</div>
<div align="center">售价:180</div>
</td>
<td>
<img src="img/008.png" /><br />
<div align="center">书名:XXX</div>
<div align="center">售价:180</div>
</td>
<td>
<img src="img/009.png" /><br />
<div align="center">书名:XXX</div>
<div align="center">售价:180</div>
</td>
<td>
<img src="img/010.png" /><br />
<div align="center">书名:XXX</div>
<div align="center">售价:180</div>
</td>
<td>
<img src="img/011.png" /><br />
<div align="center">书名:XXX</div>
<div align="center">售价:180</div>
</td>
</tr>
</table>
</div>
<!--底部-->
<div>
<table width="90%" align="center">
<tr>
<td><img src="img/012.png" /></td>
</tr>
</table>
</div>
</body>
</html>