JDBC Learning notes and questions summary

JDBC = Java Database Connectivity = database connection

JDBC is a set of unified APIs for accessing the database provided by Java. With this set of APIs, we are very handy when switching libraries. and switching the library does not change the code. The cost of learning is also reduced.

1. Developing a JDBC Program
1> Guide Package ==> Import the database driver provided by the manufacturer. ==> Mysql-connector-java-5.0.8-bin.jar
2> Registered Driver
3> Connection Database
4> Operations Database (Execute SQL)
5> Closing Resources
--------------------------------------------------------------------------------------------------------------- -----
Classes in 2.JDBC
DriverManager for registering drivers, getting connections
Connection represents the connection, obtains the statement object
Statement Shipping SQL statements
ResultSet running results from the database back to the Java side
--------------------------------------------------------------------------------------------------------------- ------
3.DriverManager Detail Issues

1> Registration-driven issue.
Drivermanager.registdriver (New Driver ()); ==> This kind of registration method, do not use in the future development.
* Use the following methods:
Class.forName ("Com.mysql.jdbc.Driver");
2> Why?
In the code of the driver class, we can see that there is a static block of code. The static code block has already done the registration-driven thing. So we just need to load
The Registdriver method is the equivalent of calling the driver class.
3> What are the benefits of using Class.forName?
* If the Registdriver method is called, it is equivalent to creating two driver objects, wasting resources.
* Use the Forname method. Because the name of the driver class is filled in as a string, we put the name in the configuration file each time it is read from the configuration file.
Then switching the driver class is very convenient. It also means that switching the database is convenient.

--------------------------------------------------------------------------------------------------------------- -------
4. Get the details of connection

Drivermanager.getconnection ("url", "username", "password");

URL fill in the format:
Outer protocol: Internal protocol://Host name [IP Address]: Port number/library name? parameter key 1 = parameter value & parameter key 2 = parameter value

Jdbc:mysql://localhost:3306/day15?useunicode=true&characterencoding=utf-8//Use Unicode encoding to set character encoding to Utf-8
Note: localhost indicates that the native IP can be entered directly into the native IP or with 127.0.0.1
If you connect this machine, you can omit localhost
Jdbc:mysql:///day15?useunicode=true&characterencoding=utf-8

Combined with the above-mentioned convenience to switch databases.
When we write, we can also write the above 3 parameters to the configuration file.

--------------------------------------------------------------------------------------------------------------- --------

5.Connection Object Detail Issues
Functions: 1. Links to the database
2. You can create a statement object that transports SQL statements based on this object
Method:
Statement createstatement () Create Statement Object
CallableStatement preparecall (String SQL) calls a database stored procedure (stored procedure not learned)
PreparedStatement preparestatement (String sql) Creating PreparedStatement objects (PM)
--------------------------------------------------------------------------------------------------------------- ---------
6.Statement objects
------------------------------------------------------------------------------
Notes in the Code:
Execute original, delete and change can return value true=> query has result set | false=> query has no result set
executebatch Batch Execution sql
Executeupdate perform additions, deletions and changes
ExecuteQuery executing a query
------------------------------------------------------------------------------

The object can be understood as a "trolley" that ships SQL statements to the database;
Method:
void Addbatch (String SQL) Adds a statement to the car. (For batch execution of SQL statements); Insert Update Delete
int[] ExecuteBatch () Transports the statement on the car to the database execution. The return value holds the number of rows affected by each statement after execution. Because it is multiple statements, it is assembled with numbers.
void Clearbatch () Clears the statement on the car.
----above 3 methods are batch execution SQL-related (the last lesson in the afternoon)----------------------
Boolean execute (String SQL) executes an SQL statement. If the statement returns a result set A return value of TRUE indicates that a query operation (select) was made. If the statement does not return a result set, the return false indicates that the deletion was done (insert update delete); Add or remove the check can
ResultSet executeQuery (String sql) execute a query with a result set. The result set is wrapped into a ResultSet object. Select)
int executeupdate (String SQL) Executes a statement that has no result set. Returns the number of rows affected by the statement. ( Insert Update Delete)

* * Conclusion:
Use: ExecuteQuery method
When executing a query statement use: Executeupdate method
--------------- --------------------------------------------------------------------------------------------------
7. ResultSet object
Function: When the executed statement is a query statement, the ResultSet object is used to encapsulate the query results.

Method:
**boolean Next () This method lets the pointer (cursor) in the result set Move down one line. and determine if the row has data. There is a return of true, no return false//This side resembles the iterator method by controlling the pointer to determine the

String getString (int cloumncount) obtains a string type of data from the currently pointed line. Based on the index where the column is located. Note: The index in JDBC starts from 1 and is different from Java!!
String getString (String columnName) obtains a string type of data from the currently pointed line. Based on the name of the column.

