Java simple MySQL database connection addition, deletion, modification, and query _ MySQL

Java simple MySQL database connection addition, deletion, modification, query 1. connect to the database and obtain the database connection variable

[Java]View plaincopyprint?

1. // 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?

1. Import java. SQL. Connection; // java package
2. Public class DBConnection
3. {
4. Private String dbDriver = "com. mysql. jdbc. Driver ";
5. Private String dbUrl = "jdbc: mysql: // [IP address]: [port number]/[Database name]"; // varies depending on the actual situation
6. Private String dbUser = "root ";
7. Private String dbPass = "root ";
8. Public Connection getConn ()
9. {
10. Connection conn = null;
11. Try
12. {
13. Class. forName (dbDriver );
14. }
15. Catch (ClassNotFoundException e)
16. {
17. E. printStackTrace ();
18. }
19. Try
20. {
21. Conn = DriverManager. getConnection (dbUrl, dbUser, dbPass); // note that three parameters
22. }
23. Catch (SQLException e)
24. {
25. E. printStackTrace ();
26. }
27. Return conn;
28. }
29. }

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?

1. Public int insert ()
2. {
3. Int I = 0;
4. String SQL = "insert into (table name) (column name 1, listing 2) values (?,?) ";
5. Connection cnn = getConn ();
7. Try {
8. PreparedStatement preStmt = cnn. prepareStement (SQL );
9. PreStmt. setString (1, value );
10. PreStmt. setString (2, value); // Or: preStmt. setInt (1, value );
11. I =preStmt.exe cuteUpdate ();
12. }
13. Catch (SQLException e)
14. {
15. E. printStackTrace ();
16. }
17. Return I; // return the number of affected rows. 1 indicates that execution is successful.
18. }

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?

1. Public int update
2. {
3. Int I = 0;
4. String SQL = "update (table name) set (column name 1) = ?, List 2 =? Where (column name) =? "; // Note that the where condition must exist.
5. Connection cnn = getConn ();
8. Try {
9. PreparedStatement preStmt = cnn. prepareStatement (SQL );
10. PreStmt. setString (1, (value ));
11. PreStmt. setString (2, (value); // Or: preStmt. setInt (1, value );
12. PreStmt. setInt (3, (value ));
13. I =preStmt.exe cuteUpdate ();
14. }
15. Catch (SQLException e)
16. {
17. E. printStackTrace ();
18. }
19. Return I; // return the number of affected rows. 1 indicates that execution is successful.
20. }

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?

1. Public String select
2. {
3. String SQL = "select * from (table name) where (column name) = (value )";
4. Connection cnn = getConn (); // The Connection is obtained through the self-written method getConn ().
5. Try
6. {
7. Statement stmt = conn. createStatement ();
8. ResultSet rs = stmt.exe cuteQuery (SQL );
10. If (rs. next ())
11. {
12. 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.
13. String m2 = rs. getString (2 );
14. }
15. // Write the searched value to the class and return the corresponding object
16. }
17. Catch (SQLException e)
18. {
19. E. printStackTrace ();
20. }
21. Return (variable of the corresponding value );
22. }

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?

1. Public int delete ()
2. {
3. String SQL = "delete from (table name) where (column name) = (value )";
4. Int I = 0;
5. Connection conn = getConn (); // The Connection is obtained through the self-written method getConn ().
6. Try
7. {
8. Statement stmt = conn. createStatement ();
9. I = stmt.exe cuteUpdate (SQL );
10. }
11. Catch (SQLException e)
12. {
13. E. printStackTrace ();
14. }
15. Return I; // if return is 1, the execution is successful;
16. }