JDBC基础知识总结

参考《Java语言程序设计基础篇第10版》总结

JDBC：访问关系型数据的JavaAPI

1..主要接口：

Driver（加载驱动程序）、Connection、Statement、ResultSet。

（1）基础的JDBC程序：

package pra.database;

import java.sql.*;
public class tryOne {
    public static void main(String[] args) throws SQLException,ClassNotFoundException{
        Class.forName("com.mysql.jdbc.Driver");
        System.out.println("Driver loaded");

        Connection connection=DriverManager.getConnection("jdbc:mysql://localhost/javabook","root","SQ12345678");//静态方法
        System.out.println("Database connected");

        Statement statement=connection.createStatement();
        //statement.executeUpdate("create table Temp(col1 char(5),col2 char(5)");//执行定义或更新语句
        ResultSet resultSet=statement.executeQuery("select firstName,mi,lastname from Student where lastName='Smith'");//执行查询语句

        while(resultSet.next()) {//
            System.out.println(resultSet.getString(1)+"\t"+resultSet.getString(2)+"\t"+resultSet.getString(3));//   resultSet.getString("firstName")也可以 
        }

        connection.close();
    }
}

（2）从JavaFX访问数据库，例子（主要initializeDB和showGrade方法）：

package pra.database;
import javafx.application.Application;
import javafx.scene.Scene;
import javafx.scene.control.Button;
import javafx.scene.control.Label;
import javafx.scene.control.TextField;
import javafx.scene.layout.HBox;
import javafx.scene.layout.VBox;
import javafx.stage.Stage;
import java.sql.*;

public class FindGrade extends Application{
      private Statement statement;
      private Statement stmt;
      private TextField tfSSN = new TextField();
      private TextField tfCourseId = new TextField();
      private Label lblStatus = new Label();

      @Override // Override the start method in the Application class
      public void start(Stage primaryStage) {
            initializeDB();
            Button btShowGrade = new Button("Show Grade");
            HBox hBox = new HBox(5);
            hBox.getChildren().addAll(new Label("SSN"), tfSSN, 
              new Label("Course ID"), tfCourseId, (btShowGrade));

            VBox vBox = new VBox(10);
            vBox.getChildren().addAll(hBox, lblStatus);

            tfSSN.setPrefColumnCount(6);
            tfCourseId.setPrefColumnCount(6);
            btShowGrade.setOnAction(e->showGrade());

            Scene scene = new Scene(vBox, 420, 80);
            primaryStage.setTitle("FindGrade"); 
            primaryStage.setScene(scene); 
            primaryStage.show(); 
      }
      private void initializeDB() {
          try {
                Class.forName("com.mysql.jdbc.Driver");
                System.out.println("Driver loaded");

                Connection connection=DriverManager.getConnection("jdbc:mysql://localhost/javabook","root","SQ12345678");
                System.out.println("Database connected");

                stmt=connection.createStatement();
          }catch (Exception ex) {
              ex.printStackTrace();
            }
      }
      //btShowGrade.setOnAction(e->showGrade());
      private void showGrade() {
            String ssn = tfSSN.getText();
            String courseId = tfCourseId.getText();
            try {
              String queryString = "select firstName, mi, lastName, title, grade from Student, Enrollment, Course " +
                "where Student.ssn = '" + ssn + "' and Enrollment.courseId "
                + "= '" + courseId +
                "' and Enrollment.courseId = Course.courseId " +
                " and Enrollment.ssn = Student.ssn";

              ResultSet rset = stmt.executeQuery(queryString);

              if (rset.next()) {
                String lastName = rset.getString(1);
                String mi = rset.getString(2);
                String firstName = rset.getString(3);
                String title = rset.getString(4);
                String grade = rset.getString(5);

                // Display result in a label
                lblStatus.setText(firstName + " " + mi +
                  " " + lastName + "'s grade on course " + title + " is " +
                  grade);
              } else {
                lblStatus.setText("Not found");//-------
              }
            }
            catch (SQLException ex) {
              ex.printStackTrace();
            }
          }
}

2..Statement：

（1）PreparedStatement接口继承自Statement接口，创建参数化的SQL语句。

Statement接口：不含参数的静态的SQL语句。
PreparedStatement接口：含有或不含参数的预编译的SQL语句。
为提高重复执行的效率，对这些SQL语句进行了预编译。

例（与上一个代码段2个方法对比）：

