JDBC封裝學習筆記(三)---物件導向的JDBC，使用preparedStatement

標籤：控制台   控制   ace   userinfo   注入   int   面向   公用類   .class   

使用PreparedStatement對象：為什麼要使用PreparedStatement
原因：（1）使用Statement需要拼接SQL，太費勁，也容易出錯。
String sql = "insert into userinfo" + " values (" + player.getId() + ",‘" + player.getPlayerID() + "‘,‘"+ player.getName() + "‘,‘" + player.getTeam() + "‘,‘" + player.getCity() + "‘," + player.getMaxScore()+ ",‘" +player.getDraftyear() + "‘)";

PreparedStatement 在建立時就可以直接傳入一個SQL語句，並且欄位使用預留位置即可，提供了處理預留位置值得方法
PreparedStatement ps = Connection.PreparedStatement(sql);

String sql = insert into userinfo values(?,?,?,?).這樣的額SQL語句是不容易出錯的，因此我們之前使用Statement做的更新操作，可以用PreparedStatement來代替了
在執行ps.executeUpdate()裡面不再需要傳入SQL語句
（2）PreparedStatement可以有效禁止SQL注入

  (3)  PreparedStatement是Statement的子介面

==============================================================================================================================================================

 在JDBCUtils.java中我們可以添加一個方法，實現如所示：其中傳入了一個SQL語句，還有一個可變參數，因為不知道具體的類型，所以使用了可變參數。。。。

// 使用preparedStatement對象進行更新

public static void updateDataUsePS(String sql, Object... args) {
// 擷取串連
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = JDBCUtils.getConnection();// 獲得串連
ps = conn.prepareStatement(sql); // 建立statement對象
// 擷取可變參數的值
for (int i = 0; i < args.length; i++) {
ps.setObject(i + 1, args[i]);
}
ps.executeUpdate();
// System.out.println(sql);
System.out.println("SQL執行成功");
} catch (ClassNotFoundException 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();
} finally {
JDBCUtils.closeSource(null, ps, conn);
System.out.println("資源關閉成功");
}

}

然後在實際的操作中調用這個方法：

// 插入一個新的運動員，使用preparedStatement

public void testAddPlayers1(Players player) {
String sql = "insert into userinfo values (?,?,?,?,?,?,?)";
JDBCUtils.updateDataUsePS(sql, player.getId(), player.getPlayerID(), player.getName(), player.getTeam(),
player.getCity(), player.getMaxScore(), player.getDraftyear());
}

//在測試方法中，去調用testAddPlayers1(Players player) 這個方法:

// 測試方法
@Test
public void testAddPlayers() {
Players player = getFromConsole();
testAddPlayers1(player);
// return player;
}

這裡就可以使用Junit架構進行測試了，控制台運行如下所示:

完整原始碼如下所示：

package com.jdbc.basedemo;

import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;

import org.junit.Test;

import com.jdbc.bean.Players;

public class CrudPlayer {
// 添加一個運動員，傳入一個運動員對象
public void addPlayers(Players player) {
// 給出一個SQL
String sql = "insert into userinfo" + " values (" + player.getId() + ",‘" + player.getPlayerID() + "‘,‘"
+ player.getName() + "‘,‘" + player.getTeam() + "‘,‘" + player.getCity() + "‘," + player.getMaxScore()
+ ",‘" + player.getDraftyear() + "‘)";
System.out.println(sql);
// 調用更新方法
JDBCUtils.updateData(sql);
}

// @插入一個新的運動員，使用preparedStatement

public void testAddPlayers1(Players player) {
String sql = "insert into userinfo values (?,?,?,?,?,?,?)";
JDBCUtils.updateDataUsePS(sql, player.getId(), player.getPlayerID(), player.getName(), player.getTeam(),
player.getCity(), player.getMaxScore(), player.getDraftyear());
}

// 測試方法
@Test
public void testAddPlayers() {
Players player = getFromConsole();
testAddPlayers1(player);
// return player;
}

// 從控制台擷取輸入的運動員資訊
private Players getFromConsole() {
// TODO Auto-generated method stub
Scanner input = new Scanner(System.in);
Players player = new Players();
System.out.println("請輸進球員資訊");
// 這裡因為主鍵在資料表中設定了自增，所以拼接的SQL中只擷取，而不用再一次輸入ID
System.out.print("球員ID:");
player.setPlayerID(input.next());
System.out.print("球員姓名:");
player.setName(input.next());
System.out.print("球隊名稱:");
player.setTeam(input.next());
System.out.print("球隊城市:");
player.setCity(input.next());
System.out.print("個人單場最高分:");
player.setMaxScore(input.nextInt());
System.out.println("選秀年:");
player.setDraftyear(input.next());

return player;
}

// 查詢類型
@Test
public void testGetPlayer() {
// 擷取查詢類型
int searchType = getTypeFromConsole();
// 執行查詢
Players players = searchPlayer(searchType);
// 列印運動員資訊
printPlayer(players);

// return players;
}

// 列印運動員資訊
private void printPlayer(Players players) {
// TODO Auto-generated method stub
if (players != null) {
System.out.println(players);
} else {
System.out.println("查無此人....");
}
}

// 具體查詢運動員資訊方法
private Players searchPlayer(int searchType) {
// TODO Auto-generated method stub
// select * 的寫法不好，這裡為了偷懶
String sql = "select * from userinfo" + " where ";
Scanner scanner = new Scanner(System.in);
// 根據確定的type，提示使用者輸入查詢的類型
//// 最終確認SQL語句
if (searchType == 1) {
System.out.print("請輸進球員ID編號:");
String playerID = scanner.next();
sql = sql + "playID=‘" + playerID + "‘";
} else {
System.out.print("請輸進球員姓名:");
String name = scanner.next();
sql = sql + "username=‘" + name + "‘";
}
// 執行查詢
Players player = getPlayer(sql);

return player;
}

// 執行查詢的方法,根據SQL語句執行查詢
private Players getPlayer(String sql) {
// TODO Auto-generated method stub
Players player = null;
Connection conn = null;
Statement statement = null;
ResultSet rs = null;

try {
conn = JDBCUtils.getConnection();
statement = conn.createStatement();
rs = statement.executeQuery(sql);
if (rs.next()) {
player = new Players(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5),
rs.getInt(6), rs.getString(7));
int id = rs.getInt(1);
String playID = rs.getString(2);
String name = rs.getString(3);
String team = rs.getString(4);
String city = rs.getString(5);
int maxScore = rs.getInt(6);
String draftYear = rs.getString(7);

System.out.println("ID:" + id);
System.out.println("playID:" + playID);
System.out.println("名字:" + name);
System.out.println("球隊:" + team);
System.out.println("城市:" + city);
System.out.println("個人單場最高分:" + maxScore);
System.out.println("選秀年:" + draftYear);

}
} catch (ClassNotFoundException 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();
} finally {
JDBCUtils.closeSource(rs, statement, conn);
}
// System.out.println(sql);
return player;
}

