03. JDBC database programming-processing DML statements

03. JDBC database programming-processing DML statements

Void	AddBatch(String SQL) Adds the given SQL command to the current list of commands for this Statement object.
Void	ClearBatch() Empties this Statement object's current list of SQL commands.
Void	Close() Releases this Statement object's database and JDBC resources immediately instead of waiting for this to happen when it is automatically closed.
Boolean	Execute(String SQL) Executes the given SQL statement, which may return multiple results.
Int []	ExecuteBatch() Submits a batch of commands to the database for execution and if all commands execute successfully, returns an array of update counts.
ResultSet	ExecuteQuery(String SQL) Executes the given SQL statement, which returns a single ResultSet object.
Int	ExecuteUpdate(String SQL) Executes the given SQL statement, which may be an INSERT, UPDATE, or DELETE statement or an SQL statement that returns nothing, such as an SQL DDL statement.
Int	ExecuteUpdate(String SQL, String [] columnNames) Executes the given SQL statement and signals the driver that the auto-generated keys indicated in the given array shocould be made available for retrieval.
Boolean	IsClosed() Retrieves whether this Statement object has been closed.
Boolean	IsCloseOnCompletion() Returns a value indicating whether this Statement will be closed when all its dependent result sets are closed.
Void	SetMaxRows(Int max) Sets the limit for the maximum number of rows that any ResultSet object generated by this Statementobject can contain to the given number.
Void	SetQueryTimeout(Int seconds) Sets the number of seconds the driver will wait for a Statement object to execute to the given number of seconds.

I. DML and Statement Interfaces
1. DML (Data manipulation language)
Database Operation statements are used to add, delete, update, and query database records, and check database integrity. Common statement keywords include insert, delete, and update.
(1) Add/modify/delete table data
Add a row of data: (thought: To which table to add? Which Rows are added? What are their respective values ?)
Insert into Table Name (column 1, column 2,..., column n) values (value 1, value 2..., value n );
Modify the column data of a row: (thought: Which table to change? How many columns of Values do you need to change? Why are values changed separately? In which lines does it take effect ?)
Update table name set column 1 = new value 1, column 2 = new value 2, where expr;
Delete row: (thought: Which table do you want to delete? Which Rows do you want to delete ?)
Delete from table name where expr;
View table data:
Select * from [Table name];


(2) Modifying fields and attributes
Modify a table (field type, field name, add field, delete field, modify table name)
① Modify Field attributes in the table (field names cannot be modified)
Alter table [table name] modify [field name] [field type] [constraints] [fisrt | after column name];
② Modify the field names and attributes in the table
Alter table [table name] change [Source Field name] [modified field name] [field type] [constraints] [fisrt | after column name];
③ Add Table Fields
Alter table [table name] add [field name] [field type] [constraints] [first | after column name];
④ Deleting table fields
Alter table [table name] drop [field name];
Note: [first | after column name] is used to modify the sorting of fields. In this case, after adds the new fields to a field; first indicates placing the new field in the first column of the table.
Modify Table Name
Command: alter table [table name] rename to [new table name];


2. Statement Interface
Interface used to execute static SQL statements and obtain returned results, obtained through the createStatement method in Connection
That is, Statement stmt = conn. createStatement ())
Reference: http://docs.oracle.com/javase/8/docs/api/index.html
Ii. JDBC database application source code practice 1. Source Code ImplementationTask: Insert a piece of data ('dongguo',) to the connected database ).
ImportJava. SQL .*;

/* MySQL database programming * instance (2): JDBC processes DML statements */public class JDBC_DML {public static void main (String [] args) {// 0. database URL, database account name, and password String url = "jdbc: mysql: // localhost/jdbc_test_db"; String DBusername = "root"; String DBpassword = "896013 "; // 1. load the database driver to the Java Virtual Machine try {Class. forName ("com. mysql. jdbc. driver "); // The Driver is a MySQL Driver Class} catch (ClassNotFoundException e) {System. out. println ("unable to find the database Driver Class, failed to load the driver! "); E. printStackTrace (); // Save the exception to log} // 2. create a Connection object conn, indicating to connect to the MySQL database Connection conn = null; Statement stmt = null; int I; try {conn = DriverManager. getConnection (url, DBusername, DBpassword); // 3. obtain the Statement object stmt = conn that can execute SQL statements. createStatement (); // 4.execute the SQL statement to insert the returned int data into i1_stmt.exe cuteUpdate ("insert into test (name, age, score) values ('dongguo',)"); if (I! = 1) {System. out. println ("An error occurred while updating the database! ") ;}} Catch (SQLException se) {System. out. println ("failed to connect to the Database"); se. printStackTrace ();} // 6. close all used JDBC objects and release JDBC resources if (stmt! = Null) // close the Declaration {try {stmt. close () ;}catch (SQLException e) {e. printStackTrace () ;}} if (conn! = Null) // close the database connection {try {conn. close () ;}catch (SQLException e) {e. printStackTrace ();}}}}

  Running result(1) insert a record stmt.exe cuteUpdate ("insert into test (name, age, score) values ('dongguo', 18, 97 )");
(2) delete A record stmt.exe cuteUpdate ("delete from test where name = 'JJ '");

2. The JDBC application dynamically inserts data into the databaseSource code: Uses command line parameters as the inserted data source to dynamically update database data.
ImportJava. SQL .*;
/

* MySQL database programming * instance (3): JDBC processes DML statements. enter the data to be inserted to the database through the command line */public class JDBC_DML2 {public static void main (String [] args) {if (args. length! = 3) // The Input is incorrect. The {System. out. println ("Parament Error, Please Input Again! "); System. exit (-1);} String nameParam = args [0]; // obtain the first parameter int ageParam = Integer of the command line. parseInt (args [1]); // obtain the second parameter of the command line and convert it to an Integer int scoreParam = Integer. parseInt (args [2]); // obtain the third parameter of the command line and convert it to an integer. // 0. string url = "jdbc: mysql: // localhost: 3306/jdbc_test_db"; // Database URL (unique resource identification identifier) String DBusername = "root "; // database username String DBpasswd = "111111"; // Database Password // 1. load the database Driver and register the Driver to DriverManager. try {Class. forName ("com. mysql. jdbc. Driver ");} catch (ClassNotFoundException e) {e. printStackTrace ();} // 2. connect to the database through the database URL Connection conn = null; Statement stmt = null; try {conn = DriverManager. getConnection (url, DBusername, DBpasswd); // 3. get the Statement object stmt = conn. createStatement (); // 4. obtain command parameters and call executeUpdate of the Statement object to update the database String SQL = "insert into test (name, age, score) values ('" + nameParam + "', "+ ageParam +", "+ scoreParam +") "; System. out. println (SQL); // call Try stmt.exe cuteUpdate (SQL);} catch (SQLException e) {e. printStackTrace ();} // 5. Release JDBC resources if (stmt! = Null) // close the Declaration {try {stmt. close () ;}catch (SQLException e) {e. printStackTrace () ;}} if (conn! = Null) // close the connection {try {conn. close () ;}catch (SQLException e) {e. printStackTrace ();}}}}

(1) set command line parametersRight-click the project and choose Run as> Open Run Dialog> Main Class and select "JDBC_DML2"
(2) running result
(3) errors and debuggingThe SQLException exception occurs when you enter a parameter to run the Java application. We can print the entered insert SQL statement and find that the program does not get the parameters passed in by the command line. In addition, if vaules is misspelled, find the cause. Modify: SQL = "insert into test (name, age, score) values ('" + nameParam + "'," + ageParam + "," + scoreParam + ")";