java learning notes

Posted by nmreddy on Fri, 01 May 2020 07:44:47 +0200

Getting to know JDBC

1, JDBC concept

jdbc English full name: java database connectivity is a set of specifications for Java to access database, which can be understood as a set of classes and interfaces provided by java to connect database

The relationship between jdbc and database driver:

  

2, jdbc easy to use

Usage process: (the driver jar needs to be imported before use)

①: Registration driver

②: connect to database

③: obtain the execution platform of sql statements

④: execute sql statement

⑤: treatment results

⑥: release resources

Execution flow chart:

 

Code implementation (data addition, deletion and modification):

 

 1 public class Demo13 {
 2     public static void main(String[] args)throws Exception {
 3         //1.Registration driver
 4         /*
 5          * Because looking at the Driver source code, we found that the Driver has been registered in the static code block of this class, so we don't need to register again
 6          * Note: after jdk1.6, you can omit the step of registration driver and do not write the system to automatically register
 7          * Paste the source code:
 8          *   static {
 9          *       try {
10          *           java.sql.DriverManager.registerDriver(new Driver());
11          *       } catch (SQLException E) {
12          *           throw new RuntimeException("Can't register driver!");
13          *       }
14          *   }
15          *   
16          *   
17          */
18         //DriverManager.registerDriver(new Driver());
19         Class.forName("com.mysql.jdbc.Driver");
20         
21         
22         //2.Connect to database
23         /*
24          * Parameter 1: fixed format of connecting ip port database name
25          * Parameter 2: database user name
26          * Parameter 3: database password
27          */
28         Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb","root","root");
29         //3.Get sql Statement execution platform
30         Statement stat= con.createStatement();
31         //4.implement sql Sentence
32         /*
33          * executeUpdate()Method is used to add, delete and modify statements
34          * Parameters are sql statements
35          * The return value is of type int. several statements were executed successfully
36          */
37         int result= stat.executeUpdate("insert into car values(null,'BMW')");
38         //5.Processing results
39         /*
40          * The processing of the result here is actually to see the business logic. Generally, it is to judge whether the statement is executed successfully or not
41          */
42         if(result>0){
43             System.out.println("Execution successful");
44         }else{
45             System.out.println("Execution failed");
46         }
47         //6.Release resources
48         stat.close();
49         con.close();
50     }
51 
52 }

 

Here is the code for the query operation:

 1 public class Demo03JDBC {
 2     public static void main(String[] args) throws ClassNotFoundException, SQLException {
 3         // 1:Registration driver
 4         Class.forName("com.mysql.jdbc.Driver");
 5         // 2:Get connection to server
 6         Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/day04db", "root", "123");
 7         // 3:Send to server sql instructions
 8         // 3.1 Obtain Statement Implementation class object of interface
 9         Statement stat = conn.createStatement();
10         // 3.2 implement sql (Query data)
11         ResultSet rs = stat.executeQuery("select * from product");
12 
13         // 4:Process the returned result set(Know column name and type)
14       /*
15         while (rs.next()) {// Determine whether there is next line of data
16             int cid = rs.getInt("cid"); // Take out the cid column
17             String cname = rs.getString("cname"); // Take out the came column
18             System.out.println(cid + "\t" + cname);
19         }
20       */
21         //Process the returned result set(Don't know column name,Know the type of column):Get by column index
22     /*
23         while (rs.next()) {// Determine whether there is next line of data
24             int cid = rs.getInt(1); // Take out the first column
25             String cname = rs.getString(2); // Take out the second column
26             System.out.println(cid + "\t" + cname);
27         }
28     */
29     //Process the returned result set(Don't know column name,Don't know the type of column):Get by column index
30         /*
31         while (rs.next()) {// Determine whether there is next line of data
32             Object obj1 = rs.getObject(1);
33             Object obj2 = rs.getObject(2);
34             System.out.println(obj1 +"\t" + obj2);
35         }
36         */
37    //Process the returned result set(Don't know column name,Don't know the type of column,Don't know how many columns):Get by column index    
38         ResultSetMetaData metaData = rs.getMetaData(); //Get the raw data of the table
39         //Get number of columns
40         int count = metaData.getColumnCount();
41         while(rs.next()){ //13 second
42             for (int i = 1; i <=count; i++) { //4 second
43                 Object obj = rs.getObject(i);
44                 System.out.print(obj+"\t");
45             }
46             System.out.println();
47         }
48         
49         // 5:Release resources
50         rs.close();
51         stat.close();
52         conn.close();
53     }
54 }

 

 

Simple package of jdbc tool class: (mainly to realize the idea)

 1 /*
 2  * This file is used to encapsulate the common functions of JDBC:
 3  * 
 4  * 1:Package driver registration 
 5  * 2:Encapsulate the connection to the database
 6  * 3:Release of encapsulated resources
 7  * 
 8  * JDK1.6 After that, Java automatically registers the driver
 9  */
10 public class MyJDBCUtils {
11     private static String classDriverName = null;
12     private static String url = null;
13     private static String username = null;
14     private static String password = null;
15     // 1:Package driver registration
16     /*
17      * Since the driver's registration only needs to be executed once, the code can be encapsulated in a static code block
18      * Since Properties only need to be parsed once, the parsed code is still placed in a static code block
19      */
20     static {
21         try {
22             //1:Establish Properties object
23             Properties prop = new Properties();
24             //2:Read data from file to collection
25             prop.load(new FileInputStream("jdbc.properties"));
26             //3:Get data for collection
27             classDriverName = prop.getProperty("driverClassName");
28             url = prop.getProperty("url");
29             username = prop.getProperty("username");
30             password = prop.getProperty("password");
31             
32             
33             Class.forName(classDriverName);
34         } catch (Exception e) {
35             e.printStackTrace();
36         }
37     }
38 
39     // 2:Encapsulate the connection to the database
40     public static Connection getConnection() throws SQLException {
41         Connection conn = DriverManager.getConnection(url, username, password);
42         return conn;
43     }
44     
45     //Encapsulation method: processing result set
46     public static void printResultSet(ResultSet rs) throws SQLException{
47         ResultSetMetaData metaData = rs.getMetaData();
48         int count = metaData.getColumnCount();
49         
50         while(rs.next()){
51             for (int i = 1; i <= count; i++) {
52                 Object obj = rs.getObject(i);
53                 System.out.print(obj+"\t");
54             }
55             System.out.println();
56         }
57         
58     }
59     
60 
61     // 3:Release of encapsulated resources null stat conn
62     public static void closeAll(ResultSet rs, Statement stat, Connection conn) {
63         if (rs != null) {
64             try {
65                 rs.close();
66             } catch (SQLException e) {
67                 e.printStackTrace();
68             }
69         }
70         if (stat != null) {
71             try {
72                 stat.close();
73             } catch (SQLException e) {
74                 e.printStackTrace();
75             }
76         }
77         if (conn != null) {
78             try {
79                 conn.close();
80             } catch (SQLException e) {
81                 e.printStackTrace();
82             }
83         }
84     }
85 }

Topics: Java JDBC Database SQL