Просмотр исходного текста страницы Java Tutorial/Database/Java DB Derby

==  Connect to Derby database ==






   
  <!-- start source code -->
   
    <source lang="java">
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
public class MainClass {
  static Connection conn;
  public static void main(String[] args) throws Exception {
    if (args.length != 2) {
      System.out.println("Usage: java JavaDBDemo <Name> <Address>");
      System.exit(1);
    }
    String driver = "org.apache.derby.jdbc.EmbeddedDriver";
    String dbName = "AddressBookDB";
    String connectionURL = "jdbc:derby:" + dbName + ";create=true";
    String createString = "CREATE TABLE ADDRESSBOOKTbl (NAME VARCHAR(32) NOT NULL, ADDRESS VARCHAR(50) NOT NULL)";
    Class.forName(driver);
    conn = DriverManager.getConnection(connectionURL);
    Statement stmt = conn.createStatement();
    stmt.executeUpdate(createString);
    PreparedStatement psInsert = conn
        .prepareStatement("insert into ADDRESSBOOKTbl values (?,?)");
    psInsert.setString(1, args[0]);
    psInsert.setString(2, args[1]);
    psInsert.executeUpdate();
    Statement stmt2 = conn.createStatement();
    ResultSet rs = stmt2.executeQuery("select * from ADDRESSBOOKTbl");
    System.out.println("Addressed present in your Address Book\n\n");
    int num = 0;
    while (rs.next()) {
      System.out.println(++num + ": Name: " + rs.getString(1) + "\n Address"
          + rs.getString(2));
    }
    rs.close();
  }
}</source>
    
   
  <!-- end source code -->
   




==  Connect to Java DB (Derby) with org.apache.derby.jdbc.EmbeddedDriver ==






   
  <!-- start source code -->
   
    <source lang="java">
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
public class JavaDBDemo {
  static Connection conn;
  public static void main(String[] args) {
    String driver = "org.apache.derby.jdbc.EmbeddedDriver";
    String connectionURL = "jdbc:derby:myDatabase;create=true";
    String createString = "CREATE TABLE Employee (NAME VARCHAR(32) NOT NULL, ADDRESS VARCHAR(50) NOT NULL)";
    try {
      Class.forName(driver);
    } catch (java.lang.ClassNotFoundException e) {
      e.printStackTrace();
    }
    try {
      conn = DriverManager.getConnection(connectionURL);
      Statement stmt = conn.createStatement();
      stmt.executeUpdate(createString);
      PreparedStatement psInsert = conn.prepareStatement("insert into Employee values (?,?)");
      psInsert.setString(1, args[0]);
      psInsert.setString(2, args[1]);
      psInsert.executeUpdate();
      Statement stmt2 = conn.createStatement();
      ResultSet rs = stmt2.executeQuery("select * from Employee");
      int num = 0;
      while (rs.next()) {
        System.out.println(++num + ": Name: " + rs.getString(1) + "\n Address" + rs.getString(2));
      }
      rs.close();
    } catch (Exception e) {
      e.printStackTrace();
    }
  }
}</source>
    
   
  <!-- end source code -->
   




==  JavaDB Drivers ==




<p>Based on the environment in which JavaDB is running, Java SE 6 contains two drivers for accessing JavaDB. They are:</p>
<p>Embedded driver enables you to access JavaDB databases running in the embedded environment.</p>
<p>The org.apache.derby.jdbc.EmbeddedDriver class implements the embedded driver.</p>
<p>Network client driver enables you to access the JavaDB database running in the client/server environment.</p>
<p>The org.apache.derby.jdbc.ClientDriver class implements the network client driver.</p>



==  JavaDB Tools ==




<p>Java SE 6 contains various tools that you can use to start, stop, access, and administer JavaDB.</p>
<p>Some of the JavaDB tools are:</p>
<OL><LI>server: Starts the JavaDB database.</LI><LI>ij Represents a JDBC scripting tool.</LI><LI>You can use ij to run scripts against a JavaDB database.</LI><LI>You can also use ij to run SQL queries.</LI><LI>You can use ij to access JavaDB running in both the embedded and client/server environments.</LI><LI>sysinfo: Displays information about the JavaDB version and the environment.</LI><LI>dblook: Generates and stores the Data Definition Language (DDL) to a file.</LI><LI>In Java SE 6, the server, ij, sysinfo, and dblook tools are packaged in the derbyrun.jar file.</LI><LI>You can locate the derbyrun.jar file in the <JAVA_HOME>/db/lib directory.</LI></OL>



