Native JDBC, jdbc

Native JDBC, jdbc

JDBC
Java DataBase Connectivity and java DataBase connection are Java APIs used to execute SQL statements.
JDBC is a Standard Specification for Java database access. It provides unified access for different relational databases. It consists of a group of interfaces and classes written in Java.

Driver
JDBC must be connected to the driver. The driver must communicate with each other to meet the communication data format specified by the device provider. The device provider provides the driver software for the device, the software can communicate with the device.

JDBC specifications (master four core objects)
DriverManager: Used to register drivers
Connection: indicates the Connection to the database.
Statement: Database SQL Statement object
ResultSet: result set or a virtual table

Using JDBC technology, you can use the mysql driver to perform database operations:
1. Registration driver
Tell JVM what driver we are using (mysql, oracle ....)
DriverManager. registerDriver (new com. mysql. jdbc. Driver (); not recommended
There are two reasons:
> The driver is registered twice.
> Strongly dependent on the database driver jar
Solution:
Class. forName ("com. mysql. jdbc. Driver ");
2. Obtain the database connection
The database is a TCP program server that connects to the server (through three handshakes)
It is equivalent to establishing a connection path for a java program to the database server.
Static Connection getConnection (String url, String user, String password)
Attempts to establish a connection to the given Database URL.
Parameter description: Location of the url to connect to the database (url) user Username password
For example: getConnection ("jdbc: mysql: // localhost: 3306/day06", "root", "root ");
URL: a protocol between SUN and the database vendor.
Jdbc: mysql: /// localhost: 3306/day06
Protocol sub-Protocol IP Address: Port Number Database
Mysql: jdbc: mysql: // localhost: 3306/day04 or jdbc: mysql: // day14 (default local connection)
Oracle Database: jdbc: oracle: thin: @ localhost: 1521: sid
3. Get the performer object
The object used to execute SQL statements.
The interface is implemented in the database driver. All interactions with databases are based on connection objects.
Statement createStatement (); // create an SQL Statement object
4. Execute the SQL statement to obtain the result set.
Execute SQL statements using executor objects
Obtain the result set of the SQL statement (add, delete, modify, and integer, execute a query of valid rows: return a result set)
Common Methods:
? Int executeUpdate (String SQL); -- execute the insert update delete statement.
? ResultSet executeQuery (String SQL); -- execute the select statement.
? Boolean execute (String SQL); -- true is returned only when select is executed and the result is returned. If other statements are executed, false is returned.
5. processing result set
The ResultSet is actually a two-dimensional table. We can call its boolean next () method to point to a row record. When the next () method is called for the first time, it points to the position of the first row record, in this case, you can use the getXXX (int col) method provided by ResultSet (different from index from 0 and column from 1) to obtain data of the specified column:
Rs. next (); // point to the first line
Rs. getInt (1); // obtain the data in the first column of the First row
Common Methods:
? Object getObject (int index)/Object getObject (String name) to obtain any Object
? String getString (int index)/String getString (String name) to obtain the String
? Int getInt (int index)/int getInt (String name) Get integer
? Double getDouble (int index)/double getDouble (String name)
6. release resources
Like the IO stream, you need to disable all things after use! The order of close is obtained first and then closed.
Use JDBC to add, delete, modify, and query the database code:

1 public static void main (String [] args) throws Exception {2 // 1. register driver 3 Class. forName ("com. mysql. jdbc. driver "); 4 // 2. obtain the database connection 5 String url = "jdbc: mysql: // localhost: 3306/mybase4"; 6 String user = "root"; 7 String password = "root "; 8 Connection conn = DriverManager. getConnection (url, user, password); 9 // 3. obtain the performer object 10 Statement stat = conn. createStatement (); 11 // call Method 12 // update (stat); 13 // call Use the method of deleting data 14 // delete (stat); 15 // call the method of adding data 16 // insert (stat ); 17 // call the Data Query Method 18 select (stat); 19 // 6. release resource 20 stat. close (); 21 conn. close (); 22} 23 24/* 25 * use JDBC technology to query data of tables in the database 26 */27 private static void select (Statement stat) throws Exception {28 // concatenate SQL statement 29 String SQL = "SELECT * FROM category"; 30/* 31*4. run SQL Statement 32 * use Method 33 * ResultSet executeQuery (String SQL) in Statement to execute the given SQL Statement, This statement returns a single ResultSet object. 34 * return value: the Implementation Class Object of the standard ResultSet interface. The implementation class object is provided by the mysql driver. You can use the ResultSet interface to receive 35 */36 ResultSet rs = stat.exe cuteQuery (SQL); 37 System. out. println (rs); // com. mysql. jdbc. JDBC4ResultSet @ 1acb189 38/* 39*5. processing result 40 * There is a method in ResultSet 41 * boolean next () to move the cursor forward a row from the current position. 42 * if the new row is valid, true is returned. If the next row does not exist, false is returned. 43 * If a result set exists, true is returned, if no result set is returned, false 44 * is equivalent to the hasNext Method 45 */46 while (rs. next () {47/* 48 * next returns true, with result set 49 * fetch result set 50 * use the method getXXX (parameter) in ResultSet; 51 * parameter: 52 * int columnIndex: The index of the column, starting from 1. 53 * String columnLabel: column name 54 * Note: 55 * If getInt and getDouble are used to specify the data type, the returned value is the corresponding data type 56 * if you use the getObject method to return the value of the object type (only print available) 57 * if you use the getString method to return the value of the String type 58 * /59/* int i1 = rs. getInt (1); 60 String s2 = rs. getString (2); 61 System. out. println (i1 + "\ t" + s2); */62 63 // System. out. println (rs. getObject (1) + "\ t" + rs. getObject (2); 64 System. out. println (rs. getObject ("cid") + "\ t" + rs. getObject ("cname"); 65 // 5. release resource 66 rs. close (); 67} 68} 69 70/* 71 * JDBC technology is used to add 72 */73 private static void insert (Statement stat) to table data in the database) throws SQLException {74 // concatenate SQL statement 75 String SQL = "INSERT INTO category (cname) VALUES ('toy')"; 76 // 4. execute SQL statement 77 int row = stat.exe cuteUpdate (SQL); 78 // 5. processing result 79 if (row> 0) {80 System. out. println ("data added successfully! "); 81} else {82 System. out. println (" An error occurred while adding data! "); 83} 84 85} 86 87/* 88 * use JDBC technology to delete the table data in the database 89 */90 private static void delete (Statement stat) throws Exception {91 // concatenated SQL statement 92 String SQL = "DELETE FROM category WHERE cid = 5"; 93 // 4. run the SQL statement 94 int row = stat.exe cuteUpdate (SQL); 95 // 5. processing result 96 if (row> 0) {97 System. out. println ("data deleted successfully! "); 98} else {99 System. out. println (" failed to delete data! "); 100} 101 102 103/* 104 * use JDBC technology to update table data in the database 105 */106 private static void update (Statement stat) throws Exception {107 // concatenate an SQL statement 108 String SQL = "UPDATE category SET cname = 'shoes and hats' WHERE cid = 6"; 109 // 4. execute the SQL statement 110 int row = stat.exe cuteUpdate (SQL); 111 // 5. processing result 112 if (row> 0) {113 System. out. println ("data update successful! "); 114} else {115 System. out. println (" failed to update data! "); 116} 117}

1. JDBC tool Class 2: "Get database connection" operation. It will exist in all functions of addition, deletion, modification, and query in the future. It can encapsulate tool class JDBCUtils. Provides methods to obtain connection objects to reuse code. 3 code Demonstration: 4 public class JDBCUtils {5 6 // private constructor to prevent external object Creation Calling Method 7 private JDBCUtils () {8} 9 10 // defines the Connectionn variable 11 private static Connection conn; 12 13 // ensure that the code is executed only once, you can put 14 static {15 try {16 // register driver 17 Class. forName ("com. mysql. jdbc. driver "); 18 // get connection 19 String url =" jdbc: mysql: // 127.0.0.1: 3306/mybase4 "; 20 String user =" root "; 21 String password = "root"; 22 conn = DriverManager. getConnection (url, user, Password); 23} catch (Exception e) {24 // Note: if the database connection fails, stop the program 25 throw new RuntimeException (e + "database connection failed "); 26} 27} 28 29 // create a static method, obtain the database Connection object, and return 30 public static Connection getConnection () {31 return conn; 32} 33 34/* 35 * Create a static method to release the resource 36 * ResultSet rs37 * Statement stat38 * Connection conn39 */40 public static void close (ResultSet rs, statement stat, Connection conn) {41 try {42 if (rs! = Null) {43 rs. close (); 44} 45} catch (SQLException e) {46 e. printStackTrace (); 47} 48 try {49 if (stat! = Null) {50 stat. close (); 51} 52} catch (SQLException e) {53 e. printStackTrace (); 54} 55 try {56 if (conn! = Null) {57 conn. close (); 58} 59} catch (SQLException e) {60 e. printStackTrace (); 61} 62} 63}

SQL Injection Problems
SQL Injection: The content entered by the user is part of the SQL statement syntax and changes the true meaning of the original SQL statement.
Assume that the SQL statement for logon is as follows:
SELECT * FROM user table where name = user input username and password = user input PASSWORD;
At this time, when the user enters the correct account and password, the user is logged on after the information is found. However, when the user enters the XXX password: XXX 'OR 'A' = 'a, the Code actually executed becomes:
SELECT * FROM user table where name = 'xxx' and password = 'xxx' OR 'A' = 'a ';
At this time, the above query statement can always query the results. Then the user logs on directly. Obviously, we do not want to see such a result. This is an SQL injection problem.
To solve this problem, we use PreparedStatement.

PreparedStatement: precompiled object, which is a subclass of the Statement object.
Features:
High Performance
The SQL statement is compiled first.
Filters out keywords entered by users.

PreparedStatement: The placeholder must be used for all the actual parameters in each SQL statement? Replace.
String SQL = "select * from user where username =? And password =? ";
To use PreparedStatement, follow these three steps:
1. PreparedStatement preprocessing object code:
To obtain the preprocessing object, you must provide SQL statements that have been processed using placeholders.
PreparedStatement psmt = conn. prepareStatement (SQL)
2. Set actual parameters
Void setXxx (int index, Xxx xx) sets the value of the specified type for the specified parameter
Parameter 1: the serial number of the actual index parameter, starting from 1.
Parameter 2: The actual parameter value of xxx. xxx indicates the specific type.
For example:
SetString (2, "1234") is the placeholder for the 2nd locations in the SQL statement? Replace with the actual parameter "1234"
3. Execute the SQL statement:
Int executeUpdate (); -- execute the insert update delete statement.
ResultSet executeQuery (); -- execute the select statement.
Boolean execute (); -- execute select to return true execute other statements to return false.