目錄
JDBC概述
JDBC搭建
1、導入mysql開發商提供的jar包
2、注冊驅動
3、與數據庫連接
注解:?
Statement:
代碼?
運行
PreparedStatement:
代碼
運行?
PreparedStatement和Statement?
Statement?
增
代碼
運行
刪
代碼
運行
改
代碼
運行
PreparedStatement?
增
代碼
運行
刪
代碼
運行
改
代碼
運行
PreparedStatement和Statement 區別
結果集處理
代碼
運行
代碼
運行
?編輯代碼
運行
JDBC概述
jdbc(Java DataBase Connectivity)java語言連接數據庫
本模塊中,java提供了一組用來連接數據庫的類和接口
java語言開發者,本身沒有提供如何具體連接數據庫的功能,只是定義了一組java程序連接數據庫的訪問接口。
1、連接到數據庫
2、向數據庫發送增刪改這一類的sql
3、發送查詢sql
java語言只需要定義連接數據庫的接口,具體的實現交給了不同的數據庫開發商
java只需要定義一套接口,就可以連接不同的接口
JDBC搭建
1、導入mysql開發商提供的jar包
2、注冊驅動
兩種方式:
1、反射方式加載驅動類
? ? ? Class.forName("com.mysql.cj.jdbc.Driver");
2 、DriverManager.registerDriver(new Driver());
3、與數據庫連接
獲得Satement執行sql語句
發送sql語句
關閉與數據庫的連接
注解:?
url:?jdbc:mysql://127.0.0.1:3306:(端口(3306))
? ? ? /schooldb(數據庫名)
? ? ? ?serverTimezone=Asia/Shanghai"?
user:用戶名(root)
password:密碼
Statement:
代碼?
package com.ffyc.jdbcpro;import com.mysql.cj.jdbc.Driver;import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;public class Demo1 {public static void main(String[] args) throws ClassNotFoundException, SQLException {//2、注冊驅動 DriverManager.registerDriver(new Driver());//3、建立與數據庫的連接String url = "jdbc:mysql://127.0.0.1:3306/schooldb?serverTimezone=Asia/Shanghai";String user = "root";//個人數據庫密碼String password = "root";Connection connection = DriverManager.getConnection(url, user, password);System.out.println(connection);//com.mysql.cj.jdbc.ConnectionImpl@25359ed8//發送sqlStatement statement = connection.createStatement();System.out.println(statement);//com.mysql.cj.jdbc.StatementImpl@6166e06fstatement.executeUpdate("insert into major(name)value ('數學')");//關閉與數據庫的連接statement.close();connection.close();}
}
運行
PreparedStatement:
代碼
package com.ffyc.jdbcpro;import com.mysql.cj.jdbc.Driver;import java.sql.*;public class Demo3 {public static void main(String[] args) throws ClassNotFoundException, SQLException {//2、注冊驅動//反射方式加載驅動類//Class.forName("com.mysql.cj.jdbc.Driver");DriverManager.registerDriver(new Driver());//3、建立與數據庫的連接String url = "jdbc:mysql://127.0.0.1:3306/schooldb?serverTimezone=Asia/Shanghai";String user = "root";//個人數據庫密碼String password = "root";Connection connection = DriverManager.getConnection(url, user, password);//發送sql//? 占位符 表示要插入一個參數PreparedStatement ps = connection.prepareStatement("insert into major(name)value (?)");//1向第一個占位符的位置插入數據ps.setObject(1, "智能");ps.executeUpdate();//關閉與數據庫的連接ps.close();connection.close();}
}
運行?
PreparedStatement和Statement?
Statement?
增
代碼
package com.ffyc.jdbcpro;import com.mysql.cj.jdbc.Driver;import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;public class Demo2 {public static void main(String[] args) throws SQLException {Demo2.insert("元神啟","動", "2018-8-23", "12345678910", 1.4);}public static void insert(String name,String gender,String birthday,String phone,double height) throws SQLException {//注冊驅動DriverManager.registerDriver(new Driver());String url = "jdbc:mysql://127.0.0.1:3306/schooldb?serverTimezone=Asia/Shanghai";String user = "root";//個人數據庫密碼String password = "root";//建立與數據庫的連接Connection connection = DriverManager.getConnection(url, user, password);//發送sqlStatement statement = connection.createStatement();statement.executeUpdate("insert into student(name,gender,birthday,phone,height)value('"+name+"','"+gender+"','"+birthday+"','"+phone+"',"+height+")");//關閉與數據庫的連接statement.close();connection.close();}
}
運行
刪
代碼
package com.ffyc.jdbcpro;import com.mysql.cj.jdbc.Driver;import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;public class Demo2 {public static void main(String[] args) throws SQLException {Demo2.delete(6);}public static void delete(int number) throws SQLException {//注冊驅動DriverManager.registerDriver(new Driver());String url = "jdbc:mysql://127.0.0.1:3306/schooldb?serverTimezone=Asia/Shanghai";String user = "root";//個人數據庫密碼String password = "root";//建立與數據庫的連接Connection connection = DriverManager.getConnection(url, user, password);//發送sqlStatement statement = connection.createStatement();//直接將字符串拼接到sql中,不能防止sql注入攻擊"or 1=1"statement.executeUpdate("delete from student where number = "+number);//關閉與數據庫的連接statement.close();connection.close();}
}
運行
改
代碼
package com.ffyc.jdbcpro;import com.mysql.cj.jdbc.Driver;import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;public class Demo2 {public static void main(String[] args) throws SQLException {Demo2.update(3,"愛麗西婭","女", "2024-4-14", "123456789", 1.72);}public static void update(int number,String name,String gender,String birthday,String phone,double height) throws SQLException {//注冊驅動DriverManager.registerDriver(new Driver());String url = "jdbc:mysql://127.0.0.1:3306/schooldb?serverTimezone=Asia/Shanghai";String user = "root";//個人數據庫密碼String password = "root";//建立與數據庫的連接Connection connection = DriverManager.getConnection(url, user, password);//發送sqlStatement statement = connection.createStatement();statement.executeUpdate("update student set name='"+name+"',gender='"+gender+"',birthday='"+birthday+"',phone='"+phone+"',height="+height+" where number ="+number);//關閉與數據庫的連接statement.close();connection.close();}
}
運行
PreparedStatement?
增
代碼
package com.ffyc.jdbcpro;import com.mysql.cj.jdbc.Driver;import java.sql.*;public class Demo4 {public static void main(String[] args) throws SQLException {Demo4.insert("小姨","鶴", "2018-8-23", "123456789", 1.4);}public static void insert(String name,String gender,String birthday,String phone,double height) throws SQLException {//注冊驅動DriverManager.registerDriver(new Driver());String url = "jdbc:mysql://127.0.0.1:3306/schooldb?serverTimezone=Asia/Shanghai";String user = "root";//個人數據庫密碼String password = "root";//建立與數據庫的連接Connection connection = DriverManager.getConnection(url, user, password);//發送sqlPreparedStatement ps = connection.prepareStatement("insert into student(name,gender,birthday,phone,height)value(?,?,?,?,?)");ps.setObject(1,name);ps.setObject(2,gender);ps.setObject(3,birthday);ps.setObject(4,phone);ps.setObject(5,height);ps.executeUpdate();//關閉與數據庫的連接ps.close();connection.close();}
}
運行
刪
代碼
package com.ffyc.jdbcpro;import com.mysql.cj.jdbc.Driver;import java.sql.*;public class Demo4 {public static void main(String[] args) throws SQLException {Demo4.delete(15);}public static void delete(int number) throws SQLException {//注冊驅動DriverManager.registerDriver(new Driver());String url = "jdbc:mysql://127.0.0.1:3306/schooldb?serverTimezone=Asia/Shanghai";String user = "root";//個人數據庫密碼String password = "root";//建立與數據庫的連接Connection connection = DriverManager.getConnection(url, user, password);//發送sql//封裝sqlPreparedStatement ps = connection.prepareStatement("delete from student where number = ?");//向sql中傳參 傳參時會進行驗證,防止sql注入攻擊,更安全ps.setObject(1, number);ps.executeUpdate();//關閉與數據庫的連接ps.close();connection.close();}
}
運行
改
代碼
package com.ffyc.jdbcpro;import com.mysql.cj.jdbc.Driver;import java.sql.*;public class Demo4 {public static void main(String[] args) throws SQLException {Demo4.update(3,"申鶴","女", "2024-4-14", "12345678910", 1.72);}public static void update(int number,String name,String gender,String birthday,String phone,double height) throws SQLException {//注冊驅動DriverManager.registerDriver(new Driver());String url = "jdbc:mysql://127.0.0.1:3306/schooldb?serverTimezone=Asia/Shanghai";String user = "root";//個人數據庫密碼String password = "root";//建立與數據庫的連接Connection connection = DriverManager.getConnection(url, user, password);//發送sqlPreparedStatement ps = connection.prepareStatement("update student set name=?,gender=?,birthday=?,phone=?,height=? where number =?");ps.setObject(1, name);ps.setObject(2, gender);ps.setObject(3, birthday);ps.setObject(4, phone);ps.setObject(5, height);ps.setObject(6, number);ps.executeUpdate();//關閉與數據庫的連接ps.close();connection.close();}
}
運行
PreparedStatement和Statement 區別
1、代碼的可讀性和可維護性. 雖然用PreparedStatement來代替Statement會使代碼多出幾行,但這樣的代碼無 論從可讀性還是可維護性上來說.都比直接用Statement的代碼高很多檔次。
2、最重要的一點是極大地提高了安全性.
? ? ?防止sql注入
? ? ?Stringsql= “ delete from user where id = ”+num;
? ? ?如果我們把[or 1=1]作為id傳入進來?
? ? ?delete from tb_name where id = 1 or 1 = 1;
因為‘1’ = ‘1’肯定成立
而如果你使用預編譯語句.你傳入的任何內容就不會和原來的語句發生任何匹 配的關系.
預編譯模式中每個占位符處,只能插入一個值,而會過濾其他語句。
結果集處理
PreparedStatement和Statement中的executeQuery()方法中會返回一 個ResultSet對象,查詢結果就封裝在此對象中.
使用ResultSet中的next()方法獲得下一行數據
使用getXXX(String name)方法獲得值
package com.ffyc.jdbcpro;import java.util.Date;public class Student {private int number;private String name;private String gender;private Date birthday;private String phone;private double height;private Date ref_time;public int getNumber() {return number;}public void setNumber(int number) {this.number = number;}public String getName() {return name;}public void setName(String name) {this.name = name;}public String getGender() {return gender;}public void setGender(String gender) {this.gender = gender;}public Date getBirthday() {return birthday;}public void setBirthday(Date birthday) {this.birthday = birthday;}public String getPhone() {return phone;}public void setPhone(String phone) {this.phone = phone;}public double getHeight() {return height;}public void setHeight(double height) {this.height = height;}public Date getRef_time() {return ref_time;}public void setRef_time(Date ref_time) {this.ref_time = ref_time;}@Overridepublic String toString() {return "Student{" +"number=" + number +", name='" + name + '\'' +", gender='" + gender + '\'' +", birthday=" + birthday +", phone='" + phone + '\'' +", height=" + height +", ref_time=" + ref_time +'}';}
}
代碼
package com.ffyc.jdbcpro;import com.mysql.cj.jdbc.Driver;import java.sql.*;public class Demo5 {public static void main(String[] args) throws SQLException {Student student = Demo5.findStudentByNumber(16);System.out.println(student);}public static Student findStudentByNumber(int number) throws SQLException {DriverManager.registerDriver(new Driver());String url = "jdbc:mysql://127.0.0.1:3306/schooldb?serverTimezone=Asia/Shanghai";String user = "root";String password = "root";Connection connection = DriverManager.getConnection(url, user, password);PreparedStatement ps = connection.prepareStatement("select number,name,gender,birthday,height,phone,reg_time from student where number = ?");ps.setObject(1, number);//執行查詢后,mysql將數據封裝到一個ResultSet對象中ResultSet rs = ps.executeQuery();System.out.println(rs);//com.mysql.cj.jdbc.result.ResultSetImpl@64d2d351//將ResultSet中的數據包裝到自己的對象中,這樣用起來更加方便//結果集中有沒有數據 如果有返回true,否則返回falseStudent stu = new Student();while (rs.next()){stu.setNumber(rs.getInt("number"));stu.setName(rs.getString("name"));stu.setGender(rs.getString("gender"));stu.setBirthday(rs.getDate("birthday"));stu.setHeight(rs.getDouble("height"));stu.setPhone(rs.getString("phone"));stu.setRef_time(rs.getTimestamp("reg_time"));}return stu;}
}
運行
代碼
package com.ffyc.jdbcpro;import com.mysql.cj.jdbc.Driver;import java.sql.*;
import java.util.ArrayList;public class Demo6 {public static void main(String[] args) throws SQLException {ArrayList<Student> students = findStudent();System.out.println(students);}public static ArrayList<Student> findStudent() throws SQLException {DriverManager.registerDriver(new Driver());String url = "jdbc:mysql://127.0.0.1:3306/schooldb?serverTimezone=Asia/Shanghai";String user = "root";String password = "root";Connection connection = DriverManager.getConnection(url, user, password);PreparedStatement ps = connection.prepareStatement("select number,name,gender,birthday,height,phone,reg_time from student");ResultSet rs = ps.executeQuery();//創建一個集合對象用來封裝多個學生對象ArrayList<Student> students = new ArrayList<>();while (rs.next()){//每循環一次,創建一個學生對象Student stu = new Student();stu.setNumber(rs.getInt("number"));stu.setName(rs.getString("name"));stu.setGender(rs.getString("gender"));stu.setBirthday(rs.getDate("birthday"));stu.setHeight(rs.getDouble("height"));stu.setPhone(rs.getString("phone"));stu.setRef_time(rs.getTimestamp("reg_time"));//把當前學生對象返回到集合中students.add(stu);}return students;}
}
運行
代碼
package com.ffyc.jdbcpro;import com.mysql.cj.jdbc.Driver;import java.sql.*;public class Demo7 {public static void main(String[] args) throws SQLException {int res = checkNum(16);System.out.println(res);}public static int checkNum(int number) throws SQLException {DriverManager.registerDriver(new Driver());String url = "jdbc:mysql://127.0.0.1:3306/schooldb?serverTimezone=Asia/Shanghai";String user = "root";String password = "root";Connection connection = DriverManager.getConnection(url, user, password);PreparedStatement ps = connection.prepareStatement("select count(*) c from student where number = ?");ps.setObject(1, number);ResultSet resultSet = ps.executeQuery();if(resultSet.next()){return resultSet.getInt("c");}else{return 0;}}
}