package jdbc; import java.sql.Connection; import java.sql.DriverManager;
import java.sql.Statement; public class demo { public static void main(String[]
args) throws Exception { // Register driver Class.forName("com.mysql.cj.jdbc.Driver");
// Registration method String url = "jdbc:mysql://127.0.0.1:3306/itcast?" +
"useSSL=false&&serverTimezone=UTC&useUnicode=true&characterEncoding=UTF-8";
String username = "root"; String password = "gyx.l123"; Connection conn =
DriverManager.getConnection(url, username, password); // definition SQL sentence String sql =
"insert into users values(4,' Zhang Liu ',30,'1',' male ',2)"; // obtain statement object Statement sm =
conn.createStatement(); // implement sql int count = sm.executeUpdate(sql);// Number of rows affected
// Processing results System.out.println(count); // Release resources sm.close(); conn.close(); } }
JDBC API Explain in detail
DriverManager
Connection
import java.sql.Connection; import java.sql.DriverManager; import
java.sql.Statement; public class demo2 { public static void main(String[] args)
throws Exception { // Registration method String url = "jdbc:mysql://127.0.0.1:3306/itcast?" +
"useSSL=false&&serverTimezone=UTC&useUnicode=true&characterEncoding=UTF-8";
String username = "root"; String password = "gyx.l123"; Connection conn =
DriverManager.getConnection(url, username, password); // definition SQL sentence String sql1 =
"update users set age = 30 where name = ' Zhang San '"; String sql2 = "update users set
age = 32 where name = ' Zhang Si '"; // obtain statement object Statement sm =
conn.createStatement(); try { // Open transaction （ Manual submission ） conn.setAutoCommit(false);
// implement sql1, And process the results int count1 = sm.executeUpdate(sql1);// Number of rows affected
System.out.println(count1); // implement sql2, And process the results int count2 =
sm.executeUpdate(sql1);// Number of rows affected System.out.println(count1); // Commit transaction
conn.commit(); } catch (Exception e) { // Rollback transaction conn.rollback(); } // Release resources
sm.close(); conn.close(); } }
Statement
DDL Statement cannot be used count The value of determines whether the execution is successful , as "drop database username" DML Statement can be used count The value of determines whether the execution is successful
ResultSet
import java.sql.Connection; import java.sql.DriverManager; import
java.sql.ResultSet; import java.sql.Statement; public class demo_ResultSet {
public static void main(String[] args) throws Exception { // Registration method String url =
"jdbc:mysql://127.0.0.1:3306/itcast?" +
"useSSL=false&&serverTimezone=UTC&useUnicode=true&characterEncoding=UTF-8";
String username = "root"; String password = "gyx.l123"; Connection conn =
DriverManager.getConnection(url, username, password); // definition SQL sentence
//DDL Statement cannot be used count The value of determines whether the execution is successful , as "drop database username" //DML Statement can be used count The value of determines whether the execution is successful
String sql = "select * from users"; // obtain statement object Statement sm =
conn.createStatement(); // implement sql ResultSet res = sm.executeQuery(sql); // Processing results
while (res.next()){ int id = res.getInt(1); String name = res.getString(2); int
age = res.getInt(3); String gender = res.getString(4); int dep = res.getInt(5);
System.out.println(id+"\t"+name+"\t"+age+"\t"+gender+"\t"+dep); } // Release resources
res.close(); sm.close(); conn.close(); /* 1 Zhang San 30 male 1 2 Zhang Si 28 male 2 3 Zhang Wu 22 female 3 4
Zhang Liu 30 male 2 5 Wang Liu 28 male 1 6 Wang Wu 28 male 1 */ } }
import pojo.users; import java.sql.Connection; import java.sql.DriverManager;
import java.sql.ResultSet; import java.sql.Statement; import
java.util.ArrayList; public class demo_ResultSet2 { public static void
main(String[] args) throws Exception { // Registration method String url =
"jdbc:mysql://127.0.0.1:3306/itcast?" +
"useSSL=false&&serverTimezone=UTC&useUnicode=true&characterEncoding=UTF-8";
String username = "root"; String password = "gyx.l123"; Connection conn =
DriverManager.getConnection(url, username, password); // definition SQL sentence String sql =
"select * from users"; // obtain statement object Statement sm = conn.createStatement();
// implement sql ResultSet res = sm.executeQuery(sql); // Processing results ArrayList<users> arr = new
ArrayList<users>(); while (res.next()){ // create object users u =new users(); // get data int
id = res.getInt(1); String name = res.getString(2); int age = res.getInt(3);
String gender = res.getString(4); int dep = res.getInt(5); // Assign values to objects u.setId(id);
u.setName(name); u.setAge(age); u.setGender(gender); u.setDep(dep); // Adding objects to a collection
arr.add(u); } // Print set System.out.println(arr); // Release resources res.close(); sm.close();
conn.close(); } } public class users { private int id; private String name;
private int age; private String gender; private int dep; public int getId() {
return id; } public void setId(int id) { this.id = id; } public String
getName() { return name; } public void setName(String name) { this.name = name;
} public int getAge() { return age; } public void setAge(int age) { this.age =
age; } public String getGender() { return gender; } public void
setGender(String gendert) { this.gender = gendert; } public int getDep() {
return dep; } public void setDep(int dep) { this.dep = dep; } @Override public
String toString() { return "users{" + "id=" + id + ", name='" + name + '\'' +
", age=" + age + ", gendert='" + gender + '\'' + ", dep=" + dep + '}'; } }
PreparedStatement
Login case ：
import java.sql.Connection; import java.sql.DriverManager; import
java.sql.ResultSet; import java.sql.Statement; public class
demo_PreparedStatement { public static void main(String[] args) throws
Exception { // Registration method String url = "jdbc:mysql://127.0.0.1:3306/itcast?" +
"useSSL=false&&serverTimezone=UTC&useUnicode=true&characterEncoding=UTF-8";
String username = "root"; String password = "gyx.l123"; Connection conn =
DriverManager.getConnection(url, username, password); // Receive the user name and password entered by the user String
name = " Xiao Hong "; String psw = "123"; //String name = "3326988"; //String psw = "'or
'1'='1"; // definition SQL sentence String sql = "select * from username_password where username
= '" + name + "' and password='" + psw + "'"; // obtain statement object Statement sm =
conn.createStatement(); // implement sql ResultSet res = sm.executeQuery(sql);
// Determine whether the login is successful if(res.next()){//res Have data , return true System.out.println(" Login successful "); }else{
System.out.println(" Login failed "); } // Release resources res.close(); sm.close(); conn.close(); } }
import java.sql.*; public class demo_PreparedStatement { public static void
main(String[] args) throws Exception { // Registration method String url =
"jdbc:mysql://127.0.0.1:3306/itcast?" +
"useSSL=false&&serverTimezone=UTC&useUnicode=true&characterEncoding=UTF-8";
String username = "root"; String password = "gyx.l123"; Connection conn =
DriverManager.getConnection(url, username, password); // Receive the user name and password entered by the user String
name = " Xiao Hong "; String psw = "123"; // definition SQL sentence String sql = "select * from
username_password where username = ? and password = ?"; // obtain
PreparedStatement object , And set ? Value of PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, name); ps.setString(2, psw); // implement sql ResultSet res =
ps.executeQuery(); // Determine whether the login is successful if (res.next()) {//res Have data , return true
System.out.println(" Login successful "); } else { System.out.println(" Login failed "); } // Release resources
res.close(); ps.close(); conn.close(); } }
Database connection pool Druid
import com.alibaba.druid.pool.DruidDataSourceFactory; import
javax.sql.DataSource; import java.io.FileInputStream; import
java.sql.Connection; import java.util.Properties; public class druid_Demo {
public static void main(String[] args) throws Exception { //1. guide jar package //2. configuration file
//3. Load profile Properties prop = new Properties(); prop.load(new
FileInputStream("jdbc-demo/src/druid.properties"));
//System.out.println(System.getProperty("user.dir")); How to find the current path //4. Get connection pool object
DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
//5. Get database connection Connection Connection conn = dataSource.getConnection();
System.out.println(conn); } }
Technology