Reference book: "Java EE open source programming Essentials 15"
JDBC (Java DataBase Connectivity) is one of the most important APIs for Java Web application development. When querying data to a database, the Java application calls the JDBC API first, and then the JDBC API submits the query to the JDBC driver, and the JDBC driver translates the query statement into the form of a specific database understanding, and the JDBC Drive retrieves the results of the SQL query. and translates to the equivalent JDBC API classes and interfaces used by Java applications.
JDBC Drive
The JDBC package itself cannot link to any database, it is just an API framework that requires a database driver and other packages to provide implementation methods.
The JDBC database driver is divided into the following categories:
- JDBC-ODBC Bridging drives: Translating the JDBC API into an ODBC API for databases such as Ms Access, MS SQL Server, etc.
- Java drives for some native APIs: some databases, such as DB2 and Informix, contain JDBC drives supplied by the database vendor and contain classes that can be called directly by the JDBC API
- Plain Java drives: Linking Java applications or applets to a database via IP/TCP
Common database drives and database URLs (local URLs)
Database name |
Database Drive |
Database URL (local) |
Oracle9i |
Oracle.jdbc.driver.OracleDriver |
Jdbc:oracle:thin: @localhost: 1521:dbname |
SQL Server 2000 |
Com.microsoft.jdbc.sqlserver.SQLServerDriver |
Jdbc:microsoft:sqlserver://localhost:1433;databasename=ddbname; |
Mysql |
Com.mysql.jdbc.Driver |
Jdbc:mysql://localhost/dbname |
Jdbc-odbc |
Sun.jdbc.odbc.JdbcOdbcDriver |
Jdbc:odbc:datasourceName |
Core components of the JDBC API
- DriverManager class: Used to track available JDBC drivers and generate database connections
- Connection interface: Used to obtain database information, generate database statements, manage database transactions
- ResultSet interface: Accesses the data returned by the SQL query. Next () locates each row of data with the corresponding get method to read the data
- Statement interface: Provides SQL statements that run on a grassroots connection, generating a result set. There are 2 sub-interfaces, PreparedStatement and CallableStatement
PreparedStatement provides a type of statement that can be precompiled with query information
CallableStatement inherits from PreparedStatement, used to encapsulate the execution of stored procedures in the database
Querying the database with JDBC
- Mount Drive
- Connecting to a database
- Querying the database
Mount Drive
Call the class forname () method to mount the database-specific drive, such as: Load the MySQL drive
Class.forName ("Com.mysql.jdbc.Driver");
Connecting to a database
Generate connection objects from the DriverManager class first, such as
String url= "Jdbc:mysql://localhost/mydatabase";
Connection con=drivermanager.getconnection (URL, "Bill", "123");
Local MySQL database mydatabase, user name is bill, password is 123
Querying the database
1) Statement object: Send a simple query statement to the database
The ExecuteQuery () method executes a simple selection (select) query, returning the ResultSet object
The Executeupdate () method executes an INSERT, UPDATE, or DELETE statement of SQL, returning an int value that gives the number of rows that are affected
Such as:
Statement st=con.createstatement ();
ResultSet rs=st.executequery ("SELECT * from students");
2) PreparedStatement object: Allows parameterized queries to be executed
Such as:
String sql= "SELECT * from students where stuid=?";
PreparedStatement ps=con.preparestatement (SQL);
Ps.setstring (1, "56789");//Set the first question mark at the parameter value of 56789, type string
ResultSet Rs=ps.executequery ();
Example:
1 ImportJava.sql.*;2 3 classJDBC test{4 Public Static voidMain (String args[]) {5 Try{6Class.forName ("Com.mysql.jdbc.Driver");7String url= "Jdbc:mysql://localhost/mydatabase";8Connection con=drivermanager.getconnection (URL, "Bill", "123");9String sql= "SELECT * from students where stuid=?";TenPreparedStatement ps=con.preparestatement (SQL); OnePs.setstring (1, "56789"); AResultSet rs=ps.executequery (); - while(Rs.next ()) { -String name=rs.getstring ("Student_name"); the } - rs.close (); - con.close (); -}Catch(SQLException e) { + e.printstacktrace (); - } + } A}
View Code
Main components of Java Web programming technology--JDBC