package com.hjf.dao;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import com.hjf.entity.Course;
import com.hjf.util.DBUtil;
/**
* 课程Dao
* Dao层操作数据
* @author Hu
*
*/
public class CourseDao {
/**
* 添加
* @param course
* @return
*/
public boolean add(Course course) {
String sql = "insert into course(name, sex,minzu,zhuce,age,zhengzhi,fuwu) values('" + course.getName() + "','" + course.getSex() + "','" + course.getMinzu() + "','" + course.getZhuce()+ "','" + course.getAge() + "','" + course.getZhengzhi() + "','" + course.getFuwu() + "')";
//创建数据库链接
Connection conn = DBUtil.getConn();
Statement state = null;
boolean f = false;
int a = 0;
try {
state = conn.createStatement();
state.executeUpdate(sql);
} catch (Exception e) {
e.printStackTrace();
} finally {
//关闭连接
DBUtil.close(state, conn);
}
if (a > 0) {
f = true;
}
return f;
}
/**
* 删除
*
* @param id
* @return
*/
public boolean delete (int id) {
boolean f = false;
String sql = "delete from course where id='" + id + "'";
Connection conn = DBUtil.getConn();
Statement state = null;
int a = 0;
try {
state = conn.createStatement();
a = state.executeUpdate(sql);
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtil.close(state, conn);
}
if (a > 0) {
f = true;
}
return f;
}
/**
* 修改
* @param name
* @param pass
*/
public boolean update(Course course) {
String sql = "update course set name='" + course.getName() + "', sex='" + course.getSex() + "', minzu='" + course.getMinzu()+ "', zhuce='" + course.getZhuce()+ "', age='" + course.getAge()+ "', zhengzhi='" + course.getZhengzhi()+ "', fuwu='" + course.getFuwu()
+ "' where id='" + course.getId() + "'";
Connection conn = DBUtil.getConn();
Statement state = null;
boolean f = false;
int a = 0;
try {
state = conn.createStatement();
a = state.executeUpdate(sql);
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtil.close(state, conn);
}
if (a > 0) {
f = true;
}
return f;
}
/**
* 验证课程名称是否唯一
* true --- 不唯一
* @param name
* @return
*/
public boolean name(String name) {
boolean flag = false;
String sql = "select name from course where name = '" + name + "'";
Connection conn = DBUtil.getConn();
Statement state = null;
ResultSet rs = null;
try {
state = conn.createStatement();
rs = state.executeQuery(sql);
while (rs.next()) {
flag = true;
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtil.close(rs, state, conn);
}
return flag;
}
/**
* 通过ID得到课程信息
* @param id
* @return
*/
public Course getCourseById(int id) {
String sql = "select * from course where id ='" + id + "'";
Connection conn = DBUtil.getConn();
Statement state = null;
ResultSet rs = null;
Course course = null;
try {
state = conn.createStatement();
rs = state.executeQuery(sql);
while (rs.next()) {
String name = rs.getString("name");
String sex = rs.getString("sex");
String minzu = rs.getString("minzu");
String zhuce = rs.getString("zhuce");
String age = rs.getString("age");
String zhengzhi = rs.getString("zhengzhi");
String fuwu = rs.getString("fuwu");
course = new Course(id, name, sex, minzu,zhuce,age,zhengzhi,fuwu);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
DBUtil.close(rs, state, conn);
}
return course;
}
/**
* 通过name得到Course
* @param name
* @return
*/
public Course getCourseByName(String name) {
String sql = "select * from course where name ='" + name + "'";
Connection conn = DBUtil.getConn();
Statement state = null;
ResultSet rs = null;
Course course = null;
try {
state = conn.createStatement();
rs = state.executeQuery(sql);
while (rs.next()) {
int id = rs.getInt("id");
String sex = rs.getString("sex");
String minzu = rs.getString("minzu");
String zhuce = rs.getString("zhuce");
String age = rs.getString("age");
String zhengzhi = rs.getString("zhengzhi");
String fuwu = rs.getString("fuwu");
course = new Course(id, name, sex, minzu,zhuce,age,zhengzhi,fuwu);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
DBUtil.close(rs, state, conn);
}
return course;
}
/**
* 查找
* @param name
* @param teacher
* @param classroom
* @return
*/
public List<Course> search(String name, String sex, String minzu, String zhuce, String age, String zhengzhi, String fuwu) {
String sql = "select * from course where ";
if (name != ""&&sex !=""&&minzu!=""&&zhuce!=""&&age!=""&&zhengzhi!=""&&fuwu!="") {
sql += "name like '%" + name + "%'&&sex like '%" + sex + "%'&&minzu like '%" + minzu + "%'&&zhuce like '%" + zhuce + "%'&&age like '%" + age + "%'&&zhengzhi like '%" + zhengzhi + "%'&&fuwu like '%" + fuwu + "%'";
}
if (sex != "") {
sql += "sex like '%" + sex + "%'";
}
if (minzu != "") {
sql += "minzu like '%" + minzu + "%'";
}
if (zhuce != "") {
sql += "zhuce like '%" + zhuce + "%'";
}
if (age != "") {
sql += "age like '%" + age + "%'";
}
if (zhengzhi != "") {
sql += "zhengzhi like '%" + zhengzhi + "%'";
}
if (fuwu != "") {
sql += "fuwu like '%" + fuwu + "%'";
}
List<Course> list = new ArrayList<>();
Connection conn = DBUtil.getConn();
Statement state = null;
ResultSet rs = null;
try {
state = conn.createStatement();
rs = state.executeQuery(sql);
Course bean = null;
while (rs.next()) {
int id = rs.getInt("id");
String name2 = rs.getString("name");
String sex2 = rs.getString("sex");
String minzu2 = rs.getString("minzu");
String zhuce2 = rs.getString("zhuce");
String age2 = rs.getString("age");
String zhengzhi2 = rs.getString("zhengzhi");
String fuwu2 = rs.getString("fuwu");
bean = new Course(id, name2, sex2, minzu2,zhuce2,age2,zhengzhi2,fuwu2);
list.add(bean);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtil.close(rs, state, conn);
}
return list;
}
/**
* 全部数据
* @param name
* @param teacher
* @param classroom
* @return
*/
public List<Course> list() {
String sql = "select * from course";
List<Course> list = new ArrayList<>();
Connection conn = DBUtil.getConn();
Statement state = null;
ResultSet rs = null;
try {
state = conn.createStatement();
rs = state.executeQuery(sql);
Course bean = null;
while (rs.next()) {
int id = rs.getInt("id");
String name2 = rs.getString("name");
String sex2 = rs.getString("sex");
String minzu2 = rs.getString("minzu");
String zhuce2 = rs.getString("zhuce");
String age2 = rs.getString("age");
String zhengzhi2 = rs.getString("zhengzhi");
String fuwu2 = rs.getString("fuwu");
bean = new Course(id, name2, sex2, minzu2,zhuce2,age2,zhengzhi2,fuwu2);
list.add(bean);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtil.close(rs, state, conn);
}
return list;
}
}
package com.hjf.entity;
public class Course {
private int id;
private String name;
private String sex;
private String minzu;
private String zhuce;
private String age;
private String zhengzhi;
private String fuwu;
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 getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public String getMinzu() {
return minzu;
}
public void setMinzu(String minzu) {
this.minzu = minzu;
}
public String getZhuce() {
return zhuce;
}
public void setZhuce(String zhuce) {
this.zhuce = zhuce;
}
public String getAge() {
return age;
}
public void setAge(String age) {
this.age = age;
}
public String getZhengzhi() {
return zhengzhi;
}
public void setZhengzhi(String zhengzhi) {
this.zhengzhi = zhengzhi;
}
public String getFuwu() {
return fuwu;
}
public void setFuwu(String fuwu) {
this.fuwu = fuwu;
}
public Course() {}
public Course(int id, String name, String sex, String minzu,String zhuce, String age, String zhengzhi,String fuwu) {
this.id = id;
this.name = name;
this.sex = sex;
this.minzu = minzu;
this.zhuce = zhuce;
this.age = age;
this.zhengzhi = zhengzhi;
this.fuwu = fuwu;
}
public Course(String name, String sex, String minzu,String zhuce, String age, String zhengzhi,String fuwu) {
this.name = name;
this.sex = sex;
this.minzu = minzu;
this.zhuce = zhuce;
this.age = age;
this.zhengzhi = zhengzhi;
this.fuwu = fuwu;
}
}
package com.hjf.service;
import java.util.List;
import com.hjf.dao.CourseDao;
import com.hjf.entity.Course;
/**
* CourseService
* 服务层
* @author Hu
*
*/
public class CourseService {
CourseDao cDao = new CourseDao();
/**
* 添加
* @param course
* @return
*/
public boolean add(Course course) {
boolean f = false;
if(!cDao.name(course.getName())) {
cDao.add(course);
f = true;
}
return f;
}
/**
* 删除
*/
public void del(int id) {
cDao.delete(id);
}
/**
* 修改
* @return
*/
public void update(Course course) {
cDao.update(course);
}
/**
* 通过ID得到一个Course
* @return
*/
public Course getCourseById(int id) {
return cDao.getCourseById(id);
}
/**
* 通过Name得到一个Course
* @return
*/
public Course getCourseByName(String name) {
return cDao.getCourseByName(name);
}
/**
* 查找
* @return
*/
public List<Course> search(String name, String sex, String minzu,String zhuce, String age, String zhengzhi,String fuwu) {
return cDao.search( name, sex, minzu,zhuce, age, zhengzhi,fuwu);
}
/**
* 全部数据
* @return
*/
public List<Course> list() {
return cDao.list();
}
}
package com.hjf.servlet;
import java.io.IOException;
import java.util.List;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.hjf.entity.Course;
import com.hjf.service.CourseService;
@WebServlet("/CourseServlet")
public class CourseServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
CourseService service = new CourseService();
/**
* 方法选择
*/
protected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
req.setCharacterEncoding("utf-8");
String method = req.getParameter("method");
if ("add".equals(method)) {
add(req, resp);
} else if ("del".equals(method)) {
del(req, resp);
} else if ("update".equals(method)) {
update(req, resp);
} else if ("search".equals(method)) {
search(req, resp);
} else if ("getcoursebyid".equals(method)) {
getCourseById(req, resp);
} else if ("getcoursebyname".equals(method)) {
getCourseByName(req, resp);
} else if ("list".equals(method)) {
list(req, resp);
}
}
/**
* 添加
* @param req
* @param resp
* @throws IOException
* @throws ServletException
*/
private void add(HttpServletRequest req, HttpServletResponse resp) throws IOException, ServletException {
req.setCharacterEncoding("utf-8");
//获取数据
String name = req.getParameter("name");
String sex = req.getParameter("sex");
String minzu = req.getParameter("minzu");
String zhuce = req.getParameter("zhuce");
String age = req.getParameter("age");
String zhengzhi = req.getParameter("zhengzhi");
String fuwu = req.getParameter("fuwu");
Course course = new Course(name, sex, minzu,zhuce, age, zhengzhi,fuwu);
//添加后消息显示
if(service.add(course)) {
req.setAttribute("message", "添加成功");
req.getRequestDispatcher("add.jsp").forward(req,resp);
} else {
req.setAttribute("message", "课程名称重复,请重新录入");
req.getRequestDispatcher("add.jsp").forward(req,resp);
}
}
/**
* 全部
* @param req
* @param resp
* @throws ServletException
*/
private void list(HttpServletRequest req, HttpServletResponse resp) throws IOException, ServletException{
req.setCharacterEncoding("utf-8");
List<Course> courses = service.list();
req.setAttribute("courses", courses);
req.getRequestDispatcher("list.jsp").forward(req,resp);
}
/**
* 通过ID得到Course
* @param req
* @param resp
* @throws ServletException
*/
private void getCourseById(HttpServletRequest req, HttpServletResponse resp) throws IOException, ServletException{
req.setCharacterEncoding("utf-8");
int id = Integer.parseInt(req.getParameter("id"));
Course course = service.getCourseById(id);
req.setAttribute("course", course);
req.getRequestDispatcher("detail2.jsp").forward(req,resp);
}
/**
* 通过名字查找
* 跳转至删除
* @param req
* @param resp
* @throws IOException
* @throws ServletException
*/
private void getCourseByName(HttpServletRequest req, HttpServletResponse resp) throws IOException, ServletException{
req.setCharacterEncoding("utf-8");
String name = req.getParameter("name");
Course course = service.getCourseByName(name);
if(course == null) {
req.setAttribute("message", "查无此课程!");
req.getRequestDispatcher("del.jsp").forward(req,resp);
} else {
req.setAttribute("course", course);
req.getRequestDispatcher("detail.jsp").forward(req,resp);
}
}
/**
* 删除
* @param req
* @param resp
* @throws IOException
* @throws ServletException
*/
private void del(HttpServletRequest req, HttpServletResponse resp) throws IOException, ServletException{
req.setCharacterEncoding("utf-8");
int id = Integer.parseInt(req.getParameter("id"));
service.del(id);
req.setAttribute("message", "删除成功!");
req.getRequestDispatcher("del.jsp").forward(req,resp);
}
/**
* 修改
* @param req
* @param resp
* @throws IOException
* @throws ServletException
*/
private void update(HttpServletRequest req, HttpServletResponse resp) throws IOException, ServletException{
req.setCharacterEncoding("utf-8");
int id = Integer.parseInt(req.getParameter("id"));
String name = req.getParameter("name");
String sex = req.getParameter("sex");
String minzu = req.getParameter("minzu");
String zhuce = req.getParameter("zhuce");
String age = req.getParameter("age");
String zhengzhi = req.getParameter("zhengzhi");
String fuwu = req.getParameter("fuwu");
Course course = new Course(id,name, sex, minzu,zhuce, age, zhengzhi,fuwu);
service.update(course);
req.setAttribute("message", "修改成功");
req.getRequestDispatcher("CourseServlet?method=list").forward(req,resp);
}
/**
* 查找
* @param req
* @param resp
* @throws ServletException
*/
private void search(HttpServletRequest req, HttpServletResponse resp) throws IOException, ServletException{
req.setCharacterEncoding("utf-8");
String name = req.getParameter("name");
String sex = req.getParameter("sex");
String minzu = req.getParameter("minzu");
String zhuce = req.getParameter("zhuce");
String age = req.getParameter("age");
String zhengzhi = req.getParameter("zhengzhi");
String fuwu = req.getParameter("fuwu");
List<Course> courses = service.search(name, sex, minzu,zhuce, age, zhengzhi,fuwu);
req.setAttribute("courses", courses);
req.getRequestDispatcher("searchlist.jsp").forward(req,resp);
}
}
package com.hjf.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/**
* 数据库连接工具
* @author Hu
*
*/
public class DBUtil {
public static String db_url = "jdbc:mysql://localhost:3306/user";
public static String db_user = "root";
public static String db_pass = "root";
public static Connection getConn () {
Connection conn = null;
try {
Class.forName("com.mysql.jdbc.Driver");//加载驱动
conn = DriverManager.getConnection(db_url, db_user, db_pass);
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}
/**
* 关闭连接
* @param state
* @param conn
*/
public static void close (Statement state, Connection conn) {
if (state != null) {
try {
state.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void close (ResultSet rs, Statement state, Connection conn) {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (state != null) {
try {
state.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void main(String[] args) throws SQLException {
Connection conn = getConn();
PreparedStatement pstmt = null;
ResultSet rs = null;
String sql ="select * from course";
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery();
if(rs.next()){
System.out.println("空");
}else{
System.out.println("不空");
}
}
}