Shulou(Shulou.com)06/02 Report--

This article shows you what are the technical knowledge points of Java, which are concise and easy to understand, which can definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

Database section

JDBC steps to connect to the database (take MYSQL as an example)

1. Load the JDBC driver:

It is implemented through the forName method of the Class class, and the driver address is put in.

When loaded successfully, an instance of the Driver class is registered with the DriverManager class.

2. Provide URL for JDBC connection and create database connection

To connect to the database, you need to request from java.sql.DriverManager and get the Connection object

This object represents a connection to a database.

Use the getConnectin () method of DriverManager to pass in the specified path to the database you want to connect to, the user name and password of the data library.

Connection con=DriverManager.getConnection (url, username, password)

& &: jdbc:mysql://localhost/test?user=root&password=123&useUnicode=true&characterEncoding=utf-8

3. Create a Statement

To execute a SQL statement, you must get an instance of java.sql.Statement

Execute a static SQL statement. It is usually implemented through a Statement instance.

Execute dynamic SQL statements. It is usually implemented through a PreparedStatement instance.

String sql = ""

Statement st = con.createStatement ()

PreparedStatement pst = con.prepareStatement (sql)

4. Execute the SQL statement

Statement interface provides three methods: executeQuery, executeUpdate and execute.

ExecuteQuery: executes the select statement and returns the ResultSet result set

ResultSet rst = pst.executeQuery ()

ExecuteUpdate: execute insert, update, delete statements

Pst.executeUpdate ()

5. Close the JDBC object

When the operation is complete, close all used JDBC objects to free up JDBC resources.

Database connection pool

The advantages of database connection pooling:

When we are not using database connection pooling, we need to create a connection every time we access the database, and after using it, we need to release and close the connection, which is very resource-consuming. When we use the database connection pool, we create a specified number of connections when tomcat starts, and then when our program uses it, we take it directly from the connection pool without creating it. Similarly, we don't need to close the connection when we are done with it, but return the connection to the connection pool for other requests to continue to use.

DBCP: relatively stable.

C3P0: high performance.

Database import and export of mysql

Configuration:

First of all, find the installation directory of mysql, enter the copy path under the bin directory, paste the bin directory of mysql in the path of the computer environment variable, authorize:, log in to mysq, and assign a certain right to a certain table to a certain user.

Grant [select,insert,update,delete,create,drop] on [databaseName]. [tableName] to [userName] @ [userIP] identified by ['connection password']

Grant select,insert,update,delete,create,drop on oa_ssh.user to root@ [IP] identified by 'root'

Assign all permissions of all libraries to a user

Grant all privileges on *. * to [userName] @ [userIp] identified by ['connection password']

Grant all privileges on *. * to root@ [IP] identified by 'root'

Assign all permissions for all libraries to all users

Grant all privileges on *. * to root@'%' identified by 'root'

Export the local database:

Mysqldump-u user name-p database name > disk: exported file name (with suffix)

Export the database remotely:

Mysqldump-h IP-u user name-p database name > exported file name (with suffix)

Export the data table remotely:

Mysqldump-u root-p-d-- add-drop-table database name > export file name (with suffix)

Import data:

Mysql-u root-p after successful login = "source disk: imported file name (with suffix)

What if an exception occurs when jdbc segments are submitted in batches?

Resolve performance issues through Map. First of all, when we submit in batches in segments, we do not use transactions, which ensures that legitimate data is automatically submitted, and illegal data is automatically rolled back on its own. In order to avoid illegal data affecting the subsequent submission of legitimate data, a dictionary table of business rules is defined to verify the data, and illegal data is recorded for subsequent processing by users, while all legal data is submitted.

Jdbc batch processing data

Batch processing data: (code optimization: improving program execution performance) reduces the number of network communications between java program code (client) and the database. The core API for bulk insertion in jdbc is the problem of inserting large amounts of data in addBatch,executeBatch: (jdbc,hibernate,ibatis)

1. Insert only one entry at a time to interact with the database multiple times (time-consuming)

two。 Bulk insert and database interact only once (memory overflow)

3. Piecewise bulk insertion (recommended)

Jdbc batch processing data is through the PreparedStatement object addbatch (), executebatch () clearbatch () to interact with the database. Usually we use piecewise batch processing to improve the performance of the program and prevent memory overflow.

1. Each sql statement interacts with the database once (non-bulk operation)

two。 Interact with the database only once (bulk operation) (memory overflow)

When the data reaches a certain amount, it interacts with the database and carries out multiple operations (segmented batch operation).

(500 or 1000)

Pst.addBatch ()

If (I > 0 & & i00 = = 0) {

Pst.executeBatch ()

Pst.clearBatch ()

}

Oracle paging

