课下作业（第八周）

1.相关内容知识总结

• MYSQL数据库的安装与使用（这里用的是xampp）
• JDBC语句连接数据库，关闭连接
• 条件与顺序查询
• 增删补操作
• 使用预处理语句
• 通用查询和事务管理

2.课上内容补作

导入Word截图：

中东国家人口：
code：

import java.sql.*;
public class Example2 {
    public static void main(String [] args) {
        Connection con=null;
        Statement sql;
        ResultSet rs;
        String c1=" Region Like 'Middle East'";
        String sqlStr =
                "select * from country where "+c1;
        try{  Class.forName("com.mysql.jdbc.Driver");
        }
        catch(Exception e){}
        String uri = "jdbc:mysql://localhost:3306/world?useSSL=true";
        String user ="root";
        String password ="";
        try{
            con = DriverManager.getConnection(uri,user,password);
        }
        catch(SQLException e){ }
        try {
            sql=con.createStatement();
            rs = sql.executeQuery(sqlStr);
            long totalPopulation=0;
            while(rs.next()) {
                int Population=rs.getInt(7);
                totalPopulation +=Population;
            }
            System.out.printf("中东总人口为："+totalPopulation);
            con.close();
        }
        catch(SQLException e) {
            System.out.println(e);
        }
    }
}

结果截图：

世界上平均寿命最长和最短的国家
code：

import java.sql.*;
public class Example3 {
    public static void main(String [] args) {
        Connection con=null;
        Statement sql;
        ResultSet rs;
        float min=100.0f,max=0.0f;
        int i=0;
        String minname=new String("");
        String maxname=new String("");
        String sqlStr =
                "select * from country order by LifeExpectancy";
        try{  Class.forName("com.mysql.jdbc.Driver");
        }
        catch(Exception e){}
        String uri = "jdbc:mysql://localhost:3306/world?useSSL=true";
        String user ="root";
        String password ="";
        try{
            con = DriverManager.getConnection(uri,user,password);
        }
        catch(SQLException e){ }
        try {
            sql=con.createStatement();
            rs = sql.executeQuery(sqlStr);
            while(rs.next()) {
                String Name=rs.getString(2);
                Float LifeExpectancy=rs.getFloat(8);
                if(LifeExpectancy>max) {
                    max =LifeExpectancy;
                    maxname=Name;
                }
                else if((LifeExpectancy<min)&&(LifeExpectancy!=0.0)){
                    {
                        min = LifeExpectancy;
                        minname = Name;
                    }
                }

            }
            con.close();
            System.out.printf("寿命最长的国家为:"+maxname+"，平均寿命为:"+max+"\n");
            System.out.printf("寿命最短的国家为:"+minname+"，平均寿命为:"+min+"\n");
        }
        catch(SQLException e) {
            System.out.println(e);
        }
    }
}

结果截图：

3. 教材11.1-11.10的代码发分析

• Example11_1

import java.sql.*; 
public class Example11_1 {
   public static void main(String args[]) {
      Connection con=null;
      Statement sql; 
      ResultSet rs;
      try{  Class.forName("com.mysql.jdbc.Driver"); //加载JDBC_MySQL驱动
      }
      catch(Exception e){}
      String uri = "jdbc:mysql://localhost:3306/students?useSSL=true";
      String user ="root";
      String password ="";
      try{  
         con = DriverManager.getConnection(uri,user,password); //连接代码
      }
      catch(SQLException e){ }
      try { 
          sql=con.createStatement();
          rs=sql.executeQuery("SELECT * FROM mess"); //查询mess表
          while(rs.next()) {//当存在下一个数据是返回true
             String number=rs.getString(1);//将表第一列的数据传给number
             String name=rs.getString(2);
             Date date=rs.getDate(3);
             float height=rs.getFloat(4);
             System.out.printf("%s\t",number);
             System.out.printf("%s\t",name);
             System.out.printf("%s\t",date); 
             System.out.printf("%.2f\n",height);
          }
          con.close();//关闭连接
      }
      catch(SQLException e) { 
         System.out.println(e);
      }
  }
}

分析：用来查询student数据库中的mess表的全部记录。
Example11_2

