JavaWeb~02.JDBC(Java Database Connectivity) 資料庫連接技術
JDBC
是一種用于執行SQL陳述句的Java API，為多種關系資料庫提供統一訪問,它由一組用Java語言撰寫的類和介面組成，有了JDBC，程式員只需用JDBC API寫一個程式，就可訪問所有資料庫，
本文資料庫表
【宣告:本文資料庫內的所有內容純屬學習所用，如有雷同，純屬巧合!】
JDBC查詢所有資訊操作
public class Test{
public static void main(String[] args) throws ClassNotFoundException, SQLException {
String driver = "com.mysql.cj.jdbc.Driver";
String url = "jdbc:mysql://127.0.0.1:3306/mybatis?useSSL=false&useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai";
String username = "root";
String pwd = "root";
//加載一個Driver驅動
Class.forName(driver);
//創建資料庫的連接
Connection connection = DriverManager.getConnection(url,username,pwd);
//創建SQL命令發送器
Statement statement = connection.createStatement();
//向資料庫發送sql陳述句然后回傳引數
String sql = "select * from student";
ResultSet resultSet = statement.executeQuery(sql);
//遍歷輸出內容
while(resultSet.next()){
//getInt后面的引數值和資料庫里面的欄位相對應
//欄位的型別也要對應
Integer son = resultSet.getInt("son");
String realname = resultSet.getString("realname");
String password = resultSet.getString("password");
String classname = resultSet.getString("classname");
Double score = resultSet.getDouble("score");
System.out.println("["+ son + " " + realname + " " + password + " " +
classname + " " + score + "]");
}
//回收資源
connection.close();
statement.close();
resultSet.close();
}
}
JDBC訪問資料庫的步驟:
加載一個Driver驅動
創建資料庫連接（Connection）
創建SQL命令發送器Statement
通過Statement發送SQL命令并得到結果
處理結果（select陳述句）
關閉資料庫資源ResultSet Statement Connection
加載Driver驅動
使用反射加載驅動,其實就是獲得一個類的位元組碼,在獲得類的位元組碼的程序中,一定會加載類進入記憶體,一旦進入記憶體會執行代碼中的靜態代碼塊,一執行代碼塊,就會自動的向DriverManager中注冊一個驅動
mysql8 之前的資料庫驅動名 com.mysql.jdbc.Driver
mysql8 開始的資料庫驅動 com.mysql.cj.jdbc.Driver
通過DriverManager獲得鏈接
url 同一資源定位符
協議 jdbc:mysql:
ip地址 127.0.0.1/localhost
url 同一資源定位符
埠號 3306
具體的資源路徑 mybatis
mysql8之前: jdbc:mysql://127.0.0.1:3306/mybatis
mysql8開始: jdbc:mysql://127.0.0.1:3306/mybatis?useSSL=false&useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai
useSSL=false 不使用SSL加密機制
&useUnicode=true 使用unicode字符集
&characterEncoding=utf8 使用utf8作為通信字符集
&serverTimezone=Asia/Shanghai 確定時區為 Asia/Shanghai
其中的mybatis指的是資料庫名,可以根據需求修改
剛才的操作呢,是JDBC非常原始的一個操作,我們寫起來會感覺非常的麻煩，假如我們要寫多個查詢的操作,難道我們也要重復寫很多遍嘛?所以,這個時候,我們就需要對剛才的操作進行微微調整，
public class Test{
public static void main(String[] args){
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
//創建資料庫連接
connection = Util.getConnection();
//創建SQL命令發送器
statement = connection.createStatement();
//向資料庫發送sql陳述句然后回傳引數
String sql = "select * from student";
resultSet = statement.executeQuery(sql);
//遍歷輸出內容
while (resultSet.next()) {
//getInt后面的引數值和資料庫里面的欄位相對應
//欄位的型別也要對應
Integer son = resultSet.getInt("son");
String realname = resultSet.getString("realname");
String password = resultSet.getString("password");
String classname = resultSet.getString("classname");
Double score = resultSet.getDouble("score");
Student student = new Student(son, realname, password, classname, score);
System.out.println(student.toString());
}
}catch (Exception e){
e.printStackTrace();
}finally {
Util.CloseAll(connection,statement,resultSet);
}
}
}
//創建工具類
class Util{
private static String driver = "com.mysql.cj.jdbc.Driver";
private static String url = "jdbc:mysql://127.0.0.1:3306/mybatis?useSSL=false&useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai";
private static String username = "root";
private static String pwd = "root";
//將前面的操作封裝在一個方法里面,這樣就可以重復呼叫了
public static Connection getConnection(){
Connection connection = null;
try {
//加載一個Driver驅動
Class.forName(driver);
//創建資料庫的連接
connection = DriverManager.getConnection(url,username,pwd);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return connection;
}
public static void CloseAll(Connection connection,Statement statement,ResultSet resultSet){
if(connection != null){
try {
connection.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(statement != null){
try {
statement.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(resultSet != null){
try {
resultSet.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
//創建學生類
class Student implements Serializable {
private Integer son;
private String realname;
private String password;
private String classname;
private Double score;
public Student(Integer son, String realname, String password, String classname, Double score) {
this.son = son;
this.realname = realname;
this.password = password;
this.classname = classname;
this.score = score;
}
@Override
public String toString() {
return "Student{" +
"son=" + son +
", realname='" + realname + '\'' +
", password='" + password + '\'' +
", classname='" + classname + '\'' +
", score=" + score +
'}';
}
public Student() {
super();
}
}
這樣一來的話,我們后面不管寫多少次JDBC的操作,都可以省略很多行代碼了~
JDBC常見例外
Exception in thread "main"java.lang.ClassNotFoundException: com.mysql.jdbc2.Driver
原因：沒有添加jar包或者com.mysql.jdbc2.Driver路徑錯誤
Exception in thread "main" java.sql.SQLException: No suitable driver found for jbdc:mysql://127.0.0.1:3306/stumgr
url錯誤
Exception in thread "main" java.sql.SQLException: Access denied for user 'root'@'localhost' (using password: YES)
原因：用戶名或者密碼錯誤
Exception in thread "main" com.mysql.jdbc.exceptions .jdbc4.MySQLIntegrityConstraintViolationException:Duplicate entry '90' for key 'PRIMARY'
原因：主鍵沖突
JDBC增刪查改操作
需求:新增學生資訊
public static void Insert(String sql){
Connection connection = null;
Statement statement = null;
try {
//創建資料庫連接
connection = Util.getConnection();
//創建SQL命令發送器
statement = connection.createStatement();
int n = statement.executeUpdate(sql);
//回傳結果
String str = n > 0 ? "新增成功!" : "新增失敗!";
System.out.println(str);
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
Util.CloseAll(connection,statement,null);
}
}
需求:根據學號修改密碼
public static void main(String[] args){
//問號 占位符
Update("update student set password = ? where son = ?","123456",666666);
}
//修改學生資訊
public static void Update(String sql,String password,Integer son){
Connection connection = null;
PreparedStatement preparedStatement = null;
try{
//建立資料庫連接
connection = Util.getConnection();
//創建SQL命令發送器
preparedStatement = connection.prepareStatement(sql);
//回傳結果 前面的數字表示第幾個問號
preparedStatement.setString(1,password);
preparedStatement.setInt(2,son);
int n = preparedStatement.executeUpdate();
String str = n > 0 ? "修改成功!" : "修改失敗!";
System.out.println(str);
}catch (SQLException sqlException){
sqlException.printStackTrace();
}finally {
Util.CloseAll(connection,preparedStatement,null);
}
}
需求:根據學號洗掉學生資訊
public static void Del(String sql,Integer son){
Connection connection = null;
PreparedStatement preparedStatement = null;
try{
//建立資料庫連接
connection = Util.getConnection();
//創建SQL命令發送器
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1,son);
int n = preparedStatement.executeUpdate();
String str = n > 0 ? "洗掉成功!" : "洗掉失敗!";
System.out.println(str);
}catch (SQLException sqlException){
sqlException.printStackTrace();
}finally {
Util.CloseAll(connection,preparedStatement,null);
}
}
需求:通過學生的學號和密碼實作登錄
public static void Login(Integer son,String password){
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
Student student = null;
String sql = "select * from student where son = ? and password = ?";
try{
//建立資料庫連接
connection = Util.getConnection();
//創建SQL命令發送器
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1,son);
preparedStatement.setString(2,password);
resultSet = preparedStatement.executeQuery();
//判斷是否為空
if(resultSet.next()){
String realname = resultSet.getString("realname");
String classname = resultSet.getString("classname");
Double score = resultSet.getDouble("score");
student = new Student(son,realname,password,classname,score);
}
String str = student != null ? "登錄成功!" : "登錄失敗!";
System.out.println(str);
}catch (SQLException sqlException){
sqlException.printStackTrace();
}finally {
Util.CloseAll(connection,preparedStatement,resultSet);
}
}
從上述操作中,我們還發現一個弊端，就是很多代碼都重復了很多遍,那么,我們就再來完善一下工具類~
//用于增刪改
public static int executeUpdate(String sql,Object [] params) {
Connection conn = null;
PreparedStatement pstmt = null;
int n = 0;//添加失敗
try {
//建立和資料庫的連接
conn = getConnection();
//創建一個SQL命令發送器
pstmt = conn.prepareStatement(sql);
//準備好SQL陳述句，通過SQL命令發送器發送給資料庫，并得到結果
for (int i = 0; i <params.length ; i++) {
pstmt.setObject(i+1, params[i]);
}
n = pstmt.executeUpdate();
System.out.println(n);
} catch (SQLException e) {
e.printStackTrace();
} finally {
//關閉資源
CloseAll(conn,pstmt,null);
}
return n;
}
//用于查詢
public static <T> List<T> baseQuery(T t, String sql, Object ... args){
// 獲取list集合中要裝的物件的位元組碼
Class aClass = t.getClass();
Connection connection = null;
PreparedStatement statement = null;
ResultSet set = null;
List<T> list = null;
try {
connection = Util.getConnection();
statement = connection.prepareStatement(sql);
// 設定引數的程序
for (int i = 0; i < args.length; i++) {
statement.setObject(i + 1, args[i]);
}
set = statement.executeQuery();
// 獲取全部的欄位
Field[] fs = aClass.getDeclaredFields();
// 先設定屬性可以訪問
for(Field f:fs){
f.setAccessible(true);
}
list=new ArrayList<>();
while(set.next()){
// 創建物件
T element = (T)aClass.newInstance();
// 從結果集的一條資料中取出每個欄位的資訊,放入element物件上去
// 遍歷fs 通過屬性名 去結果集中獲取資料
for(Field f:fs){
String name = f.getName();
Object value=null;
// 判斷物體類屬性的資料型別,選擇對應的get方法
if(f.getType()==int.class){
value = set.getInt(name);
}else if(f.getType()==double.class){
value = set.getDouble(name);
}else if(f.getType()==boolean.class){
value = set.getBoolean(name);
}else{
value= set.getObject(name);
}
f.set(element,value);
}
list.add(element);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
CloseAll(connection,statement,set);
}
return list;
}
完善了剛剛的工具類以后,我們就來修改下前面的登錄操作~
注意:在此之前!記得Student類要補上 get set 方法
public static void Login(Integer son,String password){
String sql = "select * from student where son = ? and password = ?";
List<Student> list = Util.baseQuery(new Student(),sql,son,password);
String str = list.isEmpty() != true ? "登陸成功!" : "登陸失敗!";
System.out.println(str);
}
這里我們可以看到,代碼已經明顯簡潔很多了~那么,增刪改呢?
需求:增加操作完善
public static void main(String[] args){
Object[]objects = {653221,"張橋","998554","機電工程學院",566,"alvin"};
Insert("insert into student value(?,?,?,?,?,?)",objects);
}
//新增學生資訊
public static void Insert(String sql,Object []object){
int n = Util.executeUpdate(sql,object);
String str = n > 0 ? "新增成功!" : "新增失敗!";
System.out.println(str);
}
修改和洗掉就不在這里啰嗦了~
使用JDBC結合Socket實作遠程登錄
Student還是剛才那個Student,資料庫表也依然是剛剛的資料庫表
public class Student implements Serializable {
private Integer son;
private String realname;
private String password;
private String classname;
private Double score;
}//get set那些就不寫在文章里面了,但是實際操作需要添加，
完整的Util
public class Util {
private static String driver = "com.mysql.cj.jdbc.Driver";
private static String url = "jdbc:mysql://127.0.0.1:3306/mybatis?useSSL=false&useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai";
private static String username = "root";
private static String pwd = "root";
//將前面的操作封裝在一個方法里面,這樣就可以重復呼叫了
public static Connection getConnection(){
Connection connection = null;
try {
//加載一個Driver驅動
Class.forName(driver);
//創建資料庫的連接
connection = DriverManager.getConnection(url,username,pwd);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return connection;
}
public static void CloseAll(Connection connection, Statement statement, ResultSet resultSet){
if(connection != null){
try {
connection.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(statement != null){
try {
statement.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(resultSet != null){
try {
resultSet.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
//用于增刪改
public static int executeUpdate(String sql,Object [] params) {
Connection conn = null;
PreparedStatement pstmt = null;
int n = 0;//添加失敗
try {
//2.建立和資料庫的連接
conn = getConnection();
//3.創建一個SQL命令發送器
pstmt = conn.prepareStatement(sql);
//4.準備好SQL陳述句，通過SQL命令發送器發送給資料庫，并得到結果
for (int i = 0; i <params.length ; i++) {
pstmt.setObject(i+1, params[i]);
}
n = pstmt.executeUpdate();
//System.out.println(n);
} catch (SQLException e) {
e.printStackTrace();
} finally {
//6.關閉資源
CloseAll(conn,pstmt,null);
}
return n;
}
//用于查詢
public static <T> List<T> baseQuery(T t, String sql, Object ... args){
// 獲取list集合中要裝的物件的位元組碼
Class aClass = t.getClass();
Connection connection = null;
PreparedStatement statement = null;
ResultSet set = null;
List<T> list = null;
try {
connection = Util.getConnection();
statement = connection.prepareStatement(sql);
// 設定引數的程序
for (int i = 0; i < args.length; i++) {
statement.setObject(i + 1, args[i]);
}
set = statement.executeQuery();
// 獲取全部的欄位
Field[] fs = aClass.getDeclaredFields();
// 先設定屬性可以訪問
for(Field f:fs){
f.setAccessible(true);
}
list=new ArrayList<>();
while(set.next()){
// 創建物件
T element = (T)aClass.newInstance();
// 從結果集的一條資料中取出每個欄位的資訊,放入element物件上去
// 遍歷fs 通過屬性名 去結果集中獲取資料
for(Field f:fs){
String name = f.getName();
Object value=null;
// 判斷物體類屬性的資料型別,選擇對應的get方法
if(f.getType()==int.class){
value = set.getInt(name);
}else if(f.getType()==double.class){
value = set.getDouble(name);
}else if(f.getType()==boolean.class){
value = set.getBoolean(name);
}else{
value= set.getObject(name);
}
f.set(element,value);
}
list.add(element);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
CloseAll(connection,statement,set);
}
return list;
}
}
ClientTest客戶端
public class ClientTest {
public static void main(String[] args) throws IOException {
System.out.println("----------這里是客戶端----------");
Socket client = new Socket("127.0.0.1",8888);
//獲取輸出流
ObjectOutputStream objectOutputStream = new ObjectOutputStream(client.getOutputStream());
//獲得用戶輸入的資料
Student student = Login();
//發送給服務器,讓服務器判斷是否登陸成功
objectOutputStream.writeObject(student);
//接收服務器發過來的反饋
DataInputStream dataInputStream = new DataInputStream(client.getInputStream());
System.out.println(dataInputStream.readUTF());
//關閉
client.close();
objectOutputStream.close();
dataInputStream.close();
}
public static Student Login(){
Scanner scanner = new Scanner(System.in);
System.out.print("請輸入學號: ");
int son = scanner.nextInt();
System.out.print("請輸入密碼: ");
String password = scanner.next();
return new Student(son,null,password,null,null);
}
}
ServerTest服務器端
public class ServerTest {
public static void main(String[] args) throws IOException, ClassNotFoundException {
System.out.println("----------這里是服務器端----------");
//創建ServerSocket物件
ServerSocket serverSocket = new ServerSocket(8888);
//監聽客戶端是否鏈接
Socket server = serverSocket.accept();
//獲得客戶端發送過來的資料
ObjectInputStream objectInputStream = new ObjectInputStream(server.getInputStream());
Student student = (Student) objectInputStream.readObject();
//輸入sql陳述句,進入資料庫內查詢
String sql = "select * from student where son = ? and password = ?";
List<Student> list = Util.baseQuery(new Student(),sql,student.getSon(),student.getPassword());
//判斷是否登陸成功,然后告訴客戶端
String str = list.isEmpty() != true ? "登陸成功!" : "登陸失敗!";
//發送結果給客戶端
DataOutputStream dataOutputStream = new DataOutputStream(server.getOutputStream());
dataOutputStream.writeUTF(str);
//關閉
server.close();
objectInputStream.close();
dataOutputStream.close();
}
}
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/148006.html
標籤：其他