使用Access数据库管理系统,例如Microsoft Access,建立一个名为Book.accdb的数据库。在数据库中新建bookList表,该表的字段为: ISBN(varchar),name(varchar),price(float),chubanDate(date),其中ISBN设置为主键(PRIMARY KEY)。接着输入10条记录。 编写程序,在bookList表中随机查询5条记录(参考课本P291中的private static int [] getRandomNumber(int max,int amount)方法),并计算出这5条记录price字段值的平均值,即计算平均价格,并使用GUI的表格(JTable)的方式显示该5条记录(提示:使用absolute(int row)方法将游标移到参数row指定的行),用文本框显示平均值。 注意:可使用mysql或其它数据库软件。
时间: 2024-03-15 17:45:14 浏览: 120
Access数据库.accdb驱动
很抱歉,我无法实际执行和操作数据库,因为我只是一个AI语言模型。但是,我可以给你提供一些思路和代码片段,以便你可以完成这个任务。
首先,你需要使用Java中的JDBC API来连接和操作Access数据库。这需要你先下载并安装Microsoft Access驱动程序(也称为JDBC-ODBC Bridge驱动程序)。
然后,你需要编写Java代码来创建一个名为Book.accdb的数据库,以及一个名为bookList的表格,使用以下的代码:
```java
import java.sql.*;
public class CreateDatabase {
public static void main(String[] args) {
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
String url = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=Book.accdb";
Connection conn = DriverManager.getConnection(url);
Statement stmt = conn.createStatement();
String sql = "CREATE TABLE bookList " +
"(ISBN VARCHAR(20) PRIMARY KEY, " +
" name VARCHAR(255), " +
" price FLOAT, " +
" chubanDate DATE)";
stmt.executeUpdate(sql);
stmt.close();
conn.close();
System.out.println("Table created successfully");
} catch (Exception e) {
System.err.println(e.getClass().getName() + ": " + e.getMessage());
}
}
}
```
接着,你需要使用Java代码向bookList表格中插入10条记录。这可以使用以下的代码实现:
```java
import java.sql.*;
public class InsertData {
public static void main(String[] args) {
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
String url = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=Book.accdb";
Connection conn = DriverManager.getConnection(url);
Statement stmt = conn.createStatement();
String sql = "INSERT INTO bookList (ISBN, name, price, chubanDate) " +
"VALUES ('ISBN001', 'Book1', 19.99, #2021-01-01#)";
stmt.executeUpdate(sql);
sql = "INSERT INTO bookList (ISBN, name, price, chubanDate) " +
"VALUES ('ISBN002', 'Book2', 29.99, #2021-01-02#)";
stmt.executeUpdate(sql);
// ... add 8 more records ...
stmt.close();
conn.close();
System.out.println("Records created successfully");
} catch (Exception e) {
System.err.println(e.getClass().getName() + ": " + e.getMessage());
}
}
}
```
接下来,你需要编写Java代码来从bookList表格中随机查询5条记录,并计算它们的price字段值的平均值。这可以使用以下的代码实现:
```java
import java.sql.*;
public class QueryData {
public static void main(String[] args) {
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
String url = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=Book.accdb";
Connection conn = DriverManager.getConnection(url);
Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
String sql = "SELECT * FROM bookList ORDER BY Rnd(-(100000*ID)*Time())";
ResultSet rs = stmt.executeQuery(sql);
int[] rows = getRandomNumber(rs.last(), 5);
double sum = 0;
for (int i = 0; i < rows.length; i++) {
rs.absolute(rows[i]);
sum += rs.getDouble("price");
}
double avg = sum / rows.length;
System.out.println("Average price: " + avg);
rs.close();
stmt.close();
conn.close();
} catch (Exception e) {
System.err.println(e.getClass().getName() + ": " + e.getMessage());
}
}
private static int[] getRandomNumber(int max, int amount) {
int[] result = new int[amount];
for (int i = 0; i < amount; i++) {
result[i] = (int) (Math.random() * max) + 1;
for (int j = 0; j < i; j++) {
if (result[i] == result[j]) {
i--;
break;
}
}
}
return result;
}
}
```
最后,你需要使用Java Swing库中的JTable和JScrollPane组件来显示查询结果。这可以使用以下的代码实现:
```java
import java.sql.*;
import javax.swing.*;
import javax.swing.table.*;
public class DisplayData extends JFrame {
private JTable table;
private JScrollPane scrollPane;
private JTextField avgField;
public DisplayData() {
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
String url = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=Book.accdb";
Connection conn = DriverManager.getConnection(url);
Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
String sql = "SELECT * FROM bookList ORDER BY Rnd(-(100000*ID)*Time())";
ResultSet rs = stmt.executeQuery(sql);
int[] rows = getRandomNumber(rs.last(), 5);
Object[][] data = new Object[rows.length][4];
for (int i = 0; i < rows.length; i++) {
rs.absolute(rows[i]);
data[i][0] = rs.getString("ISBN");
data[i][1] = rs.getString("name");
data[i][2] = rs.getDouble("price");
data[i][3] = rs.getDate("chubanDate");
}
double sum = 0;
for (int i = 0; i < rows.length; i++) {
sum += (Double) data[i][2];
}
double avg = sum / rows.length;
avgField = new JTextField("Average price: " + avg);
avgField.setEditable(false);
table = new JTable(data, new Object[] {"ISBN", "Name", "Price", "Chuban Date"});
scrollPane = new JScrollPane(table);
getContentPane().add(scrollPane, "Center");
getContentPane().add(avgField, "South");
pack();
setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
setVisible(true);
rs.close();
stmt.close();
conn.close();
} catch (Exception e) {
System.err.println(e.getClass().getName() + ": " + e.getMessage());
}
}
private static int[] getRandomNumber(int max, int amount) {
int[] result = new int[amount];
for (int i = 0; i < amount; i++) {
result[i] = (int) (Math.random() * max) + 1;
for (int j = 0; j < i; j++) {
if (result[i] == result[j]) {
i--;
break;
}
}
}
return result;
}
public static void main(String[] args) {
new DisplayData();
}
}
```
这将显示一个带有JTable的窗体,其中包含5条随机记录和平均价格。
阅读全文