# JDBC IO
###### tags: `JDBC`

package mylibrary;

https://dotblogs.com.tw/jerryhuang0306/2016/03/08/120005

import java.io.BufferedReader;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class JDBC123 {
public static void main(String[] args) {
try {
String connUrl = "jdbc:sqlserver://localhost:1433;databaseName=MyDB";//資料庫選擇
java.sql.Connection conn = DriverManager.getConnection(connUrl, "sa", "manager");

FileReader fr = new FileReader("C:\\test.csv");//抓CSV檔進java
BufferedReader brdFile = new BufferedReader(fr);//bufferedReader
String strLine = null;
while((strLine = brdFile.readLine())!=null){//將CSV檔字串一列一列讀入並存起來直到沒有列為止

String[] array=strLine.split(",");//因為預設是用"，"分開所以用split切開存入字串陣列
System.out.println(strLine);
for(int i=0;i<array.length;i++){//偷看陣列元素有沒有切對
System.out.println(array[i]);
}
String qryInsert="insert into [dbo].[MyDB]\r\n"
+ " ([越區]\r\n"
+ " ,[Jan]\r\n"
+ " ,[Feb]\r\n"
+ " ,[Mar]\r\n"
+ " ,[Apr]\r\n"
+ " ,[May]\r\n"
+ " ,[Jun]\r\n"
+ " ,[Jul]\r\n"
+ " ,[Aug]\r\n"
+ " ,[Sep]\r\n"
+ " ,[OCT]\r\n"
+ " ,[NOV]\r\n"
+ " ,[DEC]) values(?,?,?,?,?,?,?,?,?,?,?,?,?)";//動態SQL指令

PreparedStatement pstmt= conn.prepareStatement(qryInsert);//因為是insert所以用PreparedStatement來接

pstmt.setString(1,array[0]);
pstmt.setString(2,array[1]);//因為這欄是用int的格式，所以將array[1]值抓出來轉int
pstmt.setString(3,array[2]);
pstmt.setString(4,array[3]);
pstmt.setString(5,array[4]);
pstmt.setString(6,array[5]);
pstmt.setString(7,array[6]);
pstmt.setString(8,array[7]);
pstmt.setString(9,array[8]);
pstmt.setString(10,array[9]);
pstmt.setString(11,array[10]);
pstmt.setString(12,array[11]);
pstmt.setString(13,array[12]);

pstmt.execute();
};
}catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}catch (SQLException e){
// TODO Auto-generated catch block
e.printStackTrace();
}
}

}
選擇 Repo
訂閱