Derby使用的基本说明
博客专区 > YuMG 的博客 > 博客详情
Derby使用的基本说明
YuMG 发表于2年前
Derby使用的基本说明
  • 发表于 2年前
  • 阅读 48
  • 收藏 0
  • 点赞 1
  • 评论 0

腾讯云 十分钟定制你的第一个小程序>>>   

关于Derby使用的基本说明

1.特点优势

  1. Java based, Free software, Open source, Apache license version 2.0
  2. 全功能RDBMS
  3. 可嵌入式运行,占用资源少
  4. 轻量级的持久化文件,平台无关

2.概念与实践

2.1 嵌入式与多用户访问

这里所谓的“嵌入式”是指:Derby提供了一种可行性,使得应用级程序中可以很方便地启动一个数据库引擎模块,提供数据库功能服务。如此一来,应用与数据库就不再是传统的分布式关系,而变成了嵌入关系。技术上讲,数据库不再是一个独立的进程,而是存在并受控于应用的进程中。

Derby是一款java程序,使用它的方式其实就是使用其提供的jar。

简单地用Derby作为嵌入式数据库使用,仅需使用derby.jar即可。derby.jar中包括一个数据库引擎和一个嵌入式的JDBC驱动(进程内通信的JDBC Driver)。应用使用这个驱动与内嵌于自身进程的数据库引擎交互即可。org.apache.derby.jdbc.EmbeddedDriver是该驱动的名称。

以下是连接到内嵌数据库的标准方式:

Connection conn = DriverManager.getConnection("jdbc:derby:sample");

那么数据库是什么时候启动的呢?当第一次连接创建的时候。最简单的一个样例程序如下所示:

public class SimpleApp
{
    private String protocol = "jdbc:derby:";
     
    public static void main(String[] args)
    {
        new SimpleApp().go(args);
        System.out.println("SimpleApp finished");
    }
    
    void go(String[] args)
    {

        System.out.println("SimpleApp starting in " + framework + " mode");

        /* We will be using Statement and PreparedStatement objects for
         * executing SQL. These objects, as well as Connections and ResultSets,
         * are resources that should be released explicitly after use, hence
         * the try-catch-finally pattern used below.
         * We are storing the Statement and Prepared statement object references
         * in an array list for convenience.
         */
         
        Connection conn = null;
        ArrayList<Statement> statements = new ArrayList<Statement>(); // list of Statements, PreparedStatements
        PreparedStatement psInsert;
        PreparedStatement psUpdate;
        Statement s;
        ResultSet rs = null;
        try
        {
            Properties props = new Properties(); // connection properties
            // providing a user name and password is optional in the embedded
            // and derbyclient frameworks
            props.put("user", "user1");
            props.put("password", "user1");

            /* By default, the schema APP will be used when no username is
             * provided.
             * Otherwise, the schema name is the same as the user name (in this
             * case "user1" or USER1.)
             *
             * Note that user authentication is off by default, meaning that any
             * user can connect to your database using any password. To enable
             * authentication, see the Derby Developer's Guide.
             */

            String dbName = "derbyDB"; // the name of the database

            /*
             * This connection specifies create=true in the connection URL to
             * cause the database to be created when connecting for the first
             * time. To remove the database, remove the directory derbyDB (the
             * same as the database name) and its contents.
             *
             * The directory derbyDB will be created under the directory that
             * the system property derby.system.home points to, or the current
             * directory (user.dir) if derby.system.home is not set.
             */
            conn = DriverManager.getConnection(protocol + dbName
                    + ";create=true", props);

            System.out.println("Connected to and created database " + dbName);

            // We want to control transactions manually. Autocommit is on by
            // default in JDBC.
            conn.setAutoCommit(false);

            /* Creating a statement object that we can use for running various
             * SQL statements commands against the database.*/
            s = conn.createStatement();
            statements.add(s);

            // We create a table...
            s.execute("create table location(num int, addr varchar(40))");
            System.out.println("Created table location");
            
            /*
               We commit the transaction. Any changes will be persisted to
               the database now.
             */
            conn.commit();
            System.out.println("Committed the transaction");
            
            try
                {
                    // the shutdown=true attribute shuts down Derby
                    DriverManager.getConnection("jdbc:derby:;shutdown=true");

                    // To shut down a specific database only, but keep the
                    // engine running (for example for connecting to other
                    // databases), specify a database in the connection URL:
                    //DriverManager.getConnection("jdbc:derby:" + dbName + ";shutdown=true");
                }
                catch (SQLException se)
                {
                    if (( (se.getErrorCode() == 50000)
                            && ("XJ015".equals(se.getSQLState()) ))) {
                        // we got the expected exception
                        System.out.println("Derby shut down normally");
                        // Note that for single database shutdown, the expected
                        // SQL state is "08006", and the error code is 45000.
                    } else {
                        // if the error code or SQLState is different, we have
                        // an unexpected exception (shutdown failed)
                        System.err.println("Derby did not shut down normally");
                        printSQLException(se);
                    }
                }
        }
        catch (SQLException sqle)
        {
            printSQLException(sqle);
        }
        finanly{
            //Connection
            try {
                if (conn != null) {
                    conn.close();
                    conn = null;
                }
            } catch (SQLException sqle) {
                printSQLException(sqle);
            }
        }
}

以上程序以local方式启动了Derby,并以local方式连接上并创建了一个表。这种方式启动的嵌入式数据库只允许从宿主进程内部进行连接。从进程外部无法进行访问。这种“纯”嵌入的方式只是使用Derby的一种最简单的场景。

关于Derby的的部署使用,存在一个Framework的抽象概念。在上面的代码示例中,Derby的Framework就可以看作是SimpleApp这个类。当然这里只是在进程内部使用local JDBC驱动来访问local的数据库。那么,可以设想,如果SimpleApp变成一个稍微复杂一些的,可以代理对外数据库服务的ServerApp的话,那么Derby就变成了一个可以多用户访问的数据库了。

将Derby嵌入到一个Server Framework中,继而向外部提供多用户的网络连接访问,可以有以下几种方式:

