?? item.java
字號(hào):
package olts.logicoperation;
import java.sql.*;
import java.util.ArrayList;
import java.util.Random;
import olts.data.DatabaseAccess;
import olts.presentation.DatabaseTableColumn;
/**
* 負(fù)責(zé)完成試題的插入、刪除、修改和查找等功能的類
* @author Xia Wei
* @author 2007.12.18
* @version 1.0
*/
public class Item {
private final static String TABLE_NAME = "Item";
private DatabaseAccess databaseAccess = null;
private DatabaseTableColumn[] columns = null; //DB表列信息
/**
* 建立與數(shù)據(jù)庫的連接
*/
public void open() throws Exception {
try {
databaseAccess = new DatabaseAccess("OnlineTestSystem");
} catch (SQLException exc) {
throw new Exception("不能打開試題數(shù)據(jù)庫!");
}
// 初始化表列信息
initializeColumns();
}
/**
* 獲取有關(guān)數(shù)據(jù)庫表的列信息
*/
public DatabaseTableColumn[] getColumns() { return columns; }
/**
* 獲取關(guān)鍵字段的信息
*/
public String getKeyField() { return "id"; }
/**
* 獲取難度系數(shù)字段的信息
* @return 難度系數(shù)字段的信息
*/
public String getDifficultyField() { return "difficulty"; }
/**
* 添加一個(gè)試題記錄
* @param aItem 試題記錄對(duì)象，添加的試題記錄從此載入
*/
public void add(ItemRecord aItem) throws Exception {
// 查找是否已經(jīng)存在相同編號(hào)的試題，如果已經(jīng)存在則不能再插入
if (findById(aItem.id) != null)
throw new Exception ("代碼為" + aItem.id + "的試題已經(jīng)存在!");
String sql = "INSERT INTO " + TABLE_NAME +
" (id, itemkinds, difficulty, testtime, content, answer, score) " +
" VALUES (?, ?, ?, ?, ?, ?, ?)";
try {
databaseAccess.prepare(sql);
databaseAccess.setParameter(1, aItem.id);
databaseAccess.setParameter(2, aItem.itemkinds);
databaseAccess.setParameter(3, aItem.difficulty);
databaseAccess.setParameter(4, aItem.testtime);
databaseAccess.setParameter(5, aItem.content);
databaseAccess.setParameter(6, aItem.answer);
databaseAccess.setParameter(7, aItem.score);
databaseAccess.preparedUpdate();
} catch (SQLException exc) {
exc.printStackTrace();
throw new Exception("插入編號(hào)為" + aItem.id + "的試題失敗!您輸入的試題編號(hào)已被使用,請(qǐng)更換試題編號(hào)重新輸入.");
}
}
/**
* 修改試題的信息，試題信息從試題記錄對(duì)象參數(shù)中載入
* @param aItem 試題的信息
* @throws Exception
*/
public void update(ItemRecord aItem) throws Exception {
String sql = "UPDATE " + TABLE_NAME +
" SET itemkinds = ?, difficulty = ?, testtime = ?, " +
"content = ?, answer = ?, score = ? WHERE id = ?";
try {
databaseAccess.prepare(sql);
databaseAccess.setParameter(1, aItem.id);
databaseAccess.setParameter(2, aItem.itemkinds);
databaseAccess.setParameter(3, aItem.difficulty);
databaseAccess.setParameter(4, aItem.testtime);
databaseAccess.setParameter(5, aItem.content);
databaseAccess.setParameter(6, aItem.answer);
databaseAccess.setParameter(7, aItem.score);
databaseAccess.preparedUpdate();
} catch (SQLException exc) {
exc.printStackTrace();
throw new Exception("修改代碼為" + aItem.id + "的試題失敗!");
}
}
/**
* 通過試題編號(hào)、字段名稱、新值，修改更新試題記錄某字段值，即某單元格的值。
* @param itmeID 要修改的試題的編號(hào)
* @param modifyField 要修改的字段
* @param value 該字段的新值
* @throws Exception
*/
public void update(String itmeID, String modifyField, Object value)
throws Exception {
// 獲取字段在SQL中的編碼類型
int sqlType = getFieldSqlType(modifyField);
String sql = "UPDATE " + TABLE_NAME + " SET " + modifyField + " = ? " +
" WHERE id = ?";
try {
databaseAccess.prepare(sql);
databaseAccess.setParameter(1, value, sqlType);
databaseAccess.setParameter(2, itmeID);
databaseAccess.preparedUpdate();
} catch (SQLException exc) {
exc.printStackTrace();
throw new Exception("修改編號(hào)為" + itmeID + "的試題信息失敗!");
}
}
/**
* 通過試題編號(hào)，刪除編號(hào)所在的試題記錄
* @param itemId 試題編號(hào)
* @throws Exception
*/
public void remove(String itemId) throws Exception {
String sql = "DELETE FROM " + TABLE_NAME + " WHERE id = ?";
try {
databaseAccess.prepare(sql);
databaseAccess.setParameter(1, itemId);
databaseAccess.preparedUpdate();
} catch (SQLException exc) {
exc.printStackTrace();
throw new Exception("刪除代碼為" + itemId + "的試題信息失敗!");
}
}
// 功能：。調(diào)用數(shù)據(jù)層的功能將SQL查詢語句返回的查詢結(jié)果集處理成類似二維數(shù)組的形式。
// 此二維數(shù)組形式的結(jié)果作為Swing表格的數(shù)據(jù)模型
// 返回：以列表形式返回所有試題，試題信息放在ArrayList中，其中每一個(gè)元素為一個(gè)Object[]數(shù)組，
// 該數(shù)組的長度與columns.length相同
/**
* 查找所有試題
* @return 以列表形式返回所有試題，試題信息放在ArrayList中，其中每一個(gè)元素為一個(gè)Object[]數(shù)組
* @throws Exception
*/
public ArrayList findAll() throws Exception {
String sql = "SELECT * FROM " + TABLE_NAME;
ResultSet result = null;
try {
result = databaseAccess.query(sql);
} catch (SQLException exc) {
exc.printStackTrace();
throw new Exception("查找所有的試題信息失敗!");
}
// 將數(shù)據(jù)庫中的數(shù)據(jù)裝入ArrayList類型的data
ArrayList data = new ArrayList();
while (result.next()) {
Object[] record = new Object[columns.length];
for (int i = 0; i < columns.length; i++) {
record[i] = result.getObject(columns[i].getField());
}
data.add(record);
}
result.close();
return data;
}
/**
* 按難度系數(shù)取題
* @param difficulty 難度系數(shù)
* @return 以列表形式返回所有試題，試題信息放在ArrayList中，其中每一個(gè)元素為一個(gè)Object[]數(shù)組
* @throws Exception
*/
public ArrayList findItemByDifficulty(String difficulty) throws Exception {
ArrayList data = null;
if (difficulty.equalsIgnoreCase("0.1")||difficulty.equalsIgnoreCase("0.2")||difficulty.equalsIgnoreCase("0.3")){
String sql = "SELECT * FROM " + TABLE_NAME + " WHERE difficulty = ?";
ResultSet result = null;
try {
databaseAccess.prepare(sql);
databaseAccess.setParameter(1, difficulty);
result = databaseAccess.preparedQuery();
} catch (SQLException exc) {
exc.printStackTrace();
throw new Exception("按試題難度查找試題失敗!");
}
// 將數(shù)據(jù)庫中的數(shù)據(jù)裝入ArrayList類型的data
data = new ArrayList();
while (result.next()) {
Object[] record = new Object[columns.length];
for (int i = 0; i < columns.length; i++) {
record[i] = result.getObject(columns[i].getField());
}
data.add(record);
}
result.close();
}
return data;
}
/**
* 隨機(jī)查找若干道試題
* @param amount 試題數(shù)量
* @return 查找的若干道試題
* @throws Exception
*/
public ArrayList findSomeItemRandom(int amount) throws Exception {
Random ran = new Random();
int num=ran.nextInt(100);
String sql="select top " + amount + " * from " + TABLE_NAME + " order by rnd(-"+num+"*id)";
ResultSet result = null;
try {
result = databaseAccess.query(sql);
} catch (SQLException exc) {
exc.printStackTrace();
throw new Exception("隨機(jī)查找的試題信息失敗!");
}
// 將數(shù)據(jù)庫中的數(shù)據(jù)裝入ArrayList類型的data
ArrayList data = new ArrayList();
while (result.next()) {
Object[] record = new Object[columns.length];
for (int i = 0; i < columns.length; i++) {
record[i] = result.getObject(columns[i].getField());
}
data.add(record);
}
result.close();
return data;
}
/**
* 根據(jù)試題編號(hào)查找某一試題
* @param itemId 試題編號(hào)
* @return 以列表形式返回，每個(gè)列表元素為一個(gè)Object[]數(shù)組，該數(shù)組的長度與columns.length相同
* @throws Exception
*/
public ArrayList findByIdVersion2(String itemId) throws Exception {
String sql = "SELECT * FROM " + TABLE_NAME + " WHERE id = ?";
ResultSet result = null;
try {
databaseAccess.prepare(sql);
databaseAccess.setParameter(1, itemId);
result = databaseAccess.preparedQuery();
} catch (SQLException exc) {
exc.printStackTrace();
throw new Exception("隨機(jī)查找的試題信息失敗!");
}
// 將數(shù)據(jù)庫中的數(shù)據(jù)裝入ArrayList類型的data
ArrayList data = new ArrayList();
while (result.next()) {
Object[] record = new Object[columns.length];
for (int i = 0; i < columns.length; i++) {
record[i] = result.getObject(columns[i].getField());
}
data.add(record);
}
result.close();
return data;
}
/**
* 根據(jù)試題編號(hào)查找某一試題
* @param itemId 試題編號(hào)
* @return 以試題記錄結(jié)構(gòu)返回，這些信息放在ItemRecord對(duì)象中，如果要查找的試題不存在則返回null
* @throws Exception
*/
public ItemRecord findById(String itemId) throws Exception {
String sql = "SELECT * FROM " + TABLE_NAME + " WHERE id = ?";
ResultSet result = null;
try {
databaseAccess.prepare(sql);
databaseAccess.setParameter(1, itemId);
result = databaseAccess.preparedQuery();
} catch (SQLException exc) {
exc.printStackTrace();
throw new Exception("查找代碼為" + itemId + "的試題信息失敗!");
}
ItemRecord record = null;
if (result.next()) {
record = new ItemRecord();
try {
record.id = result.getString("id");
record.itemkinds = result.getString("itemkinds");
record.difficulty = result.getString("difficulty");
record.testtime = result.getString("testtime");
record.content = result.getString("content");
record.answer = result.getString("answer");
record.score = result.getInt("score");
} catch (SQLException exc) {
exc.printStackTrace();
throw new Exception("裝入編號(hào)為" + itemId + "的試題信息失敗!");
}
}
return record;
}
/**
* ItemRecord對(duì)象到數(shù)組的數(shù)據(jù)結(jié)構(gòu)轉(zhuǎn)化
* @param record ItemRecord對(duì)象
* @return Object[]數(shù)組，每個(gè)元素對(duì)應(yīng)一個(gè)字段值，順序與DB中表的相同
*/
public Object[] recordToArray(ItemRecord record) {
Object[] array = new Object[columns.length];
for (int i = 0; i < columns.length; i++) {
String fieldName = columns[i].getField();
if (fieldName.equals("id")) array[i] = record.id;
else if (fieldName.equals("itemkinds")) array[i] = record.itemkinds;
else if (fieldName.equals("difficulty")) array[i] =record.difficulty;
else if (fieldName.equals("testtime")) array[i] = record.testtime;
else if (fieldName.equals("content")) array[i] = record.content;
else if (fieldName.equals("answer")) array[i] = record.answer;
else if (fieldName.equals("score")) array[i] = record.score;
else System.out.println("發(fā)生了內(nèi)部錯(cuò)誤，遇到了不可識(shí)別的字段名：" + fieldName);
}
return array;
}
/**
* 隨機(jī)查找一道試題
* @return 以試題記錄返回
* @throws Exception
*/
public ItemRecord findOneItemRandom() throws Exception {
Random ran = new Random();
int num=ran.nextInt(100);
String sql="select top 1 * from " + TABLE_NAME + " order by rnd(-"+num+"*id)";
ResultSet result = null;
try {
result = databaseAccess.query(sql);
} catch (SQLException exc) {
exc.printStackTrace();
throw new Exception("查找所有的試題信息失敗!");
}
ItemRecord record = null;
if (result.next()) {
record = new ItemRecord();
try {
record.id = result.getString("id");
record.itemkinds = result.getString("itemkinds");
record.difficulty = result.getString("difficulty");
record.testtime = result.getString("testtime");
record.content = result.getString("content");
record.answer = result.getString("answer");
record.score = result.getInt("score");
} catch (SQLException exc) {
exc.printStackTrace();
throw new Exception("裝入的試題信息失敗!");
}
}
return record;
}
/**
* 關(guān)閉數(shù)據(jù)庫連接
* @throws Exception
*/
public void close() throws Exception {
try {
databaseAccess.close();
} catch (Exception exc) {
exc.printStackTrace();
throw new Exception("關(guān)閉數(shù)據(jù)庫" + TABLE_NAME + "失敗!");
}
}
/**
* 初始化數(shù)據(jù)表的字段信息
*/
private void initializeColumns() {
columns = new DatabaseTableColumn[7];
try {
// 注意JAVA語言的數(shù)據(jù)類型與SQL里的類型的匹配！
columns[0] = new DatabaseTableColumn("試題編號(hào)", "id",
Class.forName("java.lang.String"), 20, Types.CHAR);
columns[1] = new DatabaseTableColumn("題型標(biāo)識(shí)", "itemkinds",
Class.forName("java.lang.String"), 30, Types.VARCHAR);
columns[2] = new DatabaseTableColumn("難度系數(shù)", "difficulty",
Class.forName("java.lang.String"), 20, Types.VARCHAR);
columns[3] = new DatabaseTableColumn("答題時(shí)間", "testtime",
Class.forName("java.lang.String"), 20, Types.VARCHAR);
columns[4] = new DatabaseTableColumn("題目內(nèi)容", "content",
Class.forName("java.lang.String"), 100, Types.LONGVARCHAR);
columns[5] = new DatabaseTableColumn("參考答案", "answer",
Class.forName("java.lang.String"), 20, Types.LONGVARCHAR);
columns[6] = new DatabaseTableColumn("所占分值", "score",
Class.forName("java.lang.Integer"), 20, Types.INTEGER);
} catch (ClassNotFoundException exc) {
exc.printStackTrace();
}
}
/**
* 由字段名獲得字段的SQL類型
* @param fieldName 字段名
* @return 字段的SQL類型
*/
private int getFieldSqlType(String fieldName) {
for (int i = 0; i < columns.length; i++) {
if (fieldName.equals(columns[i].getField()))
return columns[i].getSqlType();
}
System.out.println("沒有關(guān)于字段" + fieldName + "的類型信息!");
return Types.VARCHAR;
}
}
?? 快捷鍵說明
復(fù)制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號(hào)
Ctrl + =
減小字號(hào)
Ctrl + -