21. JDBC Primer 1__javaweb

Getting started with JDBC What is JDBC

The technique of sending SQL statements using Java Code (Programs) is the JDBC technology .... Connection Case

/** * JDBC Connection database * @author APPle * * */public class Demo1 {//Connection database URL private String url = "Jdbc:mysql://local
                        Host:3306/day17 ";

    JDBC Protocol: Database sub-protocol: Host: Port/Connected database/private String user = "root";//username private String password = "root";//Password /** * The first method * @throws Exception/@Test public void Test1 () throws exception{//1. Create driver classes Like Driver Driver = new Com.mysql.jdbc.Driver (); The new version of//driver Driver = Org.gjt.mm.mysql.Driver ();
        Old version//set user name and Password properties props = new properties ();
        Props.setproperty ("user", user);

        Props.setproperty ("password", password);

        2. Connect the database, return the connection object Connection conn = driver.connect (URL, props);
    SYSTEM.OUT.PRINTLN (conn); /** * Use the Driver Manager class to connect to the database (registered two times, not necessary) * @throws Exception * * * @Test public void test2 () throws Excep
        tion{Driver Driver = new Com.mysql.jdbc.Driver (); DriVer driver2 = new Com.oracle.jdbc.Driver ();
        1. Registration driver (can register multiple drivers) drivermanager.registerdriver (driver);

        Drivermanager.registerdriver (DRIVER2);
        2. Connect to the specific database Connection conn = drivermanager.getconnection (URL, user, password);

    SYSTEM.OUT.PRINTLN (conn); /** * (the recommended way to connect to a database) * recommended to use the load driver class to register the driver * @throws Exception */@Test public void

        Test3 () throws exception{//driver Driver = new Com.mysql.jdbc.Driver ();

        Registers the driver Class.forName ("Com.mysql.jdbc.Driver") by loading the static code block in the form of a byte code object;
        Driver driver2 = new Com.oracle.jdbc.Driver ();
        1. Registration driver (can register multiple drivers)//drivermanager.registerdriver (driver);

        Drivermanager.registerdriver (DRIVER2);
        2. Connect to the specific database Connection conn = drivermanager.getconnection (URL, user, password);

    SYSTEM.OUT.PRINTLN (conn);
 }

}

Core APIs

|-Driver Interface: Represents the Java driver interface.
            All the specific database vendors are going to implement this interface. |-Connect (URL, properties): The way to connect to a database. URL: URL URL syntax for connection database: JDBC Protocol: Database Child Protocol://HOST: Port/Database User: Database username password: number According to the library user password |-DriverManager class: Driver manager class, for managing all registered drivers |-registerdriver (driver): Registering the Driver class object |-connection getconnection (URL,  User,password); Get Connection Objects |-Connection interface: A Connection object that represents a Java program and a database. | | Statement createstatement (): Create Statement Object | | preparedstat Ement preparestatement (String sql) Create PreparedStatement object | |-callablestatement preparecall (String sql) Create Callables Tatement Object |-statement interface: For executing static SQL statements | | int executeupdate (String sql): Executing a static update SQL statement (DDL,DML) |-ResU Ltset executequery (String sql): Executing static query SQL statement (DQL) |-preparedstatement interface: For executing precompiled SQL statements |-int EXECUTEUPDA Te (): Performs a precompiled update SQL statement (DDL,DML) |-resultset executequery (): Performs a precompiled query SQL statement (DQL) |-callablestatement interface:
  The SQL statement used to execute the stored procedure (call XXX)              |-resultset executequery (): Methods for calling stored Procedures |-ResultSet interface: For encapsulating data from queries |-Boolean next (): Move the cursor to the next line
 |-GETXX (): Gets the value of the column

Execute DDL