There are many kinds of methods for **getxxx series, and none of them are for different types in the database.
How does the type root GetXXX method in the database correspond?
The Get method corresponding to the database type
-------------------------------------------------
Char/varchar getString
int GetInt
bigint Getlong
Float/double/decimal getfloat/getdouble/getdouble
Datetime/timestamp getDate

--------------------------------------------------------------------------------------------------------------- ---

8.ResultSet (Learn content)
Result set scrolling;
Scrolling refers to the position of the pointer can not only downward, but also arbitrary control.
The methods involved are as follows:
A Boolean absolute (int row) moves the pointer to the specified position. The parameter is the position. The position of the first row is 1. If a negative number is filled in, the last line, for example -1=>. If the move is out of range, it will return false.
void Afterlast () moves the cursor to the end of this ResultSet object, just after the last line. (The row has no data)
void Beforefirst () moves the cursor to the beginning of this ResultSet object, just before the first line. (initial position of result)
Boolean first () moves the cursor to the top row
Boolean last () moves the cursor to the final line
The Boolean next () cursor moves down one line.
Boolean previous () next moves in the opposite direction. Move up one line.
//--------------------------------------------------------
Use resultset to modify records. (Learn)
By default, resultset cannot reverse-Modify records in the database. When you create a statement object, you create a statement that produces a ResultSet object that can modify the data by specifying parameters
Statement createstatement (int resultsettype, int resultsetconcurrency)
Parameter 1 ResultsetType-result set type
Resultset.type_forward_only, does not support the result set scrolling, can only forward.
Resultset.type_scroll_insensitive supports scrolling, insensitive, insensitive result sets. The default result set is this support scrolling refers to the method to adjust the pointer to achieve forward and reverse traversal and sensitive refers to the database data changes after the data in the result set whether or not to change if the selection of sensitive result sets of data only the ID of the data and other SQL statements to remove other data so generally do not use and Occupy resources
Resultset.type_scroll_sensitive supports scrolling, sensitive result sets.
Parameter 2 resultSetConcurrency-whether the result supports modification type
Resultset.concur_read_only does not support modification
Resultset.concur_updatable Support Modification

You can reverse-modify the data in the database using the following code:
String sql = "SELECT * from EMP";
Statement state = Conn.createstatement (resultset.type_forward_only,resultset.concur_updatable);

ResultSet rs = state.executequery (SQL);

Rs.next ();

Rs.updatestring ("ename", "haha");

Rs.updaterow ();

Conclusion: Do not use ResultSet to do the modified operation. Really want to make changes we want to do with the handwritten UPDATE statement.
Limitations: ID columns must be in the database to be reversed by the result set

//------------------------------------------------------------------------------------------------------------- ----
9. Releasing Resources
1> from small to large release. ResultSet < Statement < Connection
All 3 2> need to be released.
The Close method is called when 3> is released. An exception occurs if one of the objects is closed. Also make sure that other object shutdown methods are called.

Resultset.close ();
Statement.close ();
Connection.close ();
The above code is not guaranteed to be able to execute.

try{
Resultset.close ();
}catch (Exception e) {

}finally{
try{
Statement.close ();
}catch (Exception e) {
}
finally{
try{
Connection.close ();
}catch (Exception e) {

}
}
}

-------------------------------------------------------------------------------------------
Ten SQL injection attacks

--Early login logic is to bring the user name and password entered in the form into the following SQL statement. If the query results, then the login is considered successful.

SELECT * from USER WHERE name= ' and password= ' xxx ';

--SQL injection: Please try the following user name and password.

/* User name:
Password: XXX
*/
--Bring the user name and password into the SQL statement as follows:

SELECT * from USER WHERE name= ' xxx ' OR 1=1--' and password= ' xxx '; SELECT * from USER WHERE name= ' xxx ' OR 1=1 This is the same as the password behind the identity is commented out.

--found that the SQL statement lost its judgment effect, the conditional part becomes the identity.
--The problem with SQL injection is that the site can be logged in illegally.
//-------------------------------------------------------------------------------------------
What is the problem with thinking?
Bring the user name password into the SQL statement and discover that the SQL statement becomes the following form:
SELECT * from t_student WHERE name= ' abcd ' OR 1=1;--' and password= ' 1234 ';
The SQL statement is an identity condition. So the record must be queried. Cause anonymous landing. There's a security breach.

If so, how can we solve the problem?
1> WORKAROUND: When transporting SQL, we are using the statement object. If you change to a Preparestatement object, the problem does not occur.
2>sql statement do not spell it directly. and to do it in a precompiled way. (First pass a pure SQL statement to the database to compile the second pass the parameters in the SQL statement)
Complete as in the previous two steps. The problem can be resolved.
* Why do I use preparestatement objects to solve problems?
The execution of SQL requires compilation. The injection problem occurs because the user fills in the SQL statement and participates in the compilation. Using the Preparestatement object
When you execute a SQL statement, you are divided into two steps. The first step is to "ship" the SQL statement to MySQL compilation. Go back to the Java side and get the parameters shipped to the MySQL side.
The SQL statement that the user fills out will not participate in the compilation. It will only look like a parameter. Avoid SQL injection problems;
Preparestatement the same as the execution of the parent sentence, with different parameters for batch execution. Because it compiles only once. It saves a lot of compilation time. The efficiency will be high.

