Java Foundation-jdbc (i)

I. Introduction to JDBC

JDBC (Java Data Base Connectivity,java database connection) is a Java API for executing SQL statements, with various data
Libraries have a set of their own specifications, Java's approach to its operation is different, which has caused great difficulties in our development. So Sun's
For a standardized specification-JDBC, all databases that want to connect to Java are subject to this standard. It is written in a set of Java languages
Classes and interfaces to implement the.
Like a computer's graphics card driver, you want to use the video card first to install the video driver. Connect to a database the same way you want to use the home database
, you first import the drive jar package for the database. Take MySQL For example, if you want to connect with MySQL data, the first step is to import MySQL
Driver Package-Mysql-connector-java-5.1.39-bin.jar, then set the connection path, user name and password to connect. JDBC is an interface,
And the driver is the implementation of the interface, each database vendor needs to provide their own driver, to connect their own company's database.

Ii. Use of JDBC

Actually the database connection and we use the QQ step is the same. First you need to have a QQ installation package to install (Import the database driver package),
Then find your QQ startup file (set connection path), enter a user name and password to log in (set the database login and password). Double-click OK
Friend Avatar Open Chat interface (Get statement execution platform), in the Chat box to enter the data you want to send (write SQL statement), click the Send button to send
Message (execute statement), close the chat box (frees the Resource). Summed up can be divided into 7 steps:
1, Import the relevant database driver package
2, registered driver.
3, get the connection.
4, Get the statement execution platform.
5 . Execute the SQL statement.
6, processing results.
7, release resources.

(a) Import the relevant database driver package

To facilitate the management of the jar package, we create a new Lib folder under the project path that is dedicated to storing the packets.

　　1.
　　2. Right-click the jar package to add, select Add to BuildPath.

(ii) database-driven registration

JDBC defines a standard driver interface driver (under the java.sql package), each of which provides an implementation class for that class. Take
MySQL example: The MySQL driver package provides the implementation class: The Driver Class (under the COM.MYSQL.JDBC package). Tool class DriverManager provides a registration drive
The method of Registerdriver (). However, it is not recommended to register the driver with this method because:
　　1, by observing the source code found such a registration driver will cause the driver has been registered 2 times.
2, not easy to later maintenance and modification.
　　And by looking at the driver class (under the COM.MYSQL.JDBC package) find that static blocks of code in this class have already registered themselves, so you can
Register by means of reflection.
Example:

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

(iii) Access to database connections

The getconnection (Url,username,password) method provided by the tool class DriverManager can be used to obtain a connection to the database. Three
The parameters were:
1, URL: The location of the connection database, he is composed of three parts, between the use: to split.
* The first part is JDBC, which indicates that the standard of connecting database is fixed;
* The second part is the database name, the first two parts can be understood as HTTPS on the Web address: the same as the URL he and the third part of the database path
is also divided by//;
* The third part is set by the database vendors, we need to understand the requirements of each database vendor, MySQL Third part of the database service
The IP address (localhost), port number (3306) of the server, and the name of the library you want to connect to (database);
2, Username: Database user name, each database in the installation will let you set the user name and password.
3, Password: the password of the database connection.
Example:

String url= "Jdbc:mysql://localhost:3306/mydb"; String user= "boom"; String pwd= "boom"; // Get Connections Connection Connection = drivermanager.getconnection (URL, user, pwd);

(iv) Get the execution platform of the SQL statement and execute the SQL statement

