Add, delete, modify, and query operation records for connecting to the mysql database using java, javamysql

Add, delete, modify, and query operation records for connecting to the mysql database using java, javamysql

1. Connect to the database to obtain the database connection variable.

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

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

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); imo-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

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, value); preStmt. setInt (3, (value); imo-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

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

Public int delete () {String SQL = "delete from (Table Name) where (column name) = (value)"; int I = 0; Connection conn = getConn (); // The getConn () method is used to obtain the connection try {Statement stmt = conn. createStatement (); I = stmt.exe cuteUpdate (SQL);} catch (SQLException e) {e. printStackTrace ();} return I; // if 1 is returned, the execution is successful ;}