TA貢獻1887條經(jīng)驗 獲得超5個贊
javaDB其實就是Derby，它并不是一個新的數(shù)據(jù)庫產(chǎn)品,它是由IBM捐獻給Apache的DB項目的一個純Java數(shù)據(jù)庫,JDK6.0里面帶的這個Derby的版本是 10.2.1.7,支持存儲過程和觸發(fā)器;有兩種運行模式,一種是作為嵌入式數(shù)據(jù)庫,另一種是作為網(wǎng)絡數(shù)據(jù)庫,前者的數(shù)據(jù)庫服務器和客戶端都在同一個 JVM里面運行,后者允許數(shù)據(jù)庫服務器端和客戶端不在同一個JVM里面,而且允許這兩者在不同的物理機器上.值得注意的是JDK6里面的這個Derby支持JDK6的新特性JDBC 4.0規(guī)范(JSR 221),現(xiàn)在我們如果要練習JDBC的用法,沒有必要單獨裝一個數(shù)據(jù)庫產(chǎn)品了,直接用Derby就行.
1、本身沒有操作界面，可以用第三方工具來管理（也就是你說的操作界面），Aqua Data Studio 具備管理功能的用于 Apache Derby 關系數(shù)據(jù)庫的管理工具和數(shù)據(jù)庫查詢工具。直觀管理功能讓用戶能夠瀏覽和修改數(shù)據(jù)庫結構，包括架構對象和數(shù)據(jù)庫存儲，以及維護數(shù)據(jù)庫安全。集成查詢工具讓您能夠迅速創(chuàng)建、編輯和執(zhí)行 SQL 查詢與腳本。Aqua Data Studio 進一步提供導入與導出工具，從而輕松地將數(shù)據(jù)移入和移出不同的數(shù)據(jù)格式及 Apache Derby 數(shù)據(jù)庫。集成在這些工具內的是庫瀏覽器 (Repository Browser)，擁有 CVS 和 Subversion (SVN) 的完整來源控制客戶端。
2、兩者的區(qū)別，簡單的說，就是javaDB是一個簡化輕量級數(shù)據(jù)庫，適合小型系統(tǒng)的小規(guī)模測試用，完全可以跑在內存里的數(shù)據(jù)庫，它只有3M大小，而MySQL則是可以應用部署大型系統(tǒng)的數(shù)據(jù)庫，功能更多更全，也更穩(wěn)定，是用范圍更廣。
3、下面是個使用derby的簡單例子:
首先導入JAR包:derby.jar,如果你裝的是JDK6,在C:\Program Files\Sun\JavaDB\lib目錄下就可以找到.
然后就要創(chuàng)建數(shù)據(jù)庫了:
代碼
private Connection getConnection() throws SQLException {
Connection connection = DriverManager
.getConnection("jdbc:derby:userDB;create=true;user=test;password=test");
connection.setAutoCommit(false);
return connection;
}
其中userDB是要連接數(shù)據(jù)庫的名字,create=true表示如果該數(shù)據(jù)庫不存在,則創(chuàng)建該數(shù)據(jù)庫,如果數(shù)據(jù)庫存在,則用用戶user=test;密碼password=test連接數(shù)據(jù)庫.
有了數(shù)據(jù)庫,接下來該建表了:
代碼
private void createTable(Connection connection) throws SQLException {
Statement statement = connection.createStatement();
String sql = "create table USERS("
+ " ID BIGINT not null generated by default as identity,"
+ " USER_NAME VARCHAR(20) not null,"
+ " PASSWORD VARCHAR(20),"
+ " constraint P_KEY_1 primary key (ID))";
statement.execute(sql);
sql = "create unique index USER_NAME_INDEX on USERS ("
+ " USER_NAME ASC)";
statement.execute(sql);
statement.close();
}
創(chuàng)建了 USERS表,包括ID,USER_NAME,PASSWORD三個列,其中ID是主鍵,其中generated by default as identity 的作用類似sequence,identity是定義自動加一的列,
GENERATED BY ALWAYS AS IDENTITY
GENERATED BY DEFAULT AS IDENTITY
By always和by default是說明生成這個IDENTITY的方式。
By always是完全由系統(tǒng)自動生成。
by default是可以由用戶來指定一個值。
編寫與USERS表對應的javabean(這個就不多說了),:
代碼
public class User implements Serializable {
/**
*
*/
private static final long serialVersionUID = 1L;
private Long id;
private String userName;
private String password;
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getUserName() {
return userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
}
接下來就可以就數(shù)據(jù)庫進行增刪改查的操作了:
插入數(shù)據(jù):
代碼
private void create(User user) {
Connection connection = null;
try {
connection = this.getConnection();
PreparedStatement statement = connection
.prepareStatement("insert into users (user_name,password) values(?,?)");
int index = 1;
statement.setString(index++, user.getUserName());
statement.setString(index++, user.getPassword());
statement.execute();
user.setId(this.getId(connection));
connection.commit();
} catch (SQLException e) {
rollback(connection);
throw new RuntimeException(e);
} finally {
if (connection != null) {
close(connection);
}
}
}
代碼
private Long getId(Connection connection) throws SQLException {
CallableStatement callableStatement = connection
.prepareCall("values identity_val_local()");
ResultSet resultSet = callableStatement.executeQuery();
resultSet.next();
Long id = resultSet.getLong(1);
resultSet.close();
callableStatement.close();
return id;
}
getId方法是獲得系統(tǒng)默認的id值,是通過 identity_val_local()獲得的,而函數(shù)IDENTITY_VAL_LOCAL()則可以在INSERT語句執(zhí)行之后，為我們返回剛才系統(tǒng)為id所產(chǎn)生的值.感覺還是有點想sequence的curr_val.
修改數(shù)據(jù):
代碼
private void update(User user) {
Connection connection = null;
try {
connection = this.getConnection();
PreparedStatement statement = connection
.prepareStatement("update users set user_name=?,password=? where id=?");
int index = 1;
statement.setString(index++, user.getUserName());
statement.setString(index++, user.getPassword());
statement.setLong(index++, user.getId());
statement.execute();
connection.commit();
} catch (SQLException e) {
rollback(connection);
throw new RuntimeException(e);
} finally {
if (connection != null) {
close(connection);
}
}
}
刪除數(shù)據(jù):
代碼
public void delete(Long id) {
Connection connection = null;
try {
connection = this.getConnection();
PreparedStatement statement = connection
.prepareStatement("delete from users where id=?");
statement.setLong(1, id);
statement.execute();
connection.commit();
} catch (SQLException e) {
rollback(connection);
throw new RuntimeException(e);
} finally {
if (connection != null) {
close(connection);
}
}
}
查詢數(shù)據(jù):
代碼
public User findById(Long id) {
Connection connection = null;
try {
connection = this.getConnection();
PreparedStatement statement = connection
.prepareStatement("select user_name,password from users where id=?");
statement.setLong(1, id);
ResultSet resultSet = statement.executeQuery();
User user = null;
if (resultSet.next()) {
user = new User();
user.setId(id);
user.setUserName(resultSet.getString("user_name"));
user.setPassword(resultSet.getString("password"));
}
resultSet.close();
statement.close();
connection.commit();
return user;
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
if (connection != null) {
close(connection);
}
}
}
舉報