Java基础-JDBC增删改查-526互联

内容

MySQL准备

新建表t_person

CREATE TABLE `t_person` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(30) NOT NULL COMMENT '姓名',
  `birthdate` datetime NOT NULL COMMENT '出生日期',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4;

JDBC项目

创建项目
按照这篇博文搭建项目，博文地址

JDBC新增

注意新增步骤：

加载驱动
获取Connection
创建Statement
执行SQL语句(注意要写对)
关闭资源

package com.example.aji;

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

public class Main {
    public static void main(String[] args) {
        try {
            //加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            //mysql数据库相关信息
            String url="jdbc:mysql://localhost:3306/jdbc?useUnicode=true&characterEncoding=utf-8";
            String user="root";
            String password="123456789";
            //获取连接
            Connection connection = DriverManager.getConnection(url,user,password);
            Statement statement = connection.createStatement();
            //新增语句
            String insertSql="insert into t_person(name,birthdate) values('aji',now()),('aji',now())";
            int result = statement.executeUpdate(insertSql);
            //打印结果
            System.out.println(result);
            statement.close();
            connection.close();
        }catch (Exception e){
            e.printStackTrace();
        }
        System.out.println("Hello world!");
    }
}

JDBC查询

注意查询改骤：

加载驱动
获取Connection
创建Statement
执行SQL语句(注意修改条件要写对)
解析ResultSet
关闭资源

ResultSet
1. next()
  while (resultSet.next())开始时游标在第一行
  第一次遍历：
  
  第二次遍历：
  
  第三次遍历：
2. getInt(String columnLabel)
  从当前游标指向的行中获取字段名为columnLabel的字段的值，该值为int类型，该字段类型必须为INT
3. getLong(String columnLabel)
  从当前游标指向的行中获取字段名为columnLabel的字段的值，该值为long类型，该字段类型必须为BIGINT
4. getString(String columnLabel)
  从当前游标指向的行中获取字段名为columnLabel的字段的值，该值为String类型
5. getDate(String columnLabel)
  从当前游标指向的行中获取字段名为columnLabel的字段的值，该值为java.sql.Date类型
6. 更多

package com.example.aji;

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

public class Main {
    public static void main(String[] args) {
        try {
            //加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            //mysql数据库相关信息
            String url="jdbc:mysql://localhost:3306/jdbc?useUnicode=true&characterEncoding=utf-8";
            String user="root";
            String password="123456789";
            //创建连接
            Connection connection = DriverManager.getConnection(url,user,password);
            Statement statement = connection.createStatement();

            //查询语句
            String querySql="select * from t_person";
            ResultSet resultSet = statement.executeQuery(querySql);
            while (resultSet.next()) {
                System.out.print(resultSet.getInt("id"));
                System.out.print(resultSet.getString("name"));
                System.out.println(resultSet.getDate("birthdate"));
            }
            boolean execute = statement.execute(querySql);
            System.out.println(execute);
            resultSet.close();
            statement.close();
            connection.close();
        }catch (Exception e){
            e.printStackTrace();
        }
        System.out.println("Hello world!");
    }
}

JDBC修改

注意新修改骤：

加载驱动
获取Connection
创建Statement
执行SQL语句(注意修改条件要写对)
关闭资源

package com.example.aji;

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

public class Main {
    public static void main(String[] args) {
        try {
            //加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            //mysql数据库相关信息
            String url="jdbc:mysql://localhost:3306/jdbc?useUnicode=true&characterEncoding=utf-8";
            String user="root";
            String password="123456789";
            //创建连接
            Connection connection = DriverManager.getConnection(url,user,password);
            Statement statement = connection.createStatement();

            //修改语句
            String updateSql="update t_person set name='xxxxx' where id=2";
            int result = statement.executeUpdate(updateSql);
            //打印结果
            System.out.println(result);
            //查询语句
            String querySql="select * from t_person";
            ResultSet resultSet = statement.executeQuery(querySql);
            while (resultSet.next()) {
                System.out.print(resultSet.getInt("id"));
                System.out.print(resultSet.getString("name"));
                System.out.println(resultSet.getDate("birthdate"));
            }
            boolean execute = statement.execute(querySql);
            System.out.println(execute);
            resultSet.close();
            statement.close();
            connection.close();
        }catch (Exception e){
            e.printStackTrace();
        }
        System.out.println("Hello world!");
    }
}

JDBC删除

注意删除步骤：

加载驱动
获取Connection
创建Statement
执行SQL语句(注意删除条件要写对)
关闭资源

package com.example.aji;

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

public class Main {
    public static void main(String[] args) {
        try {
            //加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            //mysql数据库相关信息
            String url="jdbc:mysql://localhost:3306/jdbc?useUnicode=true&characterEncoding=utf-8";
            String user="root";
            String password="123456789";
            //创建连接
            Connection connection = DriverManager.getConnection(url,user,password);
            Statement statement = connection.createStatement();

            //删除语句
            String deleteSql="delete from t_person where id=2";
            int result = statement.executeUpdate(deleteSql);
            //打印结果
            System.out.println(result);
            //查询语句
            String querySql="select * from t_person";
            ResultSet resultSet = statement.executeQuery(querySql);
            while (resultSet.next()) {
                System.out.print(resultSet.getInt("id"));
                System.out.print(resultSet.getString("name"));
                System.out.println(resultSet.getDate("birthdate"));
            }
            boolean execute = statement.execute(querySql);
            System.out.println(execute);
            resultSet.close();
            statement.close();
            connection.close();
        }catch (Exception e){
            e.printStackTrace();
        }
        System.out.println("Hello world!");
    }
}