[关闭]
@SovietPower 2022-04-26T04:14:59.000000Z 字数 16278 阅读 1502

数据库系统 实验4 JDBC

DB

实验

首先运行之前创建的openGauss容器。

连接JDBC

  1. import static java.lang.System.out;
  3. // JDBC
  4. import java.sql.Connection;
  5. import java.sql.DriverManager;
  7. public class jdbcConnect
  8. {
  9.     static final String JDBC_DRIVER = "org.postgresql.Driver";
  10.     static final String DB_URL = "jdbc:postgresql://localhost:15432/db2022";
  11.     static final String username = "gxb", password="Gxb11111";
  13.     public static void main(String args[])
  14.     {
  15.         try
  16.         {
  17.             Class.forName(JDBC_DRIVER);
  18.             Connection c = DriverManager.getConnection(DB_URL, username, password);
  20.         } catch (Exception e) {
  21.             e.printStackTrace();
  22.             System.err.println(e.getClass().getName() + ": " + e.getMessage());
  23.             System.exit(0);
  24.         }
  25.         out.println("Connect to database gxb successfully!");
  26.     }
  27. }
  1. PS F:\Codes\Java>  & 'F:\Programs\Java\jdk1.8.0_321\bin\java.exe' '-cp' 'C:\Users\LENOVO\AppData\Local\Temp\cp_2ui26sim2oqork6q7n4daqovk.jar' 'jdbcConnect'
  2. Connect to database gxb successfully!

在数据库中创建表

运行jdbcCreate.java：

  1. import static java.lang.System.out;
  3. // JDBC
  4. // import java.sql.*;
  5. import java.sql.Connection;
  6. import java.sql.Statement;
  7. // import java.sql.PreparedStatement;
  8. // import java.sql.ResultSet;
  9. // import java.sql.SQLException;
  10. import java.sql.DriverManager;
  12. public class jdbcCreate
  13. {
  14.     static final String JDBC_DRIVER = "org.postgresql.Driver";
  15.     static final String DB_URL = "jdbc:postgresql://localhost:15432/db2022";
  16.     static final String username = "gxb", password="Gxb11111";
  18.     public static void main(String args[])
  19.     {
  20.         Connection c = null;
  21.         Statement stmt = null;
  22.         try
  23.         {
  24.             Class.forName(JDBC_DRIVER);
  25.             c = DriverManager.getConnection(DB_URL, username, password);
  26.             out.println("Connect to database gxb successfully!");
  28.             stmt = c.createStatement();
  29.             String sql = "CREATE TABLE employee (id INT," +
  30.             " name VARCHAR(20) NOT NULL, " +
  31.             " age INT NOT NULL, " +
  32.             " address VARCHAR(50), " +
  33.             " salary REAL, " +
  34.             "PRIMARY KEY (id))";
  35.             stmt.executeUpdate(sql);
  37.             stmt.close();
  38.             c.close();
  39.         }
  40.         catch ( Exception e )
  41.         {
  42.             System.err.println( e.getClass().getName()+": "+ e.getMessage() );
  43.             System.exit(0);
  44.         }
  45.         out.println("Create table company successfully!");
  46.     }
  47. }
  1. PS F:\Codes\Java>  & 'F:\Programs\Java\jdk1.8.0_321\bin\java.exe' '-cp' 'C:\Users\LENOVO\AppData\Local\Temp\cp_2ui26sim2oqork6q7n4daqovk.jar' 'jdbcCreate'
  3. Connect to database gxb successfully!
  4. Create table company successfully!

