Java performs simple addition, deletion, modification, and query of MySQL databases through JDBC

JDBC Basic operations

 

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

(1) define the record class (optional)

This is required to facilitate operations and interface definition.

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) connection acquisition

You must obtain the connection to the database before the operation.

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, load the corresponding driver
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.exe cuteUpdate ();
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.exe cuteUpdate ();
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.exe cuteQuery ();
Int col = rs. getMetaData (). getColumnCount ();
System. out. println ("=========================== ");
While (rs. next ()){
For (int I = 1; I & lt; = col; I ++ ){
System. out. print (rs. getString (I) + "\ t ");
If (I = 2) & amp; (rs. getString (I). length () & lt; 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.exe cuteUpdate ();
System. out. println ("resutl:" + I );
Pstmt. close ();
Conn. close ();
} Catch (SQLException e ){
E. printStackTrace ();
    }
Return I;
}

Test

Before testing, you need to open the services of the corresponding database in the system. The startup command for MySQL in 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 ();
}

 

Eclipse output

 

======================================
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
======================================
Copy code
 

Code analysis

In the process of adding, deleting, modifying, and querying databases, we can find the common part, that is, the general process:

(1) create a Connection object and a SQL query command string;

(2) input an SQL query command to the Connection object to obtain the PreparedStatement object;

(3) execute executeUpdate () or executeQurey () on the PreparedStatement object to obtain the result;

(4) close PreparedStatement objects and Connection objects successively.

It can be seen that when using JDBC, the two most common classes are Connection and PreparedStatement, and the ResultSet class in select. Read the Java API manual to learn its specific meaning and methods.

The following documents reference the Java API from
Http://download.oracle.com/technetwork/java/javase/6/docs/zh/api/index.html.

Connection

Java. SQL
Interface Connection

All Super interfaces:
Wrapper

 

 
Connection (session) to a specific database ). Execute the SQL statement in the connection context and return the result.
The database of the Connection object can provide information about its tables, supported SQL syntax, stored procedures, and Connection functions. This information is obtained using the getMetaData method.
 

PreparedStatemnt

Java. SQL
Interface PreparedStatement

All Super interfaces:
Statement, Wrapper
All known sub-interfaces:
CallableStatement

 

Indicates the objects of pre-compiled SQL statements.
SQL statements are pre-compiled and stored in the PreparedStatement object. This object can then be used to efficiently execute the statement multiple times.
 
Common methods
Boolean execute ()
Execute an SQL statement in this PreparedStatement object. This statement can be any type of SQL statement.
ResultSet executeQuery ()
Execute an SQL query in this PreparedStatement object and return the ResultSet object generated by this query.
Int executeUpdate ()
Execute an SQL statement in this PreparedStatement object. The statement must be a Data Manipulation Language (DML) statement, such as an INSERT, UPDATE, or DELETE statement; or an SQL statement without returned content, such as a DDL statement.
ResultSet

Java. SQL
Interface ResultSet

All Super interfaces:
Wrapper

All known sub-interfaces:

CachedRowSet, FilteredRowSet, JdbcRowSet, JoinRowSet, RowSet, SyncResolver, WebRowSet

 

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

1. Each SQL operation requires establishing and disabling connections, which will inevitably consume a lot of resource overhead. How can we avoid this?

Analysis: you can use a connection pool to maintain connections in a unified manner without having to create or close them each time. In fact, this is used by many tools that encapsulate JDBC.

 

2. In Java code, what should I do if the input data format is different from the database definition? For example, assign the String object of Java to the tinyint attribute of the database.

Analysis: The database tries to convert the SQL statement. According to my experiment, if the age attribute of tinyint is passed into a String object with a pure letter in content, it will be converted to 0. The specific conversion rules should be related to the database.