Sql update java oracle

Как извлечь запись из существующей таблицы в базе данных Oracle с помощью JDBC API

Вы можете обновить / изменить существующее содержимое записи в таблице, используя запрос UPDATE. Используя это, вы можете обновить все записи таблицы или конкретные записи.

Синтаксис

UPDATE table_name SET column1 = value1, column2 = value2. columnN = valueN WHERE [condition];

Для обновления содержимого записи в таблице с использованием JDBC API вам необходимо:

1. Зарегистрируйте драйвер: зарегистрируйте класс драйвера с помощью метода registerDriver() класса DriverManager. Передайте ему имя класса драйвера в качестве параметра.
2. Установите соединение: подключитесь к базе данных с помощью метода getConnection() класса DriverManager. Передав URL-адрес (String), имя пользователя (String), пароль (String) в качестве параметров.
3. Create Statement: создайте объект Statement с помощью метода createStatement() интерфейса Connection.
4. Выполнить запрос: выполнить запрос с помощью метода executeUpdate() интерфейса Statement.

Давайте создадим таблицу с диспетчеризацией имен в базе данных Oracle, используя оператор CREATE, как показано ниже:

CREATE TABLE Dispatches( PRODUCTNAME VARCHAR2(20), CUSTOMERNAME VARCHAR2(20), DISPATCHDATE DATE, DELIVERYTIME TIMESTAMP(6), PRICE NUMBER(38), LOCATION VARCHAR2(20) );

Теперь мы вставим 5 записей в таблицу диспетчеризации, используя операторы INSERT.

insert into dispatches values('Key-Board', 'Raja', TO_DATE('2019-09-01', 'yyyy/mm/dd'), TO_DATE('11:00:00', 'hh:mi:ss'), 7000, 'India'); insert into dispatches values('Earphones', 'Roja', TO_DATE('2019-05-01', 'yyyy/mm/dd'), TO_DATE('11:00:00', 'hh:mi:ss'), 2000, 'Vishakhapatnam'); insert into dispatches values('Mouse', 'Puja', TO_DATE('2019-03-01', 'yyyy/mm/dd'), TO_DATE('10:59:59', 'hh:mi:ss'), 3000, 'Vijayawada'); insert into dispatches values('Mobile', 'Vanaja', TO_DATE('2019-03-01', 'yyyy/mm/dd'), TO_DATE('10:10:52', 'hh:mi:ss'), 9000, 'Chennai'); insert into dispatches values('Headset', 'Jalaja', TO_DATE('2019-04-06', 'yyyy/mm/dd'), TO_DATE('11:08:59', 'hh:mi:ss' ), 6000, 'Goa');

Вслед за программой JDBC устанавливается связь с базой данных Oracle и увеличивается цена каждого продукта на 3000.

Пример извлечения

import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class UpdateRecordsExample < public static void main(String args[]) throws SQLException < //Registering the Driver DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver()); //Getting the connection String oracleUrl = "jdbc:oracle:thin:@localhost:1521/xe"; Connection con = DriverManager.getConnection(oracleUrl, "system", "password"); System.out.println("Connection established. "); //Creating the Statement Statement stmt = con.createStatement(); //Query to update records, Increasing the price of all items by 3000 String query = "Update dispatches set PRICE = PRICE+3000"; //Executing the query int i = stmt.executeUpdate(query); System.out.println("Rows updated: "+i); System.out.println("Contents of the dispatches table after updating the records: "); //Retrieving data ResultSet rs = stmt.executeQuery("Select * from dispatches"); while(rs.next()) < System.out.print("Name: "+rs.getString("ProductName")+", "); System.out.print("Customer Name: "+rs.getString("CustomerName")+", "); System.out.print("Dispatch Date: "+rs.getDate("DispatchDate")+", "); System.out.print("Delivery Time: "+rs.getTime("DeliveryTime")+", "); System.out.print("Price: "+rs.getInt("Price")+", "); System.out.print("Location: "+rs.getString("Location")); System.out.println(); >> >

Получилось извлечь следующее:

Connection established. Rows updated: 5 Contents of the dispatches table after updating the records: Name: Key-Board, Customer Name: Raja, Dispatch Date: 2019-09-01, Delivery Time: 11:00:00, Price: 10001, Location: Hyderabad Name: Earphones, Customer Name: Roja, Dispatch Date: 2019-05-01, Delivery Time: 11:00:00, Price: 5000, Location: Vishakhapatnam Name: Mouse, Customer Name: Puja, Dispatch Date: 2019-03-01, Delivery Time: 10:59:59, Price: 6000, Location: Vijayawada Name: Mobile, Customer Name: Vanaja, Dispatch Date: 2019-03-01, Delivery Time: 10:10:52, Price: 12001, Location: Chennai Name: Headset, Customer Name: Jalaja, Dispatch Date: 2019-04-06, Delivery Time: 11:08:59, Price: 9000, Location: Goa

Средняя оценка 0 / 5. Количество голосов: 0

Спасибо, помогите другим — напишите комментарий, добавьте информации к статье.

Видим, что вы не нашли ответ на свой вопрос.

