Apache Derby 简明教程

Apache Derby - Insert Data

插入查询插入数据： new records ，到表中。

Syntax

以下是 INSERT 语句的基本语法 −

ij>INSERT INTO table_name VALUES (column_name1, column_name2, ...);

其中 column1、column2 是要插入的行中的列值。

Example

以下 SQL INSERT 语句在 Student 表中插入一个新行，其中它插入 id, age, first name 和 last name 列中的值。

SQL> INSERT INTO Student VALUES (101, 20, 'Zara', 'Ali');

Syntax 2

或者，您可以通过提及列名来插入两个特定的列，如下所示 −

ij>INSERT INTO table_name VALUES (column_name1, column_name2, ...) VALUES
   (value1, value2, ...);

Note − Apache Derby 会自动计算生成列的值。例如，不需要传递本教程前面创建的 student 表中 id 列的值。如果您的表生成了列，请使用 syntax2 。

Example

ij> INSERT INTO Student(Age, First_Name, Last_Name) VALUES (21, 'Sucharitha' , 'Tyagi');
1 row inserted/updated/deleted

And, you can also insert two rows using one statement as follows −

ij>INSERT INTO Student(Age, First_Name, Last_Name) VALUES
   (20, 'Amit', 'Bhattacharya'), (22, 'Rahul', 'Desai');
2 rows inserted/updated/deleted

You can verify the contents of the table using the SELECT command (we will discuss this command later in this tutorial).

Syntax 3

You can use another query in the insert statement as −

INSERT INTO table_Name Query

Example

Suppose, we have a table named First_Year in the database as shown below with similar columns as in Student table −

ID |AGE |FIRST_NAME |LAST_NAME
-----------------------------------------------------------------
1  |20  |Raju       |Pendyala
2  |21  |Bhargav    |Prayaga
3  |22  |Deepthi    |Yerramilli

You can insert values in this table to the student table using the above syntax as −

ij> INSERT INTO Student (Age, First_Name, Last_Name)
   SELECT Age, First_Name, Last_Name FROM First_Year;
> 3 rows inserted/updated/deleted

After executing all the above insert statements, the Student table will be as follows −

ID |AGE |FIRST_NAME |LAST_NAME
-------------------------------------------------------------
1  |21  |Sucharitha |Tyagi
2  |20  |Amit       |Bhattacharya
3  |22  |Rahul      |Desai
4  |20  |Raju       |Pendyala
5  |21  |Bhargav    |Prayaga
6  |22  |Deepthi    |Yerramilli

Insert Data using JDBC program

This section teaches you how to insert data in to a table in Apache Derby database using JDBC application.

If you want to request the Derby network server using network client, make sure that the server is up and running. The class name for the Network client driver is org.apache.derby.jdbc.ClientDriver and the URL is jdbc:derby://localhost:1527/DATABASE_NAME;*create=true;user=*USER_NAME; password=PASSWORD"

Follow the steps given below to insert data into a table in Apache Derby −

Step 1: Register the driver

To communicate with the database, first of all, you need to register the driver. The forName() method of the class, Class accepts a String value representing a class name loads it in to the memory, which automatically registers it. Register the driver using this method.

Step 2: Get the connection

通常，我们与数据库进行通信时执行的第一步就是与数据库连接。类 Connection 表示与数据库服务器之间的物理连接。你可以通过调用类 DriverManager 的方法 getConnection() 创建连接对象。使用此方法来创建连接。

Step 3: Create a statement object

You need to create a Statement or PreparedStatement or, CallableStatement objects to send SQL statements to the database. You can create these using the methods createStatement(), prepareStatement() and, prepareCall() respectively. Create any of these objects using the appropriate method.

Step 4: Execute the query

After creating a statement, you need to execute it. The Statement class provides various methods to execute a query like the execute() method to execute a statement that returns more than one result set.

executeUpdate() 方法执行 INSERT、UPDATE 和 DELETE 之类的查询。 executeQuery() 方法对返回数据等的结果进行处理。使用这两种方法中的任一种，并执行先前创建的语句。

Example

以下 JDBC 示例演示了如何使用 JDBC 程序将数据插入 Apache Derby 中的表。在此处，我们使用嵌入式驱动程序连接到名为 sampleDB（如果不存在则创建）的数据库。

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class InsertData {
   public static void main(String args[]) throws Exception {
      //Registering the driver
      Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
      //Getting the Connection object
      String URL = "jdbc:derby:SampleDB;create=true";
      Connection conn = DriverManager.getConnection(URL);

      //Creating the Statement object
      Statement stmt = conn.createStatement();

      //Creating a table and populating
      String query = "CREATE TABLE Employees("
         + "Id INT NOT NULL GENERATED ALWAYS AS IDENTITY, "
         + "Name VARCHAR(255), Salary INT NOT NULL, "
         + "Location VARCHAR(255), "
         + "PRIMARY KEY (Id))";
      //Executing the query
      String query = "INSERT INTO Employees("
         + "Name, Salary, Location) VALUES "
         + "('Amit', 30000, 'Hyderabad'), "
         + "('Kalyan', 40000, 'Vishakhapatnam'), "
         + "('Renuka', 50000, 'Delhi'), "
         + "('Archana', 15000, 'Mumbai'), "
         + "('Trupthi', 45000, 'Kochin'), "
         + "('Suchatra', 33000, 'Pune'), "
         + "('Rahul', 39000, 'Lucknow'), "
         + "('Trupti', 45000, 'Kochin')";
      stmt.execute(query);
      System.out.println("Values inserted");
   }
}

Output

执行上述程序时，你将得到以下输出：

Values inserted