import java.sql.*;
/*
* SQLException
*/
public class SQLExceptionDemo {
public static void main(String[] args) {
Connection conn = null;
try {
String connUrl = "jdbc:sqlserver://localhost:1433;databaseName=jdbc";
conn = DriverManager.getConnection(connUrl, "sa", "passw0rd");
String qryStmt = "SELECT * FROM employe"; // error here
PreparedStatement stmt = conn.prepareStatement(qryStmt);
ResultSet rs = stmt.executeQuery();
while (rs.next()) {
System.out.println("name = " + rs.getString("ename"));
System.out.printf("salary =" + rs.getDouble("salary"));
}
} catch (SQLException e) {
System.out.println("Message : " + e.getMessage());
System.out.println("Vendor code : " + e.getErrorCode());
} finally {
if (conn != null)
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}// end of main()
}// end of class SQLExceptionDemo
goodice0728 發表在 痞客邦 留言(0) 人氣()
/*
* Transaction交易
*/
import java.sql.*;
public class TransactionDemo {
public static void main(String[] args) {
Connection conn = null;
try {
String connUrl = "jdbc:sqlserver://localhost:1433;databaseName=jdbc";
conn = DriverManager.getConnection(connUrl, "sa", "passw0rd");
conn.setAutoCommit(false); //隱含交易開始
String insStmt = "INSERT INTO department VALUES (?, ?)";
PreparedStatement pstmt = conn.prepareStatement(insStmt);
pstmt.setInt(1, 401);
pstmt.setString(2, "Sales");
pstmt.executeUpdate();
pstmt.setInt(1, 402);
pstmt.setString(2, "Service");
pstmt.executeUpdate();
pstmt.setInt(1, 403);
pstmt.setString(2, "Production");
pstmt.executeUpdate();
conn.commit();
pstmt.setInt(1, 404);
pstmt.setString(2, "Sales1");
pstmt.executeUpdate();
pstmt.setInt(1, 405);
pstmt.setString(2, "Service2");
pstmt.executeUpdate();
pstmt.setInt(1, 406);
pstmt.setString(2, "Production2");
pstmt.executeUpdate();
conn.commit(); //結束目前的交易並將所有暫存的資料永久變更至資料庫中
conn.setAutoCommit(true); //隱含交易結束,回復自動提交模式
pstmt = conn.prepareStatement("SELECT * FROM department");
ResultSet rs = pstmt.executeQuery();
System.out.printf("%10s %20s %n", "deptno", "dname");
while (rs.next()) {
System.out.printf("%10s %20s %n", rs.getString("deptno"), rs.getString("dname"));
}
} catch (SQLException e) {
e.printStackTrace();
try {
conn.rollback(); //如果有錯誤就結束目前的交易並將暫存的資料遺棄
System.err.println("Transaction is being rolled back");
} catch (Exception e1) {
e1.printStackTrace();
}
} finally {
if (conn != null)
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}// end of main()
}// end of class TransactionDemo
goodice0728 發表在 痞客邦 留言(0) 人氣()
import java.io.BufferedWriter;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
/*
* ResultSetMetaData(常使用)
*/
public class ResultSetMetaDataDemo {
public static void main(String[] args) throws IOException {
Connection conn = null;
PreparedStatement pstmt = null;
BufferedWriter bfw = null;
try {
bfw = new BufferedWriter(new FileWriter("D:/JDBC/store/resultsetmetdata.txt"));
String Url = "jdbc:sqlserver://localhost:1433;databaseName=jdbc";
conn = DriverManager.getConnection(Url, "sa", "passw0rd");
String selectdep = "SELECT * FROM department";
pstmt = conn.prepareStatement(selectdep);
ResultSet rs = pstmt.executeQuery();
ResultSetMetaData rsmd = rs.getMetaData();
int count = rsmd.getColumnCount();// 取得欄位數,select * from..
// *代表取得表格內所有欄位數
/*
* 1.getColumnLabel():取得欄位名稱,若有別名則傳回別名,也可用getColumnName(),結果一樣
* 2.getColumnType():取得欄位在java.sql.Type定義SQL type的整數值
* EX:若欄位的資料型態是varchar,則會傳回varchar在java.sql.Type定義的整數值12
*
* 3.getColumnTypeName():取得欄位在DBMS所使用的SQL Type名稱
* EX:若欄位的資料型態是varchar,則會傳回varchar
*
*/
for (int i = 1; i <= count; i++) {
System.out.print(
rsmd.getColumnLabel(i) + "(" + rsmd.getColumnType(i) + "," + rsmd.getColumnTypeName(i) + "),");
// 將欄位名稱、型態寫入記事本
bfw.write(
rsmd.getColumnLabel(i) + "(" + rsmd.getColumnType(i) + "," + rsmd.getColumnTypeName(i) + "),");
bfw.flush();
}
System.out.print("\n");
while (rs.next()) {
for (int i = 1; i <= count; i++) {
System.out.print(rs.getString(i) + ",");
bfw.newLine();
bfw.write(rs.getString(i) + ",");// 將欄位內容寫入記事本
bfw.flush();
}
System.out.print("\n");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
bfw.close();
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
goodice0728 發表在 痞客邦 留言(0) 人氣()
import java.sql.*;
/*
* DatabaseMetaData(較少用)
* 透過Connection介面的getMetaData()取得DatabaseMetaData物件
* DatabaseMetaData物件用來取得資料庫相關資訊,通常為開發人員用來寫獨立於資料庫的驅動程式和開發工具
*/
public class DatabasetMetaDataDemo {
public static void main(String args[]) {
Connection conn = null;
ResultSet rs = null;
try {
String Url = "jdbc:sqlserver://localhost:1433;databaseName=jdbc";
conn = DriverManager.getConnection(Url, "sa", "passw0rd");
DatabaseMetaData dbmd = conn.getMetaData();
System.out.println(dbmd.getDatabaseProductName()); // 取得資料庫名稱(sqlServer、oracle...)
System.out.println(dbmd.getDriverName());// 取得驅動程式的名稱
rs = dbmd.getTableTypes();// 取得表格種類
while (rs.next()) {
System.out.print(rs.getString("TABLE_TYPE") + ",");
System.out.print("\n");
}
/*
* getPrimaryKeys(String catalog,String schema, String table)
* catalog=資料庫名稱、schema=資料輪廓 ;EX:dbo.employee 、table=資料表名稱
*/
rs = dbmd.getPrimaryKeys(null, null, "EMPLOYEE");// null表示預設
while (rs.next()) {
System.out.print("TABLE_NAME=" + rs.getString("TABLE_NAME") + ",");// 取得表格名稱
System.out.print("COLUMN_NAME=" + rs.getString("COLUMN_NAME") + ",");// 取得欄位名稱
System.out.println("KEY_SEQ=" + rs.getString("KEY_SEQ") + ",");// 取得主鍵值
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
goodice0728 發表在 痞客邦 留言(0) 人氣()
package com.lcpan;
/*
* BLOB處理大量資料的資料型態,如圖形、檔案
* (1)使用setBinaryStream寫入資料
* (2)使用getBlob取得資料
*
<一>(1) 一般有加Buffere,代表可以一次寫多個byte
(2) reader 1字元 = 2byte
inputStream 1byte
<二> (1) SQL先建立table,cre_blobtest.sql
(2) Eclipse新增Project(Advanced) -> package(com.lcpan)
(3) Eclipse加入BLOBDemo.java,執行一次(確定有抓到,因為有args[x]) -> run configurations/Arguments標籤
-> Program arguments裡點Variables -> 選string_prompt -> OK (準備設定輸入框)
(4) Advanced專案,新增資料夾res,放入Tomcate.gif
(5) 執行 -> 輸入 res/Tomcat.gif res/Tomcat1.gif
*/
import java.sql.*;
import java.io.*;
public class BLOBDemo {
public static void main(String[] args) throws IOException {
String inFile=args[0]; //檔案來源路徑
String outFile=args[1]; //檔案輸出路徑
Connection conn=null;
PreparedStatement pstmt=null;
ResultSet rs=null;
try {
String Url="jdbc:sqlserver://localhost:1433;databaseName=jdbc";
conn = DriverManager.getConnection(Url,"sa","passw0rd");
//檢查資料庫內是否有資料
String selectsql="select photo from blobtest where name=?";
pstmt=conn.prepareStatement(selectsql);
pstmt.setString(1, inFile);
rs=pstmt.executeQuery();
if(rs.next()){//若有資料則刪除
String delsql="delete from blobtest where name=?";
pstmt=conn.prepareStatement(delsql);
pstmt.setString(1, inFile);
pstmt.executeUpdate();
System.out.println("Delete blob is successful!");
}
//寫入檔案到資料庫
File f=new File(inFile); //取得檔案來源
FileInputStream fis=new FileInputStream(f); //將檔案轉成byte,為了配合底下寫入資料庫的setBinaryStream
String insertsql="insert into blobtest VALUES(?,?) ";
pstmt=conn.prepareStatement(insertsql);
pstmt.setString(1, inFile);//寫入檔案名稱
pstmt.setBinaryStream(2,fis,f.length());//寫入圖片檔
//第一個參數為?的順序,第二個參數為檔案所在位置,第三個參數為檔案的大小
pstmt.executeUpdate();
System.out.println("Insert blob is successful!");
//取出資料庫圖片檔並寫入res資料夾
String selectphoto="select photo from blobtest where name=?";
pstmt=conn.prepareStatement(selectphoto);
pstmt.setString(1, inFile);
rs=pstmt.executeQuery();
if(rs.next()){
FileOutputStream fos=new FileOutputStream(outFile);//檔案要寫入的位置
Blob b = rs.getBlob("photo");//從資料庫取得圖片檔,圖片檔用getBlob取得,並存在Blob型別的b物件
byte[] data=b.getBytes(1,(int) b.length());
//b.getBytes()回傳型別為byte[]
//1代表起始位置,b.length()為檔案大小,回傳值為long,但getBytes()函數的第二個參數只能放int型態,因此必須轉成int型態
//FileOutputStream write方法(byte[],起始位置,檔案大小)
fos.write(data,0,(int)b.length());
fos.close(); //關閉檔案
System.out.println("File output is successful!");
}
} catch (SQLException e) {
e.printStackTrace();
}
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}// end of main()
}// end of class BLOBDemo
goodice0728 發表在 痞客邦 留言(0) 人氣()
package examrepublic101;
public class Topic3 {
public static void main(String[] args) {
/*
* 請問下列五個『SomeClass.doSomething()』之呼叫用法,何者合法?何者不合法?【5 分】
*/
int [] a = {14,25,36};
int number = 1;
doSomething(number);
doSomething(a[2]);
//doSomething(a[3]);==>超出陣列範圍
doSomething(a[number]);
//doSomething(a);==>引數只能接收整數變數,不能接受陣列
}
public static void doSomething(int n)
{
System.out.println("n="+n);
}
}
goodice0728 發表在 痞客邦 留言(0) 人氣()
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
/*
* 使用jdbc資料庫的employee表格新增名為photo之欄位(欄位型態使用varbinary(MAX))儲存圖檔
* 準備5張圖片,副檔名需一致,且檔名與員工編號相同,並儲在res資料夾中,此程式使用:1001.jpg、1002.jpg....1005.jpg
* 以批次方式(addBatch()、executeBatch())寫入資料庫
*/
public class PictureInputHomeWork3 {
public static void main(String[] args) throws IOException {
Connection conn = null;
PreparedStatement stmt=null;
File f =null;
FileInputStream fis=null;
try {
String connUrl = "jdbc:sqlserver://localhost:1433;databaseName=jdbc";
conn = DriverManager.getConnection(connUrl, "sa", "passw0rd");
//使用UPDATE指令新增相片
String qryStmt = "UPDATE employee SET photo=? WHERE empno=?";
stmt = conn.prepareStatement(qryStmt);
for(int i=1001 ;i<=1005;i++){ //依員工編號塞入對應之圖檔
//圖片的存放位置,將圖片依員工編號取名,是為了方便使用for迴圈寫入資料庫
f = new File("D:/JDBC/workspace/JDBCHomeWork/res/"+i+".jpg");
//為了配合setBinaryStream,因此使用InputStream將File轉成byte型態
fis = new FileInputStream(f);
//圖片是二進位檔(byte方式儲存),因此要使用setBinaryStream();f.length()代表圖檔容量
stmt.setBinaryStream(1, fis, f.length());//第一個?儲存圖片
stmt.setInt(2,i);//第二個?為員工編號
stmt.addBatch();
}
stmt.executeBatch(); //批次寫入資料庫
} catch (SQLException e) {
e.printStackTrace();
}finally{
fis.close(); //關閉檔案
if(stmt!=null)
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
if(conn!=null)
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
goodice0728 發表在 痞客邦 留言(0) 人氣()
import java.io.BufferedReader;
import java.io.FileReader;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
/*
* 在res資料夾內新增一文字檔emp.txt,並在文字檔內設定五筆employee的資料
* 以批次方式將文字檔內資料寫入資料庫
*/
public class EmpInsertHomeWork2 {
public static void main(String[] args) throws IOException {
Connection conn = null;
PreparedStatement pstmt=null;
BufferedReader bfr = null;
try {
String connUrl = "jdbc:sqlserver://localhost:1433;databaseName=jdbc";
conn = DriverManager.getConnection(connUrl, "sa", "passw0rd");
String qryStmt = "INSERT INTO employee2 VALUES (?, ?, ?, ?, ?, ?)";
pstmt = conn.prepareStatement(qryStmt);
//使用BufferedReader讀取emp.txt
bfr=new BufferedReader(new FileReader("D:/JDBC/workspace/JDBCHomeWork/res/emp.txt"));
String data="";
while((data=bfr.readLine()) != null){ //讀出emp.txt檔的值
String result[]=data.trim().split(","); //將emp.txt內的文字以,號分隔並去除,號間的空格並儲存在result字串陣列
for(int i=0;i<result.length;i++){
System.out.println("result["+i+"]="+result[i]+" ");//印出result陣列內的資料
//EX:當i=0時pstmt.setString(1, result[0]);...以此類推
pstmt.setString(i+1, result[i]);
}
pstmt.addBatch();//addBatch()不得放在for迴圈內否則會造成錯誤
}
pstmt.executeBatch();//批次寫入資料庫
} catch (SQLException e) {
e.printStackTrace();
}
finally{
bfr.close(); //關閉檔案
if(pstmt!=null){
try {
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
}
goodice0728 發表在 痞客邦 留言(0) 人氣()
/*
* 設一變數 int batchsize=3,使批次作業每3筆即執行一次
*/
import java.sql.*;
public class BatchHomeWork1 {
public static void main(String[] args) {
Connection conn = null;
try {
String connUrl = "jdbc:sqlserver://localhost:1433;databaseName=jdbc";
conn = DriverManager.getConnection(connUrl, "sa", "passw0rd");
String qryStmt = "SELECT empno, salary FROM employee";
PreparedStatement pstmt = conn.prepareStatement(qryStmt);
ResultSet rs = pstmt.executeQuery();
String updateStmt = "UPDATE employee SET salary = ? WHERE empno = ?";
pstmt = conn.prepareStatement(updateStmt);
int count=0; //統計addBatch()內有幾次SQL指令
int batchsize=3; //當addBatch()內有3次SQL指令就寫入資料庫
while (rs.next()) {
pstmt.setDouble(1, rs.getDouble(2) * 1.1);
pstmt.setInt(2, rs.getInt(1));
pstmt.addBatch();
count++; //每執行一筆SQL指令+1
if((count%batchsize)==0){
pstmt.executeBatch();//當SQL指令筆數為3的倍數時就執行批次處理
}
}
pstmt.executeBatch();//餘數不為0的資料,等while迴圈執行完後一同寫入資料庫
qryStmt = "SELECT ename, salary FROM employee";
pstmt = conn.prepareStatement(qryStmt);
rs = pstmt.executeQuery();
while(rs.next()) {
System.out.println("name = " + rs.getString("ename"));
System.out.println("salary = " + rs.getDouble("salary"));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (conn != null)
try {
conn.close();
} catch(SQLException e) {
e.printStackTrace();
}
}
}
}
goodice0728 發表在 痞客邦 留言(0) 人氣()