Summary of JDBC basic knowledge

Refer to the summary of Java language programming foundation version 10

JDBC: a Java API for accessing relational data

1. Main interface:

Driver, Connection, Statement, ResultSet.

(1) Basic JDBC program:

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");//Static method
        System.out.println("Database connected");

        Statement statement=connection.createStatement();
        //statement.executeUpdate("create table Temp(col1 char(5),col2 char(5)"); / / execute definition or update statement
        ResultSet resultSet=statement.executeQuery("select firstName,mi,lastname from Student where lastName='Smith'");//Execute query statement

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

        connection.close();
    }
}

(2) To access the database from JavaFX, for example (main initializeDB and showGrade methods):

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) The PreparedStatement interface inherits from the Statement interface and creates a parameterized SQL Statement.

Statement interface: a static SQL statement without parameters.
PreparedStatement interface: a precompiled SQL statement with or without parameters.
In order to improve the efficiency of repeated execution, these SQL statements are precompiled.

Example (compared with the two methods of the previous code segment):

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");

                //Move here
                String queryString = "select firstName, mi, " +
                        "lastName, title, grade from Student, Enrollment, Course " +
                        "where Student.ssn = ? and Enrollment.courseId = ? " +
                        "and Enrollment.courseId = Course.courseId";
                //Front: 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();
              //Last program uses 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();
            }
          }

(2) CallableStatement inherits from PreparedStatement and can execute SQL stored procedures

IN, OUT, IN OUT parameters
P400???

3. Get metadata:

(1) DatabaseMetaData: get database scope information

DatabaseMetaData dbMetaData=connection.getMetaData();

For example:

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 method;

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: get the metadata of ResultSet, such as the number and name of columns in the table

ResultSetMetaData rsMetaData = resultSet.getMetaData();

Code (basic):

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();
  }
}

Tags: JDBC Database MySQL SQL

Posted on Sun, 31 May 2020 04:35:47 -0400 by cpharry