  • 使用Derby自带的Network Server
    • 最容易便捷的方式,Derby提供了相关工具包,以编程API或者配置的方式直接使用即可。对外提供JDBC方式的连接与访问方法。
  • 自定义FrameWork
    • 最灵活,可以自定义任何的连接与访问方式。
  • 购买商业容器
    • 使用商业JEE容器,如IBM WebSphere Application Server。(具体没研究)

Derby Network Server 是Derby发布版本中的一部分,具体就是derbynet.jar

如果想使用Derby Network Server来创建数据库服务的话,需要依赖derby.jarderbynet.jar两个jar包。使用相关API启动Network Server的方法如下:

import org.apache.derby.drda.NetworkServerControl;
import java.net.InetAddress;
NetworkServerControl server = new NetworkServerControl (InetAddress.getByName("localhost"),1527);
server.start(null);

一个较为完整的示例程序如下:

public class ServerApp {

	public static final String DERBY_CLIENT_DRIVER = "org.apache.derby.jdbc.ClientDriver";

	// network server control specific
	private static int NETWORKSERVER_PORT=1621;

	// To connect to Derby Network Server
	// This URL describes the target database for type 4 connectivity
	// Notice that the properties may be established via the URL syntax
    // URL for the Derby client JDBC driver.
	private static final String DERBY_CLIENT_URL= "jdbc:derby://localhost:"+NETWORKSERVER_PORT+"/ServerAppdb;create=true;";
  
    // Default to using the Derby Client JDBC Driver for database connections
    String url = DERBY_CLIENT_URL;
    String jdbcDriver = DERBY_CLIENT_DRIVER;

	public static void main(String[] args) throws Exception {
        new ServerApp().startSample(args);
    }
    
