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;