JDBC Study ( One )—JDBC The concept of /JDBC Related classes / How to use JDBC

01 JDBC Concept

JDBC：Java Data Base Connectivity,Java Database connection

SUN The company is trying to simplify 、 Unified operation of database , Defined a set Java Specification of operating database （ Interface ）, be called JDBC.

The application program must interact with the database through the corresponding database driver .

therefore , Developers want to manipulate data , Need to pass through jdbc Load specific drivers .

02 JDBC Related classes

• DriverManager class

  DriverManager For loading the driver , And create a link to the database .

  Common methods ：

  • DriverManager.registerDriver(new Driver()); It is not recommended to use registerDriver Method registration driver . This will cause the driver to register twice , There will be two in memory Driver object ; Procedure depends on mysql Of api, Out of the mysql Of jar package , The program will not compile , In the future, it will be very troublesome for the program to switch the underlying database . Recommend ways ：Class.forName(“com.mysql.jdbc.Driver”); In this way, the driver object will not appear repeatedly in memory , And in this way , The program just needs a string , No need to rely on specific drivers , Make the program more flexible .
  • DriverManager.getConnection(url, user, password); Get database connection
    • Be careful url The writing format of ： agreement : Sub protocol : // host : port + database + Parameters
    • Common databases URL How to write the address ：
      • Oracle How to write it ：jdbc:oracle:thin:@localhost:1521: Data table name
      • SqlServer How to write it ：jdbc:microsoft:sqlserver://localhost:1433; DatabaseName= Data table name
      • MySql How to write it ：jdbc:mysql://localhost:3306/ Data table name , If the connection is local Mysql database , And the port used for the connection is 3306, So url The address can be abbreviated as ： jdbc:mysql:/// database

• Connection class

  The link used to represent the database , All the interaction between the client and the database is through connection Object completed .

  Be careful ：Connection object , It’s a very rare resource , Must be released immediately after use , If Connection Not in time 、 Shut down properly , It is very easy to cause system downtime

  Common methods ：

  createStatement()： Create send to database sql Of statement object .
  prepareStatement(sql) ： Create send precompile to database sql Of PrepareSatement object .
  prepareCall(sql)： Create the... That executes the stored procedure callableStatement object .
  setAutoCommit(boolean autoCommit)： Set whether transactions are automatically committed .
  commit() ： Commit a transaction on a link .
  rollback() ： Roll back transactions on this link .
  

• Statement class

  Used to send… To a database SQL sentence

  Be careful ： The object needs to free resources

  Common methods ：

  executeQuery(String sql) ： Used to send query statements to data .
  executeUpdate(String sql)： Used to send... To a database insert、update or delete sentence
  execute(String sql)： Used to send arbitrary... To the database sql sentence
  addBatch(String sql) ： Put more than one sql Statement in a batch .
  executeBatch()： Send a batch of sql Statement execution .
  

• ResultSet class

  For encapsulation Sql Statement execution result .ResultSet Object maintains a cursor pointing to a table data row , In the beginning , The cursor is first
  Before line , call ResultSet.next() Method , You can make the cursor point to a specific row of data , Call the method to get the line
  The data of . It can be understood as an iterator .

  Be careful ： The object needs to free resources

  Common methods ：

  // Rolling method ：
  next()： Move to next line
  Previous()： Move to previous line
  absolute(int row)： Move to specified row
  beforeFirst()： Move resultSet Foremost .
  afterLast() ： Move to resultSet At the back of .
  // Can pass get Get any type of data , Such as :
  getObject(int index)
  getObject(string columnName)
  getString(int index)
  getString(String columnName)
  

  Example ：

   while (resultSet.next()){
  System.out.println(resultSet.getObject("columnName1"));
  System.out.println(resultSet.getObject("columnName2"));
  System.out.println(resultSet.getObject("columnName3"));
  System.out.println(resultSet.getObject("columnName4"));
  }
  

03 How to use JDBC

• Preparation steps

  • Download driver ： By downloading the address https://mvnrepository.com/artifact/mysql/mysql-connector-java download jar package

  • guide jar package , You can refer to the article https://blog.csdn.net/yinyanyao1747/article/details/90751024 The detailed steps

  • Set up an experimental environment

    • stay mysql Create a library in , And create a data table , insert data . Here’s an example of a database ：

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

• Use JDBC

package com.hooi.demo1;
import java.sql.*;
public class JDBCTest {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//step1： The load driver
Class.forName("com.mysql.jdbc.Driver");
//step2: Get the connection
String username ="root";
String passwd = "123456";
String url ="jdbc:mysql://localhost:3306/jdbcstudy";
Connection connection = DriverManager.getConnection(url, username, passwd);
//step3: Get to send... To the database sql Of the statement statement object
Statement statement = connection.createStatement();
//step4： use string packing sql sentence
String sql ="select id,name,password,email FROM users";
//step5： perform sql sentence
ResultSet resultSet = statement.executeQuery(sql);
//step6： Get execution results
while (resultSet.next()){
System.out.println(resultSet.getObject("id"));
System.out.println(resultSet.getObject("name"));
System.out.println(resultSet.getObject("password"));
System.out.println(resultSet.getObject("email"));
}
//step7： Release resources , Close the connection
resultSet.close();
statement.close();
connection.close();
}
}