参考《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;
}