Simple CRUD (1), simple CRUD (

Simple CRUD (1), simple CRUD (
I. JDBC overview-(from Baidu)

JDBC (Java DataBase Connectivity, java DataBase connection) is a Java API used to execute SQL statements. It can provide unified access to multiple relational databases, it consists of a group of classes and interfaces written in Java. JDBC provides a benchmark to build more advanced tools and interfaces so that database developers can write database applications. JDBC is an API for Java programmers and an interface model for service providers that connect to databases.

JDBC can be used to establish a connection with the database, send statements for database operations, and process the results.

JDBC APIs provide the following interfaces and classes:

• DriverManager: This class manages the list of database drivers. Determine whether the content meets the connection request of the database driver that is correct from the communication sub-protocol used by the Java application. Identify That JDBC will be used to establish a database connection in the first drive of a certain sub-protocol.

• Driver: This interface processes communication with the database server. The Driver object is rarely directly used.DriverManagerIs used to manage objects of this type. It also abstracts detailed information related to driver object work.

• Connection: This interface is used to access all methods of the database. The connection object indicates the communication context, that is, all the communication with the database is through this unique connection object.

• Statement: You can submit the SQL statement of the object created using this interface to the database. Some derived interfaces accept parameters in addition to the stored procedure.

• ResultSet: After these objects are saved from the database, runStatementThe SQL query of the object to retrieve data. As an iterator, it can be moved to retrieve the next data.

• SQLException: This class is used to handle any errors that occur in database applications.

Ii. CRUD

Crud refers to the abbreviation of the first letter of the words "Create", "Retrieve", "Update", and "Delete" during computing. Crud is mainly used to describe the basic operation functions of the database or persistent layer (dao layer) in the software system.

Iii. Implementation of simple CRUD1. implementation conditions

1. jar package, mysql-connector-java-5.1.26-bin.jar

2. Database MySQL

2. Specific Code

1. dao layer interface

1 public interface ILoginUserDao {2/** 3 * added 4 * @ param loginUser 5 */6 void save (LoginUser loginUser ); 7 8/** 9 * delete 10 * @ param id11 */12 void delete (Integer id ); 13 14/** 15 * update 16 * @ param loginUser17 */18 void update (LoginUser loginUser ); 19 20/** 21 * query 22 * @ return23 */24 List query (); 25}

2. dao layer implementation

1 public class LoginUserDao implements ILoginUserDao {2 @ Override 3 public void save (LoginUser loginUser) {4 System. out. println ("---- save ----"); 5 // first load the jdbc implementation Class 6 try {7 // 1. load the driver 8 Class. forName ("com. mysql. jdbc. driver "); 9 // 2. create Connection 10 connection Connection = DriverManager. getConnection ("jdbc: mysql: // localhost: 3306/user", "root", "admin"); 11 // 3. create a compilation Statement 12 statement Statement = connection. createStatement (); 13 // 4. run the SQL statement 14 String SQL = "insert into loginuser (username, password) VALUES ('wang wu', '123456')"; 15 int I = statement.exe cuteUpdate (SQL ); 16 // 5. release resource 17 statement. close (); 18 connection. close (); 19 20} catch (Exception e) {21 e. printStackTrace (); 22} 23} 24 25 @ Override 26 public void delete (Integer id) {27 System. out. println ("---- delete ----"); 28 try {29 // 1. load driver 30 Class. forName ("com. mysql. jdbc. driver "); 31 // 2. create Connection 32 connection Connection = DriverManager. getConnection ("jdbc: mysql: // localhost: 3306/user", "root", "admin"); 33 // 3. create a compilation Statement 34 statement Statement = connection. createStatement (); 35 // 4. execute Statement 36 String SQL = "delete from loginuser WHERE id = 2"; 37 int I = statement.exe cuteUpdate (SQL); 38 // 5. release resource 39 statement. close (); 40 connection. close (); 41} catch (Exception e) {42 e. printStackTrace (); 43} 44} 45 46 @ Override 47 public void update (LoginUser loginUser) {48 System. out. println ("---- update ----"); 49 try {50 // 1. load driver 51 Class. forName ("com. mysql. jdbc. driver "); 52 // 2. create Connection 53 connection Connection = DriverManager. getConnection ("jdbc: mysql: // localhost: 3306/user", "root", "admin"); 54 // 3. create a compilation Statement 55 statement Statement = connection. createStatement (); 56 // 4. execute SQL statement 57 String SQL = "UPDATE loginuser SET username = '王', password = '000000' WHERE id = 1"; 58 int I = statement.exe cuteUpdate (SQL ); 59 // 5. release resource 60 statement. close (); 61 connection. close (); 62} catch (Exception e) {63 e. printStackTrace (); 64} 65} 66 67 @ Override 68 public List query () {69 System. out. println ("---- query ----"); 70 // a new list set object 71 List <LoginUser> list = new ArrayList <> (); 72 try {73 // 1. load drive 74 Class. forName ("com. mysql. jdbc. driver "); 75 // 2. create Connection 76 connection Connection = DriverManager. getConnection ("jdbc: mysql: // localhost: 3306/user", "root", "admin"); 77 // 3. create a compilation Statement 78 statement Statement = connection. createStatement (); 79 // 4. execute SQL statement 80 String SQL = "SELECT id, username, password FROM loginuser WHERE id> 4"; 81 ResultSet resultSet = statement.exe cuteQuery (SQL ); 82 // retrieve the query content 83 while (resultSet. next () {84 String username = resultSet. getString ("username"); 85 String password = resultSet. getString ("password"); 86 // create the LoginUser object 87 LoginUser loginUser = new LoginUser (); 88 loginUser. setUsername (username); 89 loginUser. setPassword (password); 90 // Add to set 91 list. add (loginUser); 92} 93 // 5. release resources 94 resultSet. close (); 95 statement. close (); 96 connection. close (); 97 98} catch (Exception e) {99 e. printStackTrace (); 100} 101 return list; 102} 103}

3. Notes

1. Use DriverManager to obtain the connection.

Connection conn = DriverManager. getConnection (String url, String user, String password)

2. add, delete, and modify the execution SQL statement statement.exe cuteUpdate (SQL) uses executeUpdate, while the query uses executeQuery = "ResultSet resultSet = statement.exe cuteQuery (SQL)