derby并不是一個新的資料庫産品,它是由ibm捐獻給apache的db項目的一個純java資料庫,jdk6.0裡面帶的這個derby的版本是10.2.1.7,支援存儲過程和觸發器;有兩種運作模式,一種是作為嵌入式資料庫,另一種是作為網絡資料庫,前者的資料庫伺服器和用戶端都在同一個jvm裡面運作,後者允許資料庫伺服器端和用戶端不在同一個jvm裡面,而且允許這兩者在不同的實體機器上.值得注意的是jdk6裡面的這個derby支援jdk6的新特性jdbc 4.0規範(jsr 221),現在我們如果要練習jdbc的用法,沒有必要單獨裝一個資料庫産品了,直接用derby就行.
下面是個使用derby的簡單例子:
首先啟動資料庫。建立一個簡單的資料庫
java代碼
private connection getconnection() throws sqlexception {
connection connection = drivermanager
.getconnection("jdbc:derby:userdb;create=true;user=test;password=test");
connection.setautocommit(false);
return connection;
}
private connection getconnection() throws sqlexception {
connection connection = drivermanager
.getconnection("jdbc:derby:userdb;create=true;user=test;password=test");
connection.setautocommit(false);
return connection;
}
其中userdb是要連接配接資料庫的名字,create=true表示如果該資料庫不存在,則建立該資料庫,如果資料庫存在,則用使用者user=test;密碼password=test連接配接資料庫.
有了資料庫,接下來該建表了:
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.close();
}
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.close();
建立了 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是完全由系統自動生成。
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;
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;
接下來就可以就資料庫進行增删改查的操作了:
插入資料:
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 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;
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();
getid方法是獲得系統預設的id值,是通過identity_val_local()獲得的,而函數identity_val_local()則可以在insert語句執行之後,為我們傳回剛才系統為id所産生的值.感覺還是有點想sequence的curr_val.
修改資料:
private void update(user user) {
.preparestatement("update users set user_name=?,password=? where id=?");
statement.setlong(index++, user.getid());
private void update(user user) {
.preparestatement("update users set user_name=?,password=? where id=?");
statement.setlong(index++, user.getid());
删除資料:
public void delete(long id) {
.preparestatement("delete from users where id=?");
statement.setlong(1, id);
public void delete(long id) {
.preparestatement("delete from users where id=?");
statement.setlong(1, id);
查詢資料:
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);
public user findbyid(long id) {
.preparestatement("select user_name,password from users where 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();
return user;
以上就是derby的簡單操作.