Preface

JDBC is a set of interface s developed by SUN Company

1. Overview of JDBC

1. What is JDBC?

Java DataBase Connectivity (Java Language Connectivity Database)

2. What is the nature of JDBC?

JDBC is a set of interface s developed by SUN Company
java.sql. *; (There are many interfaces under this package.)

3. Why Interface-oriented programming?

Decoupling: Decoupling the program and increasing its expansion force.

4. Why does SUN develop a set of JDBC interfaces?

	Because the underlying implementation of each database works differently.
	Oracle Databases have their own principles.
	MySQL Databases also have their own principles.
	MS SqlServer Databases also have their own principles.
	....
	Each database product has its own unique implementation principle.

5. Preparations for JDBC Development

Download the corresponding driver jar package from the official website and configure it into the environment variable classpath.

Configuration is developed for a text editor and does not require the above environment variables to be configured when using IDEA tools. IDEA has its own configuration.

2. Six steps in JDBC programming

Step 1: Register Drivers (Role: Tell) Java Program, which brand of database to connect to)

Step 2: Get the connection JVM The channel between the process and the database process is open, which belongs to the communication between processes. Heavy, you must close the channel after use.)

Step 3: Get the database operation object (executed exclusively) sql Object of statement)

Step 4: Execution SQL Statement ( DQL DML....)

Step 5: Processing the query result set (only if step 4 is select This fifth step deals with the result set of the query.)

Step 6: Release the resource (you must close the resource after you have used it). Java It belongs to inter-process communication with the database and must be closed after opening.)

3. Specific implementation of programming

1. Registration Driver

static void registerDriver(Driver driver)

Register the given driver with DriverManager.

/*1.Register Driver*/
Driver d = new Driver();
DriverManager.registerDriver(d);

2. Get Connections

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

Connect to a given database

/*2.Get Connections*/
String url = "jdbc:mysql://localhost :3306/bjpowernode";
String user = "root";
String password = "52741";
Connection conn = DriverManager.getConnection(url,user,password);

• Url: Uniform Resource Locator (absolute path to a resource on the network)
  URL s include: protocol, IP, port number, resource name

• jdbc:mysql://localhost:3306/bjpowernode
  jdbc:mysql://protocol
  Localhost native IP address
  Port number of 3306 MySQL database
  bjpowernode specific database instance name

• What is a communication protocol?

  A communication protocol is a data transfer format that is pre-defined before communication. The format of the data package is determined in advance.

3. Get the database operation object

Statement createStatement()
Create a Statement object to send SQL statements to the database.

/*Get the database operation object*/
Statement statement = conn.createStatement();

4. Execute sql

• int executeUpdate(String sql) (DML statement, insert increase, delete delete, update change)

• Execute the given SQL statement, which may be an INSERT, UPDATE, or DELETE statement, or an SQL statement that does not return anything, such as a SQL DDL statement.

/*Execute Insert Statement*/
String sql1 = "insert into dept(deptno,dname,loc) values (50,'Ministry of Personnel','Beijing')";
int i = statement.executeUpdate(sql1);
System.out.println(i == 1? "Insert Successful" : "Insert failed");

/*Execute modify statement*/
String sql1 = "update dept set dname = 'Sales Department',LOC = 'Tianjin' where deptno = 40";
int i = statement.executeUpdate(sql1);
System.out.println(i == 1? "Successful modification" : "Modification failed");

/*Execute delete statement*/
String sql1 = "delete from dept where deptno = 50";
int i = statement.executeUpdate(sql1);
System.out.println(i == 1? "Delete succeeded" : "Delete failed");

ResultSet executeQuery(String sql) (DQL statement, query select)

Executes the given SQL statement that returns a single ResultSet object.

/*Execute Query Statement*/
String sql = "select empno,ename,sal from emp";
resultSet= statement.executeQuery(sql);

5. Processing query result set

boolean next() moves the cursor one line forward from its current position.
String getString(int columnIndex) returns the queried value as a String type

It can operate directly:
String getInt(int columnIndex) returns the queried value in int type
String getDouble(int columnIndex) returns the queried value as a double type

If the parameter is a field name, you need to enter the renamed name of the field
For example: select empno, ename as name, Sal from EMP
The parameter passed in should be resultSet.getString("name"); It should not be a resultSet.getString("ename");

while (resultSet.next()) {
    String empno = resultSet.getString("empno");
    String ename = resultSet.getString("ename");
    String sal = resultSet.getString("sal");
    System.out.println(empno + " , " + ename +" , " + sal);
}

6. Release Resources

To ensure that the resource is released, close the resource in the finally statement block.

And follow from small to large closures to catch anomalies separately.

if (statement != null) {
    try {
        /*Release Resources*/
        statement.close();
    } catch (SQLException throwables) {
        throwables.printStackTrace();
    }
}
if (conn != null ) {
    try {
        /*Release Resources*/
        conn.close();
    } catch (SQLException throwables) {
        throwables.printStackTrace();
    }
}

7. Complete process

Connection connection = null;
Statement statement = null;
try {

    /*Read Property File*/
    ResourceBundle bundle = ResourceBundle.getBundle("jdbc");
    
    /*Register Driver*/
    Class.forName(bundle.getString("driver"));

    /*Set up a connection*/
    String url = bundle.getString("url");
    String user = bundle.getString("user");
    String password = bundle.getString("password");
    connection = DriverManager.getConnection(url,user,password);

    /*Get the database operation object*/
    statement = connection.createStatement();

    /*Execute sql*/
    String sql = "";
    int i = statement.executeUpdate(sql);
    System.out.println(i == 1 ? "Operation Successful" : "operation failed");