您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

JDBC 連接 MySQL 實戰：基於 Statement 的增刪改查完整示例

編輯：MySQL 綜合教學

準備工作：建表語句

CREATE TABLE t1 (
    id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
    name VARCHAR(32),
    password VARCHAR(32)
);
INSERT INTO t1(name,password) VALUES('admin','123');
INSERT INTO t1(name,password) VALUES('zhangsan','123');
INSERT INTO t1(name,password) VALUES('lisi','123');

核心 DAO 類：mysqlDao.java

封裝了驅動加載、連接獲取、SQL 執行、結果集處理、資源關閉等基礎操作：

package com.dao;

import java.sql.*;
import org.junit.Test;

public class mysqlDao {
    private static final String DRIVER = "com.mysql.jdbc.Driver";
    private static final String URL = "jdbc:mysql://localhost:3306/test";
    private static final String USER = "root";
    private static final String PASSWORD = "11";

    private Connection conn = null;
    private Statement stmt = null;
    private ResultSet rs = null;

    static {
        try { Class.forName(DRIVER); } 
        catch (ClassNotFoundException e) { e.printStackTrace(); }
    }

    @Test
    public void connect() {
        try {
            conn = DriverManager.getConnection(URL, USER, PASSWORD);
            System.out.println(conn != null ? "數據庫連接成功！" : "數據庫連接失敗！");
        } catch (SQLException e) { e.printStackTrace(); }
    }

    public void doSql(String sql) {
        if (sql == null) return;
        connect();
        try {
            stmt = conn.createStatement();
            stmt.execute(sql);
        } catch (SQLException e) { e.printStackTrace(); }
    }

    public int getUpCount() {
        try { return stmt.getUpdateCount(); } 
        catch (SQLException e) { e.printStackTrace(); return -1; }
    }

    public ResultSet getRs() {
        try { return stmt.getResultSet(); } 
        catch (SQLException e) { e.printStackTrace(); return null; }
    }

    public void close() {
        try { if (rs != null) rs.close(); } catch (SQLException e) { e.printStackTrace(); }
        try { if (stmt != null) stmt.close(); } catch (SQLException e) { e.printStackTrace(); }
        try { if (conn != null) conn.close(); } catch (SQLException e) { e.printStackTrace(); }
    }
}

業務操作類：createSql.java

調用 DAO 完成具體 CRUD：

package com.dao;

import java.sql.*;
import org.junit.Test;

public class createSql {
    private mysqlDao dao = new mysqlDao();
    private String sql;
    private int count;
    private ResultSet rs;

    @Test public void insert() {
        sql = "INSERT INTO t1(name,password) VALUES('lisi','1234')";
        dao.doSql(sql);
        count = dao.getUpCount();
        System.out.println(count != -1 ? "數據插入成功！" : "數據插入失敗！");
        dao.close();
    }

    @Test public void delete() {
        sql = "DELETE FROM t1 WHERE id=6";
        dao.doSql(sql);
        count = dao.getUpCount();
        System.out.println(count != -1 ? "數據刪除成功！" : "數據刪除失敗！");
        dao.close();
    }

    @Test public void update() {
        sql = "UPDATE t1 SET name='wangwu' WHERE id=1";
        dao.doSql(sql);
        count = dao.getUpCount();
        System.out.println(count != -1 ? "數據更新成功！" : "數據更新失敗！");
        dao.close();
    }

    @Test public void select() throws SQLException {
        sql = "SELECT * FROM t1";
        dao.doSql(sql);
        rs = dao.getRs();
        if (rs != null) {
            rs.last();
            int rows = rs.getRow();
            if (rows > 0) {
                rs.beforeFirst();
                while (rs.next()) {
                    System.out.println("id:" + rs.getString("id") + " name:" + rs.getString("name") + " password:" + rs.getString("password"));
                }
            } else {
                System.out.println("無查詢結果！");
            }
        }
        dao.close();
    }
}