Summary of JDBC basic knowledge

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

(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？？？

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

Add new comment

0 comments