向表中插入数据

  1. // JDBC
  2. import java.sql.Connection;
  3. import java.sql.Statement;
  4. // import java.sql.PreparedStatement;
  5. // import java.sql.ResultSet;
  6. // import java.sql.SQLException;
  7. import java.sql.DriverManager;
  9. public class jdbcInsert
  10. {
  11.     static final String JDBC_DRIVER = "org.postgresql.Driver";
  12.     static final String DB_URL = "jdbc:postgresql://localhost:15432/db2022";
  13.     static final String username = "gxb", password="Gxb11111";
  15.     public static void main(String args[])
  16.     {
  17.         Connection c = null;
  18.         Statement stmt = null;
  19.         try
  20.         {
  21.             Class.forName(JDBC_DRIVER);
  22.             c = DriverManager.getConnection(DB_URL, username, password);
  23.             c.setAutoCommit(false);
  24.             System.out.println("Connect to database gxb successfully!");
  25.             stmt = c.createStatement();
  26.             String sql = "INSERT INTO employee VALUES (1, 'Gong', 48, '2075 Kongjiang Road', 20000.00 );";
  27.             stmt.executeUpdate(sql);
  28.             sql = "INSERT INTO employee VALUES (2, 'Luan', 25, '3663 Zhongshan Road(N)', 15000.00 );";
  29.             stmt.executeUpdate(sql);
  30.             sql = "INSERT INTO employee VALUES (3, 'Hu', 23, '3663 Zhongshan Road(N)', 15000.00 );";
  31.             stmt.executeUpdate(sql);
  32.             sql = "INSERT INTO employee VALUES (4, 'Jin', 24, '3663 Zhongshan Road(N)', 15000.00 );";
  33.             stmt.executeUpdate(sql);
  34.             sql = "INSERT INTO employee VALUES (5, 'Yi', 24, '3663 Zhongshan Road(N)', 15000.00 );";
  35.             stmt.executeUpdate(sql);
  37.             stmt.close();
  38.             c.commit();
  39.             c.close();
  40.         }
  41.         catch (Exception e)
  42.         {
  43.             System.err.println(e.getClass().getName()+ ": "+ e.getMessage() );
  44.             System.exit(0);
  45.         }
  46.         System.out.println("5 records inserted successfully!");
  47.     }
  48. }
  1. PS F:\Codes\Java>  f:; cd 'f:\Codes\Java'; & 'F:\Programs\Java\jdk1.8.0_321\bin\java.exe' '-cp' 'C:\Users\LENOVO\AppData\Local\Temp\cp_2ui26sim2oqork6q7n4daqovk.jar' 'jdbcInsert' 
  2. Connect to database gxb successfully!
  3. 5 records inserted successfully!

在表中查询数据

  1. // JDBC
  2. import java.sql.Connection;
  3. import java.sql.Statement;
  4. // import java.sql.PreparedStatement;
  5. import java.sql.ResultSet;
  6. // import java.sql.SQLException;
  7. import java.sql.DriverManager;
  9. public class jdbcSelect
  10. {
  11.     static final String JDBC_DRIVER = "org.postgresql.Driver";
  12.     static final String DB_URL = "jdbc:postgresql://localhost:15432/db2022";
  13.     static final String username = "gxb", password="Gxb11111";
  15.     public static void main(String args[])
  16.     {
  17.         try
  18.         {
  19.             Class.forName(JDBC_DRIVER);
  20.             Connection c = DriverManager.getConnection(DB_URL, username, password);
  21.             c.setAutoCommit(false);
  22.             System.out.println("Connect to database gxb successfully!");
  24.             Statement stmt = c.createStatement();
  25.             ResultSet rs = stmt.executeQuery( "SELECT * FROM employee;" );
  26.             while (rs.next())
  27.             {
  28.                 int id = rs.getInt("id");
  29.                 String name = rs.getString("name");
  30.                 int age = rs.getInt("age");
  31.                 String address = rs.getString("address");
  32.                 float salary = rs.getFloat("salary");
  33.                 System.out.println( "ID = " + id );
  34.                 System.out.println( "NAME = " + name );
  35.                 System.out.println( "AGE = " + age );
  36.                 System.out.println( "ADDRESS = " + address );
  37.                 System.out.println( "SALARY = " + salary );
  38.                 System.out.println();
  39.             }
  40.             rs.close();
  41.             stmt.close();
  42.             c.close();
  43.         }
  44.         catch (Exception e)
  45.         {
  46.             System.err.println( e.getClass().getName()+": "+ e.getMessage() );
  47.             System.exit(0);
  48.         }
  49.         System.out.println("Operation done successfully!");
  50.     }
  51. }
  1. PS F:\Codes\Java>  f:; cd 'f:\Codes\Java'; & 'F:\Programs\Java\jdk1.8.0_321\bin\java.exe' '-cp' 'C:\Users\LENOVO\AppData\Local\Temp\cp_2ui26sim2oqork6q7n4daqovk.jar' 'jdbcSelect' 
  2. Connect to database gxb successfully!
  3. ID = 1
  4. NAME = Gong
  5. AGE = 48
  6. ADDRESS = 2075 Kongjiang Road
  7. SALARY = 20000.0
  9. ID = 2
  10. NAME = Luan
  11. AGE = 25
  12. ADDRESS = 3663 Zhongshan Road(N)
  13. SALARY = 15000.0
  15. ID = 3
  16. NAME = Hu
  17. AGE = 23
  18. ADDRESS = 3663 Zhongshan Road(N)
  19. SALARY = 15000.0
  21. ID = 4
  22. NAME = Jin
  23. AGE = 24
  24. ADDRESS = 3663 Zhongshan Road(N)
  25. SALARY = 15000.0
  27. ID = 5
  28. NAME = Yi
  29. AGE = 24
  30. ADDRESS = 3663 Zhongshan Road(N)
  31. SALARY = 15000.0
  33. Operation done successfully!

