[Abstract] preparation of JDBC Java connection MySQL database tool: MySQL driver jar package corresponding to MySQL (database), MyEclipse (development tool) and Navicat (database management tool) (my mql is 8.0, so MySQL connector java-8.0.13. Jar) MySQL connector java-8.0.13 Jar package

JDBC Java connection to MySql database

Tool preparation: Mysql (database), MyEclipse (development tool), Navicat (database management tool)
Corresponding MySQL driver jar package (my mql is 8.0, so mysql-connector-java-8.0.13. Jar is used)

mysql-connector-java-8.0.13.jar package download
1. Download address: https://dev.mysql.com/downloads/connector/j/ Select the platform independent option and download the jar package.

2. After downloading, unzip and find mysql-connector-java-8.0.13 jar

Add the jar package to the Myeclipse project.

Build environment and introduce jar package
1. To develop compiled code using a normal text editor, you need to configure the jar package into the ClassPath variable, ClassPath =; E:\jdbc\ojdbc5. jar
II. Use the integrated development tool myeclipse to create a new lib directory under the project - > put the jar package into -- > select the jar package and right-click - > buildpath - > add to buildpath - > OK
First Jdbc program

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

public class PreparedStatementJdbc {
//Connection string
private static final String URL = "jdbc:mysql://127.0.0.1:3306/exam?useUnicode=true&characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC";
//User name
private static final String USER = "root";
//Code
private static final String PASSWORD = "123456";

public static void main(String[] args) {
	// 1. Query the account information and print it according to the card number entered by the user
	 get(1);
	// 2. Add an account (name, password, balance) according to the information entered by the user
	// add("Niuniu 897", "123456", 9943.0);
	// 3. Modify the account password according to the information entered by the user
	//update(8, "rfgs");
	// 4. Complete the account cancellation action according to the card number and password entered by the user
	//delete(7, "123456");
}

// 1. Query the account information and print it according to the card number entered by the user

public static void get(Integer a) {
	Connection con = null;
	PreparedStatement ps = null;
	ResultSet rs = null;
	try {
		// Mysql is used to load the driver
		Class.forName("com.mysql.cj.jdbc.Driver");
		// Get a connection to the database
		con = DriverManager.getConnection(URL, USER, PASSWORD);
		// SQL statement
		String sql = "select cardid,name,password,balance from account where cardid=?";
		// Operate the database through the connection of the database to realize addition, deletion, modification and query
		ps = con.prepareStatement(sql);
		// Here? Assign actual value / / 1s
		ps.setInt(1, a);
		// Execute database statements
		rs = ps.executeQuery();
		// If there is data, rs.next returns true; otherwise, it returns false
		while (rs.next()) { System.out.println(rs.getInt("cardid") + "," + rs.getString("name") + ", " + rs.getString("password") + "," + rs.getDouble("balance"));
		}
	} catch (ClassNotFoundException e) {
		e.printStackTrace();
	} catch (SQLException e) {
		e.printStackTrace();
	} finally {
		if (rs != null) { try { rs.close(); } catch (SQLException e) { e.printStackTrace(); }
		}
		if (ps != null) { try { ps.close(); } catch (SQLException e) { e.printStackTrace(); }
		}
		if (con != null) { try { con.close(); } catch (SQLException e) { e.printStackTrace(); }
		}
	}

}

// 2. Add an account (name, password, balance) according to the information entered by the user

public static void add(String name, String password, Double balance) {
	Connection con = null;
	PreparedStatement ps = null;
	try {
		Class.forName("com.mysql.cj.jdbc.Driver");
		con = DriverManager.getConnection(URL, USER, PASSWORD);
		String sql = "insert into account(name,password,balance) values(?,?,?);";
		ps = con.prepareStatement(sql);
		ps.setString(1, name);
		ps.setString(2, password);
		ps.setDouble(3, balance);
		ps.execute();
	} catch (ClassNotFoundException e) {
		e.printStackTrace();
	} catch (SQLException e) {
		e.printStackTrace();
	} finally {
		if (ps != null) { try { ps.close(); } catch (SQLException e) { e.printStackTrace(); }
		}
		if (con != null) { try { con.close(); } catch (SQLException e) { e.printStackTrace(); }
		}
	}
}

// 3. Modify the account password according to the information entered by the user
public static void update(Integer cardid,String password) {
	Connection con = null;
	PreparedStatement ps = null;
	try {
		Class.forName("com.mysql.cj.jdbc.Driver");
		con = DriverManager.getConnection(URL, USER, PASSWORD);
		String sql = "update account set password=? where cardid=?;";
		ps = con.prepareStatement(sql);
		ps.setString(1, password);
		ps.setInt(2, cardid);
		ps.execute();
	} catch (ClassNotFoundException e) {
		e.printStackTrace();
	} catch (SQLException e) {
		e.printStackTrace();
	} finally {
		if (ps != null) { try { ps.close(); } catch (SQLException e) { e.printStackTrace(); }
		}
		if (con != null) { try { con.close(); } catch (SQLException e) { e.printStackTrace(); }
		}
	}
}

// 4. Complete the account cancellation action according to the card number and password entered by the user
public static void delete(Integer cardid,String password) {
	Connection con = null;
	PreparedStatement ps = null;
	try {
		Class.forName("com.mysql.cj.jdbc.Driver");
		con = DriverManager.getConnection(URL, USER, PASSWORD);
		String sql = "delete from account where cardid=? and password=?;";
		ps = con.prepareStatement(sql);
		ps.setInt(1, cardid);
		ps.setString(2, password);
		ps.execute();
	} catch (ClassNotFoundException e) {
		e.printStackTrace();
	} catch (SQLException e) {
		e.printStackTrace();
	} finally {
		if (ps != null) { try { ps.close(); } catch (SQLException e) { e.printStackTrace(); }
		}
		if (con != null) { try { con.close(); } catch (SQLException e) { e.printStackTrace(); }
		}
	}
}

  
 

}
Difference between Statement and PreparedStatement

