The article directories

• JDBC introduction
• The JDBC framework
• Commonly used the JDBC API
• JDBC Usage Procedure
• JDBC Connecting to the database
• Prepare test data
• Connecting to a Database
• JDBC insert data
• JDBC update data
• JDBC query data
• JDBC condition (ID) Query data
• JDBC delete data
• JDBC tools

JDBC introduction

• JDBC (Java DataBase Connectivity) is a Java API for executing SQL statements. It provides unified access to multiple relational databases. It consists of a set of classes and interfaces written in the Java language. JDBC provides a benchmark against which more advanced tools and interfaces can be built to enable database developers to write database applications, and it is also a brand name.

The JDBC framework

Commonly used the JDBC API

• The DriverManager class manages the list of database drivers. Determines whether the content matches the connection request of the correct database driver from the communication subprotocol used by the Java application. The first driver that identifies JDBC in a certain subprotocol will be used to establish a database connection.
• Driver This interface handles communication with the database server. Driver objects are rarely used directly. Instead, objects in DriverManager are used to manage objects of this type. It also abstracts the details related to the work of the driver object
• Connection All methods for this interface to access the database. The connection object represents the communication context, that is, all communication with the database is through this unique connection object.
• Statements can be submitted to the database using SQL statements of objects created by this interface. Some derived interfaces accept arguments in addition to executing stored procedures.
• ResultSet These objects are saved from the database after executing SQL queries using the Statement object to retrieve data. It acts as an iterator that can be moved to retrieve the next piece of data.
• SQLException this class is used to handle any errors that occur in a database application.

JDBC Usage Procedure

JDBC Connecting to the database

Before performing the following examples, make sure you are ready to:

• Has database administrator rights to create a database in a given schema. To execute the following example, replace this with the actual user name and passwordUsername and password.
• The MySQL or database is up and running.

Prepare test data

CREATE DATABASE 01jdbc;
Copy the code

CREATE TABLE `user` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(255) DEFAULT NULL,
  `user_password` varchar(255) DEFAULT NULL.PRIMARY KEY (`user_id`)
);
Copy the code

Connecting to a Database

import java.sql.Connection;
import java.sql.DriverManager;

public class CreateData {

	private static String url = "jdbc:mysql://localhost:3306/01jdbc"; // Database address
	private static String userName = "root"; // Database user name
	private static String passWord = "123456"; // Database password
	private static Connection conn = null;

	// Get the database connection
	public static Connection getConnection(a) {
		if (null == conn) {
			try {
				Class.forName("com.mysql.jdbc.Driver"); // Load the driver
				conn = DriverManager.getConnection(url, userName, passWord); // Create a database connection
			} catch (Exception e) {
				e.printStackTrace();
				System.out.println("JDBC connection to database failed"); }}return conn;
	}

	public static void main(String[] args) {
		System.out.println("JDBC connection to database successful");
		System.out.println("JDBC connection address:"+ getConnection()); }}Copy the code

• If the connection is successful, the memory address of the obtained connection is returned.

A JDBC connection database JDBC connection address for success: com. Mysql. JDBC. JDBC4Connection@67424e82
Copy the code

JDBC insert data

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

public class InsertData {
	
	private static String url = "jdbc:mysql://localhost:3306/01jdbc"; //Database address privatestatic String userName = "root"; //Database user name privatestatic String passWord = "123456"; //Database password privatestatic Connection conn = null;