更新数据

  1. // JDBC
  2. import java.sql.Connection;
  3. import java.sql.Statement;
  4. // import java.sql.PreparedStatement;
  5. import java.sql.ResultSet;
  6. // import java.sql.SQLException;
  7. import java.sql.DriverManager;
  9. public class jdbcUpdate
  10. {
  11.     static final String JDBC_DRIVER = "org.postgresql.Driver";
  12.     static final String DB_URL = "jdbc:postgresql://localhost:15432/db2022";
  13.     static final String username = "gxb", password="Gxb11111";
  15.     public static void main(String args[])
  16.     {
  17.         try
  18.         {
  19.             Class.forName(JDBC_DRIVER);
  20.             Connection c = DriverManager.getConnection(DB_URL, username, password);
  21.             c.setAutoCommit(false);
  22.             System.out.println("Connect to database gxb successfully!");
  24.             Statement stmt = c.createStatement();
  25.             String sql = "UPDATE employee set SALARY = 50000.00 where ID=1;";
  26.             stmt.executeUpdate(sql);
  27.             c.commit();
  28.             System.out.println("1 record is updated successfully");
  30.             ResultSet rs = stmt.executeQuery( "SELECT * FROM employee;" );
  31.             while (rs.next())
  32.             {
  33.                 int id = rs.getInt("id");
  34.                 String name = rs.getString("name");
  35.                 int age = rs.getInt("age");
  36.                 String address = rs.getString("address");
  37.                 float salary = rs.getFloat("salary");
  38.                 System.out.println( "ID = " + id );
  39.                 System.out.println( "NAME = " + name );
  40.                 System.out.println( "AGE = " + age );
  41.                 System.out.println( "ADDRESS = " + address );
  42.                 System.out.println( "SALARY = " + salary );
  43.                 System.out.println();
  44.             }
  45.             rs.close();
  46.             stmt.close();
  47.             c.close();
  48.         }
  49.         catch (Exception e)
  50.         {
  51.             System.err.println( e.getClass().getName()+": "+ e.getMessage() );
  52.             System.exit(0);
  53.         }
  54.         System.out.println("Operation done successfully!");
  55.     }
  56. }
  1. PS F:\Codes\Java>  f:; cd 'f:\Codes\Java'; & 'F:\Programs\Java\jdk1.8.0_321\bin\java.exe' '-cp' 'C:\Users\LENOVO\AppData\Local\Temp\cp_2ui26sim2oqork6q7n4daqovk.jar' 'jdbcUpdate' 
  2. Connect to database gxb successfully!
  3. 1 record is updated successfully
  4. ID = 2
  5. NAME = Luan
  6. AGE = 25
  7. ADDRESS = 3663 Zhongshan Road(N)
  8. SALARY = 15000.0
  10. ID = 3
  11. NAME = Hu
  12. AGE = 23
  13. ADDRESS = 3663 Zhongshan Road(N)
  14. SALARY = 15000.0
  16. ID = 4
  17. NAME = Jin
  18. AGE = 24
  19. ADDRESS = 3663 Zhongshan Road(N)
  20. SALARY = 15000.0
  22. ID = 5
  23. NAME = Yi
  24. AGE = 24
  25. ADDRESS = 3663 Zhongshan Road(N)
  26. SALARY = 15000.0
  28. ID = 1
  29. NAME = Gong
  30. AGE = 48
  31. ADDRESS = 2075 Kongjiang Road
  32. SALARY = 50000.0
  34. Operation done successfully!

