package com.winway.wcloud.protal.gym;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import com.mysql.jdbc.PreparedStatement;
public class ConnectionManager {
private static Connection conn;
private static Statement statement;
public static Connection getConnection(){
String driver="com.mysql.jdbc.Driver";
String url="jdbc:mysql://127.0.0.1:3306/c79740000011";
String user="root";
String password="123456";
try {
Class.forName(driver);
conn=DriverManager.getConnection(url, user, password);
} catch (ClassNotFoundException | SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return conn;
}
public static void close(PreparedStatement statement,ResultSet resultSet){
/*if(){
}*/
}
public static void save(List<World> list){
String sql=getSql(list);
System.out.println(sql);
if(sql==null){
return;
}
try {
if(getConnection()==null){
System.out.println("连接失败");
return;
}
Statement statement=getConnection().createStatement();
//删除
String delete="delete from gym_world";
boolean dele=statement.execute(delete);
System.out.println("是否删除"+dele);
int line=statement.executeUpdate(sql);
System.out.println("插入["+line+"]行");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
try {
if(statement!=null)statement.close();
if(conn!=null)conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
public static String getSql(List<World> list){
StringBuffer sqlBf=new StringBuffer("insert into gym_world values ");
if(list==null||list.size()==0){
return null;
}
for(int i=0;i<list.size();i++){
sqlBf.append("('").append(list.get(i).getXh()).append("',");
sqlBf.append("'"+list.get(i).getName()).append("',");
sqlBf.append("'"+list.get(i).getDesc()).append("',");
sqlBf.append("'"+list.get(i).getDataType()).append("',");
sqlBf.append("'"+list.get(i).getLength()).append("',");
sqlBf.append("'"+list.get(i).getIsNull()).append("',");
sqlBf.append("'").append("'");
sqlBf.append(")");
if(i!=(list.size()-1)){
sqlBf.append(",");
}
}
return sqlBf.toString();
}
}
//////////////////////////////////////////////////////////////////////entity反射注释///////////////////////////////////////////////////////////////////////////
package com.winway.wcloud.protal.gym;
import java.lang.annotation.Annotation;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.List;
import com.winway.edcollection.data.entity.EcTowerEntity;
import com.winway.edcollection.data.entity.EdpOrgInfoEntity;
import com.winway.edcollection.data.entity.EdpRoleInfoEntity;
import com.winway.edcollection.data.entity.EdpRoleUserEntity;
import com.winway.edcollection.data.entity.EdpUserInfoEntity;
import com.winway.edcollection.data.entity.FDamagedDevEntity;
import com.winway.edcollection.data.entity.FMeteorologicalDevEntity;
import com.winway.edcollection.data.entity.FMeteorologicalLogEntity;
import com.winway.edcollection.data.entity.FRealLogEntity;
import com.winway.edcollection.data.entity.FTripEntity;
import com.winway.edcollection.data.entity.FWarningDevEntity;
import com.winway.edcollection.data.entity.FWarningEntity;
import com.winway.edcollection.data.entity.FWarningRecipientEntity;
import com.winway.edcollection.data.entity.TfDamagedDevItem;
import com.winway.edcollection.data.entity.TfDamagedDevTower;
import com.winway.edcollection.data.entity.TfPossiblePathEntity;
import com.winway.edcollection.data.entity.TfRealPointsEntity;
import com.winway.edcollection.data.entity.TfTyphoonEntity;
import com.winway.edcollection.data.entity.TfYbPointsEntity;
public class ExcuteClassEntityColumn {
public static List<World> getTable(Class<?> clazz){
List<World> list=new ArrayList<World>();
//Class<TfTyphoonEntity> clazz=TfTyphoonEntity.class;
Field[] fields=clazz.getDeclaredFields();
int indexJ=1;
for(Field field:fields){
Annotation[] annos=field.getAnnotations();
for(Annotation annotation:annos){
//System.out.println(annotation.toString());
if(annotation.toString().contains("@com.winway.wcloud.dbcore.annotation.Id")){
continue;
}
String annotationStr=annotation.toString();
String name=annotationStr.substring(annotationStr.indexOf("name=")+5, annotationStr.indexOf(")")).toUpperCase();
String descccc=annotationStr.substring(annotationStr.indexOf("desc="));
String desc=descccc.substring(descccc.indexOf("desc=")+5, descccc.indexOf(","));
String dataType11=annotationStr.substring(annotationStr.indexOf("dataType="));
String dataType=dataType11.substring(dataType11.indexOf("dataType=")+9, dataType11.indexOf(","));
String length11=annotationStr.substring(annotationStr.indexOf("length="));
String length=length11.substring(length11.indexOf("length=")+7, length11.indexOf(","));
String isNull11=annotationStr.substring(annotationStr.indexOf("isNull="));
String isNull=isNull11.substring(isNull11.indexOf("isNull=")+7, isNull11.indexOf(","));
if(isNull.equals("true")){
isNull="否";
}else{
isNull="否";
}
if(dataType.toUpperCase().endsWith("STRING")){
dataType="VARCHAR";
}
if(dataType.toUpperCase().endsWith("DECIMAL")){
dataType="DOUBLE";
length="0";
}
System.out.println(indexJ+" "+name+" "+desc+" "+" "+dataType+" "+length+" "+isNull);
World world=new World(indexJ,name, desc, dataType, Integer.valueOf(length), isNull);
list.add(world);
indexJ++;
}
}
return list;
}
public static void main(String[] args) {
//读取entity反射
List<World> list=ExcuteClassEntityColumn.getTable(EcTowerEntity.class);
ConnectionManager.save(list);
}
}
/////////////////////////////////////////////////////////////////////////////Excel内容保持到数据库里////////////////////////////////////////////////////////////////////
package com.winway.wcloud.protal.gym;
import java.io.File;
import java.io.IOException;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import com.winway.wcloud.protal.util.Excel2DBUtils;
public class ExcuteExcelContent {
static String[] heads={"Name","Code","Data Type","Length"};
public static List<World> readData(){
List<World> worlds=new ArrayList<World>();
Workbook workBook = null;
Sheet sheet;
Row row;
Cell cell;
try {
workBook=WorkbookFactory.create(new File("C:/Users/gym/Desktop/world.xlsx"));
sheet=workBook.getSheetAt(0);
int rowlastNum=sheet.getLastRowNum();
int xh=1;
for(int rowindex=2;rowindex<=rowlastNum;rowindex++){
boolean reg=false;
row=sheet.getRow(rowindex);
World world=new World();
if(row==null){
continue;
}
for(int i=0;i<heads.length;i++){
cell=row.getCell(i);
String value=getCellValue(cell);
System.out.println(value);
if(i==0&&(value==null||value.equals(""))){
break;
}
changeValue(i, value, world);
reg=true;
}
if(reg){
world.setRemark("");
world.setIsNull("否");
world.setXh(xh);
System.out.println();
worlds.add(world);
xh++;
}
}
} catch (EncryptedDocumentException | InvalidFormatException
| IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
try {
if(workBook!=null)workBook.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return worlds;
}
static void changeValue(int i,String value,World world){
switch (heads[i]) {
case "Name":
world.setDesc(value);
break;
case "Code":
world.setName(value);
break;
case "Data Type":
if(value.contains("varchar")){
value="VARCHAR";
}
world.setDataType(value.toUpperCase());
break;
case "Length":
if(value==null||value.equals("")){
world.setLength(0);
}else{
world.setLength(Integer.valueOf(value));
}
break;
default:
break;
}
}
public static String getCellValue(Cell cell) {
if(cell==null){
return null;
}
String value = "";
// 以下是判断数据的类型
switch (cell.getCellType()) {
case Cell.CELL_TYPE_NUMERIC:
value = cell.getNumericCellValue() + "";
if (DateUtil.isCellDateFormatted(cell)) {
Date date = cell.getDateCellValue();
if (date != null) {
value = new SimpleDateFormat("yyyy-MM-dd").format(date);
} else {
value = "";
}
} else {
value = new DecimalFormat("0").format(cell.getNumericCellValue());
}
break;
case Cell.CELL_TYPE_STRING: // 字符串
value = cell.getStringCellValue();
break;
case Cell.CELL_TYPE_BOOLEAN: // Boolean
value = cell.getBooleanCellValue() + "";
break;
case Cell.CELL_TYPE_FORMULA: // 公式
value = cell.getCellFormula() + "";
break;
case Cell.CELL_TYPE_BLANK: // 空值
value = "";
break;
case Cell.CELL_TYPE_ERROR: // 故障
value = "非法字符";
break;
default:
value = "未知类型";
break;
}
return value;
}
public static void main(String[] args) {
List<World> list=ExcuteExcelContent.readData();
ConnectionManager.save(list);
}
}
/*
Navicat MySQL Data Transfer
Source Server : 广西
Source Server Version : 50637
Source Host : localhost:3306
Source Database : c79740000011
Target Server Type : MYSQL
Target Server Version : 50637
File Encoding : 65001
Date: 2018-03-06 15:39:03
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for gym_world
-- ----------------------------
DROP TABLE IF EXISTS `gym_world`;
CREATE TABLE `gym_world` (
`xh` int(255) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
`desc` varchar(255) DEFAULT NULL,
`dataType` varchar(255) DEFAULT NULL,
`length` int(100) DEFAULT NULL,
`isNull` varchar(255) DEFAULT NULL,
`remark` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;
-- ----------------------------
-- Records of gym_world
-- ----------------------------
INSERT INTO `gym_world` VALUES ('1', 'TF_EVALUATION_RESULT_ID', '结果ID', 'INT', '0', '否', '');
INSERT INTO `gym_world` VALUES ('2', 'EVALUATION_TIME', '评估时间', 'DATETIME', '0', '否', '');
INSERT INTO `gym_world` VALUES ('3', 'CAPTION', '标题', 'VARCHAR', '50', '否', '');
INSERT INTO `gym_world` VALUES ('4', 'GRADE', '评分', 'DOUBLE', '0', '否', '');
INSERT INTO `gym_world` VALUES ('5', 'GRADE_LEVEL', '评分等级', 'INT', '0', '否', '');
INSERT INTO `gym_world` VALUES ('6', 'RESULT_DESC', '结果描述', 'VARCHAR', '100', '否', '');
INSERT INTO `gym_world` VALUES ('7', 'RESULT_SHOW', '结果显示', 'VARCHAR', '300', '否', '');
