The previous article has demonstrated how to connect the database, because the connection of the database this work, often to do, so it is better to directly set up a class, specifically to deal with the database aspects of things, more worry, not to say more, the example, the words are in the notes.
import java.sql.*;// Import database-related libraries
/** * MysqlHandler MySQL database management class *@authorThe cat brother *@date2016.12.31 * /
public class MysqlHandler{
// Three required attributes
private Connection conn = null;//Connection represents a Connection to a database. Every time you make a phone call, every time you connect to a database
private Statement stmt = null;// After Java connects to the database, what can be done to the database? What parameters do I need to tell the database? This information depends on statements
private ResultSet rs = null;// If you are querying data from a database, place the returned data in a ResultSet
// Set up a connection to the database
public Connection buildConnection(a) {
String driver = "com.mysql.jdbc.Driver";// Driver name of the MySQL database
String url = "jdbc:mysql://localhost:3306/java? useUnicode=true&characterEncoding=utf-8";// Database connection string
String user = "root";/ / user name
String password = "Pass1234";/ / password
try{
Class.forName(driver);// Load the driver
conn=DriverManager.getConnection(url,user,password);// Enter the required parameters to obtain the connection
}
catch(Exception ex){
ex.printStackTrace();// Can output detailed exception information
}
return conn;
}
// In the second step, "Add, delete, modify, query", add, delete, modify all execute SQL statements, do not need to return the ResultSet, so set as a method
public int execute(String sql){
try {
if(stmt==null)/ / create the Statement
stmt=conn.createStatement();
int affectedCount = stmt.executeUpdate(sql);// The operation defined by STMT is actually performed here
return affectedCount;// This is the number of rows that are affected
} catch (Exception ex) {
ex.printStackTrace();
return -1;// Return -1, indicating that the execution failed and an exception occurred}}// Step 2, if it is a query, return the result set
public ResultSet query(String sql){
try {
if(stmt==null)/ / create the Statement
stmt=conn.createStatement();
rs = stmt.executeQuery(sql);// Perform the query defined in PSTMT
return rs;// Return the result set
} catch (Exception ex) {
ex.printStackTrace();
return null;// If there is an error, return null}}// Set up a connection to the database. The connection to the database is limited and must be released when it is used up. Otherwise, it is a waste of time
public void sayGoodbye(a){
// Do not include all three.close() in a try{}, if the first one is not an exception, then the next one is not an exception
if(rs! =null) {// Close the result set
try {
rs.close();
} catch(Exception ex) { ex.printStackTrace(); }}if(stmt! =null) {// Close Statement, don't waste it
try {
stmt.close();
} catch(Exception ex) { ex.printStackTrace(); }}if(conn! =null) {// Close the connection
try {
conn.close();
} catch(Exception ex) { ex.printStackTrace(); }}}}Copy the codeStudent_info = student_info; student_info = student_info
import java.sql.*;
public class TestMysql {// Test MysqlHandler class
public static void main(String[] args) {
try {// Catch an exception
testOneTime();
} catch(SQLException e) { e.printStackTrace(); }}// Test a complete add, delete, change and check operation
private static void testOneTime(a)throws SQLException{// There may be anomalies, which we capture in a wider world
MysqlHandler hand=new MysqlHandler();
Connection con=hand.buildConnection();// Establish a connection
ResultSet rs;// Can be used multiple times
/ / check
rs=hand.query("select * from student_info");
// Display the result
System.out.println("\n Query result is as follows:");
while(rs.next()){
System.out.print(rs.getInt(1) + "|");
System.out.print(rs.getString(2) + "\t");
}
// Add 2
int addCount=hand.execute("Insert into student_info (student_name) values(' Martian '),(' Mercury '));
System.out.println("\n increased"+addCount+"个");
// Delete one
int delCount=hand.execute("Delete from student_info where student_name=' student_name '");
System.out.println("\n deleted"+delCount+"个");
// Change li Si into a Martian
int changeCount=hand.execute("Update student_info set student_name=' student_name 'where student_name=' student_name '");
System.out.println("\n modified"+changeCount+"个");
/ / check
rs=hand.query("select * from student_info");
System.out.println("\n Query result is as follows:");
while(rs.next()){
System.out.print(rs.getInt(1) + "|");
System.out.print(rs.getString(2) + "\t");
}
hand.sayGoodbye();// Don't forget to close it}}Copy the codeTest results:
The query results are as follows: 1 3 | 2 | | zhang SAN li si fifty and increased 2 deleted 1 changed the query results are as follows: 1 2 3 | | martians, dick, and harry fifty and 4 5 | | martians mercuryCopy the codeRun it again and the result is:
Query results are as follows: 2 | 3 | martians, dick, and harry fifty and 4 5 | | martians have increased mercury 2 0 0 deleted modified the query results are as follows: 2 | 3 | martians, dick, and harry fifty and 4 5 | | martians mercury 6 7 | | martians mercuryCopy the codeThe result is that we rely on native SQL for the student_info table. And the subject language is relatively clean and tidy. But it’s boring, so let’s do a test. First let’s test how long it takes to execute 100 queries and change the test class as follows:
import java.sql.*;
public class TestMysql {// Test MysqlHandler class
public static void main(String[] args) {
try {// Catch an exception
long start=System.currentTimeMillis();// Generate a current millisecond (milliseconds since 0 o 'clock, January 1, 1970)
for(int i=0; i<100; i++){//*** controls the number of tests ***
testOneTime();
}
long end=System.currentTimeMillis();
System.out.println("\n Time consuming:"+(end-start)+"毫秒");
} catch(SQLException e) { e.printStackTrace(); }}// Test a full query
private static void testOneTime(a)throws SQLException{// There may be anomalies, which we capture in a wider world
MysqlHandler hand=new MysqlHandler();
Connection con=hand.buildConnection();// Establish a connection
ResultSet rs;// Can be used multiple times
/ / check
rs=hand.query("select * from student_info");
hand.sayGoodbye();// Don't forget to close it}}Copy the codeOk, we ran it a few times and found that the elapsed time was not fixed, but there was a general fluctuation range, which was related to the state of the computer and the database, so it was understandable.
Cat brother tested several sets of data as follows: execute 100 times: 889, 856.
If the modification is as follows, perform the same operation 100 times, which takes 48, 48, and 46 times.
import java.sql.*;
public class TestMysql {// Test MysqlHandler class
public static MysqlHandler hand=new MysqlHandler();
public static Connection con=hand.buildConnection();// Establish a connection
public static ResultSet rs;// Can be used multiple times
public static void main(String[] args) {
try {// Catch an exception
long start=System.currentTimeMillis();// Generate a current millisecond (milliseconds since 0 o 'clock, January 1, 1970)
for(int i=0; i<1; i++){ testOneTime(); } hand.sayGoodbye();// Don't forget to close it
long end=System.currentTimeMillis();
System.out.println("\n Time consuming:"+(end-start)+"毫秒");
} catch(SQLException e) { e.printStackTrace(); }}// Test a full query
private static void testOneTime(a)throws SQLException{// There may be anomalies, which we capture in a wider world
/ / check
rs=hand.query("select * from student_info"); }}Copy the codeStatic variable static variable static variable static variable
Project source code, series of tutorials and more content, please visit panda programming network