删除数据

  1. // JDBC
  2. import java.sql.Connection;
  3. import java.sql.Statement;
  4. import java.sql.DriverManager;
  5. import java.sql.ResultSet;
  8. public class jdbcDelete
  9. {
  10.     static final String JDBC_DRIVER = "org.postgresql.Driver";
  11.     static final String DB_URL = "jdbc:postgresql://localhost:15432/db2022";
  12.     static final String username = "gxb", password="Gxb11111";
  14.     public static void main(String args[])
  15.     {
  16.         Connection c;
  17.         try
  18.         {
  19.             Class.forName(JDBC_DRIVER);
  20.             c = DriverManager.getConnection(DB_URL, username, password);
  21.             c.setAutoCommit(false);
  22.             System.out.println("Connect to database gxb successfully!");
  24.             Statement stmt = c.createStatement();
  25.             String sql = "DELETE from employee where ID=2;";
  26.             stmt.executeUpdate(sql);
  27.             c.commit();
  28.             System.out.println("1 record is deleted successfully!");
  30.             ResultSet rs = stmt.executeQuery( "SELECT * FROM employee;" );
  31.             while (rs.next())
  32.             {
  33.                 int id = rs.getInt("id");
  34.                 String name = rs.getString("name");
  35.                 int age = rs.getInt("age");
  36.                 String address = rs.getString("address");
  37.                 float salary = rs.getFloat("salary");
  38.                 System.out.println( "ID = " + id );
  39.                 System.out.println( "NAME = " + name );
  40.                 System.out.println( "AGE = " + age );
  41.                 System.out.println( "ADDRESS = " + address );
  42.                 System.out.println( "SALARY = " + salary );
  43.                 System.out.println();
  44.             }
  45.             rs.close();
  46.             stmt.close();
  47.             c.close();
  48.         }
  49.         catch (Exception e)
  50.         {
  51.             System.err.println( e.getClass().getName()+": "+ e.getMessage() );
  52.             System.exit(0);
  53.         }
  54.         System.out.println("Operation done successfully!");
  55.     }
  56. }
  1. PS F:\Codes\Java>  f:; cd 'f:\Codes\Java'; & 'F:\Programs\Java\jdk1.8.0_321\bin\java.exe' '-cp' 'C:\Users\LENOVO\AppData\Local\Temp\cp_2ui26sim2oqork6q7n4daqovk.jar' 'jdbcDelete' 
  2. Connect to database gxb successfully!
  3. 1 record is deleted successfully!
  4. ID = 3
  5. NAME = Hu
  6. AGE = 23
  7. ADDRESS = 3663 Zhongshan Road(N)
  8. SALARY = 15000.0
  10. ID = 4
  11. NAME = Jin
  12. AGE = 24
  13. ADDRESS = 3663 Zhongshan Road(N)
  14. SALARY = 15000.0
  16. ID = 5
  17. NAME = Yi
  18. AGE = 24
  19. ADDRESS = 3663 Zhongshan Road(N)
  20. SALARY = 15000.0
  22. ID = 1
  23. NAME = Gong
  24. AGE = 48
  25. ADDRESS = 2075 Kongjiang Road
  26. SALARY = 50000.0
  28. Operation done successfully!

