Java, database connection and basic operations

To connect to a database:

Connection Connection Variable =drivermanager.getconnection (dburl, user account , password ) ;

If the connection succeeds, a Connection object is returned, and all subsequent operations on the database can be made using this object. such as Connection con = drivermanager.getconnection ("Jdbc:mysql://127.0.0.1:3306/testdata", "root", "1234");

3. establish the SQL Statement object: the Statement object is used to send SQL commands to the database .

Statement Stmt=conn. Createstatement ();

4. Execute the SQL statement:

We can query, modify, or add data to the database through SQL statements. Once the SQL statement object is established, the executeQuery (String s) method can be used to execute the Select statement, using the executeupdate (String s) to perform Insert , Delete , Update statement.

5. result Processing (course not required).

Use the next method of ResultSet to move through the result set , moving one row at a time . in each row , ResultSet provides a variety of GetX Method . They are argument by column name or index . as getInt getString and Other methods .

6. Close the connection :

Con.close ();

Some operations of the database:

For example:

Books ( ISBN, title, publishing house, pricing, author )

Reader ( number, name, unit, gender )

Employee (Employee number, name, gender, leader, age)

Borrowing ( borrowing Number, ISBN, reader number, borrowing time, book time, late return fee, damage fee, should be paid, handling people )

Simple query:

(1) inquire about the employee information of all surname Wang.

SELECT *

From employees

WHERE name like ' king %'

(2) enquiries about female employees of the year-old.

SELECT *

FORM Staff

WHERE Age =20 and sex =' woman '

(3) inquire about the book information published by the electronic industry publishing house

SELECT *

From Books

WHERE Publishing house =' electronic industry Press '

(4) statistics on the number of female workers over the age of five.

SELECT COUNT (*) as number of people

From employees

WHERE Age >30 and sex = ' woman '

Some basic operations after the connection:

1. Simple query:

rs = Stmt.executequery ("Select School Number , name from fromuser2 where name like ' % ' ");

while (Rs.next ())

{

String numname =rs.getstring (" study number ");

String name =rs.getstring (" name ");

int Age=rs.getint (" Age ");

System.out.println (numname+ "" +name+ "");

}

2. Paging query:

int number=0;

rs = Stmt.executequery ("Select COUNT ( name ) n from Fromuser2");

while (Rs.next ())

{

Number=rs.getint ("n");

}

int num=3;

int page;

String SQL;

For (page=1;page<= (number%num>0. Number/num+1:number/num);p age++) {

System.out.println (" +page+ " page : ");

Sql= "Select School Number , name , age from Fromuser2 where age > 0 Limit "+ (page-1) *num+", "+num;

SYSTEM.OUT.PRINTLN (SQL);

rs = stmt.executequery (SQL);

while (Rs.next ())

{

String numname =rs.getstring (" study number ");

String name =rs.getstring (" name ");

int Age=rs.getint (" Age ");

System.out.println (numname+ "" +name+ "" +age ");

}

}

3. Delete:

int numb = stmt.executeupdate ("Delete from Fromuser2 where name = ' Zhang An '");

if (numb>0) {

System.out.println (" Delete succeeded " + " deleted " +numb+ " several " );

}

else if (numb==0)

{

System.out.println (" Delete failed ");

4. Update:

int numbe = stmt.executeupdate ("Update fromuser2 set age =28 where name = ' Zhang An ' ");

if (numbe>0) {

System.out.println (" Insert success ");

}

else if (numb==0)

{

System.out.println (" insert failed ");

Java, database connection and basic operations