Mysql_JDBC增删改查模板代码 + java

package com.ljy.day20;

import org.junit.Test;

import java.sql.*;

public class TestInsertUpdateDelete {

    /**
     * 执行增删改操作使用：  int executeUpdate(String sql)
     * 执行查询操作：       ResultSet executeQuery(String sql)
     * @throws ClassNotFoundException
     * @throws SQLException
     */

    @Test
    public void testSelect() throws ClassNotFoundException, SQLException {
        //1. 注册驱动
        Class.forName("com.mysql.jdbc.Driver");

        //2. 连接数据库
        String url = "jdbc:mysql:///itheima";
        String name = "root";
        String password = "201223";
        Connection connection = DriverManager.getConnection(url, name, password);

        //3. 创建操作对象
        Statement statement = connection.createStatement();

        //4. 发送sql语句，并接收结果
        String sql = "select id, username as name, password from user";
        ResultSet resultSet = statement.executeQuery(sql);

        //5. 处理结果
        while (resultSet.next()) {
            int id = resultSet.getInt("id");
            String name1 = resultSet.getString("name");
            String password1 = resultSet.getString("password");
            System.out.println(id + ", " + name1 + ", "+ password1);
        }

        //6. 释放资源
        resultSet.close();
        statement.close();
        connection.close();

    }

    @Test
    public void testInsert() throws ClassNotFoundException, SQLException {
        //1. 注册驱动
        Class.forName("com.mysql.jdbc.Driver");

        //2. 连接数据库
        String url = "jdbc:mysql:///itheima";
        String name = "root";
        String password = "201223";
        Connection connection = DriverManager.getConnection(url, name, password);

        //3. 创建操作对象
        Statement statement = connection.createStatement();

        //4. 发送sql语句，并接受结果
        String uname = "熊大111";
        String upwd = "123456";
        String sql = "insert into user(username, password) values ('"+uname + "', '" + upwd + "')";
        int i = statement.executeUpdate(sql);

        //5. 处理结果
        if(i > 0) {
            System.out.println("用户添加成功！！");
        }

        //6. 释放资源
        statement.close();
        connection.close();

    }

    @Test
    public void testUpdate() throws ClassNotFoundException, SQLException {
        //1. 注册驱动
        Class.forName("com.mysql.jdbc.Driver");

        //2. 连接数据库
        String url = "jdbc:mysql:///itheima";
        String name = "root";
        String password = "201223";
        Connection connection = DriverManager.getConnection(url, name, password);

        //3. 创建操作对象
        Statement statement = connection.createStatement();

        //4. 发送 Update sql语句 ，并接收结果
        String sql = "update user set username = '熊二' where id = 4";
        int i = statement.executeUpdate(sql);

        //5. 处理结果
        if(i > 0) System.out.println("用户修改成功！！");

        //6. 释放资源
        statement.close();
        connection.close();
    }

    @Test
    public void testDelete() throws ClassNotFoundException, SQLException {
        //1. 注册驱动
        Class.forName("com.mysql.jdbc.Driver");

        //2. 连接数据库
        String url = "jdbc:mysql:///itheima";
        String name = "root";
        String password = "201223";
        Connection connection = DriverManager.getConnection(url, name, password);

        //3. 创建操作对象
        Statement statement = connection.createStatement();

        //4. 发送delete sql语句，并接收结果
        String sql = "delete from user where id = 4";
        int i = statement.executeUpdate(sql);

        //5. 处理结果
        if(i > 0) System.out.println("用户删除成功！");

        //6. 释放资源
        statement.close();
        connection.close();
    }

}