JDBC mysql crud DAO model SQL injection Vulnerability JDBC Operation large file

DAY17 Summary

Today's content

L JDBC

1.1 Summary of last lesson

SQL statements:

1. FOREIGN KEY constraint: foreign key

* Maintain multiple table relationships!

* To ensure data integrity!

2. Three types of relationships:

* One-to-many:

* One customer can correspond to multiple orders, an order belongs to only one customer!

* The principle of building the table:

* Create a field on more than one side, as the primary key for the foreign key to one side!!!

* Many-to-many:

* A student can choose multiple courses, a course can also be selected by multiple students!

* The principle of building the table:

* Create a third table, the third table in the two fields, as the foreign key points to many-to-many sides of the primary key!

One

* One: Primary key correspondence Method:

* Two tables with primary key to associate!

* Two: Unique FOREIGN Key Association:

* Suppose a one-to-many relationship is a multi-relationship! You need to create a foreign key to point to one of the primary keys on the other side! Set this foreign key to unique.

3. Multi-Table query:

* Internal connection:

* INNER JOIN

* SELECT * from A inner join B on condition;

* Select * form a b where condition;

* External connection:

* Left outer connection

* LEFT OUTER JOIN

* Right Outer connection

* Right OUTER JOIN

* Sub-query:

* Other SELECT statements are embedded in the select.

* Any: one

* All: All

1.2 jdbc:1.2.1 What is JDBC

JDBC:JDBC (Java Data Base Connectivity,Java database connection ) is a Java API for executing SQL statements that provides unified access to a variety of relational databases, which is a set of classes and interfaces written in the Java language

JDBC is a set of connection database standards provided by SUN Corporation!!!

* Driver: Java language and database equivalent to two different devices! Java access to the database! Need to communicate, communication needs to be driven!

* Database drivers are usually jar files!

* Suppose the Java programmer wants to connect to the MySQL database and understand the MySQL database driver. If the programmer wants to connect to the Oracle database, understand the Oracle driver!

* Sun company discusses with various database manufacturers. Sun provides a set of interfaces. Each database manufacturer provides this set of interface implementation classes!

1.2.2 Common objects in JDBC:

DriverManager:

Connection:

Statement:

* PreparedStatement

* CallableStatement

ResultSet:

1.2.3 JDBC's Introductory case:

Development steps:

1. Registration driver.

2. Get the connection.

3. Execute the SQL statement.

4. Release resources.

To create a database day17:

Create database day17;

Use DAY17;

Create Table User (

Id int primary Key auto_increment,

Username varchar (20),

Password varchar (20)

);

Insert into user values (null, ' AAA ', ' AAA ');

Insert into user values (null, ' BBB ', ' BBB ');

Insert into user values (NULL, ' CCC ', ' CCC ');

1.2.4 JDBC API in detail: DriverManager: Management driver

Role:

First, the registration drive

* Registerdriver (Driver Driver)---Complete the registration of the driver.

* This method will not be used in future development! There are two problems with this method!!!

* 1. The application depends on the specific driver!

* 2. This method will also cause the driver to register two times!

* View the source code of Com.mysql.jdbc.Driver class:

static {

try {

Java.sql.DriverManager.registerDriver (New Driver ());

} catch (SQLException E) {

throw new RuntimeException ("Can ' t Register driver!");

}

}

***** A static block of code executes when the class is loaded . !

***** solve both of these problems ?

Class.forName ("Com.mysql.jdbc.Driver");---load the bytecode into memory, the static code block executes as long as it is loaded into memory! And the application does not need to rely on specific drivers!

Second, access to the connection

* getconnection (string url, string user, string password)

Parameters

* URL: Connect to database URL

* JDBC:MYSQL://LOCALHOST:3306/DAY17

* JDBC: protocol

* MySQL: Is the sub-protocol of JDBC

* LocalHost: Host name

* 3306: Port number

* DAY17: Database name

* URL Shorthand: The connection is a local MySQL database.

* JDBC:MYSQL:///DAY17

* User: Connect database username

* Password: Connect database password

Connection: The connection represented

Role:

I. Creating an object that executes an SQL statement

* Statement createstatement ()---Execute SQL statement

* PreparedStatement preparestatement (String SQL)---Pre-compiling SQL to resolve SQL injection vulnerabilities.

* CallableStatement preparecall (String SQL)---Call the database stored procedure.

II. Management Services

* void Setautocommit (Boolean autocommit)---Set whether the transaction is automatically committed.

* void commit (); ---transaction submission

* void rollback (); ---transaction rollback

Statement: Execute SQL

Role:

First, execute SQL.

* ResultSet executeQuery (String sql); ---Execute the SELECT statement.

* int executeupdate (String sql); ---Execute the update, INSERT, DELETE statement, int represents the number of rows affected by the SQL statement.

* Boolean execute (String sql); ---Returns TRUE if the execution SQL has a result set. False if no result set is returned.

Second, the implementation of batch processing.

* void Addbatch (String sql); ---Add SQL to the batch.

* int[] ExecuteBatch (); ---execute batch processing

* void Clearbatch (); ---purge batches

ResultSet: Result set object. Refers to the query statement that returns the result.

A cursor is maintained inside the result set: Before starting to point to the first row. Calling next () will point to the exact line.

The result set obtains the data:

* GetXXX (int idx)---getXXX (String name);

* INT idx: column number.

* String Name: Column name (recommended)

