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的简单操作.