Access MySQL database through java _ MySQL

Java accessing MySQL database I. java connecting to MySQL database 1. install the mysql connector/j driverAdd jdbc driver: mysql-connection-java-5.1.10-bin.jar 2. connect to the MySQL databaseThe DriverManager class, Connection interface, Statement interface, and ResultSet interface exist in the java. SQL package. DriverManager class: manage drivers and database Connection interfaces: Manage established database Connection Statement interfaces: execute SQL Statement ResultSet interface: store records returned by the database
The forName () method specifies the MySQL driver syntax as follows: class. forname ("com. mysql. jdbc. driver "); class. forName ("org. git. mysql. driver "); the two drivers are actually the same, and the latter inherits the former. The getConnection () method has three parameters: url, user, and password. The url specifies the idbc data source. The data source must specify its IP address and port number. Connection connection = DriverManager. getConnection (url, user, password); // create a connection object example: Connect to the mysql database of the local computer. mysql uses the default port 3306, the connected database is syq, and the user root is used for connection. the root user password is 123456. connect to mysql The statement is as follows:Connectionconnection = DriverManager. getConnection ("jdbc: mysql: // localhost: 3306/syq", "root", "123456"); you can use this statement to connect to the MySQL syq database. This statement can also be written in the following format: String url = "jdbc: mysql: // localhost: 3306/syq "; // Obtain the protocol, IP address, port, and other information String user = "root"; // Obtain the database username String password = "123456"; // Obtain the database username Connection = DriverManager. getConnection (url, user, password); // create a Connection object II. using java to manipulate MySQL databasesAfter connecting to the MySQL database, you can query, insert, update, and delete data in the MySQL database. The Statement interface is mainly used to execute SQL statements. it defines some methods for executing SQL statements. After an SQL statement is executed, the returned results are managed by the ResultSet interface. Through these two interfaces, java can easily operate MySQL databases. 1. create a Statement objectThe Connection object calls the createStatement () method to create the Statement object. the code is as follows: Statement statement = connection. createStatement (); where statement is the Statement object; the createStatement () method returns the Statement object. You can use this java Statement to create a Statement object. After the Statement object is created, you can call the method to execute the SQL Statement. 2. use the select statement to query dataThe Statement object can call the excuteQuery () method to execute the select Statement. The select query result is returned to the ResultSet object. The code that calls the excuteQuery () method is: ResultSet result = statement. excuteQuery ("select statement"). The query result can be stored in the result using this statement. The query result may have multiple records, so you need to use a loop statement to read all records. the code is as follows: while (result. next () {String s = resule. getString ("field name"); System. out. print (s);} example: Query the student's student ID, test subject, and score from the score table. part of the code is as follows:Statement statement = connection. createStatement (); Result result = statement. excuteQuery ("select stu_id, c_name, grade from score"); while (result. next () {String id = result. getString ("stu_id"); String course = result. getString ("c_name"); String grade = result. getString ("grade"); System. out. println (id + "" + course + "" + grade );} 3. Insert update or delete dataThe excuteQuery () method can only execute select statements. To insert, update, or delete an object, the Statement object must call the excuteUpdate () method. After excuteUpdate is executed, the number of rows that affect the table is returned. The following code calls the excuteUpdate () method: int result = statement. excuteUpdate (SQL); where "SQL" must be an insert, update, or delete statement. The result returned by this method is a number. Example: Insert a new record for the score table in the following code:Statement statement = connection. createStatement (); String SQL = "insert into score values (21,902, 'English ', 85)"; int result = statement. excuteUpdate (SQL); System. out. println (result ); Update the record with id 16 in the score table and change the grade field value of the record to 100. part of the code is as follows:Statement statement = connection. createStatement (); String SQL = "UPDATE score set grade = 100 where id = 16"; int result = statement. excuteUpdate (SQL); System. out. println (result ); Delete the records with the id of 16 in the score table. part of the code is as follows:Statement statement = connection. createStatement (); String SQL = "delete from score where id = 16"; int result = statement. excuteUpdate (SQL); System. out. println (result ); 4. execute any SQL statementYou cannot determine whether to query or update the statement to be executed. you can use the excute () function. The return result of this function is a boolean value. if the return value is true, the query statement is executed. if the return value is false, the update statement is executed. The following code calls the excute () method: boolean result = statement. excute (SQL ); 5. close the created objectAfter all SQL statements are executed, you need to disable the created Connection object, Statement object, and Result object. The order of closing objects is opposite to that of creating objects. closing objects are ResultSet objects, Statement objects, and Connection objects. The object calls the close () method to close the object, and then sets the object value to null. Close some code of the object: if (result! = Null) {// judge whether the ResultSet object is null result. close (); // call the close () method to close the ResultSet object result = null;} if (statement! = Null) {statement. close (); statement = null;} if (connection! = Null) {connection. close (); connection = null ;} III. java backup and restoration of MySQL databasesIn java, you can run the mysqladmin command to back up the MySQL data volume or run the mysql command to restore the mysql database. 1. back up MySQL databases in javaThe mysqldump command is usually used to back up the MySQL database. The statement is as follows: mysqldump-u username-pPassword dbname table1 table2…> BackupName. SQL call the exec () method code: Runtime rt1_runtime.getruntime();rt.exe c ("command statement"); example: In Windows, back up a MySQL database using java:String str = "mysqldump-u root-p123456 -- opt test> C:/test. SQL "; // assign the statement of the mysqldump command to strRuntime rt = Runtime. getRuntime (); // create the runtimeobject rt.exe c ("cmd/c" + str); // call the exec () function
2. restore the MySQL database in javaThe mysql command is usually used to restore the MySQL database. The statement is as follows: mysql-u root-p [dbname]