JAVA’s JDBC knowledge framework

I. overview of JDBC

  • Why use JDBC?
  • Standard Specification for a set of operating databases provided by JDBC:java database connectivity SUN.
JAVA's JDBC knowledge framework
JDBC specification
  • The relationship between JDBC and database drivers: the relationship between interfaces and implementation.
JAVA's JDBC knowledge framework,
, JDBC, and database driven relationships
  • The JDBC specification (master four core objects):
    DriverManager: for
    Connection: said register drive and database connections created
    Statement: operation of the database SQL statement object
    ResultSet: result set or a virtual table
  • Prepare the development of a JDBC program:

Where is the JDBC specification:
JDK:
java.sql.;
javax.sql.;
database vendor driver: jar file *.jar

JAVA's JDBC knowledge framework
pictures 1

Two, the development of a JDBC program (important)

The data in the query database is displayed in the Java console

1. Create a database table and add test data to the table

Create database day06; use day06; create table users (id int primary key auto_increment, name varchar (40), password varchar (40), email varchar (60), birthday date character set utf8) collate utf8_general_ci; insert into users (name, password, email, birthday) values ('zs','123456','[email protected]'.'1980-12-04' insert into (name); users, password, email, birthday) values ('lisi','123456','[email protected]','1981-12-04'); insert into users (name, password, email, birthday) values ('wangwu','123456','[email protected]','1979-12-04');

2, create java project project and add database driver (*.jar)

3, the realization of JDBC operations

//1, register, drive //2, create connection //3, get the Statement object //4 that executes the SQL statement, execute the SQL statement, return the result //5, and process the result //6, turn off the resource
JAVA's JDBC knowledge framework
pictures 2
JAVA's JDBC knowledge framework
pictures 3

Three, JDBC commonly used classes and interfaces detailed

1, java.sql.Drivermanager class: create connection

  • A, registered driver
    DriverManager.registerDriver (New com.mysql.jdbc.Driver ());
    is not recommended for 2 reasons:

Causes drive to be registered 2 times.
relies heavily on the database driver jar
solution (reflection mechanism):
Class.forName (“com.mysql.jdbc.Driver”);

  • B, establish connection with database
The 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"); a protocol between URL:SUN and database vendors. Jdbc:mysql://localhost:3306/day06 protocol: IP protocol port number mysql: jdbc:mysql://localhost:3306/day14 database or jdbc:mysql:///day14 (the default oracle: jdbc:oracle:thin:@localhost:1521:sid Properties info connection) = new (Properties); / / reference database to info.setProperty document ("user", "root"); info.setProperty ("password", "root"); getConnection (String URL, Properties info getConnection (String) URL) DriverManager.getConnection ("jdbc:mysql://localhost:3306/day14? User=root& password=root);

2, java.sql.Connection interface: a connection

  • The implementation of the interface is in the database drive. All database interactions are based on connection objects.
  • Statement (createStatement); / / create objects of operation of the SQL statement

3, java.sql.Statement interface: operation SQL statement, and return the corresponding results of objects (small trucks)
interface implementation in database drive. The object used to execute the static SQL statement and return the result it generated.

  • ResultSet executeQuery (String SQL) returns the result set based on the query. Only select statements can be executed.
  • Int executeUpdate (String SQL) returns the affected number of rows based on the executed DML (insert, update, delete) statement.
  • Boolean execute (String SQL). This method can execute any SQL statement. Returns the Boolean value indicating whether or not to return the ResultSet result set. Returns the true only if the select statement is executed and returns results, and all other statements return false;

4, java.sql.ResultSet interface: result set (client to store table data objects)

  • A, package result set.
    provides a cursor, which defaults to the first line of the result set.
    calls next () once, and the cursor moves down a line.
    provides some get methods.
The method of encapsulating data Object getObject (int columnIndex); according to the ordinal value, the index starts from 1, Object getObject (String ColomnName), and values according to the column name. The data package in the result set to javaBean type Java data types in the database and the relationship between byte tityint short smallint int int long bigint float float double double String char varchar Date date Boolean (next) from the current position of the cursor moves down a line of int getInt (int colIndex) in the form of int to obtain the ResultSet result set current for the specified column number value of int getInt (String colLabel) in the form of int to obtain the ResultSet result set the current row column name is specified (int colIndex) float getFloat in the form of float to obtain ResultSet result set the current row specified column number value of float getFloat (String colLabel) in the form of float to obtain the ResultSet result set the current line value of String getString int (specified column name in the form of String by colIndex) The results obtained ResultSet set the current row specified column number value of String getString (String colLabel) in the form of String to obtain the ResultSet result set the current row column name is specified (int columnIndex) Date getDate; Date getDate (String columnName); void close (ResultSet) to close like B, can move the cursor Boolean (next method) from the cursor the current position of forward line. Boolean previous () moves the cursor to the previous line of the ResultSet object. The Boolean absolute (int row) argument is the index of the current row, starting from 1, locating the specified index rows based on the index of the rows. Void afterLast () moves the cursor to the end, just after the last line. Void beforeFirst () moves the cursor to the beginning, just before the first line.

5, the release of resources: limited resources, to properly shut down.

JAVA's JDBC knowledge framework
pictures 4
JAVA's JDBC knowledge framework
pictures 5

Four, the use of JDBC to achieve CRUD operations

Import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.ResourceBundle; public class DBUtils static String private {private driverClass; static String URL; private static String username private static String; password static{; / / this object is used to load the properties file data ResourceBundle RB = ResourceBundle.getBundle ("dbinfo"); //dbinfo.properties the configuration file of driverClass = rb.getString ("driverClass"); url = rb.getString ("URL"); username = rb.getString ("username"); password = rb.getString ("password"); try {Class.forName (driverClass); Catch (ClassNotFoundException E) {}}} (e.printStackTrace); / / get the connection method of public static Connection (getConnection) throws Exception{return DriverManager.getConnection (URL, username, password);} / / public static void closed resources method closeAll (ResultSet RS, Statement stmt, Connection conn) {/ / close resource if (RS! =null (try) {{rs.Close}); catch (Exception E) {e.printStackTrace (rs = null);};} if (stmt! =null) {{stmt.close} (try); catch (Exception E) {(e.printStackTrace)}; Stmt = null;} if (conn! =null) {{conn.close} (try); catch (Exception E) {e.printStackTrace (conn = null);};}}}

Five, to achieve a user login function

JAVA's JDBC knowledge framework
pictures 6

Six, SQL injection problem: preparedStatement

PreparedStatement: a precompiled object that is a subclass of the Statement object.

Features:
performance is high,
will first compile the SQL statement,
, SQL statements in the parameters will change, filtering the user input keywords.

JAVA's JDBC knowledge framework
pictures 7

Supplementary example:

JAVA's JDBC knowledge framework
pictures 8