【注意添加jxl.jar 和 資料庫jar包】
package com.hp.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class DBhelper {
publicfinal String DBDriver = "com.mysql.jdbc.Driver";
publicfinal String URL = "jdbc:mysql://127.0.0.1:3306/onlinexam";
Connectioncon = null;
ResultSetres = null;
publicvoid DataBase() {
try{
Class.forName(DBDriver);
con= DriverManager.getConnection(URL, "root", "123456");
}catch (ClassNotFoundException e) {
//TODO Auto-generated catch block
System.err.println("裝載 JDBC/ODBC驅動程式失敗。");
e.printStackTrace();
}catch (SQLException e) {
//TODO Auto-generated catch block
System.err.println("無法連接配接資料庫");
e.printStackTrace();
}
}
// 根據Stringsql, String str[]查詢傳回結果集
public ResultSet Search(String sql, String str[]) {
DataBase();
try {
PreparedStatement pst=con.prepareStatement(sql);
if (str != null) {
for (int i = 0; i <str.length; i++) {
pst.setString(i + 1, str[i]);
}
}
res = pst.executeQuery();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return res;
}
public int AddU(String sql, Object str[]) {
int a = 0;
DataBase();
try {
PreparedStatement pst =con.prepareStatement(sql);
if (str != null) {
for (int i = 0; i <str.length; i++) {
pst.setObject(i + 1, str[i]);
}
}
a = pst.executeUpdate();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return a;
}
}
===============================================================================================================================
package com.hp.excel;
import java.io.File;
import java.sql.SQLException;
import java.util.List;
import com.hp.bean.Student;
import com.hp.bean.Teacher;
import com.hp.dao.StudentDao;
import com.hp.dao.TeacherDao;
import com.hp.dao.impl.StudentDaoImpl;
import com.hp.dao.impl.TeacherDaoImpl;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
public class TestDbToExcel {
publicstatic void main(String[] args) {
TestDbToExcelaa = new TestDbToExcel();
try{
aa.StudentTestDbToExcel();
System.out.println("StudentTestDbToExcel()執行完畢");
//aa.TeacherTestDbToExcel();
//System.out.println("TeacherTestDbToExcel()執行完畢");
}catch (Exception e) {
//TODO Auto-generated catch block
e.printStackTrace();
}
}
publicvoid StudentTestDbToExcel() throws Exception {
StudentDaostudentDao = new StudentDaoImpl();
WritableWorkbookwwb = null;
//建立可寫入的Excel工作簿
StringfileName = "D://onlinexam/StudentTestDbToExcel.xls";
Filefile = new File(fileName);
if(!file.exists()) {
file.createNewFile();
}
//以fileName為檔案名來建立一個Workbook
wwb= Workbook.createWorkbook(file);
//建立工作表
//學生編号(stu_id)+學生姓名(stu_name)+學生密碼(stu_password)+學生性别(stu_sex)+學生出生日期(stu_born)+學生所在班級(cla_id)+學生所在系别(dep_id)
WritableSheetws = wwb.createSheet("Test Shee 1", 0);
//查詢資料庫中所有的資料
List<Student>list = studentDao.getStudentAllByDb();
//要插入到的Excel表格的行号，預設從0開始
Labellabelstu_id = new Label(0, 0, "學生編号(stu_id)");// 表示第一列第一行
Labellabelstu_name = new Label(1, 0, "學生姓名(stu_name)");
Labellabelstu_password = new Label(2, 0, "學生密碼(stu_password)");
Labellabelstu_sex = new Label(3, 0, "學生性别(stu_sex)");
Labellabelstu_born = new Label(4, 0, "學生出生日期(stu_born)");
Labellabelcla_id = new Label(5, 0, "學生所在班級(cla_id)");
Labellabeldep_id = new Label(6, 0, "學生所在系别(dep_id)");
ws.addCell(labelstu_id);
ws.addCell(labelstu_name);
ws.addCell(labelstu_password);
ws.addCell(labelstu_sex);
ws.addCell(labelstu_born);
ws.addCell(labelcla_id);
ws.addCell(labeldep_id);
for(int i = 0; i < list.size(); i++) {
LabellabelId_i = new Label(0, i + 1, list.get(i).getStu_id() + "");//String
LabellabelName_i = new Label(1, i + 1, list.get(i).getStu_name());
LabellabelPassword_i = new Label(2, i + 1, list.get(i)
.getStu_password());
LabellabelSex_i = new Label(3, i + 1, list.get(i).getStu_sex());
LabellabelBorn_i = new Label(4, i + 1, list.get(i).getStu_born()
.toString());
LabellabelClaId_i = new Label(5, i + 1, list.get(i).getCla_id()
+"");
LabellabelDepId_i = new Label(6, i + 1, list.get(i).getDep_id()
+"");
ws.addCell(labelId_i);
ws.addCell(labelName_i);
ws.addCell(labelPassword_i);
ws.addCell(labelSex_i);
ws.addCell(labelBorn_i);
ws.addCell(labelClaId_i);
ws.addCell(labelDepId_i);
}
//寫進文檔
wwb.write();
//關閉Excel工作簿對象
wwb.close();
}
publicvoid TeacherTestDbToExcel() throws Exception {
TeacherDaoteacherDao = new TeacherDaoImpl();
WritableWorkbookwwb = null;
//建立可寫入的Excel工作簿
StringfileName = "D://onlinexam/TeacherTestDbToExcel.xls";
Filefile = new File(fileName);
if(!file.exists()) {
file.createNewFile();
}
//以fileName為檔案名來建立一個Workbook
wwb= Workbook.createWorkbook(file);
//建立工作表
//教師編号(tea_id)+教師姓名(tea_name)+教師密碼(tea_password)+教師性别(tea_sex)+教師出生日期(tea_born)+教師所在系别(dep_id)
WritableSheetws = wwb.createSheet("Test Shee 1", 0);
//查詢資料庫中所有的資料
List<Teacher>list = teacherDao.getTeacherAllByDb();
//要插入到的Excel表格的行号，預設從0開始
Labellabeltea_id = new Label(0, 0, "教師編号(tea_id)");// 表示第一列第一行
Labellabeltea_name = new Label(1, 0, "教師姓名(tea_name)");
Labellabeltea_password = new Label(2, 0, "教師密碼(tea_password)");
Labellabeltea_sex = new Label(3, 0, "教師性别(tea_sex)");
Labellabeltea_born = new Label(4, 0, "教師出生日期(tea_born)");
Labellabeldep_id = new Label(6, 0, "教師所在系别(dep_id)");
ws.addCell(labeltea_id);
ws.addCell(labeltea_name);
ws.addCell(labeltea_password);
ws.addCell(labeltea_sex);
ws.addCell(labeltea_born);
ws.addCell(labeldep_id);
for(int i = 0; i < list.size(); i++) {
LabellabelId_i = new Label(0, i + 1, list.get(i).getTea_id() + "");//String
LabellabelName_i = new Label(1, i + 1, list.get(i).getTea_name());
LabellabelPassword_i = new Label(2, i + 1, list.get(i)
.getTea_password());
LabellabelSex_i = new Label(3, i + 1, list.get(i).getTea_sex());
LabellabelBorn_i = new Label(4, i + 1, list.get(i).getTea_born()
.toString());
LabellabelDepId_i = new Label(6, i + 1, list.get(i).getDep_id()
+"");
ws.addCell(labelId_i);
ws.addCell(labelName_i);
ws.addCell(labelPassword_i);
ws.addCell(labelSex_i);
ws.addCell(labelBorn_i);
ws.addCell(labelDepId_i);
}
//寫進文檔
wwb.write();
//關閉Excel工作簿對象
wwb.close();
}
}
===============================================================================================================================
package com.hp.excel;
import java.util.List;
import com.hp.bean.Student;
import com.hp.bean.Teacher;
import com.hp.dao.StudentDao;
import com.hp.dao.TeacherDao;
import com.hp.dao.impl.StudentDaoImpl;
import com.hp.dao.impl.TeacherDaoImpl;
import com.hp.util.DBhelper;
public class TestExcelToDb {
publicstatic void main(String[] args) throws Exception {
TestExcelToDbaa = new TestExcelToDb();
aa.StudentTestExcelToDb();
System.out.println("StudentTestExcelToDb()執行完畢");
}
publicvoid StudentTestExcelToDb() {
StudentDaostudentDao = new StudentDaoImpl();
//得到表格中所有的資料
List<Student>listExcel = studentDao
.getStudentAllByExcel("D://onlinexam/StudentTestDbToExcel.xls");
//得到資料庫表中所有的資料
//List<Student> listDb=studentDao.getStudentAllByDb();
DBhelperdb = new DBhelper();
for(Student student : listExcel) {
intid = student.getStu_id();
if(!studentDao.isStudentExist(id)) {
//不存在就添加,stu_id未設定為自動增長
Stringsql = "insert into t_student(stu_id,stu_name,stu_password,stu_sex,stu_born,cla_id,dep_id)values(?,?,?,?,?,?,?)";
//如果數組 str 不為Object類型，為某一類型（如String）。那麼如果資料類型不唯一，再轉換成String類型後，與sql語句整合後執行增删改操作就會抛出類型不比對錯誤。
Object[]str = new Object[] { student.getStu_id() + "",
student.getStu_name(),student.getStu_password(),
student.getStu_sex(),student.getStu_born(),
student.getCla_id()+ "", student.getDep_id() + "" };
db.AddU(sql,str);
System.out.println("TestExcelToDb類——StudentTestExcelToDb()——1");
}else {
//存在就更新
Stringsql = "update t_student setstu_name=?,stu_password=?,stu_sex=?,stu_born=?,cla_id=?,dep_id=? wherestu_id=?";
//如果數組 str 不為Object類型，為某一類型（如String）。那麼如果資料類型不唯一，再轉換成String類型後，與sql語句整合後執行增删改操作就會抛出類型不比對錯誤。
Object[]str = new Object[] { student.getStu_name(),
student.getStu_password(),student.getStu_sex(),
student.getStu_born(),
student.getCla_id()+ "", student.getDep_id() + "",id+"" };
db.AddU(sql,str);
System.out.println("TestExcelToDb類——StudentTestExcelToDb()——2");
}
}
}
publicvoid TeacherTestExcelToDb() {
TeacherDaoteacherDao = new TeacherDaoImpl();
//得到表格中所有的資料
List<Teacher>listExcel = teacherDao.getTeacherAllByExcel("D://onlinexam/TeacherTestDbToExcel.xls");
//得到資料庫表中所有的資料
//List<Teacher> listDb=teacherDaoImpl.getTeacherAllByDb();
DBhelperdb = new DBhelper();
for(Teacher teacher : listExcel) {
intid = teacher.getTea_id();
if(!teacherDao.isTeacherExist(id)) {
//不存在就添加,tea_id未設定為自動增長
Stringsql = "insert into t_teacher(tea_id,tea_name,tea_password,tea_sex,tea_born,dep_id)values(?,?,?,?,?,?)";
//如果數組 str 不為Object類型，為某一類型（如String）。那麼如果資料類型不唯一，再轉換成String類型後，與sql語句整合後執行增删改操作就會抛出類型不比對錯誤。
Object[]str = new Object[] { teacher.getTea_id()+ "",
teacher.getTea_name(),teacher.getTea_password(),
teacher.getTea_sex(),teacher.getTea_born(), teacher.getDep_id() + "" };
db.AddU(sql,str);
System.out.println("TestExcelToDb類——TeacherTestExcelToDb()——1");
}else {
//存在就更新
Stringsql = "update t_teacher settea_name=?,tea_password=?,tea_sex=?,tea_born=?,dep_id=? where tea_id=?";
//如果數組 str 不為Object類型，為某一類型（如String）。那麼如果資料類型不唯一，再轉換成String類型後，與sql語句整合後執行增删改操作就會抛出類型不比對錯誤。
Object[]str = new Object[] {teacher.getTea_name(), teacher.getTea_password(),
teacher.getTea_sex(),teacher.getTea_born(), teacher.getDep_id() + "",id+""};
db.AddU(sql,str);
System.out.println("TestExcelToDb類——TeacherTestExcelToDb()——2");
}
}
}
}
===============================================================================================================================
package com.hp.bean;
import java.io.Serializable;
import java.util.Date;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;
@Entity
@Table(name="t_student")
public class Student implements Serializable {
privatestatic final long serialVersionUID = 1L;
privateint stu_id;
privateString stu_name;
privateString stu_password;
privateString stu_sex;
privateDate stu_born;
privateint cla_id;
privateint dep_id;
//privateint age;
publicDate getStu_born() {
returnstu_born;
}
publicvoid setStu_born(Date stu_born) {
this.stu_born= stu_born;
}
publicString getStu_sex() {
returnstu_sex;
}
publicvoid setStu_sex(String stu_sex) {
this.stu_sex= stu_sex;
}
publicint getDep_id() {
returndep_id;
}
publicvoid setDep_id(int dep_id) {
this.dep_id= dep_id;
}
@Id
publicint getStu_id() {
returnstu_id;
}
publicvoid setStu_id(int stu_id) {
this.stu_id= stu_id;
}
publicString getStu_name() {
returnstu_name;
}
publicvoid setStu_name(String stu_name) {
this.stu_name= stu_name;
}
publicString getStu_password() {
returnstu_password;
}
publicvoid setStu_password(String stu_password) {
this.stu_password= stu_password;
}
publicint getCla_id() {
returncla_id;
}
publicvoid setCla_id(int cla_id) {
this.cla_id= cla_id;
}
@Override
publicString toString() {
return"Student [stu_id=" + stu_id + ", stu_name=" + stu_name
+", stu_password=" + stu_password + ", stu_sex=" + stu_sex
+", stu_born=" + stu_born + ", cla_id=" + cla_id + ",dep_id="
+dep_id + "]";
}
publicStudent() {
}
publicStudent(int stu_id, String stu_name, String stu_password,
Stringstu_sex, Date stu_born, int cla_id, int dep_id) {
this.stu_id= stu_id;
this.stu_name= stu_name;
this.stu_password= stu_password;
this.stu_sex= stu_sex;
this.stu_born= stu_born;
this.cla_id= cla_id;
this.dep_id= dep_id;
}
}
===================================================================================================================================
package com.hp.dao;
import java.util.Date;
import java.util.List;
import com.hp.bean.*;
import com.hp.util.Page;
//學生業務邏輯接口
public interface StudentDao {
........省略其他功能的方法
publicList<Student> getStudentAllByDb();
publicList<Student> getStudentAllByExcel(String file);
public boolean isStudentExist(int sys_id);
}
==================================================================================================================================
package com.hp.dao.impl;
import java.io.File;
import java.io.IOException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;
import com.hp.bean.CourseView;
import com.hp.bean.Department;
import com.hp.bean.QuesView;
import com.hp.bean.Questions;
import com.hp.bean.StuTest;
import com.hp.bean.StuTestSimpleView;
import com.hp.bean.StuTestView;
import com.hp.bean.Student;
import com.hp.bean.StudentView;
import com.hp.bean.Test;
import com.hp.bean.TestView;
import com.hp.dao.StudentDao;
import com.hp.util.DBhelper;
import com.hp.util.HibernateSessionFactory;
import com.hp.util.MySessionFactory;
import com.hp.util.Page;
//學生業務邏輯接口的實作類
public class StudentDaoImpl implements StudentDao {
........省略其他功能的方法
publicList<Student> getStudentAllByDb() {
List<Student>list = new ArrayList<Student>();
try {
DBhelperdb = new DBhelper();
Stringsql = "select * from t_student";
ResultSetrs = db.Search(sql, null);
while(rs.next()) {
intstu_id = rs.getInt("stu_id");
Stringstu_name = rs.getString("stu_name");
Stringstu_password = rs.getString("stu_password");
Stringstu_sex = rs.getString("stu_sex");
Datestu_born = rs.getDate("stu_born");
intcla_id = rs.getInt("cla_id");
intdep_id = rs.getInt("dep_id");
//System.out.println(stu_id+" "+stu_password+" "+stu_born+
//" "+dep_id);
list.add(newStudent(stu_id, stu_name, stu_password, stu_sex,
stu_born,cla_id, dep_id));
}
} catch(SQLException e) {
// TODOAuto-generated catch block
e.printStackTrace();
}
return list;
}
publicList<Student> getStudentAllByExcel(String file) {
List<Student>list_t_student = new ArrayList<Student>();
try {
Workbookrwb = Workbook.getWorkbook(new File(file));
Sheet rs= rwb.getSheet("Test Shee 1");// 或者rwb.getSheet(0)
int clos= rs.getColumns();// 得到所有的列
int rows= rs.getRows();// 得到所有的行
System.out.println("clos:"+ clos + "\t" + " rows:" + rows);
for (inti = 1; i < rows; i++) {
for(int j = 0; j < clos; j++) {
//第一個是列數，第二個是行數
Stringstu_id = rs.getCell(j++, i).getContents();// 預設最左邊編号也算一列
//是以這裡得j++
String stu_name =rs.getCell(j++, i).getContents();
Stringstu_password = rs.getCell(j++, i).getContents();
Stringstu_sex = rs.getCell(j++, i).getContents();
Stringstu_born = rs.getCell(j++, i).getContents();
Stringcla_id = rs.getCell(j++, i).getContents();
Stringdep_id = rs.getCell(j++, i).getContents();
//将String類型的 stu_born 轉換成java.util.Date類型的 stu_born_d
SimpleDateFormatsdf = new SimpleDateFormat("yyyy-MM-dd");// 小寫的mm表示的是分鐘
java.util.Datestu_born_d = new Date();
try{
stu_born_d= sdf.parse(stu_born);
}catch (ParseException e) {
//TODO Auto-generated catch block
e.printStackTrace();
}
System.out.println("Student[stu_id=" + stu_id
+", stu_name=" + stu_name + ", stu_password="
+stu_password + ", stu_sex=" + stu_sex
+", stu_born=" + stu_born + ", cla_id=" + cla_id
+", dep_id=" + dep_id + "]");
list_t_student
.add(newStudent(Integer.parseInt(stu_id),
stu_name,stu_password, stu_sex,
stu_born_d,Integer.parseInt(cla_id),
Integer.parseInt(dep_id)));
}
}
} catch(BiffException e) {
// TODOAuto-generated catch block
e.printStackTrace();
} catch(IOException e) {
// TODOAuto-generated catch block
e.printStackTrace();
}
returnlist_t_student;
}
public booleanisStudentExist(int stu_id) {
try {
DBhelperdb = new DBhelper();
ResultSetrs = db.Search("select * from t_student where stu_id=?",
newString[] { stu_id + "" });
if(rs.next()) {
returntrue;
}
} catch(SQLException e) {
// TODOAuto-generated catch block
e.printStackTrace();
}
return false;
}
}
===================================================================================================================================
![Java小案例——随機數猜測随機數猜測[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)