JDBC 語句示例——插入、刪除、更新、選擇記錄

本文將java.sql.Statement通過示例向您展示如何使用執行插入、更新、刪除和選擇 SQL DML 命令。

1.使用java.sql.Statement.execute(String sql)運行insert、update和delete命令。

/* This method can be used to execute insert, update, delete dml command. */
public void executeSql(String ip, int port, String dbName, String userName, String password, String sql)
{
    /* Declare the connection and statement object. */
    Connection conn = null;
    Statement stmt = null;
    try
    {
        /* Get connection object. */
        conn = this.getMySqlConnection(ip, port, dbName, userName, password);        
        /* Get statement object. */
        stmt = conn.createStatement();        
        /* The method can execute insert, update and delete dml command. */
        stmt.execute(sql);        
        System.out.println("Execute sql successfuly, " + sql);
    }catch(Exception ex)
    {
        ex.printStackTrace();
    }finally
    {
        this.closeDBResource(stmt, conn);
    }
}     
/* Close statement and connection after use, this can avoid resource waste. */
public void closeDBResource(Statement stmt, Connection conn)
{
    try
    {
        if(stmt!=null)
        {
            stmt.close();
            stmt = null;
        }        
        if(conn!=null)
        {
            conn.close();
            conn = null;
        }
    }catch(Exception ex)
    {
        ex.printStackTrace();
    }
}

2. 插入和返回自動生成的密鑰。

對于insert命令，用于java.sql.Statement.execute(String sql, int autoGeneratedKeys)插入并返回自增鍵的值，本例中為id值。

/* Execute insert command and return the auto generated record id. */
public int executeInsertSql(String ip, int port, String dbName, String userName, String password, String sql)
{
    int ret = -1;
    /* Declare the connection and statement object. */
    Connection conn = null;
    Statement stmt = null;
    try
    {
        /* Get connection object. */
        conn = this.getMySqlConnection(ip, port, dbName, userName, password);        
        /* Get statement object. */
        stmt = conn.createStatement();        
        /* The method can execute insert dml command and return auto generated key values. */
        stmt.execute(sql, Statement.RETURN_GENERATED_KEYS);        
        ResultSet rs = stmt.getGeneratedKeys();            
        if(rs.next())
        {
            /* Please note the index start from 1 not 0. */
            ret = rs.getInt(1);
        }                    
        System.out.println("Execute sql successfuly, " + sql);
    }catch(Exception ex)
    {
        ex.printStackTrace();
    }finally
    {
        this.closeDBResource(stmt, conn);
        return ret;
    }    
}

3. 執行select SQL 命令。 

/* This method can be used to execute select dml command. */
public List executeSelectSql(String ip, int port, String dbName, String userName, String password, String selectSql)
{
    List ret = new ArrayList();
    /* Declare the connection and statement object. */
    Connection conn = null;
    Statement stmt = null;
    try
    {
        /* Get connection object. */
        conn = this.getMySqlConnection(ip, port, dbName, userName, password);        
        /* Get statement object. */
        stmt = conn.createStatement();        
        /* The method can execute select dml command. */
        ResultSet rs = stmt.executeQuery(selectSql);        
        if(rs!=null)
        {
            while(rs.next())
            {
                int teacherId = rs.getInt("id");                
                String teacherName = rs.getString("name");                
                String teahcerEmail = rs.getString("email");            
                TeacherDTO teacherDto = new TeacherDTO();                
                teacherDto.setId(teacherId);                
                teacherDto.setName(teacherName);                
                teacherDto.setEmail(teahcerEmail);                
                ret.add(teacherDto);                
                System.out.println("id = " + teacherDto.getId());
                System.out.println("name = " + teacherDto.getName());
                System.out.println("email = " + teacherDto.getEmail());
                System.out.println("**************************************");
            }
        }       
        System.out.println("Execute sql successfuly, " + selectSql);
    }catch(Exception ex)
    {
        ex.printStackTrace();
    }finally
    {
        this.closeDBResource(stmt, conn);
        return ret;
    }
}

4.TeacherDTO.java。

該類用于在教師表中保存一條數據記錄。

