crypto-ca.rar

package db; 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.sql.Timestamp; import java.text.ParseException; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Date; public class mySQL { // JDBC 驱动名及数据库 URL static final String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver"; static final String DB_URL = "jdbc:mysql://localhost:3306/cert?useUnicode=true&characterEncoding=utf-8&serverTimezone=GMT"; // 数据库的用户名与密码，需要根据自己的设置 static final String USER = "root"; static final String PASS = "123123"; public static String getCAOriginCert(){ Connection conn = null; Statement stmt = null; String result = null; try{ // 注册 JDBC 驱动 Class.forName(JDBC_DRIVER); // 打开链接 System.out.println("连接数据库..."); conn = DriverManager.getConnection(DB_URL,USER,PASS); // 执行查询 System.out.println(" 实例化Statement对象..."); stmt = conn.createStatement(); String sql = "select certString,privateKey from cacert where notBefore <= now() and notAfter >= now()"; ResultSet rs = stmt.executeQuery(sql); while(rs.next()) { result = rs.getString("certString")+"key="+rs.getString("privateKey"); //System.out.println(result); } // 完成后关闭 rs.close(); stmt.close(); conn.close(); }catch(SQLException se){ // 处理 JDBC 错误 se.printStackTrace(); }catch(Exception e){ // 处理 Class.forName 错误 e.printStackTrace(); }finally{ // 关闭资源 try{ if(stmt!=null) stmt.close(); }catch(SQLException se2){ }// 什么都不做 try{ if(conn!=null) conn.close(); }catch(SQLException se){ se.printStackTrace(); } } //System.out.println("Goodbye!"); return result; } public static int abandonCertsFromCertString(String certString) { ArrayList<Integer> idList = mySQL.getCertFromString(certString); int result = 0; for(int i=0;i<idList.size();i++) { Boolean a = mySQL.abandonCertFromID(idList.get(i)); if(a==true)result++; } return result; } public static boolean abandonCertFromID(int id) { Connection conn = null; Statement stmt = null; Boolean result = false; try{ // 注册 JDBC 驱动 Class.forName(JDBC_DRIVER); // 打开链接 System.out.println("连接数据库..."); conn = DriverManager.getConnection(DB_URL,USER,PASS); // 执行查询 System.out.println(" 实例化Statement对象..."); stmt = conn.createStatement(); String sql; sql = "UPDATE cert SET abandoned="+"'true'"+" WHERE id="+id; stmt.executeUpdate(sql); // 完成后关闭 stmt.close(); conn.close(); }catch(SQLException se){ // 处理 JDBC 错误 se.printStackTrace(); }catch(Exception e){ // 处理 Class.forName 错误 e.printStackTrace(); }finally{ // 关闭资源 try{ if(stmt!=null) stmt.close(); }catch(SQLException se2){ }// 什么都不做 try{ if(conn!=null) conn.close(); }catch(SQLException se){ se.printStackTrace(); } } //System.out.println("Goodbye!"); return result; } public static boolean abandonCACertFromID(int id) { Connection conn = null; Statement stmt = null; Boolean result = false; try{ // 注册 JDBC 驱动 Class.forName(JDBC_DRIVER); // 打开链接 System.out.println("连接数据库..."); conn = DriverManager.getConnection(DB_URL,USER,PASS); // 执行查询 System.out.println(" 实例化Statement对象..."); stmt = conn.createStatement(); String sql; sql = "UPDATE cacert SET abandoned="+"'true'"+" WHERE id="+id; stmt.executeUpdate(sql); // 完成后关闭 stmt.close(); conn.close(); }catch(SQLException se){ // 处理 JDBC 错误 se.printStackTrace(); }catch(Exception e){ // 处理 Class.forName 错误 e.printStackTrace(); }finally{ // 关闭资源 try{ if(stmt!=null) stmt.close(); }catch(SQLException se2){ }// 什么都不做 try{ if(conn!=null) conn.close(); }catch(SQLException se){ se.printStackTrace(); } } //System.out.println("Goodbye!"); return result; } /** * 注册证书。会返回证书编号 * @param name * @return */ public static int registCert(String certString) { //certInfo形如name:alias:info:sinfo String[] buffer = certString.split("%"); String name = buffer[0]; String alias = buffer[5]; String info = buffer[1]; String sinfo = buffer[2]; Date currentTime = new Date(Long.parseLong(buffer[3])); Date endTime = new Date(Long.parseLong(buffer[4])); Timestamp notBefore = new Timestamp(currentTime.getTime()); Timestamp notAfter = new Timestamp(endTime.getTime()); String sql = "INSERT INTO cert(username,alias,notBefore,notAfter,userinfo,sinfo,certString,abandoned)values(?,?,?,?,?,?,?,?);"; //========= Connection conn = null; Statement stmt = null; try{ // 注册 JDBC 驱动 Class.forName(JDBC_DRIVER); // 打开链接 System.out.println("连接数据库..."); conn = DriverManager.getConnection(DB_URL,USER,PASS); // 执行查询 System.out.println(" 实例化Statement对象..."); //stmt = conn.createStatement();; PreparedStatement pstmt = conn.prepareStatement(sql); pstmt.setString(1, name); pstmt.setString(2, alias); pstmt.setTimestamp(3, notBefore); pstmt.setTimestamp(4, notAfter); pstmt.setString(5, info); pstmt.setString(6, sinfo); pstmt.setString(7, certString); pstmt.setString(8, "false"); pstmt.executeUpdate(); //sql = "select id from cert where" // 完成后关闭 //rs.close(); pstmt.close(); conn.close(); }catch(SQLException se){ // 处理 JDBC 错误 se.printStackTrace(); }catch(Exception e){ // 处理 Class.forName 错误 e.printStackTrace(); }finally{ // 关闭资源 try{ if(stmt!=null) stmt.close(); }catch(SQLException se2){ }// 什么都不做 try{ if(conn!=null) conn.close(); }catch(SQLException se){ se.printStackTrace(); } } System.out.println("Goodbye!"); //=========