import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.sql.Statement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import tech.tablesaw.api.StringColumn;
import tech.tablesaw.api.NumberColumn;
import tech.tablesaw.api.DoubleColumn;
import tech.tablesaw.api.Table;
public class ConnectJava {
static final private String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";
static final private String DB_URL = "jdbc:mysql://localhost:3306/Simon?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC";
static final String USER = "root";
static final String PASS = "admin";
public static void main(String[] args) throws IOException {
ConnectJava cj = new ConnectJava();
cj.gainFromSQL();
}
public void gainFromSQL() {
Connection conn = null;
Statement stmt = null;
ArrayList idList =new ArrayList();
ArrayList nameList =new ArrayList();
ArrayList urlList =new ArrayList();
try {
Class.forName(JDBC_DRIVER);
System.out.println("Connect to database:");
conn = DriverManager.getConnection(DB_URL, USER, PASS);
System.out.println("实例化Statement对象");
stmt = (Statement) conn.createStatement();
String sql = "SELECT id, name, url FROM websites";
ResultSet rs = stmt.executeQuery(sql);
int row = 0;
while (rs.next()) {
int id = rs.getInt("id");
String name = rs.getString("name");
String url = rs.getString("url");
idList.add(Integer.toString(id));
nameList.add(name);
urlList.add(url);
ArrayList appendList =new ArrayList();
appendList.add(Integer.toString(id));
appendList.add(name);
appendList.add(url);
appendFile(row++, appendList);
}
tableSawTest(idList, nameList, urlList);//Display the list of database.
rs.close();
stmt.close();
conn.close();
} catch (SQLException se) {
se.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (stmt != null) {
stmt.close();
}
} catch (SQLException se2) {
}
try {
if (conn != null) {
conn.close();
}
} catch (SQLException se) {
se.printStackTrace();
}
}
System.out.println("Goodbye!");
}
public void appendFile(int rowIndex, ArrayList appendList) throws IOException{
String path = "D:\\Hello.xls";
File file = new File(path);
int columnIndex = 0;
HSSFWorkbook workbook = null;
HSSFSheet sheet = null;
if(file.exists()){
FileInputStream fis = new FileInputStream(file);
workbook = new HSSFWorkbook(fis);
sheet = workbook.getSheet("Sheet0");
}else {
workbook = new HSSFWorkbook();
sheet = workbook.createSheet();
}
HSSFRow row = sheet.getRow(rowIndex);
for(String value : appendList){
if(row == null){
row = sheet.createRow(rowIndex);
}
HSSFCell cell = row.createCell(columnIndex++);
cell.setCellValue(value);
}
workbook.write(file);
}
public void tableSawTest(ArrayList idList, ArrayList nameList, ArrayList urlList) {
String[] id = new String[idList.size()];
String[] name = new String[nameList.size()];
String[] url = new String[urlList.size()];
idList.toArray(id);
nameList.toArray(name);
urlList.toArray(url);
Table table = Table.create("Complete Collection of Web sites").addColumns(
StringColumn.create("ID", id),
StringColumn.create("站点å称", name),
StringColumn.create("站点地å€", url));
System.out.println(table.print());
}
}