Java Database Programming Bible- P5

Java Database Programming Bible- P5: Welcome to Java Database Programming Bible. This book is for readers who are alreadyfamiliar with Java, and who want to know more about working with databases. The JDBCApplication Programming Interface has made database programming an importantaspect of Java development, particularly where Web applications are concerned.
Nội dung trích xuất từ tài liệu:
Java Database Programming Bible- P5 Chapter 6:Inserting, Updating, and Deleting Data Using INSERT ... SELECT The INSERT statement illustrated in the example of Listing 6-1 is primarily intended for inserting records into a table one at a time. For applications such as storing information from membership applications or entering employee records, this is the perfect solution. However, there are times when you want to copy subsets of data from one table to another. On these occasions, doing the transfer one record at a time introduces a lot of overhead because each record has to be individually retrieved from one table and inserted into another other. SQL allows you to handle these situations by combining the INSERT command with a SELECT command, which queries the database for the desired records. The advantage of this approach is that the whole process is carried out within the RDBMS, avoiding the overhead of retrieving records and reinserting them externally. The SELECT statement The SELECT statement is used to query the database for specific rows. This is the basic form of the SELECT statement: SELECT Field1, Field2, ... Y FROM FL TableName AM [ WHERE ... ]; In place of a comma-delimited list of field names, you can supply the asterisk wildcard character, *, to TE request all fields: SELECT * FROM TableName; Cross-Reference The SELECT statement is discussed in detail in Chapter 7. An example of a situation where you might use INSERT...SELECT is the creation of a table containing only the first and last names from the Contact_Info Table. As illustrated in Chapter 5, the SQL command to create the table is as follows: CREATE TABLE Names (First_Name VARCHAR(20), Last_Name LName VARCHAR(30)); To insert the corresponding data from the original Contact_Info Table, use a SQL INSERT...SELECT command to select the desired fields from the Contact_Info Table, and insert them into the new Names Table. Heres an example: INSERT INTO Names SELECT First_Name, Last_Name FROM Contact_Info; Essentially, this command tells the database management system to perform these two separate operations internally: -199 - Team-Fly®Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Chapter 6:Inserting, Updating, and Deleting Data A SELECT (to query the Contact_Info Table for the FName and LName fields from all records) An INSERT (to input the resulting record set into the new Names Table By performing these operations within the RDBMS, the use of the INSERT...SELECT command eliminates the overhead of retrieving the records and reinserting them. The WHERE clause The optional WHERE clause allows you to make conditional queries; for example, you can get all records where the last name is Corleone and insert them into the Names Table with this statement: INSERT INTO Names SELECT First_Name, Last_Name FROM Contact_Info WHERE Last_Name = Corleone; Using INSERT ... SELECT with JDBC As with any other SQL command, it is easy to use INSERT ... SELECT with JDBC. If you substitute the code snippet of Listing 6-2 for the main() of Listing 6-1 and run the example again, you will create a Name Table populated with the first and last names. Listing 6-2: Using INSERT ... SELECT with JDBC public static void main(String args[]){ DataInserter inserter = new DataInserter(); String SQLCommand = INSERT INTO NAMES + SELECT First_Name,Last_Name FROM CONTACT_INFO + WHERE Last_Name = Corleone; ; inserter.execute(SQLCommand); } } Once you have data in a table, you are likely to have to update it to reflect changes in data fields like addresses or inventory item count. The next section shows how to use the SQL UPDATE command to m ...