Mysql database connection and addition, deletion, modification, and query Java code implementation (Statement version), mysqlstatement

Mysql database connection and addition, deletion, modification, and query Java code implementation (Statement version), mysqlstatement

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:

MysqlDao. java:

Package com. dao; import java. SQL. connection; import java. SQL. driverManager; import java. SQL. resultSet; import java. SQL. SQLException; import java. SQL. statement; import org. junit. test; public class mysqlDao {private static String driver = "com. mysql. jdbc. driver "; // Driver private String url =" jdbc: mysql: // localhost: 3306/test "; // database connection address private String user =" root "; // database account private String password = "11"; // Database password private Connection connection = null; // Connection private Statement stmt = null; // declare private ResultSet rs = null; // result set private int I =-1; /** create driver **/static {try {Class. forName (driver);} catch (ClassNotFoundException e) {// TODO Auto-generated catch blocke. printStackTrace () ;}/ ** load driver **/@ Testpublic void connect () {// TODO Auto-generated method stubtry {connection = DriverManager. getConnection (url, user, password); if (co Nnection! = Null) {System. out. println ("database connection successful! ");} Else {System. out. println (" database connection failed! ") ;}} Catch (SQLException e) {// TODO Auto-generated catch blocke. printStackTrace () ;}/ ** Execute SQL statement **/public void doSql (String SQL) {// TODO Auto-generated method stubSystem. out. println ("This Is mysqlDao. doSql () Method! "); If (SQL! = Null) {connect (); try restarting stmt1_connection.createstatement(;;stmt.exe cute (SQL);} catch (SQLException e) {// TODO Auto-generated catch blocke. printStackTrace () ;}}/ ** obtain affected rows **/public int getUpCount () {// TODO Auto-generated method stubtry {I = stmt. getUpdateCount ();} catch (SQLException e) {// TODO Auto-generated catch blocke. printStackTrace ();} return I;}/** get result set **/public ResultSet getRs () {try {rs = Stmt. getResultSet ();} catch (SQLException e) {// TODO Auto-generated catch blocke. printStackTrace ();} return rs;}/** execute the close method **/public void close () {// TODO Auto-generated method stubtry {if (rs! = Null) {rs. close () ;}} catch (SQLException e) {// TODO Auto-generated catch blocke. printStackTrace () ;}finally {try {if (stmt! = Null) {stmt. close () ;}} catch (SQLException e) {// TODO Auto-generated catch blocke. printStackTrace () ;}finally {if (connection! = Null) {try {connection. close () ;}catch (SQLException e) {// TODO Auto-generated catch blocke. printStackTrace ();}}}}}}

CreateSql. java

Package com. dao; import java. SQL. resultSet; import java. SQL. SQLException; import org. junit. test; public class createSql {mysqlDao mysqldao = new mysqlDao (); private String SQL; private int I =-1; private ResultSet rs = null; /** insert data **/@ Testpublic void insert () {// TODO Auto-generated method stubsql = "insert into t1 (name, password) values ('lisi ', '200') "; // create an SQL statement mysqldao. doSql (SQL); // execute the SQL statement I = mysqldao. getUp Count (); // obtain the number of affected rows if (I! =-1) {System. out. println ("data inserted successfully! ");} Else {System. out. println (" data insertion failed! ");} Mysqldao. close (); // close the connection}/** delete data **/@ Testpublic void delete () {// TODO Auto-generated method stubsql = "delete from t1 where id = 6"; mysqldao. doSql (SQL); I = mysqldao. getUpCount (); if (I! =-1) {System. out. println ("data deleted successfully! ");} Else {System. out. println (" data deletion failed! ");} Mysqldao. close ();}/** modify data **/@ Testpublic void update () {// TODO Auto-generated method stubsql = "update t1 set name = 'hangzhou' where id = 1"; mysqldao. doSql (SQL); I = mysqldao. getUpCount (); if (I! =-1) {System. out. println ("data update successful! ");} Else {System. out. println (" data update failed! ");} Mysqldao. close ();}/** traverse data **/@ Testpublic void select () throws SQLException {// TODO Auto-generated method stubsql = "select * from t1"; mysqldao. doSql (SQL); rs = mysqldao. getRs (); if (rs! = Null) {rs. last (); I = rs. getRow (); if (I> 0) {rs. beforeFirst (); while (rs. next () {String id = rs. getString ("id"); String name = rs. getString ("name"); String password = rs. getString ("password"); System. out. println ("id:" + id + "" + "name:" + name + "password:" + password) ;}} else {System. out. println ("sorry, no information you want to query! ") ;}} Else {System. out. println (" exception... ") ;}mysqldao. close ();}}