向Mysql插入1000万条数据

[careers1111]

在用hibernate向Mysql插入1000万条数据 在插入将近一万条数据的时候

不多查询数据库发现插入成功 有数据 不过程序出现报错了

2010-5-23 20:32:53 org.springframework.core.CollectionFactory <clinit>
信息: JDK 1.4+ collections available
2010-5-23 20:32:53 org.springframework.core.CollectionFactory <clinit>
信息: Commons Collections 3.x available
2010-5-23 20:32:53 org.springframework.beans.factory.xml.XmlBeanDefinitionReader loadBeanDefinitions
信息: Loading XML bean definitions from class path resource [ApplicationContext.xml]
2010-5-23 20:32:54 org.springframework.context.support.AbstractRefreshableApplicationContext refreshBeanFactory
信息: Bean factory for application context [org.springframework.context.support.ClassPathXmlApplicationContext;hashCode=26281671]: org.springframework.beans.factory.support.DefaultListableBeanFactory defining beans [dataSource,sessionFactory,jdbcExceptionTranslator,hibernateTemplate,transactionManager,pmc.model.facade.dao.UserDao,pmc.model.facade.UserFacade]; root of BeanFactory hierarchy
2010-5-23 20:32:54 org.springframework.context.support.AbstractApplicationContext refresh
信息: 7 beans defined in application context [org.springframework.context.support.ClassPathXmlApplicationContext;hashCode=26281671]
2010-5-23 20:32:54 org.springframework.context.support.AbstractApplicationContext initMessageSource
信息: Unable to locate MessageSource with name 'messageSource': using default [org.springframework.context.support.DelegatingMessageSource@1462851]
2010-5-23 20:32:54 org.springframework.context.support.AbstractApplicationContext initApplicationEventMulticaster
信息: Unable to locate ApplicationEventMulticaster with name 'applicationEventMulticaster': using default [org.springframework.context.event.SimpleApplicationEventMulticaster@1865b28]
2010-5-23 20:32:54 org.springframework.beans.factory.support.DefaultListableBeanFactory preInstantiateSingletons
信息: Pre-instantiating singletons in factory [org.springframework.beans.factory.support.DefaultListableBeanFactory defining beans [dataSource,sessionFactory,jdbcExceptionTranslator,hibernateTemplate,transactionManager,pmc.model.facade.dao.UserDao,pmc.model.facade.UserFacade]; root of BeanFactory hierarchy]
2010-5-23 20:32:54 org.springframework.jdbc.datasource.DriverManagerDataSource setDriverClassName
信息: Loaded JDBC driver: com.mysql.jdbc.Driver
2010-5-23 20:32:54 org.hibernate.cfg.Environment <clinit>
信息: Hibernate 3.2.6
2010-5-23 20:32:54 org.hibernate.cfg.Environment <clinit>
信息: hibernate.properties not found
2010-5-23 20:32:54 org.hibernate.cfg.Environment buildBytecodeProvider
信息: Bytecode provider name : cglib
2010-5-23 20:32:54 org.hibernate.cfg.Environment <clinit>
信息: using JDK 1.4 java.sql.Timestamp handling
2010-5-23 20:32:54 org.hibernate.cfg.HbmBinder bindRootPersistentClassCommonValues
信息: Mapping class: pmc.model.pojo.PmcUser -> pmc_user
2010-5-23 20:32:54 org.hibernate.cfg.HbmBinder bindRootPersistentClassCommonValues
信息: Mapping class: pmc.model.pojo.Province -> t_province
2010-5-23 20:32:54 org.hibernate.cfg.HbmBinder bindRootPersistentClassCommonValues
信息: Mapping class: pmc.model.pojo.City -> t_city
2010-5-23 20:32:54 org.hibernate.cfg.HbmBinder bindRootPersistentClassCommonValues
信息: Mapping class: pmc.model.pojo.Town -> t_town
2010-5-23 20:32:54 org.hibernate.cfg.HbmBinder bindRootPersistentClassCommonValues
信息: Mapping class: pmc.model.pojo.Content -> t_content
2010-5-23 20:32:54 org.springframework.orm.hibernate3.LocalSessionFactoryBean afterPropertiesSet
信息: Building new Hibernate SessionFactory
2010-5-23 20:32:54 org.hibernate.cfg.Environment verifyProperties
警告: Property [hibernate.cglib.use_reflection_optimizer] has been renamed to [hibernate.bytecode.use_reflection_optimizer]; update your properties appropriately
2010-5-23 20:32:54 org.hibernate.connection.ConnectionProviderFactory newConnectionProvider
信息: Initializing connection provider: org.springframework.orm.hibernate3.LocalDataSourceConnectionProvider
2010-5-23 20:32:54 org.hibernate.cfg.SettingsFactory buildSettings
信息: RDBMS: MySQL, version: 5.0.18-nt
2010-5-23 20:32:54 org.hibernate.cfg.SettingsFactory buildSettings
信息: JDBC driver: MySQL-AB JDBC Driver, version: mysql-connector-java-5.1.8 ( Revision: ${svn.Revision} )
2010-5-23 20:32:54 org.hibernate.dialect.Dialect <init>
信息: Using dialect: org.hibernate.dialect.MySQLDialect
2010-5-23 20:32:54 org.hibernate.transaction.TransactionFactoryFactory buildTransactionFactory
信息: Using default transaction strategy (direct JDBC transactions)
2010-5-23 20:32:54 org.hibernate.transaction.TransactionManagerLookupFactory getTransactionManagerLookup
信息: No TransactionManagerLookup configured (in JTA environment, use of read-write or transactional second-level cache is not recommended)
2010-5-23 20:32:54 org.hibernate.cfg.SettingsFactory buildSettings
信息: Automatic flush during beforeCompletion(): disabled
2010-5-23 20:32:54 org.hibernate.cfg.SettingsFactory buildSettings
信息: Automatic session close at end of transaction: disabled
2010-5-23 20:32:54 org.hibernate.cfg.SettingsFactory buildSettings
信息: JDBC batch size: 15
2010-5-23 20:32:54 org.hibernate.cfg.SettingsFactory buildSettings
信息: JDBC batch updates for versioned data: disabled
2010-5-23 20:32:54 org.hibernate.cfg.SettingsFactory buildSettings
信息: Scrollable result sets: enabled
2010-5-23 20:32:54 org.hibernate.cfg.SettingsFactory buildSettings
信息: JDBC3 getGeneratedKeys(): enabled
2010-5-23 20:32:54 org.hibernate.cfg.SettingsFactory buildSettings
信息: Connection release mode: on_close
2010-5-23 20:32:54 org.hibernate.cfg.SettingsFactory buildSettings
信息: Maximum outer join fetch depth: 2
2010-5-23 20:32:54 org.hibernate.cfg.SettingsFactory buildSettings
信息: Default batch fetch size: 1
2010-5-23 20:32:54 org.hibernate.cfg.SettingsFactory buildSettings
信息: Generate SQL with comments: disabled
2010-5-23 20:32:54 org.hibernate.cfg.SettingsFactory buildSettings
信息: Order SQL updates by primary key: disabled
2010-5-23 20:32:54 org.hibernate.cfg.SettingsFactory buildSettings
信息: Order SQL inserts for batching: disabled
2010-5-23 20:32:54 org.hibernate.cfg.SettingsFactory createQueryTranslatorFactory
信息: Query translator: org.hibernate.hql.ast.ASTQueryTranslatorFactory
2010-5-23 20:32:54 org.hibernate.hql.ast.ASTQueryTranslatorFactory <init>
信息: Using ASTQueryTranslatorFactory
2010-5-23 20:32:54 org.hibernate.cfg.SettingsFactory buildSettings
信息: Query language substitutions: {}
2010-5-23 20:32:54 org.hibernate.cfg.SettingsFactory buildSettings
信息: JPA-QL strict compliance: disabled
2010-5-23 20:32:54 org.hibernate.cfg.SettingsFactory buildSettings
信息: Second-level cache: enabled
2010-5-23 20:32:54 org.hibernate.cfg.SettingsFactory buildSettings
信息: Query cache: disabled
2010-5-23 20:32:54 org.hibernate.cfg.SettingsFactory createCacheProvider
信息: Cache provider: org.hibernate.cache.HashtableCacheProvider
2010-5-23 20:32:54 org.hibernate.cfg.SettingsFactory buildSettings
信息: Optimize cache for minimal puts: disabled
2010-5-23 20:32:54 org.hibernate.cfg.SettingsFactory buildSettings
信息: Structured second-level cache entries: disabled
2010-5-23 20:32:54 org.hibernate.cfg.SettingsFactory buildSettings
信息: Statistics: disabled
2010-5-23 20:32:54 org.hibernate.cfg.SettingsFactory buildSettings
信息: Deleted entity synthetic identifier rollback: disabled
2010-5-23 20:32:54 org.hibernate.cfg.SettingsFactory buildSettings
信息: Default entity-mode: pojo
2010-5-23 20:32:54 org.hibernate.cfg.SettingsFactory buildSettings
信息: Named query checking : enabled
2010-5-23 20:32:54 org.hibernate.impl.SessionFactoryImpl <init>
信息: building session factory
2010-5-23 20:32:55 org.hibernate.impl.SessionFactoryObjectFactory addInstance
信息: Not binding factory to JNDI, no JNDI name configured
2010-5-23 20:32:55 org.springframework.beans.factory.xml.XmlBeanDefinitionReader loadBeanDefinitions
信息: Loading XML bean definitions from class path resource [org/springframework/jdbc/support/sql-error-codes.xml]

