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 的简单例子:
首先启动数据库。创建一个简单的数据库
- 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.execute(sql);
- 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;
- }
- }
接下来就可以就数据库进行增删改查的操作了:
插入数据:
- 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 方法是获得系统默认的id 值, 是通过identity_val_local() 获得的, 而函数IDENTITY_VAL_LOCAL() 则可以在INSERT 语句执行之后,为我们返回刚才系统为id 所产生的值. 感觉还是有点想sequence 的curr_val.
修改数据:
- 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);
- }
- }
- }
删除数据:
- 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);
- }
- }
- }
查询数据:
- 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);
- }
- }
- }
以上就是derby 的简单操作.