private void initializeDB() {
          try {
                Class.forName("com.mysql.jdbc.Driver");
                System.out.println("Driver loaded");

                Connection connection=DriverManager.getConnection("jdbc:mysql://localhost/javabook","root","SQ12345678");
                System.out.println("Database connected");

                //移到这里
                String queryString = "select firstName, mi, " +
                        "lastName, title, grade from Student, Enrollment, Course " +
                        "where Student.ssn = ? and Enrollment.courseId = ? " +
                        "and Enrollment.courseId = Course.courseId";//--------
                //前面：private PreparedStatement preparedStatement;
                preparedStatement=connection.prepareStatement(queryString);//--------
          }catch (Exception ex) {
              ex.printStackTrace();
            }
      }
      private void showGrade() {
            String ssn = tfSSN.getText();
            String courseId = tfCourseId.getText();
            try {
              preparedStatement.setString(1, ssn);//--------
              preparedStatement.setString(2,courseId ); //--------        
              ResultSet rset = preparedStatement.executeQuery();//--------
              //上一个程序用Statement：ResultSet rset = stmt.executeQuery(queryString);

              if (rset.next()) {
                String lastName = rset.getString(1);
                String mi = rset.getString(2);
                String firstName = rset.getString(3);
                String title = rset.getString(4);
                String grade = rset.getString(5);

                // Display result in a label
                lblStatus.setText(firstName + " " + mi +
                  " " + lastName + "'s grade on course " + title + " is " +
                  grade);
              } else {
                lblStatus.setText("Not found");//-------
              }
            }
            catch (SQLException ex) {
              ex.printStackTrace();
            }
          }

PreparedStatement用法示例（important）：

DriverManager.registerDriver(new Driver());
connection=DriverManager.getConnection(url, user, password);
String sql="select * from Employee where id=?";//----
PreparedStatement preparedStatement=connection.prepareStatement(sql);//----此处sql：带"?"------

preparedStatement.setInt(1, 1);//----
ResultSet rset = preparedStatement.executeQuery();//----

rs=conn.createStatement().executeQuery(sql);//---------------

PreparedStatement、Statement区别示例（important）：

Statement stmt=connection.createStatement();
String sql="insert into Employee(name,sex,gross) values ('"+name+"',"+sex+","+gross+");";
 rs=stmt.executeUpdate(sql);//-------


String sql="select * from Employee where id=?";
PreparedStatement pstmt=connection.prepareStatement(sql);//----此处sql：带"?"---            
pstmt.setInt(1, 1);//从1开始计数         
rs=pstmt.executeQuery();//-------

（2）CallableStatement继承自PreparedStatement，可以执行SQL存储过程

IN、OUT、IN OUT参数
P400？？？

3..获取元数据：

（1）DatabaseMetaData:获取数据库范围的信息

DatabaseMetaData dbMetaData=connection.getMetaData();

例：

import java.sql.*;

public class tryFour {
    public static void main(String[] args) throws SQLException,ClassNotFoundException{
        Class.forName("com.mysql.jdbc.Driver");
        System.out.println("Driver loaded");

        Connection connection=DriverManager.getConnection("jdbc:mysql://localhost/javabook","root","SQ12345678");
        System.out.println("Database connected");

        DatabaseMetaData dbMetaData=connection.getMetaData();
        dbMetaData.get......//syso

        connection.close();
    }
}

getTables方法;

Class.forName("com.mysql.jdbc.Driver");
        System.out.println("Driver loaded");

        Connection connection=DriverManager.getConnection("jdbc:mysql://localhost/javabook","root","SQ12345678");
        System.out.println("Database connected");

        DatabaseMetaData dbMetaData=connection.getMetaData();//DatabaseMetaData
        ResultSet reTables=dbMetaData.getTables(null, null, null, new String[] {"TABLE"});//
        System.out.println("User tables:");
        while(reTables.next()) {
            System.out.println(reTables.getString("TABLE_NAME")+" ");//
        }

        connection.close();

（2）ResultSetMetaData:获取结果集合ResultSet的元数据，如表的列数和列名

ResultSetMetaData rsMetaData = resultSet.getMetaData();

代码（基础）：

import java.sql.*;

public class trySix {
  public static void main(String[] args)throws SQLException, ClassNotFoundException {
    Class.forName("com.mysql.jdbc.Driver");
    System.out.println("Driver loaded");

    Connection connection = DriverManager.getConnection("jdbc:mysql://localhost/javabook", "scott", "tiger");
    System.out.println("Database connected");

    Statement statement = connection.createStatement();

    ResultSet resultSet = statement.executeQuery("select * from Enrollment");

    ResultSetMetaData rsMetaData = resultSet.getMetaData();//--
    for (int i = 1; i <= rsMetaData.getColumnCount(); i++)//
      System.out.printf("%-12s\t", rsMetaData.getColumnName(i));//
    System.out.println();

    while (resultSet.next()) {
      for (int i = 1; i <= rsMetaData.getColumnCount(); i++)//---
        System.out.printf("%-12s\t", resultSet.getObject(i));//---
      System.out.println();
    }

    connection.close();
  }
}

模板-PreparedStatement 防注入（important）

//connection PreparedStatement分离
//PreparedStatement 不会被注入------------------------------
//name="张三',true,0);delete from Employee where id=1; "; 
    public static  ResultSet selectById(String sql,int id) {
        ArrayList<Employee> employees=new ArrayList<>();
        Connection connection=null;
        ResultSet resultSet=null;       

        try {
            DriverManager.registerDriver(new Driver());
            connection=DriverManager.getConnection(url, user, password);
            String sql="select * from Employee where id=?";
            PreparedStatement preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setInt(1, 1);
            ResultSet rset = preparedStatement.executeQuery();
        }catch(SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return resultSet;
    }