The Statement object is used to send SQL statements to the database. There are actually three kinds of Statement objects that act as package containers for executing SQL statements on a given connection: Statement, PreparedStatement (which inherits from Statement), and CallableStatement (which inherits from PreparedStatement). They are dedicated to sending specific types of SQL statements: the Statement object is used to execute simple SQL statements without parameters; The PreparedStatement object is used to execute precompiled SQL statements with or without parameters; The CallableStatement object is used to make calls to stored procedures in the database. Every time a Statement executes an SQL Statement, the database must compile the SQL Statement. It is best used when only one query is executed and the result is returned. The efficiency is higher than that of PreparedStatement However, there is a risk of SQL injection. PreparedStatement is pre compiled. When executing an SQL with variable parameters, PreparedStatement is more efficient than Statement, because DBMS precompiling an SQL is certainly more efficient than compiling an SQL multiple times. Better security and effectively prevent SQL injection. For statements that execute repeatedly, use Prepared
Statement will be more efficient. Execute SQL statements with parameters and support batch execution of SQL. Due to the Cache mechanism, the precompiled statements will be placed in the Cache. The next time the same SQL statement is executed, it can be taken out of the Cache directly.
The CallableStatement extends the PreparedStatement interface to call stored procedures. It provides support for input and output parameters. The CallableStatement interface also supports sql queries for input parameters provided by the PreparedStatement interface.
PreparedStatement: the database will precompile the sql statement. When the same sql statement is executed next time, the database side will not precompile again. Instead, the buffer of the database is directly used to improve the efficiency of data access (but try to use the "sign" to pass parameters). If the sql statement is executed only once, it will not be reused in the future. In terms of security, PreparedStatement is through? To pass parameters, which avoids the problem of sql injection caused by splicing sql, so it has good security.
PreparedStatement is recommended for development

Source: albertyang blog. csdn. Net, author: Albert Yang, the copyright belongs to the original author. If you need to reprint, please contact the author.

Original link: albertyang blog. csdn. net/article/details/83833246