本人是一名大二学生,自学Java做了一个图书管理系统的数据库课程设计,能力有限,欢迎大佬指点!
连接数据库操作
/**
* 建立数据库连接!!
*/
package util;
import java.sql.*;
public class DBConnection {
public static final String DRIVER ="com.microsoft.sqlserver.jdbc.SQLServerDriver";
public static final String URL="jdbc:sqlserver://127.0.0.1:1433;databaseName=StudentManagement";
public static final String USER="sa";
public static final String PASSWORD="hyc5670526";
Connection conn =null;
public DBConnection(){
try{
Class.forName(DRIVER);
}catch (ClassNotFoundException e) {
e.printStackTrace();
}
try{
conn=DriverManager.getConnection(URL,USER,PASSWORD);
}catch (SQLException e) {
e.printStackTrace();
}
}
public Connection getConnection(){
return this.conn;
}
public void close(){
if(this.conn!=null){
try{
this.conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
DAO类
package dao;
import vo.Borrow;
import util.DateConvert;
import vo.User;
public interface IUserDao {
//插入读者信息
public boolean doInsert(User user) throws Exception;
//显示图书信息
public boolean doSelectAllBook()throws Exception;
//显示读者信息
public boolean doSelectAllStudent()throws Exception;
//显示借阅信息
public boolean doSelectAllBorrow()throws Exception;
//按学号修改读者信息
public boolean doUpdate(int id,User user)throws Exception;
//按学号删除读者信息
public boolean doDelete(int id)throws Exception;
//按图书编号查找图书信息
public boolean doFindBook(int id)throws Exception;
//插入借阅信息
public boolean doInsert(Borrow borrow,DateConvert dateconvert)throws Exception;
//删除借阅信息
public boolean doDelete2(int id)throws Exception;
}
DAOImp类
package dao;
import java.sql.*;
import java.util.Scanner;
import util.DBConnection;
import util.DateConvert;
import vo.Borrow;
import vo.User;
public class IUserDaoImp implements IUserDao{
private DBConnection dbc = null;
private Connection conn = null;
private static String username;
private static String password;
static Scanner input = new Scanner(System.in);
public IUserDaoImp() {
this.dbc = new DBConnection();
this.conn = dbc.getConnection();
}
//插入学生信息!!!
public boolean doInsert(User user) {
boolean flag = false;
PreparedStatement pstmt = null;
String sql = "insert into Student(Student_id,Student_name,Student_phone,Student_department,Borrow_id) values(?,?,?,?,?)";
try {
pstmt = conn.prepareStatement(sql); //模糊查询
pstmt.setInt(1, user.getId());
pstmt.setString(2, user.getName());
pstmt.setString(3, user.getPhone());
pstmt.setString(4,user.getDepartment());
pstmt.setString(5, user.getB_id());
if (pstmt.executeUpdate() > 0) {
flag = true;
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return flag;
}
//查找全部图书信息!!!
public boolean doSelectAllBook(){
Statement stmt =null;
String sql="select * from Book";
try{
stmt=conn.createStatement();
//执行查询数据库的SQL语句 ,返回一个结果集(ResultSet)对象。
ResultSet Book = stmt.executeQuery(sql);
System.out.println("图书编号"+" 书名"+" 作者"+" 出版社");
while(Book.next()){//如果对象中有数据,就会循环打印出来
System.out.printf("%-10s",Book.getString("Book_id"));
System.out.printf("%-30s",Book.getString("Book_name"));
System.out.printf("%-20s",Book.getString("Book_author"));
System.out.printf("%-20s\n",Book.getString("Book_publisher"));
}
}catch (SQLException e) {
e.printStackTrace();
} finally {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return true;
}
//查找全部学生信息!!!
public boolean doSelectAllStudent(){
Statement stmt =null;
String sql="select * from Student";
try{
stmt=conn.createStatement();
ResultSet Student=stmt.executeQuery(sql);
while(Student.next()){
System.out.println("学号:"+Student.getString("Student_id")+"\t"+"姓名:"+Student.getString("Student_name")+"\t"+"联系方式:"+Student.getString("Student_phone")+"\t"+"所在院系:"+Student.getString("Student_department")+"\t"+ "借书证号:"+Student.getString("Borrow_id"));
}
}catch(SQLException e){
e.printStackTrace();
}finally{
try{
stmt.close();
}catch(SQLException e){
e.printStackTrace();
}
}
return true;
}
//查找全部借阅信息!!!
public boolean doSelectAllBorrow(){
Statement stmt=null;
String sql="select *from Borrow";
try{
stmt=conn.createStatement();
ResultSet Borrow=stmt.executeQuery(sql);
while(Borrow.next()){
System.out.println("借书证号:"+Borrow.getString("Borrow_id")+"\t"+"学号:"+Borrow.getString("Student_id")+"\t"+"图书编号:"+Borrow.getString("Book_id")+"\t"+"借书日期:"+Borrow.getString("Borrow_date")+"\t"+"还书日期:"+Borrow.getString("Return_date")+"\t"+"是否续借:"+Borrow.getString("Judge"));
}
}catch(SQLException e){
e.printStackTrace();
}finally{
try{
stmt.close();
}catch(SQLException e){
e.printStackTrace();
}
}
return true;
}
//按学号修改学生信息!!!
public boolean doUpdate(int id,User user){
boolean flag=false;
PreparedStatement pstmt = null;
String sql="UPDATE Student SET Student_name=?,Student_phone=?,Student_department=?,Borrow_id=? WHERE Student_id=?";
try{
pstmt=conn.prepareStatement(sql);
pstmt.setString(1, user.getName());
pstmt.setString(2, user.getPhone());
pstmt.setString(3, user.getDepartment());
pstmt.setString(4, user.getB_id());
pstmt.setInt(5, user.getId());
if (pstmt.executeUpdate() > 0) {
flag = true;
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return flag;
}
//按学号删除学生信息!!!
public boolean doDelete(int id){
boolean flag=false;
PreparedStatement pstmt=null;
String sql="DELETE FROM Student WHERE Student_id=?";
try{
pstmt=conn.prepareStatement(sql);
pstmt.setInt(1, id); //接收id传入sql语句
if (pstmt.executeUpdate() > 0) {// 至少已经更新了一行
flag = true;
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return flag;
}
//按图书编号查找图书信息!!!
public boolean doFindBook(int id){
PreparedStatement pstmt=null;
String sql="SELECT Book_id,Book_name,Book_author,Book_publisher FROM Book WHERE Book_id=?";
try{
pstmt=conn.prepareStatement(sql);
pstmt.setInt(1, id);
ResultSet rs=pstmt.executeQuery();
while(rs.next()){
System.out.println("图书编号:"+rs.getInt(1)+"\t"+"书名:"+rs.getString(2)+"\t"+"图书作者:"+rs.getString(3)+"\t"+"出版社:"+rs.getString(4));
}
}catch (SQLException e) {
e.printStackTrace();
} finally {
try {
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return true;
}
public boolean doInsert(Borrow borrow,DateConvert dateconvert){
boolean flag=false;
PreparedStatement pstmt=null;
String sql="INSERT INTO Borrow(Borrow_id,Student_id,Book_id,Borrow_date,Return_date,Judge) VALUES(?,?,?,?,?,?)";
try{
pstmt=conn.prepareStatement(sql);
pstmt.setInt(1, borrow.getInt1());
pstmt.setInt(2, borrow.getInt2());
pstmt.setInt(3, borrow.getInt3());
pstmt.setString(4, borrow.getDate1());
pstmt.setString(5, borrow.getDate2());
pstmt.setString(6, borrow.getString());
if (pstmt.executeUpdate() > 0) {
flag = true;
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return flag;
}
public boolean doDelete2(int id){
boolean flag=false;
PreparedStatement pstmt=null;
String sql="DELETE FROM Borrow WHERE Borrow_id=?";
try{
pstmt=conn.prepareStatement(sql);
pstmt.setInt(1, id); //接收id传入sql语句
if (pstmt.executeUpdate() > 0) {// 至少已经更新了一行
flag = true;
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return flag;
}
public void zhuce() throws SQLException{
System.out.println("请输入用户名:");
username=input.next();
System.out.println("请输入登录密码:");
String p1=input.next();
System.out.println("请再次输入密码:");
String p2=input.next();
if(p1.equals(p2)){
password=p1;
String sql="INSERT INTO Users(Username,Password) VALUES(?,?)";
PreparedStatement ptmt=conn.prepareStatement(sql);
ptmt.setString(1, username);
ptmt.setString(2, password);
ptmt.execute();
System.out.println("注册成功!");
}else{
System.out.println("你输入的两次密码不一致,请重新注册!");
zhuce();
}
}
public void denglu()throws SQLException{
System.out.println("用户名:");
String username=input.next();
System.out.println("密码:");
String password=input.next();
String sql="SELECT Username,Password FROM Users WHERE Username=? AND Password=?";
PreparedStatement ptmt=conn.prepareStatement(sql);
ptmt.setString(1, username);
ptmt.setString(2, password);
ResultSet rs=ptmt.executeQuery();
if(rs.next()){
System.out.println("登录成功!");
}else{
System.out.println("账号或密码不正确,请重新登录!\n");
denglu();
}
}
}
格式化日期
package util;
import java.text.SimpleDateFormat;//SimpleDateFormat 使得可以选择任何用户定义的日期-时间格式的模式
import java.util.Calendar;
import java.util.Date;
public class DateConvert {
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
public String nowDay(){
Calendar c = Calendar.getInstance();
c.setTime(new Date());
Date d=c.getTime();
String day=format.format(d);
return day;
}
public String nextDay(){
Calendar c = Calendar.getInstance();
c.setTime(new Date());
c.add(Calendar.MONTH, +1);
Date d = c.getTime();
String month = format.format(d);
return month;
}
}
InputData类
package util;
import java.io.*;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
public class InputData {
private BufferedReader buf=null;
public InputData(){
this.buf=new BufferedReader(new InputStreamReader(System.in));
}
public String getString(String info){
String temp=null;
System.out.print(info);
try{
temp=this.buf.readLine();
}catch (IOException e) {
e.printStackTrace();
}
return temp;
}
public int getInt(String info, String err) {
int temp = 0;
String str = null;
boolean flag = true;
while (flag) {
str = this.getString(info);
if (str.matches("^\\d+$")) {
temp = Integer.parseInt(str);//将str转化为int
flag = false;
} else {
System.out.println(err);
}
}
return temp;
}
}
JavaBean
package vo;
public class User {
private int student_id;
private String student_name;
private String student_phone;
private String student_department;
private String borrow_id;
public User(){}
public User( int id,String name,String phone,String department,String b_id){
this.student_id=id;
this.student_name=name;
this.student_phone=phone;
this.student_department=department;
this.borrow_id=b_id;
}
public User(int id){
this.student_id=id;
}
public void setId(int id){
this.student_id=id;
}
public int getId(){
return student_id;
}
public void setName(String name){
this.student_name=name;
}
public String getName(){
return student_name;
}
public void setPhone(String phone){
this.student_phone=phone;
}
public String getPhone(){
return student_phone;
}
public void setDepartment(String department){
this.student_department=department;
}
public String getDepartment(){
return student_department;
}
public void setB_id(String b_id){
this.borrow_id=b_id;
}
public String getB_id(){
return borrow_id;
}
public String toString(){
return "学号"+this.getId()+"姓名"+this.getName()+"联系方式"+this.student_phone+"院
系"+this.student_department+"借书证号"+this.borrow_id;
}
}
package vo;
public class Borrow {
private int borrow_id;
private int student_id;
private int book_id;
private String borrow_date;
private String return_date;
private String judge;
public Borrow(int borrow_id,int student_id,int book_id,String borrow_date,String return_date,String judge){
this.borrow_id=borrow_id;
this.student_id=student_id;
this.book_id=book_id;
this.borrow_date=borrow_date;
this.return_date=return_date;
this.judge=judge;
}
public void setInt1(int borrow_id){
this.borrow_id=borrow_id;
}
public int getInt1(){
return borrow_id;
}
public void setInt2(int student_id){
this.student_id=student_id;
}
public int getInt2(){
return student_id;
}
public void setInt3(int book_id){
this.book_id=book_id;
}
public int getInt3(){
return book_id;
}
public void setDate1(String borrow_date){
this.borrow_date=borrow_date;
}
public String getDate1(){
return borrow_date;
}
public void setDate2(String return_date){
this.return_date=return_date;
}
public String getDate2(){
return return_date;
}
public void setString(String judge){
this.judge=judge;
}
public String getString(){
return judge;
}
}
Menu类
package view;
import java.util.Scanner;
import java.sql.SQLException;
import java.text.ParseException;
import dao.IUserDaoImp;
import util.InputData;
import vo.User;
import vo.Borrow;
import util.DateConvert;
public class Menu {
IUserDaoImp iu = new IUserDaoImp();
InputData input = new InputData();
DateConvert dc=new DateConvert();
static String MANAGER="hyc";
static String KEY="123456";
public Menu() throws ParseException, SQLException{
while(true){
this.show();
}
}
public void show() throws ParseException, SQLException{
@SuppressWarnings("resource") //不显示使用了不赞成使用的类或方法时的警告
Scanner iput=new Scanner(System.in);
System.out.println("-----------------------------欢迎使用图书管理系统------------------------");
System.out.println("1.用户登录");
System.out.println("2.用户注册");
System.out.println("3.管理员登录");
int k=iput.nextInt();
switch(k){
case 1:
iu.denglu();
System.out.println("*************************************************************************");
System.out.println("| 1.读者注册 |");
System.out.println("| 2.图书信息 |");
System.out.println("| 3.借书 |");
System.out.println("| 4.还书 |");
System.out.println("| 0.退出系统 |");
System.out.println("*************************************************************************");
System.out.println("请选择:");
int q=iput.nextInt();
User user=null;
Borrow borrow=null;
DateConvert dateconvert=null;
switch(q){
case 1:
int id=input.getInt("请输入学号:", "格式不对,请重新输入!");
String name=input.getString("请输入人员姓名:");
String phone=input.getString("请输入联系方式:");
String department=input.getString("请输入院系:");
String b_id=input.getString("请输入读书证号:");
user=new User(id,name,phone,department,b_id);
if (iu.doInsert(user)) {
System.out.println("添加成功!");
} else {
System.out.println("添加失败!");
}break;
case 2:
iu.doSelectAllBook();break;
case 3:
int id5=input.getInt("请输入借书证号:", "格式不对,请重新输入!");
int id6=input.getInt("请输入学号:", "格式不对,请重新输入!");
int id7=input.getInt("请输入图书编号:", "格式不对,请重新输入!");
String dt1=dc.nowDay();
String dt2=dc.nextDay();
System.out.println("借书时间为:"+dt1);
System.out.println("还书时间为:"+dt2);
String judge="否";
borrow=new Borrow(id5,id6,id7,dt1,dt2,judge);
if (iu.doInsert(borrow,dateconvert)) {
System.out.println("借书成功!");
} else {
System.out.println("借书失败!");
}
break;
case 4:
int id8=input.getInt("请输入还书同学的借书证号:", "格式不对,请重新输入!");
if(iu.doDelete2(id8)){
System.out.println("还书成功!");
}else{
System.out.println("还书失败!");
}
}break;
case 0:
{ System.out.println("谢谢使用!");
System.exit(0);
}
break;
case 2:
iu.zhuce();break;
case 3:
System.out.println("请输入用户名:");
String manager=iput.next();
System.out.println("请输入密码:");
String key=iput.next();
if(manager.equals(MANAGER)&&key.equals(KEY)){
System.out.println("登入系统成功!");
System.out.println("*************************************************************************");
System.out.println("| 1.图书信息 |");
System.out.println("| 2.读者信息 |");
System.out.println("| 3.借阅信息 |");
System.out.println("| 4.修改读者信息 |");
System.out.println("| 5.删除读者信息 |");
System.out.println("| 6.查找图书信息 |");
System.out.println("| 0.退出系统 |");
System.out.println("*************************************************************************");
System.out.println("请输入一个数字:");
int p=iput.nextInt();
switch(p){
case 1:
iu.doSelectAllBook();break;
case 2:
iu.doSelectAllStudent();break;
case 3:
iu.doSelectAllBorrow();break;
case 4:
int id2=input.getInt("请输入学号:", "格式不对,请重新输入!");
String name2=input.getString("请修改人员姓名:");
String phone2=input.getString("请修改联系方式:");
String department2=input.getString("请修改院系:");
String b_id2=input.getString("请修改读书证号:");
user=new User(id2,name2,phone2,department2,b_id2);
if(iu.doUpdate(id2, user)){
System.out.println("修改成功!");
}else{
System.out.println("修改失败!");
}break;
case 5:
int id3=input.getInt("请输入要删除学生的学号:", "格式不对,请重新输入!");
if(iu.doDelete(id3)){
System.out.println("删除成功!");
}else{
System.out.println("删除失败!");
}break;
case 6:
int id4=input.getInt("请输入要查找的图书编号:", "格式不对,请重新输入!");
iu.doFindBook(id4);
break;
case 0:
System.out.println("谢谢使用!");
System.exit(0);
break;
default:
System.out.println("请选择正确的操作!");
}
break;
}
else{
System.out.println("账号或密码不正确!");
}
}
}
}
主方法
package driver;
import java.sql.SQLException;
import java.text.ParseException;
import view.Menu;
public class BookManager {
public static void main(String[] args) throws ParseException, SQLException {
new Menu();
}
}
数据库为SQL server2008
欢迎各位大神前来指导!!!!!!!!!!!!