4th Chapter Database Connection: JDBC reading notes __ Database

Programs developed in the Java programming language and JDBC can be run across platforms and are not subject to vendor constraints.

The design of 4.1 jdbc

JDBC is made up of two layers, the above layer is the JDBC API, which communicates with the JDBC Manager driver API, sends different SQL statements to it, and the manager (transparent to the programmer) communicates with the Third-party drivers that actually connect to the database, and returns the query information. or perform the actions specified by the query.

The JDBC driver is divided into the following types:

Type 1 driver

Responsible for converting JDBC to ODBC and communicating with the database using an ODBC driver

Type 2 driver

Some drivers written in the Java programming language and partially written using native code to communicate with the client API of the database

Type 3 Driver

A pure Java client library that uses a cross-database protocol to transfer a database access request to a server component, and then the server component converts the access request to a specific database protocol

Type 4 Driver

A pure Java library for JDBC access requests for direct conversion to specific database protocols

4.2 Structured Query Language

JDBC is an interface to SQL (Structured Query Language), and SQL is actually an interface with all the newest relational databases.

4.3 Installing JDBC

It is recommended that you do not use the Jdbc/odbc bridge driver that is equipped with the JAVA2 SDK, and even more against using this driver for desktop databases such as access.

Basic concepts of 4.4 JDBC programming

1. Database URL

Syntax: Jdbc:subprotocol name:other Stuff

Where the specific driver is Subprotocol, the format of the other stuff parameter depends on the child protocol it uses.

2. Establish a connection

Class.forName (Driver Class); Registering drivers

String url = ...;

String username = ...;

String password = ...;

Connetion conn = drivermanager.getconnection (URL, username, password);

To read a property file to establish a connection

Properties Props = new properties ();

FileInputStream in = new FileInputStream ("Database.properties");

Props.load (in);

In.close ();

String drivers = Props.getproperty ("Jdbc.drivers");

String url = props.getproperty ("Jdbc.drivers");

String username = props.getproperty ("Jdbc.username");

String Password = props.getproperty ("Jdbc.password");

Connetion conn = drivermanager.getconnection (URL, username, password);

3. Execute SQL command

Statement stat = conn.createstatement ();

String sql = ...;

ResultSet rs = stat.executequery (sql);/stat.executeupdate (SQL);

while (Rs.next ()) {

...

}

4. Advanced SQL type

Blob B=resultset.getblob (1);
InputStream Bin=b.getbinarystryeam ();
Clob C=resultset.getclob (2);
Reader Creader=c.getcharacterstream ();

Write:

FileInputStream fis=new FileInputStream (f,connection conn);
Byte[] Buffer=new byte[1024];
Data=null;
int Sept=0;int len=0;

while ((Sept=fis.read (buffer))!=-1) {
if (data==null) {
len=sept;
Data=buffer;
}else{
Byte[] temp;
int templength;
templength=len+sept;
Temp=new Byte[templength];
Data=temp;
Len=templength;
}
if (Len!=data.length ()) {
byte temp=new Byte[len];
Data=temp;
}
}
String sql= "INSERT into Filedata (filename,blobdata) value (?,?)";
PreparedStatement ps=conn.preparestatement (SQL);
Ps.setstring (1,f.getname ());
Ps.setobject (2,data);
Ps.executeupdate ();

Read out:

try {

Clob C=resultset.getclob (2);

Reader Reader=c.getcharacterstream ():

if (reader = = null) {

return null;

}

StringBuffer sb = new StringBuffer ();

char[] Charbuf = new char[4096];

for (int i = Reader.read (charbuf); i > 0; i = reader.read (charbuf)) {
Sb.append (charbuf, 0, I);

}

return sb.tostring ();

catch (Exception e) {

Return "";

}

4.5 Performing query operations

Adopt host Variable Mode:

String userId = 1;

String sql = "SELECT * Form user where user_id=?";

PreparedStatement pStat = conn.preparestatement (sql);

Pstat.setstring (1,userid);

ResultSet rs = Pstat.executequery ();

4.6 Scrollable and updatable result sets

1. Scrollable result set

Statement stat = conn.createstatement (type,concurrency);

Or

PreparedStatement stat = conn.preparestatement (command,type, concurrency);

which

Type includes:

Resultset.type_forward_only cannot scroll

Resultset.type_scroll_insensitive can be scrolled, but changes are not sensitive

Resultset.type_scroll_sensitive can be scrolled, but change sensitive

Concurrency include:

Resultset.concur_read_only cannot be updated

Resultset.concur_updatable can update

Common methods:

Rs.previous () scrolling result set

Rs.relative (n) moves the cursor backwards or forwards n rows

Rs.absolute (n) sets the cursor to a specific line number

Rs.getrow () Gets the current line number

2. Updatable result Sets

Common methods:

Rs.getconcurrency () to see if the result set is updatable

Rs.updatexxx () can only be used to modify the value of the row, cannot modify the database

Rs.updaterow () Sends all information updates in the current row to the database

Rs.cancelrowupdates () undo updates to the current row

Rs.movetoinsertrow () Moves the cursor to a specific location

Rs.insertrow () passes the new row to the database

Rs.movetocurrentrow () Moves the cursor back to the previous position

Rs.deleterow () deletes the line under the cursor

Example:

Rs. Movetoinsertrow ();

Rs.updatestring ("title", title);

Rs.updatestring ("ISBN", ISBN);

Rs.insertrow ();

Rs.movetocurrentrow ();