// 從控制台讀入一個整數，返回1，用球員ID 查詢，2，用姓名查詢，其他的無效並提示讓使用者重新輸入
private static int getTypeFromConsole() {
// TODO Auto-generated method stub
// 1 根據提示讓使用者輸入查詢類型，返回1，用球員ID 查詢，2，用姓名查詢
System.out.println("請輸入查詢類型:1.使用球員ID查詢,2.使用姓名查詢");
Scanner input = new Scanner(System.in);
int type = input.nextInt();
// 判斷
if (type != 1 && type != 2) {
System.out.println("輸入不合法,請重新輸入....");
throw new RuntimeException(); // 中斷程式

}
return type;
}

}

=================================================================================================================================================================

JDBCUtils.java的原始碼檔案：

package com.jdbc.basedemo;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

/*
* 操作資料庫的公用類，裡面是一些公用方法
* */
public class JDBCUtils {
// 擷取資料庫連接
// 串連資料庫

public static Connection getConnection() throws ClassNotFoundException, IOException, SQLException {
// 定義相關變數
String driverClass = null;
String jdbcUrl = null;
String user = null;
String password = null;

// 讀取設定檔jdbc.properties
InputStream in = JDBCUtils.class.getClassLoader().getResourceAsStream("jdbc.properties");

Properties properties = new Properties();
properties.load(in);
driverClass = properties.getProperty("driver");
jdbcUrl = properties.getProperty("jdbcUrl");
user = properties.getProperty("user");
password = properties.getProperty("password");
// 載入資料庫驅動
Class.forName(driverClass);
// 擷取串連
Connection conn = DriverManager.getConnection(jdbcUrl, user, password);
return conn;
}

// 釋放資源的方法
public static void closeSource(ResultSet rs, Statement statement, Connection conn) {
if (rs != null) {
try {
rs.close();
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}
}
if (statement != null) {
try {
statement.close();
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}
}
}

// 封裝一個通用的更新方法，適用於INSERT,UPDATE,DELETE
public static void updateData(String sql) {
// 擷取串連
Connection conn = null;
Statement statement = null;
ResultSet rs = null;
try {
conn = JDBCUtils.getConnection();// 獲得串連
statement = conn.createStatement(); // 建立statement對象
statement.executeUpdate(sql);
// System.out.println(sql);
System.out.println("SQL執行成功");
} catch (ClassNotFoundException 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();
} finally {
JDBCUtils.closeSource(rs, statement, conn);
System.out.println("資源關閉成功");
}

}
// 使用preparedStatement對象進行更新

public static void updateDataUsePS(String sql, Object... args) {
// 擷取串連
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = JDBCUtils.getConnection();// 獲得串連
ps = conn.prepareStatement(sql); // 建立statement對象
// 擷取可變參數的值
for (int i = 0; i < args.length; i++) {
ps.setObject(i + 1, args[i]);
}
ps.executeUpdate();
// System.out.println(sql);
System.out.println("SQL執行成功");
} catch (ClassNotFoundException 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();
} finally {
JDBCUtils.closeSource(null, ps, conn);
System.out.println("資源關閉成功");
}

}
}

JDBC封裝學習筆記(三)---物件導向的JDBC，使用preparedStatement