hibernate连接mysql， 批量插入，JDBC，主键自增，类内有其他类，pqSQL，daetime，BigDecimal 精确数值，循环只插入一条数据，卡在executeBatch();

一、hibernate连接mysql

0、配置数据库

create table VehicleDataAndLocation (
	id int not null auto_increment,
	time datetime,
	vin varchar(255),

	speed decimal,
	
	longitude decimal,
	latitude decimal,
	
	primary key (id),
	INDEX(vin),
	INDEX(time))
	PARTITION BY HASH(id) PARTITIONS 365;
			 

创建索引及分区表，按时间分区，分成365个区，一天一个区，建立VIN与time做索引

1、新建maven项目，然后到http://mvnrepository.com/拷hibernate及java-mysql的驱动jar包链接配置到pom.xml

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.dflzm.dataAnalysis</groupId>
    <artifactId>EVEnduranceMileageAnalyze</artifactId>
    <version>1.0-SNAPSHOT</version>

    <dependencies>

    <!-- https://mvnrepository.com/artifact/org.hibernate/hibernate-core hibernate核心-->
	<dependency>
	    <groupId>org.hibernate</groupId>
	    <artifactId>hibernate-core</artifactId>
	    <version>5.4.11.Final</version>
	</dependency>
    
    <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java 数据库连接驱动 -->
	<dependency>
	    <groupId>mysql</groupId>
	    <artifactId>mysql-connector-java</artifactId>
	    <version>8.0.18</version>
	</dependency>

    </dependencies>


    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>3.8.1</version>
                <configuration>
                    <source>1.8</source>
                    <target>1.8</target>
                </configuration>
            </plugin>
        </plugins>
    </build>
    

</project>

目录结构:

等待jar包下载完成....

 

2、编写数据类

public class VehicleDataAndLocation {
	//主键自动增长，不用给id赋值
	private int id ;
	
	private Date time;
	
	private String vin;
	
	private VehicleData VehicleData;
	
	private VehicleLocationData VehicleLocationData;
}

//另一文件下的子类
public class VehicleData {
    private BigDecimal  speed;                  //车速
}

//另一文件下的子类
public class VehicleLocationData {
    private BigDecimal  longitude;              //经度（东经为正，西经为负）
    private BigDecimal  latitude;               //纬度（北纬为正，南纬为负）
}

3、编写hibernate配置文件

在resources，new 一个名为hibernate.cfg.xml的文件

<?xml version="1.0" encoding="UTF-8"?>
 <!DOCTYPE hibernate-configuration PUBLIC
         "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
        "http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">
 
 <hibernate-configuration>
     <session-factory>
         <!-- 数据库JDBC配置 -->
        <property name="connection.driver_class">com.mysql.cj.jdbc.Driver</property>
        <!-- 数据库URL rewriteBatchedStatements开启数据库批处理  test：数据库database名称 -->
       	<property name="connection.url">jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true&amp;serverTimezone=GMT%2B8&amp;useUnicode=true&amp;characterEncoding=UTF-8</property>
        <!-- 账号 -->
        <property name="connection.username">root</property>
        <!-- 密码 -->
        <property name="connection.password">123456</property>

        <!-- JDBC数据库连接池大小 -->
        <property name="connection.pool_size">50</property>
		
		<!-- 在控制台输出sql -->
		<property name="hibernate.show_sql">false</property>

		<!-- 重启程序时自动更新ddl和配置 -->
		<property name="hbm2ddl.auto">update</property>
		<!-- <property name="format_sql">true</property> -->
		<!--配置hibernate 批量处理  -->
		<property name="hibernate.jdbc.batch_size">50</property> 
		<property name="hibernate.cache.use_second_level_cache">false</property>  

 		
       	<!-- SQL dialect -->
        <!-- <property name="dialect">org.hibernate.dialect.H2Dialect</property> -->
        <property name="dialect">org.hibernate.dialect.MySQL5Dialect</property>

        <!-- Disable the second-level cache  -->
      	<property name="cache.provider_class">org.hibernate.cache.internal.NoCacheProvider</property>

     	<!-- Echo all executed SQL to stdout -->
       	<property name="show_sql">true</property>
	
        <!-- 每次启动的时候，会把表先删除重新创建-create 只更新不删除创建-update -->
        <property name="hbm2ddl.auto">update</property>

        <!-- 相对路径：数据类和表关联关系文件存放路径，Hibernate会在整个classpath下查找该文件 -->
        <mapping resource="VehicleDataAndLocation.xml"/>

	</session-factory> 
	
</hibernate-configuration>

其中，mapping resource为 数据类与表结构的映射文件，采用相对路径，即，从hibernate.cfg.xml当前位置进行查找文件

4、编写数据类与表结构的映射文件：VehicleDataAndLocation.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC
        "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
        "http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd">
        
       <!-- 填入包名，结合class name，找到数据类。table为数据库表的表名 -->