	public void startSample(String[] args) throws Exception {
	  NetworkServerControl nwServer;
	  Connection conn = null;
	  PrintWriter pw = null;

	  try  {

		pw = new PrintWriter(System.out,true);	// to print messages
		pw.println("Using JDBC driver: " + jdbcDriver);

		/* Start - In order to start the network server do the following
		   In case you want to start the server as a script or another program
		   comment out the next block of code (i.e. until the comment line 'End - network server started')
		   Also, comment out the 'Shutdown Derby Network Server' line of code at the bottom
		   In case you decide to comment out the starting of the network server, make sure that the
		   client thread is not making an embedded connection but instead making only a client connection.
		   Also note, the server logs messages to the file derby.log in the
		   directory you run this program
		 */

		 	{
				nwServer = new NetworkServerControl(InetAddress.getByName("localhost"),NETWORKSERVER_PORT);
				nwServer.start(pw);

				boolean knowIfServerUp = false; //do we know if server is ready to accept connections
				int numTimes = 5;

				// Test to see if server is ready for connections, for 15 seconds.
				while(!knowIfServerUp && (numTimes >0)) {
					try {
						// testing for connection to see if the network server is up and running
						// if server is not ready yet, this method will throw an exception
						numTimes--;
						nwServer.ping();
						knowIfServerUp = true;
					}
					catch(Exception e) {
						System.out.println("[ServerApp] Unable to obtain a connection to network server, trying again after 3000 ms.");
						Thread.currentThread().sleep(3000);
					}
				}
				if(!knowIfServerUp) {
					pw.println("[ServerApp] Exiting, since unable to connect to Derby Network Server.");
					pw.println("[ServerApp] Please try to increase the amount of time to keep trying to connect to the Server.");
					System.exit(1);
				}

				pw.println("[ServerApp] Derby Network Server started.");
			}
		/*End - network server started*/
        
        // Get database connection via DriverManager api
        // See Derby documentation for description of properties that may be set
		// in the context of the network server.
		Properties properties = new java.util.Properties();

		// The user and password properties are a must, required by JCC
		properties.setProperty("user","derbyuser");
		properties.setProperty("password","pass");
		try	{
			conn =  (Connection) DriverManager.getConnection(url, properties);
		} catch(Exception e) {
			pw.println("[ServerApp] Connection request unsuccessful, exception thrown was: ");
			pw.println("[ServerApp] Please check if all the jar files are in the classpath and the dbUrl is set correctly.");
			e.printStackTrace();
			System.exit(1);  //critical error, so exit
		}
		conn.close();
   	  } catch (Exception e) {
		  e.printStackTrace();
      }
   	  finally
   	  {
		if(pw != null) pw.close();
      }
	 }
}

以上程序中以NetworkServerControl 提供的API在程序中启动了Derby Network Server Framework,然后以远程连接的URL建立了一个测试性连接。

更多关于Network Server的工作描述,请参照《Derby Server and Administration Guide》 - Part1: Derby Server Guide

2.2 工作目录与属性配置

启动Derby时需要为其指定一个工作目录,Derby将在这个目录中维护持久化数据与日志、配置文件等。这个工作目录的路径由名称为derby.system.home的Java系统属性来指定(当指定不存在时将会新建目录)。如果该属性未指定那么将会使用启动应用进程时的当前目录。

系统工作目录中的内容如下:

<WORKING_DIR>:
  -derby.log
  -derby.properties
  -<DB_Name_1_Dir>
  -<DB_Name_2_Dir>
  -<DB_Name_3_Dir>
  ...

derby.log中是系统级别的日志输出。缺省该日志文件再重新启动后将会覆写。通过derby.infolog.append属性可以改变该行为。

derby.properties中是系统级别的属性配置定义。该配置文件是用户定制的。系统不会自动生成该文件。

其余的子目录是跟在Derby中创建的数据库一一对应的。每个数据库将对应一个子目录,目录名称为数据库名称。这些子目录中都将会有以下内容:

  • log directory
    • Contains files that make up the database transaction log, used internally for data recovery (not the same thing as the error log).
  • seg0 directory
    • Contains one file for each user table, system table, and index (known as conglomerates).
  • service.properties file
    • A text file with internal configuration information.

Derby本身可以进行特别多的属性设置,这些设置有两种作用域:

  1. 系统级别:涉及到所有数据库。可通过编程设置或者derby.properties文件设置。
  2. 数据库级别:仅涉及到指定数据库。设置并保存在对应的数据库中。

属性设置的优先级如下:

编程设置的系统级属性 > 数据库级别的属性设定 > debry.properties文件中的属性设定

所有的属性中也可分为动态或者静态属性,“动态”即运行时可变,“静态”即启动时设定运行期不可变,变更需重启。

更多的关于属性设定的内容参见《Derby Reference Manual》- "Derby property reference" ;《Derby Server and Administration Guide》 - "Setting Network Server properties"

2.3 Database Schema And User

Derby中有着比较清晰的Database与Schema之间的关系,一个DB中将会有多个Schema,每个Schema下才有Table、Index、Sequence等数据库对象。

User与Schema可以说是按名称的一一对应关系。比如,使用sccot账户登录后,那么其默认的Schema就是sccot。如果匿名(未提供用户名)连接,那么将使用缺省的APPSchema。

更多的关于安全管理的内容参见《Derby Security Guide》

3.功能限制说明

3.1 Limitations for database values

The following table lists limitations on various database values in Derby.

ValueLimit
Maximum columns in a table1,012
Maximum columns in a view5,000
Maximum indexes on a table32,767 or storage capacity
Maximum tables referenced in an SQL statement or a viewStorage capacity
Maximum elements in a select list1,012
Maximum predicates in a WHERE or HAVING clauseStorage capacity
Maximum number of columns in a GROUP BY clause32,677
Maximum number of columns in an ORDER BY clause1,012
Maximum number of prepared statementsStorage capacity
Maximum declared cursors in a programStorage capacity
Maximum number of cursors opened at one timeStorage capacity
Maximum number of constraints on a tableStorage capacity
Maximum level of subquery nestingStorage capacity
Maximum number of subqueries in a single statementStorage capacity
Maximum number of rows changed in a unit of workStorage capacity
Maximum constants in a statementStorage capacity
Maximum depth of cascaded triggers16

3.2 DATE, TIME, and TIMESTAMP limitations

The following table lists limitations on date, time, and timestamp values in Derby.

ValueLimit
Smallest DATE value0001-01-01
Largest DATE value9999-12-31
Smallest TIME value00:00:00
Largest TIME value24:00:00
Smallest TIMESTAMP value0001-01-01-00.00.00.000000
Largest TIMESTAMP value9999-12-31-23.59.59.999999

3.3 Limitations on identifier length

The following table lists limitations on identifier lengths in Derby.

IdentifierMaximum Number of Characters Allowed
Constraint name128
Correlation name128
Cursor name128
Data source column name128
Data source index name128
Data source name128
Savepoint name128
Schema name128
Unqualified column name128
Unqualified function name128
Unqualified index name128
Unqualified procedure name128
Parameter name128
Unqualified trigger name128
Unqualified table name, view name, stored procedure name128

3.4 Numeric limitations

The following table lists limitations on the numeric values in Derby.

ValueLimit
Largest negative INTEGER-2,147,483,648
Largest positive INTEGER2,147,483,647
Largest negative BIGINT-9,223,372,036,854,775,808
Largest positive BIGINT9,223,372,036,854,775,807
Largest negative SMALLINT-32,768
Largest positive SMALLINT32,767
Largest decimal precision31
Largest negative DOUBLE-1.7976931348623157E+308
Largest positive DOUBLE1.7976931348623157E+308
Smallest negative normalized DOUBLE-2.2250738585072014E-308
Smallest positive normalized DOUBLE2.2250738585072014E-308
Smallest negative denormalized DOUBLE-4.9E-324
Smallest positive denormalized DOUBLE4.9E-324
Largest negative REAL-3.4028235E+38
Largest positive REAL3.4028235E+38
Smallest negative normalized REAL-1.17549435E-38
Smallest positive normalized REAL1.17549435E-38
Smallest negative denormalized REAL-1.4E-45
Smallest positive denormalized REAL1.4E-45

3.5 String limitations

The following table lists limitations on string values in Derby.

ValueMaximum Limit
Length of CHAR254 characters
Length of VARCHAR32,672 characters
Length of LONG VARCHAR32,700 characters
Length of CLOB2,147,483,647 characters
Length of BLOB2,147,483,647 characters
Length of character constant32,672
Length of concatenated character string2,147,483,647
Length of concatenated binary string2,147,483,647
Number of hex constant digits16,336
Length of DOUBLE value constant30 characters

3.6 XML limitations

The following table shows the limitation on XML data types in Derby.

IssueLimitation
Length of XML2,147,483,647 characters
共有 人打赏支持
粉丝 7
博文 22
码字总数 78374
×
YuMG
如果觉得我的文章对您有用,请随意打赏。您的支持将鼓励我继续创作!
* 金额(元)
¥1 ¥5 ¥10 ¥20 其他金额
打赏人
留言
* 支付类型
微信扫码支付
打赏金额:
已支付成功
打赏金额: