JDBC访问Mysql示例

1.简单的JDBC代码示例

1.查询语句

public class JdbcFirstDemo {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        // 1.载入jdbc驱动
        Class.forName("com.mysql.cj.jdbc.Driver");

        // 2.配置基本信息
        String url = "jdbc:mysql://localhost:3306/workdb?useUnicode=true&characterEncoding=utf8&useSSL=true";
        String username = "root";
        String password = "123456";

        // 3.获取数据库连接对象
        Connection connection = DriverManager.getConnection(url, username, password);

        // 4.创建sql对象
        Statement statement = connection.createStatement();

        // 5.执行查询sql
        String sql = "select * from t_students";
        ResultSet resultSet = statement.executeQuery(sql);
        while (resultSet.next()) {
            System.out.println(resultSet.getObject("id"));
            System.out.println(resultSet.getObject("name"));
            System.out.println(resultSet.getObject("gender"));
            System.out.println(resultSet.getObject("grade"));
            System.out.println(resultSet.getObject("score"));
            System.out.println("====================================");
        }
    }
}

结果：

2.插入语句

        // 6.插入语句
        String sql2 = "insert into t_students (`name`, `gender`, `grade`,`score`,`student_no`) values ('小明','2','4',95,6)";
        int res = statement.executeUpdate(sql2);
        if (res >= 1) {
            System.out.println("insert successful.");
        } else {
            System.out.println("error.");
        }

截图：

3.更新语句

        // 7. 更新语句
        String sql3 = "update t_students set score = 100 where id = 21";
        int res = statement.executeUpdate(sql3);
        if (res >= 1) {
            System.out.println("update successful.");
        } else {
            System.out.println("error.");
        }

截图：

4.删除语句

        // 8. 删除语句
        String sql4 = "delete from t_students where id = 21";
        int res = statement.executeUpdate(sql4);
        if (res >= 1) {
            System.out.println("delete successful.");
        } else {
            System.out.println("error.");
        }

截图：

2.通过JDBC工具类访问Mysql

1. JDBC配置文件(需放到工程代码中的resource路径下)

driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/workdb?useUnicode=true&characterEncoding=utf8&useSSL=true
username=root
password=123456

2. JDBC工具类

public class JdbcUtils {
    // jdbc驱动
    private static String driver = null;

    // 数据库url
    private static String url = null;

    // 数据库用户
    private static String username = null;

    // 数据库密码
    private static String password = null;

    static {
        try {
            InputStream resourceAsStream = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
            Properties properties = new Properties();
            properties.load(resourceAsStream);

            driver = properties.getProperty("driver");
            url = properties.getProperty("url");
            username = properties.getProperty("username");
            password = properties.getProperty("password");

            // jdbc驱动加载
            Class.forName(driver);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnect() throws Exception {
        return DriverManager.getConnection(url, username, password);
    }

    public static void releaseConnection(Connection connection, Statement statement, ResultSet set) throws Exception {
        if (set != null) {
            set.close();
        }
        if (statement != null) {
            statement.close();
        }
        if (connection != null) {
            connection.close();
        }
    }
}

3. 示例代码：

public class JdbcSecondDemo {
    public static void main(String[] args) throws Exception {
        Connection connection = null;

        Statement statement = null;

        ResultSet set = null;

        try {
            connection = JdbcUtils.getConnect();
            statement = connection.createStatement();
            String sql = "select * from t_students";
            ResultSet resultSet = statement.executeQuery(sql);
            while (resultSet.next()) {
                System.out.println(resultSet.getObject("id"));
                System.out.println(resultSet.getObject("name"));
                System.out.println(resultSet.getObject("gender"));
                System.out.println(resultSet.getObject("grade"));
                System.out.println(resultSet.getObject("score"));
                System.out.println("====================================");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.releaseConnection(connection, statement, set);
        }
    }
}