/** * Execute DDL statement (CREATE TABLE) */@Test public void Test1 () {Statement stmt = null;
    Connection conn = null;

        try {//1. Driver registration Program Class.forName ("Com.mysql.jdbc.Driver");

        2. Get the Connection Object conn = drivermanager.getconnection (URL, user, password);

        3. Create Statement stmt = Conn.createstatement (); 4. Prepare SQL String sql = "CREATE TABLE student (id INT PRIMARY KEY auto_increment,name VARCHAR (), Gender VARCHAR (2))

        ";

        5. Send SQL statement, execute SQL statement, get return result int count = stmt.executeupdate (SQL); 6. The output System.out.println ("affects the +count+" line.)
    ");
        catch (Exception e) {e.printstacktrace ();
    throw new RuntimeException (e);
            finally{//7. Close the connection (order: closed first after opening) if (Stmt!=null) try {stmt.close ();
                catch (SQLException e) {e.printstacktrace ();
            throw new RuntimeException (e);
  } if (Conn!=null) try {              Conn.close ();
                catch (SQLException e) {e.printstacktrace ();
            throw new RuntimeException (e);
 }
    }
}

perform DML

PreparedStatement vs statment
1) syntax is different: PreparedStatement can use precompiled SQL, and statment can only use static SQL
2 for different efficiencies: PreparedStatement can use SQL buffers, which are more efficient than statment
3) Security: PreparedStatement can effectively prevent SQL injection, while statment does not prevent SQL injection.
recommended use of PreparedStatement

/** * Use statement to execute DML statements * @author APPle * * */public class Demo2 {private String URL = "jdbc:mysql://localhost:33
    06/day17 ";
    Private String user = "root";

    Private String Password = "root";
        /** * Add * * @Test public void Testinsert () {Connection conn = null;
        Statement stmt = null;

            try {//Get the Connection object through the tool class Conn = Jdbcutil.getconnection ();

            3. Create statement Object stmt = Conn.createstatement ();

            4.sql statement String sql = "INSERT into student (Name,gender) VALUES (' Dick ', ' female ')";

            5. Execute SQL int count = stmt.executeupdate (SQL);

        System.out.println ("Affected" +count+ "line");
            catch (Exception e) {e.printstacktrace ();
        throw new RuntimeException (e);
                finally{//Close resource/*if (stmt!=null) try {stmt.close ();
        catch (SQLException e) {            E.printstacktrace ();
                throw new RuntimeException (e);
                } if (Conn!=null) try {conn.close ();
                    catch (SQLException e) {e.printstacktrace ();
                throw new RuntimeException (e);
        }*/Jdbcutil.close (conn, stmt);
        }/** * Modify/@Test public void Testupdate () {Connection conn = null;
        Statement stmt = null;
        Simulate user input String name = "Chen VI";
        int id = 3;

            try {/*//1. Registration drive Class.forName ("Com.mysql.jdbc.Driver"); 2. Get the Connection Object conn = drivermanager.getconnection (URL, user, password);////Get Connection object through tool class Co

            nn = jdbcutil.getconnection ();

            3. Create statement Object stmt = Conn.createstatement (); 4.sql statement String sql = "UPDATE student SET name= '" +name+ "' WHERE id=" +id+ "";

            SYSTEM.OUT.PRINTLN (SQL);

            5. Execute SQL int count = stmt.executeupdate (SQL);

        System.out.println ("Affected" +count+ "line");
            catch (Exception e) {e.printstacktrace ();
        throw new RuntimeException (e);
                finally{//Close resource/*if (stmt!=null) try {stmt.close ();
                    catch (SQLException e) {e.printstacktrace ();
                throw new RuntimeException (e);
                } if (Conn!=null) try {conn.close ();
                    catch (SQLException e) {e.printstacktrace ();
                throw new RuntimeException (e);
        }*/Jdbcutil.close (conn, stmt);
        }/** * Delete * * @Test public void Testdelete () {Connection conn = null;
        Statement stmt = null; //analog user input int id = 3;

            try {/*//1. Registration drive Class.forName ("Com.mysql.jdbc.Driver"); 2. Get the Connection Object conn = drivermanager.getconnection (URL, user, password);////Get Connection object through tool class Co

            nn = jdbcutil.getconnection ();

            3. Create statement Object stmt = Conn.createstatement ();

            4.sql statement String sql = "DELETE from student WHERE id=" +id+ "";

            SYSTEM.OUT.PRINTLN (SQL);

            5. Execute SQL int count = stmt.executeupdate (SQL);

        System.out.println ("Affected" +count+ "line");
            catch (Exception e) {e.printstacktrace ();
        throw new RuntimeException (e);
                finally{//Close resource/*if (stmt!=null) try {stmt.close ();
                    catch (SQLException e) {e.printstacktrace ();
                throw new RuntimeException (e);
           } if (conn!=null) try {conn.close ();
                    catch (SQLException e) {e.printstacktrace ();
                throw new RuntimeException (e);
        }*/Jdbcutil.close (conn, stmt);
 }
    }
}

Perform DQL

/** * Use statement to execute DQL statement (query operation) * @author APPle/public class Demo3 {@Test public void test1 () {Conn
        Ection conn = null;
        Statement stmt = null;
            try{//Get Connection conn = Jdbcutil.getconnection ();
            Create Statement stmt = Conn.createstatement ();
            Prepare SQL String sql = "SELECT * from student";

            Execute SQL ResultSet rs = stmt.executequery (SQL);

            Move cursor/*boolean flag = Rs.next ();
            Flag = Rs.next ();
            Flag = Rs.next ();
                if (flag) {//Fetch column value//index int id = rs.getint (1);
                String name = rs.getstring (2);
                String gender = rs.getstring (3);

                System.out.println (id+ "," +name+ "," +gender);
                Column name int id = rs.getint ("id");
                String name = rs.getstring ("name"); String gender = Rs.getstrING ("gender");
            System.out.println (id+ "," +name+ "," +gender);
                }*///Traversal result while (Rs.next ()) {int id = rs.getint ("id");
                String name = rs.getstring ("name");
                String gender = rs.getstring ("gender");
            System.out.println (id+ "," +name+ "," +gender);
            }}catch (Exception e) {e.printstacktrace ();
        throw new RuntimeException (e);
        }finally{Jdbcutil.close (conn, stmt); }
    }
}

