JDBC entry (1), jdbc entry

JDBC entry (1), jdbc entry

JDBC (Java DataBase Connectivity, java DataBase connection) is a Java API used to execute SQL statements. It can provide unified access to multiple relational databases, it consists of a group of classes and interfaces written in Java. JDBC provides a benchmark to build more advanced tools and interfaces so that database developers can write database applications. JDBC is also a trademark name.

I. Java database connection example:

1. steps:

• Export jar package: Driver.
• Load Driver Class: Class. forName ("Class Name ");
• The url, username, and password are provided, and the url is memorized.
• Use the DriverManager class to obtain the Connection object.

1 public class Demo1 {2/** 3 * ClassNotFoundException: 4 * No driver package imported 5*6 * SQLException: 7 * check three parameters: url, username, and password are correct 8 * check whether the mysql server is enabled. 9*10 */11 @ Test12 public void fun1 () throws ClassNotFoundException, SQLException {13/** 14 * Four jdbc configuration parameters 15 * driverClassName: com. mysql. jdbc. driver16 * url: jdbc: mysql: // localhost: 3306/Database Name 17 * username: root18 * password: 19 */20 Class. forName ("com. mysql. jdbc. driver "); // load the Driver Class (register the Driver) 21 22 // use url, username, and password to obtain the Connection object 23 Connection con = DriverManager. getConnection ("jdbc: mysql: // localhost: 3306/mydb_1", "root", ""); 24 System. out. println (con); 25} 26}

2. Basic exceptions

Driver package not imported: java. lang. ClassNotFoundException: com. mysql. jdbc. Driver;

The database does not exist: com. mysql. jdbc. exceptions. jdbc4.MySQLSyntaxErrorException: Unknown database 'mydb ';

Port error: com. mysql. jdbc. exceptions. jdbc4.CommunicationsException: Communications link failure;

Incorrect password: java. SQL. SQLException: Access denied for user 'root' @ 'localhost' (using password: YES );

User does not exist: java. SQL. SQLException: Access denied for user 'root' @ 'localhost' (using password: YES );

Ii. JDBC principles

Class. forName ("com. mysql. jdbc. driver "); // The data is equivalent to the following two sentences, which have a logical relationship with the last sentence com. mysql. jdbc. driver driver = new com. mysql. jdbc. driver; DriverManager. registerDriver (driver); Connection con = DriverManager. getConnection ("jdbc: mysql: // localhost: 3306/mydb_1", "root ","");

All java. SQL. Driver implementation classes provide static blocks. The code in the block is to register itself into DriverManage, such as some source code in com. mysql. jdbc. Driver:

public class Driver extends NonRegisteringDriver implements java.sql.Driver {    //    // Register ourselves with the DriverManager    //    static {        try {            java.sql.DriverManager.registerDriver(new Driver());        } catch (SQLException E) {            throw new RuntimeException("Can't register driver!");        }    }    /**     * Construct a new driver and register it with DriverManager     *      * @throws SQLException     *             if a database error occurs.     */    public Driver() throws SQLException {        // Required for Class.forName().newInstance()    }}

After JDBC 4.0, in each Driver jar package, a file named java. SQL. Driver is provided under the META-INF/services Directory, the content of the file is the implementation Class Name of the interface. In the database connection example, the 20th line "Class. forName (" com. mysql. jdbc. Driver ");" can be omitted without writing.

Iii. Simple addition, deletion, modification, and query of databases using JDBC

1 package demo2; 2 3 import org. junit. test; 4 import java. SQL. *; 5 6 public class Demo2 {7/* 8 * database addition, deletion, and modification operations 9 **/10 @ Test 11 public void fun1 () throws ClassNotFoundException, SQLException {12/* 13*1. Obtain Connection 14*1. Prepare four parameters 15*2. Load the Driver Class 16*3. Obtain Connection 17 */18 19 String driverClassName = "com. mysql. jdbc. driver "; 20 // jdbc protocol format, jdbc: name of the Industry and Commerce: Sub-Protocol (defined by the industry and commerce) 21 // For mysql, its sub-protocol structure: // host: Terminal Slogans/database name 22 String url = "jdbc: mysql: // localhost: 3306/mydb1"; 23 String username = "root"; 24 String password = ""; 25 // load the Driver Class 26 Class. forName (driverClassName); 27 // use DriverManager and the remaining three parameters to obtain Connection 28 Connection con = DriverManager. getConnection (url, username, password); 29/* 30*2. add, delete, and modify a database 31*1. Create a sender of the Statement 32 * Statement through the Connection object, its function is to send SQL statements to the database, 33*2. Call its int executeUpda Te (String SQL), which can send DML, DDL 34 **/35 Statement stmt = con. createStatement (); 36 // String SQL = "INSERT INTO stu VALUES ('000000', 'hangw', 88, 'male ')"; 37 // String SQL = "UPDATE stu SET name = 'zhaoliu', age = 22, gender = 'female 'where" + 38 // "number = '000000 '"; 39 String SQL = "delete from stu"; 40 int r = stmt.exe cuteUpdate (SQL); 41 System. out. println (r); 42} 43/* 44 * query operation 45 **/46 @ Test 47 pub Lic void fun2 () throws ClassNotFoundException, SQLException {48 49/* 50*1. Get Connection 51*2. Get Statement, send the select statement 52*3. parse the "table" returned by the query 53 */54/* 55*1. Obtain the connection 56 */57 String driverClassName = "com. mysql. jdbc. driver "; 58 String url =" jdbc: mysql: // localhost: 3306/mydb1 "; 59 String username =" root "; 60 String password =" "; 61 62 Class. forName (driverClassName); 63 Connection con = Driver Manager. getConnection (url, username, password); 64/* 65*2. Run the select Statement 66 **/67 Statement stmt = con. createStatement (); 68/* 69 * Call Statement's ResultSet rs = executeQuery (String querySql); 70 **/71 ResultSet rs = stmt.exe cuteQuery ("SELECT * FROM emp "); 72/* 73*3. parse ResultSet 74*1. Move the row cursor to the first line and call the next () method. 75 **/76 while (rs. next () {// move the cursor down a row and determine whether the next row contains 77 int empno = rs. getInt (1); // obtain the value of this column by column number 78 String ename = rs. getString ("ename"); // obtain the value of this column using the column name 79 double sal = rs. getDouble ("sal"); 80 81 System. out. println (empno + "," + ename + "," + sal); 82} 83/* 84*4. Disable resource 85 * inverted 86 **/87 rs. close (); 88 stmt. close (); 89 con. close (); // It must be closed. 90} 91 // normalize 92 @ Test 93 public void fun3 () throws Exception {94 Connection con = null; // Define reference 95 Statement stmt = null; 96 ResultSet rs = null; 97 try {98 // 1. Obtain Connection 99 String driverClassName = "com. mysql. jdbc. driver "; 100 String url =" jdbc: mysql: // localhost: 3306/mydb1 "; 101 String username =" root "; 102 String password =" "; 103 Class. forName (driverClassName); 104 con = DriverManager. getConn Ection (url, username, password); // instantiate 105 // 2. Create Statement106 stmt = con. createStatement (); 107 String SQL = "SELECT * FROM emp"; 108 rs = stmt.exe cuteQuery (SQL); // instantiate 109 // 3. Traverse rs cyclically, print the data 110 // getString () and getObject () are the common 111 int count = rs. getMetaData (). getColumnCount (); 112 while (rs. next () {113 for (int I = 1; I <= count; I ++) {114 System. out. print (rs. getString (I); 115 if (I <count) {116 System. out. pri Nt (","); 117} 118} 119 System. out. println (); 120} 121 122} catch (Exception e) {123 throw new RuntimeException (e); 124} finally {125 // to prevent null pointer exceptions, use the judgment statement 126 if (rs! = Null) rs. close (); 127 if (stmt! = Null) stmt. close (); 128 if (con! = Null) con. close (); 129} 130} 131}