dbs.java

一个采用Java Socket编程实现的C/S模式的记单词软件

package project;

import java.sql.*;
public class DBS
{
  //根据用户名返回对应的密码值 (已测）
 public String Check_PassWord(String userName)
  {
       String User_Name = userName;
        String PassWord = "";
         String Jdriver="sun.jdbc.odbc.JdbcOdbcDriver";  
          String conURL="jdbc:odbc:student";
   try
   { 
     Class.forName(Jdriver); 
    }
   catch(java.lang.ClassNotFoundException e)
    {
       System.out.println("forname:"+e.getMessage()); 
     }
   try
    { 
      Connection con=DriverManager.getConnection(conURL);    
       Statement k =con.createStatement();
        ResultSet rs = k.executeQuery("select 密码 from 用户信息 where 用户名 = '"+ User_Name +"'");
while(rs.next())
  {
      PassWord = rs.getString("密码");  
   }
    k.close(); 
     con.close(); 
     return(PassWord);
   }catch(SQLException e)  
    {
       System.out.println("SQLException:"+e.getMessage());
       return null;
     }  
   
 }
 
  //在用户表上插入纪录 ( 已测试）

public boolean User_Insert(String userName,String passWord,String Name,String Sex,String eMail,int degree)
 {
   String NewUserName = userName;
   String NewPassWord = passWord;
   String NewName = Name;
   String NewSex = Sex;
   String NewEmail = eMail;
   int NewDegree = degree;
 
   String JDriver="sun.jdbc.odbc.JdbcOdbcDriver";
   String conURL="jdbc:odbc:student";
   try
    {  
       Class.forName(JDriver); 
     }
   catch(java.lang.ClassNotFoundException e)
      { 
        System.out.println("forname:"+e.getMessage()); 
      }
  try
    { 
      Connection con=DriverManager.getConnection(conURL);
      //使用SQL命令insert插入纪录到表中
      Statement s=con.createStatement();
      String r1="insert into 用户信息 values('"+NewUserName+"','"+NewPassWord+"','"+NewName+"','"+NewSex+"','"+NewEmail+"',"+NewDegree+")";
      s.executeUpdate(r1);//执行SQL语言
      s.close();con.close();
      return true;
     }
  catch(SQLException e) 
   { 
      System.out.println("SQLException:"+e.getMessage());
      return false;
    }
  }

//在单词表上插入纪录（已测） 

public boolean newWord_Insert(String word,String wordKind,String wordMeanniing,int wordDegree)
 {
  // String NewTableName = tableName; 
    String newWord = word;
    String newWordKind = wordKind;
    String newWordMeanning = wordMeanniing;
    int newWordDegree = wordDegree;
       
   
     String JDriver="sun.jdbc.odbc.JdbcOdbcDriver";
      String conURL="jdbc:odbc:student";
      try
      {  
          Class.forName(JDriver);  
       }
      catch(java.lang.ClassNotFoundException e)
       { 
          System.out.println("forname:"+e.getMessage()); 
        }
  try
   { 
      Connection con=DriverManager.getConnection(conURL);
      //使用SQL命令insert插入纪录到表中
      Statement s=con.createStatement();
      String r1="insert into WordList values('"+ newWord + "','" + newWordKind + "','" + newWordMeanning + "','"+ newWordDegree +"')";
      s.executeUpdate(r1);//执行SQL语言
      s.close();con.close();
      return(true);
    }
  catch(SQLException e) 
   { 
      System.out.println("SQLException:"+e.getMessage());
      return false;
    }
     
  }

//修改用户密码 （已测） 
public boolean Update_UserPassWord(String userName,String passWord)
 {
    String NewUserName = userName;
    String NewPassWord = passWord;
    String Jdriver="sun.jdbc.odbc.JdbcOdbcDriver";
    String conURL="jdbc:odbc:student";
    try{ 
          Class.forName(Jdriver); 
        }
    catch(java.lang.ClassNotFoundException e) 
    { 
        System.out.println("forname:"+e.getMessage());
     }
  try {
         Connection con=DriverManager.getConnection(conURL);
         Statement s = con.createStatement();
         String r1 = "UPDATE 用户信息 set 密码 = '"+ NewPassWord +"' where 用户名 = '"+ NewUserName +"'";
         s.executeUpdate(r1);
         s.close();
         con.close();
      }
  catch(SQLException e)
   {  
      System.out.println("SQLException:"+e.getMessage());  
    } 
  return(true);
 }
 

//管理员删除指定词条记录 （已测） 

public boolean Delete_WordRecord(String deleteWord,String wordKind)
{
  String delete_Word = deleteWord;
    String delete_WordKind = wordKind;
     String Jdriver="sun.jdbc.odbc.JdbcOdbcDriver";  String conURL="jdbc:odbc:student";
  try 
   { 
      Class.forName(Jdriver); 
    }
  catch(java.lang.ClassNotFoundException e)
    {
      System.out.println("forname:"+e.getMessage()); 
    }
  try
    { 
      Connection con=DriverManager.getConnection(conURL);
	Statement s=con.createStatement();  //删除记录
          PreparedStatement ps=con.prepareStatement("delete from WordList where 单词 = '"+ delete_Word +"' AND 词性 = '"+ delete_WordKind +"'");
           ps.executeUpdate();    
            ps.close();
             s.close();	  
              con.close(); 
             return(true);    
     }
  catch(SQLException e)
   {
      System.out.println("SQLException:"+e.getMessage()); 
       return false;
    } 
  }
//用户删除生词本中指定词条记录 （已测） 

public boolean Delete_NewWordRecord(String deleteWord)
{
  String delete_Word = deleteWord;
   String Jdriver="sun.jdbc.odbc.JdbcOdbcDriver"; 
    String conURL="jdbc:odbc:student";
  try 
   { 
      Class.forName(Jdriver); 
    }
  catch(java.lang.ClassNotFoundException e)
    {
      System.out.println("forname:"+e.getMessage()); 
    }
  try
    { 
      Connection con=DriverManager.getConnection(conURL);
	Statement s=con.createStatement();  //删除记录
          PreparedStatement ps=con.prepareStatement("delete from WordList where 单词 = '"+ delete_Word +"'");
           ps.executeUpdate();    
            ps.close();
             s.close();	  
              con.close(); 
             return(true);    
     }
  catch(SQLException e)
   {
      System.out.println("SQLException:"+e.getMessage()); 
       return false;
    } 
  }

//查看所有用户并输出（已测）（已修改打包）  
public String LooK_All_User()
 {
   String LongString = "";
    String Jdriver="sun.jdbc.odbc.JdbcOdbcDriver";  
    String conURL="jdbc:odbc:student";
 try{ 
       Class.forName(Jdriver); 
     }
  catch(java.lang.ClassNotFoundException e)
    {
      System.out.println("forname:"+e.getMessage()); 
     }
  try
   { 
      Connection con=DriverManager.getConnection(conURL);    
       Statement k =con.createStatement();
        ResultSet rs = k.executeQuery("select * from 用户信息 ");
 while(rs.next())
   {
     LongString = LongString + rs.getString("用户名")+" "+rs.getString("密码")+" "+rs.getString("姓名")+" "+rs.getString("性别")+" "+rs.getString("电子邮箱")+" "+rs.getString("级别")+" ";  
   }
      k.close(); 
       con.close(); }  
  catch(SQLException e)
   {
      System.out.println("SQLException:"+e.getMessage()); 
    } 
   return(LongString);
  }

//查看指定单词表的全部单词(包括查看生词表)并返回
public String Scrip_WordList(String TableName)
 {
   String Table_Name = TableName;
    String LongString = "";
    String Jdriver="sun.jdbc.odbc.JdbcOdbcDriver";  
     String conURL="jdbc:odbc:student";
 try
   { 
     Class.forName(Jdriver); 
    }
  catch(java.lang.ClassNotFoundException e)
    {
       System.out.println("forname:"+e.getMessage()); 
     }
 try
   { 
      Connection con=DriverManager.getConnection(conURL);    
       Statement k =con.createStatement();
        ResultSet rs = k.executeQuery("select * from "+Table_Name);
 while(rs.next())
   {
      LongString =LongString + rs.getString("单词")+" "+rs.getString("词性")+" "+rs.getString("释义")+" "+rs.getString("等级")+" ";  
    }
    k.close(); 
     con.close(); }  
  catch(SQLException e)  
    {
       System.out.println("SQLException:"+e.getMessage()); 
     } 
     System.out.println(LongString);
     return(LongString); 
  }
//查看指定级别的指定词性的所有单词并输出（已测）
public String Scrip_ChosenKind_WordList(String WordDegree,String wordKind)
 {
   String Word_Degree = WordDegree;
    String Word_Kind = wordKind;
     String LongString = "";
      String Jdriver="sun.jdbc.odbc.JdbcOdbcDriver";  
       String conURL="jdbc:odbc:student";
 try
   { 
     Class.forName(Jdriver); 
    }
  catch(java.lang.ClassNotFoundException e)
    {
       System.out.println("forname:"+e.getMessage()); 
     }
 try
   { 
      Connection con=DriverManager.getConnection(conURL);    
       Statement k =con.createStatement();
        ResultSet rs = k.executeQuery("select * from WordList where 等级 = '"+ Word_Degree +"' AND 词性 = '"+ Word_Kind +"'");
 while(rs.next())
   {
   LongString =LongString + " "+rs.getString("单词")+" "+rs.getString("词性")+" "+rs.getString("释义")+" "+rs.getString("等级");  
    }
    k.close(); 
     con.close(); }  
  catch(SQLException e)  
    {
       System.out.println("SQLException:"+e.getMessage()); 
     } 
 return(LongString);  
}

//查看属于同一级别的全部单词（已测）

public String Check_SomeDegree_AllWord(String wordDegree)
 {
   
    String Word_Degree = wordDegree;
     String LongString = "";
      String Jdriver="sun.jdbc.odbc.JdbcOdbcDriver";  
       String conURL="jdbc:odbc:student";
 try
   { 
     Class.forName(Jdriver); 
    }
  catch(java.lang.ClassNotFoundException e)
    {
       System.out.println("forname:"+e.getMessage()); 
     }
 try
   { 
      Connection con=DriverManager.getConnection(conURL);    
       Statement k =con.createStatement();
        ResultSet rs = k.executeQuery("select * from WordList where 等级 = '"+ Word_Degree +"'");
 while(rs.next())
   {
   LongString =LongString + rs.getString("单词")+" "+rs.getString("词性")+" "+rs.getString("释义")+" "+rs.getString("等级")+ " ";  
    }
    k.close(); 
     con.close(); }  
  catch(SQLException e)  
    {
       System.out.println("SQLException:"+e.getMessage()); 
     } 
 return(LongString); 
  }

