dbsql.java

java写的源码

package DBlab;
import java.awt.BorderLayout;
import java.awt.Dimension;
import java.awt.Toolkit;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.sql.*;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.GregorianCalendar;
import java.util.Vector;
import java.util.Date;

import javax.swing.JButton;
import javax.swing.JFrame;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import javax.swing.table.AbstractTableModel;

public class DBSQL {
	public static long Diff(String date1)//返回与当天的日期差,因为是超期才用这个，所以今天肯定比return date大
	{
		long diff = 0;
		SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
		Date d1=null;
		try{
			d1 = (Date) formatter.parse(date1);
			Date d2=new Date();
			diff = d2.getTime() - d1.getTime();
		}catch(ParseException e){}
		return diff/(1000*60*60*24);//转化为天		
	}
	public static boolean loginin (String uid,String password){
		boolean search = false;
		String sql = "select LOGIN from USER where USERID='";
		sql = sql + uid + "' and LOGIN='" + password + "'";
		try{ 
			Connection conn = JDBConnection.getConnection();
			Statement stmt = conn.createStatement();			
			ResultSet rs = stmt.executeQuery( sql );
			if (rs.next())
				search = true;
			rs.close();
			stmt.close();
			conn.close();
		}catch(SQLException e){
			e.printStackTrace();
		}catch(Exception e){
			e.printStackTrace();
		}
		return search;
	}
	public static String userinfo (String uid , String info){
		String result = "";
		try{ 
			Connection conn = JDBConnection.getConnection();
			Statement stmt = conn.createStatement();			
			ResultSet rs = stmt.executeQuery("SELECT * FROM USER WHERE USERID='"+uid+"'");
			if(rs.next())
			{
				result = rs.getString(info);
			}
			else
				result ="NONE";			
			rs.close();
			stmt.close();
			conn.close();
		}catch(SQLException e){
			e.printStackTrace();
		}catch(Exception e){
			e.printStackTrace();
		}
		return result;
	}
	public static int userborrowed (String uid){
		int amount = 0;
		AbstractTableModel tm;
		final JFrame searchresult = new JFrame ("查询结果");
		searchresult.setSize (400,300);
		searchresult.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
		Dimension screenSize = Toolkit.getDefaultToolkit().getScreenSize();
		int screenWidth = screenSize.width;
		int screenHeight = screenSize.height;
		int x = (screenWidth - searchresult.getWidth())/2;
		int y = (screenHeight - searchresult.getHeight())/2;
		searchresult.setLocation (x,y);
		searchresult.setVisible (true);

		final Vector vect = new Vector();
		final String title[]={"ISBN","书名","借书日期","应还日期"};
		tm=new AbstractTableModel(){
			public int getColumnCount(){
				return title.length;}//取得表格列数
			public int getRowCount(){
				return vect.size();}//取得表格行数
			public Object getValueAt(int row,int column){
				if(!vect.isEmpty())
					return ((Vector)vect.elementAt(row)).elementAt(column);
				else
					return null;}//取得单元格中的属性值
			public String getColumnName(int column){
				return title[column];}//设置表格列名
			public void setValueAt(Object value,int row,int column){}	//数据模型不可编辑，该方法设置为空
			public Class getColumnClass(int c){
				return getValueAt(0,c).getClass();
			}//取得列所属对象类
			public boolean isCellEditable(int row,int column){
				return false;
			}//设置单元格不可编辑，为缺省实现
		};
		JTable jtable=new JTable(tm);//生成自己的数据模型
		jtable.setAutoResizeMode(JTable.AUTO_RESIZE_ALL_COLUMNS);//设置表格调整尺寸模式
		jtable.setCellSelectionEnabled(false);//设置单元格选择方式
		jtable.setShowVerticalLines(true);//设置是否显示单元格间的分割线
		jtable.setShowHorizontalLines(true);
		JScrollPane pane = new JScrollPane(jtable);
		searchresult.add(pane, BorderLayout.CENTER);

		JButton jtbOK = new JButton("确定");
		searchresult.add(jtbOK,BorderLayout.SOUTH);

		jtbOK.addActionListener(new ActionListener(){
			public void actionPerformed (ActionEvent e){
				searchresult.dispose();
			}
		});

		vect.removeAllElements();//初始化向量对象
		tm.fireTableStructureChanged();//更新表格内容
		try{ 
			Connection conn = JDBConnection.getConnection();
			Statement stmt = conn.createStatement();			
			ResultSet rs = stmt.executeQuery("SELECT ISBN, BOOKNAME, BORROWDATE, RETURNDATE FROM BORROWED WHERE USERID='"+uid+"' AND STATE = 'B'");
			while(rs.next()){
				amount++;
				Vector rec_vector=new Vector();		//从结果集中取数据放入向量rec_vector中
				rec_vector.addElement(rs.getString(1)); 				
				rec_vector.addElement(rs.getString(2));
				rec_vector.addElement(rs.getDate(3));
				rec_vector.addElement(rs.getDate(4));
				vect.addElement(rec_vector);//向量rec_vector加入向量vect中
			}
			tm.fireTableStructureChanged();
			rs.close();
			stmt.close();
			conn.close();
		}catch(SQLException e){
			e.printStackTrace();
		}catch(Exception e){
			e.printStackTrace();
		}
		return amount;
	}
	public static int bookinfo (String keyword , int style ){	//style:0-书名关键字 ；1-作者；
		int amount = 0;
		final JFrame searchresult = new JFrame ("查询结果");
		searchresult.setSize (400,300);
		searchresult.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
		Dimension screenSize = Toolkit.getDefaultToolkit().getScreenSize();
		int screenWidth = screenSize.width;
		int screenHeight = screenSize.height;
		int x = (screenWidth - searchresult.getWidth())/2;
		int y = (screenHeight - searchresult.getHeight())/2;
		searchresult.setLocation (x,y);
		searchresult.setVisible (true);

		AbstractTableModel tm;
		final Vector vect = new Vector();
		final String title[]={"ISBN","书名","作者","出版社","在库数量","位置"};
		tm=new AbstractTableModel(){
			public int getColumnCount(){
				return title.length;}//取得表格列数
			public int getRowCount(){
				return vect.size();}//取得表格行数
			public Object getValueAt(int row,int column){
				if(!vect.isEmpty())
					return ((Vector)vect.elementAt(row)).elementAt(column);
				else
					return null;}//取得单元格中的属性值
			public String getColumnName(int column){
				return title[column];}//设置表格列名
			public void setValueAt(Object value,int row,int column){}	//数据模型不可编辑，该方法设置为空
			public Class getColumnClass(int c){
				return getValueAt(0,c).getClass();
			}//取得列所属对象类
			public boolean isCellEditable(int row,int column){
				return false;
			}//设置单元格不可编辑，为缺省实现
		};
		JTable jtable=new JTable(tm);//生成自己的数据模型
		jtable.setAutoResizeMode(JTable.AUTO_RESIZE_ALL_COLUMNS);//设置表格调整尺寸模式
		jtable.setCellSelectionEnabled(false);//设置单元格选择方式
		jtable.setShowVerticalLines(true);//设置是否显示单元格间的分割线
		jtable.setShowHorizontalLines(true);
		JScrollPane pane = new JScrollPane(jtable);
		searchresult.add(pane, BorderLayout.CENTER);

		JButton jtbOK = new JButton("确定");
		searchresult.add(jtbOK,BorderLayout.SOUTH);
		jtbOK.addActionListener(new ActionListener(){
			public void actionPerformed (ActionEvent e){
				searchresult.dispose();
			}
		});


		vect.removeAllElements();//初始化向量对象
		tm.fireTableStructureChanged();//更新表格内容

		try{ 
			Connection conn = JDBConnection.getConnection();
			Statement stmt = conn.createStatement();
			String str = (style == 0) ? "BOOKNAME" : "AUTHOR" ;
			String str1 = "SELECT ISBN, BOOKNAME, AUTHOR, PRESS, REMAIN, PLACE FROM BOOKINFO WHERE " + str + " LIKE '%" + keyword + "%'";
			ResultSet rs = stmt.executeQuery(str1);
			while(rs.next()){
				Vector rec_vector=new Vector();		//从结果集中取数据放入向量rec_vector中
				rec_vector.addElement(rs.getString(1)); 				
				rec_vector.addElement(rs.getString(2));
				rec_vector.addElement(rs.getString(3));
				rec_vector.addElement(rs.getString(4));
				rec_vector.addElement(rs.getInt(5));
				rec_vector.addElement(rs.getString(6));
				vect.addElement(rec_vector);//向量rec_vector加入向量vect中
			}
			tm.fireTableStructureChanged();
			rs.close();
			stmt.close();
			conn.close();
		}catch(SQLException e){
			e.printStackTrace();
		}catch(Exception e){
			e.printStackTrace();
		}
		return amount ;
	}
	public static boolean borrowbook (String uid , String ISBN){
		boolean result =false;
		try{ 
			Connection conn = JDBConnection.getConnection();
			Statement stmt1 = conn.createStatement();
			int amount = 0;//用来表示读者已经借过的书的数量

			//查找作者已经借过的书数量
			ResultSet info =stmt1.executeQuery("SELECT AMOUNT FROM USER WHERE USERID='"+uid+"'");
			if(info.next())
			{
				amount = info.getInt(1);
			}
			//查找要借的书的余量
			int remain = 0;//书的余量
			String bookname = "";
			Statement stmt2 = conn.createStatement();
			ResultSet rs = stmt2.executeQuery("SELECT REMAIN, BOOKNAME FROM BOOKINFO WHERE ISBN ='"+ISBN+"'");
			if(rs.next())
			{
				remain = rs.getInt(1);
				bookname = rs.getString(2);
			}

			if (amount < 10 && remain > 0)//满足条件，可以借书
			{
				Statement stmt3 = conn.createStatement();			

				GregorianCalendar getTD=new GregorianCalendar();
				Date borrowdate = getTD.getTime();
				SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
				String borrowdatestr=sdf.format(borrowdate);
				getTD.add(Calendar.DATE, 30);
				Date returndate = getTD.getTime();
				String returndatestr=sdf.format(returndate);

				String sqlText = "INSERT INTO BORROWED values('"+ISBN+"','"+uid+"','"+bookname+"','"+borrowdatestr+"','"+returndatestr+"',0,'B',0)";//添加values至BORROWED中
				System.out.println(sqlText);
				int nRow1 = stmt3.executeUpdate(sqlText);//更新BORROWED中的信息
				String sqlText2 = "UPDATE USER SET AMOUNT=AMOUNT+1 WHERE USERID='"+uid+"'";//用户借书量加1
				String sqlText3 = "UPDATE BOOKINFO SET REMAIN=REMAIN-1,TIMES=TIMES+1 WHERE ISBN='"+ISBN+"'";				//图书信息也要更改，余量减1，被借次数加1
				int nRow2= stmt3.executeUpdate(sqlText2);
				int nRow3= stmt3.executeUpdate(sqlText3);				
				result =true;//操作成功 
				stmt3.close();			
			}
			info.close();
			rs.close();
			stmt1.close();
			stmt2.close();
			conn.close();			
		}catch(SQLException e){
			e.printStackTrace();
		}catch(Exception e){
			e.printStackTrace();
		}
		return result;	
	}
	public static int returnbook (String uid , String ISBN){
		int punishment = 0;//表示返回值，意义为罚款数额
		try{
			Connection conn = JDBConnection.getConnection();
			Statement stmt1 = conn.createStatement();
			Date returndate = null ;//用来表示读者应该还书的日期
			//查找return date
			String sqlText1="SELECT RETURNDATE FROM BORROWED WHERE USERID='"+uid+"'AND ISBN='"+ISBN+"' AND STATE='B'";
			ResultSet info =stmt1.executeQuery(sqlText1);