Mysql удалить строку java

JDBC Delete All Rows In Table Example

In this tutorial provide the example of how to delete all records from table using mysql JDBC driver.

In this tutorial provide the example of how to delete all records from table using mysql JDBC driver.

JDBC Delete All Rows In Table Example:

In this tutorial provide the example of how to delete all records from table using mysql JDBC driver. This tutorial use the «com.mysql.jdbc.Driver» driver and java class «DeleteAllRows» that import all related class and also defined all related variable .

This tutorial first import all related classes, register the JDBC driver, open the connection from database server, again create query and execute. There use mysql query «DELETE FROM user» that execute and delete all records from table and display output «Deleted All Rows In The Table Successfully. «. If database or table not exist give run time exception. If table already empty then count deleted rows o and display output «Table already empty.». The «DeleteAllRows.java» code given below as:

import java.sql.DriverManager; import java.sql.Connection; import java.sql.Statement; import java.sql.SQLException; public class DeleteAllRows< // JDBC driver name and database URL static String driverName = "com.mysql.jdbc.Driver"; static String url = "jdbc:mysql://localhost:3306/"; // defined and set value in dbName, userName and password variables static String dbName = "testjdbc"; static String userName = "root"; static String password = ""; public static void main(String[] args)< // create Connection con, and Statement stmt Connection con; Statement stmt; try< Class.forName(driverName).newInstance(); con = DriverManager.getConnection(url+dbName, userName, password); try< stmt = con.createStatement(); String query = "DELETE FROM user"; int deletedRows=stmt.executeUpdate(query); if(deletedRows>0)< System.out.println("Deleted All Rows In The Table Successfully. "); >else < System.out.println("Table already empty."); >> catch(SQLException s) < System.out.println("Deleted All Rows In Table Error. "); s.printStackTrace(); >// close Connection con.close(); >catch (Exception e) < e.printStackTrace(); >> > 
Program Output :
F:\jdbc>java DeleteAllRows 
Deleted All Rows In The Table Successfully.
F:\jdbc>java DeleteAllRows 
Table already empty.
Источник
Mysql удалить строку java


Для добавления, редактирования и удаления данных мы можем ипользовать рассмотренный в прошлой теме метод executeUpdate . С помощью результата метода мы можем проконтроллировать, сколько строк было добавлено, изменено или удалено.
Добавление
Так, возьмем созданную в прошлой теме таблицу Products:
CREATE TABLE Products ( Id INT PRIMARY KEY AUTO_INCREMENT, ProductName VARCHAR(20), Price INT )
 
И добавим в эту таблицу несколько объектов:
 
import java.sql.*; public class Program < public static void main(String[] args) < try< String url = "jdbc:mysql://localhost/store?serverTimezone=Europe/Moscow&useSSL=false"; String username = "root"; String password = "password"; Class.forName("com.mysql.cj.jdbc.Driver").getDeclaredConstructor().newInstance(); try (Connection conn = DriverManager.getConnection(url, username, password))< Statement statement = conn.createStatement(); int rows = statement.executeUpdate("INSERT Products(ProductName, Price) VALUES ('iPhone X', 76000)," + "('Galaxy S9', 45000), ('Nokia 9', 36000)"); System.out.printf("Added %d rows", rows); >> catch(Exception ex) < System.out.println("Connection failed. "); System.out.println(ex); >> >
 
Для добавления данных в БД применяется команда INSERT. В данном случае в таблицу Products добавляется три объекта. И после выполнения программы на консоли мы увидим число добавленных объектов:
 
C:\Java>javac Program.java C:\Java>java -classpath c:\Java\mysql-connector-java-8.0.11.jar;c:\Java Program Added 3 rows C:\Java>
 
А добавленные строки мы можем увидеть в таблице в бд MySQL:
 
 
 
Редактирование
 
Изменим строки в таблице, например, уменьшим цену товара на 5000 единиц. Для изменения применяется команда UPDATE:
 
import java.sql.*; public class Program < public static void main(String[] args) < try< String url = "jdbc:mysql://localhost/store?serverTimezone=Europe/Moscow&useSSL=false"; String username = "root"; String password = "password"; Class.forName("com.mysql.cj.jdbc.Driver").getDeclaredConstructor().newInstance(); try (Connection conn = DriverManager.getConnection(url, username, password))< Statement statement = conn.createStatement(); int rows = statement.executeUpdate("UPDATE Products SET Price = Price - 5000"); System.out.printf("Updated %d rows", rows); >> catch(Exception ex) < System.out.println("Connection failed. "); System.out.println(ex); >> >
 
Удаление
 
Удалим один объект из таблицы с помощью команды DELETE:
 
