作业

学生管理系统需求：
库表：

学生表（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 的知识内容、还有日期工具类

db.properties

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/companydb?useUnicode=true&characterEncoding=utf8
username=root
password=root

advanced.RowMapper

import java.sql.ResultSet;

public interface RowMapper<T> {
    public T getRow(ResultSet resultSet);
}

advanced.impl.T_studentRowMapper

import advanced.RowMapper;
import entity.t_student;

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

/**
 * 学生表（t_student），字段：
 * id、int  主键、自增
 * name、 字符串
 * password、 字符串
 * sex、 字符串
 * borndate、date
 * Phone 字符串
 */
public class T_studentRowMapper implements RowMapper<t_student> {
    @Override
    public t_student getRow(ResultSet resultSet) {
        t_student student = new t_student();
        try {
            student.setId(resultSet.getInt("id"));
            student.setName(resultSet.getString("name"));
            student.setPassword(resultSet.getString("password"));
            student.setSex(resultSet.getString("sex"));
            student.setBorndate(resultSet.getDate("borndate"));
            student.setPhone(resultSet.getString("Phone"));
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return student;
    }
}

dao.T_studentDao

import entity.t_student;

import java.util.List;

public interface T_studentDao {
    int insert(t_student student);
    int update(t_student student);
    int delete(int id);
    t_student select(int id);
    List<t_student> selectAll();
}

dao.impl.T_studentDaoimpl

import advanced.RowMapper;
import advanced.impl.T_studentRowMapper;
import dao.T_studentDao;
import entity.t_student;
import utils.DaoUtil;
import utils.DateUtil;

import java.util.List;

public class T_studentDaoimpl implements T_studentDao {
    DaoUtil<t_student> daoUtil = new DaoUtil<t_student>();
    RowMapper<t_student> rowMapper = new T_studentRowMapper();

    @Override
    public int insert(t_student stu) {
        String sql = "insert into t_student(id,name,password,sex,borndate,Phone) values(?,?,?,?,?,?);";
        return daoUtil.commonsUpdate(sql,new Object[]{stu.getId(),stu.getName(),stu.getPassword(),stu.getSex(), DateUtil.utilToSQL(stu.getBorndate()),stu.getPhone()});
    }

    @Override
    public int update(t_student stu) {
        String sql = "update t_student set name = ?,password = ?,sex = ?,borndate = ?,Phone = ? where  id = ?;";
        return daoUtil.commonsUpdate(sql,new Object[]{stu.getName(),stu.getPassword(),stu.getSex(),DateUtil.utilToSQL(stu.getBorndate()),stu.getPhone(),stu.getId()});
    }

    @Override
    public int delete(int id) {
        String sql = "delete from t_student where id = ?;";
        return daoUtil.commonsUpdate(sql,id);
    }

    @Override
    public t_student select(int id) {
        String sql = "select * from t_student where id = ?;";
        List<t_student> list = daoUtil.commonsSelect(sql,rowMapper,id);
        if (list != null) {
            return list.get(0);
        }
        return null;
    }

    @Override
    public List<t_student> selectAll() {
        String sql = "select * from t_student";
        return daoUtil.commonsSelect(sql,rowMapper,new Object[]{});
    }
}

entity.t_student

import java.util.Date;

/**
 * 学生表（t_student），字段：
 * id、int  主键、自增
 * name、 字符串
 * password、 字符串
 * sex、 字符串
 * borndate、date
 * Phone 字符串
 */
public class t_student {
    private int id;
    private String name;
    private String password;
    private String sex;
    private Date borndate;
    private String Phone;

    public t_student() {
    }

    public t_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;
        Phone = phone;
    }

    public t_student(String name, String password, String sex, Date borndate, String phone) {
        this.name = name;
        this.password = password;
        this.sex = sex;
        this.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) {
        Phone = phone;
    }

    @Override
    public String toString() {
        return "学生信息：" +
                "学生id" + id +
                " 学生姓名：" + name +
                " 密码：" + password +
                " 性别：" + sex +
                " 生日：" + borndate +
                " 电话号码：" + Phone ;
    }
}

service.T_studentService

public interface T_studentService {
    public String insert();
    public String delete(int id);
    public String update(int id);
    public String login(int id , String password);
    public String showStudent(int id);
    public String showAllStudent();
    public t_student getStudent();
}

service.impl.T_studentServiceimpl

