Javaweb Series 12 (JDBC)



Introduction to 1.JDBC
Links to Java database Connectivity;java databases
For example, according to a video card, we need to install the database driver, the Factory Chamber of Commerce provides, provides a jar package, the Sun company provides and the common interface, implements this interface, this interface is JDBC, provides the standard interface for operating the database,
Application of 2.JDBC
First step: Load the database driver using DriverManager inside Registerdriver method
Step Two: Create a link drivermanager.getconnection ();
There are three parameters in the method
First parameter: which database to connect to
Shorthand Method Jdbc:mysql:///day15
Use range: If the connection is native and the port is 3306
Second parameter: Connection database user name
Third parameter: password
Step three: Write SQL statements
Fourth step: Execute the SQL statement
First create the Statement object
Execute SQL through object inside method
If you execute a query statement, use the ExecuteQuery
The query return is a result set resultset
Iterate through the result set to get each record
Fifth step: Release resources, who last open, who will be the first to close

DriverManager object of 3.jdbc
Inside the SQL Package
Main uses:
The first load database driver, Registerdriver will load two times, through the source of MySQL, driver class inside static code block inside is class loading time execution, general use the radial database driver, Class.forName (" Com.mysql.jdbc.Driver "); All that's left is a package that loads SQL
The second drive gets a link to the database
getconnection (String URL; String user,string Password)
First parameter: which database to connect to
Second parameter: Connection database user name
Third parameter: Using a database password
Connection object of 4.JDBC
Inside the SQL Package
Create statement object: Createstatement (): object representing the execution of the SQL statement
Create Preparestatement object: Preparestatement (String sql): Represents a Precompiled object
Create CallableStatement object: Preparecall (String SQL): The object that executes the stored procedure
Setautocommit (Boolean autocommit): Indicates whether it is automatically committed, is set to False, is not automatically committed, and is automatically committed by default
Commit (): Represents the Commit transaction
Rollback (): Indicates ROLLBACK TRANSACTION
Statement object of 5.JDBC
Key Features: Executing SQL statements
Method:
When performing a query operation: ResultSet executeQuery (String sql)
Returns the resultset result set, iterating through the result set to get each record
Execute add modification when deleting: int executeupdate (String sql)
returns int, number of successful records
Methods for executing SQL statements: Boolean execute (String sql)
Returns a Boolean type,
Returns true if the first result is a ResultSet object (performing a query operation);
Returns False if it is an update count or if there are no results
ResultSet object of 6.jdbc
When performing a query operation, return resultset
Methods for traversing result sets: Next ()
while (Rs.next ()) {}
Get the contents of each line:
For example, the Get field is of type int, using method getint ("Field name")
For example, the Get field is a string type, using method GetString ("Field name")
If you do not know what type the field is, use the method GetObject (String ColumnLabel)
Use the Getxx method to get the value, you can write the field name, and you can write the position of the segment, but the position starts at 1.
Traversal of the result set
First, before the first line, when the next method is executed, a line is traversed downward.
By default, the result set can only be passed down, and the values after the traversal cannot be modified
However, you can set the result set to scroll, and you can modify
Traversal of the result set

7. JDBC Scrolling result set (learn)
If you want the result set to be scrolling, the table must have a primary key
Result set type
* Type_forward_only: Result set can only be down
* Type_scroll_insensitive: Result set can be scrolled
* Type_scroll_sensitive: Result set can be scrolled

Result set concurrency policy
* Concur_read_only: Result set cannot be modified
* Concur_updatable: Result set can be modified

    combination:
   * type_forward_only  concur_read_only: Result set can only be down and not modifiable (default)  
   * type_scroll_insensitive  concur_read_only: The result set is scrollable but not modifiable.
   * type_scroll_sensitive  concur_updatable: The result set is scrollable and can be modified.

    Set scrolling result set when creating statement
    createstatement (int resultsettype, int resultsetconcurrency)
