Detailed JDBC implementation process (add, delete, modify and check)

Keywords: SQL Database MySQL JDBC

I. create a table in the database

In this example, MySQL database management system is used. In order to facilitate the operation, Navicat Premium, a graphical interface, is used to operate mysql. The following is the table creation statement:

use test;//Using the test database
create table user_info(//Create a table in the test database with the name of user? Info. Set the id, user? Name and password fields in the table
	id char(36),
	user_name varchar(12) unique,//unique constraint guarantees the uniqueness of user name field
	password varchar(15)
)
//Note the difference between char and varchar here:
char: 
 1. Writing: char(M)
 2. M The maximum number of characters, which can be omitted, defaults to 1
 3. Features: fixed length characters
 4. Space consumption: more consumption
 5. Efficiency: high
varchar: 
 1. Writing: varchar(M)
 2. M The maximum number of characters, cannot be omitted
 3. Features: variable length characters
 4. Space consumption: saving
 5. Efficiency: low

II. Using JDBC to realize query

Here is the sample code

public class Test {
	public static void main(String[] args) {
		//Because resources need to be closed in the finally code block, variables are defined here instead of in the try code block (the purpose is to improve the scope of variables and code reusability)
		Connection con = null;
		Statement sta = null;
		ResultSet res = null;

		try {
			Class.forName("com.mysql.jdbc.Driver");//1. Loading drive
		    con = DriverManager.getConnection("jdbc:mysql://Localhost: 3306 / test "," root "," root "); / / 2. Establish a connection with the database
		    sta = con.createStatement();//3. Create SQL statement object
		    String sql = "select * from user_info";//4. Write SQL statement
		    res = sta.executeQuery(sql);//5. Execute SQL statement
		    while (res.next()) {
				String id = res.getString("id");//Returns the value of the id field
				String name = res.getString("user_name");//Returns the value of the user name field
				String password = res.getString("password");//Returns the value of the password field
				System.out.println(id+","+name+","+password);//6. Use while loop to traverse result set
			}//Here, the execution principle of the while loop is: after executing the SQL statement, the ResultSet object stores the data table returned from accessing the database, that is, the result set. The ResultSet object has a pointer to its current data row, initially before the first row, the first time you use next() to point to the first row of the returned result set. Every time next() is used, the pointer points to the next row. At this time, the res.getString() method is used to return the value of a field in the row. The while loop traverses a piece of data every time it is executed. When the pointer points to the back of the last row, the Boolean value of false is returned to end the loop.
		} catch (Exception e) {
			e.printStackTrace();
		}finally {//7. Release resources
			//Note: the order of releasing resources here must be ResultSet, Statement and Connection, because if Connection is released first, the resources occupied by ResultSet and Statement will not be released.
			try {
				if (res!=null) {//It is necessary to judge whether the object is empty or not, because if the object is empty before the assignment without if judgment, an error of null pointer exception will be reported.
					res.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				if (sta!=null) {
					sta.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				if (con!=null) {
					con.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}

III. add, delete and modify using JDBC

Here is the sample code: (because adding, deleting, and modifying only SQL statements are different, only add is taken as an example here.)

public class Test {
	public static void main(String[] args) {
		//Because resources need to be closed in the finally code block, define variables here instead of in the try code block (in order to improve the scope of variables)
		Connection con = null;
		Statement sta = null;
		ResultSet res = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");//1. Loading drive
			con = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306 / test "," root "," 0314 "); / / 2. Establish connection
			sta = con.createStatement();//3. Create SQL statement object
			for (int i = 0; i < 10; i++) {
				String id = UUID.randomUUID().toString();//Use UUID here to randomly generate id
				String sql = "insert into user_info(id,user_name,password) values('"+id+"','"+i+"','"+i+"')";//4. Write SQL statement
				int affect = sta.executeUpdate(sql);//5. Execution statement
				System.out.println(affect);//6. Handling the implementation results
			}
		} catch (Exception e) {
		e.printStackTrace();
		}finally {//7. Release resources
			//Note: the order of releasing resources here must be Statement and Connection, because if you release Connection first, the resources occupied by Statement will not be released.
			try {
				if (sta!=null) {//It is necessary to judge whether the object is empty or not, because if the object is empty before the assignment without if judgment, an error of null pointer exception will be reported.
					sta.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}try {
				if (con!=null) {//
					con.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}	
//Supplement:
//The SQL statements to be deleted are:
	delete * from user_info;//Delete all data in the table
	delete * from user_info where name='admin';//Delete all data in the table whose name is admin
//The SQL statements to be modified are:
	update user_info set id='d4d0654e-f15f-4940-a768-8ffed8cf56ef',user_name='Tom',password='123456';

Posted by ntg on Wed, 20 Nov 2019 09:26:43 -0800