Getting started with webday17JDBC, mySQL time type conversion in DAO mode, batch processing _ MySQL

SUN provides a set of database access specifications (that is, a set of interfaces) and protocol standards for database connection, then various database manufacturers follow SUN's specifications to provide a set of APIs to access their own database servers. The naming convention provided by SUN is

Getting started with JDBC

1. JDBC (Java DataBase Connectivity) is a Java DataBase connection. to put it bluntly, it uses Java to operate databases.

2. JDBC principles

SUN provides a set of database access specifications (that is, a set of interfaces) and protocol standards for database connection, then various database manufacturers follow SUN's specifications to provide a set of APIs to access their own database servers. The specification provided by SUN is named JDBC, and the APIs provided by various vendors that follow the JDBC specification can access their own databases are called drivers!

3. Introduction to JDBC core classes (interfaces)

The core classes in JDBC include DriverManager, Connection, Statement, and ResultSet.

DriverManager has two functions:

Register driver: this allows JDBC to know which driver to use;

Prepare to obtain the Connection: If you can obtain the Connection, it indicates that it has been connected to the database.

The Connection object indicates the Connection, and the communication with the database is expanded through this object:

The most important method of Connection is to obtain the Statement object;

Statement is used to send SQL statements to the database.

VoidexecuteUpdate (String SQL): execute update operations (insert, update, delete, etc );

Explain ResultSetexecuteQuery (String SQL): executes the query operation. after the database executes the query, the query result is ResultSet;

The ResultSet object indicates the query result set. The result set is generated only after the query operation is executed.

Booleannext (): move "row cursor" to the next row, and return whether the moving row exists.

XXXgetXXX (int col): get the value of the specified column in the current row using many get methods. the parameter is the number of columns. the number of columns starts from 1, not 0.

4. get started with JDBC

A: export jar package: driver!

B: loading driver Class: Class. forName ("Class name ");

C: ur, username, and password are provided. the url is memorized!

D: Use the DriverManager class to obtain the Connection object!

4.1: mysql database driver jar package: mysql-connector-java-5.1.13-bin.jar

4.2: get connection

Two steps are required to obtain the connection:

First, use DriverManager to register the driver.

: Register the driver:

There is only one sentence: Class. forName ("com. mysql. jdbc. Driver ")

Principle: com. mysql. jdbc. Driver will be registered to DriverManager in the static code block of the source code.

DriverManager. registerDriver (newcom. mysql. jdbc. Driver ());

Although the driver can be registered, it is hard-coded and dependent on the jar package. you need to modify the code for changing the database.

Second, use DriverManager to obtain the Connection object.

: Get connection:

There is only one line of code: DriverManager. getConnection (url, username, password)

Username and password are Database usernames and passwords

Url: used to locate the "url" of the database to be connected"

Mysql url: jdbc: mysql: // localhost: 3306/mydb1

Example:

Connectioncon = DriverManager. getConnection ("jdbc: mysql: // localhost: 3306/mydb1", "root", "123 ");

4.3: Get Statement

Statementstmt = con. createStatement ();

Statement is used to send SQL statements to be executed to the database!

4.4 send SQL add, delete, and modify statements

Stringsql = "insert into user value ('hangsan ', '123 ')";

Execute the update operation, that is, execute insert, update, delete, create table, alter table, drop table

Intm = stmt.exe cuteUpdate (SQL); // m indicates the number of affected rows.

4.5 send SQL query statements

Stringsql = "select * from user ";

ResultSetrs = stmt.exe cuteQuery (SQL );

The executeQuery () method returns the ResultSet, which encapsulates the query results and is called the result set.

4.6 read data from the result set

Rs. next (); // Move the cursor to the first line

Rs. getInt (1); // obtain the data in the first column of the first row

Common methods:

ObjectgetObject (int col)

StringgetString (int col)

IntgetInt (int col)

DoublegetDouble (int col)

Cursor method of the rolling result set

VoidbeforeFirst (): Place the cursor in front of the first line, which is also the default position of the cursor;

VoidafterLast (): Place the cursor behind the last row;

Booleanfirst (): Place the cursor on the first line. the returned value indicates whether the cursor is successfully adjusted;

Booleanlast (): Place the cursor in the last row;

BooleanisBeforeFirst (): determines whether the current cursor is placed before the first line;

BooleanisAfterLast (): whether the current cursor position is behind the last line;

