java 插入数据，如果已经插入，则在 sql 中更新
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/36442307/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Insert data and if already inserted then update in sql
提问by zeeshan nisar
I simply want to insert the data to a SQL database table and if there is some data inserted already then I want to update that data. How can I do this using Java. Kindly help me, and sorry for bad English in advance.
我只是想将数据插入到 SQL 数据库表中，如果已经插入了一些数据，那么我想更新该数据。我如何使用 Java 执行此操作。请帮助我，并提前为不好的英语道歉。
采纳答案by Thush-Fdo
Just identify the unique itemin your data set (like Idor a code). Then by using that try to do a SELECTquery first. If the Resultsetis empty, do the INSERTelse try to UPDATEthe details.
只需识别数据集中的唯一项（如Id或code）。然后通过使用它尝试首先执行SELECT查询。如果结果集是空的，执行INSERT别的尝试UPDATE细节。
回答by Andreas
The standard SQL statement for INSERT(if new) or UPDATE(if exists) is called MERGE.
用于INSERT（如果是新的）或UPDATE（如果存在）的标准 SQL 语句被称为MERGE。
Since you didn't specify which DBMS dialect you're asking about, I'll refer you to the Wikipedia article "Merge (SQL)", which covers most DBMS dialects. Summary:
由于您没有指定您询问的是哪种 DBMS 方言，我将向您推荐维基百科文章“合并 (SQL)”，其中涵盖了大多数 DBMS 方言。概括：
MERGE INTO tablename USING table_reference ON (condition) WHEN MATCHED THEN UPDATE SET column1 = value1 [, column2 = value2 ...] WHEN NOT MATCHED THEN INSERT (column1 [, column2 ...]) VALUES (value1 [, value2 ...])Database management systems Oracle Database, DB2, Teradata, EXASOL, CUBRID, MS SQLand Vectorwisesupport the standard syntax. Some also add non-standard SQL extensions.
MySQL:
INSERT ... ON DUPLICATE KEY UPDATESQLite:
INSERT OR REPLACE INTOPostgreSQL:
INSERT INTO ... ON CONFLICT
MERGE INTO tablename USING table_reference ON (condition) WHEN MATCHED THEN UPDATE SET column1 = value1 [, column2 = value2 ...] WHEN NOT MATCHED THEN INSERT (column1 [, column2 ...]) VALUES (value1 [, value2 ...])数据库管理系统Oracle Database、DB2、Teradata、EXASOL、CUBRID、MS SQL和Vectorwise支持标准语法。有些还添加了非标准的 SQL 扩展。
MySQL：
INSERT ... ON DUPLICATE KEY UPDATESQLite:
INSERT OR REPLACE INTOPostgreSQL:
INSERT INTO ... ON CONFLICT
回答by npinti
回答by Patel Vicky
try to following way:
尝试以下方式：
Example Query
示例查询
INSERT INTO table (id, name, city) VALUES(1, "ABC", "XYZ") ON DUPLICATE KEY UPDATE
name="ABC", city="XYZ"
INSERT INTO table (id, name, city) VALUES(1, "ABC", "XYZ") ON DUPLICATE KEY UPDATE
name="ABC", city="XYZ"
for more help see documentation. Click here
如需更多帮助，请参阅文档。 点击这里
回答by Hitesh Dabhi
you have to first check the data exist in table if exist then use update query otherwise insert data its simple
您必须首先检查表中是否存在数据（如果存在）然后使用更新查询，否则插入数据很简单
回答by VALARMATHI
Set any field as the unique identity.For an example consider that employee details has to be entered in the table name **EmployeeDetails.**in this case employee_id can be considered as unique.
将任何字段设置为唯一标识。例如，考虑必须在表名 *EmployeeDetails.** 中输入员工详细信息。在这种情况下，employee_id 可以被视为唯一的。
use SELECTquery select * from EmployeeDetails where employee_id= "the unique keyvalue"; if the resultset is not empty then use UPDATEquery to update the fields.
使用SELECT查询 select * from EmployeeDetails where employee_id="the unique keyvalue"; 如果结果集不为空，则使用UPDATE查询来更新字段。
update EmployeeDetails set Employee_id=?,Full_name=?, Designation=?, Email_id=?, Password=? where Employee_id='" + id + "'"; If the resultset is empty then use the INSERT query to insert the values to the table
更新 EmployeeDetails 设置 Employee_id=?,Full_name=?, Designation=?, Email_id=?, Password=? where Employee_id='" + id + "'"; 如果结果集为空，则使用 INSERT 查询将值插入到表中
Insert into EmployeeDetails values(...)
插入 EmployeeDetails 值(...)
回答by VALARMATHI
package com.stackwork;
//STEP 1. Import required packages
import java.sql.*;
import java.util.Scanner;
public class Updation {
// JDBC driver name and database URL
static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
static final String DB_URL = "jdbc:mysql://localhost/Employee";
// Database credentials
static final String USER = "root";
static final String PASS = "admin";
private static Scanner sc;
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
try{
//STEP 2: Register JDBC driver
Class.forName("com.mysql.jdbc.Driver");
//STEP 3: Open a connection
System.out.println("Connecting to database...");
conn = DriverManager.getConnection(DB_URL,USER,PASS);
//STEP 4: Execute a query
System.out.println("Creating statement...");
stmt = conn.createStatement();
String sql;
//STEP 5: Get the employee_id for whom data need to be updated/inserted
sc = new Scanner(System.in);
System.out.println("Enter the Employee_id for the record to be updated or inserted");
int Emp_idvalue=sc.nextInt();
sql = "SELECT * FROM EmployeeDetails where Emp_id="+Emp_idvalue;
ResultSet rs = stmt.executeQuery(sql);
if (!rs.next())
{
//STEP 6: If the previous details is not there ,then the details will be inserted newly
System.out.println("Enter the name to be inserted");
String Emp_namevalue =sc.next();
System.out.println("Enter the address to be inserted");
String Emp_addvalue =sc.next();
System.out.println("Enter the role to be inserted");
String Emp_rolevalue =sc.next();
PreparedStatement ps = conn
.prepareStatement("insert into EmployeeDetails values(?,?,?,?)");
ps.setString(2, Emp_namevalue);
ps.setString(3, Emp_addvalue);
ps.setString(4, Emp_rolevalue);
ps.setInt(1, Emp_idvalue);
ps.executeUpdate();
System.out.println("Inserted successfully");
}
else
{
//STEP 7: If the previous details is there ,then the details will be updated
System.out.println("Enter the name to be updated");
String Emp_namevalue =sc.next();
System.out.println("Enter the address to be updated");
String Emp_addvalue =sc.next();
System.out.println("Enter the role to be updated");
String Emp_rolevalue =sc.next();
String updateQuery = "update EmployeeDetails set Emp_id=?,Emp_name=?, Emp_address=?, Emp_role=? where Emp_id='"
+ Emp_idvalue + "'";
PreparedStatement ps1 = conn.prepareStatement(updateQuery);
ps1.setString(2, Emp_namevalue);
ps1.setString(3, Emp_addvalue);
ps1.setString(4, Emp_rolevalue);
ps1.setInt(1, Emp_idvalue);
ps1.executeUpdate();
System.out.println("updated successfully");
}
//Clean-up environment
rs.close();
stmt.close();
conn.close();
}catch(SQLException se){
//Handle errors for JDBC
se.printStackTrace();
}catch(Exception e){
//Handle errors for Class.forName
e.printStackTrace();
}
}
}