Select * from (select * from (select s. Wow rn from student s) where rn0

If you're still a little confused, join my Java architect: 766529531 and talk to senior engineers with many years of Java development experience. You can also get free video learning materials and e-book learning materials!

Basic data types of Oracle

Basic data types of Oracle (commonly used):

1. Character type

Char fixed-length string occupies 2000 bytes

Varchar2 variable length string occupies 4000 bytes

Nvarchar2 occupies 2000 characters (up to 2000 letters / Chinese)

2. Large object type (lob)

Blob: maximum length of binary data is 4G

Blob is used to save some pictures, videos and files.

For example, when we upload files, we usually store the uploaded files on the hard disk without occupying the database. When downloading, if the project is migrated, the files should also be migrated. So we can store it in the database with blob. But this also increases the burden on the database.

Clob: the maximum length of character data is 4G. There are some limitations in storing large strings varchar2 and nvarchar2. They are limited in length, but no matter using varchar2 or nvarchar2 type or clob in the database, String is used to receive them on the Java side.

3. Numerical type

Integer integer type, small integer.

Float floating point type.

Real real number type.

Number (pforce s) contains numeric types of decimal places. P represents precision and s represents the number of digits after the decimal.

Eg: number (10. 2) indicates that there can be 8 digits before the decimal point and 2 digits after the decimal point.

4. Date type

Date date (day-month-year) DD-MM-YY (HH-MI-SS)

Timestamp can be more accurate to microseconds than date. The exact range of 0: 9 defaults to 6.

The difference between id, rowid and rownum

Unique identification of the physical location of the rowid.

Id is the only logical identifier, so rowid is faster than id and is by far the fastest.

The way a record is located.

Both rowid and rownum are "pseudo series"

The so-called "pseudo sequence" is a series that is hidden by default.

A field used by rownum to mark the order of results in a result set

It is characterized by sequential marking and continuous.

In other words, only if there is a record of rownum=1, can there be a record of rownum=2.

The rownum keyword can only be with = 5

2: mysql paging

Select * from music where id limit 5

How to quickly copy data from one table to another table in 3:oracle (another table does not exist, but the data is empty)

[if! supportLists] 1 、 [endif]. When another table does not exist:

Create table new table as select * tables to be replicated by from

[if! supportLists] 2, [endif] when there is another table:

Insert into new table name select field the name of the table to be copied by from

4: music album

Find out the id album names in the special table and how many songs are below

Select s.id, min (s.sname), count (m.mid) from special s inner

Join ms m on s.id=m.id group by s.id

5: quickly delete a table (no rollback, that is, no logging)

TRUNCATE from table name

6:inner join

Select lookup information from table name 1 inner join table name 2 on table name 1. Column name = table name 2. Column name

7:left join

Left outer link select lookup information from table name 1 left join table name 2 on table name 1. Column name = table name 2. Column name

8:right join

Right outer connection select lookup information from table name 1 right join table name 2 on table name 1. Column name = table name 2. Column name

Query traversal tree structure (start with) in 9:oracle

Select * from extmenu

Start with pid=1

Connect by prior id = pid

Quickly delete the parent node and all nodes under the parent node:

Delete from extmenu where id in (

Elect * from extmenu

Start with pid=1

Connect by prior id = pid

)

10: find out the information of 60-70, 80-90, 95-100 students.

Select * from stu where chengji between 60 and 70 or between 80 and 90 or between 95 and 100

Select * from stu where chengji > 60 and chengji

< 70 or chengji >

80 and chengji

< 90 or chengji >

95 and chengji

< 100 11：用exists替换in------进行联表查询 select * from dept where exists(select * from emp where emp.deptno=dept.deptno);或select * from dept d inner join emp e on d.deptno = e.deptno(只查询出两表共同拥有的字段数据) 如果你依然觉得有些茫然，不如加入我的Java架构师之路：766529531 跟有多年Java开发经验的资深工程师聊一聊。也可获取免费的视频学习资料以及电子书学习资料喔！ 12：删除表中的重复数据： delete from xin a where a.rowid != ( select max(b.rowid) from xin b where a.name = b.name ); 13：row_number()，rank() over ，dense_rank() over 按工资排序 select sal, row_number() over(order by sal desc) rank1, rank() over(order by sal desc) rank, dense_rank() over(order by sal desc) drank from emp 14：select * from (select emp.* from( dense_rank() over(partition by departNo order by sal desc) rk from emp ) Where rk=4 ibatis批量 this.getSqlMapClientTemplate().execute( new SqlMapClientCallback() { public Object doInSqlMapClient( SqlMapExecutor executor) throws SQLException { executor.startBatch(); for (int i = 0, n = list.size(); i < n; i++) { executor.insert( "productAttach.insertProductAttach", list.get(i)); } executor.executeBatch(); return null; } }); ibatis,jdbc,hibernate的分段的实现: 都应该在组装list的时候进行拆分（如：action层加入） if(list.size() % 1000 == 0) { productAttachService.addBatch(list); list.clear(); } if (list.size() >

0)

ProductAttachService.addBatch (list)

What are the technical knowledge points of Java? have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.