JavaWeb入门（二）：JDBC的基本使用

我们以java操作mysql为例

一、下载JDBC的驱动包

• 下载地址：https://downloads.mysql.com/archives/c-j/

二、把JDBC驱动包加载到项目中（如何加载第三方包:https://www.cnblogs.com/Infancy/p/12499208.html）

三、Java使用JDBC操作mysql数据库我们将之分为四步

1. 加载驱动
2. 获取连接
3. 创建执行sql语句的对象
4. 编写sql语句，执行sql语句并得到返回结果

四、JDBC操作数据示例代码（自己写的，亲测可以跑起来）

• 添加数据

  @Test
      public void addData(){
          Connection conn = null;
          Statement statement = null;
          try {
              //加载驱动
              Class.forName("com.mysql.jdbc.Driver");
              //获取连接
              conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/web_test3", "root", "root");
              //创建执行sql的对象
              statement = conn.createStatement();
              //编写sql语句
              String sql  = "insert into user_baseinfo values(null,'1','老师','正常','fs')";
              //执行sql语句
              int num = statement.executeUpdate(sql);
              if(num>0){
                  System.out.println("添加成功");
              }else{
                  System.out.println("添加失败");
              }
          }catch(Exception e){
              e.printStackTrace();
          }finally {
              if(conn!=null){
                  try {
                      conn.close();
                  }catch (Exception e){
                      e.printStackTrace();
                  }
                  conn = null;
              }
              if(statement!=null){
                  try {
                      statement.close();
                  }catch (Exception e){
                      e.printStackTrace();
                  }
                  statement=null;
              }
          }
      }

• 删除数据

  @Test
      public void deleteData(){
          Connection conn = null;
          Statement statement = null;
          try {
              //加载驱动
              Class.forName("com.mysql.jdbc.Driver");
              //获取连接
              conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/web_test3","root" , "root");
              //创建执行sql的对象
              statement = conn.createStatement();
              //编写sql语句
              String strSql = "delete from user_baseinfo where id = 9";
              //执行sql语句
              int num = statement.executeUpdate(strSql);
              if(num>0){
                  System.out.println("删除成功");
              }else{
                  System.out.println("删除失败");
              }
          }catch(Exception ex){
              ex.printStackTrace();
          }finally {
              if(conn!=null){
                  try {
                      conn.close();
                  }catch (Exception e){
                      e.printStackTrace();
                  }
                  conn = null;
              }
              if(statement!=null){
                  try {
                      statement.close();
                  }catch (Exception e){
                      e.printStackTrace();
                  }
                  statement=null;
              }
          }
      }

• 修改数据

  @Test
      public void modifyData(){
          Connection conn = null;
          Statement statement = null;
          try {
              //加载驱动
              Class.forName("com.mysql.jdbc.Driver");
              //创建连接
              conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/web_test3", "root", "root");
              //用连接获取执行sql对象
              statement = conn.createStatement();
              //编写sql语句
              String strSql = "update user_baseinfo set username = 'fs' where id = 9";
              //执行sql
              int num = statement.executeUpdate(strSql);
              if(num>0){
                  System.out.println("修改成功");
              }else{
                  System.out.println("修改失败");
              }
          }catch(Exception ex){
              ex.printStackTrace();
          }finally {
              if(conn!=null){
                  try {
                      conn.close();
                  }catch (Exception e){
                      e.printStackTrace();
                  }
                  conn = null;
              }
              if(statement!=null){
                  try {
                      statement.close();
                  }catch (Exception e){
                      e.printStackTrace();
                  }
                  statement=null;
              }
          }
      }

• 查询数据

  @Test
      public void selDate(){
          Connection conn = null;//数据库连接对象
          Statement statement = null;//执行sql对象
          ResultSet rs = null;//结果集
          try {
              //加载驱动
              Class.forName("com.mysql.jdbc.Driver");
              //获取连接
              conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/web_test3", "root", "root");
              //获取执行对象
              statement = conn.createStatement();
              //编写sql
              String strSql = "select * from user_baseinfo";
              //执行sql
              rs = statement.executeQuery(strSql);
              //遍历结果集
              while (rs.next()){
                  System.out.println("id："+rs.getInt("id")+"    姓名"+rs.getString("username"));
              }
          }catch(Exception ex){
              ex.printStackTrace();
          }finally {
              if(conn!=null) {
                  try {
                      conn.close();
                  }catch(Exception e) {
                      e.printStackTrace();
                  }
                  conn= null;
              }
              if(statement!=null) {
                  try {
                      statement.close();
                  }catch(Exception e) {
                      e.printStackTrace();
                  }
                  statement= null;
              }
              if(rs!=null) {
                  try {
                      rs.close();
                  }catch(Exception e) {
                      e.printStackTrace();
                  }
                  rs= null;
              }
          }
      }

完整代码如下：

package com.zhurouwangzi.com;

import org.junit.Test;

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

public class HelloTest {
    public static void main(String[] args){
        
    }

    @Test
    public void addData(){
        Connection conn = null;
        Statement statement = null;
        try {
            //加载驱动
            Class.forName("com.mysql.jdbc.Driver");
            //获取连接
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/web_test3", "root", "root");
            //创建执行sql的对象
            statement = conn.createStatement();
            //编写sql语句
            String sql  = "insert into user_baseinfo values(null,'1','老师','正常','fs')";
            //执行sql语句
            int num = statement.executeUpdate(sql);
            if(num>0){
                System.out.println("添加成功");
            }else{
                System.out.println("添加失败");
            }
        }catch(Exception e){
            e.printStackTrace();
        }finally {
            if(conn!=null){
                try {
                    conn.close();
                }catch (Exception e){
                    e.printStackTrace();
                }
                conn = null;
            }
            if(statement!=null){
                try {
                    statement.close();
                }catch (Exception e){
                    e.printStackTrace();
                }
                statement=null;
            }
        }
    }

    @Test
    public void deleteData(){
        Connection conn = null;
        Statement statement = null;
        try {
            //加载驱动
            Class.forName("com.mysql.jdbc.Driver");
            //获取连接
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/web_test3","root" , "root");
            //创建执行sql的对象
            statement = conn.createStatement();
            //编写sql语句
            String strSql = "delete from user_baseinfo where id = 9";
            //执行sql语句
            int num = statement.executeUpdate(strSql);
            if(num>0){
                System.out.println("删除成功");
            }else{
                System.out.println("删除失败");
            }
        }catch(Exception ex){
            ex.printStackTrace();
        }finally {
            if(conn!=null){
                try {
                    conn.close();
                }catch (Exception e){
                    e.printStackTrace();
                }
                conn = null;
            }
            if(statement!=null){
                try {
                    statement.close();
                }catch (Exception e){
                    e.printStackTrace();
                }
                statement=null;
            }
        }
    }

    @Test
    public void modifyData(){
        Connection conn = null;
        Statement statement = null;
        try {
            //加载驱动
            Class.forName("com.mysql.jdbc.Driver");
            //创建连接
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/web_test3", "root", "root");
            //用连接获取执行sql对象
            statement = conn.createStatement();
            //编写sql语句
            String strSql = "update user_baseinfo set username = 'fs' where id = 9";
            //执行sql
            int num = statement.executeUpdate(strSql);
            if(num>0){
                System.out.println("修改成功");
            }else{
                System.out.println("修改失败");
            }
        }catch(Exception ex){
            ex.printStackTrace();
        }finally {
            if(conn!=null){
                try {
                    conn.close();
                }catch (Exception e){
                    e.printStackTrace();
                }
                conn = null;
            }
            if(statement!=null){
                try {
                    statement.close();
                }catch (Exception e){
                    e.printStackTrace();
                }
                statement=null;
            }
        }
    }

    @Test
    public void selDate(){
        Connection conn = null;//数据库连接对象
        Statement statement = null;//执行sql对象
        ResultSet rs = null;//结果集
        try {
            //加载驱动
            Class.forName("com.mysql.jdbc.Driver");
            //获取连接
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/web_test3", "root", "root");
            //获取执行对象
            statement = conn.createStatement();
            //编写sql
            String strSql = "select * from user_baseinfo";
            //执行sql
            rs = statement.executeQuery(strSql);
            //遍历结果集
            while (rs.next()){
                System.out.println("id："+rs.getInt("id")+"    姓名"+rs.getString("username"));
            }
        }catch(Exception ex){
            ex.printStackTrace();
        }finally {
            if(conn!=null) {
                try {
                    conn.close();
                }catch(Exception e) {
                    e.printStackTrace();
                }
                conn= null;
            }
            if(statement!=null) {
                try {
                    statement.close();
                }catch(Exception e) {
                    e.printStackTrace();
                }
                statement= null;
            }
            if(rs!=null) {
                try {
                    rs.close();
                }catch(Exception e) {
                    e.printStackTrace();
                }
                rs= null;
            }
        }
    }
}

 转载请注明出处：https://www.cnblogs.com/Infancy/p/12499806.html