import java.sql.*; public class Program < public static void main(String[] args) < try< String url = "jdbc:mysql://localhost/store?serverTimezone=Europe/Moscow&useSSL=false"; String username = "root"; String password = "password"; Class.forName("com.mysql.cj.jdbc.Driver").getDeclaredConstructor().newInstance(); try (Connection conn = DriverManager.getConnection(url, username, password))< Statement statement = conn.createStatement(); int rows = statement.executeUpdate("DELETE FROM Products WHERE row(s) deleted", rows); >> catch(Exception ex) < System.out.println("Connection failed. "); System.out.println(ex); >> >
 
Как видно из примеров, не так сложно взаимодействовать с базой данных. Достаточно передать в метод executeUpdate нужную команду SQL.
 
Источник
 
A Java MySQL DELETE example
 
Here’s a Java MySQL DELETE example, demonstrating how to issue a SQL DELETE command from your Java source code.
 
A simple MySQL database table
 
The first thing we’ll need is an example MySQL database table to work with. To keep it simple — but also show several different data types — I created the following MySQL database table:
 
create table users ( id int unsigned auto_increment not null, first_name varchar(32) not null, last_name varchar(32) not null, date_created timestamp default now(), is_admin boolean, num_points int, primary key (id) );
 
A few of these fields are a little contrived, but I wanted to show several different data types in one table, so this is what I came up with. (In particular, the field "num_points" is a little unusual. I made it up so I could show an int data type in this table, and I was thinking of those websites where points are awarded for giving correct answers.)
 
Other than that, this MySQL database table is relatively normal, though it is greatly simplified.
 
An example MySQL SELECT statement
 
Before looking at the Java source code, if I now execute this SQL query from the MySQL command prompt:
 
I currently see this output:
 
+----+------------+-----------+---------------------+----------+------------+ | id | first_name | last_name | date_created | is_admin | num_points | +----+------------+-----------+---------------------+----------+------------+ | 2 | Fred | Flinstone | 2010-06-23 00:00:00 | 0 | 6000 | | 3 | Barney | Rubble | 2010-06-23 00:00:00 | 0 | 5000 | +----+------------+-----------+---------------------+----------+------------+ 2 rows in set (0.00 sec)
 
This is important to see, because I'm about to delete that "Barney Rubble" data record.
 
Java MySQL DELETE example - source code
 
Given that MySQL database table, let's assume that we just want to delete one record in this table. To do so, we just need to follow these steps:
 
 
Create a Java Connection to our MySQL database.
 
Create a SQL DELETE query statement.
 
Create a Java PreparedStatement for our SQL DELETE query.
 
Set the fields on our Java PreparedStatement object.
 
Execute our Java PreparedStatement .
 
Close our Java MySQL database connection.
 
Catch any SQL exceptions that may come up during the process.
 
 
I've tried to document the following Java MySQL DELETE example so you can see these steps. Note that in this example my MySQL database username is "root", my password is blank, and the MySQL database is running on the same computer where this program is run, so the database host name is "localhost".
 
import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; /** * A Java MySQL DELETE example. * Demonstrates the use of a SQL DELETE statement against a * MySQL database, called from a Java program, using a * Java PreparedStatement. * * Created by Alvin Alexander, http://devdaily.com */ public class JavaMysqlDeleteExample < public static void main(String[] args) < try < // create the mysql database connection String myDriver = "org.gjt.mm.mysql.Driver"; String myUrl = "jdbc:mysql://localhost/test"; Class.forName(myDriver); Connection conn = DriverManager.getConnection(myUrl, "root", ""); // create the mysql delete statement. // i'm deleting the row where the id is "3", which corresponds to my // "Barney Rubble" record. String query = "delete from users where PreparedStatement preparedStmt = conn.prepareStatement(query); preparedStmt.setInt(1, 3); // execute the preparedstatement preparedStmt.execute(); conn.close(); >catch (Exception e) < System.err.println("Got an exception! "); System.err.println(e.getMessage()); >> >
 
Java MySQL DELETE example - discussion
 
As noted in the Java MySQL DELETE source code, this SQL DELETE query is hard-wired to delete the row in the database where the "id" column has a value of "3". I know from looking at my database that this is the record corresponding to the "Barney Rubble" data. In a real-world program, you'll likely have the id for the user record you want to delete, so your SQL DELETE query will look very similar to the one shown here, except you'll have a variable in the place where I have hard-coded the number three.
 
After this query runs, you can verify that it worked by looking at the data from the MySQL command prompt, running a SELECT query like this:
 
where you will see some output like this:
 
+----+------------+-----------+---------------------+----------+------------+ | id | first_name | last_name | date_created | is_admin | num_points | +----+------------+-----------+---------------------+----------+------------+ | 2 | Fred | Flinstone | 2010-06-23 14:02:00 | 0 | 6000 | +----+------------+-----------+---------------------+----------+------------+ 1 row in set (0.00 sec)
 
Java MySQL DELETE example - summary
 
These days, in "real world" Java database programs I almost always use the Spring JDBC libraries to access a database, but when you're first getting started, I think it's important to see examples like this so you can understand how things work under the covers.
 
