Java---JDBC

JDBC

概述
JDBC（Java数据库连接，java数据库连接）是一种用于执行SQL语句的Java API，可以为多种关系数据库提供统一访问，它由一组用Java语言编写的类和接口组成.JDBC提供了一种基准，据此可以构建更高级的工具和接口，使数据库开发人员能够编写数据库应用程序，JDBC实现了所有这些面向标准的目标并且具有简单，严格类型定义且高性能实现的接口。

JDBC步骤

1. 加载驱动
2. 获取与数据库连接
3. 获取用于向数据库发送sql语句的statement
4. 向数据库发sql，并获取代表结果集的resultset
5. 取出结果集的数据
6. 关闭连接，释放资源

JBDC对数据库的增删改

因为本文所使用的的例子只对一个数据库进行操作，所以我们可以编写一个工具类JDBCUtils。每次对数据库的增删改都要加载驱动和数据库连接，所以我们把这些在工具类中编写好，在编写代码时，只需要改变sql语句。我们可以将有关数据库的信息，比如用户名、密码这些等等写在一个文本中，从这个文本读取并使用数据库的信息。
文本：

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

JDBCUtils：

package JDBCUtils;

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 is = JDBCUtils.class.getClassLoader().getResourceAsStream("db.properties"); //用流读取文本
            Properties properties = new Properties();
            properties.load(is);
             driver = properties.getProperty("driver");
             username = properties.getProperty("username"); //获取数据库用户名
             password = properties.getProperty("password"); //获取密码
             url = properties.getProperty("url"); //获取URL

            Class.forName(driver);  //加载驱动


        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }


    }

    public static Connection getConnection() throws SQLException {   //获取与数据库连接
        return DriverManager.getConnection(url,username,password);
    }

    public static void closeAll(ResultSet resultSet,Statement statement,Connection connection){  //关闭资源
        if(resultSet!=null){
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(statement!=null){
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(connection!=null){
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

• 增

  public void insert(){
      Connection connection = null;
      Statement statement = null;
      try {
          connection = JDBCUtils.getConnection();
          statement = connection.createStatement();
          String sql="INSERT INTO users(id,`name`,`password`,email,birthday)VALUES(4,'lisi','123456','lisi@sina.com','1979-12-04');";
          int i = statement.executeUpdate(sql);
      } catch (SQLException e) {
          e.printStackTrace();
      }finally {
          JDBCUtils.closeAll(null,statement,connection);
      }
  }

• 删

public void delete(){
      Connection connection = null;
      Statement statement = null;
      try {
           connection = JDBCUtils.getConnection();
           statement = connection.createStatement();
          String sql = "delete from users where id = 4";
          int i = statement.executeUpdate(sql);
      } catch (SQLException e) {
          e.printStackTrace();
      }finally {
          JDBCUtils.closeAll(null,statement,connection);
      }
  }

• 改

    public void update(){
      Connection connection = null;
      Statement statement = null;
      try {
           connection = JDBCUtils.getConnection();
           statement = connection.createStatement();
          String sql = "UPDATE users SET `name`='lisi' WHERE id=4;";
          int i = statement.executeUpdate(sql);
      } catch (SQLException e) {
          e.printStackTrace();
      }finally {
          JDBCUtils.closeAll(null,statement,connection);
      }
  }

• 查

    public void select(){
      Connection connection = null;
      Statement statement = null;
      ResultSet resultSet = null;
      try {
           connection = JDBCUtils.getConnection();
           statement = connection.createStatement();
          String sql = "select * from users";
           resultSet = statement.executeQuery(sql);
          while (resultSet.next()){
              System.out.println(resultSet.getInt("id"));
              System.out.println(resultSet.getString("name"));
              System.out.println(resultSet.getString("password"));
              System.out.println(resultSet.getString("email"));
              System.out.println(resultSet.getDate("birthday"));
          }
      } catch (SQLException e) {
          e.printStackTrace();
      }finally {
          JDBCUtils.closeAll(resultSet,statement,connection);
      }

  }