Once you find the software you want to open and enter your username and password, you'll get the chat window. There are 2 ways to get the execution platform of SQL statements in JDBC,
The user is connected to the class connection.
1. Create a Statement object to send SQL statements to the database: Createstatement (), this method executes the SQL statement,
Database incoming parameters to be spelled into SQL, there is an injection of security issues.
2. Create a PreparedStatement object to send the parameterized SQL statement to the database: Preparestatement (String sql),
This method executes the SQL statement with a placeholder for the parameter placeholder and assigns a value to the placeholder by means of the method without an injection problem.
Database additions and deletions to the corresponding 2 methods, query method-executeQuery, adding and deleting the Method-executeupdate ().
* Statement
1. Executes the given SQL statement, which returns a single ResultSet object: ExecuteQuery (String SQL)
2. Execute the given SQL statement, which may be an INSERT, UPDATE, or DELETE statement, or an SQL statement that does not return any content
(such as the SQL,DDL statement): executeupdate (String sql) returns a value of int-the number of rows affected.
* PreparedStatement
1. Execute the SQL query in this PreparedStatement object and return the ResultSet object generated by the query: ExecuteQuery ()
2. Execute the SQL statement in this PreparedStatement object, the statement must be a SQL data manipulation language (manipulation language,dml)
Statements, such as INSERT, UPDATE, or DELETE statements, or SQL statements with no return content, such as DDL statements: executeupdate ()
3. Set the specified parameter to the given Java string value: setString (int parameterindex-placeholder position from the beginning, String x)
Set .... The following types are based on the type of merit you want to pass in, such as: setint (int parameterindex, int x), see JDK for specific parameter types.
* ResultSet (Result set)
1. Gets the value of the specified column in the current row of this ResultSet object as a String in the Java programming language:
getString (int columnindex-Starting from the first column), not recommended.
2. Gets the value of the specified column in the current row of this ResultSet object as a String in the Java programming language:
GetTime (int columnindex-column name),the column name must be written right OH!!!Recommended.

Chestnut 1 (Statement's query):

1 //Registration Driver2Class.forName ("Com.mysql.jdbc.Driver");3String url= "Jdbc:mysql://localhost:3306/mydb";4String user= "Root";5String pwd= "Root";6 //Get Connections7Connection Connection =drivermanager.getconnection (URL, user, pwd);8 //gets the execution platform for the SQL statement9Statement Statement =connection.createstatement ();Ten //Writing SQL statements OneString sql= "select * from sort;"; A //Execute SQL statement to return result set -ResultSet query =statement.executequery (SQL); - //Print column names (for good looks) theSystem.out.println ("Sid\tsname\tsprice\tsdesc"); -System.out.println ("-------------------------------"); - //Looping result sets -  while(Query.next ()) { +System.out.println (query.getstring ("Sid") + "\ T" +query.getstring ("sname") + "\ T" -+query.getstring ("Sprice") + "\ T" +query.getstring ("Sdesc")); + } A //Close Resource at query.close (); - connection.close (); - statement.close (); -}

Chestnut 2 (modified by statement):

//Registration DriverClass.forName ("Com.mysql.jdbc.Driver"); String URL= "Jdbc:mysql://localhost:3306/mydb"; String User= "Root"; String pwd= "Root";//Get ConnectionsConnection Connection =drivermanager.getconnection (URL, user, pwd);//gets the execution platform for the SQL statementStatement Statement =connection.createstatement ();//Execute SQL statementintrow = Statement.executeupdate ("INSERT into Sort" (Sname,sprice,sdesc) VALUES (' Inflatable doll ', 160, ' shy ');); System.out.println (row);//Close Resourcequery.close (); Connection.close (); Statement.close ();}

Chestnut 3 (PreparedStatement's analog login feature):

 Public Static voidMain (string[] args)throwsException {//Registration DriverClass.forName ("Com.mysql.jdbc.Driver");//Get ConnectedConnection Connection = drivermanager.getconnection ("Jdbc:mysql://localhost:3306/mydb", "root", "root"); Scanner SC=NewScanner (system.in); System.out.println ("Please enter user name"); String User=Sc.nextline (); System.out.println ("Please enter your password"); String pwd=sc.nextline ();//according to the input parameters to the library query, use? PlaceholderString s= "SELECT * from users WHERE uuser=? and upwd=? ";//gets the execution platform for the SQL statementPreparedStatement statement =Connection.preparestatement (s);//assign a value to a placeholderStatement.setstring (1, user); Statement.setstring (2, PWD);//Execute SQL statementResultSet query =statement.executequery ();//if it is not emptyif(Query.next ()) {System.out.println ("Successful Landing");}Else{System.out.println ("Login Failed");}//Close Resourcequery.close (); Statement.close (); Connection.close ();}

Java Foundation-jdbc (i)