使用Prepare语句批量插入及更新

  1. // JDBC
  2. import java.sql.*;
  4. public class DBTest
  5. {
  6.     static final String JDBC_DRIVER = "org.postgresql.Driver";
  7.     static final String DB_URL = "jdbc:postgresql://localhost:15432/db2022";
  8.     static final String username = "gxb", password="Gxb11111";
  10.     //Connect to database
  11.     public static Connection GetConnection(String username, String passwd)
  12.     {
  13.         Connection conn = null;
  14.         try {
  15.             Class.forName(JDBC_DRIVER).newInstance();
  16.         } catch (Exception e) {
  17.             e.printStackTrace();
  18.             return null;
  19.         }
  20.         try {
  21.             conn = DriverManager.getConnection(DB_URL, username, passwd);
  22.             System.out.println("Connect to database successfully!");
  23.         } catch (Exception e) {
  24.             e.printStackTrace();
  25.             return null;
  26.         }
  27.         return conn;
  28.     }
  30.     //Create table customer
  31.     public static void CreateTable(Connection conn)
  32.     {
  33.         Statement stmt = null;
  34.         try
  35.         {
  36.             stmt = conn.createStatement();
  37.             stmt.executeUpdate("CREATE TABLE customer(id INTEGER, name VARCHAR(20), PRIMARY KEY (id));");
  38.             System.out.println("Create table customer successfully!");
  39.             stmt.close();
  40.         }
  41.         catch (SQLException e)
  42.         {
  43.             if (stmt != null) {
  44.                 try {
  45.                     stmt.close();
  46.                 } catch (SQLException e1) {
  47.                     e1.printStackTrace();
  48.                 }
  49.             }
  50.             e.printStackTrace();
  51.         }
  52.     }
  54.     //Execute prepare statement, batch insert
  55.     public static void BatchInsertData(Connection conn)
  56.     {
  57.         PreparedStatement pst = null;
  58.         try {
  59.             pst = conn.prepareStatement("INSERT INTO customer VALUES (?,?)");
  60.             for (int i = 0; i < 10; i++)
  61.             {
  62.                 pst.setInt(1, i);
  63.                 pst.setString(2, "ECNUer " + i);
  64.                 pst.addBatch();
  65.             }
  66.             pst.executeBatch();
  67.             System.out.println("Insert 10 record in 1 batch successfully!");
  69.             pst.close();
  70.         }
  71.         catch (SQLException e)
  72.         {
  73.             if (pst != null)
  74.             {
  75.                 try {
  76.                     pst.close();
  77.                 } catch (SQLException e1) {
  78.                     e1.printStackTrace();
  79.                 }
  80.             }
  81.             e.printStackTrace();
  82.         }
  83.     }
  85.     //Update by prepare statement
  86.     public static void ExecPreparedSQL(Connection conn)
  87.     {
  88.         PreparedStatement pstmt = null;
  89.         try {
  90.             pstmt = conn.prepareStatement("UPDATE customer SET name = ? WHERE id = 1");
  91.             pstmt.setString(1, "Gong");
  93.             pstmt.executeUpdate();
  94.             System.out.println("Update custormer 1's name successfully!");
  96.             pstmt.close();
  97.         }
  98.         catch (SQLException e)
  99.         {
  100.             if (pstmt != null)
  101.             {
  102.                 try {
  103.                     pstmt.close();
  104.                 } catch (SQLException e1) {
  105.                     e1.printStackTrace();
  106.                 }
  107.             }
  108.             e.printStackTrace();
  109.         }
  110.     }
  112.     // Main program
  113.     public static void main(String[] args)
  114.     {
  115.         Connection conn = GetConnection(username, password);
  116.         CreateTable(conn);
  117.         BatchInsertData(conn);
  118.         ExecPreparedSQL(conn);
  119.         try {
  120.             conn.close();
  121.         } catch (SQLException e) {
  122.             e.printStackTrace();
  123.         }
  124.     }
  125. }
  1. PS F:\Codes\Java>  f:; cd 'f:\Codes\Java'; & 'F:\Programs\Java\jdk1.8.0_321\bin\java.exe' '-cp' 'C:\Users\LENOVO\AppData\Local\Temp\cp_2ui26sim2oqork6q7n4daqovk.jar' 'DBTest' 
  2. Connect to database successfully!
  3. Create table customer successfully!
  4. Insert 10 record in 1 batch successfully!
  5. Update custormer 1's name successfully!

