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){}}}