千锋第46天作业

UTILS

public class DBUtils {
    private static final Properties PROPERTIES = new Properties();
    //为当前线程绑定一个Connection连接。从头到尾
    private static final ThreadLocal<Connection> THREAD_LOCAL = new ThreadLocal<Connection>();

    static {
        InputStream is = DBUtils.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() {
        //在ThreadLocal里取
        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);//存在ThreadLocal里
            }

        } 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 {
            DBUtils.closeAll(connection, null, null);
        }
    }

    //回滚事务
    public static void rollback() {
        Connection connection = getConnection();
        try {
            connection.rollback();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.closeAll(connection, null, null);
        }
    }


    //释放资源
    public static void closeAll(Connection connection, Statement statement, ResultSet resultSet) {
        try {
            if (resultSet != null) {
                resultSet.close();
            }
            if (statement != null) {
                statement.close();
            }
            if (connection != null) {
                connection.close();
                THREAD_LOCAL.remove();//关闭连接后，移除线程中绑定的连接对象。
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
public class DateUtils {
    private static final SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");

    //字符串转Util
    public static java.util.Date strToUtilDate(String str) {
        try {
            return simpleDateFormat.parse(str);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    //util转sql
    public static java.sql.Date utilToSql(java.util.Date date) {
        return new java.sql.Date(date.getTime());
    }
}
public class DaoUtils<T> {
    /**
     * 增删改方法
     */
    public int commonsUpdate(String sql, Object... args) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = DBUtils.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                preparedStatement.setObject(i + 1, args[i]);
            }
            return preparedStatement.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtils.closeAll(null, preparedStatement, null);
        }
        return 0;
    }

    /**
     * 查一个或查多个
     */
    public List<T> commonsSelect(String sql, RowMapper<T> rowMapper, Object... args) {
        List<T> elements = new ArrayList<T>();
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = DBUtils.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()) {

                elements.add(rowMapper.getRow(resultSet));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.closeAll(null, preparedStatement, resultSet);
        }
        return elements;
    }
}

ENTITY

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;
    }

    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;
    }

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

DAO

import java.util.List;

public class StudentDaoImpl implements StudentDao {
    DaoUtils<Student> daoUtil = new DaoUtils<Student>();


    @Override
    public int insert(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(), DateUtils.utilToSql(stu.getBorndate()),stu.getPhone()});
    }

    @Override
    public int update(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(), DateUtils.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 Student select(int id) {
        List<Student> list = daoUtil.commonsSelect("select * from t_student where id = ?;",new StudentRowMapper(),id);
        if (list != null) {
            return list.get(0);
        }
        return null;
    }

    @Override
    public List<Student> selectAll() {
        String sql = "select * from t_student";
        return daoUtil.commonsSelect(sql,new StudentRowMapper(),new Object[]{});
    }
}
public interface StudentDao {
    int insert(Student student);
    int update(Student student);
    int delete(int id);
    Student select(int id);
    List<Student> selectAll();
}

SERVICE

public class StudentServiceImpl implements StudentService {

    private Connection connection = null;
    private PreparedStatement preparedStatement = null;
    private ResultSet resultSet = null;

    private StudentDao studao = new StudentDaoImpl();

    @Override
    public String insert() {
        String result = "注册失败";
        try {
            DBUtils.begin();
            Student stu = getStudent();
            int i = studao.insert(stu);
            if (i > 0) {
                DBUtils.commit();
                result = "注册成功";
            }
        } catch (Exception e) {
            DBUtils.rollback();
        }
        return result;
    }

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

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

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

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

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

    @Override
    public Student getStudent() {
        Student stu = new 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(DateUtils.strToUtilDate(sc.next()));
        System.out.println("请输入手机号");
        stu.setPhone(sc.next());
        return stu;
    }
}
public interface 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 Student getStudent();

}

TEST

public class TestStudent {
    public static void main(String[] args) {
        StudentService s = new 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(s.insert());
                    break;
                case 2:
                    System.out.println("请输入id");
                    System.out.println(s.delete(sc.nextInt()));
                    break;
                case 3:
                    System.out.println("请输入id");
                    System.out.println(s.update(sc.nextInt()));
                    break;
                case 4:
                    System.out.println("请输入id");
                    int id = sc.nextInt();
                    System.out.println("请输入密码");
                    String password = sc.next();
                    System.out.println(s.login(id,password));
                    break;
                case 5:
                    System.out.println("请输入id");
                    System.out.println(s.showStudent(sc.nextInt()));
                    break;
                case 6:
                    System.out.println(s.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>
						<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>