目錄 1. MySQL準備 2. JDBC項目 3. JDBC新增 4. JDBC查詢 5. JDBC修改 6. JDBC刪除 內容 MySQL準備 新建表t_person CREATE TABLE `t_person` ( `id` int(11) NOT NULL AUTO_INCREMENT ...
目錄
1. MySQL準備
2. JDBC項目
3. JDBC新增
4. JDBC查詢
5. JDBC修改
6. JDBC刪除
內容
MySQL準備
- 新建表t_person
CREATE TABLE `t_person` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主鍵',
`name` varchar(30) NOT NULL COMMENT '姓名',
`birthdate` datetime NOT NULL COMMENT '出生日期',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4;
JDBC項目
- 創建項目
按照這篇博文搭建項目,博文地址
JDBC新增
註意新增步驟:
- 載入驅動
- 獲取Connection
- 創建Statement
- 執行SQL語句(註意要寫對)
- 關閉資源
package com.example.aji;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class Main {
public static void main(String[] args) {
try {
//載入驅動
Class.forName("com.mysql.cj.jdbc.Driver");
//mysql資料庫相關信息
String url="jdbc:mysql://localhost:3306/jdbc?useUnicode=true&characterEncoding=utf-8";
String user="root";
String password="123456789";
//獲取連接
Connection connection = DriverManager.getConnection(url,user,password);
Statement statement = connection.createStatement();
//新增語句
String insertSql="insert into t_person(name,birthdate) values('aji',now()),('aji',now())";
int result = statement.executeUpdate(insertSql);
//列印結果
System.out.println(result);
statement.close();
connection.close();
}catch (Exception e){
e.printStackTrace();
}
System.out.println("Hello world!");
}
}
JDBC查詢
註意查詢改驟:
- 載入驅動
- 獲取Connection
- 創建Statement
- 執行SQL語句(註意修改條件要寫對)
- 解析ResultSet
- 關閉資源
- ResultSet
-
next()
while (resultSet.next())開始時游標在第一行
第一次遍歷:
第二次遍歷:
第三次遍歷:
-
getInt(String columnLabel)
從當前游標指向的行中獲取欄位名為columnLabel的欄位的值,該值為int類型,該欄位類型必須為INT -
getLong(String columnLabel)
從當前游標指向的行中獲取欄位名為columnLabel的欄位的值,該值為long類型,該欄位類型必須為BIGINT -
getString(String columnLabel)
從當前游標指向的行中獲取欄位名為columnLabel的欄位的值,該值為String類型 -
getDate(String columnLabel)
從當前游標指向的行中獲取欄位名為columnLabel的欄位的值,該值為java.sql.Date類型
-
package com.example.aji;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class Main {
public static void main(String[] args) {
try {
//載入驅動
Class.forName("com.mysql.cj.jdbc.Driver");
//mysql資料庫相關信息
String url="jdbc:mysql://localhost:3306/jdbc?useUnicode=true&characterEncoding=utf-8";
String user="root";
String password="123456789";
//創建連接
Connection connection = DriverManager.getConnection(url,user,password);
Statement statement = connection.createStatement();
//查詢語句
String querySql="select * from t_person";
ResultSet resultSet = statement.executeQuery(querySql);
while (resultSet.next()) {
System.out.print(resultSet.getInt("id"));
System.out.print(resultSet.getString("name"));
System.out.println(resultSet.getDate("birthdate"));
}
boolean execute = statement.execute(querySql);
System.out.println(execute);
resultSet.close();
statement.close();
connection.close();
}catch (Exception e){
e.printStackTrace();
}
System.out.println("Hello world!");
}
}
JDBC修改
註意新修改驟:
- 載入驅動
- 獲取Connection
- 創建Statement
- 執行SQL語句(註意修改條件要寫對)
- 關閉資源
package com.example.aji;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class Main {
public static void main(String[] args) {
try {
//載入驅動
Class.forName("com.mysql.cj.jdbc.Driver");
//mysql資料庫相關信息
String url="jdbc:mysql://localhost:3306/jdbc?useUnicode=true&characterEncoding=utf-8";
String user="root";
String password="123456789";
//創建連接
Connection connection = DriverManager.getConnection(url,user,password);
Statement statement = connection.createStatement();
//修改語句
String updateSql="update t_person set name='xxxxx' where id=2";
int result = statement.executeUpdate(updateSql);
//列印結果
System.out.println(result);
//查詢語句
String querySql="select * from t_person";
ResultSet resultSet = statement.executeQuery(querySql);
while (resultSet.next()) {
System.out.print(resultSet.getInt("id"));
System.out.print(resultSet.getString("name"));
System.out.println(resultSet.getDate("birthdate"));
}
boolean execute = statement.execute(querySql);
System.out.println(execute);
resultSet.close();
statement.close();
connection.close();
}catch (Exception e){
e.printStackTrace();
}
System.out.println("Hello world!");
}
}
JDBC刪除
註意刪除步驟:
- 載入驅動
- 獲取Connection
- 創建Statement
- 執行SQL語句(註意刪除條件要寫對)
- 關閉資源
package com.example.aji;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class Main {
public static void main(String[] args) {
try {
//載入驅動
Class.forName("com.mysql.cj.jdbc.Driver");
//mysql資料庫相關信息
String url="jdbc:mysql://localhost:3306/jdbc?useUnicode=true&characterEncoding=utf-8";
String user="root";
String password="123456789";
//創建連接
Connection connection = DriverManager.getConnection(url,user,password);
Statement statement = connection.createStatement();
//刪除語句
String deleteSql="delete from t_person where id=2";
int result = statement.executeUpdate(deleteSql);
//列印結果
System.out.println(result);
//查詢語句
String querySql="select * from t_person";
ResultSet resultSet = statement.executeQuery(querySql);
while (resultSet.next()) {
System.out.print(resultSet.getInt("id"));
System.out.print(resultSet.getString("name"));
System.out.println(resultSet.getDate("birthdate"));
}
boolean execute = statement.execute(querySql);
System.out.println(execute);
resultSet.close();
statement.close();
connection.close();
}catch (Exception e){
e.printStackTrace();
}
System.out.println("Hello world!");
}
}