习题

  1. import java.util.Scanner;
  3. import static java.lang.System.out;
  5. // JDBC
  6. // import java.sql.*;
  7. import java.sql.Connection;
  8. import java.sql.Statement;
  9. import java.sql.PreparedStatement;
  10. import java.sql.ResultSet;
  11. import java.sql.SQLException;
  12. import java.sql.DriverManager;
  14. // mysql
  15. import com.mysql.cj.exceptions.MysqlErrorNumbers;
  17. // class
  18. public class Exercise_5_12
  19. {
  20.     static final String JDBC_DRIVER = "org.postgresql.Driver";
  21.     static final String DB_URL = "jdbc:postgresql://localhost:15432/db2022";
  22.     static final String username = "gxb", password="Gxb11111";
  24.     static Connection conn;
  26.     public static void main(String... args)
  27.     {
  28.         // Connect to DB
  29.         // a.
  30.         Scanner scanner = new Scanner(System.in);
  31.         int ret;
  32.         if ((ret=ConnectDB(scanner))!=0)
  33.         {
  34.             while(ret == MysqlErrorNumbers.ER_ACCESS_DENIED_ERROR)
  35.             {
  36.                 out.println("Invalid Password. Access denied.");
  37.                 ret = ConnectDB(scanner);
  38.             }
  39.             if (ret != 0)
  40.             {
  41.                 Fail();
  42.                 return;
  43.             }
  44.         }
  46.         // b.
  47.         if (!TaskB(scanner))
  48.         {
  49.             Fail();
  50.             return;
  51.         }
  52.         // c.
  53.         int id = TaskC(scanner);
  54.         if (id == 0)
  55.         {
  56.             Fail();
  57.             return;
  58.         }
  59.         // d.
  60.         TaskD(id);
  62.         // Finish and close conn and stmt
  63.         CloseConnection(conn);
  65.         out.println("\nfinish!");
  66.     }
  68.     static void Fail()
  69.     {
  70.         out.println("Operation failed. Please check your config.");
  71.     }
  73.     static boolean TaskB(Scanner scanner)
  74.     {
  75.         boolean done = false;
  76.         ResultSet rs = null;
  77.         PreparedStatement pStmt = null;
  78.         out.print("\nB: type the name: ");
  80.         while(true)
  81.         {
  82.             String query = scanner.next();
  83.             try
  84.             {
  85.                 pStmt = conn.prepareStatement(
  86.                     "SELECT id, name FROM instructor "+
  87.                     "WHERE lower(name) like ?"
  88.                 );
  89.                 pStmt.setString(1, "%"+query+"%");
  91.                 rs = pStmt.executeQuery();
  92.                 while(rs.next())
  93.                 {
  94.                     done = true;
  95.                     out.printf("%d %s\n", rs.getInt(1), rs.getString(2));
  96.                 }
  97.             } catch (SQLException se) {
  98.                 se.printStackTrace();
  99.                 return false;
  100.             } catch (Exception e) {
  101.                 e.printStackTrace();
  102.                 return false;
  103.             }
  104.             // out.println(pStmt.toString());
  106.             if (done) break;
  107.             out.println("No such ID found.");
  108.         }
  110.         CloseResultSet(rs);
  111.         ClosePreparedStatement(pStmt);
  112.         return true;
  113.     }
  115.     static int TaskC(Scanner scanner)
  116.     {
  117.         int ret = 0;
  118.         ResultSet rs = null;
  119.         PreparedStatement pStmt = null;
  120.         out.print("\nC: type the ID: ");
  122.         int query = scanner.nextInt();
  123.         if (query<0 || query>99999)
  124.         {
  125.             out.println("Invalid id.");
  126.             return 0;
  127.         }
  128.         try
  129.         {
  130.             pStmt = conn.prepareStatement(
  131.                 "SELECT id, name FROM instructor "+
  132.                 "WHERE id=?"
  133.             );
  134.             pStmt.setInt(1, query);
  136.             rs = pStmt.executeQuery();
  137.             if (rs.next())
  138.             {
  139.                 ret = rs.getInt(1);
  140.                 out.printf("ID Found:\n%d %s\n", rs.getInt(1), rs.getString(2));
  141.             }
  142.             else
  143.                 out.println("No such name found. Program exiting.");
  144.         } catch (SQLException se) {
  145.             se.printStackTrace();
  146.         } catch (Exception e) {
  147.             e.printStackTrace();
  148.         }
  150.         CloseResultSet(rs);
  151.         ClosePreparedStatement(pStmt);
  152.         return ret;
  153.     }
  155.     static void TaskD(int id)
  156.     {
  157.         boolean ret = false;
  158.         ResultSet rs = null;
  159.         PreparedStatement pStmt = null;
  160.         out.printf("\nD: checking ID: %d\n", id);
  162.         try
  163.         {
  164.             pStmt = conn.prepareStatement(
  165.                 "WITH temp(course_id, sec_id, semester, year, cnt) AS "+
  166.                     "(SELECT course_id, sec_id, semester, year, count(*) "+
  167.                     "FROM teaches inner join takes using(course_id, sec_id, semester, year) "+
  168.                     "WHERE teaches.id=? "+
  169.                     "GROUP BY course_id, sec_id, semester, year) "+
  170.                 "SELECT dept_name, course_id, title, sec_id, semester, year, cnt "+
  171.                 "FROM course join temp using(course_id) "+
  172.                 "ORDER BY dept_name, course_id, year, semester"
  173.             );
  174.             pStmt.setInt(1, id);
  176.             rs = pStmt.executeQuery();
  177.             while (rs.next())
  178.             {
  179.                 ret = true;
  180.                 out.printf("%s %s %s %d %s %d %d\n", rs.getString(1), rs.getString(2), rs.getString(3), rs.getInt(4), rs.getString(5), rs.getInt(6), rs.getInt(7));
  181.             }
  182.         } catch (SQLException se) {
  183.             se.printStackTrace();
  184.         } catch (Exception e) {
  185.             e.printStackTrace();
  186.         }
  188.         CloseResultSet(rs);
  189.         ClosePreparedStatement(pStmt);
  191.         if (!ret)
  192.             out.println("He/She hasn't taught any course yet.");
  193.     }
  195.     static int ConnectDB(Scanner scanner)
  196.     {
  197.         out.println("Input your username and password:");
  198.         // String username = "root";
  199.         // String password = "GXB";
  200.         String username = scanner.next();
  201.         String password = scanner.next();
  203.         try
  204.         {
  205.             Class.forName(JDBC_DRIVER);
  206.             conn = DriverManager.getConnection(DB_URL, username, password);
  207.         }
  208.         catch(SQLException se)
  209.         {
  210.             if (se.getErrorCode() != MysqlErrorNumbers.ER_ACCESS_DENIED_ERROR)
  211.                 se.printStackTrace();
  212.             return se.getErrorCode();
  213.         }
  214.         catch(Exception e)
  215.         {
  216.             e.printStackTrace();
  217.             return -1;
  218.         }
  219.         return 0;
  220.     }
  222.     static void CloseConnection(Connection conn)
  223.     {
  224.         try {
  225.             if(conn!=null) conn.close();
  226.         } catch(SQLException se) {
  227.             se.printStackTrace();
  228.         }
  229.     }
  231.     static void CloseStatement(Statement stmt)
  232.     {
  233.         try {
  234.             if(stmt!=null) stmt.close();
  235.         } catch(SQLException se) {
  236.             se.printStackTrace();
  237.         }
  238.     }
  240.     static void ClosePreparedStatement(PreparedStatement pStmt)
  241.     {
  242.         try {
  243.             if(pStmt!=null) pStmt.close();
  244.         } catch(SQLException se) {
  245.             se.printStackTrace();
  246.         }
  247.     }
  249.     static void CloseResultSet(ResultSet rs)
  250.     {
  251.         try {
  252.             if(rs!=null) rs.close();
  253.         } catch(SQLException se) {
  254.             se.printStackTrace();
  255.         }
  256.     }
  257. }
  1. PS F:\Codes\Java>  f:; cd 'f:\Codes\Java'; & 'F:\Programs\Java\jdk1.8.0_321\bin\java.exe' '-cp' 'C:\Users\LENOVO\AppData\Local\Temp\cp_2ui26sim2oqork6q7n4daqovk.jar' 'Exercise_5_12' 
  2. Input your username and password:
  3. gxb
  4. Gxb11111
  6. B: type the name: o
  7. 63395 McKinnon
  8. 4233 Luo
  9. 50885 Konstantinides
  10. 80759 Queiroz
  11. 97302 Bertolino
  12. 74420 Voronina
  13. 35579 Soisalon-Soininen
  14. 31955 Moreira
  15. 6569 Mingoz
  16. 90643 Choll
  17. 42782 Vicentino
  18. 22591 DAgostino
  19. 52647 Bancilhon
  20. 36897 Morris
  21. 3199 Gustafsson
  22. 34175 Bondi
  23. 33351 Bourrier
  24. 43779 Romero
  25. 77346 Mahmoud
  26. 28400 Atanassov
  28. C: type the ID: 28400
  29. ID Found:
  30. 28400 Atanassov
  32. D: checking ID: 28400
  33. Statistics 603 Care and Feeding of Cats 1 Fall 2003 306
  34. Statistics 604 UNIX System Programmming 1 Spring 2009 300
  36. finish!
添加新批注
在作者公开此批注前，只有你和作者可见。
回复批注