使用JDBC实现增删该查

JDBC实现增删改查的详细步骤

一、使用JDBC的过程一般为：

1.数据库驱动
2.获取数据库连接
3.执行SQL语句
4.获取查询结果

二、详细步骤

1.首先导入连接数据库的jar包，jar包我们可以在MySQL官网进行下载，然后在项目下面新建一个文件夹命名为lib，将jar包导入到这里。

2.依赖jar包


3.接下来就可以使用jdbc来实现简单的增删该查了。
  首先我是在数据库中已经建好一个school库，接下来就是对数据库的增删该查。
这里我是用了单元测试，这样就不用创建多个测试类，放在一个测试类就可以了，创建单元测试的方法为 @Test，然后IDEA自动导包，这样运行的时候直接点击左边的运行按钮就可以了。

package org.qq.com;

import com.mysql.jdbc.Driver;
import org.junit.Test;

import java.sql.*;

public class Test1 {
    //增加
    @Test
    public void insert() throws Exception {

        //1.连接数据库
        Class.forName("com.mysql.jdbc.Driver");
        //2.获取数据库的连接
        String username = "root";
        String password = "123456";
        String url = "jdbc:mysql://localhost:3306/school";
        Connecton connection = DriverManager.getConnection(url, username, password);
        //3.编写SQL语句
        String sql = "INSERT INTO SUBJECT (SubjectNo,SubjectName,ClassHour,gradeId)VALUES(21,'Chinese',123,3)";
        //4.执行SQL语句
        Statement statement = connection.createStatement();
        int i = statement.executeUpdate(sql);//返回受影响的行数
        if (i > 0) {
            System.out.println("插入成功");
        }
        //释放资源
        connection.close();
        statement.close();
    }
    
    //删除
    @Test
    public void delate() throws Exception {
        //1.连接数据库
        Class.forName("com.mysql.jdbc.Driver");
        //2.获得数据库的连接
        String username = "root";
        String password = "password";
        String url = "jdbc:mysql://localhost:3306/school";
        Connection connection = DriverManager.getConnection(url, username, password);
        //3.编写SQL语句
        Statement statement = connection.createStatement();
        String sql = "delete from student where StudentNo = 1002";
        int i = statement.executeUpdate(sql);
        if (i > 0) {
            System.out.println("删除成功");
        }
        connection.close();
        statement.close();
    }
    //更新
    @Test
    public void update() throws Exception {
        //1.连接数据库
        Class.forName("com.mysql.jdbc.Driver");
        //2.获得数据库的连接
        String username = "root";
        String password = "123456";
        String url = "jdbc:mysql://localhost:3306/school";
        Connection connection = DriverManager.getConnection(url, username, url);
        //3.编写SQL语句
        Statement statement = connection.createStatement();
        String sql = "update  result set StudentResult = StudentResult+5";
        int i = statement.executeUpdate(sql);
        if (i > 0) {
            System.out.println("更新成功");
        }
        connection.close();
        statement.close();
    }
    
    //查找
    @Test
    public void select() throws Exception {
        //1.连接数据库
        Class.forName("com.mysql.jdbc.Driver");
        //2.获取数据库的连接
        String username = "root";
        String password = "123456";
        String url = "jabc:mysql://localhost:3306/school";
        Connection connection = DriverManager.getConnection(url, username, url);
        //3.编写SQL语句
        Statement statement = connection.createStatement();
        String sql = "SELECT * FROM student WHERE StudentNo = 1001 ";
        ResultSet resultSet = statement.executeQuery(sql);
        //获得查询的结果
        while (resultSet.next()) {
            System.out.println(resultSet.getObject(1));
            System.out.println(resultSet.getObject(2));
            System.out.println(resultSet.getObject(3));
            System.out.println(resultSet.getObject(4));
            System.out.println(resultSet.getObject(5));

        }
    }
}

三、创建一个工具类来实现上述功能

  从上面的代码中可以看出我们每执行一次增伤该查就要写一遍连接数据库，很火的数据库连接，释放资源等等，这样是无形中就增加了我们的劳动量并且代码看起来也不整洁，因此可以编写一个工具类来实现重复功能。

1、首先创建一个db.properties文件，将下面代码写在里面

driver = com.mysql.jdbc.Driver
username = root
password = 123456
url = jdbc:mysql://localhost:3306/school?useSSL=true

2.编写工具类

package org.qq.com.JDBC;

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class JDBCUtils {
    private static String driver = null;
    private static String username = null;
    private static String password = null;
    private static String url = null;
    static {
        try {
            //加载配置文件
            InputStream stream = JDBCUtils.class.getClassLoader().getResourceAsStream("database.properties");
            Properties properties = new Properties();
            properties.load(stream);
            //读取配置文件
             driver = properties.getProperty("driver");
             username = properties.getProperty("name");
             password = properties.getProperty("password");
             url = properties.getProperty("url");
             //记载数据库驱动
            Class.forName(driver);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    //获取数据库连接对象
    public static Connection getConnection()throws Exception{
        return DriverManager.getConnection(url,username,password);
    }
    //释放资源
    public static void closeAll(ResultSet resultSet, Connection connection, Statement statement){
        if (resultSet != null){
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (connection != null){
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (statement != null){
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

3.利用工具类实现增上改查

package org.westos.dao;

import org.junit.Test;
import org.westos.utils.JDBCUtils;

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

public class Test1 {
   @Test
   //增加
   public void insert(){
      Connection connection = null;
      Statement statement = null;
      try {
         //1.获取数据库连接
         connection = JDBCUtils.getConnection();
         //2.创建statement对象
         statement = connection.createStatement();
         //3.编写SQL语句
         String sql = "INSERT INTO SUBJECT (SubjectNo,SubjectName,ClassHour,GradeID)VALUES(20,'chinese',14,3)";
         //4.执行SQL语句
         int i = statement.executeUpdate(sql);
         if (i>0){
            System.out.println("插入成功");
         }
      } catch (SQLException e) {
         e.printStackTrace();
      }finally {
         JDBCUtils.closeAll(null,statement,connection);
      }

   }
   @Test
   //删除
   public void delete(){
      Connection connection = null;
      Statement statement = null;
      try {
         //1.获取数据库连接
          connection = JDBCUtils.getConnection();
         //2.创建statement对象
         statement = connection.createStatement();
         //3.编写SQL语句
         String sql = "DELETE FROM SUBJECT WHERE SubjectName='english'";
         int i = statement.executeUpdate(sql);
         if (i>0){
            System.out.println("删除成功");
         }
      } catch (SQLException e) {
         e.printStackTrace();
      }finally {
         JDBCUtils.closeAll(null,statement,connection);
      }
   }
   @Test
   //更改
   public void update(){
      Connection connection = null;
      Statement statement = null;
      try {
         //1.获取数据库连接
         connection = JDBCUtils.getConnection();
         //2.创建statement对象
         statement = connection.createStatement();
         //3.编写SQL语句
         String sql = "UPDATE SUBJECT SET  ClassHour=ClassHour-10;";
         int i = statement.executeUpdate(sql);//这里返回的i是受影响的行数
         if (i>0){
            System.out.println("更改成功");
         }
      } catch (SQLException e) {
         e.printStackTrace();
      }finally {
         JDBCUtils.closeAll(null,statement,connection);
      }
   }
   @Test
   //查询
   public void query(){
      Connection connection = null;
      Statement statement = null;
      ResultSet resultSet = null;
      try {
         //1.获取数据库连接
         connection = JDBCUtils.getConnection();
         //2.创建statement对象
         statement = connection.createStatement();
         //3.编写SQL语句
         String sql = "SELECT * FROM student WHERE StudentNo=1010";
         resultSet = statement.executeQuery(sql);
         while (resultSet.next()){
            System.out.println(resultSet.getString(1));
            System.out.println(resultSet.getString(2));
            System.out.println(resultSet.getString(3));
            System.out.println(resultSet.getString(4));
            System.out.println(resultSet.getString(5));
            System.out.println(resultSet.getString(6));
            System.out.println(resultSet.getString(7));
            System.out.println(resultSet.getString(8));
            System.out.println(resultSet.getString(9));
            System.out.println(resultSet.getString(10));
         }
      } catch (SQLException e) {
         e.printStackTrace();
      }finally {
         JDBCUtils.closeAll(resultSet,statement,connection);
      }
   }
}

4.从上面使用工具类可以看出代码写起来非常简便，我们几乎只是需要编写SQL语句就可以实现增删该查。

四、编写SQL语句的注意事项

1.首先是单词不能拼错，例如删除是delete而不是deleta,更新是update而不是updata
2.在编写sql语句的时候要知道表的结构，逻辑不能错，不然就会报错
3.在idea中写的SQL语句不确定的时候，可以在数据库工具中例如sql.yong 中先执行一遍，看能否成功运行。