Shulou(Shulou.com)06/01 Report--

1. Design of JDBC

From the beginning, Java technology developers recognized Java's enormous potential for database applications. Since 1995, they have been working to extend Java standard libraries to allow access to databases using SQL. They originally hoped that by extending Java, they would be able to let people communicate with any database "purely" in Java. However, they quickly discovered that this was an impossible task: there were many different databases in the industry and the protocols they used were different. Although many database vendors have expressed support for Java to provide a standard set of network protocols for database access, each enterprise expects Java to adopt its own network protocol.

All database vendors and tool developers agree that Java would be useful if it could provide a "pure" Java API for SQL access, as well as a driver manager to allow third-party drivers to connect to specific databases. This allows database vendors to provide their own drivers and plug them into the driver manager. This will be a simple mechanism to register third-party drivers with the driver manager.

This interface organization follows Microsoft's very successful ODBC model, which provides a programming interface for C language access to databases. Both JDBC and ODBC are based on the same idea: programs written according to the API can communicate with the driver manager, which communicates with the actual database through the driver.

All of this means that the JDBC API is the interface most programmers have to use.

JDBC: Java database connectivity SUN provides a set of standard specifications for manipulating databases.

JDBC vs. Database Driver: Interface vs. Implementation.

JDBC specification (master four core objects):

DriverManager: Used to register drivers

Connection: Represents a connection created with a database

Statement: Objects that manipulate database sql statements

ResultSet: ResultSet or a virtual table

Preparations for developing a JDBC program:

Where is the JDBC specification:

In JDK:

java.sql.*;

javax.sql.*;

Driver provided by database vendor: jar file

*.jar

2. Developing a JDBC program

Data in the query database is displayed in java console

Create database tables and add test data to them

2. Create java project and add database driver

3. Implement JDBC operation

//1, Register driver

DriverManager.registerDriver(new com.mysql.jdbc.Driver());

//2. Create a connection

Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb1","root","password");

//3. Get the Statement object that executes sql statement

Statement stmt = conn.createStatement();

//4, execute sql statement and return result

String sql = "select * from user";

ResultSet rs = stmt.executeQuery(sql);

//5. Processing results

while(rs.next()){

System.out.println(rs.getObject(1));

......

}

//6. Close resources

rs.close();

stmt.close();

3. Detailed explanation of JDBC common classes and interfaces

java.sql.Drivermanager class: Create connections

a. Registration driver

DriverManager.registerDriver(new com.mysql.jdbc.Driver()); Not recommended

There are two reasons:

Causing the driver to be registered twice

A driver jar that strongly depends on the database

Solution:

Class.forName("com.mysql.jdbc.Driver");

b. Establish connection with database

static Connection getConnection(String url, String user, String password) //Attempt to establish a connection to the given database URL.

getConnection("jdbc:mysql://localhost:3306/day06", "root", "password");

URL: An agreement between SUN and the database vendor.

jdbc:mysql://localhost:3306/mydb1

Protocol IP : Port Number Database

mysql: jdbc:mysql://localhost:3306/mydb1 or jdbc:mysql:///mydb1(default native connection)

oracle: jdbc:oracle:thin:@localhost:1521:sid

Properties info = new Properties(); //to refer to database documentation

info.setProperty("user", "root");

info.setProperty("password","password");

getConnection(String url, Properties info)

getConnection(String url)

DriverManager.getConnection("jdbc:mysql://localhost:3306/day14? user=root&password=password");

java.sql.Connection interface: a connection

The implementation of the interface is in the database driver. All interaction with the database is based on connection objects.

Statement createStatement(); //Create an object that operates on sql statements

3. java.sql.Statement interface: operate sql statement and return corresponding result object (pickup truck)

The implementation of the interface is in the database driver. An object used to execute a static SQL statement and return the results it generates.

ResultSet executeQuery(String sql); //Returns a result set based on the query statement. Only select statements can be executed

int executeUpdate(String sql); //Returns the number of affected rows based on the DML(insert update delete) statement executed

boolean execute(String sql); //This method can execute arbitrary sql statements. Returns a boolean value indicating whether to return ResultSet result sets. Returns true only when the select statement is executed and there is a return result, all other statements return false; http://www.ytsgfk120.com/

4. java.sql.ResultSet interface: result set (object where client stores table data)

A. Encapsulate the result set

Provide a cursor that points by default to before the first row of the result set

Next() is called once to move the cursor down one line

Provide some ways to get

Methods of encapsulating data

getObject(int columnIndex); //Values according to sequence number, index starts from 1

Object getObject(String ColumnName); //Values according to column name.

Encapsulate the data in the result set into a javaBean

Java data types are related to types in databases

Java database

byte tityint

short smallint

int int

long bigint

float float

double double

String char varchar

Date date

boolean next(); //Move the cursor down one line from the current position

int getInt(int colIndex); //Gets the column number value specified in the current row of ResultSet result set as int

int getInt(String colLabel); //Get ResultSet result set as int

float getFloat(int colIndex); //Get ResultSet result set current row specified column number value as float

float getFloat(String colLabel); //Get ResultSet result set current row specified column name value as float

String getString(int colIndex); //Gets the column number value specified in the current row of ResultSet result set as String

String getString(String colLabel); //Get ResultSet result set current row specified column name value as String

Date getDate(int columnIndex);

Date getDate(String columnName);

void close(); //Close ResultSet object

b. Method of moving cursor

boolean next(); //Move the cursor forward one line from the current position.

boolean previous(); //Move the cursor to the previous line of this ResultSet object.

boolean absolute(int row); //parameter is the index of the current row, starting at 1 to locate the row moved at the specified index based on the row index.

void afterLast(); //Move the cursor to the end, just after the last line.

void beforeFirst(); //Move the cursor to the beginning, just before the first line.

SQL injection problem: preparedStatement

preparedStatement: a precompiled object, a subclass of the Statement object

Features:

High performance

SQL statements are compiled first.

The parameters in the sql statement change, filtering out keywords entered by the user

PreparedStatement ps = null;

ResultSet rs = null;

try{

conn = DBUtils.getConnection();

String sql = "select * from user where name=? and password=? ";

ps = conn.prepareStatement(sql); //Note: When creating this object, put sql statements first

//assign values to precompiled sql statements

ps.setString(1, name);

ps.setString(2, password);

rs = ps.executeQuery();

}catch(Exception e){

e.printStackTrace();

}

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.