derby数据库常用操作

数据库创建脚本

Connect 'jdbc:derby:dburl;create=true;dataEncryption=true;bootPassword=bootpass;user=user';

AUTOCOMMIT OFF;
----
CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.connection.requireAuthentication', 'true');
CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.authentication.provider', 'BUILTIN');
CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.user.adminuser', 'adminpass');
CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.user.commonuser', 'userpass');
CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.user.guessuser', 'guesspass');
CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.propertiesOnly', 'true');
CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.defaultConnectionMode', 'noAccess');
CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.fullAccessUsers','adminuser,commonuser');
CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.readOnlyAccessUsers', 'guessuser');
CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.stream.error.file', 'logs/derby.log');
CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.infolog.append', 'true');
----

create table ...

 

 

查看表的主键

SELECT CONSTRAINTNAME FROM SYS.SYSCONSTRAINTS conts join SYS.SYSTABLES tables on conts.TABLEID=tables.TABLEID where tables.TABLENAME='表名'

 

 

关闭数据库连接
public static void shutdownDatabase() { try { DriverManager.getConnection("jdbc:derby:;shutdown=true"); } catch (SQLException se) { e.printStackTrace(); } }

 

备份数据库

/** * 备份数据库 * * @param dir * 备份到的目录 */ public static void backupDatabase(String dir) { shutdownDatabase(); try { Connection conn = getConnection(); CallableStatement cs = conn .prepareCall("CALL SYSCS_UTIL.SYSCS_BACKUP_DATABASE(?)"); cs.setString(1, dir); cs.execute(); cs.close(); conn.close(); } catch (SQLException e) { e.printStackTrace(); } }

 

恢复备份的数据库

 /** * 恢复数据库 * * @param dir * 从哪个文件夹恢复 */ public static String restoreDatabase(String dir) { Connection conn = null; try { shutdownDatabase(); Properties props = new Properties(); props.put(,); props.put(,); props.put(,); String dbURL = "jdbc:derby:dburl;restoreFrom=" + dir; Class.forName(DRIVER).newInstance(); conn = DriverManager.getConnection(dbURL, props); return null; } catch (Exception ex) { } finally { try { conn.close(); } catch (Exception ex) { } } }