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';