JDBC introduction, jdbc

JDBC introduction, jdbc

Four objects connecting to the database through jdbc

DriverManager Driver Class

DriverManager. registerDriver (new com. mysql. jdbc. Driver (); not recommended

There are two reasons:

> The driver is registered twice.

> Strongly dependent on the database driver jar

Solution:

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

 Connection

Static Connection getConnection (String url, String user, String password)

Attempts to establish a connection to the given Database URL.

GetConnection ("jdbc: mysql: // localhost: 3306/day06", "root", "root ");

URL: a protocol between SUN and the database vendor.

Jdbc: mysql: /// localhost: 3306/day06

Protocol sub-Protocol IP Address: Port Number Database

Database Type

Mysql: jdbc: mysql: // localhost: 3306/day14 or jdbc: mysql: // day14 (default local connection)

Oracle: jdbc: oracle: thin: @ localhost: 1521: sid

GetConnection (String url, Properties info)

Propertiesinfo = new Properties (); // you can use a file instead of a file to reference the database documentation.

Info. setProperty ("user", "root"); // user Name

Info. setProperty ("password", "root"); // password

// Get the connection object return value connection

Connection conn = DriverManager. getConnection ("jdbc: mysql: // localhost: 3306/day14? User = root & password = root ");

Statement operation Database Class

// Create an operation database object

Statement state = conn. createStatement ();

String SQL = "SQL statement ";

Result rt = state.exe cuteQuery (SQL); // return Result set

Problem: SQL Injection

Solution

Use input parameters to prevent SQL Injection
Statement (PreparedStatement) // precompiled object PreparedStatement

Features:

1. High Performance

2. Compile the SQL statement first.

3. Parameters in SQL statements change, and keywords entered by users are filtered out.

Statement state = conn. preparedStatement ();

String SQL = "select * from user where username =? And password =? ";

Result rt = state.exe cuteQuery (SQL); // return Result set

State. setString (1, username );

State. setString (2, password );

Result returned by the execution object
ResultSet executeQuery ();
Int executeUpdate ();
Boolean execute ();

Delete from users where id =?
Ps. setInt (1, 5 );

ResultSet Result set

Result set (Object for storing table data on the client)

// Obtain data

Next ();

GetString ();
GetDouble ();
GetDate ();

Summary
Write code using four core objects

Try {
// Load the driver
Class. forName ("com. mysql. jdbc. Driver ");
// Create a Connection
Connection conn = DriverManager. getConnection ("jdbc: mysql: // day06", "root", "abc ");
// Get the statement object for executing the SQL statement
// Conn. createStatement ();
PreparedStatement ps = conn. prepareStatement ("select * from users where name =? And pwd =? ");
Ps. setString (1, "tom ");
Ps. setString (2, "123 ");
// Execute the statement and return the result
ResultSet rs = ps.exe cuteQuery ();
// Processing result
While (rs. next ()){
User u = new User ();
U. setId (rs. getInt (1 ));
....}
}

Catch (Exception e ){
E. printSt ...}

Finally {
// Close the resource
If (rs! = Null)
{Rs. close ();}
If (ps! = Null)
{Ps. close ();}
If (conn! = Null)
{Conn. close ();}
}