import java.sql.*; 
public class Example11_2 {
   public static void main(String args[]) {
      Connection con;
      Statement sql; 
      ResultSet rs;
      con = GetDBConnection.connectDB("students","root","");
      if(con == null ) return;
      try { 
          sql=con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                                    ResultSet.CONCUR_READ_ONLY);
          rs = sql.executeQuery("SELECT * FROM mess ");
          rs.last();
          int max = rs.getRow();
          System.out.println("表共有"+max+"条记录,随机抽取2条记录：");
          int [] a =GetRandomNumber.getRandomNumber(max,2);//得到1-max之间2个不同随机数
          for(int i:a){
             rs.absolute(i);//油标移动到第i行
             String number = rs.getString(1);
             String name = rs.getString(2);
             Date date = rs.getDate(3);
             float h = rs.getFloat(4);
             System.out.printf("%s\t",number);
             System.out.printf("%s\t",name);
             System.out.printf("%s\t",date); 
             System.out.printf("%.2f\n",h);
          }
          con.close();
      }
      catch(SQLException e) { 
         System.out.println(e);
      }
  }
}
import java.sql.*; 
public class GetDBConnection {
   public static Connection connectDB(String DBName,String id,String p) {
      Connection con = null;
      String 
      uri = "jdbc:mysql://localhost:3306/"+DBName+"?useSSL=true&characterEncoding=utf-8";
      try{  Class.forName("com.mysql.jdbc.Driver");//加载JDBC-MySQL驱动
      }
      catch(Exception e){}
      try{  
         con = DriverManager.getConnection(uri,id,p); //连接代码
      }
      catch(SQLException e){}
      return con;
   }
}
import java.util.*;
public class GetRandomNumber {
   public static int [] getRandomNumber(int max,int amount) {
      //1-max之间的amount个不同随机整数
     int [] randomNumber = new int[amount];
     int index =0;
     randomNumber[0]= -1;
     Random random = new Random();
     while(index<amount){
       int number = random.nextInt(max)+1;
       boolean isInArrays=false;
       for(int m:randomNumber){//m依次取数组randomNumber元素的值（见3.7）
          if(m == number)
            isInArrays=true;  //number在数组里了
       }
       if(isInArrays==false){
           //如果number不在数组randomNumber中:
           randomNumber[index] = number;
           index++;   
       }
     }
     return  randomNumber;
   }
}

分析：将数据库单独分装在一个GetDatabaseConnection类中。随机查询student数据库中的mess表的两条记录。
Example11_3

import java.sql.*; 
public class Example11_3 {
   public static void main(String args[]) {
      Connection con;
      Statement sql; 
      ResultSet rs;
      con = GetDBConnection.connectDB("students","root","");
      if(con == null ) return;
      String c1=" year(birthday)<=2000 and month(birthday)>7";//条件1
      String c2=" name Like '张_%'";
      String c3=" height >1.65";
      String sqlStr =
      "select * from mess where "+c1+" and "+c2+" and "+c3+"order by birthday";
      try { 
          sql=con.createStatement();
          rs = sql.executeQuery(sqlStr);
          while(rs.next()) { 
             String number=rs.getString(1);
             String name=rs.getString(2);
             Date date=rs.getDate(3);
             float height=rs.getFloat(4);
             System.out.printf("%s\t",number);
             System.out.printf("%s\t",name);
             System.out.printf("%s\t",date); 
             System.out.printf("%.2f\n",height);
          }
          con.close();
      }
      catch(SQLException e) { 
         System.out.println(e);
      }
  }
}
import java.sql.*; 
public class GetDBConnection {
   public static Connection connectDB(String DBName,String id,String p) {
      Connection con = null;
      String uri = 
     "jdbc:mysql://localhost:3306/"+DBName+"?useSSL=true&characterEncoding=utf-8";
      try{  Class.forName("com.mysql.jdbc.Driver");//加载JDBC-MySQL驱动
      }
      catch(Exception e){}
      try{  
         con = DriverManager.getConnection(uri,id,p); //连接代码
      }
      catch(SQLException e){}
      return con;
   }
}

分析：查询mess表中姓张，身高大于16.5，出生年份为2000或2000年之前，月份在七月份之后的学生，并按出生日期排序。
Example11_4

