JAVA--JDBC Connection Database

We know that JDBC in Java is used to connect applications and data systems, and this article mainly looks at the implementation and usage details of JDBC. Mainly includes the following points:

• Basic knowledge of JDBC (data driver)
• Connection configuration for JDBC
• Use JDBC additions and deletions to change
• Some of the usage details of JDBC

first, the basic knowledge of JDBC
The program we write in Java, whether it is a desktop application or a Web application, does not have direct access to the database system on our native computer, which requires the use of drivers to make a connection between the two.

Like this, our application needs to call different drivers for different database systems to connect to the operational database system, but for different database systems, we need to learn the use of their respective driver interfaces, or more cumbersome. And once the program is migrated, the code module that will cause data manipulation needs to be rewritten.
Therefore, in order to simplify the Java database connection operation, Sun company defined a set of Java Operation Database specification, JDBC (Java Database Connectivity). From then on, the programmer can connect and manipulate the database using pure Java code.

JDBC provides a range of interfaces, including database connections, additions and deletions, and other changes. Will go down to call the corresponding driver, then these drivers will go directly to manipulate the database, execute the SQL statement, return the results. For our programmers, we just need to learn how to use JDBC and not care about how each driver is used.

Second, the JDBC connection configuration
The following steps are required to successfully connect to our local database using JDBC:

• Download the corresponding database system-supplied driver
• To add a driver package to a JDK package
• Call the getconnection () of the DriverManager class to get the database connection object

