Introduction to JDBC

JDBC Detailed 

1. What is JDBC?

JDBC (Java database Connection) is a Java connection technology, and the JDBC API is a Java API that can access any type of table column data, especially data stored in a relational database.
The APIs contained in the JDBC library are typically used with databases for the following:

• Connecting to a database
• Creating SQL statements
• Executing SQL queries in the database
• View and modify data records in a database

2. Database driver and Common interface

The JDBC API uses a database-driven connection operation database, which is actually the JDBC interface implementation of the database vendor, which is a jar file of the implementation class for interfaces such as connection.
I am using MySQL database, so I use the database driver package Mysql-connector-java-5.1.40-bin.jar provided by MySQL.
The driver package generally provides many interfaces for operating the database, commonly used are:

• Driver interface: In the program to connect the database, you must first load a specific vendor's database driver, different databases have different loading methods, MySQL driver loading method is: Class.forName ("Com.mysql.jdbc.Driver");
• Connection Interface: Connection a connection to a particular database (session), executes an SQL statement in the connection context, and returns the result. The Drivermanager.getconnection (URL, user, password) method can be used to obtain the appropriate database connection. MySQL database connection Get method is: Connection conn = drivermanager.getconnection ("Jdbc:mysql://host:port/database", "User", "password" )。 The interface contains several common methods:  
  1, Createstatement (): Creates statement objects that send SQL statements to the database;  
  2, PreparedStatement (SQL) : Create a Preparedsatement object that sends precompiled SQL to the database,  
  3, Setautocommit (Boolean): Sets whether the database transaction is automatically committed,  
  4, commit (): commits the transaction;  
  5, Rollback (): Transaction rollback.
• Statement Interface: An object used to execute a static SQL statement and return the results it produces. There are generally three kinds of Statement objects:  
  1, Statement: Created by createstatement for sending simple SQL statements (without parameters);  
  2, PreparedStatement : Inherited from the statement interface, created by PreparedStatement, for sending SQL statements with one or more parameters. PreparedStatement objects are more efficient than statement objects and can prevent SQL injection, so we generally use preparedstatement; 
  3, CallableStatement: Inherits from the PreparedStatement interface, created by method Preparecall, to invoke the stored procedure.  
  The following are some common statement methods:  
  1, execute (String sql): Run the statement, return the result set,  
  2, ExecuteQuery (String sql) : Runs the SELECT statement, returns the resultset result set,  
  3, executeupdate (String SQL): Runs the insert/update/delete operation, returns the updated number of rows,  
  4. Addbatch (String sql): puts multiple SQL statements into one batch,  
  5, ExecuteBatch (): Sends a batch of SQL statement execution to the database.
• ResultSet interface: ResultSet provides methods to retrieve different types of fields, commonly used are:
  1, getString (int index), getString (String columnName): To obtain in the database is a varchar, char and other types of data objects;
  2, getfloat (int index), getfloat (String columnName): Obtains data object which is float type in the database;
  3, getDate (int index), getDate (String columnName): Obtains data of date type in the database;
  4, Getboolean (int index), Getboolean (String columnName): Obtains data in the database that is a Boolean type;
  5, GetObject (int index), getObject (String columnName): Gets any type of data in the database.
  Compared to the above methods, the ResultSet interface provides a more common approach to result set processing:
  1, Next (): Move to the next line;
  2, Previous (): Move to the previous line;
  3, Absolute (int row): Move to the specified line;
  4, Beforefirst (): Mobile resultset the front;
  5, Afterlast (): Move to the last face of resultset.

3. JDBC Use steps

The JDBC Standard process is: Load the JDBC driver → establish a database connection connection→ create a statement that executes SQL statement→ process The execution result resultset→ release the resource, The order in which the resources are freed is: resultset→statement→connection.
Here is one of my JDBC applet, as a basic example:
First create a database table to use:

create database db_test;use db_test; create table person (id int primary key, name varchar (20), Job varchar (20));  insert into person values (1001, ' Zhangsan ',  ' teacher ');     