import java.sql.*; 
public class Example11_4 {
   public static void main(String args[]) {
      Connection con;
      Statement sql; 
      ResultSet rs;
      con = GetDBConnection.connectDB("students","root","");
      if(con == null ) return;
      String jiLu="('R11q','王三','2000-10-23',1.66),"+
                  "('R10q','李武','1989-10-23',1.76)";    //2条记录
      String sqlStr ="insert into mess values"+jiLu;
      try { 
          sql=con.createStatement(); 
          int ok = sql.executeUpdate(sqlStr);
          rs = sql.executeQuery("select * from mess");
          while(rs.next()) { 
             String number=rs.getString(1);
             String name=rs.getString(2);
             Date date=rs.getDate(3);
             float height=rs.getFloat(4);
             System.out.printf("%s\t",number);
             System.out.printf("%s\t",name);
             System.out.printf("%s\t",date); 
             System.out.printf("%.2f\n",height);
          }
          con.close();
      }
      catch(SQLException e) { 
         System.out.println("记录中number值不能重复"+e);
      }
  }
}

分析：在mess表中插入两条记录。
Example11_5

import java.sql.*; 
public class Example11_5 {
   public static void main(String args[]) {
      Connection con;
      PreparedStatement preSql;  //预处理语句对象preSql
      ResultSet rs;
      con = GetDBConnection.connectDB("students","root","");
      if(con == null ) return;
      String sqlStr ="insert into mess values(?,?,?,?)";
      try { 
          preSql = con.prepareStatement(sqlStr);//得到预处理语句对象preSql
          preSql.setString(1,"A001");       //设置第1个?代表的值
          preSql.setString(2,"刘伟");       //设置第2个?代表的值
          preSql.setString(3,"1999-9-10"); //设置第3个?代表的值
          preSql.setFloat(4,1.77f);        //设置第4个?代表的值   
          int ok = preSql.executeUpdate();
          sqlStr="select * from mess where name like ? ";
          preSql = con.prepareStatement(sqlStr);//得到预处理语句对象preSql
          preSql.setString(1,"张%");       //设置第1个?代表的值
          rs = preSql.executeQuery();
          while(rs.next()) { 
             String number=rs.getString(1);
             String name=rs.getString(2);
             Date date=rs.getDate(3);
             float height=rs.getFloat(4);
             System.out.printf("%s\t",number);
             System.out.printf("%s\t",name);
             System.out.printf("%s\t",date); 
             System.out.printf("%.2f\n",height);
          }
          con.close();
      }
      catch(SQLException e) { 
         System.out.println("记录中number值不能重复"+e);
      }
  }
}
import java.sql.*; 
public class GetDBConnection {
   public static Connection connectDB(String DBName,String id,String p) {
      Connection con = null;
      String uri = 
     "jdbc:mysql://localhost:3306/"+DBName+"?useSSL=true&characterEncoding=utf-8";
      try{  Class.forName("com.mysql.jdbc.Driver");//加载JDBC-MySQL驱动
      }
      catch(Exception e){}
      try{  
         con = DriverManager.getConnection(uri,id,p); //连接代码
      }
      catch(SQLException e){}
      return con;
   }
}

分析：使用预处理语句向mess表添加并查询了姓张的记录
Example11_6

import javax.swing.*;
public class Example11_6 {
   public static void main(String args[]) {
  String [] tableHead;
  String [][] content; 
  JTable table ;
  JFrame win= new JFrame();
  Query findRecord = new  Query();
  findRecord.setDatabaseName("students");
  findRecord.setSQL("select * from mess");
  content = findRecord.getRecord();
  tableHead=findRecord.getColumnName();
  table = new JTable(content,tableHead); 
  win.add(new JScrollPane(table));
  win.setBounds(12,100,400,200);
  win.setVisible(true); 
  win.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
   }
}
import java.sql.*;
public class Query {
   String databaseName="";      //数据库名
   String SQL;              //SQL语句
   String [] columnName;        //全部字段（列）名
   String [][] record;          //查询到的记录
   public Query() {
  try{  Class.forName("com.mysql.jdbc.Driver");//加载JDBC-MySQL驱动
  }
  catch(Exception e){}
   }
   public void setDatabaseName(String s) {
  databaseName=s.trim();
   }
   public void setSQL(String SQL) {
  this.SQL=SQL.trim();
   }
   public String[] getColumnName() {
   if(columnName ==null ){
       System.out.println("先查询记录");
       return null;
   }
   return columnName;
   }
   public String[][] getRecord() {
   startQuery();
   return record;
   }
   private void startQuery() { 
  Connection con;
  Statement sql;  
  ResultSet rs;
  String uri = 
 "jdbc:mysql://localhost:3306/"+
  databaseName+"?useSSL=true&characterEncoding=utf-8";
  try { 
    con=DriverManager.getConnection(uri,"root","");
    sql=con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                            ResultSet.CONCUR_READ_ONLY);
    rs=sql.executeQuery(SQL);
    ResultSetMetaData metaData = rs.getMetaData();
    int columnCount = metaData.getColumnCount();//字段数目 
    columnName=new String[columnCount]; 
    for(int i=1;i<=columnCount;i++){
        columnName[i-1]=metaData.getColumnName(i);
    } 
    rs.last(); 
    int recordAmount =rs.getRow();  //结果集中的记录数目
    record = new String[recordAmount][columnCount];
    int i=0;
    rs.beforeFirst();
    while(rs.next()) { 
      for(int j=1;j<=columnCount;j++){
         record[i][j-1]=rs.getString(j); //第i条记录，放入二维数组的第i行
      }
      i++;
    }
    con.close();
  }
  catch(SQLException e) {
    System.out.println("请输入正确的表名"+e);
  }
   }    
}

