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)