==  JavaDB URLs ==




<p>To connect to an existing JavaDB database using the embedded driver:</p>

   
  <!-- start source code -->
   
    <source lang="java">
jdbc:derby:<databaseName>;<URLAttributes></source>
    
   
  <!-- end source code -->
   

<OL><LI>host: Specifies the computer name or Internet Protocol address where Java DB is running. If Java DB is running on the same computer as that of the client, you can use localhost or 127.0.0.1 as the value of the host option.</LI><LI>Port: Specifies the port number used by JavaDB to accept incoming requests.</LI><LI>databaseName: Represents the name of the database to connect with.</LI></OL>



==  New to Java 6 is a lightweight database called Derby. ==




<p>Derby is a product of the Apache Database project.</p>
<p>Derby is a transactional, relational database.</p>
<p>Derby provides a small footprint on disk.</p>
<p>Deployment is much easier because Derby supports storage of a database archived in a JAR file.</p>
<p>Derby allows you to simply distribute the JAR file.</p>



==  Ping JavaDB. ==




<p>The command to ping JavaDB is:</p>

   
  <!-- start source code -->
   
    <source lang="java">
java �jar derbyrun.jar server ping
java �jar derbyrun.jar server ping 192.168.0.1 1555</source>
    
   
  <!-- end source code -->
   




==  The JavaDB Directory Structure ==




<p>When you install Java SE 6, JavaDB installs in the db directory of JAVA_HOME.</p>
<p>The db directory contains the following subdirectories:</p>
<OL><LI>demo: Contains sample programs and scripts to use JavaDB.</LI><LI>lib: Contains the JavaDB JAR files. You need to use the JavaDB JAR files to start, stop, and access JavaDB.</LI><LI>frameworks: Contains scripts for setting up the environment to use JavaDB in different environments.</LI></OL>



==  The JavaDB Environments ==




<p>Embedded: Allows only a single application to access a database at one time.</p>
<p>In the embedded environment, no network access occurs.</p>
<p>When you load a JDBC driver from a Java program, JavaDB automatically starts in the embedded environment.</p>
<p>Client/server: Allows multiple applications to connect to JavaDB over the network.</p>
<p>To start JavaDB in the client/server environment, you need to use the server command line tool of JavaDB.</p>



==  The SQL script for creating the Employee Table ==






   
  <!-- start source code -->
   
    <source lang="java">
CREATE TABLE Employee (
    Employee_ID int NOT NULL,
    NAME        varchar (50) NOT NULL,
    DESCRIPTION varchar (250) NOT NULL
)
////////////////////////////////////////////////////////
public class Employee {
    public int employeeId;
    public String name;
    public String description;

    public String toString () {
        StringBuilder buf = new StringBuilder ();
        buf.append ("\n\tEmployee ID = ") .append (employeeId);
        buf.append ("\n\tName = ").append (name);
        buf.append ("\n\tDescription = ").append (description);
        return buf.toString ();
     }
}
///////////////////////////////////////////////////////
import java.sql.BaseQuery;
import java.sql.DataSet;
import java.sql.Select;
public interface EmployeeQueries extends BaseQuery {
    // Select all employees
    @Select (sql = "SELECT Employee_ID, NAME, DESCRIPTION FROM Employee", readOnly=false, connected=false)
    DataSet<Employee> getAllEmployees();
    // Select employee by name
    @Select ("SELECT Employee_ID, NAME, DESCRIPTION FROM Employee where NAME = ?")
    DataSet<Employee> getEmployeeByName(String name);
}

