JDBC concepts and quick start cases

1. Concept of JDBC:

Operate database by Java code (add, delete, modify, check)

2. Quick start procedure:

A.Import jar package

B.Registration driven  
Class.forName("com.mysql.jdbc.Driver");

C.Acquisition drive
String url = "jdbc:mysql://127.0.0.1:3306 / EE "; / / EE is the name of the database
Connection conn = DriverManager.getConnection(url, "root", "123");

D.Get operation object  
Statement stat = conn.createStatement();

E.operation SQL     
int num = stat.executeUpdate(sql);

F.Release resources
stat.close();
conn.close();

Detailed introduction of JDBC classes

1. DriverManager driver manager

Registration driven
 Register the driver's code Class.forName("com.mysql.jdbc.Driver");

Connection to database

A. Get execution database pairs      
    Statement stat = conn.createStatement();
Get preset statement execution database object 
    PrepareStatement ps = conn.prepareStatement(sql statement);
B. Management affairs
 a. Open transaction 
    conn.setAutoCommit(false); //false set to manually commit transactions. true set to auto commit
 b. Commit transaction 
    conn.commit();
c. Rollback transaction 
    conn.rollback();

Statement operation database

A.update operation(DML"Addition, deletion and modification of table records"and DDL"Addition, deletion and modification of tables and databases") 
int num = stat.executeUpdate("SQL Sentence");  //The SQL statement here is to add, delete, modify, and return several lines

B.Query operation(DQL"Querying table records")
ResultSet resu = stat.executeQuery("SQL Query statement"); //The SQL statement here is the query statement DQL 

4. ResultSet query table result set

A.Determine whether the next data still exists
boolean b = resu.next();  //If the next data exists, the return value is true. If not, the return value is false

B.Get the data in the result set
a.Result set found by query,Index value acquisition(Index value starts at 1)
    int data01 = resu.getInt(1);        //Get int type data with query result set index 1
    String data02 = resu.getString(2);  //The data of type String with query result set index 2 is obtained
b.Result set found by query,Column name get
    int data01 = resu.getInt("id");         //Get the int type data with the query result set column name of id
    String data02 = resu.getString("name"); //The data obtained is the String type data with the query result set column name of name

5. PrepareStatement operation database

A.Get preset operation
 String sql = "select * from user where username = ? and password = ?";
PreparedStatement ps = Connection.prepareStatement(sql); //Get the operation of connection through preset object

B.Setting parameters
ps.setString(1,"root"); //Parameter 1: question mark, parameter 2: corresponding preset value
ps.setString(2,"123");//Parameter 1: question mark, parameter 2: corresponding preset value

C.implement SQL Sentence
ps.executeQuery();

The writing method of JDBC tool class

//[1] Static code block for loading static member variables (data in 4 files)
//[2] Static tool method Connection getConnection()
//[3] The resource releasing method [overloaded method] passes in 2 parameters and 3 parameters
public class JDBCUtils {

private static String url;
private static String user;
private static String password;
private static String driver;
/**
 * These values can be obtained by reading the file only once. Using static code blocks
 */
static{
    //Read the resource file to get the value.

    try {
        //1. Create the Properties collection class.
        Properties pro = new Properties();
        //How to get files under src path -- > classloader classloader
        ClassLoader classLoader = JDBCUtils.class.getClassLoader();
        URL res  = classLoader.getResource("jdbc.properties");
        String path = res.getPath();
       //2. Load file
       pro.load(new FileReader(path));
        //3. Get data, assign value
        url = pro.getProperty("url");
        user = pro.getProperty("user");
        password = pro.getProperty("password");
        driver = pro.getProperty("driver");
        //4. Registration driver
        Class.forName(driver);
    } catch (IOException e) {
        e.printStackTrace();
    } catch (ClassNotFoundException e) {
        e.printStackTrace();
    }
}


/**
 * Get connection
 * @return Connection object
 */
public static Connection getConnection() throws SQLException {
    Connection conn = DriverManager.getConnection(url, user, password);
    return conn;
}

/**
 * Release resources
 * @param stmt
 * @param conn
 */
public static void close(Statement stmt, Connection conn){
    if( stmt != null){
        try {
            stmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    if( conn != null){
        try {
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}


/**
 * Release resources
 * @param stmt
 * @param conn
 */
public static void close(ResultSet rs, Statement stmt, Connection conn){
    if( rs != null){
        try {
            rs.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    if( stmt != null){
        try {
            stmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    if( conn != null){
        try {
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

}