JSP+Servlet

数据库的设计

数据库的创建

MySQL数据库：

数据库：db_student
学生表：tb_student
管理员表：tb_manager

create database db_student;

create table tb_student
(
	stuid int(11) not null auto_increment primary key,
	stuname varchar(10) not null,
	stusex varchar(10) not null,
	stuage varchar(10) not null,
	stutel varchar(20) not null,
	studept varchar(20) not null,
	stuaddress varchar(50) not null
);


create table tb_manager
(
	m_id int(11) not null auto_increment primary key,
	m_username varchar(20) not null,
	m_password varchar(40) not null
);

学生实体类：
Student.java

package com.bbs2018.student.model;

public class Student {
	private int stId;// 学号
	private String stName;// 姓名
	private String stSex;// 性别
	private String stAge;// 年龄
	private long stTel;// 电话
	private String stDept;// 系别
	private String stAddress;// 地址

	public int getStId() {
		return stId;
	}
	public void setStId(int stId) {
		this.stId = stId;
	}

	public String getStName() {
		return stName;
	}
	public void setStName(String stName) {
		this.stName = stName;
	}

	public String getStSex() {
		return stSex;
	}
	public void setStSex(String stSex) {
		this.stSex = stSex;
	}

	public String getStAge() {
		return stAge;
	}
	public void setStAge(String stAge) {
		this.stAge = stAge;
	}

	public long getStTel() {
		return stTel;
	}
	public void setStTel(long stTel) {
		this.stTel = stTel;
	}

	public String getStDept() {
		return stDept;
	}
	public void setStDept(String stDept) {
		this.stDept = stDept;
	}

	public String getStAddress() {
		return stAddress;
	}
	public void setStAddress(String stAddress) {
		this.stAddress = stAddress;
	}

	public Student(int stId, String stName, String stSex, String stAge,
			long stTel, String stDept, String stAddress) {
		this.stId = stId;
		this.stName = stName;
		this.stSex = stSex;
		this.stAge = stAge;
		this.stTel = stTel;
		this.stDept = stDept;
		this.stAddress = stAddress;
	}

	public Student(String stName, String stSex, String stAge, long stTel,
			String stDept, String stAddress) {
		this.stName = stName;
		this.stSex = stSex;
		this.stAge = stAge;
		this.stTel = stTel;
		this.stDept = stDept;
		this.stAddress = stAddress;
	}

	public Student() {
	}
}

数据访问层，学生信息CRUD操作
接口：StudentDao.java

package com.bbs2018.student.dao;

import java.util.List;

import com.bbs2018.student.model.Student;

/**
 * 数据访问层，学生信息CRUD操作
 * @author bbs2018
 */

public interface StudentDao {
	/**
	 * 获取指定的用戶(更新页面操作使用)
	 * 
	 * @param id
	 * @return stu
	 */
	public Student findStudentByid(int id);
		

	/**
	 * 添加学生信息
	 * 
	 * @param stu
	 * @return flag
	 */
	public boolean addStudent(Student stu);

	/**
	 * 删除学生信息
	 * 
	 * @param id
	 * @return flag
	 */
	public boolean delStudent(int id);

	/**
	 * 更新学生信息
	 * 
	 * @param stu
	 * @return flag
	 */
	public boolean updateStudent(Student stu);

	/**
	 * 查询全体学生信息
	 * 
	 * @return list
	 */
	@SuppressWarnings("unchecked")
	public List StSelect();
	/**
	 * 根据ID查询学生信息
	 * 
	 * @return Student
	 */
	 public Student findStudentById(String id); 
}

学生数据访问层实现类：

StudentDaoImpl.java

package com.bbs2018.student.dao.impl;

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

import com.bbs2018.student.dao.StudentDao;
import com.bbs2018.student.model.Student;

public class StudentDaoImpl implements StudentDao {
	private Connection conn = DbConn.getConn();

