JDBC Operations Database

? JDBC is all called: Java database Connectivity (connection to Java databases). In order to simplify and unify the operation of database, Sun Company defines a set of Java Operation Database specification, called JDBC. Learn the JDBC technical purpose of using Java technology to manipulate data records in a database   what is a driver? Two devices to communicate, to meet a certain communication data format, the data format by the device provider, the device provider for the device to provide driver software, through the software can communicate with the device   if there is no jdbc,java programmers need to face the various database-driven interface programming, development complex; Sun The company provides a set of unified JDBC Interface specifications, Java programs only need to use JDBC to operate any database, the JDBC implementation class is provided by each database vendor,  Learning JDBC1, learning jdk self-contained JDBC interface specification   java.sql Javax.sqldrivermanager driver Management class Connection Connection interface statement (PreparedStatement, CallableStatement) database operation resultset result set interface  2, must introduce different database drive in engineering to realize  JDBC experience? Programming reads data from the user table and prints it in a command-line window. CREATE TABLE User (   id int primary key auto_increment,   username varchar) Unique not null,   password varchar (1) Not null,   email varchar (+) NOT NULL);    Build experimental environment:   , mysq L Create a library and create the user table and insert the data for the table.     2, create a new Java project, and import data-driven. Second, write the program, load the database drive     DriverManager in the program. Registerdriver (Driver driver) Third, establish a connection (Connection)     Connection conn = drivermanager.getconnection (URL , the user, pass); Iv. Create a Statement object to send SQL to the database and send sql    Statement st = conn.createstatement ();    ResultSet rs = st.executequery (sql); Take data from the resultset representing the result set, print to the command Line window VI, disconnect from the database, and release the relevant resources  JDBC API details (emphasis) DriverManager class static void Registerdriver (Driver Driver)   Register a JDBC driver Note: Multiple JDBC drivers can be registered at the same time in DriverManager for example: Registering MySQL, Oralce, DB2 driver, through the JDBC URL analysis, decide which driver to use the static Connection getconnection (string url, string user, string password)   based on the JDBC URL and Username, password for a database connection   actual development, the deprecated use of drivermanager.registerdriver causes the driver to register twice, making the program dependent on the specific database API recommended: Class.forName (" Com.mysql.jdbc.Driver "); Loading the driver class completes the driver registration so that the program does not rely on MySQL api ***** do not introduce specific API JDBC related to the database urljdbc:mysql://localhost:3306/day13 here JDBC: is the JDBC Connection protocol here mysql://is MySQL database connection protocol, JDBC Sub-protocol localhost:3306 host and port DAY13 database   Common database URL syntax mysql jdbc:mysql:// Localhost:3306/day13oracle Jdbc:oracle:thin: @localhost:1521:sid    Create data Table userscreate table users (    id int PRIMARY key,   username varchar (20) Unique not null,   password varchar (a) not null,   email varchar (+) NOT null);  insert some data record insert Into the users values (1, ' Zhangsan ', ' 123 ', ' [email protected] '); INSERT into users values (2, ' Lisi ', ' 123 ', ' [email  protected], insert into users values (3, ' Wangwu ', ' 123 ', ' [email protected] '); Execute insert in  oracle The update delete must use the commit operation  4) in the project to introduce Oracle's JDBC driver installation directory \app\oracle\product\10.2.0\server\jdbc\lib\ojdbc14.jar  5) Modify the Oracle driver Class oracle.jdbc.driver.oracledriver  and url  Jdbc:oracle:thin: @localhost:1521:orcl  MySQL If the connection localhost:3306 can omit Jdbc:mysql://localhost:3306/day13---------------Jdbc:mysql:///day11jdbcurl can pass? & Common Properties of carrying parameters: Useunicode=true&characterencoding=utf-8-----------Solve the problem of manipulating database garbled  connection Connection interface Application One: Get SQL Action Object statement  conn.createstatement () The object can send SQL to the database for execution PreparedStatement Conn.preparestatement (SQL) Pre-compiles SQL statements to prevent SQL injection callablestatement conn.preparecall (SQL); The object can call a stored procedure in the database (Oracle learning later)   Application II: Management of database Transactions (Tomorrow) Conn.setautocommit (Boolean); Sets whether the transaction is automatically committed Conn.commit (); Commit database transaction conn.rollback (); The rollback database transaction statement is used to send SQL to the database to get the results of the operation sending a single sqlexecuteupdate to send an INSERT update DELETE statement to the database, returning an int type parameter, Represents the number of rows executequery  used to send a SELECT statement to the database, and the resultset result set object execute is used by the database to send any SQL statements (including the DDL DML DCL) to return a Boolean, The result of SQL execution is resultset returns true, otherwise false  sends multiple sqladdbatch (SQL) to the batch queue ExecuteBatch () executes all SQL statements in the queue, sending multiple SQL to the database at one time   Use resultset to traverse the result set while (Rs.next ()) {   //According to the Database internal column type, select the appropriate getxxx method    int----getint  & Nbsp;varchart----getstring   date-----getdate}  in java.sql define date, time, TimeStamp corresponding database in date time times Tamp type---------------Java.sql.date/time/timestamp are java.util.Date subclasses java.sql.Date only date no time java.sql.Time Only time has no date java.sql.TimeStamp both date and time  getxxx have two ways of writing the first getString (index) Result set column index the second kind getString (column name)   Think: What should the code do if the SQL statement might return a row of data, or it might not be able to find any records? -----used to log in if (Rs.next ()) { //Data}else{ //not found data} resultset advanced applications----scrolling result set connection interface createstatement ()   Returns statement object, generates resultset by default after operation of SQL to perform next forward scrolling, does not support modifying data in scrolling (read-only does not perform scrolling) The Connection interface also provides createstatement (int resultsettype, int resultsetconcurrency) to set the result set type in the Create statement object, concurrency policy   Result set type resultset.type_forward_only can only forward, call next cannot back scroll resultset.type_scroll_insensitive support result set back scrolling, Cannot view modification results resultset.type_scroll_sensitive  support result set back scrolling, view modify result   result set concurrency policy resultset.concur_read_only Read-only resultset.concur_updatable support modification   Common three combinations resultset.type_forward_only and resultset.concur_read_only  (default) Read-only does not support scrolling back to resultset.type_scroll_insensitive and resultset.concur_read_only  read-only, support rolling back Resultset.type_scroll_ Sensitive and resultset.concur_updatable support rolling back, support for data modification JDBC Completion crud example writing to user table additions and deletions, extracting public methods from duplicate Code jdbcutils tool class, Write database connection parameters to the Properties Profile   Sample code  dao mode DAO mode (data Access object): The data source operation is completely encapsulated in the persistence layer through DAO, and the business layer operates by manipulating Java objects. , complete the operation on the data source * The business layer does not need to know the underlying implementation of the data source, manipulating the data source through Java Objects  dao schema structure: 1, data source (MySQL database) 2, Business Object layer code, call DAO to complete the data source Operation 3, Dataaccessobject Data Access object, Persistence layer DAO program, package to data source additions and deletions, provide method parameters are Java Object 4, Transferobject transport object (value object) business layer by passing to the data layer to the object, complete the data source additions and deletions to change the  dao login example   using three-layer structure and DAO mode to login cn.itcast.web ----presentation Layer cn.itcast.service----Business Layer Cn.itcast.dao-----Persistence layer Cn.itcast.domain--- --Corresponding data table entity class to Object  1, write login.jsp login form, submit/day11/login2, write Loginservlet GET request data, call business layer UserService3, UserService write business logic , call data layer Userdao return result 4, get result in loginservlet, Judge result jump page   sample code  sql injection because the user input is not fully checked, and SQL is stitched together, when the user input parameters, Add some SQL keywords to the parameters to achieve the purpose of changing the results of SQL operation, or you can complete a malicious attack.  string sql = select * FROM user where username = ' and password = ';  For example: one, input username: Lao li ' or ' 1 ' = ' 1  &nb Sp Password Random select * from user where username = ' Lao li ' or ' 1 ' = ' 1 ' and password = '; * and priority execution above or   solution SQL injection: Using PRE Paredstatement instead of Statementpreparedstatement solves the principle of SQL injection, which runs in SQL where the parameter is represented by a placeholder in the form of a select * from user where username =? and password =?; Send SQL with? to the database to finish compiling (SQL with SQL not executed is compiled called precompilation), after SQL compilation, we find that two parameters are missing PreparedStatement can be? Instead of parameters sent to the database server because SQL has been compiled, special characters in parameters are not compiled as special characters and cannot be reachedSQL injection Purpose   Question: What is the SQL injection principle?    1. Connect a value that is always true at input     2. Using MySQL – Comment why PreparedStatement Can I prevent SQL injection?     Because it precompiled the SQL statement.   JDBC processing Big Data? In real-world development, the program needs to save large text or binary data blobs to the database. Text is the name of MySQL, called clob&nbsp in Oracle, the basic concept: Big data is also called LOB (Large Objects), LOB is divided into: Clob and blob clob for storing large text. Text blob is used to store binary data, such as example, sound, binary text, and so on.   for MySQL, there are only blobs, and no clob,mysql storage large text uses Text,text and blobs are divided into: tinytext (255), TEXT (64k), Mediumtext (16M) and Longtext (4G) Tinyblob, BLOBs, Mediumblob, and LONGBLOBJDBC? Business Scenario: When you need to send a batch of SQL statement execution to the database, you should avoid sending execution to the database. Instead, use JDBC's batch processing mechanism to improve execution efficiency. There are two ways to implement batching: statement.addbatch (SQL) • Execute batch SQL statement ExecuteBatch () Method: Execute Batch Command · Clearbatch () Method: Clear the Batch command? batch processing with Statement.addbatch (SQL): • Pros: You can send multiple different SQL statements to the database. • Cons:? SQL statements are not precompiled. When you send multiple statements to the database, but only the different SQL statements, you need to write a number of SQL statements repeatedly. For example:        INSERT into user (Name,password) VALUES (' AA ', ' 111 ');        INSERT INTO User (Name,password) VALUES (' BB ', ' 222 ');        Insert into user (Name,password) VALUES (' cc ', ' 333 ');        Insert into user (Name,password) VALUES (' dd ', ' 444 '); the second way to implement batching: Preparedstatement.addbatch ()   Implement batch processing with Preparedstatement.addbatch () Advantages: The post-compilation SQL statement is sent with high execution efficiency. • Cons: Only apply in batches with the same SQL statement but with different parameters. So this form of batching is often used to bulk insert data in the same table, or to bulk update the data for a table.   NOTE: The default use of PreparedStatement is not precompiled, which requires the useserverprepstmts=true parameter in the URL (the version prior to MySQL Server 4.1 does not support precompilation, And connector/j in the version after 5.0.5, the default is not to turn on the pre-compilation feature). For example: Jdbc:mysql://localhost:3306/test?useserverprepstmts=true to ensure that the MySQL driver first sends SQL statements to the server for precompilation, and then executes ExecuteQuery ( ) only sends parameters to the server.   compilation occurs two times when different PreparedStatement objects are used to execute the same SQL statement because the driver does not cache the compiled function key, resulting in two compilations. If you want to cache key for the post-compilation function, set the CACHEPREPSTMTS parameter to True. such as:jdbc:mysql://localhost:3306/test?useserverprepstmts=true&cacheprepstmts=true  MySQL batch processing also needs to be opened by parameters: Rewritebatchedstatements=true For example: jdbc:mysql://localhost:3306/test?rewritebatchedstatements= True mysql drive to use more than mysql-connector-java-5.1.13       Importjava.sql.Connection; Import Java.sql.DriverManager; Import java.sql.SQLException; Import java.sql.Statement;  Import Org.gjt.mm.mysql.Driver;Registered driver Drivermanager.registerdriver (new Driver ()); Connection TEST Connection conn = Drivermanager.getconnection ("Jdbc:mysql://localhost:3306/mydb1", "root", "root");//Through the Connection object Create an Action SQL statement object Statementstatement st = Conn.createstatement ();//Operation SQL statement String t_sql = "SELECT * from Employee";//Execute SQL return Query result set Resultset;java.sql.resultset rs = St.executequery (T_sql); Traverse the result set while (Rs.next ()) {int id = rs.getint ("id"); String name = rs.getstring ("name"); String gender = rs.getstring ("gender"); SYSTEM.OUT.PRINTLN ("Serial number:" + ID + ", Name:" + name + ", Position:" + gender+ ";");}//Release Resourcesrs.close ();st.close ();conn.close ();

JDBC Operations Database