IDEA+Java+Jsp

DB.close(rs);

DB.close(stmt);

DB.close(conn);

}

return totalRecords;

}

/**

• 批量导入excel表中学生的信息到数据库中（用到组件jxl.jar)

• @param file excel表所在的相对路径或绝对路径（包括文件完整的名字）

• @return 返回那些学号在数据库中已经存在的学号

*/

public static List adds(String file) {

Connection conn = DB.getConn();

List snumber = new ArrayList();

Workbook info;//定义工作簿

String sql = “insert into student_info values (?, ?, ?, ?, ?, ?, ?, ?)”;

try {

info = Workbook.getWorkbook(new FileInputStream(file));

Sheet sheet = info.getSheet(0);//获得工作薄中表单对象

int size = sheet.getRows(); //获得表单的行数

for(int i = 1;i < size;i++){

Cell c = sheet.getCell(0,i);//获得表单的i行0列的单元格即学号

/*

• 查看学号是否已经存在

*/

if(StudentManager.getBySno(c.getContents().trim())!=null) {

snumber.add(c.getContents().trim());

continue;

}

PreparedStatement pstmt = DB.prepare(conn, sql);

for(int j=0; j<8; j++) {

c = sheet.getCell(j,i);

pstmt.setString(j+1, c.getContents().trim());

}

pstmt.execute();

}

} catch (BiffException e) {

e.printStackTrace();

} catch (FileNotFoundException e) {

e.printStackTrace();

} catch (IndexOutOfBoundsException e) {

e.printStackTrace();

} catch (IOException e) {

e.printStackTrace();

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

return snumber;

}

}

TeacherManager

---

教师信息管理类，实现教师信息的增加，查看，删除。

package com.sjsq.service;

import com.sjsq.model.Teacher;

import com.sjsq.util.DB;

import jxl.Cell;

import jxl.Sheet;

import jxl.Workbook;

import jxl.read.biff.BiffException;

import java.io.FileInputStream;

import java.io.FileNotFoundException;

import java.io.IOException;

import java.sql.*;

import java.util.ArrayList;

import java.util.List;

public class TeacherManager {

public static boolean save(Teacher te) {

Connection conn = DB.getConn();

String sql = null;

boolean b = false;

sql = “insert into teacher_info values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)”;

PreparedStatement pstmt = DB.prepare(conn, sql);

try {

pstmt.setString(1, te.getNumber());

pstmt.setString(2, te.getName());

pstmt.setString(3, te.getSex());

pstmt.setString(4, te.getDept());

pstmt.setString(5, te.getDegree());

pstmt.setString(6, te.getTitle());

pstmt.setShort(7, te.getRight());

pstmt.setString(8, te.getPhone());

pstmt.setString(9, te.getEmail());

pstmt.setString(10, te.getGroup());

pstmt.setString(11, te.getPassword());

pstmt.execute();

b = true;

} catch (SQLException e) {

e.printStackTrace();

} finally {

DB.close(pstmt);

DB.close(conn);

}

return b;

}

public static int getTeachers(List teachers, int pageNo,

int pageSize) {

int totalRecords = -1;

Connection conn = DB.getConn();

String sql = null;

sql = "select * from teacher_info limit " + (pageNo - 1) * pageSize

• “,” + pageSize;

Statement stmt = DB.getStatement(conn);

ResultSet rs = DB.getResultSet(stmt, sql);

Statement stmtCount = DB.getStatement(conn);

ResultSet rsCount = null;

rsCount = DB.getResultSet(stmtCount,

“select count(*) from teacher_info”);

try {

rsCount.next();

totalRecords = rsCount.getInt(1);

while (rs.next()) {

Teacher teacher = new Teacher();

teacher.setEmail(rs.getString(“temail”));

teacher.setGroup(rs.getString(“tgroup”));

teacher.setName(rs.getString(“tname”));

teacher.setNumber(rs.getString(“tno”));

teacher.setPassword(rs.getString(“tpassword”));

teacher.setPhone(rs.getString(“ttel”));

teacher.setDept(rs.getString(“tdept”));

teacher.setSex(rs.getString(“tsex”));

teacher.setDegree(rs.getString(“tdegree”));

teacher.setRight(rs.getShort(“tright”));

teacher.setTitle(rs.getString(“ttitle”));

teachers.add(teacher);

}

} catch (SQLException e) {

e.printStackTrace();

} finally {

DB.close(rsCount);

DB.close(stmtCount);

DB.close(rs);

DB.close(stmt);

DB.close(conn);

}

return totalRecords;

}

public static boolean deleteByTno(String id) {

boolean b = false;

Connection conn = DB.getConn();

String sql = null;

sql = “delete from teacher_info where tno = '” + id + “'”;

Statement stmt = DB.getStatement(conn);

try {

DB.executeUpdate(stmt, sql);

b = true;

} catch (Exception e) {

e.printStackTrace();

} finally {

DB.close(stmt);

DB.close(conn);

}

return b;

}

public static Teacher check(String num, String password)

throws UserNotFoundException, PasswordNotCorrectException {

Teacher teacher = null;

Connection conn = DB.getConn();

String sql = null;

sql = “select * from teacher_info where tno = '” + num + “'”;

Statement stmt = DB.getStatement(conn);

ResultSet rs = DB.getResultSet(stmt, sql);

try {

if (!rs.next()) {

throw new UserNotFoundException(“用户不存在:” + num);

} else {

if (!password.equals(rs.getString(“tpassword”))) {

throw new PasswordNotCorrectException(“密码不正确!”);

}

}

teacher = new Teacher();

teacher.setEmail(rs.getString(“temail”));

teacher.setGroup(rs.getString(“tgroup”));

teacher.setName(rs.getString(“tname”));

teacher.setNumber(rs.getString(“tno”));

teacher.setPassword(rs.getString(“tpassword”));

teacher.setPhone(rs.getString(“ttel”));

teacher.setDept(rs.getString(“tdept”));

teacher.setSex(rs.getString(“tsex”));

teacher.setDegree(rs.getString(“tdegree”));

teacher.setRight(rs.getShort(“tright”));

teacher.setTitle(rs.getString(“ttitle”));

} catch (SQLException e) {

e.printStackTrace();

} finally {

DB.close(rs);

DB.close(stmt);

DB.close(conn) 《大厂前端面试题解析+Web核心总结学习笔记+企业项目实战源码+最新高清讲解视频》无偿开源 徽信搜索公众号【编程进阶路】 ;

}

return teacher;

}

public static Teacher getByTno(String num) {

Connection conn =