javaDB简单使用笔记

Javadb学习

环境变量设置:

DERBY_HOME=D:\ProgramFiles\Environment\javadb

path=.;%JAVA_HOME%/bin;%path%;%CATALINA_HOME%/bin;%ANT_HOME%/bin;%ANT_HOME%/bin;C:\Program Files\IDM Computer Solutions\UltraEdit\;%JAVA_HOME%\jre\lib;%DERBY_HOME%\bin

 

classpath=.;%JAVA_HOME%\lib\tools.jar;%JAVA_HOME%\lib\dt.jar;%JAVA_HOME%\bin;%JAVA_HOME%\jre\lib;%DERBY_HOME%\lib\derby.jar;%DERBY_HOME%\lib\derbyclient.jar;%DERBY_HOME%\lib\derbynet.jar;%DERBY_HOME%\lib\derbyrun.jar

 

开启Javadb服务:

java -jar %derby_home%\lib\derbyrun.jar server start

关闭javadb服务

java -jar %derby_home%\lib\derbyrun.jar server shutdown

 

 

ij

ij> exit;  退出

 

Email server : derby-user@db.apache.org

 

 

 用ij连接数据库javadb

Creating a Derby database and running SQL statementsNow, you will use the Derby ij tool to load the Derby database engine. You will use theDerby embedded driver to create and connect to the firstdb database. You will alsouse a few basic SQL statements to create and populate a table.1. Run the Derby ij tool.If you included the DERBY_HOME/bin directory in your PATH environment variable,type:ijOtherwise, you can use the java command to start the ij tool.OperatingSystemCommandUNIX(Korn Shell)java -jar $DERBY_HOME/lib/derbyrun.jar ijij version 10.5Windows java -jar %DERBY_HOME%\lib\derbyrun.jar ijij version 10.52. Create the database and open a connection to the database using the embeddeddriver.CONNECT 'jdbc:derby:firstdb;create=true';Description of connection command:connectThe ij command to establish a connection to a database. The Derbyconnection URL is enclosed in single quotation marks. An ij command canbe in either uppercase or lowercase.jdbc:derby:The JDBC protocol specification for the Derby driver.firstdbThe name of the database. The name can be any string. Because nofilepath is specified, the database is created in the default working directory(DERBYTUTOR).;create=trueThe Derby URL attribute that is used to create a database. Derby does nothave an SQL create database command.;Getting Started with Java DB23The semicolon is the ij command terminator.3. Create a table with two columns using standard SQL.CREATE TABLE FIRSTTABLE(ID INT PRIMARY KEY,NAME VARCHAR(12));0 rows inserted/updated/deleted4. Insert three records.INSERT INTO FIRSTTABLE VALUES(10,'TEN'),(20,'TWENTY'),(30,'THIRTY');3 rows inserted/updated/deleted5. Perform a simple select of all records in the table.SELECT * FROM FIRSTTABLE;ID |NAME------------------------10 |TEN20 |TWENTY30 |THIRTY3 rows selected6. Perform a qualified select of the record with column ID=20.SELECT * FROM FIRSTTABLEWHERE ID=20;ID |NAME------------------------20 |TWENTY1 row selected7. Optional: Create and populate additional tables and other schema objects.a. Load the SQL script ToursDB_schema.sql.run 'ToursDB_schema.sql';ij> ...CREATE TABLE AIRLINES(AIRLINE CHAR(2) NOT NULL ,AIRLINE_FULL VARCHAR(24),BASIC_RATE DOUBLE PRECISION,...0 rows inserted/updated/deleted... Other output messages not shown ...b. Populate the tables with data by running the script loadTables.sql.run 'loadTables.sql';ij> run 'loadCOUNTRIES.sql';ij> insert into COUNTRIES values ( 'Afghanistan','AF','Asia');1 row inserted/updated/deletedij> insert into COUNTRIES values ( 'Albania','AL','Europe');1 row inserted/updated/deleted... Other output messages not shown ...8. Exit the ij tool.exit;You should be returned to the DERBYTUTOR directory.9. Browse the most

 

Activity 2: Run SQL using the client driver

java -jar %DERBY_HOME%\lib\derbyrun.jar server start

换一个dos窗口

java -jar %DERBY_HOME%\lib\derbyrun.jar ij

CONNECT 'jdbc:derby://localhost:1527/seconddb;create=true';

备注区别:CONNECT 'jdbc:derby:firstdb;create=true'; 

 

CREATE TABLE SECONDTABLE(ID INT PRIMARY KEY,NAME VARCHAR(14));0 rows inserted/updated/deleted

 

 

INSERT INTO SECONDTABLE VALUES(100,'ONE HUNDRED'),(200,'TWO HUNDRED'),(300,'THREE HUNDRED');3 rows inserted/updated/deleted

 

SELECT * FROM SECONDTABLE;ID |NAME------------------------100 |ONE HUNDRED200 |TWO HUNDRED300 |THREE HUNDRED3 rows selected

 

Exit ij.

 

java -jar %DERBY_HOME%\lib\derbyrun.jar servershutdown

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import  org.apache.derby.jdbc.EmbeddedDriver;
public class JavaDBTest {

    public static void main(String[] args) throws Exception {
        String driver = "org.apache.derby.jdbc.EmbeddedDriver";
        String url = "jdbc:derby:";
        String facetDir = "aaa";
        Class.forName(driver).newInstance();
        Connection connection = DriverManager.getConnection(url + facetDir+ ";create=true");
        connection.setAutoCommit(true);
        Statement stmt = connection.createStatement();
        String creatSql="CREATE TABLE  testDB (" + "facetId INTEGER PRIMARY KEY," + " name VARCHAR(256))";
        stmt.executeUpdate(creatSql);
        stmt.executeUpdate("INSERT INTO testDB(facetId, name) " + "VALUES(1, '123')");
        stmt.executeUpdate("INSERT INTO testDB(facetId, name) " + "VALUES(2, '123')");
        stmt.executeUpdate("INSERT INTO testDB(facetId, name) " + "VALUES(3, '123')");
         System.out.println(stmt.executeQuery("select * from testDB").toString());
    }
}