@[TOC]

The official reference documentation: docs.oracle.com/javase/tuto… The following code example links: download.csdn.net/download/we…

1. Introduction to JDBC

1.1 Client Operation Methods of the MySQL Database:

Use third-party clients to access MySQL: SQLyog, Navicat, SQLWave, MyDB Studio, EMS SQL Manager for MySQL

Use the MySQL command line interface (CLI) and Java to access the MySQL database

1.1.1 What is JDBC

JDBC specification definition == interface ==, the specific implementation by the major database vendors to achieve.

JDBC is the standard specification for Java to access a database. How to operate a database really requires a specific implementation class, that is, == database driver ==. Each database vendor writes its own database driver according to its own database communication format. So we only need to be able to call the JDBC interface method, == database driver provided by the database vendor ==.

Benefits of using JDBC:

  1. Programmers who want to develop programs that access databases simply need to be able to call methods in the JDBC interface, regardless of how the class is implemented.
  2. Other JDBC-supported databases can be accessed using the same set of Java code with a few modifications

1.1.2 Packages used for JDBC Development:

Packages that will be used instructions
java.sql All interfaces and classes related to JDBC access to the database
javax.sql Database extension pack that provides additional database functionality. For example, connection pool
Database driver Provided by major database vendors, additional downloads are required to implement the JDBC interface class

1.2 JDBC core API

Interface or class role
DriverManager class 1. Manage and register database drivers 2. Obtain database connection objects
Connectioninterface A connection object that can be used to create Statement and PreparedStatement objects
Statement interface An SQL statement object used to send SQL statements to the database server.
PreparedStatemeninterface An SQL Statement object that is a subinterface to a Statement
ResultSet interface Used to encapsulate the result set of a database query and returned to the client Java program

1.3 Importing the Driver Jar Package

1.4 Loading and Registering the driver

Methods to load and register drivers describe
Class.forName(Database driven implementation class) Load and register the database Driver from the mysql vendor — “com.mysql.jdbc.driver”

Registration driver code:

public class Demo1  {
	public static void main(String[] args) throws ClassNotFoundException {
		// Throw an exception that the class cannot find, register the database driver
		Class.forName("com.mysql.jdbc.Driver"); }}Copy the code

Com.mysql.jdbc.driver source code:

// The Driver interface, which all database vendors must implement, represents a Driver class.
public class Driver implements java.sql.Driver { 
	public Driver(a) throws SQLException {}static { 
		try {
			DriverManager.registerDriver(new Driver());   
			// Register the database driver
		} 
		catch (SQLException var1) {
			throw new RuntimeException("Can't register driver!"); }}}Copy the code

Note: starting with JDBC3, the version that is currently in common use. Can be used directly without registering the driver. The sentence class.forname can be omitted.

2. The DriverManager class

2.1 DriverManager role:

1) Manage and register drivers 2) create database connections

2.2 Methods in class:

Static method in the DriverManager class describe
Connection getConnection (String url, String user, String password) Get the database connection object from the connection string, username, and password
Connection getConnection (String url, Properties info) Get the connection object from the connection string, property object
Use JDBC to connect to a database.
Four parameters for JDBC to connect to a database instructions
– –
The user name User name for logging in
password Login password
Connection string URL Mysql > select * from ‘mysql’;JDBC: mysql: / / localhost: 3306 / database [?] parameter name = parameter values
The string name of the driver class com.mysql.jdbc.Driver
Mysql > connect to database
Protocol name: Sub-protocol :// Server name or IP address: port number/database name? Parameter = Parameter value
### 2.4.1MySQL

2.4.2MySQL:

Prerequisite: The server must be local and the port number is 3306

jdbc:mysql:/// Database name
Copy the code

2.4.3 Processing garbled characters

If the database is garbled, you can specify the following argument:? CharacterEncoding = UTF8, which means that the database processes the data in UTF-8 encoding.

jdbc:mysql://localhost:3306 characterEncoding=utf8
Copy the code

2.5 Case: Obtain the MySQL database connection object

2.5.1 Using the user name, password, and URL to obtain the connection object

package com.sqltest; 
import java.sql.Connection;
import java.sql.DriverManager; 
import java.sql.SQLException;