import dao.T_studentDao;
import dao.impl.T_studentDaoimpl;
import entity.t_student;
import service.T_studentService;
import utils.DBUtil;
import utils.DateUtil;
import java.util.List;
import java.util.Scanner;

public class T_studentServiceimpl implements T_studentService {
    private T_studentDao studao = new T_studentDaoimpl();
    @Override
    public String insert() {
        String result = "注册失败";
        try {
            DBUtil.begin();
            t_student stu = getStudent();
            int i = studao.insert(stu);
            if (i > 0) {
                DBUtil.commit();
                result = "注册成功";
            }
        }catch (Exception e){
            DBUtil.rollback();
        }
        return result;
    }

    @Override
    public String delete(int id) {
        String result = "删除失败";
        try {
            DBUtil.begin();
            int i = studao.delete(id);
            if (i > 0) {
                DBUtil.commit();
                result = "删除成功";
            }
        }catch (Exception e){
            DBUtil.rollback();
        }
        return result;
    }

    @Override
    public String update(int id) {
        String result = "更新失败";
        try {
            DBUtil.begin();
            t_student checkstu = studao.select(id);
            if (checkstu == null) {
                throw new RuntimeException("目标id不存在");
            }
            t_student stu = getStudent();
            stu.setId(checkstu.getId());
            int i = studao.update(stu);
            if (i > 0) {
                DBUtil.commit();
                result = "更新成功";
            }
        }catch (Exception e){
            DBUtil.rollback();
        }
        return result;
    }

    @Override
    public String login(int id, String password) {
        String result = "登录失败";
        try {
            DBUtil.begin();
            t_student stu = studao.select(id);
            if(stu == null){
                throw new RuntimeException("用户名错误");
            }
            if(stu.getPassword().equals(password)){
                DBUtil.commit();
                result = "登陆成功";
            }else {
                result = "密码不正确";
            }
        }catch (Exception e){
            DBUtil.rollback();
        }
        return result;
    }

    @Override
    public String showStudent(int id) {
        String result = "查询失败";
        try {
            DBUtil.begin();
            t_student stu = studao.select(id);
            if (stu != null) {
                DBUtil.commit();
                return stu.toString();
            }
        }catch (Exception e){
            DBUtil.rollback();
        }
        return result;
    }

    @Override
    public String showAllStudent() {
        String result = "查询失败";
        try {
            DBUtil.begin();
            List<t_student> list = studao.selectAll();
            if (list != null) {
                DBUtil.commit();
                for (t_student stu:list
                     ) {
                    System.out.println(stu.toString());
                }
                result = "查询完毕";
            }
        }catch (Exception e){
            DBUtil.rollback();
        }
        return result;
    }

    @Override
    public t_student getStudent(){
        t_student stu = new t_student();
        Scanner sc = new Scanner(System.in);
        System.out.println("请输入姓名");
        stu.setName(sc.next());
        System.out.println("请输入密码");
        stu.setPassword(sc.next());
        System.out.println("请输入性别（男/女）");
        stu.setSex(sc.next());
        System.out.println("请输入生日(格式：yyyy-MM-dd)");
        stu.setBorndate(DateUtil.strToUtilDate(sc.next()));
        System.out.println("请输入手机号");
        stu.setPhone(sc.next());
        return stu;
    }
}

utils.DaoUtil

import advanced.RowMapper;

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

public class DaoUtil<T> {

    public int commonsUpdate(String sql,Object... args){
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = DBUtil.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            if (args != null) {
                for (int i = 0; i <args.length ; i++) {
                    preparedStatement.setObject(i+1,args[i]);
                }
            }
            return preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.closeAll(null,preparedStatement,null);
        }
        return 0;
    }

    public List<T> commonsSelect(String sql, RowMapper<T> rowMapper,Object... args){
        List<T> list = new ArrayList<T>();
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = DBUtil.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            if (args != null) {
                for (int i = 0; i < args.length; i++) {
                    preparedStatement.setObject(i+1,args[i]);
                }
            }
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                list.add(rowMapper.getRow(resultSet));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.closeAll(null,preparedStatement,resultSet);
        }
        return list;
    }
}

utils.DateUtil

import java.text.ParseException;
import java.text.SimpleDateFormat;

public class DateUtil {
    private static final SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");

    public static java.util.Date strToUtilDate(String str){
        try {
            return simpleDateFormat.parse(str);
        } catch (ParseException e) {
            e.printStackTrace();
        }
        return null;
    }