Напишите комментарий, что можно добавить к статье, какой информации не хватает.

Источник

UPDATE records in a table with JDBC

The JDBC classes and interfaces are available in java.sql and javax.sql packages.

Steps to update records in a table with UPDATE SQL statement using JDBC —

Class.forName("oracle.jdbc.driver.OracleDriver");

In the upcoming example, we are going to create a table within the Oracle database using its JDBC driver.

Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE","scott", "tiger");

• 1521 is the general port number to connect to the database.
• XE stands for Oracle Express Edition Database.
• scott is our username to connect to the Oracle database and tiger is the password.

Statement stmt = con.createStatement();

Creating a Statement object to using createStatement() method of Connection interface.

int count = stmt.executeUpdate(query);

count gives us the total number of rows updated in a database table due to the execution of a SQL query using executeUpdate() method, where query is a String object which specifies the SQL query to execute.

con is a Connection reference used to close the connection with the database after the updation is over.

Updating data in database table using JDBC

 import java.sql.*; class A < public static void main(String. ar) < try < //First SQL UPDATE Query to update record. String query1 = "Update MyTable2 Set FirstName='Thomas' Where FirstName = 'Tom'"; //Second SQL UPDATE Query to update record. String query2 = "Update MyTable2 Set FirstName='Bradly' where age = '53'"; //Third SQL SELECT Query to retrieve updated records. String query3 = "SELECT * FROM MyTable2"; Class.forName("oracle.jdbc.driver.OracleDriver"); Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE","System", "Promila21"); Statement stmt = con.createStatement(); //Executing first SQL UPDATE query using executeUpdate() method of Statement object. int count = stmt.executeUpdate(query1); System.out.println("Number of rows updated by executing query1 = " + count); //Executing second SQL UPDATE query using executeUpdate() method of Statement object. count = stmt.executeUpdate(query2); System.out.println("Number of rows updated by executing query2 = " + count); //Executing SQL SELECT query using executeQuery() method of Statement object. ResultSet rs = stmt.executeQuery(query3); System.out.println("Result of executing query3 to display updated records"); System.out.println("ID " + "\t" + "FirstName" + "\t" + "LastName" + "\t" + "Age"); //looping through the number of row/rows retrieved after executing SELECT query3 while(rs.next()) < System.out.print(rs.getString("ID") + "\t"); System.out.print(rs.getString("FirstName") + "\t" + "\t"); System.out.print(rs.getString("LastName")+ "\t" + "\t"); System.out.println(rs.getString("Age") + "\t"); >> catch(SQLException e) < System.out.println(e); >>//main() method ends >//class definitin ends 

Output

Number of rows updated by executing query1 = 1 Number of rows updated by executing query2 = 1 Result of executing query3 to display updated records ID FirstName LastName Age 1 Thomas Hanks 61 2 Johnny Depp 54 3 Bradly Pitt 53

In the output, you may see the updated records in table MyTable, where FirstName Tom is changed to Thomas and Brad is changed to Bradley.

ID	FirstName	LastName	Age
1	Thomas	Hanks	61
2	Johnny	Depp	54
3	Bradley	Pitt	53

Table — MyTable

Источник

Using Prepared Statements

Sometimes it is more convenient to use a PreparedStatement object for sending SQL statements to the database. This special type of statement is derived from the more general class, Statement , that you already know.

If you want to execute a Statement object many times, it usually reduces execution time to use a PreparedStatement object instead.

The main feature of a PreparedStatement object is that, unlike a Statement object, it is given a SQL statement when it is created. The advantage to this is that in most cases, this SQL statement is sent to the DBMS right away, where it is compiled. As a result, the PreparedStatement object contains not just a SQL statement, but a SQL statement that has been precompiled. This means that when the PreparedStatement is executed, the DBMS can just run the PreparedStatement SQL statement without having to compile it first.

Although you can use PreparedStatement objects for SQL statements with no parameters, you probably use them most often for SQL statements that take parameters. The advantage of using SQL statements that take parameters is that you can use the same statement and supply it with different values each time you execute it. Examples of this are in the following sections.

However, the most important advantage of prepared statements is that they help prevent SQL injection attacks. SQL injection is a technique to maliciously exploit applications that use client-supplied data in SQL statements. Attackers trick the SQL engine into executing unintended commands by supplying specially crafted string input, thereby gaining unauthorized access to a database to view or manipulate restricted data. SQL injection techniques all exploit a single vulnerability in the application: Incorrectly validated or nonvalidated string literals are concatenated into a dynamically built SQL statement and interpreted as code by the SQL engine. Prepared statements always treat client-supplied data as content of a parameter and never as a part of an SQL statement. See the section SQL Injection in Database PL/SQL Language Reference, part of Oracle Database documentation, for more information.

The following method, CoffeesTable.updateCoffeeSales , stores the number of pounds of coffee sold in the current week in the SALES column for each type of coffee, and updates the total number of pounds of coffee sold in the TOTAL column for each type of coffee:

public void updateCoffeeSales(HashMap salesForWeek) throws SQLException < String updateString = "update COFFEES set SALES = ? where COF_NAME = ?"; String updateStatement = "update COFFEES set TOTAL = TOTAL + ? where COF_NAME = ?"; try (PreparedStatement updateSales = con.prepareStatement(updateString); PreparedStatement updateTotal = con.prepareStatement(updateStatement)) < con.setAutoCommit(false); for (Map.Entrye : salesForWeek.entrySet()) < updateSales.setInt(1, e.getValue().intValue()); updateSales.setString(2, e.getKey()); updateSales.executeUpdate(); updateTotal.setInt(1, e.getValue().intValue()); updateTotal.setString(2, e.getKey()); updateTotal.executeUpdate(); con.commit(); >> catch (SQLException e) < JDBCTutorialUtilities.printSQLException(e); if (con != null) < try < System.err.print("Transaction is being rolled back"); con.rollback(); >catch (SQLException excep) < JDBCTutorialUtilities.printSQLException(excep); >> > >

Creating a PreparedStatement Object

The following creates a PreparedStatement object that takes two input parameters:

String updateString = "update COFFEES " + "set SALES = ? where COF_NAME = ?"; // . PreparedStatement updateSales = con.prepareStatement(updateString);

Supplying Values for PreparedStatement Parameters

You must supply values in place of the question mark placeholders (if there are any) before you can execute a PreparedStatement object. Do this by calling one of the setter methods defined in the PreparedStatement class. The following statements supply the two question mark placeholders in the PreparedStatement named updateSales :

updateSales.setInt(1, e.getValue().intValue()); updateSales.setString(2, e.getKey());

The first argument for each of these setter methods specifies the question mark placeholder. In this example, setInt specifies the first placeholder and setString specifies the second placeholder.

After a parameter has been set with a value, it retains that value until it is reset to another value, or the method clearParameters is called. Using the PreparedStatement object updateSales , the following code fragment illustrates reusing a prepared statement after resetting the value of one of its parameters and leaving the other one the same:

// changes SALES column of French Roast //row to 100 updateSales.setInt(1, 100); updateSales.setString(2, "French_Roast"); updateSales.executeUpdate(); // changes SALES column of Espresso row to 100 // (the first parameter stayed 100, and the second // parameter was reset to "Espresso") updateSales.setString(2, "Espresso"); updateSales.executeUpdate();

Using Loops to Set Values

You can often make coding easier by using a for loop or a while loop to set values for input parameters.

The CoffeesTable.updateCoffeeSales method uses a for-each loop to repeatedly set values in the PreparedStatement objects updateSales and updateTotal :

for (Map.Entry e : salesForWeek.entrySet()) < updateSales.setInt(1, e.getValue().intValue()); updateSales.setString(2, e.getKey()); // . >

The method CoffeesTable.updateCoffeeSales takes one argument, HashMap . Each element in the HashMap argument contains the name of one type of coffee and the number of pounds of that type of coffee sold during the current week. The for-each loop iterates through each element of the HashMap argument and sets the appropriate question mark placeholders in updateSales and updateTotal .

Executing PreparedStatement Objects

As with Statement objects, to execute a PreparedStatement object, call an execute statement: executeQuery if the query returns only one ResultSet (such as a SELECT SQL statement), executeUpdate if the query does not return a ResultSet (such as an UPDATE SQL statement), or execute if the query might return more than one ResultSet object. Both PreparedStatement objects in CoffeesTable.updateCoffeeSales(HashMap) contain UPDATE SQL statements, so both are executed by calling executeUpdate :

updateSales.setInt(1, e.getValue().intValue()); updateSales.setString(2, e.getKey()); updateSales.executeUpdate(); updateTotal.setInt(1, e.getValue().intValue()); updateTotal.setString(2, e.getKey()); updateTotal.executeUpdate(); con.commit();

No arguments are supplied to executeUpdate when they are used to execute updateSales and updateTotals ; both PreparedStatement objects already contain the SQL statement to be executed.

Note: At the beginning of CoffeesTable.updateCoffeeSales , the auto-commit mode is set to false:

Consequently, no SQL statements are committed until the method commit is called. For more information about the auto-commit mode, see Transactions.

Return Values for the executeUpdate Method

Whereas executeQuery returns a ResultSet object containing the results of the query sent to the DBMS, the return value for executeUpdate is an int value that indicates how many rows of a table were updated. For instance, the following code shows the return value of executeUpdate being assigned to the variable n :

updateSales.setInt(1, 50); updateSales.setString(2, "Espresso"); int n = updateSales.executeUpdate(); // n = 1 because one row had a change in it 

The table COFFEES is updated; the value 50 replaces the value in the column SALES in the row for Espresso . That update affects one row in the table, so n is equal to 1.

When the method executeUpdate is used to execute a DDL (data definition language) statement, such as in creating a table, it returns the int value of 0. Consequently, in the following code fragment, which executes the DDL statement used to create the table COFFEES , n is assigned a value of 0:

// n = 0 int n = executeUpdate(createTableCoffees);

Note that when the return value for executeUpdate is 0, it can mean one of two things:

• The statement executed was an update statement that affected zero rows.
• The statement executed was a DDL statement.

Источник