Execute SQL statements using PreparedStatement

public class Demo1 {/** * Add/@Test public void Testinsert () {Connection conn = null;
        PreparedStatement stmt = null;

            try {//1. get connection conn = Jdbcutil.getconnection (); 2. Prepare precompiled SQL String sql = INSERT into student (Name,gender) VALUES (?,?);

            ? represents a placeholder//3 for a parameter. Performs precompiled SQL statements (check syntax) stmt = conn.preparestatement (sql);
            4. Set parameter value/** * parameter one: parameter position starts from 1/stmt.setstring (1, "Dick");

            Stmt.setstring (2, "male");

            5. Send parameters, execute SQL int count = stmt.executeupdate ();

        System.out.println ("Affected" +count+ "line");
            catch (Exception e) {e.printstacktrace ();
        throw new RuntimeException (e);
        finally {jdbcutil.close (conn, stmt); }/** * Modify/@Test public void Testupdate () {Connection conn = nulL
        PreparedStatement stmt = null;

            try {//1. get connection conn = Jdbcutil.getconnection (); 2. Prepare precompiled SQL String sql = "UPDATE student SET name=?" WHERE id=? ";

            ? represents a placeholder//3 for a parameter. Performs precompiled SQL statements (check syntax) stmt = conn.preparestatement (sql);
            4. Set parameter value/** * parameter one: parameter position starts from 1/stmt.setstring (1, "Harry");

            Stmt.setint (2, 9);

            5. Send parameters, execute SQL int count = stmt.executeupdate ();

        System.out.println ("Affected" +count+ "line");
            catch (Exception e) {e.printstacktrace ();
        throw new RuntimeException (e);
        finally {jdbcutil.close (conn, stmt);
        }/** * Delete * * @Test public void Testdelete () {Connection conn = null;
        PreparedStatement stmt = null;

        try {//1. get connection conn = Jdbcutil.getconnection ();    2. Prepare precompiled SQL String sql = "DELETE from student WHERE id=?";

            ? represents a placeholder//3 for a parameter. Performs precompiled SQL statements (check syntax) stmt = conn.preparestatement (sql);

            4. Set parameter value/** * parameter one: parameter position starts from 1/stmt.setint (1, 9);

            5. Send parameters, execute SQL int count = stmt.executeupdate ();

        System.out.println ("Affected" +count+ "line");
            catch (Exception e) {e.printstacktrace ();
        throw new RuntimeException (e);
        finally {jdbcutil.close (conn, stmt);
        }/** * Query * * * @Test public void Testquery () {Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;

            try {//1. get connection conn = Jdbcutil.getconnection (); 

            2. Prepare precompiled SQL String sql = "SELECT * from student";

        3. Precompiled stmt = conn.preparestatement (sql);    4. Execute SQL RS = Stmt.executequery ();
                5. Traverse RS while (Rs.next ()) {int id = rs.getint ("id");
                String name = rs.getstring ("name");
                String gender = rs.getstring ("gender");
            System.out.println (id+ "," +name+ "," +gender);
            } catch (Exception e) {e.printstacktrace ();
        throw new RuntimeException (e);
        Finally {//Shut down Resource jdbcutil.close (CONN,STMT,RS);
 }
    }
}

callablestatement Execute stored Procedures

/** * Use Cabllestatement to invoke stored procedures * @author APPle * * */public class Demo1 {/** * Invoke stored procedures with input parameters * Call Pro_
     FindByID (4);
        * * @Test public void test1 () {Connection conn = null;
        CallableStatement stmt = null;
        ResultSet rs = null;

            try {//Get Connection conn = Jdbcutil.getconnection (); Prepare SQL String sql = "Call Pro_findbyid (?)";

            can perform precompiled SQL//Precompiled stmt = Conn.preparecall (sql);

            Set Input Parameters Stmt.setint (1, 6); Send parameter rs = Stmt.executequery ();

            NOTE: All SQL statements that invoke stored procedures are executed using the ExecuteQuery method ...
                Traversal result while (Rs.next ()) {int id = rs.getint ("id");
                String name = rs.getstring ("name");
                String gender = rs.getstring ("gender");
            System.out.println (id+ "," +name+ "," +gender);
    } catch (Exception e) {e.printstacktrace ();        throw new RuntimeException (e);
        finally {jdbcutil.close (conn, stmt, RS);
     }/** * Executes a stored procedure with an output parameter * call PRO_FINDBYID2 (5, @NAME);
        * * @Test public void Test2 () {Connection conn = null;
        CallableStatement stmt = null;
        ResultSet rs = null;
            try {//Get Connection conn = Jdbcutil.getconnection (); Prepare SQL String sql = "Call Pro_findbyid2 (?,?)"; The first one. is the input parameter, the second one.

            is the output parameter//Precompiled stmt = Conn.preparecall (sql);
            Set Input Parameters Stmt.setint (1, 6);
             Set output parameters (register output parameters)/** * parameter one: parameter position * parameter two: JDBC type of output parameter in stored procedure VARCHAR (20)

            * * Stmt.registeroutparameter (2, Java.sql.Types.VARCHAR); Send parameters, execute stmt.executequery ();
        The result is not returned to the result set, but rather returns to the output parameter//The value of the output parameter/** * Index value: The position of the output parameter in the precompiled SQL * *    String result = stmt.getstring (2);

        The Getxx method is specially used to obtain the output parameter System.out.println (result) in the stored procedure;
            catch (Exception e) {e.printstacktrace ();
        throw new RuntimeException (e);
        finally {jdbcutil.close (conn, stmt, RS);
 }
    }
}