JDBC连接数据库&SQL基本操作

Java访问数据库的基本步骤

  1. 装载数据库的JDBC驱动程序

     Class.forName(" com.mysql.jdbc.Driver").newInstance();
    
  2. 建立与数据库的连接

    String url = "jdbc:mysql://主机:端口号/数据库名?useUnicode=true&characterEncoding=utf-8&useSSL=false";  
    //例如:String url = "jdbc:mysql://localhost:3306/testdb?useUnicode=true&characterEncoding=utf-8&useSSL=false";
    String user = "root";
    String password = "211";
    
    Connection conn= DriverManager.getConnection( url, user, password );
    
  3. 建立Statement 对象,准备执行SQL语句

    String sql = "";  //“”内为所要执行的sql语句
    PreparedStatement pstmt = conn.prepareStatement(sql);
    
  4. 执行SQL语句
    常用3种执行SQL语句的方法:

    • executeQuery():用于Select语句,返回结果集(ResultSet)

          String sql = “select * from 表名”;
          ResultSet rs = pstmt.executeQuery(sql);
      
    • executeUpdate():用于创建和更新表(如Update/Insert/ Delete/Create),返回受影响行数(int)

      String sql = “Delete from 表名 where … ”;
      int rsCount = pstmt.executeUpdate(sql); 
      
    • execute():用于执行任何SQL语句,返回布尔值(执行是否成功)
  5. 处理ResultSet结果集

    while( rs.next() ) {  
        String s1=rs.getString("name");  //根据列名取值
        String s2=rs.getString(1);  //根据列号取值(从1开始)
        int col3=rs.getInt(3);  //非字符串数据示例
    }
    
  6. 依次关闭ResultSet、Statement和Connection对象

    rs.close();    
    stmt.close();    
    conn.close();
    

    示例:

    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    
    try {
        Class.forName("com.mysql.jdbc.Driver").newInstance();
    
        String url = "jdbc:mysql://localhost:3306/users?useUnicode=true&characterEncoding=utf-8&useSSL=false";
        String user = "root";
        String password = "211";
    
        conn = DriverManager.getConnection(url, user, password);
    
        String sql = "select * from users";
        pstmt = conn.prepareStatement(sql);
        rs = pstmt.executeQuery();
    
        while (rs.next()) {
            System.out.println(rs.getInt(1));
            System.out.println(rs.getString(2));
            System.out.println(rs.getString(3));
        }
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        try {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (pstmt != null) {
                pstmt.close();
                pstmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }
        } catch (Exception e) {
            System.out.println("数据库关闭错误");
            e.printStackTrace();
        }
    }
    

SQL基本操作

  • 显示数据库: show databases;
  • 使用数据库: use 库名; (如: use mysql; )
  • 显示数据库表: show tables;
  • 显示表结构: describe 表名;
  • 删除表: drop table 表名;
  • 删除数据库: drop database 库名;
  • 在数据库中创建表: create table 表名( 字段定义);
    例如:

    use testdb; 
    create table users(
        id int auto_increment not null,
        username varchar(20) not null,
        password varchar(20),
        primary key(id)
    );
    
  • 添加:

    insert into users values(1, 'wustzz','123456');
    insert into users values(null, 'zz','123456');
    
  • 查询:

    select * from users where id=1;
    select distinct username from users where username like '%zz';
    select username,password from users where id in (1,3,5);
    select count(*) as total from users;
    select max(id) from users;
    
  • 删除记录:

    delete from users where id>100;
    delete from users;
    
  • 更新记录:

    update users set password='888888' where id between 1 and 10 ;
    update users set username="wustzz",password="888888";
    注意:一般不允许修改主键
    

MySQL中文处理

先运行:

Set  Names utf-8;
create table teacher(  
id int(3) not null primary key,
name char(10),
address varchar(50),
year date)
default character set utf-8;
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值