import java.sql.Connection;
import java.sql.DataSet;
import java.sql.DriverManager;
import java.sql.SQLException;
public class QueryDemo {
    public static void main (String [] args) {
        Connection connection = null;
        try {
            String url = "jdbc:inetdae7:localhost:1433?database=Adaptor";
            String login = "sa";
            String password "admin";
            connection = DriverManager.getConnection(url, login,password);
            EmployeeQueries qo = connection.createQueryObject(EmployeeQueries.class); 
            // Get all employees
            DataSet<Employee> rows = qo.getAllEmployees ();
            for (Employee employee : rows) {
                System.out.println (employee);
            }
            // Create new Employee object
            if (! rows.isReadOnly () ) {
                System.out.printIn ("\nCreate new employee");
                Employee r = new Employee ();
                r.Employee_ID = 12345;
                r.name = "Supervisor";
                r.description = "Do monitoring job";
                boolean insertResult = rows. insert (r);
                rows.sync (connection);
                System.out.println ("\tInserted: " + insertResult);
          }
          // Retrieve Employee by name
          System.out.println ("\nGet employee by name:");
          DataSet<Employee> rows2 = qo.getEmployeeByName ("Supervisor");
          Employee employee = rows2.get (0);
          System.out.println (Employee);
          if (Employee != null) {
              // Modify Role
              System.out.println ("\nModify current Employee:");
              employee.description = "Do supervising job";
              boolean modifyResult = rows2.modify (employee);
              rows2.sync (connection);
              System.out.println ("\tModified: " + modifyResult);
          }
      } catch (SQLException e) {
          for (Throwable t : e) {
               t.printStackTrace ();
          }
       } finally {
            // Close connection
            try {
                connection.close ();
            } catch (SQLException e) {
                e.printStackTrace ();
            } 
         }
    }
}</source>
    
   
  <!-- end source code -->
   




==  To stop JavaDB, you can use the following command: ==






   
  <!-- start source code -->
   
    <source lang="java">
java �jar derbyrun.jar server shutdown</source>
    
   
  <!-- end source code -->
   




==  To view run time information for JavaDB, you can use the following command: ==






   
  <!-- start source code -->
   
    <source lang="java">
java �jar derbyrun.jar server runtime</source>
    
   
  <!-- end source code -->
   




==  To view the structure of the Employee table ==






   
  <!-- start source code -->
   
    <source lang="java">
Describe Employee;</source>
    
   
  <!-- end source code -->
   




==  Using the ij Tool ==




<p>The ij tool enables you to connect to a running JavaDB server and execute scripts and queries against a database.</p>
<p>The command to use the ij tool is:</p>

   
  <!-- start source code -->
   
    <source lang="java">
java �jar derbyrun.jar ij</source>
    
   
  <!-- end source code -->
   

<p>Commands of the ij Tool</p>
CommandDescriptionDriverLoads the specified JDBC driver.ProtocolSets the specified protocol to use for connecting to a database.ConnectEstablishes a connection with a database.Set ConnectionSwitches from the existing connection to a different connection specified with a connection name.Show ConnectionsLists all active connections.DisconnectDisconnects the current connection, a connection specified by a connection name, or all active connections.Show SchemasLists all schemas present in the current database.Show TablesLists all tables present in the current database.Show ViewsLists all views present in the current database.Show ProceduresLists all stored procedures present in the current database.Show IndexesLists all indexes present in a specified table.DescribeDisplays information about columns, such as column name and data type, for a specified table.rumitCommits the current transaction.RollbackRolls back the current transaction.



==  Using the sysinfo Tool ==




<p>The code to run the sysinfo tool is:</p>

   
  <!-- start source code -->
   
    <source lang="java">
java -jar derbyrun.jar sysinfo</source>
    
   
  <!-- end source code -->
   




==  Viewing and Switching Connections ==




<p>Show Connections command lists the active connections established with JavaDB.</p>
<p>You can then use the Set connection command to switch to a different connection.</p>
<p>To view and switch between connections:</p>

   
  <!-- start source code -->
   
    <source lang="java">
show connections;</source>
    
   
  <!-- end source code -->
   




==  Working with Tables ==




<p>Use the Create Table SQL command to create a new table in a database.</p>

   
  <!-- start source code -->
   
    <source lang="java">
Create table employee (name varchar(30),address varchar(50));</source>
    
   
  <!-- end source code -->
   




==  You can use the connect command of ij to establish a connection with a database ==




<p>The connect command loads a JDBC driver to establish the connection.</p>
<p>The command to establish a connection with a sample database is:</p>

   
  <!-- start source code -->
   
    <source lang="java">
connect "jdbc:derby:sample";</source>
    
   
  <!-- end source code -->
   




==  You can use the server tool to start JavaDB in a network client environment. ==




<p>To start JavaDB:</p>
<p>Set the Path environment variable to include the <JAVA_HOME>/db/lib directory.</p>
<p>Open the command prompt and type the following command:</p>

   
  <!-- start source code -->
   
    <source lang="java">
java �jar derbyrun.jar server start</source>
    
   
  <!-- end source code -->