分析：这段代码的功能是将数据库名以及SQL语句传递给Query类的对象。
Example11_7

import java.sql.*; 
public class Example11_7{
    public static void main(String args[]){
       Connection con = null;
       Statement sql;
       ResultSet rs; 
       String sqlStr;
       con = GetDBConnection.connectDB("students","root","");
       if(con == null ) return;
       try{ float n = 0.02f;
            con.setAutoCommit(false);       //关闭自动提交模式
            sql = con.createStatement();
            sqlStr = "select name,height from mess where number='R1001'";
            rs = sql.executeQuery(sqlStr);
            rs.next();
            float h1 = rs.getFloat(2);
            System.out.println("事务之前"+rs.getString(1)+"身高:"+h1);
            sqlStr = "select name,height from mess where number='R1002'"; 
            rs = sql.executeQuery(sqlStr);
            rs.next();
            float h2 = rs.getFloat(2);
            System.out.println("事务之前"+rs.getString(1)+"身高:"+h2);  
            h1 = h1-n;
            h2 = h2+n;
            sqlStr = "update mess set height ="+h1+" where number='R1001'";
            sql.executeUpdate(sqlStr);
            sqlStr = "update mess set height ="+h2+" where number='R1002'";
            sql.executeUpdate(sqlStr);
            con.commit(); //开始事务处理,如果发生异常直接执行catch块
            con.setAutoCommit(true); //恢复自动提交模式
            String s = "select name,height from mess"+
                      " where number='R1001'or number='R1002'";
            rs = 
            sql.executeQuery(s);
            while(rs.next()){
               System.out.println("事务后"+rs.getString(1)+
                                  "身高:"+rs.getFloat(2));  
            }
            con.close();
         }
         catch(SQLException e){
            try{ con.rollback();          //撤销事务所做的操作
            }
            catch(SQLException exp){}
         }
    }
}
import java.sql.*; 
public class GetDBConnection {
   public static Connection connectDB(String DBName,String id,String p) {
      Connection con = null;
      String uri = 
     "jdbc:mysql://localhost:3306/"+DBName+"?useSSL=true&characterEncoding=utf-8";
      try{  Class.forName("com.mysql.jdbc.Driver");//加载JDBC-MySQL驱动
      }
      catch(Exception e){}
      try{  
         con = DriverManager.getConnection(uri,id,p); //连接代码
      }
      catch(SQLException e){}
      return con;
   }
}

分析：这段代码的功能是将mess表中的number字段R1001的height值减少n，并将减少的n增加到字段是R1002的height上。
Example11_8

import java.sql.*;
public class Example11_8 {
   public static void main(String[] args) {
      Connection con =null;
      Statement sta = null;
      ResultSet rs;
      String SQL;
      try { 
        Class.forName("org.apache.derby.jdbc.EmbeddedDriver");//加载驱动
      }
      catch(Exception e) {
        System.out.println(e);  
        return;
      }
      try { 
         String uri ="jdbc:derby:students;create=true";
         con=DriverManager.getConnection(uri);  //连接数据库
         sta = con.createStatement();
      }
      catch(Exception e) {
        System.out.println(e);  
        return;
      }
      try { SQL = "create table chengji(name varchar(40),score float)";
            sta.execute(SQL);//创建表
      }
      catch(SQLException e) { 
         //System.out.println("该表已经存在"); 
      }
      SQL ="insert into chengji values"+
            "('张三', 90),('李斯', 88),('刘二', 67)";
      try {
         sta.execute(SQL);
         rs = sta.executeQuery("select * from chengji "); // 查询表中的记录
         while(rs.next()) {
            String name=rs.getString(1);
            System.out.print(name+"\t");
            float score=rs.getFloat(2);
            System.out.println(score);
         }
         con.close();
      } 
      catch(SQLException e) {
          System.out.println(e);  
      }
  }
}