	public static Connection getConnection() {
		if (null == conn) {
			try {
				//Class. ForName (" com.mysql.jdbc.driver ");//Create a database connection for CONN=DriverManager.getConnection(url, userName, passWord); } catch (Exception e) { e.printStackTrace(); }}return conn;
	}

	public static void main(String[] args) {
		//To preparesql
		String sql ="INSERT INTO user(user_name, user_password) VALUES (' xixin1 ', '123456')"; Connection conn= getConnection();
		PreparedStatement pst = null;
		try {
			//Create the precompiled statement PST= conn.prepareStatement(sql);
			//performSQLpst.executeUpdate(); System.out.println("JDBC inserted successfully ");//Pst.close (); conn.close(); } catch (SQLExceptione) { e.printStackTrace(); System.out.println("JDBC insert failed "); }}}Copy the code

JDBC Inserted successfullyCopy the code

JDBC update data

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

public class UpdateData {
	
	private static String url = "jdbc:mysql://localhost:3306/01jdbc"; //Database address privatestatic String userName = "root"; //Database user name privatestatic String passWord = "123456"; //Database password privatestatic Connection conn = null;

	public static Connection getConnection() {
		if (null == conn) {
			try {
				//Class. ForName (" com.mysql.jdbc.driver ");//Create a database connection for CONN=DriverManager.getConnection(url, userName, passWord); } catch (Exception e) { e.printStackTrace(); }}return conn;
	}

	public static void main(String[] args) {
		//To preparesql
		String sql ="Update user set user_name=' xishu2 ', user_password='654321' where user_id=1"; Connection conn= getConnection();
		PreparedStatement pst = null;
		try {
			//Create the precompiled statement PST= conn.prepareStatement(sql);
			//performSQLpst.executeUpdate(); System.out.println("JDBC update succeeded ");//Pst.close (); conn.close(); } catch (SQLExceptione) { e.printStackTrace(); System.out.println("JDBC update failed "); }}}Copy the code

• Running results:

JDBC update successfulCopy the code

• View tables
  
  

JDBC query data

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

public class SelectData {
	
	private static String url = "jdbc:mysql://localhost:3306/01jdbc"; //Database address privatestatic String userName = "root"; //Database user name privatestatic String passWord = "123456"; //Database password privatestatic Connection conn = null;

	public static Connection getConnection() {
		if (null == conn) {
			try {
				//Class. ForName (" com.mysql.jdbc.driver ");//Create a database connection for CONN=DriverManager.getConnection(url, userName, passWord); } catch (Exception e) { e.printStackTrace(); }}return conn;
	}

	public static void main(String[] args) {
		//To preparesql
		String sql = "select * from user";
		Connection conn = getConnection();
		PreparedStatement pst = null;
		try {
			//Create the precompiled statement PST= conn.prepareStatement(sql);
			//performSQL
			ResultSet rst =pst.executeQuery(); while (rst.next()) { System.out.print(rst.getInt("user_id")); System.out.print(rst.getString("user_name")); System.out.println(rst.getString("user_password")); } system.out. println("JDBC query succeeded ");//Pst.close (); conn.close(); } catch (SQLExceptione) { e.printStackTrace(); System.out.println("JDBC query failed "); }}}Copy the code

1Xi moving2 654321
2Xi moving3 123JDBC query successfulCopy the code

JDBC condition (ID) Query data

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

public class SelectByIdData {
	
	private static String url = "jdbc:mysql://localhost:3306/01jdbc"; //Database address privatestatic String userName = "root"; //Database user name privatestatic String passWord = "123456"; //Database password privatestatic Connection conn = null;

	public static Connection getConnection() {
		if (null == conn) {
			try {
				//Class. ForName (" com.mysql.jdbc.driver ");//Create a database connection for CONN=DriverManager.getConnection(url, userName, passWord); } catch (Exception e) { e.printStackTrace(); }}return conn;
	}

	public static void main(String[] args) {
		//To preparesql
		String sql = "select * from user where user_id = 1";
		Connection conn = getConnection();
		PreparedStatement pst = null;
		try {
			//Create the precompiled statement PST= conn.prepareStatement(sql);
			//performSQL
			ResultSet rst =pst.executeQuery(); while (rst.next()) { System.out.print(rst.getInt("user_id")); System.out.print(rst.getString("user_name")); System.out.println(rst.getString("user_password")); } system.out. println("JDBC conditional query succeeded ");//Pst.close (); conn.close(); } catch (SQLExceptione) { e.printStackTrace(); System.out.println("JDBC conditional query failed "); }}}Copy the code

• Running results:

1Xi moving2 654321The JDBC condition query succeededCopy the code

JDBC delete data

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

public class DeleteDate {
	
	private static String url = "jdbc:mysql://localhost:3306/easilyj"; //Database address privatestatic String userName = "root"; //Database user name privatestatic String passWord = "123456"; //Database password privatestatic Connection conn = null;

	public static Connection getConnection() {
		if (null == conn) {
			try {
				//Class. ForName (" com.mysql.jdbc.driver ");//Create a database connection for CONN=DriverManager.getConnection(url, userName, passWord); } catch (Exception e) { e.printStackTrace(); }}return conn;
	}

	public static void main(String[] args) {
		//To preparesql
		String sql = "delete from user where user_id = 2";
		Connection conn = getConnection();
		PreparedStatement pst = null;
		try {
			//Create the precompiled statement PST= conn.prepareStatement(sql);
			//performSQLpst.executeUpdate(); System.out.println("JDBC deleted successfully ");//Pst.close (); conn.close(); } catch (SQLExceptione) { e.printStackTrace(); System.out.println("JDBC deletion failed "); }}}Copy the code

JDBC deleted successfullyCopy the code

JDBC tools

• After the above learning, it will be found that add, delete, change and check have a lot in common, the difference is only the SQL statement and the way to receive the return value is different, so in order to make the program better reuse, we usually extract the same code. The following code demonstrates a new operation using the extracted utility class.
• DBUtil tools

import java.sql.Connection;
import java.sql.DriverManager;

public class DBUtil {

	private static String url = "jdbc:mysql://localhost:3306/01jdbc"; //Database address privatestatic String userName = "root"; //Database user name privatestatic String passWord = "123456"; //Database password privatestatic Connection conn = null;

	public static Connection getConnection() {
		if (null == conn) {
			try {
				//Class. ForName (" com.mysql.jdbc.driver ");//Create a database connection for CONN=DriverManager.getConnection(url, userName, passWord); } catch (Exception e) { e.printStackTrace(); }}return conn;
	}

	public static void main(String[] args) {
		//System.out.println(getConnection())); }}Copy the code

com.mysql.jdbc.JDBC4Connection@67424e82
Copy the code

• Change the new data to the following code:

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import com.xdr.util.DBUtil;

public class InsertData {

	public static void main(String[] args) {
		//To preparesql
		String sql =INSERT INTO user(user_name, user_password) VALUES (' 123456', '123456'); Connection conn= DBUtil.getConnection();
		PreparedStatement pst = null;
		try {
			//Create the precompiled statement PST= conn.prepareStatement(sql);
			//performSQLpst.executeUpdate(); System.out.println("JDBC inserted successfully ");//Pst.close (); conn.close(); } catch (SQLExceptione) { e.printStackTrace(); System.out.println("JDBC insert failed "); }}}Copy the code

• View tables
  
  
• Similarly, add, delete, change and check can be changed in this way, making the code more concise, more logical and reusable.