JavaWeb-JDBC增删改查

目录

1. MySQL准备
2. JDBC项目
3. JDBC新增
4. JDBC查询
5. JDBC修改
6. JDBC删除

内容

MySQL准备

  1. 新建表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项目

  1. 创建项目
    按照这篇博文搭建项目,博文地址

JDBC新增

注意新增步骤:

  1. 加载驱动
  2. 获取Connection
  3. 创建Statement
  4. 执行SQL语句(注意要写对)
  5. 关闭资源
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查询

注意查询改骤:

  1. 加载驱动
  2. 获取Connection
  3. 创建Statement
  4. 执行SQL语句(注意修改条件要写对)
  5. 解析ResultSet
  6. 关闭资源
  • 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修改

注意新修改骤:

  1. 加载驱动
  2. 获取Connection
  3. 创建Statement
  4. 执行SQL语句(注意修改条件要写对)
  5. 关闭资源
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删除

注意删除步骤:

  1. 加载驱动
  2. 获取Connection
  3. 创建Statement
  4. 执行SQL语句(注意删除条件要写对)
  5. 关闭资源
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!");
    }
}

热门相关:重生之将门毒后   网游三国之城市攻略   重生之女将星   一等狂妃:邪王,请接招!   网游三国之城市攻略