分析：这段代码的功能是使用了Derby数据库系统创建了名字是student的数据库，并在数据库中建立了chengji表

课后编程题目

1、code：

import java.sql.*;
public class test1 {
public static void main(String args[]) {
    Connection con;
    Statement sql;
    ResultSet rs;
    con = GetDBConnection.connectDB("students","root","");//连接数据库
    if(con == null ) return;
    String sqlStr =
            "select * from mess "+"order by birthday";
    //通过order by排序
    try {
        sql=con.createStatement();
        rs = sql.executeQuery(sqlStr);
        while(rs.next()) {
            String number=rs.getString(1);
            String name=rs.getString(2);
            Date date=rs.getDate(3);
            float height=rs.getFloat(4);
            System.out.printf("%s\t",number);
            System.out.printf("%s\t",name);
            System.out.printf("%s\t",date);
            System.out.printf("%.2f\n",height);
        }
        con.close();
    }
    catch(SQLException e) {
        System.out.println(e);
    }
}
}

GetDBConnection 类

import java.sql.*; 
public class GetDBConnection {
   public static Connection connectDB(String DBName,String id,String p) {
      Connection con = null;
      String 
      uri = "jdbc:mysql://localhost:3306/"+DBName+"?useSSL=true&characterEncoding=utf-8";
      try{  Class.forName("com.mysql.jdbc.Driver");//加载JDBC-MySQL驱动
      }
      catch(Exception e){}
      try{  
         con = DriverManager.getConnection(uri,id,p); //连接代码
      }
      catch(SQLException e){}
      return con;
   }
}

2、code：

import java.sql.*;
public class Query {
   String databaseName="";      //数据库名
   String SQL;              //SQL语句
   String [] columnName;        //全部字段（列）名
   String [][] record;          //查询到的记录
   public Query() {
  try{  Class.forName("com.mysql.jdbc.Driver");//加载JDBC-MySQL驱动
  }
  catch(Exception e){}
   }
   public void setDatabaseName(String s) {
  databaseName=s.trim();
   }
   public void setSQL(String SQL) {
  this.SQL=SQL.trim();
   }
   public String[] getColumnName() {
   if(columnName ==null ){
       System.out.println("先查询记录");
       return null;
   }
   return columnName;
   }
   public String[][] getRecord() {
   startQuery();
   return record;
   }
   private void startQuery() { 
  Connection con;
  Statement sql;  
  ResultSet rs;
  String uri = 
 "jdbc:mysql://localhost:3306/"+
  databaseName+"?useSSL=true&characterEncoding=utf-8";
  try { 
    con=DriverManager.getConnection(uri,"root","");
    sql=con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                            ResultSet.CONCUR_READ_ONLY);
    rs=sql.executeQuery(SQL);
    ResultSetMetaData metaData = rs.getMetaData();
    int columnCount = metaData.getColumnCount();//字段数目 
    columnName=new String[columnCount]; 
    for(int i=1;i<=columnCount;i++){
        columnName[i-1]=metaData.getColumnName(i);
    } 
    rs.last(); 
    int recordAmount =rs.getRow();  //结果集中的记录数目
    record = new String[recordAmount][columnCount];
    int i=0;
    rs.beforeFirst();
    while(rs.next()) { 
      for(int j=1;j<=columnCount;j++){
         record[i][j-1]=rs.getString(j); //第i条记录，放入二维数组的第i行
      }
      i++;
    }
    con.close();
  }
  catch(SQLException e) {
    System.out.println("请输入正确的表名"+e);
  }
   }    
}

Query类

import javax.management.Query;
import javax.swing.*;
public class test2 {
    public static void main (String args[]) {
        String [] tableHead;
        String [] content;
        JTable table;
        JFrame win= new JFrame();
        Query findRecord = new Query();
        findRecord.setDatabaseName(args[0]);
        findRecord.setSQL("select * from "+args[1]);
        content = findRecord.getRecord();
        tableHead=findRecord.getColumnName();
        table = new JTable(content,tableHead);
        win.add(new JScrollPane(table));
        win.setBounds(12,100,400,200);
        win.setVisible(true);
        win.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
    }
}