package com.dev2qa.java.jdbc;
/* This class represent one record in database teacher table. */
public class TeacherDTO {    
    private int id;    
    private String name;    
    private String email;
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public String getEmail() {
        return email;
    }
    public void setEmail(String email) {
        this.email = email;
    }
}

5. 完成示例代碼。

這個例子將使用一個 MySQL 數據庫test和表老師，老師表有三列，他們是id，name和email。

您可以在 phpMyAdmin 中創建表老師。您需要添加一個名為id的列，類型為int，并選中A_I復選框以使其自動遞增。

示例代碼步驟：

插入一條記錄(你好，hello @dev2qa.com )。

插入另一條記錄 (hello1, hello1@dev2qa.com ) 并返回自動生成的記錄 ID。

將名稱更新為 jerry 使用第二個記錄 ID。

查詢teacher表中的所有記錄。

刪除電子郵件為hello1@dev2qa.com 的記錄 。

再次列出教師表中的所有記錄。

public static void main(String[] args) {        
    /* Below are db connection required data. */
    String ip = "localhost";
    int port = 3306;
    String dbName = "test";
    String userName = "root";
    String password = "";    
    /* Create an instance. */
    JDBCStatementExample jdbcStatementExample = new JDBCStatementExample();    
    /* Insert one record. */
    String insertSql = "insert into teacher(name, email) values('hello','hello@dev2qa.com')";
    /* Execute the insert command. */
    jdbcStatementExample.executeSql(ip, port, dbName, userName, password, insertSql);    
    /* Insert another record. */
    insertSql = "insert into teacher(name, email) values('hello1','hello1@dev2qa.com')";
    /* Execute the insert command. */
    int autoGenId = jdbcStatementExample.executeInsertSql(ip, port, dbName, userName, password, insertSql);    
    /* update record. */
    String updateSql = "update teacher set name = 'jerry' where id = " + autoGenId;
    /* Execute the update command. */
    jdbcStatementExample.executeSql(ip, port, dbName, userName, password, updateSql);    
    /* select records. */
    String selectSql = "select * from teacher";
    jdbcStatementExample.executeSelectSql(ip, port, dbName, userName, password, selectSql);    
    String deleteSql = "delete from teacher where email = 'hello1@dev2qa.com'";
    jdbcStatementExample.executeSql(ip, port, dbName, userName, password, deleteSql);    
    /* select records after delete. */
    selectSql = "select * from teacher";
    jdbcStatementExample.executeSelectSql(ip, port, dbName, userName, password, selectSql);
}
/* This method return java.sql.Connection object from MySQL server. */
public Connection getMySqlConnection(String ip, int port, String dbName, String userName, String password)
{
    /* Declare and initialize a sql Connection variable. */
    Connection ret = null;    
    try
    {    
        /* Register for mysql jdbc driver class. */
        Class.forName("com.mysql.jdbc.Driver");        
        /* Create mysql connection url. */
        String mysqlConnUrl = "jdbc:mysql://" + ip + ":" + port + "/" + dbName;        
        /* Get the mysql Connection object. */
        ret = DriverManager.getConnection(mysqlConnUrl, userName , password);
    }catch(Exception ex)
    {
        ex.printStackTrace();
    }finally
    {
        return ret;
    }
}

輸出：

<terminated> JDBCStatementExamp|e [Java Application] C:\Java\jrel.B.O_131\bin\javaw.exe [Aug 28, 2017, 7:59:53 PM]
Execute sql successfuly, insert into teacher(name, email) values('hello','hello@dev2qa.com')
Execute sql successfuly, insert into teacher(name, email) values('hello1','hello1@dev2qa.com')
Execute sql successfuly, update teacher set name = 'jerry' where id = 22
id = 21
name = hello
email = hello@dev2qa.com
**************************************
id = 22
name = jerry
email = hello1@dev2qa.com
**************************************
Execute sql successfuly, select * from teacher
Execute sql successfuly, delete from teacher where email = 'hello1@dev2qa.com'
id = 21
name = hello
email = hello@dev2qa.com
**************************************
Execute sql successfuly, select * from teacher