Scrolling result set: (Learn)

* The statement object needs to be set using the scrolling result set.

* How do I create a statement when I use it?

* Statement stmt = conn.createstatement ();

* There is one such method in the Connection object

* createstatement (int resultsettype, int resultsetconcurrency)

Parameters

* ResultsetType: Result set type

* Type_forward_only: Result set can only be down

* Type_scroll_insensitive: The result set can be scrolled, but cannot be modified.

* Type_scroll_sensitive: The result set can be scrolled and the record can be modified.

* resultSetConcurrency: Result set concurrency policy

* Concur_read_only: Read-only

* Concur_updatable: Can be modified by

Combination

* Type_forward_only concur_read_only: The result set can only be down and cannot be modified. (The default)

* Type_scroll_insensitiv concur_read_only: The result set can be scrolled, but cannot be modified.

* Type_scroll_sensitive concur_updatable: The result set can be scrolled and can be modified.

1.2.5 Resource Release

After the JDBC program finishes running, remember to release the objects that are created by the program to interact with the database during the run, typically resultset, statement, and connection objects.

In particular, connection object, it is very rare resources, after use must be released immediately, if the connection can not be timely, correct shutdown, it is very easy to cause system downtime. Connection's use principle is to create as late as possible, releasing as early as possible.

To ensure that the resource release code can run, the resource release code must also be placed in the finally statement.

To view the MySQL documentation:

Code for the standard resource release:

if (rs! = null) {

try {

Rs.close ();

} catch (SQLException Sqlex) {

}

rs = null;

}

if (stmt! = null) {

try {

Stmt.close ();

} catch (SQLException Sqlex) {

}

stmt = null;

}

if (conn!= null) {

try {

Conn.close ();

} catch (SQLException Sqlex) {

}

conn= null;

}

1.2.6 CRUD Operations

See Code

Extraction of 1.2.7 tool class 1.2.8 Java EE development mode DAO Pattern: 1.2.9 DAO's login case:

1. Build the development environment:

* Import jar package.

* Import Tool classes and configuration files.

2. Design page:

3. Build the Package

SQL Injection Vulnerability:

Known user name, password do not know! Log in with your account!!

Input

1, user name: Zhang San ' or ' 1=1 password is arbitrary.

2, user name: Zhang San '--password arbitrary.

* The cause of the problem:

* Splicing of SQL:

* SELECT * FROM user where username = ' variable ' and password = ' variable ';

* Demo question:

* SELECT * FROM user where username = ' Zhang San ' or ' 1=1' and password = ' random ';

* SELECT * FROM user where username = ' Zhang San '--' and password = ' variable ';

* Avoid the problem:

* Use JS to verify the front-end: Control text box cannot enter special characters!!!

* JS checksum is not required! In order to improve the user's experience! JS calibration can be bypassed!

* Background check is required!

* Resolve SQL injection vulnerabilities completely:

* PreparedStatement: SQL can be precompiled. SQL variables are used as placeholders.

* SELECT * FROM user where username =? and password =?; The---? number represents a variable. PreparedStatement compiles SQL ahead of time. The compiled SQL format is fixed.

1.2.10 JDBC's large data type reads and writes.

There are two types in the MySQL database: TEXT, BLOB. Text is used for the storage of binary files. (Photo, MP3)

* CLOB, blobs in Oracle Database

* Use JDBC to manipulate large file types in the database.

* Tinytext (255), TEXT (64k), Mediumtext (16M) and Longtext (4G)

* Create a table:

Create Table MyText (

Id int primary Key auto_increment,

Data Mediumtext

);

Create Table Myblob (

Id int primary Key auto_increment,

Data Mediumblob

);

1.2.11 Performing batch Processing

Batch processing: Executes a batch of SQL.

10,000 records are inserted in the MySQL database for approximately 4 minutes.

* 100000: More than 40 seconds. 10000:4 seconds.

Oracle:

* 100,000:4 seconds. 10000: Instant.

Today's content Summary:

Jdbc:

* JDBC Concept: Java database connection. Sun provides a set of operational database APIs.

* API:

* DriverManager: Management driver

* 1. Registration drive

* Class.forName ("Com.mysql.jdbc.Driver");

* 2. Get the connection

* getconnection (String url,string username,string password);

* Connection: Connection object

* 1. Create an object that executes SQL.

* Statement:

* Preparedstatment:

* Callablestatment:

* 2. Management Services:

* Setautocommit (Boolean flag);

* Commit ();

* ROLLBACK ();

* Statement:

* 1. Execute SQL

* ExecuteQuery (SQL);

* executeupdate (SQL);

* Execute (SQL);

* 2. Perform batch processing:

* Addbatch (SQL);

* ExecuteBatch (SQL);

* Clearbatch ();

* ResultSet:

* Only down cannot be modified by default:

* Traverse result set:

* Get result set values:

* Scrolling result set (learn)

* Release resources:

* JDBC CRUD operations:

* Extract the tool class:

* using the tool class Crud:

* DAO mode:

* DAO The data Access object. encapsulates a single operation on a data source, provides a set of interfaces for the business layer to invoke. pass an object in the process of calling!

* using DAO Model Write login case:

* SQL Injected Vulnerability:

* resolve the use of PreparedStatement.

* Large file read/write: (Learn)

* Batch Processing

JDBC mysql crud DAO model SQL injection Vulnerability JDBC Operation large file