A brief introduction to JDBC in Java

What is JDBC? This article gives you a detailed introduction to the application programming interface in the Java language that is used to standardize how client programs access the database. It has certain reference value. Interested friends can refer to the preface: What is JDBC

Introduction to Wikipedia:

Java Database Connectivity (JDBC for short) is used in the Java language to standardize how client programs come to The application programming interface that accesses the database provides methods such as querying and updating data in the database. JDBC is also a trademark of Sun Microsystems. JDBC is for relational databases.

Simply put, it is a type of Java API used to execute SQL statements. Through JDBC, we can directly use Java programming to operate relational databases. Through encapsulation, developers can use pure Java API to complete SQL execution.

1. Preparation (1): MySQL installation configuration and basic learning

The picture below is the database I will use for demonstration next surface.

2. Preparation (2): Download the jar package corresponding to the database and import it

Use JDBC needs to import the corresponding jar package in the project. Import method under Eclipse:

Right-click on the project icon, select "Properties", select "Add External JARs..." in "Java Bulid Path", and select the jar package obtained after downloading and unzipping.

           

If you operate MySQL, the following import will not report an error:

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;

In addition, you also need the JDBC package, which can be imported directly.

import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

3. Basic JDBC operations

For the sake of simplicity, database-related operations and commands , parameters are all hard-coded. Interested readers can explore these and reduce the coupling between data and operations.

Let’s first look at the specific code and practice it. The fifth part of this article does a little research on the API used.

All the following methods and data members are inside the public class JDBCOperation.

(1) Define the record class (optional)
This is mainly done to facilitate operation and interface Definition is optional.

static class Student {
  private String Id;
  private String Name;
  private String Sex;
  private String Age;

  Student(String Name, String Sex, String Age) {
   this.Id = null; //default
   this.Name = Name;
   this.Sex = Sex;
   this.Age = Age;
  }

  public String getId() {
   return Id;
  }

  public void setId(String Id) {
   this.Id = Id;
  }

  public String getName() {
   return Name;
  }

  public void setName(String Name) {
   this.Name = Name;
  }

  public String getSex() {
   return Sex;
  }

  public void setSex(String Sex) {
   this.Sex = Sex;
  }

  public String getAge() {
   return Age;
  }

  public void setage(String Age) {
   this.Age = Age;
  }
}

(2) Obtaining the connection
must be obtained before operating Database connection.

private static Connection getConn() {
 String driver = "com.mysql.jdbc.Driver";
 String url = "jdbc:mysql://localhost:3306/samp_db";
 String username = "root";
 String password = "";
 Connection conn = null;
 try {
  Class.forName(driver); //classLoader,加载对应驱动
  conn = (Connection) DriverManager.getConnection(url, username, password);
 } catch (ClassNotFoundException e) {
  e.printStackTrace();
 } catch (SQLException e) {
  e.printStackTrace();
 }
 return conn;
}

(3)insert

##

private static int insert(Student student) {
 Connection conn = getConn();
 int i = 0;
 String sql = "insert into students (Name,Sex,Age) values(?,?,?)";
 PreparedStatement pstmt;
 try {
  pstmt = (PreparedStatement) conn.prepareStatement(sql);
  pstmt.setString(1, student.getName());
  pstmt.setString(2, student.getSex());
  pstmt.setString(3, student.getAge());
  i = pstmt.executeUpdate();
  pstmt.close();
  conn.close();
 } catch (SQLException e) {
  e.printStackTrace();
 }
 return i;
}

(4)update

##

private static int update(Student student) {
 Connection conn = getConn();
 int i = 0;
 String sql = "update students set Age='" + student.getAge() + "' where Name='" + student.getName() + "'";
 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;
}

(5)select

Take select * from XXX as an example.

private static Integer getAll() {
 Connection conn = getConn();
 String sql = "select * from students";
 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;
}

(6)delete

private static int delete(String name) {
 Connection conn = getConn();
 int i = 0;
 String sql = "delete from students where Name='" + name + "'";
 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;
}

4. Test

Before testing, you need to open the service of the corresponding database in the system. The startup command of MySQL under Windows is
net start mysql

Test code

public static void main(String args[]) {
 JDBCOperation.getAll();
 JDBCOperation.insert(new Student("Achilles", "Male", "14"));
 JDBCOperation.getAll();
 JDBCOperation.update(new Student("Bean", "", "7"));
 JDBCOperation.delete("Achilles");
 JDBCOperation.getAll();
}

Output in Eclipse

============================
1    Ender        male    8   
2    Bean        male    6   
3    Petra        fema    9   
4    Peter        male    9   
5    _Graff        male    40   
6    GOD        fema    255   
============================
============================
1    Ender        male    8   
2    Bean        male    6   
3    Petra        fema    9   
4    Peter        male    9   
5    _Graff        male    40   
6    GOD        fema    255   
7    Achilles    Male    14   
============================
resutl: 1
resutl: 1
============================
1    Ender        male    8   
2    Bean        male    7   
3    Petra        fema    9   
4    Peter        male    9   
5    _Graff        male    40   
6    GOD        fema    255   
============================

五、代码分析

在上述对数据库进行增删改查的过程中，可以发现其共性部分，即通用的流程：

　　(1)创建Connection对象、SQL查询命令字符串；
　　(2)对Connection对象传入SQL查询命令，获得PreparedStatement对象；
　　(3)对PreparedStatement对象执行executeUpdate()或executeQurey()获得结果；
　　(4)先后关闭PreparedStatement对象和Connection对象。

可见，使用JDBC时，最常打交道的是Connection、PreparedStatement这两个类，以及select中的ResultSet类。查阅Java API手册可以了解其具体的意义和方法。

Wrapper

java.sql 

接口 Connection
所有超级接口：
Wrapper

public interface Connectionextends Wrapper 

与特定数据库的连接（会话）。在连接上下文中执行 SQL 语句并返回结果。
Connection 对象的数据库能够提供描述其表、所支持的 SQL 语法、存储过程、此连接功能等等的信息。此信息是使用 getMetaData 方法获得的。

PreparedStatemnt

java.sql 
接口 PreparedStatement
所有超级接口：

Statement, Wrapper

所有已知子接口：

CallableStatement

public interface PreparedStatementextends Statement
表示预编译的 SQL 语句的对象。
SQL 语句被预编译并存储在 PreparedStatement 对象中。然后可以使用此对象多次高效地执行该语句。 

常用方法

boolean execute()
Execute the SQL statement in this PreparedStatement object. The statement can be any kind of SQL statement.
ResultSet executeQuery()
Execute the SQL query in this PreparedStatement object and return the ResultSet object generated by the query.
int executeUpdate()
Execute the SQL statement in this PreparedStatement object. The statement must be a SQL Data Manipulation Language (DML) statement, such as an INSERT, UPDATE or DELETE statement. ; Or a SQL statement without return content, such as a DDL statement.

ResultSet

java.sql
Interface ResultSet
All super interfaces:
Wrapper
All known sub-interfaces Interface:
CachedRowSet, FilteredRowSet, JdbcRowSet, JoinRowSet, RowSet, SyncResolver, WebRowSet

##public interface ResultSetextends Wrapper

represents The data table of the database result set is usually generated by executing a statement to query the database.

6. Questions to think about

Every SQL operation needs to establish and close a connection, which will inevitably consume a lot of resource overhead. How to avoid it? ?

Analysis: You can use a connection pool to maintain the connection uniformly without having to establish and close it every time. In fact, this is used by many tools that encapsulate JDBC.

The above is the detailed content of A brief introduction to JDBC in Java. For more information, please follow other related articles on the PHP Chinese website!