//derby Database Operations
Derby Configuration Hibernate auto Generate (jdbc:derby:test;create=true;) path generate Test Derby database in Tomcat/bin directory
Derby Database Hibernate basic Configuration
Applictioncontext.properties Basic Configuration
Hibernate.dialect = Org.hibernate.dialect.DerbyDialect
Jdbc.driver = Org.apache.derby.jdbc.ClientDriver #客户端模式 can have only one database link
Jdbc.driver = Org.apache.derby.jdbc.EmbeddedDriver #网络模式 can have multiple database links
Jdbc.url = Jdbc:derby:springexample;create=true; #单链接模式访问 Create =true; No, create
Jdbc.url = Jdbc:derby://127.0.0.1:1527/derbydb; #多链接模式访问
Jdbc.username = JZJK
Jdbc.password = JZJK
Derby Database Hibernate configuration
<!--loading related profiles--
<bean id= "Propertyconfigurer" class= "Org.springframework.beans.factory.config.PropertyPlaceholderConfigurer" >
<property name= "Locations" >
<list>
<value>classpath:applicationContext.properties</value>
</list>
</property>
</bean>
<bean id= "DataSource" class= "Org.logicalcobwebs.proxool.ProxoolDataSource" >
<property name= "Driver" value= "${jdbc.driver}" ></property>
<property name= "Driverurl" value= "${jdbc.url}" ></property>
<property name= "user" value= "${jdbc.username}" ></property>
<property name= "Password" value= "${jdbc.password}" ></property>
<property name= "Prototypecount" >
<value>20</value>
</property>
<property name= "Maximumconnectioncount" >
<value>100</value>
</property>
<property name= "Minimumconnectioncount" >
<value>10</value>
</property>
<property name= "Maximumactivetime" value= "300000"/>
</bean>
<bean id= "Hibernateproperties"
class= "Org.springframework.beans.factory.config.PropertiesFactoryBean" >
<property name= "Properties" >
<props>
<prop key= "Hibernate.hbm2ddl.auto" >none</prop>
<prop key= "Hibernate.dialect" >${hibernate.dialect}</prop>
<prop key= "Hibernate.show_sql" >${hibernate.show_sql}</prop>
</props>
</property>
</bean>
The Derby database defaults to no user authentication, and if you need to enable user authentication, the configuration process is as follows:
1, the designated database certification provider for the database built-in users
Call Syscs_util. Syscs_set_database_property (' Derby.authentication.provider ', ' BUILTIN ');
2. Enable database access authentication
Call Syscs_util. Syscs_set_database_property (' derby.connection.requireAuthentication ', ' true ');
3. Create database user and password
Call Syscs_util. Syscs_set_database_property (' Derby.user.JZJK ', ' jzjk ');
4. Set the global Access user
Call Syscs_util. Syscs_set_database_property (' derby.database.fullAccessUsers ', ' jzjk ');
5. Set the default connection mode
Call Syscs_util. Syscs_set_database_property (' Derby.database.defaultConnectionMode ', ' noAccess ');
Execute the above command in the IJ tool and restart the Derby database service, which requires user password authentication when connecting to the database again.
IJ Connect statement Connect ' jdbc:derby:d:/database name; User=jzjk;password=jzjk ';
Command Desk
Connect ' jdbc:derby:d:/tomcat/bin/test; ';
Connect ' jdbc:derby://127.0.0.1:1527/test; ';
Set connection connectionname; Switch to the specified connection
Show connections List all connections
autocommit [On|off] setting auto-commit mode
Show[tables | views | procedures | functions | synonyms];
show indexes [|from table];
Show roles;Role
Show Enabled_roles; Roles that are enabled
Describe name table or view: The structure
Run ' *.sql ' execution script
Derby functions
function returns
bigint (123.45) 123
char (123.45) ' 123.45 '
Date (time) ' Yyyy-mm-dd '
Double (123.45) 123.45
Integer (123.45) 123
Samllint (123.45) 123
Time (Time) ' Hh:mm:ss '
TimeStamp (Time) ' Yyyy-mm-dd hh:mm:ss '
varchar (123.45) ' 123.45 '
Aggregation functions
AVG () Average
Count () Head Office
Max () Maximum Value
Min () Minimum value
SUM () And
Mathematical functions
ABS () or absval () absolute value
MoD (paramter1,parmeter2) parameter 1 divided by the remainder of parameter 2
Sort () square root
Date and Time functions
Function Return
Day (Time) The day ordinal of a month
Hour (time) When
Minute (time) Score of
Month (time) Month
Second (time) Seconds
Year (time) Years
String functions
|| Connection symbols
LCase () or lower () Convert lowercase
Length () Length
Locate () Returns the starting position of the first occurrence of a substring in the search string, or if no substring is found.0. The first parameter is a substring, the second argument is the search string, and the optional starting position can be supplied as the third argument.
RTrim () Remove Right space
LTrim () Remove left space
SUBSTR () Returns part of the input string for the VARCHAR type, starting at the specified position, continuing to the end of the string, or continuing to the location specified by the optional third parameter. If the starting position is positive, it is relative to the beginning of the string and, if it is a negative value, relative to the end of the string
UCASE () or UPPER () convert uppercase
Action Statement
Distinct name to repeat field
Select column from table where column =? Group BY column order by column DESC;
Delete from table;
Update table Set ...;
Insert into table (column ...) values (value ...);
Paging Query
SELECT * FROM (select Row_number () over () as Rownum,user_name from User_info) User_info where rownum >=2 and rownum< = 5;
Data type
bigint
Integer
Blob
Char
varchar
Clob
varchar
varbinary
TimeStamp
Time
NUMERIC
Derby Installation and use
The Derby database is a purely Java-implemented memory database that belongs to an Apache open source project. Because it is implemented in Java, it can be run on any platform, and another feature is a small size, no installation, just a few small jar packages to run.
The Derby database has two modes of operation:
1) inline mode. The Derby database shares the same JVM with the application, typically the application is responsible for starting and stopping, not visible to other applications other than the application that started it, that is, other applications cannot access it;
2) network mode. The Derby database exclusively has a JVM that runs as a standalone process on the server. In this mode, multiple applications are allowed to access the same derby database.
On Apache, Derby has 4 release packages, which are examples of bin release packages. The bin release package contains script files, demos, jar files, and so on that execute the Derby database Tools, set up the Derby database environment.
1. Install Derby Database
Just download the Derby Zip or tar package from Derby's official website and unzip it. Here, for example, the Db-derby-10.4.1.3-bin version, the following directories are extracted:
1) Bin directory, including some tools scripts and device environment scripts;
2) Demo directory, including some examples of programs;
3) The Docs directory, which contains the Derby documentation;
4) Javadoc directory containing Derby's API documentation;
5) Lib directory, which contains the jar file of the Derby database;
6) Test Catalog, some of Derby's testing jar packages;
2. Using Derby Scripts
Derby provides several scripts to manipulate the Derby database, and before you can use these scripts, you must set up a derby environment.
The following example sets environment variables at the command line, these settings are temporary, and if you open a new command-line window, you need to reset it again, and if you want to make the environment variable permanent, you can set it on my computer.
Set the DERBY_HOME environment variable first, specify your Derby directory for Derby_home, and if your derby is extracted to the E:\ db-derby-10.4.1.3-bin directory, you can set it at the command line as follows:
Set Derby_home=e:\ Db-derby-10.4.1.3-bin
Add the Derby_home\bin directory to the PATH environment variable:
setpath=%derby_home%\bin;%path%
This will simplify your input later on the command line, or 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, the jar package that requires derby is added to the CLASSPATH environment variable, and several scripts are provided in the Derby_home%\bin directory for setting up classpath to simplify the hassle of manually adding jar packages to classpath:
1) SETEMBEDDEDCP. When you run Derby using inline mode, you can use this script setting. The script adds Derby.jar and Derbytools.jar to the environment variable;
2) SETNETWORKSERVERCP. When using network mode to run Derby, use this script to set the CLASSPATH variable on the Derby service side. The script adds Derbynet.jar to the environment variable;
3) setnetworkclientcp. When using network mode to run Derby, use this script to set the CLASSPATH variable for the Derby client. The script adds Derbyclient.jar and Derbytools.jar to the environment variable.
These scripts are typically used only when you run the Derby tool through Derbyrun.jar.
Derby offers three tool scripts: 1) sysinfo;2) ij;3) Dblook. When you run these three scripts, these scripts are automatically set if you do not set the CLASSPATH environment variable.
1) SysInfo
Use SysInfo to display your Java environment Information and Derby version information. The method of use is to enter directly under the command line:
Sysinfo.bat
2) Dblook
Using Dblook, you can export DDL definitions for all or part of a database to a console or file. How to use:
Dblook.bat–d <sourceDBUrl> [Options]
3) IJ
Use the IJ tool to interact with the database, execute SQL scripts such as queries, add deletions, create tables, and so on. At the command line, enter:
Ij.bat
You can start the IJ tool and then start executing the SQL script. When you want to exit the IJ tool, enter it at the command line
Exit
Can.
3. Using the IJ script
1) Run the Derby database in inline mode
Start the IJ tool after entering Ij.bat on the command line. Then create the database with the following command and create a connection to the database:
Connect ' jdbc:derby:firstdb;create=true ';
The Connect command allows you to create a connection to the specified database, with a JDBC URL that specifies which database to create the connection to. The IJ command is case insensitive.
The Jdbc:derby in the parameter is the drive protocol of the Derby database; Firstdb is the database, because there is no path specified, the database will be created under the current directory under your command line; Create=true indicates that the database is created if the database does not exist; ";" is the terminating character of the IJ command.
When the database is created successfully, Derby creates a directory in the same directory under the current command line as the database (here is FIRSTDB), which holds the files for the database.
Once you have connected to the database, you can start executing SQL scripts, such as creating a table:
CREATE TABLE firsttable (ID int primary key,name varchar (20));
Then insert the record:
INSERT into firsttable values (1, ' hotpepper ');
You can also execute a query:
SELECT * from Firsttable;
You can also execute a SQL file by using the Run command:
Run ' E:\derby\demo\programs\toursdb\ToursDB_schema.sql ';
Finally, exit the IJ tool by exit.
You can find a derby.log log file in the current directory under your command line where Derby records the database startup and shutdown information.
2) Derby database running network mode
In this mode, you need to use two console windows, one for starting the Derby database server, and the other as the client accessing the Derby database.
You can start the Derby database server by Startnetworkserver.bat in the Derby_home\bin directory, just enter it on the command line:
Startnetworkserver.bat the directory under which this command is executed, the corresponding directory is the default storage location for the database.
The database is started, and the successful startup will output the following information in the console:
The security manager has been installed with the basic server security policy.
Apache Derby Network Server-10.4.1.3-(648739) started and ready for 2008-09-06
Accept connection on port 1527 at 00:38:12.540 GMT
In another console, using the IJ command to access the Derby database server, after entering Ij.bat to start the IJ tool, establish a connection to the server with the following command and create a database:
Connect ' jdbc:derby://localhost:1527/seconddb;create=true ';
The database in the parameter is different from the inline mode, where "//localhost:1527/" is used, and the URL that accesses the network mode needs to specify the IP address and port of the server, and the other is the same as the inline mode.
Once you have connected to the server, you can start executing SQL scripts, such as creating a table:
CREATE TABLE firsttable (ID int primary key,name varchar (20));
Then insert the record:
INSERT into firsttable values (1, ' hotpepper ');
You can also execute a query:
SELECT * from Firsttable;
You can also execute a SQL file by using the Run command:
Run ' E:\derby\demo\programs\toursdb\ToursDB_schema.sql ';
Finally exit the IJ tool by exit;
4. Accessing the Derby database in a Java application
The difference between accessing the Derby database and accessing other databases using Java code is as follows:
1) different JDBC drivers;
2) The database connection URL is different;
3) When accessing the inline-mode database, you need to display the shutdown database.
The following separate instances access code for the inline mode and network mode Derby database
1) access to the inline-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 not detailed here, as are other databases. It is important to note that when accessing an inline-mode derby database through a Java application, it is the responsibility of the application to close the Derby database at the end of the program, as shown in the code finally
Drivermanager.getconnection ("Jdbc:derby:;shutdown=true");
The shutdown parameter is used to close the Derby database, and if a database is specified in the URL, only the specified database is closed and the entire Derby database is not closed. When the database shutdown succeeds, Derby throws an error code of XJ015 and an exception of 08006 indicates that the shutdown succeeds and the application can not handle both exceptions.
2) Access network mode Derby database
The difference between the network mode and the inline mode is:
A The database connection URL is different;
B Invalid shutdown of Derby database when application exits;
C database-driven differences;
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) {
......
}
Because the Derby database is run as a standalone database in network mode, it can be accessed by multiple applications, so the application should not close the Derby database at the end of the run.
5. Using Derbyrun.jar
Through Derbyrun.jar can also use the above mentioned SysInfo, IJ, dblook Three script functions, before using Derbyrun.jar, in addition to the DERBY_HOME environment variables, you must display the settings classpath environment variables, This can be set by the SETEMBEDDEDCP, SETNETWORKSERVERCP, and setnetworkclientcp scripts mentioned above, depending on how you use the Derby database.
You can run the tools in Derbyrun by Java–jar%derby_home%\lib\ Derbyrun.jar:
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
Manipulating data
drop table tableName;
ALTER TABLE TableName ALTER CLOUMNNAME SET data type varchar (100);
ALTER TABLE tableName drop cloumnname;
Create INDEX IndexName on TableName (COLUMN1,COLUMN2);
Drop index IndexName;
Auto-add Value
generated always as identity;
CREATE TABLE test (UID int generated by default as identity);
Sequence
Create sequence [SchemaName.] Sql92identifier [sequenceelement]*
sequenceelement{
As DataType
Start with Singedinteger
Increment by Singedinteger
MaxValue Singedinteger | No MaxValue
MinValue Singedinteger | No MinValue
Cycle | No cycle
}
Create sequence test as bigint start with 1;
DROP SEQUENCE test Restrict;
Values next value for seqname;//reclaims the next sequence value.
Derby Database Hibernate configuration
<!--loading related profiles--
<bean id= "Propertyconfigurer" class= "Org.springframework.beans.factory.config.PropertyPlaceholderConfigurer" >
<property name= "Locations" >
<list>
<value>classpath:applicationContext.properties</value>
</list>
</property>
</bean>
<bean id= "DataSource" class= "Org.logicalcobwebs.proxool.ProxoolDataSource" >
<property name= "Driver" value= "${jdbc.driver}" ></property>
<property name= "Driverurl" value= "${jdbc.url}" ></property>
<property name= "user" value= "${jdbc.username}" ></property>
<property name= "Password" value= "${jdbc.password}" ></property>
<property name= "Prototypecount" >
<value>20</value>
</property>
<property name= "Maximumconnectioncount" >
<value>100</value>
</property>
<property name= "Minimumconnectioncount" >
<value>10</value>
</property>
<property name= "Maximumactivetime" value= "300000"/>
</bean>
<bean id= "Hibernateproperties"
class= "Org.springframework.beans.factory.config.PropertiesFactoryBean" >
<property name= "Properties" >
<props>
<prop key= "Hibernate.hbm2ddl.auto" >none</prop>
<prop key= "Hibernate.dialect" >${hibernate.dialect}</prop>
<prop key= "Hibernate.show_sql" >${hibernate.show_sql}</prop>
</props>
</property>
</bean>
Derby Database Operations Collation