<hibernate-mapping package="com.dflzm.dataAnalysis.DAO">
	<class name="VehicleDataAndLocation" table="VehicleDataAndLocation" dynamic-update="true">	
		<id name="id" type="int" column="id" >
         <!-- native:灵活操作，数据库主键id自增时，不用为id赋值 -->
		 <generator class="native" />
		</id>
		<!-- 时间要采用java.util.Date,若采用java.sql.Date就只会有日期没有 时间  -->
		<property name="time" type="java.util.Date"  column="time" />
		<property name="vin"  type="string"  column="vin" />
		
		<!-- 整车数据 identity -->
		<component name = "VehicleData">
            <!-- 数据类类型为BigDecimal对应数据库类型big_decimal -->
			<property name = "speed" type="big_decimal" column = "speed" />
			
		</component>
		
		<!-- 定位数据 -->
		<component name = "VehicleLocationData">

			<property name = "longitude" type = "big_decimal" column = "longitude" />
			<property name = "latitude" type = "big_decimal" column = "latitude" />

		</component>

	</class>
</hibernate-mapping>

要点：

1、数据类中有普通类，映射文件要用<component> </component>来表示，普通类里面的属性也要写出来

2、数据库字段类型为datetime，映射文件字段type要采用java.util.Date，若采用java.sql.Date则有日期而没有时间

3、数据类字段BigDecimal类型，对应映射文件中 big_decimal

5、编写启动类：Launcher.java

public class Launcher {
	/**
	 * @author shism
	 * @param list :List<VehicleDataAndLocation> 集合数据
	 * @return 
	 * @apiNote hibernate自带批处理程序 效率: 10000条数据 286s
	 */
	public static void insertVehicleDataAndLocation(List<VehicleDataAndLocation> list) {
		SessionFactory sessionFactory = null;
		Session session = null;
		VehicleDataAndLocation VehicleDataAndLocation = null;
		if(list.isEmpty()) return;
		
		try {
			sessionFactory = new Configuration().configure().buildSessionFactory();
			session = sessionFactory.openSession();
			session.beginTransaction();
			
			for(int i =0; i < list.size(); i++) {
                //要重新new一个对象，否则一直只是改同一个对象的数据，save的时候一个flush只写一条
				VehicleDataAndLocation = new VehicleDataAndLocation(list.get(i));
				session.save(VehicleDataAndLocation);
				if(0 == i % 100 ) {
					session.flush();
					session.clear();
				}
			}				
			session.getTransaction().commit();
		}catch(Exception e) {
			e.printStackTrace();
			session.getTransaction().rollback();
		}finally {
			session.close();
			sessionFactory.close();
		}
	}
}

使用hibernate自带的批量出入，要设置容量，在达到batch_size进行提交。在hibernate.cfg.xml配置如下信息。

<!-- 在控制台输出sql -->
<property name="hibernate.show_sql">false</property>

<!-- 重启程序时自动更新ddl和配置 -->
<property name="hbm2ddl.auto">update</property>
<!-- <property name="format_sql">true</property> -->

<!--配置hibernate 批量处理  -->
<property name="hibernate.jdbc.batch_size">50</property> 
<property name="hibernate.cache.use_second_level_cache">false</property>  

要点：

1、save的时候要重新new一个对象，否则一直只是改同一个对象的数据，save的时候一个flush只写一条。

产生问题：循环只插入一（list.zise()/100 ）条数据

2、hibernate批量插入时间过于长，10000条数据花费286s

6、mian函数调用启动函数

public class main {
	/**
	 * @param args
	 */
	public static void main(String[] args) {
        VehicleDataAndLocation test = new VehicleDataAndLocation();
        List<VehicleDataAndLocation> list = new ArrayList<VehicleDataAndLocation>()  ;
		for(long i = 0 ; i < 10000;i++) {
			test.setTime(new Date(System.currentTimeMillis()));
			test.setVin(String.valueOf(i));
				list.add(test);
		}
		
                //计时
		long startTime =  System.currentTimeMillis();
		//调用启动类
		Launcher.insertVehicleDataAndLocation(list);
		
		long endTime =  System.currentTimeMillis();
		long usedTime = (endTime-startTime)/1000;
		System.out.println("use:"+usedTime);
    }
}