	/**
	 * 获取指定的用戶(更新页面操作使用)
	 * 
	 * @param id
	 * @return stu
	 */
	public Student findStudentByid(int id) {
		Student stu = null;
		String sql = "select * from Student2 where stId=?";
		try {
			PreparedStatement psmt = conn.prepareStatement(sql);
			psmt.setInt(1, id);
			ResultSet rs = psmt.executeQuery();
			while (rs.next()) {
				int stId = rs.getInt("stId");
				String stName = rs.getString("stName");
				String stSex = rs.getString("stSex");
				String stAge = rs.getString("stAge");
				Long stTel = rs.getLong("stTel");
				String stDept = rs.getString("stDept");
				String stAddress = rs.getString("stAddress");
				stu = new Student(stId, stName, stSex, stAge, stTel, stDept,
						stAddress);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return stu;
	}

	/**
	 * 添加学生信息
	 * 
	 * @param stu
	 * @retursn flag
	 */
	public boolean addStudent(Student stu) {
		// Student(stId,stName,stSex,stAge,stTel,stDept,stAddress)
		boolean flag = false;
		String sql = "insert into Student2(stId,stName,stSex,stAge,stTel,stDept,stAddress) values(?,?,?,?,?,?,?)";
		try {
			PreparedStatement psmt = conn.prepareStatement(sql);
			psmt.setInt(1, stu.getStId());
			psmt.setString(2, stu.getStName());
			psmt.setString(3, stu.getStSex());
			psmt.setString(4, stu.getStAge());
			psmt.setLong(5, stu.getStTel());
			psmt.setString(6, stu.getStDept());
			psmt.setString(7, stu.getStAddress());
			int i = psmt.executeUpdate();
			if (i == 1) {
				flag = true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
	}

	/**
	 * 删除学生信息
	 * 
	 * @param id
	 * @return flag
	 */
	public boolean delStudent(int id) {
		boolean flag = false;
		String sql = "delete from Student2 where stId=?";
		try {
			PreparedStatement psmt = conn.prepareStatement(sql);
			psmt.setInt(1, id);
			if (psmt.executeUpdate() > 0) {
				flag = true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
	}

	/**
	 * 更新学生信息
	 * 
	 * @param stu
	 * @return flag
	 */
	public boolean updateStudent(Student stu) {
		boolean flag = false;
		String sql = "update Student2 set stName=?,stSex=?,stAge=?,stTel=?,stDept=?,stAddress=? where stId=? ";
		try {
			PreparedStatement psmt = conn.prepareStatement(sql);
			psmt.setString(1, stu.getStName());
			psmt.setString(2, stu.getStSex());
			psmt.setString(3, stu.getStAge());
			psmt.setLong(4, stu.getStTel());
			psmt.setString(5, stu.getStDept());
			psmt.setString(6, stu.getStAddress());
			psmt.setInt(7, stu.getStId());

			int i = psmt.executeUpdate();
			if (i == 1) {
				flag = true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
	}

	/**
	 * 查询全体学生信息
	 * 
	 * @return list
	 */
	@SuppressWarnings("unchecked")
	public List StSelect() {
		List list = new ArrayList();
		String sql = "select * from Student2";
		try {
			Statement smt = conn.createStatement();
			ResultSet rs = smt.executeQuery(sql);
			// Student(stId,stName,stSex,stAge,stTel,stDept,stAddress)
			while (rs.next()) {
				int stId = rs.getInt("stId");
				String stName = rs.getString("stName");
				String stSex = rs.getString("stSex");
				String stAge = rs.getString("stAge");
				Long stTel = rs.getLong("stTel");
				String stDept = rs.getString("stDept");
				String stAddress = rs.getString("stAddress");

				Student stu = new Student(stId, stName, stSex, stAge, stTel,
						stDept, stAddress);
				list.add(stu);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public Student findStudentById(String id) {
		// TODO Auto-generated method stub
		Student student = null;
		String sql = "select * from Student2 where stId=?";

		PreparedStatement pstmt;
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, id);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				student = new Student();
				student.setStId(Integer.parseInt(rs.getString("stId")));
				student.setStName(rs.getString("stName"));
				student.setStSex(rs.getString("stSex"));
				student.setStAge(rs.getString("stAge"));
				student.setStDept(rs.getString("stDept"));
				student.setStAddress(rs.getString("stAddress"));
				student.setStTel(Long.parseLong(rs.getString("stTel")));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return student;
	}
}
数据库连接类：
DbConn.java
package com.bbs2018.student.util;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

public class DbConn {
	/**
	 * 数据库连接类
	 * @author bbs2018
	 */
	private static String username=null;
	private static String password=null;
	private static  String driver=null;
	private static String url=null;
	static{
		Properties ps=new Properties();
		try {
			ps.load(DbConn.class.getResourceAsStream("/db.properties"));
			driver=ps.getProperty("driver");
			url=ps.getProperty("url");
			username=ps.getProperty("username");
			password=ps.getProperty("password");
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	public static Connection getConn() {
		Connection con = null;
		try {
		    Class.forName(driver);
			con = DriverManager.getConnection(url, username, password);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return con;
	}

	public void closeCon(Connection con) {
		// TODO Auto-generated method stub
		if(con!=null){
			try {
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
	public static void main(String[] args) {
		try {
			DbConn.getConn();
			System.out.println("数据库连接成功");
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			
		}
	}
}