JDBC Link MySQL Database

Unit_1

First: Jdbc:java Database connectivity Sun provides a set of standard specifications for operational databases.

The relationship between JDBC and database-driven is the relationship between interface and implementation.

JDBC involves four core objects

1:drivermanager: Registering Database Drivers

2:connection: Represents a connection to a database creation

3:statement: Objects that manipulate database SQL statements generally use his subclass preparedstatement to solve "SQL injection problems"

4:resultset: Translation comes from the meaning of the result set, often used to load some information obtained from the database, such as the result of a SELECT statement

First, DriverManager

There are two ways of registering a database:

①:drivermanager.registerdriver (New Com.mysql.jdbc.Driver ()); This method we use CTRL click on the source code found in the bottom will register 2 drives, need to import the corresponding jar package

②:class.forname ("Com.mysql.jdbc.Driver"); The driver class is loaded directly by launching mechanism, and the more common way

Second, Connection

Gets the connection to the database connection connection, which attempts to connect to the URL of the given database, as in the following code

Connection conn = drivermanager.getconnection ("Jdbc:mysql://localhost:3306/stu", "root", "123456");

Jdbc:mysql://localhost:3306/stu

Protocol: Sub-Protocol://HOST Address: Port number/Database

Usually we will write the relevant configuration of the database in the configuration file in the actual development.

Third, Statement (subclass PreparedStatement)

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

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

int executeupdate (String sql) returns the number of rows affected, based on the DML (INSERT update Delete) statement that is executed.

The Boolean execute (String sql) method can execute arbitrary SQL statements. Returns a Boolean value that indicates whether the resultset result set is returned. The other statements return false only if the SELECT statement is executed and returns True when the result is returned;

PreparedStatement is a pre-compiled mechanism by querying the document to find that it is a subclass of the statement interface

Features: The written SQL statements are compiled beforehand and the parameters in the SQL statements sometimes change to filter out the user input keywords

such as: String sql = "SELECT * from student where name=?" and password=? ";

Use instead of user input or form items to query

Iv. ResultSet

A, encapsulates the result set.

Provides a cursor that the default cursor points to before the first row of the result set.

Call Next () and the cursor moves down one line.

Provides some get methods.

Ways to encapsulate data

Object getObject (int columnindex); Based on ordinal value, index starting from 1

Object GetObject (String colomnname); Values are based on the column name.

Encapsulating data in a result set into a JavaBean

The relationship between the data type of Java and the type in the database

BYTE Tityint

Short smallint

int int

Long bigint

float float

Double Double

String Char varchar

Date Date

Boolean next () moves the cursor down one line from the current position

int getInt (int colindex) Gets the value of the specified column number in the current row of the resultset result set in int form

int getInt (String collabel) gets the resultset result set for the current row of the specified column name value in int form

float getfloat (int colindex) gets the resultset result set current row specified column number value in float form

Float getfloat (String collabel) Get resultset result set current row specified column name value in float form

String getString (int colindex) Gets the current row of the resultset result set with the specified column number value in string form

String getString (String collabel) Gets the current row of the resultset result set for the specified column name value in string form

Date getDate (int columnindex);

Date getDate (String columnName);

void Close () Close the ResultSet object

B, the method of the movable cursor

Boolean next () moves the cursor forward one line from the current position.
Boolean previous () moves the cursor to the previous line of this ResultSet object.

The boolean absolute (int row) parameter is the index of the current row, starting from 1 to locate the specified index row for the move based on the index of the row.

void Afterlast () moves the cursor to the end, just after the last line.

void Beforefirst () moves the cursor to the beginning, just before the first line.

Previous Demo:

