Mysql database connection and addition, deletion, modification, and query Java code implementation (Statement Version)
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 ();}}