Main components of Java Web programming technology--JDBC

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