Java simple MySQL database connection addition, deletion, modification, query 1. connect to the database and obtain the database connection variable
[Java]View plaincopyprint?
- // Note that this is the method for connecting to mysql
// Note that this is the method for connecting to mysql
Note that when connecting to the database
(1) open the DB Browser to create a new Database Driver, pay attention to add the Driver JARs when the package, my is the mysql-connector-java-5.0.3-bin.jar
(2) copy the jar package of the database to the WEB-INF \ lib under the project
[Java]View plaincopyprint?
- Import java. SQL. Connection; // java package
- Public class DBConnection
- {
- Private String dbDriver = "com. mysql. jdbc. Driver ";
- Private String dbUrl = "jdbc: mysql: // [IP address]: [port number]/[Database name]"; // varies depending on the actual situation
- Private String dbUser = "root ";
- Private String dbPass = "root ";
- Public Connection getConn ()
- {
- Connection conn = null;
- Try
- {
- Class. forName (dbDriver );
- }
- Catch (ClassNotFoundException e)
- {
- E. printStackTrace ();
- }
- Try
- {
- Conn = DriverManager. getConnection (dbUrl, dbUser, dbPass); // note that three parameters
- }
- Catch (SQLException e)
- {
- E. printStackTrace ();
- }
- Return conn;
- }
- }
Import java. SQL. connection; // java package public class DBConnection {private String dbDriver = "com. mysql. jdbc. driver "; private String dbUrl =" jdbc: mysql: // [IP address]: [port number]/[Database name] "; // change according to the actual situation private String dbUser = "root"; private String dbPass = "root"; public Connection getConn () {Connection conn = null; try {Class. forName (dbDriver);} catch (ClassNotFoundException e) {e. printStackTrace ();} try {conn = DriverManager. getConnection (dbUrl, dbUser, dbPass); // note that there are three parameters} catch (SQLException e) {e. printStackTrace () ;}return conn ;}}
2. INSERT operation
[Java]View plaincopyprint?
- Public int insert ()
- {
- Int I = 0;
- String SQL = "insert into (table name) (column name 1, listing 2) values (?,?) ";
- Connection cnn = getConn ();
-
- Try {
- PreparedStatement preStmt = cnn. prepareStement (SQL );
- PreStmt. setString (1, value );
- PreStmt. setString (2, value); // Or: preStmt. setInt (1, value );
- I =preStmt.exe cuteUpdate ();
- }
- Catch (SQLException e)
- {
- E. printStackTrace ();
- }
- Return I; // return the number of affected rows. 1 indicates that execution is successful.
- }
Public int insert () {int I = 0; String SQL = "insert into (table name) (column name 1, listing 2) values (?,?) "; Connection cnn = getConn (); try {PreparedStatement preStmt = cnn. prepareStement (SQL); preStmt. setString (1, value); preStmt. setString (2, value); // Or: prestmt.setint(1. The value of the catch parameter i1_prestmt.exe cuteUpdate ();} catch (SQLException e) {e. printStackTrace ();} return I; // return the number of affected rows. 1 indicates that execution is successful}
3. update operations
[Java]View plaincopyprint?
- Public int update
- {
- Int I = 0;
- String SQL = "update (table name) set (column name 1) = ?, List 2 =? Where (column name) =? "; // Note that the where condition must exist.
- Connection cnn = getConn ();
-
-
- Try {
- PreparedStatement preStmt = cnn. prepareStatement (SQL );
- PreStmt. setString (1, (value ));
- PreStmt. setString (2, (value); // Or: preStmt. setInt (1, value );
- PreStmt. setInt (3, (value ));
- I =preStmt.exe cuteUpdate ();
- }
- Catch (SQLException e)
- {
- E. printStackTrace ();
- }
- Return I; // return the number of affected rows. 1 indicates that execution is successful.
- }
Public int update {int I = 0; String SQL = "update (table name) set (column name 1) = ?, List 2 =? Where (column name) =? "; // Note that the where condition Connection cnn = getConn () is required; try {PreparedStatement preStmt = cnn. prepareStatement (SQL); preStmt. setString (1, (value); preStmt. setString (2, (value); // Or: prestmt.setint(1. The value of prestmt.setint(3,(value of commandid i1_prestmt.exe cuteUpdate ();} catch (SQLException e) {e. printStackTrace ();} return I; // return the number of affected rows. 1 indicates that execution is successful}
4. search operation
[Java]View plaincopyprint?
- Public String select
- {
- String SQL = "select * from (table name) where (column name) = (value )";
- Connection cnn = getConn (); // The Connection is obtained through the self-written method getConn ().
- Try
- {
- Statement stmt = conn. createStatement ();
- ResultSet rs = stmt.exe cuteQuery (SQL );
-
- If (rs. next ())
- {
- Int m1 = rs. getInt (1); // or rs. getString (1). it is determined based on the value type of the column in the database. the parameter is the first column.
- String m2 = rs. getString (2 );
- }
- // Write the searched value to the class and return the corresponding object
- }
- Catch (SQLException e)
- {
- E. printStackTrace ();
- }
- Return (variable of the corresponding value );
- }
Public String select {String SQL = "select * from (table name) where (column name) = (value)"; Connection cnn = getConn (); // The getConn () method is used to obtain the connection try {Statement stmt = conn. createStatement (); ResultSet rs = stmt.exe cuteQuery (SQL); if (rs. next () {int m1 = rs. getInt (1); // or rs. getString (1) is determined based on the value type of the column in the database. the parameter is the first String m2 = rs. getString (2) ;}// you can write the searched value to the class and return the corresponding object} catch (SQLException e) {e. printStackTrace () ;}return (variable of the corresponding value );}
5. delete operation
[Java]View plaincopyprint?
- Public int delete ()
- {
- String SQL = "delete from (table name) where (column name) = (value )";
- Int I = 0;
- Connection conn = getConn (); // The Connection is obtained through the self-written method getConn ().
- Try
- {
- Statement stmt = conn. createStatement ();
- I = stmt.exe cuteUpdate (SQL );
- }
- Catch (SQLException e)
- {
- E. printStackTrace ();
- }
- Return I; // if return is 1, the execution is successful;
- }