1 packagetest2;2
3 import java.sql.*;4 importjava.text.SimpleDateFormat;5 importjava.util.Date;6 importjava.util.regex.Pattern;7 public classData {8
9 //连接数据库***********************************************************************
10 public Connection getConnection()//连接数据库
11 {12 try{13 Class.forName("com.mysql.cj.jdbc.Driver");14 //System.out.println("加载驱动成功");
15 }catch(ClassNotFoundException e)16 {17 e.printStackTrace();18 }19 String user="root";20 String password="123456";21 String url = "jdbc:mysql://localhost:3306/ztest01?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf-8&autoReconnect=true";22 Connection con=null;23 try{24 con=DriverManager.getConnection(url,user,password);25 //System.out.println("数据库连接成功");
26 }catch(SQLException e)27 {28 e.printStackTrace();29 }30 returncon;31 }32 //关闭方法**********************************************************************
33 public voidclose (Connection con)34 {35 try{36 if(con!=null)37 {38 con.close();39 }40 }catch(SQLException e)41 {42 e.printStackTrace();43 }44 }45 public voidclose (PreparedStatement preparedStatement)46 {47 try{48 if(preparedStatement!=null)49 {50 preparedStatement.close();51 }52 }catch(SQLException e)53 {54 e.printStackTrace();55 }56 }57 public voidclose(ResultSet resultSet)58 {59 try{60 if(resultSet!=null)61 {62 resultSet.close();63 }64 }catch(SQLException e)65 {66 e.printStackTrace();67 }68 }69 //数据库操作********************************************************************
70 public void addreader(String id,String name,String sex,String school,String password)//add reader
71 {72 Connection connection =getConnection();73 PreparedStatement preparedStatement=null;74 try{75 String sql = "insert into readers (id,name,sex,school,password) values (?,?,?,?,?)";76 preparedStatement=connection.prepareStatement(sql);77 preparedStatement.setString(1,id);78 preparedStatement.setString(2,name);79 preparedStatement.setString(3,sex);80 preparedStatement.setString(4,school);81 preparedStatement.setString(5,password);82 preparedStatement.executeUpdate();83 } catch(SQLException e) {84 e.printStackTrace();85 }finally{86 close(preparedStatement);87 close(connection);88 }89 }90 public void addbook(String id,String name,String author,String company,String number,String nowout)//add book
91 {92 Connection connection =getConnection();93 PreparedStatement preparedStatement=null;94 try{95 String sql = "insert into books (id,name,author,company,number,nowout) values (?,?,?,?,?,?)";96 preparedStatement=connection.prepareStatement(sql);97 preparedStatement.setString(1,id);98 preparedStatement.setString(2,name);99 preparedStatement.setString(3,author);100 preparedStatement.setString(4,company);101 preparedStatement.setString(5,number);102 preparedStatement.setString(6,nowout);103 preparedStatement.executeUpdate();104 } catch(SQLException e) {105 e.printStackTrace();106 }finally{107 close(preparedStatement);108 close(connection);109 }110 }111
112 public voidaddborrow(String readerid,String bookid,String outnow) {113 Connection connection =getConnection();114 PreparedStatement preparedStatement1=null;115 PreparedStatement preparedStatement2=null;116 try{117 String sql1 = "insert into borrows (readerid,bookid,borrowtime) values (?,?,?)";118 preparedStatement1=connection.prepareStatement(sql1);119 preparedStatement1.setString(1,readerid);120 preparedStatement1.setString(2,bookid);121
122 SimpleDateFormat sdf = new SimpleDateFormat( "yyyy-MM-dd HH:mm:ss");123 Date dadate = newDate();124 String nowDaDate =sdf.format(dadate);125 Timestamp daTs =Timestamp.valueOf(nowDaDate);126 preparedStatement1.setTimestamp(3,daTs);127
128 String sql2 = "update books set outnow=? where id=?";129 preparedStatement2=connection.prepareStatement(sql2);130 preparedStatement2.setString(1,outnow);131 preparedStatement2.setString(2,bookid);132
133 preparedStatement1.executeUpdate();134 preparedStatement2.executeUpdate();135 } catch(SQLException e) {136 e.printStackTrace();137 }finally{138 close(preparedStatement1);139 close(preparedStatement2);140 close(connection);141 }142 }143
144 public voidback(String readerid,String bookid,String outnow)145 {146 Connection connection =getConnection();147 PreparedStatement preparedStatement1=null;148 PreparedStatement preparedStatement2=null;149 try{150 String sql = "delete from borrows where bookid= ? and readerid = ?";151 preparedStatement1=connection.prepareStatement(sql);152 preparedStatement1.setString(1,bookid);153 preparedStatement1.setString(2,readerid);154
155 String sql2 = "update books set outnow=? where id=?";156 preparedStatement2=connection.prepareStatement(sql2);157 preparedStatement2.setString(1,outnow);158 preparedStatement2.setString(2,bookid);159
160 preparedStatement1.executeUpdate();161 preparedStatement2.executeUpdate();162 } catch(SQLException e) {163 e.printStackTrace();164 }finally{165 close(preparedStatement1);166 close(preparedStatement2);167 close(connection);168 }169 }170
171
172
173 //判断******************************************************************************
174 public boolean passwordIsRightStudents(String id,String password) { //judge password is right-Students
175 Connection connection =getConnection();176 PreparedStatement preparedStatement=null;177 ResultSet rs=null;178 try{179 String sql = "select * from readers";180 preparedStatement=connection.prepareStatement(sql);181 rs=preparedStatement.executeQuery();182 while(rs.next()){183 if( id.equals(rs.getObject(1))&&password.equals(rs.getObject(5)) )184 return true;185 }186 } catch(SQLException e) {187 e.printStackTrace();188 }finally{189 close(rs);190 close(preparedStatement);191 close(connection);192 }193 return false;194 }195
196
197 public boolean useridisIdRight(String num) {//judge userid
198 int k=0;199 if(num.length()==8) {200 for(int i=0;i<8;i++) {201 char c=num.charAt(i);202 if(c=='0'||c=='1'||c=='2'||c=='3'||c=='4'||c=='5'||c=='6'||c=='7'||c=='8'||c=='9')203 k++;204 }205 if(k==8)206 return true;207 else return false;208 }209 else return false;210 }211
212
213 public boolean studentexist(String id) { //have this student
214 Connection connection =getConnection();215 PreparedStatement preparedStatement=null;216 ResultSet rs=null;217 try{218 String sql = "select id from readers";219 preparedStatement=connection.prepareStatement(sql);220 rs=preparedStatement.executeQuery();221 while(rs.next()){222 if( id.equals(rs.getObject(1)) )223 return true;224 }225 } catch(SQLException e) {226 e.printStackTrace();227 }finally{228 close(rs);229 close(preparedStatement);230 close(connection);231 }232 return false;233 }234
235 public boolean bookexist(String id) { //have this book
236 Connection connection =getConnection();237 PreparedStatement preparedStatement=null;238 ResultSet rs=null;239 try{240 String sql = "select id from books";241 preparedStatement=connection.prepareStatement(sql);242 rs=preparedStatement.executeQuery();243 while(rs.next()){244 if( id.equals(rs.getObject(1)) )245 return true;246 }247 } catch(SQLException e) {248 e.printStackTrace();249 }finally{250 close(rs);251 close(preparedStatement);252 close(connection);253 }254 return false;255 }256 public boolean bookidisIdRight(String num) {//judge bookid
257 int k=0;258 for(int i=0;i
267 }268 public boolean isNumber(String str) {//judge int
269 Pattern pattern = Pattern.compile("^[-\\+]?[\\d]*$");270 returnpattern.matcher(str).matches();271 }272
273
274 public boolean readerHaveBorrow(String readerid,String bookid) { //student have chosen this book
275 Connection connection =getConnection();276 PreparedStatement preparedStatement=null;277 ResultSet rs=null;278 try{279 String sql = "select * from borrows";280 preparedStatement=connection.prepareStatement(sql);281 rs=preparedStatement.executeQuery();282 while(rs.next()){283 if( readerid.equals(rs.getObject(2))&&bookid.equals(rs.getObject(1)) )284 return true;285 }286 } catch(SQLException e) {287 e.printStackTrace();288 }finally{289 close(rs);290 close(preparedStatement);291 close(connection);292 }293 return false;294 }295
296
297 public static voidmain(String[] args) {298 Data a= newData();299 //a.addbook("00","00","00","00","00","00");
300
301 }302
303 }