二、改善插入效率，采用StatelessSession(无状态会话）

在Launcher.java编写启动类，并在mian调用相应的启动类

	/**
	 * @author shism
	 * @param list
	 * @apiNote 无状态会话，采用数据流的方式,效率 10000条数据 296s
	 */
	public static void insertVehicleDataAndLocationStatelessSession(List<VehicleDataAndLocation> list) {
		SessionFactory sessionFactory = null;
		StatelessSession session = null;
		VehicleDataAndLocation VehicleDataAndLocation = null;
		if(list.isEmpty()) return;
		
		try {
			sessionFactory = new Configuration().configure().buildSessionFactory();
			session = sessionFactory.openStatelessSession();
			session.beginTransaction();
			
			for(int i =0; i < list.size(); i++) {
				VehicleDataAndLocation = new VehicleDataAndLocation(list.get(i));
				session.insert(VehicleDataAndLocation);
			}
						
			session.getTransaction().commit();
		}catch(Exception e) {
			e.printStackTrace();
			session.getTransaction().rollback();
		}finally {
			session.close();
			sessionFactory.close();
		}
	}

执行效率：插入10000条数据，时间为296s。

三、改善插入效率，采用JDBC

/**
    * @author shism
    * @param list
    * @apiNote 效率 :10000条数据 1s
*/
    public static void insertVehicleDataAndLocationJDBC(List<VehicleDataAndLocation> list)  {
        //配置
        Configuration conf = null;
        //会话工厂
        SessionFactory sessionFactory = null;
        //服务注册
        ServiceRegistry service = null;
        //会话
        Session session = null;
		
        if(list.isEmpty()) return;
		
        try {
            conf = new Configuration().configure();
            service = new StandardServiceRegistryBuilder().applySettings(conf.getProperties()).build();
            sessionFactory = conf.buildSessionFactory(service);
            session = sessionFactory.openSession();
            session.beginTransaction();
			
            //doWork进行JDBC操作
            session.doWork(new Work() {
				
                @Override
                public void execute(Connection connection) throws SQLException {
                    // TODO Auto-generated method stub
                    String sql = "insert into VehicleDataAndLocation (time, vin,  speed, longitude, latitude) values (?, ?, ?, ?, ?)";
                    PreparedStatement PreparedStatement = connection.prepareStatement(sql);
                    //关掉自动提交事务
                    connection.setAutoCommit(false);
					
                    for(int i = 0; i < list.size(); i++) {
                        //?号对应序号
                        //timestamp包含日期与时间，与数据库datetime对应
                        PreparedStatement.setTimestamp(1, new Timestamp(list.get(i).getTime().getTime()));
                        PreparedStatement.setString(2, list.get(i).getVin());

                        PreparedStatement.setBigDecimal(3, list.get(i).getVehicleData().getSpeed());
                        PreparedStatement.setBigDecimal(4, list.get(i).getVehicleLocationData().getLongitude());
                        PreparedStatement.setBigDecimal(5, list.get(i).getVehicleLocationData().getLatitude());
                        //插入batch中
                        PreparedStatement.addBatch();
                    }
                    //插入所有数据后执行batch
                    PreparedStatement.executeBatch();
                    //数据库层提交事务
                    connection.commit();
                }
            });

            session.getTransaction().commit();
        }catch(Exception e) {
            e.printStackTrace();
            //回滚
            session.getTransaction().rollback();
        }finally {
            session.close();
            sessionFactory.close();
        }
    }

问题：执行时卡在executeBatch();不动

原因：数据库未开启批处理

解决方案：在hibernate.cfg.xml中配置 rewriteBatchedStatements = true 

<!-- 数据库URL rewriteBatchedStatements开启数据库批处理  test：数据库database名称 -->
<property name="connection.url">jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true&amp;serverTimezone=GMT%2B8&amp;useUnicode=true&amp;characterEncoding=UTF-8</property>

效率：10000条数据，花费1s。

 

四、连接pgSQL

id serial, 
time timestamp without time zone, 
vin varchar(255), 
speed decimal(20,6), 
longitude decimal(20,6), 
latitude decimal(20,6),  
primary key (id)) ;
CREATE INDEX VehicleDataAndLocation_id_index ON VehicleDataAndLocation (id);
CREATE INDEX VehicleDataAndLocation_id_time ON VehicleDataAndLocation (time);
CREATE INDEX VehicleDataAndLocation_id_vin ON VehicleDataAndLocation (vin);
CREATE TABLE VehicleDataAndLocation_0 ( check (id >= 0 and id < 500000) ) INHERITS (VehicleDataAndLocation);
CREATE TABLE VehicleDataAndLocation_1 ( check (id >= 500000 and id< 1000000) ) INHERITS (VehicleDataAndLocation);
CREATE TABLE VehicleDataAndLocation_2 ( check (id >= 1000000 and id< 1500000) ) INHERITS (VehicleDataAndLocation);
CREATE TABLE VehicleDataAndLocation_3 ( check (id >= 2000000 and id< 25000000) ) INHERITS (VehicleDataAndLocation);

主键自增，创建索引及分区表

1、pom.xml的dependencies加入pgsql的依赖

<!-- https://mvnrepository.com/artifact/org.postgresql/postgresql -->
<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.2.10</version>
</dependency>

2、配置hibernate.cfg.xml的驱动及账号密码，默认账号为 postgress

<!-- postgre sql -->
<property name="connection.driver_class">org.postgresql.Driver</property>    
<property name="connection.url">jdbc:postgresql://localhost/test</property>
		
<!-- 账号密码 -->
<property name="connection.username">postgres</property> 
<property name="connection.password">root</property>