Here's a step-by-step demonstration and explanation, first download the corresponding DBMS (the database management system provides drivers), you can use IBM DB2, or Microsoft SQL Server, or MySQL. This article uses SQL Server. : https://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=11774. Run Sqljdbc_6.0.8112.100_chs.exe unzip the file, or unzip the sqljdbc_6.0.8112.100_ directly Chs.tar.gz, then go to the extracted folder, find the JRE folder, (there should be two version 7,8), find the corresponding JDK version, copy the Sqljdbc42.jar package inside, to the local JDK folder.
The default should be in: C:\Program Files\java, go into the JDK folder (do not enter the wrong), and then Jre\lib\ext, paste the JDBC package just into it.
The following opens the IDE to create a project,main function that pastes the following code, specifically what it means, after the article will say.

 Public Static void Main(string[] args) {String drivername="Com.microsoft.sqlserver.jdbc.SQLServerDriver"; String dburl="Jdbc:sqlserver://localhost:1433;databasename= The name of one of your databases."; String username="Your database login name"; String userpwd="Your database login password";Try{//Load driverClass.forName (drivername);//Get Connection objectConnection dbconn= drivermanager.getconnection (Dburl, UserName, userpwd); System. out. println ("Database connection succeeded"); }Catch(Exception e)        {E.printstacktrace (); }    }

Run the above code if the print connection is successful, continue to the following. Otherwise, you can comment on the message or Baidu to solve. The following begins to explain each statement:
First of all, the premise of all operations is to tell the JVM what data drivers our program will use, whether it is mysql,sqlserver, or Oracle. Class.forName (drivername); This statement is the function, we look at the drivername string.

装载MySql驱动：Class.forName("com.mysql.jdbc.Driver");装载Oracle驱动：Class.forName("oracle.jdbc.driver.OracleDriver");装载SqlServer驱动：Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

Our above code used in the value of the drivername is the third parameter value, the mainstream database management system is the three, the three code is unchanged, the reader can be based on their own computer database on their own choice.
The second step is to get the connection object, if the exception is not thrown to show that the connection is successful, we start with three parameters. The first parameter is a URL, his format is:jdbc:< sub-Protocol >:< sub-name, and this format is basically provided by each database is agreed to die, you just need to select them and add their own parameters. Common three URL formats:

对于 Oracle 数据库连接，采用如下形式：jdbc:oracle:thin:@localhost:1521:sid对于 SQLServer 数据库连接，采用如下形式：jdbc:microsoft:sqlserver//localhost:1433; DatabaseName=sid对于 MYSQL 数据库连接，采用如下形式：jdbc:mysql://localhost:3306/sid

The SID is the name of a specific database in the local database. Here is the second parameter, from the name of the reader can easily determine that this is the identification, the third parameter is a password. I believe these three parameters can be easily understood. Here's a look at the DriverManager class and some of his methods.
DriverManager can call it the driver management interface, the main implementation is the function of the management of the driver. For example: Initialize the driver, start the driver to establish the JDBC Connection object, and some operations to get the log information. We mainly use it to create database connection objects, and then through this object operation database additions and deletions to change. If the database connection fails, an exception will be thrown. Below we introduce this connection object, and through it to complete the increase and deletion check.

third, use JDBC additions and deletions to change
It is necessary to obtain the database connection object to realize the operation of adding and deleting. The following are the main methods of this connection interface:

createStatement()prepareStatement(String sql)prepareCall(String sql)

First, this method returns a statement object, which is used to send a static SQL statement to the database, and the second method returns a PreparedStatement object, which is a precompiled statement object. Used primarily to send dynamic SQL statements. The specific difference is stated below. The third method returns an object that is used to perform the related operation of the stored procedure.
Let's look at the object statement that can manipulate general SQL statements. He has the following common methods:

ResultSet executeQuery(String sql)int executeUpdate(Stringexecute(String sql)intString sql )

Test method ExecuteQuery

 Public Static void Main(string[] args) {Try{//Load driverClass.forName ("Com.microsoft.sqlserver.jdbc.SQLServerDriver");//Get Connection objectConnection dbconn= Drivermanager.getconnection ("Jdbc:sqlserver://localhost:1433;databasename=fitweb","SA","123456");            Statement Statement = Dbconn.createstatement (); ResultSet rs = statement.executequery ("SELECT * from users"); while(Rs.next ()) {System. out. println (Rs.getstring ("Name"));            } rs.close ();        Dbconn.close (); }Catch(Exception e)        {E.printstacktrace (); }    }//The output of the program is the value of all the fields named name in the Users table in my table Fitweb

For the resultset result set in the code above, you can temporarily go ahead. Just know that we pass in the executable SQL statement to the ExecuteQuery method, and he returns the result after execution. For the method executeupdate the main is to complete the data table of the increase and deletion.

int x = statement.executeUpdate("UPDATE users SET NAME = ‘华为‘ WHERE NAME =‘李斯‘");

You can modify the data in the table like this, and you can insert,delete operations. For the Execute method, which does not distinguish between a query or a modification operation, you can pass any SQL statement to him, except that the query does not return a result set, or False if the contents of the table are successfully modified to return true. Here's a look at the batch SQL operation:

Statement Statement = Dbconn. Createstatement();Statement. Addbatch("UPDATE users SET name = ' Walker ' WHERE name = ' Huawei '");Statement. Addbatch("UPDATE users SET name = ' Huawei ' WHERE name = ' Walker '");Int[] Count = statement. ExecuteBatch();for (int c:count) {System. out. Print(c);System. out. Print(",");} statement. Close();Dbconn. Close();Output Result:1,1

The previous method can only manipulate one SQL statement at a time, and our executebatch is used to execute multiple SQL statements at one time, and the return value is an int array, which represents the number of rows affected after each operation of the database. The number of rows affected after the first SQL execution is saved in index 0, and so on. For the operation of precompiled statement, we put it in the next section.

Iv. Some of the use details of JDBC
The name of SQL injection must be known to all, and none of the methods we introduced earlier seem to be about how to prevent this kind of hacking. SQL injection refers to the SQL syntax injected into an SQL statement with parameters. Similar to this:

selectfromwhere name = userName

If we need to query a person's information in our application, and the condition of the query is that the user needs to enter their own user name, then we query according to the user name, as above if the user entered the following content:

"‘张三‘ or ‘李四‘";selectfromwhere name = userName等价：selectfromwhere‘张三‘‘李四‘

This will not be Zhang San and John Doe information are queried out, if the person through the big data enumeration, it is likely that all data in the data table will be queried.
We can use, pre-compile statement objects to avoid this thing. Look at the code first:

preparedstatement statement = Dbconn .preparestatement  ( "INSERT into user (Id,name) VALUES (?,?)" )  Statement.setint  (1 , 1 )  Statement.setstring  (2 ,  "Walker" )  Statement.executeupdate  ()  Statement.close  ()  Dbconn.close  () ;   

The result of the program running is that you insert a piece of data into my user table. Now let's look at what each statement means. First of all? The meaning of the placeholder means that there will be parameters passed in, but the specific value is what the temporary do not know. The following two setxxx are for assigning a placeholder and then performing a change operation to complete the database update. We say why does it prevent SQL injection? Because all the user input parameters are used? placeholder, which means that whatever you're passing in, I'll just take you as a parameter.

"‘张三‘ or ‘李四‘";selectfromwhere name = ?等价：selectfromwhere‘‘张三‘ or ‘李四‘‘//相当于：‘？‘，无论？是什么都不会产生任何问题

In addition, using this precompiled statement can greatly improve performance because he caches the SQL template, except that the remainder is cached in addition to the Parameters section, and is called directly when the next encounter occurs, improving performance. It is therefore recommended that you use this method to manipulate the database.

      Finally, a detailed introduction to the operation of the result set. It was used at the beginning of this article, but it's just a simple part. The main methods of the ResultSet (result set) interface are as follows:

boolean next  () string  getString (int  columnindex) Boolean Getboolean (int  columnindex) int  getInt ( Span class= "hljs-built_in" >int  columnindex) string  getString (string  ColumnLabel) java.sql. date  getDate (int  columnindex) java.sql.< Span class= "hljs-built_in" >time  getTime (int  columnindex) Java.sql.Timestamp Gettimestamp (int  columnindex) void Beforefirst () void Afterlast () Blob GetBlob (int  columnindex) Clob Getclob ( int  columnindex)  

This interface has more than 4,000 lines of code, we only list the common methods. We can interpret the entire result set as a two-dimensional table with one cursor for each table that iterates through all the rows. The next () method is used to determine if there is another row and the return value is Boolean. The GetXXX method represents getting the field specified in the row that the current cursor points to, either using the index to locate the field or by the name of the field. If it is an index, 1 is the starting position.
With respect to LOB type data, this is a large object, often a picture or some other kind of data. A binary large object is called a blob, and a character-type large object is called a clob. Call GetBlob or Getclob to get to the appropriate object. Through their own internal getbytes, or getInputStream and other methods, can get the content. There is no detail here.
Let's look at the operation of the multi-result set, see the code:

Statement Statement = Dbconn. Createstatement();Boolean B = Statement. Execute("SELECT * FROM Users;select * from sports;");if (b) {ResultSet rs = statement. Getresultset();while (RS. Next()) {System. out. println(RS. getString("Name"));} B = Statement. Getmoreresults();if (b) {ResultSet RS2 = statement. Getresultset();while (rs2. Next()) {System. out. println(rs2. getString("Sportsname"));}}} statement. Close();Dbconn. Close();

We call the Execute method to execute the SQL statement, and we need to execute a two SQL statement that returns TRUE if the result set is successfully obtained. The first result set is obtained by the Getresultset method, the information of all fields named name is output, and the Getmoreresults method is called to determine if there are any other result sets, if any, returns true. The current result set, that is, the second result set, is then obtained through getresultset to output information.

In order not to make the article too long, there is still one point of knowledge placed in the next chapter.

JAVA--JDBC Connection Database