[呼啦啦123a]

靠，怎么插入的，不会是内存溢出了吧，1000万太大了，必须分批插入 5000 一组或者，直接sql 导入比较好

[dafeicao]

看了半天..就发现四行有用的东西

严重: Could not create connection to database server. Attempted reconnect 3 times. Giving up.
2010-5-23 20:33:28 org.hibernate.event.def.AbstractFlushingEventListener performExecutions
严重: Could not synchronize database state with session
org.hibernate.exception.JDBCConnectionException: Cannot open connection

你这个数据库是在本机吗?? 没有打开的连接= =

[xfwangruixue]

Caused by: com.mysql.jdbc.exceptions.MySQLNonTransientConnectionException: Could not create connection to database server. Attempted reconnect 3 times. Giving up.

[dafeicao]

严重怀疑最后的时候..数据库进程 挂了- -

[icy_csdn]

不能建立连接，数据库没用连接沲么？

[qingzhe2008]

Caused by: com.mysql.jdbc.CommunicationsException: The driver was unable to create a connection due to an inability to establish the client portion of a socket.

This is usually caused by a limit on the number of sockets imposed by the operating system. This limit is usually configurable.

For Unix-based platforms, see the manual page for the 'ulimit' command. Kernel or system reconfiguration may also be required.

For Windows-based platforms, see Microsoft Knowledge Base Article 196271 (Q196271).

