實訓第2天的主要任務是使用jdbc技術連接配接MySQL資料庫,對資料庫表中的資料實作增、删、改、查等基本功能。
JDBC的7個步驟:
1.加載驅動
2.建立連接配接
3.寫sql語句
4.得到statement對象
5.執行sql,得到結果集
6.處理結果集
7.關閉資源
最終包結構:
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLiAzNfRHLGZkRGZkRfJ3bs92YsYTMfVmepNHLwkkaNBTR61UMRpHW4Z0MMBjVtJWd0ckW65UbM5WOHJWa5kHT20ESjBjUIF2X0hXZ0xCMx81dvRWYoNHLrdEZwZ1Rh5WNXp1bwNjW1ZUba9VZwlHdssmch1mclRXY39CXldWYtlWPzNXZj9mcw1ycz9WL49zZuBnLyIjN2IDMwAjMyAzNwkTMwIzLc52YucWbp5GZzNmLn9Gbi1yZtl2Lc9CX6MHc0RHaiojIsJye.png)
下面是具體的實作過程:
(1)打開ieda,選擇Create New Project,建立一個項目;
(2)選擇Java,Project SDK框選配置好的jdk,點選Next;
(3)不勾選Create project from template,點Next;
(4)填寫項目名,選擇項目位址,點選Finish。
(5)打開SQLyog,建立資料庫(我的是idea),在資料庫中建立表(userinfo),表的設定如下:
(6)先手動輸入幾個資料,在後面會用到;
(7)在idea的項目名處右鍵->New->Directory,建立Directory:lib
(8)把mysql-connector-java-5.1.36.jar資源包移入lib目錄下,選中資源包右鍵->Add as Library->OK;
(9)在src目錄下建立Package(我的命名:com.song)
(10)在song目錄下建立class:UserInfo
package com.song;
public class UserInfo {
public UserInfo(String username, String password){
this.username = username;
this.password = password;
}
private int id;
private String username;
private String password;
@Override
public String toString(){
return "UserInfo{" +
"id=" + id +
", username='" + username + '\'' +
", password='" + password + '\'' +
'}';
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
}
(11)在song目錄下建立class:query(注意在第2步建立連接配接時,設定為自己資料庫名、使用者名、密碼)
package com.song;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class query {
public static void main(String[] args){
ResultSet resultSet = null;
PreparedStatement statement = null;
Connection connection = null;
List<UserInfo> userInfos = new ArrayList<>();
try{
// 1.加載驅動
Class.forName("com.mysql.jdbc.Driver");
//2.建立連接配接
connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/idea?useSSL=true&"
+ "characterEncoding=utf-8&user=root&password=root");
//3.寫sql
String sql = "select * from userinfo";
//4.得到statement對象
statement = connection.prepareStatement(sql);
//5.執行sql,得到結果集
resultSet = statement.executeQuery();
//6.處理結果集
while(resultSet.next()){
int id = resultSet.getInt(1);
String username = resultSet.getString(2);
String password = resultSet.getString(3);
UserInfo userInfo = new UserInfo(username,password);
userInfo.setId(id);
userInfos.add(userInfo);
}
System.out.println(userInfos);
//7.關閉資源
}catch(Exception e){
e.printStackTrace();
}finally {
if(resultSet != null){
try{
resultSet.close();
}catch(SQLException e) {
e.printStackTrace();
}
}
if(statement != null){
try{
statement.close();
}catch(SQLException e) {
e.printStackTrace();
}
}
if(connection != null){
try{
connection.close();
}catch(SQLException e) {
e.printStackTrace();
}
}
}
}
}
(12)這時已經實作了查詢功能,運作query裡的主函數,可以輸出查詢結果;
(13)同理實作增、删、改功能。建立class:add
package com.song;
import java.sql.*;
public class add {
public static void main(String[] args){
Connection connection = null;
PreparedStatement statement = null;
try {
// 1.加載驅動
Class.forName("com.mysql.jdbc.Driver");
//2.建立連接配接
connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/idea?useSSL=true&"
+ "characterEncoding=utf-8&user=root&password=root");
//3.寫SQL
String sql = "insert into userinfo(id,username,password) values(?,?,?)";
//4.得到statement對像
statement = connection.prepareStatement(sql);
//添加的使用者資訊
statement.setInt(1,5);
statement.setString(2,"song");
statement.setString(3,"song");
//5.執行sql,得到結果集
statement.executeUpdate();
//6.處理結果集
//7.關閉資源
}catch(Exception e){
e.printStackTrace();
}finally {
if(statement != null){
try{
statement.close();
}catch(SQLException e) {
e.printStackTrace();
}
}
if(connection != null){
try{
connection.close();
}catch(SQLException e) {
e.printStackTrace();
}
}
}
}
}
重新整理資料庫,檢視運作結果
(14)建立class:delete
package com.song;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class delete {
public static void main(String[] args){
Connection connection = null;
PreparedStatement statement = null;
try {
//1.加載驅動
Class.forName("com.mysql.jdbc.Driver");
//2.建立連接配接
connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/idea?useSSL=true&"
+ "characterEncoding=utf-8&user=root&password=root");
//3.寫sql
String sql = "delete from userinfo where id=5"; //删除的id
//4.得到statement對象
statement = connection.prepareStatement(sql);
//5.執行sql,得到結果集
statement.executeUpdate();
//6.處理結果集
//7.關閉資源
}catch(Exception e){
e.printStackTrace();
}finally {
if(statement != null){
try {
statement.close();
}catch(SQLException e){
e.printStackTrace();
}
}
if(connection != null){
try{
connection.close();
}catch(Exception e) {
e.printStackTrace();
}
}
}
}
}
重新整理資料庫,檢視運作結果
(15)建立class:update
package com.song;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class update {
public static void main(String[] args){
Connection connection = null;
PreparedStatement statement = null;
try {
//1.加載驅動
Class.forName("com.mysql.jdbc.Driver");
//2.建立連接配接
connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/idea?useSSL=true&"
+ "characterEncoding=utf-8&user=root&password=root");
//3.寫SQL
String sql = "update userinfo set username='SONG',password='SONG' where id=1";
//4.得到statement對象
statement = connection.prepareStatement(sql);
//5.執行sql,得到結果集
statement.executeUpdate();
//6.處理結果集
//7.關閉資源
}catch(Exception e){
e.printStackTrace();
}finally {
if(statement!=null){
try{
statement.close();
}catch (SQLException e){
e.printStackTrace();
}
}
if(connection!=null){
try{
connection.close();
}catch (Exception e){
e.printStackTrace();
}
}
}
}
}
重新整理資料庫,檢視運作結果