通过JDBC对MySQL的操作

业务分析

 1. 连接数据库。
 2. 对数据库进行操作。

代码实现(增、改、删)

package cn.itcast.demo1;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.junit.Test;

/**
 * 实现jdbc的增 删 查 改 功能
 * 
 * @author YangKeKe
 * 
 */
public class demo2 {

    /**
     * 实现增 删 改的功能
     * 
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    @Test
    public void fun1() throws ClassNotFoundException, SQLException {
        /*
         * 连接数据库
         */
        String driverClassName = "com.mysql.jdbc.Driver";
        String url = "jdbc:mysql://localhost:3306/mydb3";
        String username = "root";
        String password = "123";

        // 加载驱动类
        Class.forName(driverClassName);
        // 完成连接
        Connection con = DriverManager.getConnection(url, username, password);

        // 实现对数据库的操作(增 删 改)
        Statement statement = con.createStatement();

        // 对表格进行添加
        String sqlAdd = "INSERT INTO stu VALUES"
                + "('itcast_0004','yangwenqing','20', 'man','colloage')";
        // 对数据库进行更新(将 number为itcast_0003的名称改为yangmu)
        String sqlUpdate = "UPDATE stu SET name = 'yangmu' "
                + "WHERE number = 'itcast_0003'";

        // 对数据库进行删除(删除itcast_0002这一行)
        String sqlDelete = "DELETE FROM stu WHERE number = 'itcast_0002'";

        int add = statement.executeUpdate(sqlAdd);
        int update = statement.executeUpdate(sqlUpdate);
        int delete = statement.executeUpdate(sqlDelete);
        System.out.println(add);
        System.out.println(update);
        System.out.println(delete);

    }

    /**
     * 实现对数据库的查询操作
     * 
     * @throws ClassNotFoundException
     *             C
     * @throws SQLException
     */
    @Test
    public void fun2() throws ClassNotFoundException, SQLException {

        /*
         * 1、加载驱动类 2、实现对数据库的连接
         */
        String driverClassName = "com.mysql.jdbc.Driver";
        String url = "jdbc:mysql://localhost:3306/mydb3";
        String username = "root";
        String password = "123";

        Class.forName(driverClassName);

        Connection con = DriverManager.getConnection(url, username, password);

        Statement statement = con.createStatement();

        String sql = "SELECT * FROM stu";

        ResultSet resultSet = statement.executeQuery(sql);

        /*
         * 解析ResultSet
         */
        if (resultSet.next()) {
            String number = resultSet.getString("number");
            String name = resultSet.getString("name");
            int age = resultSet.getInt(3);
            String gender = resultSet.getString("gender");
            String education = resultSet.getString("education");
            System.out.println(number + ", " + name + ", " + gender + ", "
                    + education);
        }
        /*
         * 关闭资源(倒关)
         */
        resultSet.close();
        statement.close();
        con.close();
        
    }

 /*
     * 实现代码的规范化
     */
    @Test
    public void fun3() throws Exception {
        
        Connection con = null;
        Statement statement = null;
        ResultSet relt = null;
        
        try {
            String driverClassName = "com.mysql.jdbc.Driver";
            String url = "jdbc:mysql://localhost:3306/mydb3";
            String username = "root";
            String password = "123";

            Class.forName(driverClassName);
            con = DriverManager.getConnection(url, username,
                    password);
           
            statement = con.createStatement();
           
            String sql = "SELECT * FROM stu";
            
            relt = statement.executeQuery(sql);
            
            while(relt.next()){
                
                String number = relt.getString("number");
                String name = relt.getString("name");
                int age = relt.getInt(3);
                String gender = relt.getString("gender");
                String education = relt.getString("education");
                System.out.println(number + ", " + name + ", " + gender + ", "
                        + education);
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            if(relt == null) relt.close();
            if(statement == null) statement.close();
            if(con == null) con.close();
        }

    }
}

控制台输出

3 ( 这里显示的是对表格中影响的行数)
itcast_0001, yangkeke, man, juniorcolleage

注意事项

1、对数据库的查询需要对ResultSet进行解析
2‘、需要关闭资源，倒关。以及代码的规范性。