[叶涛网站推广优化]

可以干掉hibernate

[钱不是问题]

用 这 个LOAD DATA LOCAL INFILE
以前我用这个导入500万条只要2秒,不过要是txt格式

[leemiki]

楼主你也太牛了，my/sql能经得起你这么折腾吗？怀疑中

[shine333]

你的代码是不是
一个Connection，在没有close之前，使用了N多个Statement/PreparedStatment

[lq282514781]

[Quote=引用 12 楼 shine333 的回复:]

你的代码是不是
一个Connection，在没有close之前，使用了N多个Statement/PreparedStatment
[/Quote]顶

[careers1111]

3q3q3q3q

[hongyu5920]

你们都是牛人啊！

[羽蓝]

[Quote=引用 14 楼 shine333 的回复:]

要么就是更夸张的，N多个Connection
[/Quote]

我也很怀疑。

[crazylaa]

批量插入，最好还是jdbc/PreparedStatement的executeBatch

[Micle]

mysql 每次连接是有时间的，比如是30秒，你到了时间还不结束连接，mysql就自动给你断掉了，你可以调大点

[liqmicky]

使用SQL/PREPARESTATEMENT

[abcwzl]

还有看你事务的状态，hibernate 没问题的。hibernate 本身有批量更新的方法。建议去看看DEMO。

[perior]

数据库肯定挂了

[Thinking_In_IT]

1000万 条数据

建议

1 使用 数据库自身的 批量导入命令，用java 调那个脚本就行了

2 存储过程