  //根据用户名创建生词本表
  public void Create_StrangeWord_Table(String UserName)
  { 
     String TableName =  UserName;
     String JDriver="sun.jdbc.odbc.JdbcOdbcDriver";   //声明jdbc驱动程序类型
     String conURL="jdbc:odbc:student";                //定义jdbc的url对象
     try
      { 
         Class.forName(JDriver); 
       }                  //加载jdbc-odbc桥驱动程序
     catch(java.lang.ClassNotFoundException e)
      { 
         System.out.println("forname:"+e.getMessage());
       }
     try 
      {
         Connection con=DriverManager.getConnection(conURL);//连接数据库URL
         Statement s=con.createStatement();           //建立Statement类对象
	  //创建生词本表
	 String query1="create table "+ TableName +"(单词 String,词性 String,释义 String,等级 int,primary key(单词))";//说明：此处只定义了“单词”为主键。会造成一个单词只能加入一次，尽管他们词性不同 
         s.executeUpdate(query1);  //执行SQL语句
         s.close();               //释放statement所连接的数据库及jdbc资源
	 con.close();             //关闭与数据库的连接 
       }
    catch(SQLException e)
     { 
        System.out.println("SQLException:"+e.getMessage());
      }
   }

  /////在已有生词本表上加入生词
  public boolean Add_StrangeWord(String UserName,String word,String wordKind,String wordMeanniing,int wordDegree)
  { 
     String TableName =  UserName;
       String View_Word = word;
        String newWordKind = wordKind;
         String newWordMeanning = wordMeanniing; 
          int newWordDegree = wordDegree;
          
         String JDriver="sun.jdbc.odbc.JdbcOdbcDriver";   //声明jdbc驱动程序类型
          String conURL="jdbc:odbc:student";                //定义jdbc的url对象
     try
      { 
         Class.forName(JDriver); 
       }                  //加载jdbc-odbc桥驱动程序
     catch(java.lang.ClassNotFoundException e)
      { 
         System.out.println("forname:"+e.getMessage());
       }
     try 
      {
         Connection con=DriverManager.getConnection(conURL);//连接数据库URL
         Statement s=con.createStatement();           //建立Statement类对象
	  //创建生词本表
	 String query1="INSERT INTO "+ TableName +" VALUES('"+ View_Word +"','"+ newWordKind +"','"+ newWordMeanning +"',"+ newWordDegree +")";
         s.executeUpdate(query1);  //执行SQL语句
         s.close();               //释放statement所连接的数据库及jdbc资源
	 con.close();//关闭与数据库的连接 
         return true;
       }
    catch(SQLException e)
     { 
        System.out.println("SQLException:"+e.getMessage());
        return false;
      }
   }
  //查看具体的某个具体的单词受否存在
  public String  Check_OneWord(String checkWord,String wordKind)
 {
   String Check_Word = checkWord;
    String Word_Kind = wordKind;
    String LongString = "";
      String Jdriver="sun.jdbc.odbc.JdbcOdbcDriver";  
       String conURL="jdbc:odbc:student";
 try
   { 
     Class.forName(Jdriver); 
    }
  catch(java.lang.ClassNotFoundException e)
    {
       System.out.println("forname:"+e.getMessage()); 
     }
 try
   { 
      Connection con=DriverManager.getConnection(conURL);    
       Statement k =con.createStatement();
        ResultSet rs = k.executeQuery("select * from WordList where 单词 = '"+ Check_Word +"' AND 词性 = '"+ Word_Kind +"'");
 while(rs.next())
   {
      LongString =LongString + rs.getString("单词");//+" "+rs.getString("词性")+" "+rs.getString("释义")+" "+rs.getString("等级");  
    }
    k.close(); 
     con.close();
    }  
  catch(SQLException e)  
    {
       System.out.println("SQLException:"+e.getMessage()); 
     } 
  return(LongString); 
  }

}