Java-JDBC- 操作Mysql数据库（非连接池）

使用JDBC进行CRUD与数据库交互！

所贴代码均基于此表结构：

CRUD-users表结构

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

/**
 * CRUD
 * 使用preparedStatement防止注入进行CRUD
 */
public class PSCrudUsers {

    @Test
    public void testInsert() throws ClassNotFoundException, SQLException {
        UUID uuid = UUID.randomUUID();
        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/xxxx", "root", "xxxxx");
        String sql = "insert into users(userId,username,gender,age,password) values(?,?,?,?,?);";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setObject(1, uuid.toString().replace("-", ""));
        preparedStatement.setObject(2, "Ronnie");
        preparedStatement.setObject(3, "male");
        preparedStatement.setObject(4, 47);
        preparedStatement.setObject(5, "123456");
        int rows = preparedStatement.executeUpdate();
        if (rows > 0) {
            System.out.println("Insert success!");
        } else {
            System.out.println("Fail to insert!");
        }
        preparedStatement.close();
        connection.close();
    }

    @Test
    public void testUpdate() throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/xxxxx", "root", "xxxxxx");
        String sql = "update users set password =? where username=?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setObject(1, "qwer");
        preparedStatement.setObject(2, "Ronnie");
        int rows = preparedStatement.executeUpdate();
        if (rows > 0) {
            System.out.println("Update success!");
        } else {
            System.out.println("Fail to update!");
        }
        preparedStatement.close();
        connection.close();

    }

    @Test
    public void testDelete() throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/xxxxx", "root", "xxxxxx");
        String sql = "delete from users where username=?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setObject(1, "张三");
        int rows = preparedStatement.executeUpdate();
        if (rows > 0) {
            System.out.println("delete success!");
        } else {
            System.out.println("Fail to delete!");
        }
        preparedStatement.close();
        connection.close();
    }

    @Test
    public void testSelect() throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.cj.jdbc.Driver");
        //获取链接
        Connection connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/xxxxx", "root", "xxxxx");
        String sql = "select * from users;";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        ResultSet resultSet = preparedStatement.executeQuery();
        ResultSetMetaData metaData = resultSet.getMetaData();//装当前结果集列的信息
        int columnCount = metaData.getColumnCount();//列的数量
        List<Map> list = new ArrayList<>();

        while (resultSet.next()) {
            Map map = new HashMap<>();
            for (int i = 1; i <= columnCount; i++) {
                Object value = resultSet.getObject(i);//获取第i列的值（resultSet中的行列都从1开始数）
//                String columnName = metaData.getColumnName(i);//获取第i列的属性名称(只能获取数据库中的列明，无法获取别名)
                String columnLabel = metaData.getColumnLabel(i);//select userId as ID,username as Name,gender,age,password from users;中可获取别名ID、Name
                map.put(columnLabel,value);
            }
//            //不推荐手动写死
//            map.put("userId",resultSet.getString(1));
//            map.put("username",resultSet.getString(2));
//            map.put("gender",resultSet.getString(3));
//            map.put("ege",resultSet.getInt(4));
//            map.put("password",resultSet.getString(5));
            list.add(map);
        }
        System.out.println(list);
        resultSet.close();
        preparedStatement.close();
        connection.close();

    }

}