1  PackageCn.xisole;2 3 Importjava.sql.Connection;4 ImportJava.sql.DriverManager;5 ImportJava.sql.ResultSet;6 Importjava.sql.Statement;7 Importjava.util.Properties;8 9 Importorg.junit.Test;Ten /* One use JDBC technology to query database data and display it in three ways at the console as follows A Note that the Guide pack imports the interface as much as possible - 1 Registration Drive - 2 Getting the connection connection the 3 Gets the object that executes the SQL statement statement - 4 Execute the SQL statement and return the result - 5 Processing Results - 6 Closing Resources +  */ -  Public classJdbc_demo1 { + @Test A      Public voidTest1 ()throwsException { at          -         /*1 Load driver Use the reflection mechanism to load the driver class it is recommended to use this method - class.forname ("Com.mysql.jdbc.Driver"); No jar drive is also available*/ -          -         //1 Registration Drive -Drivermanager.registerdriver (Newcom.mysql.jdbc.Driver ()); in          -         //2 Getting the connection connection toConnection conn = drivermanager.getconnection ("Jdbc:mysql://localhost:3306/stu", "root", "123456"); +      -         //3 Gets the object that executes the SQL statement statement theStatement stmt =conn.createstatement (); */* General use preparedstatement*/ $         //4 Execute the SQL statement and return the resultPanax NotoginsengResultSet rs = stmt.executequery ("SELECT * FROM Student"); -SYSTEM.OUT.PRINTLN ("id" + "name" + "password" + "email" + "Brithday"); the      +         //5 Processing Results A          while(Rs.next ()) { theSystem.out.println (Rs.getobject ("id") + "" "+rs.getobject (" NAME ") +" "+rs.getobject (" password ") +" "+rs.getobject (" Email ") +" "+rs.getobject (" Birthday "))); +             //System.out.println (Rs.getobject (1) + "" "+rs.getobject (2) +" "+rs.getobject (3) +" +rs.getobject (4) + "" + Rs.getobject (5)); -System.out.println ("-------------------------------"); $         } $      -         //6 Closing Resources - rs.close (); the stmt.close (); - conn.close ();Wuyi     } the @Test -      Public voidTest2 ()throwsexception{ Wu  -         //1 Load driver Use the reflection mechanism to load the driver class it is recommended to use this method AboutClass.forName ("Com.mysql.jdbc.Driver");//no jar drive is also available $          -         /*//1 Registered Driver - Drivermanager.registerdriver (New Com.mysql.jdbc.Driver ()); -         */ A          +         //2 Getting the connection connection theProperties info =NewProperties (); -Info.setproperty ("User", "root"); $Info.setproperty ("Password", "123456"); theConnection conn = drivermanager.getconnection ("Jdbc:mysql://localhost:3306/stu", info); the      the         //3 Gets the object that executes the SQL statement statement theStatement stmt =conn.createstatement (); -      in         //4 Execute the SQL statement and return the result theResultSet rs = stmt.executequery ("SELECT * FROM Student"); theSYSTEM.OUT.PRINTLN ("id" + "name" + "password" + "email" + "Brithday"); About      the         //5 Processing Results the          while(Rs.next ()) { theSystem.out.println
(Rs.getobject (1) + "" +rs.getobject (2) + "" +rs.getobject (3) + "" +rs.getobject (4) + "" +rs.getobject (5)); +System.out.println ("-------------------------------"); -         } the     Bayi         //6 Closing Resources the rs.close (); the stmt.close (); - conn.close (); -          the     } the @Test the      Public voidTest3 ()throwsexception{ the  -         //1 Load driver Use the reflection mechanism to load the driver class it is recommended to use this method theClass.forName ("Com.mysql.jdbc.Driver");//no jar drive is also available the          the         //2 Getting the connection connection94  theConnection conn = drivermanager.getconnection
("jdbc:mysql://localhost:3306/stu?user=root&password=123456"); the      the         //3 Gets the object that executes the SQL statement statement98Statement stmt =conn.createstatement (); About      -         //4 Execute the SQL statement and return the result101ResultSet rs = stmt.executequery ("SELECT * FROM Student");102SYSTEM.OUT.PRINTLN ("id" + "name" + "password" + "email" + "Brithday");103     104         //5 Processing Results the          while(Rs.next ()) {106System.out.println (Rs.getobject (1) + "" "+rs.getobject (2) +" "+
Rs.getobject (3) + "+rs.getobject (4) +" "+rs.getobject (5));107System.out.println ("-------------------------------");108         }109      the         //6 Closing Resources111 rs.close (); the stmt.close ();113 conn.close (); the     } the}

A simple summary of the individual JDBC:

in the author's understanding, JDBC is regarded as the process of transporting goods, the data in the database is what we want to transport, we must first have a driver's license to allow Transport (Class.forName (com.mysql.jdbc.Driver))

Second, there is a path to which database and the user name and password required to access the database, Drivermanager.getconnection (Url,user,password); This method returns the Connection object

Then we need a "list of goods" (Statement), tell the truck when we are directly to the warehouse to do some kind of operation, need not to bring back the truck when the data back,

Use the van's compartment (ResultSet) to bring back the data from the warehouse, if necessary. If not required, release resources, such as IO stream operations in Java SE Similar to the first established connection after close ()

The above belongs to the personal summary of the recent days of self-study, if there are shortcomings please give us a lot of advice. -isole

JDBC Link MySQL Database