Java JDBC Summary One (basic operations and SQL injection issues)

JDBC Definition: JDBC (Java database Connectivity,java connection) is a Java API for executing SQL statements. JDBC is the standard specification for Java access to databases and provides unified access to different relational databases, consisting of a set of interfaces and classes written in the Java language.

JDBC Specification (mastering four core objects):

DriverManager class: For registering drivers (Management control driver)

Connection: Represents a connection to a database creation

Statement: Objects that manipulate database SQL statements

ResultSet: Result set or a virtual table (query results return ResultSet set)

JDBC Case Implementation

@Test

// Check all the classification information

public void Demo1 () throws exception{

// Note: Using the JDBC specification, the content under the java.sql package is used

//1 registered driver

class.forname ("Com.mysql.jdbc.Driver");

//2 getting connected

String url = "Jdbc:mysql://localhost:3306/mydb";

Connection conn = drivermanager.getconnection (URL, "root", "root");

//3 getting The object that executes the SQL statement

Statement stmt = Conn.createstatement ();

//4 executing SQL Statements

ResultSet rs = stmt.executequery ("Select * from category");

//5 processing result sets

while (Rs.next ()) {

// get one row of data

Integer cid = rs.getint ("cid");

String cname = rs.getstring ("CNAME");

System.out.println (cid + "," + CNAME);

    }

//6 Releasing Resources

rs.close ();

stmt.close ();

conn.close ();  

}

APIExplanation: Registration driver

Drivermanager.registerdriver (New Com.mysql.jdbc.Driver ());

There are 2 reasons:

> causes the driver to be registered 2 times.

> strongly relies on the database driver jar

Workaround:

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

APIDetailed: Get links

Static Connection getconnection (string url, string user, string password)

An attempt was made to establish a connection to a given database URL.

Parameter description: The URL needs to connect to the database location (URL) user username password password

For example: getconnection ("Jdbc:mysql://localhost:3306/day06", "root", "root");

An agreement between the Url:sun company and the database vendor.

Jdbc:mysql://localhost:3306/day06

Protocol sub-Protocol IP: Port number Database

mysql:jdbc:mysql://localhost:3306/day04 or JDBC:MYSQL:///DAY14 (default native connection)

Oracle database: Jdbc:oracle:thin: @localhost: 1521:sid

SQLInjection Problems

PreparedStatement: Precompiled object, which is a subclass of the statement object.

Characteristics:

High performance

Will compile the SQL statement first

Can filter out the user input keywords.

PreparedStatement preprocessing objects, all the actual arguments in each SQL statement that are processed must be replaced with placeholders.

String sql = "SELECT * from user where username =?" and password =? ";

PreparedStatement, it needs to be done in the following 3 steps:

1. PreparedStatement Preprocessing object code:

# to obtain a preprocessing object, you need to provide a handler that has already been processed using the placeholder SQL Statement

PreparedStatement PSMT = conn.preparestatement (SQL)

1. Set the actual parameters

void setxxx (int index, Xxx xx) sets the specified parameter to a value of the specified type

parameter 1:index actual parameter sequence number, starting from 1 .

parameter 2:xxx actual parameter value,xxx indicates the specific type.

For example:

setString (2, "1234") put SQL clause in the statement 2 placeholder for a location ? Replace with actual parameters "1234"

2. Execute the SQL statement:

int executeupdate ();-- Execution Insert Update Delete Statement .

ResultSet executeQuery ();--- Execution Select Statement .

Boolean execute ();-- Execution Select return true executes other statements to return false.

Java JDBC Summary One (basic operations and SQL injection issues)