Java BASICS (13) ----- add, delete, modify, and query in JDBC

Relationship between CRUD and SQL in JDBC

The addition, deletion, modification, and query of databases by JDBC are the foundation of JDBC technology and the cornerstone of technologies such as transaction learning, batch processing, and updatable results in the future. The fundamental purpose of connecting JDBC to a database is to operate data. However, operations on data are to add, delete, modify, and query data, or "CRUD" for short ". SQL technology is used to add, delete, modify, and query databases. java programs are used to send SQL statements to the database, so that the database management system can parse the statements and return the corresponding results.

Database Structure

The data in the database exists in the form of a two-dimensional table ,:

SQL Introduction

SQL (Structured Query Language) is divided into DML (Database Operation Language) and DDL (database definition language ).

DML includes the following four parts:

SELECT ----- query statement

Insert into ----- INSERT statement <喎?http: www.bkjia.com kf ware vc " target="_blank" class="keylink"> Authorization + response/response + CjxwPjxzdHJvbmc + response/Csr + response + 3b/response + CjxwPjxzdHJvbmc + response + PC9lbT4tINDeuMTK/b7dv + I8ZW0 + response Authorization + cve-vzw0 + PC9zdHJvbmc + authorization/KOouMSx5KOpyv2 + 3b/ise08ZW0 + authorization/authorization + authorization/e8/authorization + s/authorization + PC9wPgo8aDI + PHN0cm9uZz7K/b7dv + primary/b7dv + KjrMq508PK/b7dv + K/primary/hidden/primary/b7doaO0 + sLryr7A/aO6PC9wPgo8cD48L3A + primary "brush: java; "> package com. jdbc; import java. SQL. connection; import java. SQL. driverManager; Import java. SQL. preparedStatement; import java. SQL. SQLException; import java. SQL. statement; import com. mysql. jdbc. driver; public class Test2 {/*** insert data in two ways */public static void main (String [] args) {try {insertTest_2 ();} catch (SQLException e) {e. printStackTrace () ;}}// the pre-compiled statement publicstatic void insertTest_1 () throws SQLException {// 1 is not used to register the driver DriverManager. registerDriver (new com. mysql. jdbc. driver (); // 2, get the link Co Nnection conn = DriverManager. getConnection ("jdbc: mysql: // localhost: 3306/Persons", "root", "root"); // 3, create Statement stmt = conn. createStatement (); String SQL = "insert into Person values (1, 'adams', 'john', 'oxford Street ', 'London')"; // 4, execute the statement int n1_stmt.exe cuteUpdate (SQL); if (n> 0) {System. out. println ("inserted successfully");} else {System. out. println ("insertion failed");} conn. close (); stmt. close () ;}// use precompiled statements: This effectively improves the efficiency and places SQL injection. Public static void insertTest_2 () throws SQLException {Connection conn = getConnection (); String SQL = "insert into Person values (?,?,?,?,?) "; PreparedStatement stmt = conn. prepareStatement (SQL); stmt. setInt (1, 4); stmt. setString (2, "Wang"); stmt. setString (3, "Wu"); stmt. setString (4, "Yushang Road"); stmt. setString (5, "Shanghai"); int n1_stmt.exe cuteUpdate (); if (n> 0) {System. out. println ("inserted successfully");} else {System. out. println ("insertion failed") ;}// extract the code for obtaining the Connection from the second method. public static Connection getConnection () {Connection conn = null; try {DriverManager. registerDriver (new com. mysql. jdbc. driver (); conn = DriverManager. getConnection ("jdbc: mysql: // localhost: 3306/Persons", "root", "root");} catch (SQLException e) {e. printStackTrace () ;}return conn ;}}
Pre-compiled statements are generally used in development to improve efficiency.

SELECT statement

First, create a Persons database. Sample Code

Package com. jdbc; import java. SQL. connection; import java. SQL. driverManager; import java. SQL. resultSet; import java. SQL. statement;/** get database name **/public class Test1 {public static void main (String [] args) {test ();} static void test () {// 1, register the driver try {DriverManager. registerDriver (new com. mysql. jdbc. driver (); // 2, establish Connection conn = DriverManager. getConnection ("jdbc: mysql: // localhost: 3306/Persons", "root", "root"); // 3, create Statement stmt = conn. createStatement (); // 4, execute the query ResultSet rs = stmt.exe cuteQuery ("select LastName from Person"); // 5, process the result while (rs. next () {System. out. println (rs. getString ("LastName") ;}} catch (Exception e) {e. printStackTrace ();}}}

DELETE statement

Sample Code:

Package com. jdbc; import java. SQL. connection; import java. SQL. driverManager; import java. SQL. SQLException; import java. SQL. statement; public class Test4 {/*** Delete the records of all cities in Shanghai */public static void main (String [] args) throws SQLException {Connection conn = getConn (); statement stmt = conn. createStatement (); int n1_stmt.exe cuteUpdate ("delete from Person where City = 'shanghai'"); if (n> 0) {System. out. println ("Total" + n + "Deleted Data") ;}} public static Connection getConn () {Connection conn = null; try {DriverManager. registerDriver (new com. mysql. jdbc. driver (); conn = DriverManager. getConnection ("jdbc: mysql: // localhost: 3306/Persons", "root", "root");} catch (SQLException e) {e. printStackTrace () ;}return conn ;}}

UPDATE statement:

Package com. jdbc; import java. SQL. connection; import java. SQL. driverManager; import java. SQL. SQLException; import java. SQL. statement; public class Test5 {/*** change the value of a column */public static void main (String [] args) throws SQLException {// 1, register the driver DriverManager. registerDriver (new com. mysql. jdbc. driver (); // 2, obtain the Connection conn = DriverManager. getConnection ("jdbc: mysql: // localhost: 3306/Persons", "root", "root"); // obtain the Statement stmt = conn. createStatement (); // String SQL = "update Person set FirstName = 'David' where FirstName = 'john'"; // run the int n1_stmt.exe cuteUpdate (SQL) statement ); if (n> 0) {System. out. println ("modified successfully");} else {System. out. println ("failed to modify");} conn. close (); stmt. close ();}}

DELETE statement:

Package com. jdbc; import java. SQL. connection; import java. SQL. driverManager; import java. SQL. SQLException; import java. SQL. statement; public class Test4 {/*** Delete the records of all cities in Shanghai */public static void main (String [] args) throws SQLException {Connection conn = getConn (); statement stmt = conn. createStatement (); int n1_stmt.exe cuteUpdate ("delete from Person where City = 'London"); if (n> 0) {System. out. println ("Total" + n + "Deleted Data");} conn. close (); stmt. close ();} public static Connection getConn () {Connection conn = null; try {DriverManager. registerDriver (new com. mysql. jdbc. driver (); conn = DriverManager. getConnection ("jdbc: mysql: // localhost: 3306/Persons", "root", "root");} catch (SQLException e) {e. printStackTrace () ;}return conn ;}}