/** * get the connection object */
public class Demo2 {
    public static void main(String[] args) throws SQLException 
    { 
	    String url = "jdbc:mysql://localhost:3306/day24";
		//1) Use username, password, URL to get the connection object
        Connection connection = DriverManager.getConnection(url, "root"."root");
		//com.mysql.jdbc.JDBC4Connection@68de145System.out.println(connection); }}Copy the code

2.5.2 Use properties file and URL to get connection object

package com.sqltest;

import java.sql.Connection; 
import java.sql.DriverManager; 
import java.sql.SQLException;
import java.util.Properties;

public class Demo3 {
    public static void main(String[] args) throws SQLException {
		// Url connection string
        String url = "jdbc:mysql://localhost:3306/day24";
		// Attribute object
        Properties info = new Properties();
		// Put the username and password in the info object

        info.setProperty("user"."root"); 	
        info.setProperty("password"."root");
        Connection connection = DriverManager.getConnection(url, info);
		//com.mysql.jdbc.JDBC4Connection@68de145 System.out.println(connection);}}Copy the code

3. Connection:

3.1 Connection Function:

The Connection interface, whose concrete implementation class is implemented by the vendor of the database, represents a Connection object.

3.2 Connection method:

Methods in the Connection interface describe
Statement createStatement() Create an SQL statement object

4. The Statement interface

4.1JDBC Database Access Procedure

  1. Register and load drivers (can be omitted)
  2. Get connected
  3. Connection Obtains the Statement object
  4. Execute SQL statements using the Statement object
  5. Return result set
  6. Release resources

4.2 Statement Functions:

Represents a statement object that sends SQL statements to the server, executes static SQL statements and returns the results it generates.

4.3 Methods in Statement:

Method in the Statement interface describe
int executeUpdate(String sql) Used to send DML statements, add, delete, delete operations, insert, update, delete

SQL statement:

Return value: Returns the number of rows that affect the database
ResultSet executeQuery(String sql) Send DQL statements to perform query operations. select

Parameter: SQL statement

Return value: The result set of the query

4.4 Releasing Resources

  1. The following objects need to be released: ResultSet ResultSet, Statement Statement, and Connection
  2. Release principle: first open after closed, open after closed first. ResultSet  Statement  Connection
  3. Which code block to put in: finally block

4.5 Performing DDL Operations

4.5.1 Requirements: Use JDBC to create a student table in MySQL database

4.5.2 of code:


package com.itheima;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

/** * create a student table */
public class Demo4DDL {

    public static void main(String[] args) {
        //1. Create a connection
        Connection conn = null; Statement statement = null;
        try {
            conn = DriverManager.getConnection("jdbc:mysql:///day24"."root"."root");
            //2. Get the statement object from the connection object
            statement = conn.createStatement();
            //3. Use the statement object to send SQL statements to the server
/ / 4. Execute SQL
            statement.executeUpdate("create table student (id int PRIMARY key auto_increment, " + "name varchar(20) not null, gender boolean, birthday date)");
//5. Return the number of affected rows (DDL does not return a value)
		System.out.println("Table created successfully");
        } catch (SQLException e) {
            e.printStackTrace();
        }
//6. Release resources
        finally {
    // Check before closing
            if(statement ! =null) {
                try {
                    statement.close();
                } catch(SQLException e) { e.printStackTrace(); }}if(conn ! =null) { 
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}
Copy the code

4.6 Performing DML Operations

Requirement: Add 4 records to student table, primary key is auto growth

Steps:

  1. Creating a connection object
  2. Create a Statement object
  3. Execute SQL statement: executeUpdate(SQL)
  4. Returns the number of affected rows
  5. Release resources

Code:

package com.sqltest;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;


/** * add 4 records to student table, primary key is auto growth */
public class Demo5DML {

    public static void main(String[] args) throws SQLException {
// 1) Create a connection object
        Connection connection = DriverManager.getConnection("jdbc:mysql:///day24"."root"."root");
// 2) Create a Statement object
        Statement statement = connection.createStatement();
// 3) Execute SQL statement:
		executeUpdate(sql) ;
		int count = 0;
// 4) Return the number of affected rows
        count += statement.executeUpdate(Insert into sc values(null, 1, '1993-03-24 ')");
        count += statement.executeUpdate(Insert into sc values(null, 0, '1995-03-24 ')");
        count += statement.executeUpdate(Insert into sc values(null, 1, '1903-03-24 ')");
        count += statement.executeUpdate(Insert into sc values(null, 'chang ', 0,' 1993-03-11 ')");
        System.out.println("Inserted." + count + "A record");
// 5) Release resourcesstatement.close(); connection.close(); }}Copy the code

4.7 Performing DQL Operations

4.7.1 the ResultSet interface:

Function: encapsulate the result set of database query, traverse the result set, take out each record.

Methods in the interface:

A method in the ResultSet interface describe
boolean next() 1) Move the cursor down 1 row

2) Return Boolean, true if there is a next record, false otherwise
The data typegetXxx() 1) By field name, the parameter is of type String. Returns different types

2) Pass the column number, the argument is an integer, starting at 1. Returns different types

4.7.2 Common Data Type Conversion table

SQL type Jdbc corresponding methods The return type
BIT(1) bit(n) getBoolean() boolean
TINYINT getByte() byte
SMALLINT getShort() short
INT getInt() int
BIGINT getLong() long
CHAR,VARCHAR getString() String
Text(Clob) Blob getClob()``getBlob() Clob Blob
DATE getDate() Java.sql.Date represents only dates
TIME getTime() Java.sql.Time indicates only the Time
TIMESTAMP getTimestamp() Java.sql.Timestamp has both date and time

Date, Time, Timestamp, java.util.Date, java.util

4.7.3 Requirements: Ensure that there are more than three records in the database, query all student information

Steps:

  1. Get the connection object
  2. Get the statement object
  3. Execute the SQL statement to obtain a ResultSet object
  4. Loop through to retrieve each record
  5. Output console
  6. Releasing resources results:

Code:

package com.sqltest;

import java.sql.*;

/** * query all student information */
public class Demo6DQL {

    public static void main(String[] args) throws SQLException {
//1) Get the connection object
	Connection connection =DriverManager.getConnection("jdbc:mysql://localhost:3306/day24"."root"."root");
//2) get the statement object
        Statement statement = connection.createStatement();
//3) Execute the SQL statement to obtain the ResultSet object
        ResultSet rs = statement.executeQuery("select * from student");
//4) Loop through to fetch each record
        while(rs.next()) {
            int id = rs.getInt("id");
            String name = rs.getString("name"); boolean gender = rs.getBoolean("gender"); Date birthday = rs.getDate("birthday");
    //5) Output console
            System.out.println("No. :" + id + Name: + name + ", gender: + gender + ", birthday:" + birthday);
        }
	    //6) Release resourcesrs.close(); statement.close(); connection.close(); }}Copy the code

4.7.4 Precautions for the ResultSet Interface:

  1. If the cursor is Before the first row, use rs.getxx () to get the column value: Before start of result set
  2. If the cursor is After the last line, use rs.getxx () to get the column value: After end of result set
  3. == Close the ResultSet, Statement, and Connection==

5. Database tool class JdbcUtils

When do you create your own utility classes?

If a feature is used frequently, we recommend making it a utility class that can be reused in different places.

5.1 requirements:

There is a lot of duplicate code in the code written above, which can be extracted from the common code.

5.2 Creating class JdbcUtil contains three methods:

  1. Several strings can be defined as constants: username, password, URL, driver class
  2. Get a connection to the database:getConnection()
  3. Close all open resources:

Close (Connection conn, Statement STMT), close(Connection conn, Statement STMT, ResultSet RS)

JdbcUtil. Java code:


package com.sqltest.utils; 
import java.sql.*;
/** * Tools to access the database */
public class JdbcUtils {

    // Several strings can be defined as constants: username, password, URL, driver class
    private static final String USER = "root"; 
    private static final String PWD = "root";
    private static final String URL = "jdbc:mysql://localhost:3306/day24";
    private static final String DRIVER= "com.mysql.jdbc.Driver";

    /** * Register driver */
    static {
        try {
            Class.forName(DRIVER);
        } catch(ClassNotFoundException e) { e.printStackTrace(); }}/** * get the database connection */
    public static Connection getConnection(a) throws SQLException {
        return DriverManager.getConnection(URL,USER,PWD);
    }

    /** * Close all open resources */
    public static void  close(Connection conn, Statement stmt) {
        if(stmt! =null) {
            try {
                stmt.close();
            } catch(SQLException e) { e.printStackTrace(); }}if(conn! =null) {
            try {
                conn.close();
            } catch(SQLException e) { e.printStackTrace(); }}}/** * Close all open resources */
    public static void close(Connection conn, Statement stmt, ResultSet rs) {
        if(rs! =null) {
            try {
                rs.close();
            } catch(SQLException e) { e.printStackTrace(); } } close(conn, stmt); }}Copy the code

5.3 Case: User Login

5.3.1 requirements:

  1. There is a list of users

  2. Add a few user records

    create table user (
id int primary key auto_increment, 
name varchar(20),
password varchar(20))insert into user values (null.'jack'.'123'), (null.'rose'.'456');

-- Login, SQL is case insensitive
select * from user where name='JACK' and password='123';

-- Login failed
select * from user where name='JACK' and password='333';
Copy the code

  3. The Statement string is used to log in to the system. The user enters the user name and password on the console.

5.3.2 steps:

  1. Get the user name and password entered from the console to query the database
  2. Write a login method

A. Use the tool class to get connections b. Create statement objects and generate SQL statements using concatenated strings C. Query the database. If there are records, the login is successful. Otherwise, the login fails

5.3.3 code


package com.sqltest;

import com.itheima.utils.JdbcUtils; 
import javax.xml.transform.Result;
import java.sql.Connection; 
import java.sql.ResultSet; 
import java.sql.SQLException; 
import java.sql.Statement; 
import java.util.Scanner;

public class Demo7Login {

    // The user name and password entered from the console
    public static void main(String[] args) {
        Scanner sc = new Scanner(System.in);
        System.out.println("Please enter user name:");
        String name = sc.nextLine();
        System.out.println("Please enter your password:");
        String password = sc.nextLine();
        login(name, password);

    }

    /** * The login method */
    public static void login(String name, String password) {
        //a) get the connection through the utility class
        Connection connection = null;
        Statement statement = null;
        ResultSet rs = null;
        try {
            connection = JdbcUtils.getConnection();
            //b) create statement object, use concatenation string to generate SQL statement
            statement = connection.createStatement();
            //c) Query the database. If there are records, the login is successful; otherwise, the login fails
            String sql = "select * from user where name='" + name + "' and password='" + password + "'";
            System.out.println(sql);
            rs = statement.executeQuery(sql);

            if (rs.next()) {
                System.out.println("Login successful, welcome to:" + name);
            }
            else {
                System.out.println("Login failed"); }}catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //d) Release resourcesJdbcUtils.close(connection, statement, rs); }}}Copy the code