Advanced JDBC-SQL statement execution (MySQL)

From the example in the previous article JDBC connection to MySQL database and example (go to this Article), we mentioned that using statement to execute SQL statements, such as connection; statement statement ;...... statement = connction. creatstatement (); string SQL = "insert into staff (name, age, sex, address, depart, worklen, wage)" + "values ('serin', 25, 'M', 'guangzhou ', 'Engine', '1', '000000') "cmdst.exe cuteupdate (SQL); in the above Code, it is very troublesome to construct SQL statements, in particular, parameters are prone to errors. Therefore, Sun provides a more advanced statement, namely, preparedstatement. This article describes preparedstatement. 1. What is preparedstatementFor more information, see the Java API documentation. preparedstatement is a sub-interface () of statement, which indicates the objects of pre-compiled SQL statements. SQL statements are pre-compiled and stored in PreparedStatementObject. This object can then be used to efficiently execute the statement multiple times. 2. Use preparedstatement to obtain the parameters executed in the command line and insert them into a data table.Related experiment processes, including creating databases required by the program in advance, creating required data tables, loading driver packages in the development environment, etc, refer to the previous article JDBC connection to MySQL database and example (go to this Article) for the specific Code as follows:

Package COM. serein. JDBC; import Java. SQL. *; public class preparedstatemettest {public static void main (string [] ARGs) {// check whether the command line contains 7 parameters, if (ARGs. length! = 7) {system. Out. println ("parameter error! Please input again! "); System. exit (-1);} // The program obtains the seven parameter values in the running Stack: string name = ARGs [0]; int age = 0; try {age = integer. parseint (ARGs [1]);} catch (numberformatexception e) {system. out. println ("parameter error! Age shocould be number format! "); System. exit (-1);} string sex = ARGs [2]; string address = ARGs [3]; string depart = ARGs [4]; int worklen = 0; try {worklen = integer. parseint (ARGs [5]);} catch (numberformatexception e) {system. out. println ("parameter error! Worklen shocould be number format! "); System. exit (-1);} int wage = 0; try {Wage = integer. parseint (ARGs [6]);} catch (numberformatexception e) {system. out. println ("parameter error! Wage shocould be number format! "); System. exit (-1);} // create the preparedstatement object preparedstatement pstmt = NULL; // create the connection object connection conn = NULL; // connect to the database, and insert the data try {// load the MySQL driver instance. Two methods are provided, which are equivalent class. forname ("com. mySQL. JDBC. driver "); // new Oracle. JDBC. driver. oracledriver (); // establish the connection conn = drivermanager. getconnection ("JDBC: mysql: // localhost: 3306/myuser", "root", "root"); // use the preparedstatement object to construct and execute SQL statements, the seven question marks indicate the value pstmt = conn to be retained for the seven fields. preparestat Ement ("insert into staff (name, age, sex, address, depart, worklen, wage) values (?, ?, ?, ?, ?, ?, ?) "); // Use the set method in the preparedstatement object to set the inserted value pstmt. setstring (1, name); pstmt. setint (2, age); pstmt. setstring (3, sex); pstmt. setstring (4, address); pstmt. setstring (5, depart); pstmt. setint (6, worklen); pstmt. setint (7, wage1_1_pstmt.exe cuteupdate (); // If the insertion is successful, the system is displayed. out. print ("A data record is inserted successfully! "); // Catch an exception occurred when loading the driver} catch (classnotfoundexception e) {e. printstacktrace (); // capture SQL statement execution failure exception} catch (sqlexception e) {e. printstacktrace (); // restore variable Initial Value} finally {try {If (pstmt! = NULL) {pstmt. Close (); pstmt = NULL;} If (Conn! = NULL) {conn. Close (); Conn = NULL;} // capture SQL exceptions} catch (sqlexception e) {e. printstacktrace ();}}}}

After writing the code, run the program with parameters as follows:


The input parameters are as follows ):Sereinchan
25
M
Guangzhou
Engine
3
5000

View the console. "A data record is inserted successfully! ":

Check the MySQL database for confirmation:

Completed successfully!