Mysql database connection and addition, deletion, modification, and query Java code implementation (PreparedStatement version), mysql database addition, deletion, modification, and query

Mysql database connection and addition, deletion, modification, and query Java code implementation (PreparedStatement version), mysql database addition, deletion, modification, and query

Database:

create table t1(id int primary key not null auto_increment,name varchar(32),password varchar(32));insert into t1(name,password) values('admin','123');insert into t1(name,password) values('zhangsan','123');insert into t1(name,password) values('lisi','123');

Java code:

MySqlConnection. java code:

Package com. dbdao. mysql; import java. SQL. connection; import java. SQL. driverManager; import java. SQL. preparedStatement; import java. SQL. resultSet; import java. SQL. SQLException; import org. junit. test; public class mySqlConnection {// create a database driver name private static String Driver_class = "com. mysql. jdbc. driver "; // Database url private String url =" jdbc: mysql: // localhost: 3306/test "; // database username private String user =" root "; // Database Password priv Ate String password = "11"; // database link private Connection con = null; // prepare to declare the SQL statement private PreparedStatement pstmt = null; // The result set private ResultSet rs = null; // affects the number of rows private int I;/** create driver **/static {try {Class. forName (Driver_class);} catch (ClassNotFoundException e) {// TODO Auto-generated catch blocke. printStackTrace () ;}/ ** load driver **/@ Testpublic void getConnect () {// TODO Auto-generated method stubtry {con = DriverM Anager. getConnection (url, user, password);} catch (SQLException e) {// TODO Auto-generated catch blocke. printStackTrace ();} // determines whether the database is successfully loaded if (con! = Null) {System. out. println ("the database is loaded successfully! ");} Else {System. out. println (" failed to load the database! ") ;}}/** Run the SQL statement **/public void doSql (String SQL, Object [] object) {// TODO Auto-generated method stub // determine whether the SQL statement exists if (SQL! = Null) {// getConnect (); // determines whether the object array exists if (object = null) {// if not, create, prevent NULL pointer exception object = new Object [0];} try {// declare a prepared SQL statement pstmt = con. prepareStatement (SQL); // assign one-to-one values to Object (int I = 0; I <object. length; I ++) {pstmt. setObject (I + 1, object [I]);} // execute the declared SQL statement pstmt.exe cute ();} catch (SQLException e) {// TODO Auto-generated catch blocke. printStackTrace () ;}} else {System. out. println ("SQL statement does not exist! ") ;}}/** Get result set **/public ResultSet getRS () {try {// obtain result set method rs = pstmt. getResultSet ();} catch (SQLException e) {// TODO Auto-generated catch blocke. printStackTrace ();} // return result set return rs;}/** obtain affected rows **/public int getUpdateCount () {// TODO Auto-generated method stubtry {// method for obtaining affected rows I = pstmt. getUpdateCount ();} catch (SQLException e) {// TODO Auto-generated catch blocke. printStackTrace ();} // return the number of affected rows return I;}/** close method **/public void getClose () {// TODO Auto-generated method stub // close the result set try {// method rs for disabling the result set. close ();} catch (SQLException e) {// TODO Auto-generated catch blocke. printStackTrace ();} finally {// close the declared SQL statement try {// close the declared SQL statement method pstmt. close ();} catch (SQLException e) {// TODO Auto-generated catch blocke. printStackTrace ();} finally {// uninstall the driver try {// uninstall the driver method con. close ();} catch (SQLException e) {// TODO Auto-generated catch blocke. printStackTrace ();}}}}}

LoginService. java code:

Package com. service; import java. SQL. resultSet; import java. SQL. SQLException; import org. junit. test; import com. dbdao. mysql. mySqlConnection; public class loginService {// import mySqlConnection class mySqlConnection mysqlCon = new mySqlConnection (); // prepare the SQL statement private String SQL; // affect the number of rows (after the data changes, the number of affected rows is greater than 0. If the value is equal to 0, no change is made. Therefore, if the change fails, the number of affected rows must be negative.) private int I =-1; // result set private ResultSet rs; /** insert data **/@ Testpublic void insert () {// TODO Auto-generated method stub // create an SQL statement SQL = "insert into t1 (name, password) values (?,?) "; // Create an object array Object [] object = new Object [] {" admin "," 123456 "}; // execute the SQL statement mysqlCon. doSql (SQL, object); // obtain the number of affected rows I = mysqlCon. getUpdateCount (); // determines whether insertion is successful if (I! =-1) {System. out. println ("data inserted successfully! ");} Else {System. out. println (" data insertion failed! ");} // Close the link mysqlCon. getClose ();}/** delete data **/@ Testpublic void delete () {// TODO Auto-generated method stub // create an SQL statement SQL = "delete from t1 where id =? "; // Create an object array Object [] object = new Object [] {3}; // execute the SQL statement mysqlCon. doSql (SQL, object); // obtain the number of affected rows I = mysqlCon. getUpdateCount (); // determines whether the deletion is successful if (I! =-1) {System. out. println ("data deleted successfully! ");} Else {System. out. println (" data deletion failed! ");} // Close the link mysqlCon. getClose ();}/** update data **/@ Testpublic void update () {// TODO Auto-generated method stub // create an SQL statement SQL = "update t1 set password =? Where name =? "; // Create an Object array Object [] object = new Object [] {" 11 "," admin "}; // execute the SQL statement mysqlCon. doSql (SQL, object); // obtain the number of affected rows I = mysqlCon. getUpdateCount (); // determines whether the data is successfully updated if (I! =-1) {System. out. println ("data update successful! ");} Else {System. out. println (" data update failed! ");} // Close the link mysqlCon. getClose ();}/** traverse data **/@ Testpublic void select () {// TODO Auto-generated method stub // create an SQL statement SQL = "select * from t1"; // execute the SQL statement mysqlCon. doSql (SQL, null); // obtain the result set rs = mysqlCon. getRS (); // determines whether the result set is null if (rs! = Null) {try {// move the cursor to the end of the result set. Note that rs. afterLast () cannot be used here; otherwise, it is null. Rs. last (); // obtain the number of rows in the result set I = rs. getRow (); // determines whether the result set exists if (I> 0) {// move the cursor to the front-end rs of the result set. beforeFirst (); // cyclically traverse all rows while (rs. next () {// traverse the content of each element in a row String name = rs. getString ("name"); String password = rs. getString ("password"); // print the result System on the console. out. println ("name:" + name + "password:" + password) ;}} else {System. out. println ("the result set is empty! ") ;}} Catch (SQLException e) {// TODO Auto-generated catch blocke. printStackTrace () ;}} else {System. out. println (" the result set does not exist! ") ;}// Close the link mysqlCon. getClose ();}}