mysql.java

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

package library;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Date;

public class MySQL {

	private static final int TABLE_NUM=6;
	private Connection conn;
	private Statement statement;
	private ResultSet resultSet;
	private String[] buildDatabase=new String[TABLE_NUM];
	private PreparedStatement[] preStatement=new PreparedStatement[TABLE_NUM];
	public MySQL(String database,String username,String password) throws ClassNotFoundException, SQLException
	{
		Date dTime=new Date(new Date().getTime());
		SimpleDateFormat fymd=new SimpleDateFormat("yyyy-MM-dd hh:mm:ss"); 
		String dateTime=fymd.format(dTime);
		System.out.println(dateTime);
		buildDatabase[0]=
					"create table if not exists book(bookID bigint unsigned auto_increment primary key," + 		//创建book表
					"bookName varchar(50) not null," +
					"author varchar(20) not null," +
					"sort varchar(30) not null," +
					"price float unsigned not null," +
					"description text," +
					"publishDate date not null," +
					"totality smallint not null check(totality>=0),"+
					"stocks  smallint default 0 check(stocks<=totality and stocks>=0)," +
					"cover mediumblob," +
					"remark mediumtext);" ;
					
		buildDatabase[1]="create table if not exists student(studentID bigint unsigned primary key," +					//创建student表
					"password varchar(50) check(len(password)>5 and len(password)<50)," +
					"borrowNum tinyint unsigned default 0," +
					"surplusNum tinyint unsigned not null check(surplusNum>0)," +
					"loginDate datetime," +
					"lastestDate datetime);" ;
					
		buildDatabase[2]="create table if not exists administrator(administratorName varchar(50) primary key," +	//创建administrator表
					"password varchar(50) check(len(password) between 5 and 50)," +
					"loginDate datetime," +
					"lastestDate datetime);" ;
					
		buildDatabase[3]="create table if not exists record(recordID bigint unsigned auto_increment 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));" ;
					
		buildDatabase[4]="create table if not exists bulletin(bulletinID bigint unsigned auto_increment primary key," +				//创建bulletin表
					"title varchar(50) not null," +
					"content longtext not null," +
					"promulgateDate date not null," +
					"promulgator varchar(50) not null);" ;
					
		buildDatabase[5]="insert ignore into administrator " +													//设置默认管理员
					"values('admin','admin','"+dateTime+"','"+dateTime+"');";
		/*buildDatabase[6]="insert into bulletin(title,content,promulgateDate,promulgator)" +
					" values('关于阅览室合并的通知'," +
					"' 根据图书馆工作安排，中文报刊阅览室与第三阅览室将于5月21日（下周一）开始合并，" +
					"从下周一起需查阅中文报刊的读者请从第三阅览室入口进出。由此给您带来的不便，敬请谅解！'," +
					"'"+sDate+"'"+",'洪智标');";
		String description="本书第1版、第2版和第3版分别于1983年、1991年、2000年出版。" +
				"第3版被列为“面向21世纪课程教材”，第4版是普通高等教育“十五”国家级规划教材，相应课程于2005年被评为国家精品课程。. "+
				"本书系统全面地阐述数据库系统的基础理论、基本技术和基本方法。全书分为4篇17章。基础篇包括绪论、关系数据库、" +
				"关系数据库标准语言SQL、数据库安全性和数据库完整性，共5章；设计与应用开发篇包括关系数据理论、数据库设计和数据库编程，" +
				"共3章；系统篇包括关系查询处理和查询优化、数据库恢复技术、并发控制和数据库管理系统，共4章；" +
				"新技术篇包括数据库技术新发展、分布式数据库系统、对象关系数据库系统、XML数据库和数据仓库与联机分析处理技术，共5章。.." ;

		buildDatabase[7]="insert into book(bookName,author,sort,price,description,publishDate,totality,stocks) " +
					"values('数据库系统概论','王珊,萨师煊','计算机技术',33.8,'"+description+"','2006-5-4',5,5);";*/
			Class.forName("com.mysql.jdbc.Driver");                                        //注册MySQL驱动
			conn = DriverManager.getConnection(database,username,password);                //创建数据库连接
			//conn.setAutoCommit(false);														//建立事务机制
			statement = conn.createStatement();                                             //创建语句对象
			buildDatabase();
	}
	public ResultSet getResultSet(String SQLStatement) throws SQLException {
		Statement stat=conn.createStatement();
		resultSet = stat.executeQuery(SQLStatement); 								//执行查询语句
		return resultSet;
		
	}
	synchronized public boolean executeStatement(String SQLStatement) throws SQLException{
		statement.executeUpdate(SQLStatement);											//执行删改语句
		return true;
		

	}
	/*public boolean commit(){
		try {
			conn.commit();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}*/
	synchronized private boolean buildDatabase() throws SQLException{          		
		statement.execute("create database if not exists library;");			//创建数据库
		statement.execute("use library;");
		//System.out.println("create database sucessfully");
		/*if(isInitialized==false)
		{
			for(int i=0;i<TABLE_NUM;i++)
			{
				preStatement[i]=conn.prepareStatement(buildDatabase[i]);								//预编译执行语句
			}
			isInitialized=true;
		}*/
		for(int i=0;i<TABLE_NUM;i++)
		{
			preStatement[i]=conn.prepareStatement(buildDatabase[i]);								//预编译执行语句
			preStatement[i].execute();															//创建数据库
			
		}
		return true;
	}
	synchronized public boolean rebuildDatabase() throws SQLException{
		statement.execute("drop database if exists library;");
		return buildDatabase();

	}
	public void closeMySQL() throws SQLException{
		if(resultSet!=null)
		{
			resultSet.close();
		}
		if(statement!=null)
		{
			statement.close();
		}
		if(conn!=null)
		{
			conn.close();
		}
	}
	
	

}