mysql.java

一个用java开发的具有搜索功能的图书管理系统

package librarysystem;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Mysql {
	
	private Connection conn;
	private Statement statement;
	private ResultSet resultSet;
	private String buildDatabase;
	private PreparedStatement preStatement;
	public Mysql(String database,String username,String password)
	{
		buildDatabase="drop database if exists library;" +
		
					"create database if not exists library;"+                                		//创建数据库
			
					"use library;"+
					
					"create table book(bookID bigint auto_increment unsigned primary key," + 		//创建book表
					"bookName varchar(50) not null," +
					"author varchar(20) not null," +
					"sort varchar(30) not null," +
					"publishDate date not null," +
					"totality smallint default 0 check(totality>=0)"+
					"stocks  smallint default 0 check(stocks>=0)," +
					"cover longblog," +
					"remark mediumtext;)" +
					
					"create table student(studentID bigint unsigned primary key," +					//创建student表
					"password varchar(50) check(len(password)>6 and len(password)<50)," +
					"borrowNum tinyint unsigned default 0," +
					"surplusNum tinyint unsigned not null check(surplusNum>0)," +
					"lastestDate datetime);" +
					
					"create table administrator(administratorName varchar(50) primary key," +		//创建administrator表
					"password varchar(50) check(len(password)>6 and len(password)<50));" +
					
					"create table record(recordID bigint auto_increment unsigned primary key," +	//创建record表
					"bookID bigint unsigned not null," +
					"studentID bigint unsigned not null," +
					"borrowDate date not null," +
					"isReturned enum('no','yes') not null," +
					"foreign key(bookID)    references book(bookID)," +
					"foreign key(studentID) references student(studentID));" +
					
					"create table bulletin(bulletinID bigint unsigned primary key," +				//创建bulletin表
					"title varchar(50) not null," +
					"content longtext not null," +
					"promulgateDate datetime not null," +
					"promulgator varchar(50) not null);" +
					
					"insert into administrator" +													//设置默认管理员
					"values('administrator','administrator');";
		try
		{
			Class.forName("com.mysql.jdbc.Driver");                                        //注册MySQL驱动
			//conn = DriverManager.getConnection("jdbc:mysql://localhost/test","root","");
			conn = DriverManager.getConnection(database,username,password);                //创建数据库连接
			statement = conn.createStatement();                                             //创建语句对象
			preStatement=conn.prepareStatement(buildDatabase);								//预编译执行语句
			preStatement.execute();															//创建数据库
		}
		catch(Exception ex)
		{
			ex.printStackTrace();
		}
	}
	public ResultSet getResultSet(String SQLStatement) {
		try 
		{
			resultSet = statement.executeQuery(SQLStatement); 								//执行查询语句
			return resultSet;
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
			return null;
		}
		
	}
	public boolean executeStatement(String SQLStatement){
		try
		{
			statement.executeUpdate(SQLStatement);											//执行删改语句
			return true;
		}
		catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
		

	}
	public boolean rebuildDatabase(){
		try 
		{
			preStatement.execute();
			return true;
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
			return false;
		}
	}
	public void closeMySQL(){
		try {
			resultSet.close();
			conn.close();
			statement.close();
		} catch (SQLException e) {
			// TODO 自动生成 catch 块
			e.printStackTrace();
		}
	}
	
	

}