Java Link MySQL transfer data

MySQL section

1. First build MySQL database data table

① run-mysql–u root–p Enter password (set when MySQL is installed) to open MySQL

② CREATE database database name;

show databases; View Database

drop database name; Deleting a database

③ Select database use database name;

④ Creating a data table

CREATE TABLE Table name

(

Field Name 1 data type constraints,

Field Name 2 data type constraints,

... ...

Field name N data type constraint condition

);

MySQL data type

Character: char (number of character data size) immutable character/varchar (data size) variable character/text

Number: float/double (7,2) int

Date: Date/time/datetime/timestamp

Mysql constraint conditions

PRIMARY KEY constraint: Primary key-a flag that identifies all record uniqueness, the corresponding data cannot be null, and cannot be duplicated

nonempty constraint: NOT null--the field information must be filled in and cannot be populated with null

Unique constraint: Unique--The field information cannot have re-information

FOREIGN KEY constraint: foreign key--reference the data information in the primary table from the table

Check constraint: Check-checks the current input input validity

Default value: Default-fills with defaults when the user does not fill in the data

Eg:

Create TableUserInfo (IDint Primary Keyauto_increment, #编号, shaping, primary key, auto grow usernamevarchar( -)Unique, #唯一性 passwordvarchar( -) not NULL, #非空 sexChar(1)default 'Demon', #默认值ageint, Birth dateNULL#允许为空);

⑤ view current database presence table show tables;

⑥ View table structure information desc table name;

⑦ Delete data table drop table name;

⑧ Adding data

Inset into table name (field name 1, field name 2,.....) VALUES (value 1, value 2,.....);

Insert into UserInfo (Username,password,sex,age,birth) VALUES (' Zhang San ', ' 111 ', ' Male ', 22, ' 2000-1-1 ');

⑨ Viewing data

Select Field Name 1, field name 2,..... from table name;

Select Id,username,password,sex,age,birth from UserInfo;

⑩ Deleting data

Delete from table name where condition data;

Delete from userinfo where id=1;

JAVA section

2. Writing Java files

Main steps:

1 adding drivers

2 Linked databases

3 Operating data

4 Close Link

① Add driver: Import Mysql-connector-java-bin.jar file, right-click Build path to reference external jar package

Try {            //  Class.forName Get class object                class.forname ("Com.mysql.jdbc.Driver" );         Catch (ClassNotFoundException e) {               //  TODO auto-generated catch block                      E.printstacktrace ();                     SYSTEM.OUT.PRINTLN ("database driver failed to load ...");        

② Connection Database:

Try {                     //dirvermanager.getconnection (); Connect to database                       =  Drivermanager.getconnection (URL,USER,PWD);               Catch (SQLException e) {                     //  TODO auto-generated catch block                      E.printstacktrace ();                     SYSTEM.OUT.PRINTLN ("database connection Failed");              }

③ Operating Database

Add action

              //String sql = "INSERT into UserInfo (Username,password,sex,age,birth) VALUES (' Zere ', ' 666 ', ' female ', 24, ' 1994-4-17 ')"; //Delete OperationString SQL= "Delete from userinfo where id=1";Try {                     //The statement method creates the object used to execute the SQL statement and returns the results it generates, using the connection method createstatement createSTA=conn.createstatement (); //The statement interface has a executeupdate (String sql) method that sends an SQL statement and returns the number of records that executed successfully                     intnum =sta.executeupdate (SQL); if(Num >0) {System.out.println ("Database operation succeeded"); }Else{System.out.println ("Database operation failed 1"); }              } Catch(SQLException e) {//TODO auto-generated Catch blockE.printstacktrace (); System.out.println ("Database operation failed 2"); }      

④ Closing Database Operations objects

Try {                     sta.close ();                     Conn.close ();               Catch (SQLException e) {                     //  TODO auto-generated catch block                      E.printstacktrace ();              }

⑤ running the database connection method

 Public Static void Main (string[] args) {                           new  mysqllink ();              Link.init ();       }

Integration code:

 PackageCom.cz.link;Importjava.sql.Connection;ImportJava.sql.DriverManager;Importjava.sql.SQLException;Importjava.sql.Statement; Public classMysqllink {/*link Step 1. Add driver 2. Connect to database 3. Operation data 4. Close link*/String URL= "Jdbc:mysql://localhost:3306/javass"; String User= "Root"; String pwd= "123456"; //declaring linked objectsConnection Conn=NULL; String SQL=NULL;  PublicStatement STA =NULL;  Public voidinit () {//1. Load Driver                           Try {                     //Class.forName Get Class objectClass.forName ("Com.mysql.jdbc.Driver"); } Catch(ClassNotFoundException e) {//TODO auto-generated Catch blockE.printstacktrace (); System.out.println ("Database-driven load failed ..."); }                                  //2. Connect to the database                           Try {                     //dirvermanager.getconnection (); Connect to DatabaseConn=drivermanager.getconnection (URL,USER,PWD); } Catch(SQLException e) {//TODO auto-generated Catch blockE.printstacktrace (); System.out.println ("Database connection Failed"); }                           //3. Data Manipulation//Add Action//String sql = "INSERT into UserInfo (Username,password,sex,age,birth) VALUES (' Zere ', ' 666 ', ' female ', 24, ' 1994-4-17 ')"; //Delete OperationString SQL= "Delete from userinfo where id=1";Try {                     //The statement method creates the object used to execute the SQL statement and returns the results it generates, using the connection method createstatement createSTA=conn.createstatement (); //the Statement interface has executeupdate (String SQL); method sends SQL and returns the number of records that executed successfully                     intnum =sta.executeupdate (SQL); if(Num >0) {System.out.println ("Database operation succeeded"); }Else{System.out.println ("Database operation failed 1"); }              } Catch(SQLException e) {//TODO auto-generated Catch blockE.printstacktrace (); System.out.println ("Database operation failed 2"); }                           //4. Close the database Operations object              Try{sta.close ();              Conn.close (); } Catch(SQLException e) {//TODO auto-generated Catch blockE.printstacktrace (); }       }              Public Static voidMain (string[] args) {Mysqllink link=NewMysqllink ();       Link.init (); }}

Java Link MySQL transfer data