    public static java.sql.Date utilToSQL(java.util.Date date){
        return new java.sql.Date(date.getTime());
    }
}

utils.DBUtil

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class DBUtil {
    private static final Properties properties = new Properties();
    private static final ThreadLocal<Connection> THREAD_LOCAL = new ThreadLocal<Connection>();

    static {
        InputStream is = DBUtil.class.getResourceAsStream("/db.properties");
        try {
            properties.load(is);
            Class.forName(properties.getProperty("driver"));
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection(){
        Connection connection = THREAD_LOCAL.get();
        try {
            if (connection == null) {
                connection = DriverManager.getConnection(properties.getProperty("url"),properties.getProperty("username"),properties.getProperty("password"));
                THREAD_LOCAL.set(connection);
            }
        }catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }

    public static void begin(){
        Connection connection = getConnection();
        try {
            connection.setAutoCommit(false);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void commit(){
        Connection connection = getConnection();
        try {
            connection.commit();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.closeAll(connection,null,null);
        }
    }

    public static void rollback(){
        Connection connection = getConnection();
        try {
            connection.rollback();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.closeAll(connection,null,null);
        }
    }

    public static void closeAll(Connection connection, PreparedStatement preparedStatement,ResultSet resultSet){
        try {
            if (resultSet != null) {
                resultSet.close();
            }
            if (preparedStatement != null) {
                preparedStatement.close();
            }
            if (connection != null) {
                connection.close();
                THREAD_LOCAL.remove();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

test.Test

import service.T_studentService;
import service.impl.T_studentServiceimpl;

import java.util.Scanner;

public class Test {
    public static void main(String[] args) {
        T_studentService tss = new T_studentServiceimpl();
        int choice = 0;
        do {
            System.out.println("1.添加学生 2.删除学生 3.修改学生信息 4.学生登陆 5.查询单个学生 6.查询所有学生 0.退出");
            Scanner sc = new Scanner(System.in);
            choice = sc.nextInt();
            switch (choice){
                case 1:
                    System.out.println(tss.insert());
                    break;
                case 2:
                    System.out.println("请输入id");
                    System.out.println(tss.delete(sc.nextInt()));
                    break;
                case 3:
                    System.out.println("请输入id");
                    System.out.println(tss.update(sc.nextInt()));
                    break;
                case 4:
                    System.out.println("请输入id");
                    int id = sc.nextInt();
                    System.out.println("请输入密码");
                    String password = sc.next();
                    System.out.println(tss.login(id,password));
                    break;
                case 5:
                    System.out.println("请输入id");
                    System.out.println(tss.showStudent(sc.nextInt()));
                    break;
                case 6:
                    System.out.println(tss.showAllStudent());
                    break;
                case 0:
                    break;
            }
        }while (choice != 0);
        System.out.println("已退出系统");
    }
}

新增学生

更新学生信息

登录

查询学生信息

查询全部学生信息

删除学生

第二个作业是 HTML 的综合案例！

<!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>好程序员特训营&nbsp;&nbsp;</a>
						<a>JavaEE分布式开发&nbsp;&nbsp;</a>
						<a>JavaSE核心基础&nbsp;&nbsp;</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>首页&nbsp;&nbsp;</span>
						<span>课程培训&nbsp;&nbsp;</span>
						<span>教学保障&nbsp;&nbsp;</span>
						<span>免费视频&nbsp;&nbsp;</span>
						<span>公开课&nbsp;&nbsp;</span>
						<span>企业合作&nbsp;&nbsp;</span>
						<span>就业喜报&nbsp;&nbsp;</span>
						<span>学员天地&nbsp;&nbsp;</span>
						<span>关于千锋&nbsp;&nbsp;</span>
						<span>加入我们</span>
						<hr />
					</td>
				</tr>
				<tr>
					<td colspan="2" align="right">
						首页>课程培训>JavaEE列表
					</td>
				</tr>
			</table>
		</div>
		
			<div>
				<table>
					<tr>
						<td><b><big>课程培训</big></b></td>
					</tr>
					<tr>
					<td><b>共108种课程内容</b></td>
					</tr>
					<tr>
						<td><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 align="center" width="100%">
				<tr>
					<td><img src="img/012.png" /></td>
				</tr>
			</table>
		</div>
	</body>
</html>