BooleanisFirst (): whether the current cursor position is on the first line;

BooleanisLast (): whether the current cursor position is on the last line;

Booleanprevious (): Move the cursor up a line;

Booleannext (): Move the cursor down a line;

Booleanrelative (int row): relative displacement. when the row is a positive number, it indicates moving down the row. if it is a negative number, it indicates moving up the row;

Booleanabsolute (int row): absolute displacement. move the cursor to the specified row;

IntgetRow (): returns all rows of the current cursor.

Obtain meta data of the result set

Get metadata: rs. getMetaData (),

The returned value is ResultSetMetaData;

Number of columns in the result set: int getColumnCount ()

Get the name of the specified column: String getColumnName (intcolIndex)

4.7 close Resources

Like IO streams, close is obtained first and then closed.

Rs. close ();

Stmt. close ();

Con. close ();

Code 4.8

Public class Demo2 {/** connect to the database and the Connection is successful! * Add, delete, and modify the database */@ Testpublic void fun1 () throws ClassNotFoundException, SQLException {/** 1. obtain Connection * 1. prepare four parameters * 2. load driver class * 3. get Connection * // prepare four parameters: String driverClassName = "com. mysql. jdbc. driver "; // jdbc protocol format! Jdbc: name of the industry and commerce: Sub-protocol (defined by the industry and commerce) // for mysql, its sub-Protocol structure: // host: port number/database name String url = "jdbc: mysql: // localhost: 3306/mydb3"; String username = "root"; String password = "123 "; // load the driver Class. forName (driverClassName); // use DriverManager and save the three parameters to obtain ConnectionConnection con = DriverManager. getConnection (url, username, password);/** 2. add, delete, and modify the database. * 1. create a Statement *> Statement sender through the Connection object. its function is to send an SQL Statement to the database! * 2. call its int executeUpdate (String SQL), which can send DML, DDL * // 1. use Connection to obtain the Statement object Statement stmt = con. createStatement (); // 2. use Statement to send SQL statements! // String SQL = "INSERT INTO stu VALUES ('itcast _ 000000', 'wangwu', 88, 'male ')"; // String SQL = "UPDATE stu SET name = 'zhaoliu', age = 22," + // "gender = 'female 'WHERE number = 'itcast _ 80 '"; string SQL = "delete from stu"; int r = stmt.exe cuteUpdate (SQL); System. out. println (r);}/*** execute query * @ throws ClassNotFoundException * @ throws SQLException */@ Testpublic void fun2 () throws ClassNotFoundException, SQLExcept Ion {/** 1. get Connection * 2. get Statement, and send select Statement * 3. parse the returned "table! * // ** 1. obtain the connection * 1. prepare four major connection parameters */String driverClassName = "com. mysql. jdbc. driver "; String url =" jdbc: mysql: // localhost: 3306/exam "; String username =" root "; String password =" 123 ";/** 2. load the driver Class */Class. forName (driverClassName);/** 3. call DriverManger's getConnection () using the three parameters to obtain the Connection */Connection con = DriverManager. getConnection (url, username, password);/** 2. get Statement and execute select Statement * 1. get the Statement object: Con CreateStatement () method */Statement stmt = con. createStatement ();/** 2. call the ResultSet rs = executeQuery (String querySql) */ResultSet rs = stmt.exe cuteQuery ("select * from emp");/** 3. parse the ResultSet * 1. you can call the next () method to move the row cursor to the first line! */While (rs. next () {// Move the cursor down a row and determine whether the next row exists! Int empno = rs. getInt (1); // obtain the value of this column by column number! String ename = rs. getString ("ename"); // obtain the value of this column by column name. double sal = rs. getDouble ("sal"); System. out. println (empno + "," + ename + "," + sal);}/** 4. shut down the resource * inverted */rs. close (); stmt. close (); con. close (); // This stuff must be closed. if it is not closed, it will die !}

4.9 canonicalized code

// Normalization @ Testpublic void fun3 () throws Exception {Connection con = null; // define reference Statement stmt = null; ResultSet rs = null; try {/** 1. obtain the connection */String driverClassName = "com. mysql. jdbc. driver "; String url =" jdbc: mysql: // localhost: 3306/exam "; String username =" root "; String password =" 123 "; Class. forName (driverClassName); con = DriverManager. getConnection (url, username, password); // instantiate/** 2. create a Statement */st Mt = con. createStatement (); String SQL = "select * from emp"; rs = stmt.exe cuteQuery (SQL); rs. last (); // Move the cursor to the last rs row. beforeFirst ();/** 3. Traverse rs cyclically and print the data ** getString () and getObject! * /// While (rs. next () {// System. out. println (rs. getObject (1) + "," // + rs. getString ("ename") + "," + rs. getDouble ("sal"); //} int count = rs. getMetaData (). getColumnCount (); while (rs. next () {// traverse the row for (int I = 1; I <= count; I ++) {// traverse the column System. out. print (rs. getString (I); if (I <count) {System. out. print (",") ;}} System. out. println () ;}} catch (Exception e) {throw new RuntimeException (e);} finally {// close if (r S! = Null) rs. close (); if (stmt! = Null) stmt. close (); if (con! = Null) con. close ();}}}

5. Use of PreparedStatement

It is a subinterface of the Statement interface.

Advantages: prevents SQL attacks, improves code readability and maintainability, and improves efficiency

Usage:

An SQL template is provided. The so-called SQL template contains "?". SQL statement, where "?" Is the parameter

Use the prepareStatement (String SQL) of Connection: bind it to an SQL template when creating it;

Call the setXXX () series methods of PreparedStatement to set the question mark value.

Call the executeUpdate () or executeQuery () method. Note that the method without parameters is called;

Code

/** 1. get PreparedStatement * 1. SQL Template: All parameters are used? To replace * 2. call the Connection method to obtain PreparedStatement */String SQL = "select * from t_user where username =? And password =? "; PreparedStatement pstmt = con. prepareStatement (SQL);/** 2. assign a value to the parameter */pstmt. setString (1, username); // assign values to the 1st question marks. The value is usernamepstmt. setString (2, password); // assign a value to the 2nd question mark with the value passwordResultSet rs = pstmt.exe cuteQuery (); // call the query method and send the query statement pstmt to the database. setString (1, "liSi"); pstmt. setString (2, "123" specify parameter pstmt.exe cuteQuery ();

JdbcUtils tool class

Purpose:
Four parameters for connecting to the database are: Driver class, url, user name, and password. These parameters are associated with specific databases,
If you want to change the database in the future, you need to modify these four parameters,
In order not to modify the code, we will write a JdbcUtils class to read the configuration parameters from the configuration file and create a connection object.

Tool code v1.0

Dbconfig. properties

driverClassName=com.mysql.jdbc.Driverurl=jdbc:mysql://localhost:3306/mydb1?useUnicode=true&characterEncoding=UTF8username=rootpassword=123

Public class JdbcUtils {private static Properties props = null; // it is executed only once when the JdbcUtils class is loaded! Static {// initialize props, that is, load dbconfig. properties file to the props object try {InputStream in = JdbcUtils. class. getClassLoader (). getResourceAsStream ("dbconfig. properties "); // the configuration file path is under the src directory props = new Properties (); props. load (in);} catch (IOException e) {throw new RuntimeException (e);} // load the driver Class try {Class. forName (props. getProperty ("driverClassName");} catch (ClassNotFoundException e) {throw new RuntimeException (e);} // Get Connect! Public static Connection getConnection () throws SQLException {// get Connectionreturn DriverManager. getConnection (props. getProperty ("url"), props. getProperty ("username"), props. getProperty ("password "));}}

UserDao (for interface programming and DAO factory)

The DAO (Data Access Object) mode is to write a class that encapsulates the code used to Access the database. DAO is between the database and the Service.

Entity domain, that is, the object to operate. for example, if the table we operate on is a user table, we need to write a User class first;

DAO mode requires a DAO interface first;

Then provide an implementation class for the DAO interface;

Write a DAO factory, and the Service obtains DAO implementation through the factory.

Modify project:

1. change UserDao to an interface, and then change the original UserDao class named UserDaoImpl.

2. modify the UserDao instantiation in UserService: private UserDao userDao = DaoFactory. getUserDao ()

3. create a DaoFactory and provide getUserDao ()

Code

Factory

/*** Get the name of dao implementation class through the configuration file! * Create a class object using the class name! (Reflected !) * @ Author cxf **/public class DaoFactory {private static Properties props = null; static {// load the configuration file content to the props object try {InputStream in = DaoFactory. class. getClassLoader (). getResourceAsStream ("dao. properties "); props = new Properties (); props. load (in) ;}catch (Exception e) {throw new RuntimeException (e );}} /*** return the implementation class object of a specific UserDao * @ return */public static UserDao getUserDao () {/*** provides a configuration file, which contains the UserDao interface Name of the implementation class! * This method gets the class name of the implementation class and creates an object through reflection! * // ** Obtain the name of the dao implementation class */String daoClassName = props. getProperty ("cn. itcast. usermng. dao. userDao ");/** create an object implementing the Class through reflection */try {Class clazz = Class. forName (daoClassName); return (UserDao) clazz. newInstance () ;}catch (Exception e) {throw new RuntimeException (e );}}}

Time type

Ing between database types and types in java:

DATE → java. SQL. Date

TIME → java. SQL. Time

TIMESTAMP → java. SQL. Timestamp

All attributes in the domain object cannot be included in the java. SQL package! That is, you cannot use java. SQL. Date;

ResultSet # getDate () returns java. SQL. Date ()

PreparedStatement # setDate (int, Date). The second parameter is also java. SQL. Date.

Time type conversion:

Java. util. Date contains java. SQL. Date, Time, Timestamp

Converts the value of util to a millisecond value.

Create SQL Date, Time, Timestamp with millisecond value

Java. SQL. Date, Time, Timestamp extends java. util. Date

This step does not need to be processed: Because java. SQL. Date is java. util. Date;

Java. util. Datedate = new java. util. Date ();

Longl = date. getTime ();

Java. SQL. DatesqlDate = new java. SQL. Date (l );

1. time type in Java

The java. SQL package provides three database-related date and time types:

Date: indicates the Date, which is only year, month, and day, and has no hour, minute, and second. Time will be lost;

Time: indicates the Time, only hours, minutes, and seconds, and no year, month, or day. The date is lost;

Timestamp: indicates the Timestamp, including year, month, day, hour, minute, second, and millisecond.

These three classes are subclasses of java. util. Date.

2. time type conversion

Grant the three time types of the database to java. util. Date, which basically does not need to be converted, because it refers to the reference of the subclass object to the parent class and does not need to be converted.

Java. SQL. Date date =...

Java. util. Date d = date;

Java. SQL. Time time =...

Java. util. Date d = time;

Java. SQL. Timestamp timestamp =...

Java. util. Date d = timestamp;

When you need to convert java. util. Date to three time types of the database, you cannot directly assign values. This requires three time-type constructors of the database. The constructors of the Date, Time, and TimeStamp classes in the java. SQL package all need a long parameter, which indicates the millisecond value. To create these three types of objects, you only need to have a millisecond value. We know that java. util. Date has the getTime () method to get the millisecond value, so this conversion is not a problem.

Java. utl. Date d = new java. util. Date ();

Java. SQL. Date date = newjava. SQL. Date (d. getTime (); // The time and second will be lost.

Time time = new Time (d. getTime (); // the year, month, and day are lost.

Timestamp timestamp = newTimestamp (d. getTime ());

Big data

1. what is big data?

Big data is big byte data or big character data. Standard SQL provides the following types to store big data:

Standard SQL big data type

Type	Length
Tinyblob	28--1B (256B)
Blob	216-1B (64 K)
Mediumblob	224-1B (16 M)
Longblob	232-1B (4G)
Tinyclob	28--1B (256B)
Clob	216-1B (64 K)
Mediumclob	224-1B (16 M)
Longclob	232-1B (4G)

Mysql does not provide four types: tinyclob, clob, mediumclob, and longclob.

Tinytext, text, mediumtext, and longtext

Code

Save the mp3 file to the database!

Com. mysql. jdbc. PacketTooBigException: Packet for query is too large (9802817> 1048576). You can change this value on the server by setting the max_allowed_packet 'variable.


In my. ini, add max_allowed_packet = 10 M under [mysqld]. for example:

[Mysqld]
Max_allowed_packet = 64 M

/*** Big data ** @ author cxf **/public class Demo4 {/*** save the mp3 file to the database. * @ Throws SQLException * @ throws IOException * @ throws FileNotFoundException */@ Testpublic void fun1 () throws Exception {/** 1. obtain Connection * 2. provide an SQL template and create pstmt * 3. set parameters in the SQL template * 4. call executeUpdate () of pstmt to execute */Connection con = JdbcUtils. getConnection (); String SQL = "insert into tab_bin values (?,?,?) "; PreparedStatement pstmt = con. prepareStatement (SQL); pstmt. setInt (1, 1); pstmt. setString (2, "Dancing");/*** get Blob * 1. we have some files with the target Blob * 2. first, convert the file to byte [] * 3. then use byte [] to create Blob * // Convert the object to byte [] byte [] bytes = IOUtils. toByteArray (new FileInputStream ("F:/"); // use byte [] to create BlobBlob = new SerialBlob (bytes); // set the parameter pstmt. setBlob (3, blobw.mongopstmt.exe cuteUpdate ();}/*** read mp3 from database * @ throws SQLE Xception */@ Testpublic void fun2 () throws Exception {/** 1. create Connection */Connection con = JdbcUtils. getConnection ();/** 2. the select statement template is provided to create pstmt */String SQL = "select * from tab_bin"; PreparedStatement pstmt = con. prepareStatement (SQL);/** 3. pstmt executes the query and obtains ResultSet */ResultSet rs = pstmt.exe cuteQuery ();/** 4. obtain column data Named data in rs */if (rs. next () {Blob blob = rs. getBlob ("data");/** convert Blob into a file on the hard disk! * // ** 1. obtain the input stream object through Blob * 2. create an output stream object by yourself * 3. write data from the input stream to the output stream */InputStream in = blob. getBinaryStream (); OutputStream out = new FileOutputStream ("c:/lgfw.mp3"); IOUtils. copy (in, out );}}}

Batch processing

MySQL batch processing also needs to be enabled through Parameters: rewriteBatchedStatements = true

1 Statement batch processing

Batch processing is a batch of processing, not one by one!

When you have 10 SQL statements to execute, it is very inefficient to send an SQL statement to the server at a time!

The solution is to use batch processing, that is, to send multiple SQL statements to the server at a time, and then the server processes them at a time.

Batch processing only targets update (add, delete, and modify) statements. batch processing does not query anything!

You can call the Statement class addBatch (Stringsql) method multiple times,

Add all the SQL statements to be executed to a batch,

Call the executeBatch () method of the Statement class to execute the statements in the current "batch.

VoidaddBatch (String SQL): add a statement to the batch;

Int [] executeBatch (): execute all statements in the "batch. The returned value indicates the row data affected by each statement;

VoidclearBatch (): clears all statements in the batch.

For (int I = 0; I <10; I ++) {String number = "S_10" + I; String name = "stu" + I; int age = 20 + I; String gender = I % 2 = 0? "Male": "female"; String SQL = "insert into stu values ('" + number + "', '" + name + "'," + age + ", '"+ gender +"') ";stmt contains a collection internally, which is used to load SQL .addbatch( SQL )}}stmt.exe cuteBatch [execute a batch, that is, all SQL statements in the batch are sent to the server] ();

After "batch" is executed, the SQL statements in the "batch" are cleared! That is to say, two consecutive calls to executeBatch () are equivalent to one call! Because the SQL statement is no longer available in the "batch" during the second call.

You can also call the clearBatch () method of Statement to clear the batch before executing the batch operation "!

2PreparedStatement batch processing

The batch processing of PreparedStatement is different because each PreparedStatement object is bound to an SQL template.

Therefore, what is added to the PreparedStatement is not an SQL statement, but "?". Assign values.

/*** Batch processing ** @ author cxf **/public class Demo5 {/*** the pstmt object contains a set of * 1. use loop crazy to add SQL parameters to pstmt. it has its own template. a set of parameters can match the template penalty to produce an SQL statement * 2. call its execution batch method to complete sending to the database! * @ Throws SQLException */@ Testpublic void fun5 () throws SQLException {/** pstmt: *> add parameters to batch *> execute batch! */Connection con = JdbcUtils. getConnection (); String SQL = "INSERT INTO t_stu VALUES (?,?,?,?) "; PreparedStatement pstmt = con. prepareStatement (SQL); // add the parameter for (int I = 0; I <10000; I ++) {pstmt. setInt (1, I + 1); pstmt. setString (2, "stu _" + I); pstmt. setInt (3, I); pstmt. setString (4, I % 2 = 0? "Male": "female"); pstmt. addBatch (); // add a batch! This set of parameters is saved to the set .} Long start = system.currenttimemillis(;;pstmt.exe cuteBatch (); // execute the batch! Long end = System. currentTimeMillis (); System. out. println (end-start); // 412764,301 }}