Two parameters inside the     method, using the constants inside the ResultSet
8, the JDBC release resource
    Principle: Who last opens, who first closes the
    before closing the connection method causes a problem, if an exception occurs during program execution, the closing statement does not execute to
    write Finally, Indicates whether or not an exception occurred, finally will always be executed to, need to write the closed statement to finally inside
   //close connection
    if (rs! = null) {
    try {
    rs.close ();
    catch (SQLException e) {
    e.printstacktrace ();
   
   //Allow JVM to be reclaimed quickly
    rs = null;
   }

10. Package JDBC Tool class
    read the properties configuration file in two ways:
    The first way to read the properties configuration file is to read with the ClassLoader (read only the file below classes)
   //Because the file is under SRC, the deployment into Tomcat will be inside classes
   //Read the file inside the classes, use the ClassLoader to read the properties
    inputstream in = MyJDBCUtils.class.getClassLoader (). getResourceAsStream ("db.properties");
   //Read properties file
    Properties P = new Properties ();
    p.load (in);
    drivername = P.getproperty ("drivername");
    url = p.getproperty ("url");
    username = p.getproperty ("username");
    password = p.getproperty ("password");

Generally read in the second way in development (using the ResourceBundle tool class to read the properties configuration file)
Resourcebundle.getbundle ("Profile name, no suffix name"). GetString ("Name of the value inside the configuration file");
static {
drivername = Resourcebundle.getbundle ("db"). GetString ("drivername");
url = resourcebundle.getbundle ("db"). getString ("url");
Username = Resourcebundle.getbundle ("db"). GetString ("username");
Password = resourcebundle.getbundle ("db"). GetString ("password");
}
Scope of Use: Only the properties configuration file inside the classes can be read
If you want to read the configuration file while the tool class is loading, assign a value,
To implement static {inside the code that reads the configuration file} by writing a code block

11. The DAO pattern of Java EE
Review the MVC development model (also known as Model II)
M: Model, using JavaBean technology to implement encapsulated data
V: view, using JSP technology to implement display data
C: Controller: Use servlet technology for management operations, what data to display in which JSP page
DAO: The mode used to manipulate the database

Drawing Analysis DAO pattern
Java EE architecture: Client layer, Web tier, business logic layer, persistence layer
Web layer, business logic layer, persistence layer This three layer is called Java EE three-tier structure
DAO Pattern: Using patterns in the persistence layer, focusing on operating mode (CRUD) on the database
The interface is created first, and the method of manipulating the database is defined inside the interface.
The second way to create a class to implement this interface is to manipulate the database.
Manipulating databases at the business logic layer through the interfaces provided by DAO
JSP+SERVLET+JAVABEAN+JDBC Architecture: Allows you to achieve all the applications you can see today
This architecture is not used in the general enterprise because the architecture is too low (too much code)
The general enterprise inside uses the framework to implement the system, the general commonly used frame SSH:STRUTS2 spring hibernate
General use of the STRUTS2 framework at the Web tier
General use of the spring framework at the business logic level
Hibernate framework generally used in persistence layer
The relationship between MVC and DAO
Both are patterns, and these two patterns are different development periods, which are presented by different people. MVC is proposed by the developer,
The DAO model was proposed by Sun. MVC overall architecture pattern, DAO focuses on persistence layer (Operation database mode)
You can use the DAO pattern inside the MVC pattern to focus on manipulating the database
13. Prevention of SQL Injection Vulnerability
SELECT * from Stu where sname= ' "+user.getusername () +" ' and Password= ' "+user.getpassword () +" ' "
SELECT * from Stu where sname= ' Zhangsan ' or ' 1=1 ' and password= ' QQQ ' "
Using Preparestatement objects to prevent SQL injection
Precompiled SQL Object: pre-compile SQL before executing
Create Preparestatement
PreparedStatement preparestatement (String sql)
Code
String sql = "SELECT * from Stu where sname=?" and password=? ";
Pre-compiled SQL
PST = conn.preparestatement (SQL);
Passing parameters, the first parameter indicates that the first argument starts from 1
Pst.setstring (1, User.getusername ());
Pst.setstring (2, User.getpassword ());
Execute SQL
rs = Pst.executequery ();

14. JDBC Batch operations (learn)
Batch: Execute multiple SQL statements at one time
Method
Addbatch (SQL): Loading SQL statements into batch operations
ExecuteBatch (): Performing a batch operation
Code
Create a statement object
stmt = Conn.createstatement ();
Add these SQL to the batch process.
Stmt.addbatch (SQL1);
Stmt.addbatch (SQL2);
Stmt.addbatch (SQL3);
Execute multiple SQL
Stmt.executebatch ();

Javaweb Series 12 (JDBC)