📄 dbsql.java
字号:
if(info.next())
{
returndate = info.getDate(1);
}
else
return -1;
SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
String rdstring=sdf.format(returndate);
System.out.println(returndate);
long day=Diff(rdstring);
punishment = (int)(2 * day);//每天2元
if (punishment < 0){
punishment = 0 ;
}
Date today = new Date();
String todaystr=sdf.format(today);
//返回书本,更新表
//将状态记为已还
String sql1 = "UPDATE BORROWED SET STATE='R',RETURNDATE = '"+todaystr+"',PUNISH = "+Integer.toString(punishment)+" WHERE USERID='"+uid+"'AND ISBN='"+ISBN+"'";
System.out.println(sql1);
//更新读者里面的信息，将AMOUNT减1
String sql2 = "UPDATE USER SET AMOUNT = AMOUNT-1 WHERE USERID='"+uid+"'";
//更新书本情况，REMAIN加1
String sql3 = "UPDATE BOOKINFO SET REMAIN = REMAIN+1 WHERE ISBN='"+ISBN+"'";
Statement stmt2 = conn.createStatement();
Statement stmt3 = conn.createStatement();
Statement stmt4 = conn.createStatement();
int info1 = stmt2.executeUpdate(sql1);
int info2 = stmt3.executeUpdate(sql2);
int info3 = stmt4.executeUpdate(sql3);
stmt1.close();
stmt2.close();
stmt3.close();
stmt4.close();
conn.close();
}catch(SQLException e){
e.printStackTrace();
}catch(Exception e){
e.printStackTrace();
}
return punishment;
}
public static boolean renew (String uid , String ISBN){
boolean result=false;
try{ //两种情况不能续借：1，已经续借过;2，超期未还
Connection conn = JDBConnection.getConnection();
Statement stmt1 = conn.createStatement();
Statement stmt2 = conn.createStatement();
Statement stmt3 = conn.createStatement();
//String state ="";//书的状态，是否已经续借
Date returndate = null;//用来表示读者应该还书的日期
int renew = 0;//续借次数
String sqlText1 = "SELECT RENEW FROM BORROWED WHERE USERID='"+uid+"'AND ISBN='"+ISBN+"'";
ResultSet info1 =stmt2.executeQuery(sqlText1);
if(info1.next())
{
renew = info1.getInt(1);
info1.close();
}
if(renew == 1)
{
result = false;
}
else
{//查找return date
String sqlText2 = "SELECT RETURNDATE FROM BORROWED WHERE USERID='"+uid+"'AND ISBN='"+ISBN+"'";
ResultSet info2 =stmt2.executeQuery(sqlText2);
if(info2.next())
{
returndate = info2.getDate(1);
info2.close();
}
GregorianCalendar now = new GregorianCalendar();
Date newday = (Date) now.getTime();
if (returndate.after(newday))//未过期,可以续借
{//判断不同的权限
String sql = "SELECT IDENTITY FROM USER WHERE USERID='"+uid+"'";
Statement stmt4 = conn.createStatement();
ResultSet info3 = stmt4.executeQuery(sql);
String identity = "";
if(info3.next())
{
identity = info3.getString(1);
}
if(identity.compareToIgnoreCase("t")==0 )//是老师，可以续借60天
{
now.add(Calendar.DATE,60);
newday = (Date) now.getTime();
}
else
{
now.add(Calendar.DATE, 30);
newday = (Date) now.getTime();
}
System.out.println(newday);
SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
String newdaystring=sdf.format(newday);
String sqlText3 = "UPDATE BORROWED SET RENEW = 1, RETURNDATE='" +newdaystring+"'"+
"WHERE USERID='"+uid+"' AND ISBN ='"+ISBN+"' AND STATE='B'"; //更新BORROWED表中的信息，renew＝1 归还日期增加30天
int info =stmt2.executeUpdate(sqlText3);
result= true;
}
}
stmt1.close();
stmt2.close();
stmt3.close();
conn.close();
}catch(SQLException e){
e.printStackTrace();
}catch(Exception e){
e.printStackTrace();
}
return result;
}
public static boolean allbookinfo (){
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.setToolTipText("显示全部查询结果");//设置帮助提示
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, AUTHOR, PRESS, AMOUNT, AMOUNT-REMAIN-MISSING, REMAIN, PLACE, MISSING, TIMES from bookinfo order by TIMES DESC");
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.getInt(6));
rec_vector.addElement(rs.getInt(7));
rec_vector.addElement(rs.getString(8));
rec_vector.addElement(rs.getInt(9));
rec_vector.addElement(rs.getInt(10));
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 true;
}
public static int overtime (int N ){
int count=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","用户ID","书名","借阅日期","应还日期","续借次数","罚款金额"};
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.setToolTipText("显示全部查询结果");//设置帮助提示
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(); //把今天推后N天
Calendar now = Calendar.getInstance();
now.add(Calendar.DATE,N);
Date newday = (Date) now.getTime();
String sql="select returndate from borrowed where state='B'";
ResultSet rs = stmt.executeQuery(sql);
while(rs.next())
{
Date returndate = rs.getDate(1);
SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
String rdstring=sdf.format(returndate);
if(returndate.before(newday))//超期
{
count++;
String sql1 = "select ISBN, USERID, BOOKNAME, BORROWDATE, RETURNDATE, RENEW, PUNISH from borrowed where returndate ='" + rdstring +"' and state = 'B'";
Statement stmt1 = conn.createStatement();
ResultSet rs1 = stmt1.executeQuery(sql1);
if (rs1.next()){
Vector rec_vector=new Vector(); //从结果集中取数据放入向量rec_vector中
rec_vector.addElement(rs1.getString(1));
rec_vector.addElement(rs1.getString(2));
rec_vector.addElement(rs1.getString(3));
rec_vector.addElement(rs1.getDate(4));
rec_vector.addElement(rs1.getDate(5));
rec_vector.addElement(rs1.getInt(6));
rec_vector.addElement(rs1.getInt(7));
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 count;
}
}
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -