18th Chapter JDBC

1. Connect to the database using JDBC:

A. Importing drive jar packages
B.class.forname (DRIVER);
2. Establish a connection
con = drivermanager.getconnection (URL, USERNAME, PWD);

2. Add, Delete, change: (As long as the SQL can be modified to achieve)
Import java.sql.Connection;
Import Java.sql.DriverManager;
Import java.sql.SQLException;
Import java.sql.Statement;

public class Upadatetest {
public static final String DRIVER = "Com.mysql.jdbc.Driver";
public static final String URL = "Jdbc:mysql://localhost:3306/petstore";
public static final String USERNAME = "root";
public static final String PWD = "root";

public static void Main (string[] args) {
Connection con = null;
Statement Statement = null;　　　　　　　　
try {
Class.forName (DRIVER);
con = drivermanager.getconnection (URL, USERNAME, PWD);
statement = Con.createstatement ();
String sql = "Update pet set name= ' fluffy ' where id=2";
int result = statement.executeupdate (SQL);///Change/delete
if (Result > 0) {
System.out.printl N ("modified successfully");　　　　　　　　
} else {
System.out.println ("modification Failed");
　　　　　　}
} catch (ClassNotFoundException e) {
E.printstacktrace ();
　　　　　　} catch (SQLException e) {
E.printstacktrace ();
　　　　　　　　　　}finally{
Try {
Statement.close ();
Con.close ();　　　　　　　　
} catch (SQLException e) {
E.printstacktrace ();
}
}
}
}

3. Enquiry:
Import java.sql.Connection;
Import Java.sql.DriverManager;
Import Java.sql.ResultSet;
Import java.sql.SQLException;
Import java.sql.Statement;

public class Querytest {
public static final String DRIVER = "Com.mysql.jdbc.Driver";
public static final String URL = "Jdbc:mysql://localhost:3306/petstore";
public static final String USERNAME = "root";
public static final String PWD = "root";

public static void Main (string[] args) {
Connection con = null;
Statement Statement = null;
ResultSet Rs=null;
try {
Class.forName (DRIVER);
con = drivermanager.getconnection (URL, USERNAME, PWD);
statement = Con.createstatement ();
String sql = "SELECT * from Pet";
rs = statement.executequery (SQL);
while (Rs.next ()) {
System.out.print (Rs.getint (1) + "\ T");
System.out.print (rs.getstring (2) + "\ T");
System.out.print (Rs.getint (3) + "\ T");
System.out.print (Rs.getint (4) + "\ T");
System.out.print (rs.getstring (5) + "\ n");
}
} catch (ClassNotFoundException e) {
E.printstacktrace ();
} catch (SQLException e) {
E.printstacktrace ();
}finally{
try {
Rs.close ();
Statement.close ();
Con.close ();
} catch (SQLException e) {
E.printstacktrace ();
}
}

}
}

The difference between the 4.statement methods:
Execute infrequently used return value is Boolean type, true refers to return result set
Executeupdate increment, delete, change return int type, refers to the number of rows affected
ExecuteQuery returns the ResultSet type, which refers to the result set

5.prepareStatement Avoid SQL injection exceptions
How to use:
Import java.sql.Connection;
Import Java.sql.DriverManager;
Import java.sql.PreparedStatement;
Import Java.sql.ResultSet;
Import java.sql.SQLException;
Import java.util.ArrayList;
Import java.util.List;

public class Preptest {
public static final String DRIVER = "Com.mysql.jdbc.Driver";
public static final String URL = "Jdbc:mysql://localhost:3306/petstore";
public static final String USERNAME = "root";
public static final String PWD = "root";

public static void Main (string[] args) {
Connection con = null;
PreparedStatement PS = null;
ResultSet rs = null;
list<master> masterlst = new arraylist<master> ();
try {
1 Load Driver
Class.forName (DRIVER);
2 Creating a connection
con = drivermanager.getconnection (URL, USERNAME, PWD);
3 Declaring SQL
StringBuffer sql = new StringBuffer ("SELECT * from Master where 1=1");
Sql.append ("and name=?");
Sql.append ("and money=?");
String sql= "SELECT * from Master";
4. Create a PreparedStatement object
PS = con.preparestatement (SQL);
5. Pass Parameters
Ps.setstring (1, "Li");
Ps.setint (2, 100);
6. Execute SQL
rs = Ps.executequery ();
7 times Calendar Display
while (Rs.next ()) {
Master m = new Master ();
M.setid (Rs.getint (1));
M.setname (rs.getstring (2));
M.setpassword (Rs.getstring (3));
M.setmoney (Rs.getint (4));
Masterlst.add (m);
}
} catch (ClassNotFoundException e) {
E.printstacktrace ();
} catch (SQLException e) {
E.printstacktrace ();
} finally {
8. Close objects//close from inside to outside
try {
Rs.close ();
Ps.close ();
Con.close ();
} catch (SQLException e) {
E.printstacktrace ();
}
}

SYSTEM.OUT.PRINTLN ("Query result:");
for (Master m:masterlst) {
System.out.println (M.getid () + "\ T" +m.getname () + "\ T" +m.getpassword () + "\ T" +m.getmoney ());
}
}

}

18th Chapter JDBC