Derby database learning: Derby and Derby. Get started with Derby.

Derby http://db.apache.org/derby/ official

Derby learning materials 1: http://www.oschina.net/code/snippet_12_71

Derby study 2: http://hotpepper.javaeye.com/blog/237831

The following content is from the Internet and is not summarized by individuals.

 

Bytes. Because it is implemented in Java, it can be run on any platform. Another feature is that it is small in size and free of installation. You only need a few small jar packages to run it.
The Derby database has two running modes:
1) embedded mode. The Derby database shares the same JVM with the application, which is usually started and stopped by the application. It is invisible to other applications except the ones that start it, that is, other applications cannot access it;
2) network mode. The Derby database exclusively occupies one JVM and runs as an independent process on the server. In this mode, multiple applications are allowed to access the same Derby database.
On Apache, Derby has four release packages. Here we use the bin release package as an example. The bin release package contains script files, demos, and jar files for executing the Derby database tool, setting the Derby database environment. 1. Install the Derby Database
You only need to download the Derby ZIP or tar package from the official Derby website and decompress it. Here we take the db-derby-10.4.1.3-bin version as an example, unzip and get the following directory:
1) The bin directory contains some tool scripts and scripts of the device environment;
2) the demo directory contains some instance programs;
3) the docs directory contains Derby documents;
4) The javadoc directory contains the Derby API documentation;
5) The lib directory contains the JAR file of the Derby database;
6) test directory, some test jar packages of Derby;
2. Use the Derby script
Derby provides several scripts to operate the Derby database. Before using these scripts, you must set the Derby runtime environment.
In the following example, environment variables are set in the command line. these settings are temporary. If you open a new command line window, you need to reset it again, if you want to make the environment variable take effect permanently, you can set it on my computer.
First set the environment variable derby_home and specify your derby directory for derby_home. If your derby is decompressed to the E:/db-derby-10.4.1.3-bin directory, you can set the following in the command line:
Set derby_home = E:/db-derby-10.4.1.3-bin
Add the derby_home/bin directory to the path environment variable:
Set Path = % derby_home %/bin; % PATH %
This will simplify your input in the command line later. Otherwise, you must use the full path of the script every time or you must go to the derby_home/bin directory to execute the script.
Finally, add the Derby jar package to the classpath environment variable. Several scripts are provided in the derby_home %/bin directory to set classpath, to simplify the trouble of manually adding a jar package to classpath:
1) setembeddedcp. You can use this script to set Derby to run in Embedded mode. This script adds Derby. jar and derbytools. jar to environment variables;
2) setnetworkservercp. When Derby is run in network mode, use this script to set the classpath variable of the Derby server. This script adds derbynet. jar to the environment variable;
3) setnetworkclientcp. When Derby is run in network mode, use this script to set the classpath variable of the Derby client. This script adds derbyclient. jar and derbytools. jar to environment variables.
Generally, these scripts are used only when you run the Derby tool through derbyrun. jar.
Derby provides three tool scripts: 1) sysinfo; 2) ij; 3) dblook. When you run these three scripts, if you do not set the classpath environment variables, these scripts will be automatically set.
1) sysinfo
You can use sysinfo to display your Java environment information and Derby version information. You can directly enter the following information in the command line:
Sysinfo. bat
2) dblook
You can use dblook to export DDL definitions of all or some databases to the console or files. Usage:
Dblook. Bat-D <sourcedburl> [Options]
3) IJ
Use the IJ tool for database interaction and execute SQL scripts, such as queries, addition, deletion, modification, and table creation. Enter:
IJ. bat
You can start the IJ tool and then start executing the SQL script. To exit the IJ tool, enter
Exit;
You can.
3. Use the IJ script
1) run the embedded Derby Database
Enter IJ. bat in the command line to start the IJ tool. Run the following command to create a database and connect to the database:
Connect 'jdbc: Derby: firstdb; Create = true ';
You can use the connect command to create a connection to a specified database, and use a jdbc url to specify the database to create a connection. The IJ command is case-insensitive.
In the parameter, JDBC: Derby is the drive protocol of the Derby database; firstdb is the database life. Because no path is specified, the database will be created in the directory where your command line is located; "Create = true" indicates that the database is created if the database does not exist. ";" indicates the end of the IJ command.
When the database is created successfully, derby will create a directory in the directory where your command line is located that is consistent with the database's life (firstdb here), where the database files are stored.
After connecting to the database, you can start to execute the SQL script, such as creating a table:
Create Table firsttable (ID int primary key, name varchar (20 ));
Insert the record:
Insert into firsttable values (1, 'hotpepper ');
You can also perform a query:
Select * From firsttable;
You can also run the SQL file using the run command:
Run 'e:/Derby/demo/programs/toursdb/toursdb_schema. SQL ';
Finally, exit the IJ tool through exit.
You can find a derby. log file in the directory where your command line is located, where Derby records the information about database startup and shutdown.
2) Derby database in Network Mode
In this mode, you need to use two console windows, one for starting the Derby database server, and the other for accessing the Derby database client.
You can use startnetworkserver. bat in the derby_home/bin directory to start the Derby database server. You only need to enter:
Startnetworkserver. bat
The database is started. After the database is started successfully, the following information is output on the console:
You have installed the security management program using the basic server security policy.
Apache Derby network server-10.4.1.3-(648739) started and ready for 2008-09-06
Connect to port 540 at 00:38:12. 1527 GMT
On the other console, run the IJ command to access the Derby database server. After Entering IJ. BAT to start the IJ tool, run the following command to establish a connection with the server and create a database:
Connect 'jdbc: Derby: // localhost: 1527/seconddb; Create = true ';
The database life part in the parameter is different from the embedded mode. "// localhost: 1527/" is used here. To access the URL in the network mode, you must specify the IP address and port of the server, the rest is the same as the embedded mode.
After connecting to the server, you can execute the SQL script. For example, create a table:
Create Table firsttable (ID int primary key, name varchar (20 ));
Insert the record:
Insert into firsttable values (1, 'hotpepper ');
You can also perform a query:
Select * From firsttable;
You can also run the SQL file using the run command:
Run 'e:/Derby/demo/programs/toursdb/toursdb_schema. SQL ';
Finally exit the IJ tool through exit;
4. Access the Derby database in a Java application
The differences between using Java code to access the Derby database and accessing other databases are as follows:
1) JDBC drivers are different;
2) different database connection URLs;
3) when accessing an embedded database, you must disable the database.
The following code accesses the Derby database in the embedded mode and network mode respectively.
1) access the embedded mode Derby Database
String driver = "org. Apache. Derby. JDBC. embeddeddriver ";
String url = "JDBC: Derby: firstdb; Create = true ";
Connection conn;
Try {
Class. forname (driver );
Conn = drivermanager. getconnection (URL );
} Catch (exception e ){
......
} Finally {
......
Drivermanager. getconnection ("JDBC: Derby:; shutdown = true ");
}
After the connection is established, other data operations, such as querying and updating data, are the same as those in other databases. Note that when you access the Derby database in the embedded mode through a Java application, the application has the responsibility to close the Derby database at the end of the program, as shown in finally
Drivermanager. getconnection ("JDBC: Derby:; shutdown = true ");
The shutdown parameter is used to close the Derby database. If the database life is specified in the URL, only the specified database is closed, and the entire Derby database is not closed. When the database is successfully shut down, derby will throw an error code xj015 and a 08006 exception, indicating that the database is shut down successfully. The application may not handle these two exceptions.
2) access the network mode Derby Database
The difference between the network mode and the embedded mode is:
A. Different database connection URLs;
B. the Derby database is disabled when the application exits;
C. Different database drivers;
String driver = "org. Apache. Derby. JDBC. clientdriver ";
String url = "JDBC: Derby: // localhost: 1527/firstdb; Create = true ";
Connection conn;
Try {
Class. forname (driver );
Conn = drivermanager. getconnection (URL );
} Catch (exception e ){
......
}
In network mode, the Derby database is an independent database and can be accessed by multiple applications. Therefore, the Derby database should not be closed when the application ends. 5. Use derbyrun. Jar
Use derbyrun. jar can also use the above-mentioned sysinfo, IJ, dblook script functions, in the use of derbyrun. before jar, except for the derby_home environment variables, you must display and set the classpath environment variables. You can set these variables through the setembeddedcp, setnetworkservercp, and setnetworkclientcp mentioned above, the specific script depends on how you use the Derby database.
You can use Java-jar % derby_home %/lib/derbyrun. jar to run the tool in derbyrun:
1) Use sysinfo. Java-jar % derby_home %/lib/derbyrun. Jar sysinfo
2) use IJ. Java-jar % derby_home %/lib/derbyrun. Jar IJ
3) Use dblook. Java-jar % derby_home %/lib/derbyrun. Jar dblook
6. New programmers who have installed JDK 6 may find that JDK 6 has added a new directory named dB in addition to the traditional bin and JRE directories. This is a new member of Java 6: java db. This is a pure Java-implemented, open-source database management system (DBMS), originated from the project Derby under the Apache Software Foundation (ASF. It is only 2 MB in size, which is a pocket-sized database compared to the database with frequent access to G. However, this does not prevent Derby from being fully functional and supports the features required by almost all database applications. What's even more valuable is that, relying on the strong community power of ASF, Derby is supported by large companies including IBM and sun and excellent programmers from around the world. It is no wonder that Sun chose to include its 10.2.2 version in JDK 6 as an embedded database. This seems to inject a brand new vitality into JDK: Java programmers no longer need to spend a lot of energy installing and configuring databases, so they can perform secure, easy-to-use, standard, and free database programming. In this chapter, we will first look at the world of java db to explore how to use it to compile programs with rich functions.
Hello, java db: derby in Embedded Mode
Now that we have an embedded database, let's start with a simple example and try to use it. This program performs operations that most database applications may perform: Create a database named hellodb In the DBMS, and create a data table named hellotable; insert two pieces of data into the table. Then, query the data and print the result on the console. Finally, delete the table and database and release the resource.
Public class hellojavadb {
Public static void main (string [] ARGs ){
Try {// load the driver
Class. forname ("org. apache. derby. JDBC. then, run the following command in the command line (in this example, the Windows platform, of course, you can make a slight change to other systems:

Listing 2. Run the hellojavadb command
Java-CP.; % java_home % dblibderby. Jar hellojavadb
Figure 1. Execution result of the hellojavadb Program

The above procedures are no different from those in the past. The difference is that we don't need to worry about DBMS configuration, because Derby has automatically created a directory named hellodb under the current directory to physically store data and logs. All you need to do is pay attention to the naming problem: in Embedded mode, the driver name should be org. Apache. Derby. JDBC. embeddeddriver; when creating a new database, you must add create = true after the Protocol. In addition, you can use the following code to shut down all databases and Derby engines:

Listing 3. disabling all databases and Derby Engines
Drivermanager. getconnection ("JDBC: Derby:; shutdown = true ");
If you only want to close a database, you can call:

Listing 4. Closing a database
Drivermanager. getconnection ("JDBC: Derby: hellodb; shutdown = true ");
In this way, the cost of maintaining and managing databases using the Derby embedded mode is close to zero. This is good news for those who want to concentrate on writing code. However, some people may wonder why most DBMS systems do not adopt the embedded mode? You may wish to do a small experiment. When we run the hellojavadb program in two command line windows at the same time. The result is the same as the result, but the other has an error, as shown in figure 2.

Figure 2. Limitations of the embedded Mode

The cause of the error is actually very simple: when using the embedded mode, Derby itself does not run in an independent process, but runs in the same Java Virtual Machine (JVM) together with the application. Therefore, Derby becomes a part of the application just like other jar files used by the application. This makes it easy to understand why our example program runs smoothly by adding the Derby JAR file to the classpath. This also shows that only one JVM can start the database: two applications running in different JVM instances naturally cannot access the same database.
In view of the limitations mentioned above and the need for multiple connections from different JVMs to access a database, the next section will introduce another Derby mode: network server ).
Network Server Mode
As mentioned above, the network server mode is a more traditional Client/Server mode. We need to start a derby network server to process client requests, whether from the same JVM instance or from another machine on the network. In addition, the client uses the drda (Distributed Relational database architecture) Protocol to connect to the server. This is a database interaction standard advocated by the Open Group. Figure 3 shows the general structure of the mode.
As Derby developers strive to reduce the difference between the network server mode and the embedded mode, we only need to modify the program in Listing 1. As shown in listing 5, a new function and some string variables are added to hellojavadb. It is not hard to see that the new Code only changes some strings that are specifically pointed out in the previous section: the driver class is Org. apache. derby. JDBC. clientdriver, and the database connection protocol becomes JDBC: Derby: // localhost: 1527 /. This is a URL-like string. In fact, the connection format of the Derby network client is JDBC: Derby: // server [: Port]/databasename [; attributekey = value]. In this example, we use the simplest local machine as the server, and the port is Derby's default port 1527.

Figure 3. Derby network server mode Architecture

Listing 5. hellojavadb in network server mode
Public class hellojavadb {
Public static string driver = "org. Apache. Derby. JDBC. embedded, of course, only clients are not enough. We also need to start the network server. The class that controls the network server in Derby is org. Apache. Derby. drda. networkservercontrol. Therefore, type the following command. If you want to know more about networkservercontrol options, you only need to remove the start parameter to see the help information. All Network Server implementations are included in derbynet. Jar by derby.

Listing 6. Start a network server
Java-CP.; "C: Program filesjavajdk1.6.0dblibderby. Jar ";
"C: Program filesjavajdk1.6.0dblibderbynet. Jar"
Org. Apache. Derby. drda. networkservercontrol start
Correspondingly, the implementation of the network client is included in derbyclient. jar. Therefore, you only need to add the JAR file to the classpath, and the modified client can read data smoothly. Try again using two command line windows to connect to the database, and you will be able to get the correct result. If you no longer need a server, you can use the shutdown parameter of networkservercontrol to disable the server.
More
So far, this article introduces Java dB (Derby), a new member of Java SE 6, and how to use java db in the embedded mode and network server mode. Of course, this is just a short taste. More advanced options need to be found in Sun and Derby documents. At the end of this chapter, we will briefly introduce several java db gadgets to speed up development. They are all located in the org. Apache. Derby. Tools Package. You need to obtain information or test them during development.
• IJ: a tool used to run SQL scripts;
• Dblook: A schema Extraction Tool for the Derby database;
• Sysinfo: tool class for Displaying System and Derby information; driver ";
Public static string protocol = "JDBC: Derby :";

Public static void main (string [] ARGs ){
// Same as before
}
Private Static void parsearguments (string [] ARGs ){
If (ARGs. Length = 0 | args. length> 1 ){
Return;
}
If (ARGs [0]. inclusignorecase ("derbyclient ")){
Framework = "derbyclient ";
Driver = "org. Apache. Derby. JDBC. clientdriver ";
Protocol = "JDBC: Derby: // localhost: 1527 /";
}
}
}
Embeddeddriver "). newinstance ();
System. Out. println ("load the embedded driver ");
Connection conn = NULL;
Properties props = new properties ();
Props. Put ("user", "user1"); props. Put ("password", "user1 ");
// Create and connect the database named hellodb
Conn = drivermanager. getconnection ("JDBC: Derby: hellodb; Create = true", props );
System. Out. println ("Create and connect to hellodb ");
Conn. setautocommit (false );

// Create a table and insert two records
Statement S = conn. createstatement ();
S.exe cute ("create table hellotable (name varchar (40), score INT )");
System. Out. println ("created table hellotable ");
S.exe cute ("insert into hellotable values ('ruth Cao ', 86 )");
S.exe cute ("insert into hellotable values ('flora Shi', 92 )");
// List the two records
Resultset rs = s.exe cutequery (
"Select name, score from hellotable order by score ");
System. Out. println ("namettscore ");
While (Rs. Next ()){
Stringbuilder builder = new stringbuilder (Rs. getstring (1 ));
Builder. append ("T ");
Builder. append (Rs. getint (2 ));
System. Out. println (builder. tostring ());
}
// Delete the table
S.exe cute ("Drop table hellotable ");
System. Out. println ("dropped table hellotable ");

Rs. Close ();
S. Close ();
System. Out. println ("Closed result set and statement ");
Conn. Commit ();
Conn. Close ();
System. Out. println ("committed transaction and closed connection ");

Try {// perform a clean shutdown
Drivermanager. getconnection ("JDBC: Derby:; shutdown = true ");
} Catch (sqlexception SE ){
System. Out. println ("database shut down normally ");
}
} Catch (throwable e ){
// Handle the exception
}
System. Out. println ("simpleapp finished ");
}
}
For more information, see: http://www.javaeye.com/wiki/topic/237831

The following code can be run directly:

Code:

1. Package ttwork. Derby;
3. Import java. SQL. connection;
5. Import java. SQL. drivermanager;
7. Import java. SQL. preparedstatement;
9. Import java. SQL. resultset;
11. Import java. SQL. statement;
13. Public class derbytest {
14. Static connection conn;
15. Public static void main (string [] ARGs ){
16. String driver = "org. Apache. Derby. JDBC. embeddeddriver ";
17. String connectionurl = "JDBC: Derby: mydatabase; Create = true ";
18. String createstring = "create table employee2 (name varchar (32) not null, address varchar (50) not null )";
19. Try {
20. Class. forname (driver );
21. } Catch (Java. Lang. classnotfoundexception e ){
22. E. printstacktrace ();
23. }
24. Try {
25. Conn = drivermanager. getconnection (connectionurl );
26. Statement stmt = conn. createstatement ();
27. Stmt.exe cuteupdate (createstring );
28. Preparedstatement psinsert = Conn
29. . Preparestatement ("insert into employee2 values (?,?) ");
30. Psinsert. setstring (1, "123 ");
31. Psinsert. setstring (2, "321 ");
32. Psinsert.exe cuteupdate ();
33. Statement stmt2 = conn. createstatement ();
34. Resultset rs = stmt2.executequery ("select * From employee2 ");
35. Int num = 0;
36. While (Rs. Next ()){
38. System. Out. println (++ num + ": Name:" + Rs. getstring (1)
39. + "/N address" + Rs. getstring (2 ));
40. }
41. Rs. Close ();
42. } Catch (exception e ){
43. E. printstacktrace ();
44. }
45. }
46. }