Rhythmk learn Java step by step (2): operate MySQL Databases

1. Download such as: mysql-connector-java-5.1.22.zip decompress get jar connection package.

2. Import the project: Right-click the project name ---> build path-> Configure build path... select libraries and then select the right

Option 2 add external jars import mysql-. Jar

3. Create a data table:

 
Create Table 'user' ('id'Int(11) Not null auto_increment, 'name' varchar (32) Not null, 'age'Int(11) Not null, 'addtime' datetime not null, primary key ('id') Engine= InnoDB auto_increment = 8 default charset = utf8;
 

 

4. TestProgram:

 Package  App1.rhythmk.com;  Import  Java. SQL. connection;  Import  Java. SQL. drivermanager;  Import  Java. SQL. resultset;  Import  Com. MySQL. JDBC. statement;  /* ** Table creation script create table 'user' ('id' int (11) not null auto_increment, 'name' varchar (32) not null, 'age' int (11) not null, 'addtime' datetime not null, primary key ('id') engine = InnoDB auto_increment = 8 default charset = utf8;  */  Public   Class  Thirdmysql {  //  Final is similar to const in. net.      Private   Static   Final String databaseconurl = "JDBC: mysql: // 127.0.0.1: 3306/rhythmkdb" ;  /** * Rhythmk * 1. right-click the project name ---> build path-> Configure build path... A box pops up. Select * libraries in the four options, and then select the second option on the right to add external jars.  */      Public   Static   Void  Main (string [] ARGs ){  //  Todo auto-generated method stub  Test (); excutebyparam (); add (); Update (); select (); del ();}  Public   Static   Void Test (){  Try  {Connection con = Null  ; Class. forname ( "Com. MySQL. JDBC. Driver" ). Newinstance (); con = Drivermanager. getconnection (databaseconurl, "Root", "wangkun" ); MSG ( "MySQL connection successful. " );}  Catch  (Exception ex) {MSG (ex. tostring ());}} Public   Static   Void  Add () {string SQL = "Insert into 'user' (name, age, addtime )" ; SQL + = "Values ('rhythmk ', 21, '2017-1-1 ')" ;  Try  {Connection con = Null  ; Class. forname ( "Com. MySQL. JDBC. Driver" ). Newinstance (); con =Drivermanager. getconnection (databaseconurl, "Root", "wangkun" ); Java. SQL. Statement statement = Con. createstatement (); statement.exe cuteupdate (SQL); resultset result = Statement. executequery ( "Select last_insert_id ()" );  Int Resultid = 0 ;  If  (Result. Next ()){  // This index does not start from 0 .. Resultid = result. getint (1 );} MSG ( "Add database get return ID" + Resultid );}  Catch  (Exception ex) {MSG (ex. tostring ());}}  Public   Static   Void  Update () {string SQL = "Update 'user' set name = 'rhythmk. com' where ID <3" ;  Try  {Connection con = Null  ; Class. forname ( "Com. MySQL. JDBC. Driver" ). Newinstance (); con = Drivermanager. getconnection (databaseconurl, "Root", "wangkun" ); Java. SQL. Statement statement = Con. createstatement ();  Int Resultid = Statement.exe cuteupdate (SQL); MSG ( "Return ID after database update" + Resultid );}  Catch (Exception ex) {MSG (ex. tostring ());}}  Public   Static   Void  Del () {string SQL = "Delete from 'user' where id = 3" ;  Try  {Connection con = Null  ; Class. forname ( "Com. MySQL. JDBC. Driver" ). Newinstance (); con =Drivermanager. getconnection (databaseconurl, "Root", "wangkun" ); Java. SQL. Statement statement = Con. createstatement ();  Int Resultid = Statement.exe cuteupdate (SQL); MSG ( "Del database returns ID" + Resultid );}  Catch  (Exception ex) {MSG (ex. tostring ());}}  Public   Static   Void Select () {string SQL = "Select ID, name from 'user '" ;  Try  {Connection con = Null  ; Class. forname ( "Com. MySQL. JDBC. Driver" ). Newinstance (); con = Drivermanager. getconnection (databaseconurl, "Root", "wangkun" ); Java. SQL. Statement statement = Con. createstatement (); resultset result =Statement.exe cutequery (SQL); string resultstr = "" ;  While  (Result. Next () {resultstr + = "\ R \ n id =" + result. getstring (1 ); Resultstr + = ", Name =" + result. getstring ("name" );} MSG (resultstr );  //  Statement.exe cuteupdate (SQL );  }  Catch  (Exception ex) {MSG (ex. tostring ());}} Public   Static   Void  Excutebyparam () {string SQL = "Select * From 'user' where name =? " ;  Try  {Connection con = Null  ; Class. forname ( "Com. MySQL. JDBC. Driver" ). Newinstance (); con = Drivermanager. getconnection (databaseconurl, "Root", "wangkun"); Java. SQL. preparedstatement preparedsm = Con. preparestatement (SQL); preparedsm. setstring ( 1, "rhythmk" ); Resultset result = Preparedsm.exe cutequery (); string resultstr = "" ;  While  (Result. Next () {resultstr + = "\ R \ n id =" + result. getstring (1 ); Resultstr + = ", Name =" + result. getstring ("name" );} MSG ( "Excutebyparam: \ r \ n" +Resultstr );  //  Statement.exe cuteupdate (SQL );  }  Catch  (Exception ex) {MSG (ex. tostring ());}}  Public   Static   Void  MSG (string MSG) {system. Out. println (MSG );}}