Create the corresponding Java project and add MySQL to Classpath, I am here because of the use of spring-boot, so it is convenient not to manually add. Create the person class and the corresponding test class, you can write a main method to facilitate testing:

PublicClass Person {Private Integer ID;private String name;Private String teacher;public void setName (String name) { Span class= "Hljs-keyword" >this.name = name; } public void setId (Integer ID) { Span class= "Hljs-keyword" >this.id = ID; } public void setTeacher (String Teacher) {this.teacher = Teacher;} public String getname () {return Name } public Integer getid () {return ID;} public String getteacher () {return Teacher }} 

Import java.sql.*;PublicClasspersontest {PublicStaticvoidMain (string[] arrgs) {person P1 =New Person (); P1.setid (1002); P1.setname ("Lisi"); P1.setjob ("Student"); person P2 =New Person (); P2.setid (1001); P2.setname ("Wangwu"); P2.setjob ("Teacher"); GetAll (); Update (p2); GetAll (); Insert (p1); GetAll (); }/** * Get Database connection method *@return */PrivateStatic ConnectionGetconn () {String Driver ="Com.mysql.jdbc.Driver";A string that follows indicates that the SSL protocol is closed string url ="Jdbc:mysql://192.168.1.110:3306/db_test?useunicode=true&characterencoding=utf-8&usessl=false"; String username ="Root"; String Password ="123456"; Connection Connection =Nulltry {Load drive Class.forName (driver);Get database Connection connection = (connection) drivermanager.getconnection (URL, username, password); }catch (SQLException e) {e.printstacktrace ();}catch (ClassNotFoundException e) {e.printstacktrace ();}return connection; }/** * Insert Method *@param person *@return */PrivateStaticIntInsert (person person) {Connection Connection = Getconn ();int i =0; String sql =Insert into person (id,name,job) values (?,?,?); PreparedStatement pstmt;try {Gets the PreparedStatement object from the connection pstmt = (preparedstatement) connection.preparestatement (SQL);Assemble SQL Pstmt.setint (1, Person.getid ()); Pstmt.setstring (2, Person.getname ()); Pstmt.setstring (3, Person.getjob ());Execute SQL i = Pstmt.executeupdate ();Close connection pstmt.close (); Connection.close (); }catch (SQLException e) {e.printstacktrace ();}return i; }/** * Update method *@param person *@return */PrivateStaticIntUpdate (person person) {Connection conn = Getconn ();int i =0; String sql ="Update person set name= '" + person.getname () +"' Where id= '" + person.getid () +"‘"; PreparedStatement pstmt;try {pstmt = (preparedstatement) conn.preparestatement (sql); i = Pstmt.executeupdate (); System.out.println ("Resutl:" + i); Pstmt.close (); Conn.close (); }catch (SQLException e) {e.printstacktrace ();}return i; }PrivateStatic IntegerGetAll () {Connection conn = Getconn (); String sql ="SELECT * from person"; PreparedStatement pstmt;try {pstmt = (preparedstatement) conn.preparestatement (SQL); ResultSet rs = Pstmt.executequery ();int col = Rs.getmetadata (). getColumnCount (); System.out.println ("============================");while (Rs.next ()) {for (int i =1; I <= col; i++) {System.out.print (rs.getstring (i) +"\ t");if ((i = =2) && (rs.getstring (i). Length () <8)) {System.out.print ("\ t"); }} System.out.println (""); } System.out.println ("============================"); }catch (SQLException e) {e.printstacktrace ();} return null;} private static int Delete (Integer ID) {Connection conn = Getconn (); int i = 0; String sql = "Delete from the person where id= '" + ID + "'"; PreparedStatement pstmt; try {pstmt = (preparedstatement) conn.preparestatement (sql); i = Pstmt.executeupdate (); System.out.println ("resutl:" + i); Pstmt.close (); Conn.close (); } catch (SQLException e) {e.printstacktrace ();} return i;}}              

The final results are as follows:

Introduction to JDBC