-----------------------------------------------
The difference between using the Preparestatement object and the statement object

1.Statement can be created first, and then write the SQL statement.
Preparestatement be sure to pass in the SQL statement when it is created, because it is shipped to the database before it is precompiled

Api:
PreparedStatement PST = conn.preparestatement (SQL);

2. Preparestatement to set the arguments in the statement before executing.

Api:
Pst.setstring (1, name); --the invocation of the set method depends on the type of the parameter.

Char/varchar setString
int Setint
Double setdouble
Datatime/timestamp setDate
3. Statement objects pass in SQL statements when they are actually executed
The SQL statement and corresponding parameters have been set preparestatement before execution. Execution method does not require parameters

Api:
ResultSet rs = Pst.executequery ();

Note: This is because the SQL statement is defined first, so the parameter section is used? A question mark instead of such as: String sql = "SELECT * from T_user WHERE name=?" and password=? ";
-----------------------------------------------------------------------------------------------

11. Access large text (may be encountered in the future)
CREATE TABLE MyText (
ID int primary KEY auto_increment,
Text text
)

1 Getting connected
Connection conn = Jdbcutils.getconnection ();
2 Writing SQL
String sql = "INSERT into mytext values (null,?)";
3 Creating Preparestatement
PreparedStatement PS = conn.preparestatement (SQL);
4 Setting parameters
Parameter 1: Index of the parameter
Parameter 2: Stream of text that needs to be saved
Parameter 3: File length

File F = new file ("Src/text.txt");

FileReader reader = new FileReader (f);

Ps.setcharacterstream (1, Reader, (int) f.length ());

5 Execute SQL
int result = Ps.executeupdate ();
SYSTEM.OUT.PRINTLN (result);
6 Closing Resources
Jdbcutils.close (conn, PS, NULL);

The

//setcharacterstream (parameter Index,reader stream, file length is noted by the length method in the file class to convert long to int) must be strong because the MySQL driver does not implement the long type parameter in this method
-- ---------------------------------------------------------------------------------------------
12. Store 2 binary ( multimedia files)
CREATE TABLE Myblob (
ID int primary key auto_increment,
file blob
)
--------------------------- ---------------------------------------------------------------------
13. Bulk execution of SQL (statement/preparestatement

Statement:
//2 get Statement
Statement St =conn.createstatement ();
3 Add multiple SQL statements to St in
St.addbatch ("CREATE TABLE T_stu (ID int primary key auto_increment, name varchar ())");
St.addbatch ("INSERT into t_stu values (null, ' Tom ')");
St.addbatch ("INSERT into t_stu values (null, ' Jerry ')");
St.addbatch ("INSERT into t_stu values (null, ' Jack ')");
St.addbatch ("INSERT into t_stu values (null, ' Rose ')");

4 Execute SQL
Int[] results = St.executebatch (); Returns an array of arrays representing the number of rows affected after execution
System.out.println (arrays.tostring (results)); [0,1,1,1,1] Because the first is a statement to build a table, so does not affect the row return 0

-------------------------------------------------------------------
Preparestatement:
2 Writing SQL statements
String sql = "INSERT into T_STU values (null,?)";
3 Creating Preparestatement
PreparedStatement PS = conn.preparestatement (SQL);
4 loops. Adding parameters
for (int i=0;i<100;i++) {
Ps.setstring (1, "User" +i);
Ps.addbatch ();
}
5 Batch Execution
Int[] Results =ps.executebatch ();
System.out.println (arrays.tostring (results));

Summary: Bulk execution can be flexible with statement and preparestatement the former can execute different SQL statements but occupy a high resource, the latter can only execute one SQL statement but can pass different parameters multiple times in the database compiled only once but the resource is low.

--------------------------------------------------------------------------------------------------------------- ----

Summarize the problems you are experiencing today:

Usually we talk about a connected database written as a tool class and we need to be aware of the following points:

A. The registration driver only needs one time for the same database, so we need to write this line of code to the static code block as the class loads and executes!

B. We write a method to connect to the database in the same project, and we may operate on different libraries in the same item so we make multiple connections call this method by changing the URL in the configuration file to change the library to operate, so the registration driver can not write in this method!

C. When releasing resources, pay attention to gradual release, Connection object produces statement object, and Satement object produces ResultSet object. Therefore, the reverse is turned off, and you need to determine whether the null pointer (the connection may fail) and forcibly close the upper level object with finally. code see above.

* * The index in the database starts at 1.

*int result = Ps.executeupdate (); Execute SQL This returns an int type representing the number of rows affected after the SQL statement was executed. Note: The CREATE TABLE returns 0, and the additions and deletions return 1.

* We use well-known objects when using IO streams, which makes it easy to close resources.

JDBC Learning notes and questions summary