“This is the second day of my participation in the Gwen Challenge in November. See details: The Last Gwen Challenge in 2021”

The environment

• MySQL version 5.5
• eclipse
• MySQL connection driverMysql connector - Java - 5.1.18 - bin. The jar

Versions prior to mysql8.0 use different JAR packages than later versions, and there are some differences in how they are used. Here, MY MySQL version is 5.5. \color{red} Link to the driver link at the end of the article, you need to download the link to the driver link at the end of the article, you need to download

The preparatory work

• Add the JAR package to the project, right-click the project as shown below, and selectConfigure Build Path...

• Add the jar package you just downloaded

Operation of adding, deleting, checking and modifying

• First, create a database templateDBConfig.java

Note: Create a database in advance and fill in your own database name in JDBC_URL

/* * * like dust */
package test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;


/** * Database connection and shutdown utility class *@author ruochen
 * @version1.0 * /
public class DBConfig {
	/** Define the database driver class name */
	private static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
	
	/** database connection address */
	private static final String JDBC_URL = "JDBC: mysql: / / 127.0.0.1:3306 / JavaDB? characterEncoding=utf8";
	
	/** Database user name */
	private static final String JDBC_USERNAME = "root";
	
	/** Database password */
	private static final String JDBC_PASSWORD = "root";
	
	/** Database connection object */
	private static Connection conn = null;
	
	/** * get the link *@return* /
	public static Connection getConnection(a) {
		try {
			try {
				// Load the driver
				Class.forName(JDBC_DRIVER);
				System.out.println("Loading successful");
			} catch (java.lang.ClassNotFoundException e) {
				System.out.println("ForName: " + e.getMessage());
			}
			conn = DriverManager.getConnection(JDBC_URL, JDBC_USERNAME, JDBC_PASSWORD);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return conn;
	}
	
	public static void closeConnection(ResultSet rs, Statement ps, Connection conn) {
		try {
			if (null! = rs) { rs.close(); }if (null! = ps) { ps.close(); }if (null != conn) {
				conn.close();
			}
		} catch (Exception e) {
			System.out.println("Close connection error"); e.printStackTrace(); }}}Copy the code

• Build table operationCreateTest.java

/* * * like dust */
package test;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/** * JDBC link database, create table *@author ruochen
 * @version1.0 * /
public class CreateTest {
	public static void main(String[] args) {
		Connection conn = DBConfig.getConnection();
		try {
			Statement st = conn.createStatement();
			ResultSet re = conn.getMetaData().getTables(null.null."student".null);
			if (re.next()) {
				System.out.println(Table already exists);
			} else {
			// Construct a predicate sentence
			String sql = "create table student ( "
					+ "id char(10),"
					+ "name char(16),"
					+ "score integer" + ")";
			st.executeUpdate(sql);
			System.out.println("Created successfully");
			}
			DBConfig.closeConnection(re, st, conn);
		} catch (SQLException e) {
			System.out.println("SQLException: "+ e.getMessage()); }}}Copy the code

The loading succeeded. The creation succeededCopy the code

• The insertInsertTest.java

/* * * like dust */
package test;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;

/** * Insert data into table *@author ruochen
 * @version1.0 * /
public class InsertTest {

	public static void main(String[] args) {
		Connection conn = DBConfig.getConnection();
		try {
			Statement st = conn.createStatement();
			String r1 = "Insert into sc values('0001', 80)";
			String r2 = "Insert into sc values('0002', '0002')";
			String r3 = "Insert into sc values('0003', '0003', 90)";
			
			st.executeUpdate(r1);
			st.executeUpdate(r2);
			st.executeUpdate(r3);
			System.out.println("Insert successful");
			
			DBConfig.closeConnection(null, st, conn);
		} catch(SQLException e) { e.printStackTrace(); }}}Copy the code

The loading succeeded. The insertion succeededCopy the code

• The update operationUpdateTest.java

/* * * like dust */
package test;

import java.sql.Connection;
import java.sql.PreparedStatement;

/** * Update database operation *@author ruochen
 * @version1.0 * /
public class UpdateTest {

	public static void main(String[] args) {
		Connection conn = DBConfig.getConnection();
		
		String[] id = {"0002"."0003"};
		int[] score = {70.60};
		PreparedStatement ps;
		try {
			ps = conn.prepareStatement("Update student set score=? where id=?");
			int i = 0, idlen = id.length;
			do {
				ps.setInt(1, score[i]);
				ps.setString(2, id[i]);
				ps.executeUpdate();
				System.out.println("Modified successfully");
				++i;
			} while (i < idlen);
			DBConfig.closeConnection(null, ps, conn);
		} catch(Exception e) { e.printStackTrace(); }}}Copy the code

Loading succeeded Modification succeeded Modification succeededCopy the code

• Delete and query operationsSearchTest.java

/* * * like dust */
package test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

/** * delete from database *@author ruochen
 * @version1.0 * /
public class SearchTest {
	public static void main(String[] args) {
		Connection conn = DBConfig.getConnection();
		try {
			Statement st = conn.createStatement(); 
			PreparedStatement ps = conn.prepareStatement("delete from student where id=?");
			ps.setString(1."0002");
			ps.executeUpdate();
			System.out.println("Deleted successfully");
			ResultSet rs = st.executeQuery("select * from student");
			while (rs.next()) {
				System.out.println(rs.getString("id") + "\t"
						+ rs.getString("name") + "\t" + rs.getShort("score"));
			}
			st.close();
			DBConfig.closeConnection(rs, ps, conn);
		} catch(Exception e) { e.printStackTrace(); }}}Copy the code

Loading succeeded Deleting succeeded 0001 Xiao Wang 80 0003 Xiao Zhang 60Copy the code

Mysql connector – Java – 5.1.18 – bin. Jar links: pan.baidu.com/s/1Ep7pKfFn… Extraction code: TZ8N

Finally, welcome to pay attention to my personal wechat public account “Little Ape Ruochen”, get more IT technology, dry goods knowledge, hot news