In summary, this example demonstrated:
 
 
How to connect to a MySQL database.
 
How to write a Java MySQL DELETE query (for use with a Java PreparedStatement ).
 
How to set the desired field values for a Java PreparedStatement .
 
How to execute the Java PreparedStatement .
 
How to close the Java MySQL database connection.
 
One way to confirm that our data was successfully deleted in our MySQL database.
 
 
I hope this Java MySQL DELETE example (using a Java PreparedStatement ) makes sense. As usual, if you have any questions or comments about this example, just use the Comment form below.
 
Источник
 
How to delete data in a MySQL database with Java?
 
Delete data from a MySQL database with the help of DELETE command. The syntax is as follows.
 
delete from yourTableName where condition;
 
I will delete data from a MySQL database with the help of JAVA programming language. First, create a table and insert some records. The following is the query to create a table.
 
mysql> create table DeleteTableDemo -> ( -> id int, -> Name varchar(200) -> ); Query OK, 0 rows affected (0.94 sec)
 
Insert records in the above table. The query to insert records is as follows.
 
mysql> insert into DeleteTableDemo values(101,'Smith'); Query OK, 1 row affected (0.21 sec) mysql> insert into DeleteTableDemo values(102,'Johnson'); Query OK, 1 row affected (0.27 sec)
 
Now we can check how many records are in my table. The query is as follows.
 
mysql> select *from DeleteTableDemo;
 
The following is the output.
 
+------+---------+ | id | Name | +------+---------+ | 101 | Smith | | 102 | Johnson | +------+---------+ 2 rows in set (0.00 sec)
 
We have two records in the table. Now, let us deleted data from a MySQL database table with the help of delete command. Here is the JAVA code that deletes the data with Before that, we will establish a Java Connection to our MySQL database.
 
import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import com.mysql.jdbc.Connection; import com.mysql.jdbc.PreparedStatement; import com.mysql.jdbc.Statement; public class JavaDeleteDemo < public static void main(String[] args) < Connection conn = null; Statement stmt = null; try < try < Class.forName("com.mysql.jdbc.Driver"); >catch (Exception e) < System.out.println(e); >conn = (Connection) DriverManager.getConnection("jdbc:mysql://localhost/business", "Manish", "123456"); System.out.println("Connection is created successfully:"); stmt = (Statement) conn.createStatement(); String query1 = "delete from DeleteTableDemo " + "where stmt.executeUpdate(query1); System.out.println("Record is deleted from the table successfully. "); > catch (SQLException excep) < excep.printStackTrace(); >catch (Exception excep) < excep.printStackTrace(); >finally < try < if (stmt != null) conn.close(); >catch (SQLException se) <> try < if (conn != null) conn.close(); >catch (SQLException se) < se.printStackTrace(); >> System.out.println("Please check it in the MySQL Table. Record is now deleted. "); > >
 
The following is the output.
 
 
mysql> select *from DeleteTableDemo;
 
The following is the output.
 
+------+---------+ | id | Name | +------+---------+ | 102 |Johnson | +------+---------+ 1 row in set (0.00 sec) We have deleted the data with id 101.
 
Источник
 
 Java - how to make MySQL delete query with JDBC? 
 
 
 Brian-Tompset
 
In Java it is possible to make SQL DELETE query with JDBC in following way.
 
 
Note: read this article to know how to download and install JDBC driver proper way.
 
 
1. DELETE query with JDBC example
 
 
Note: this approach prevents of SQL Injection attack.
 
 
package com.dirask.examples; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.SQLException; public class Program < private static final String DB_NAME = "test"; private static final String DB_HOST = "127.0.0.1"; // 'localhost' private static final String DB_USER = "root"; private static final String DB_PASSWORD = "root"; private static final String DB_URL = "jdbc:mysql://" + DB_HOST + "/" + DB_NAME + "?serverTimezone=UTC"; public static void main(String[] args) throws ClassNotFoundException < String sql = "DELETE FROM `users` WHERE `name` = ?"; try ( // gets connection with database Connection connection = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD); // sends queries and receives results PreparedStatement statement = connection.prepareStatement(sql); ) < // this way to prevent sql injection statement.setString(1, "John"); int count= statement.executeUpdate(); System.out.print("Number of deleted rows is " + count + "."); >catch (SQLException e) < // some logic depending on scenario // maybe LOGGER.log(e.getMessage()) and "result false" e.printStackTrace(); >> >
 
Number of deleted rows is 1.
 
 
2. Data base preparation
 
CREATE TABLE `users` ( `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `name` VARCHAR(100) NOT NULL, `role` VARCHAR(15) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB;
 
INSERT INTO `users` (`name`, `role`) VALUES ('John', 'admin'), ('Chris', 'moderator'), ('Kate', 'user'), ('Denis', 'moderator');
 
See also
 
Источник
 
Читайте также:  Python pre commit hook