使用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对象进行更新
1 public static voidupdateDataUsePS(String sql, Object... args) {2 //获取连接
3 Connection conn = null;4 PreparedStatement ps = null;5 ResultSet rs = null;6 try{7 conn = JDBCUtils.getConnection();//获得连接
8 ps = conn.prepareStatement(sql); //创建statement对象9 //获取可变参数的值
10 for (int i = 0; i < args.length; i++) {11 ps.setObject(i + 1, args[i]);12 }13 ps.executeUpdate();14 //System.out.println(sql);
15 System.out.println("SQL执行成功");16 } catch(ClassNotFoundException e) {17 //TODO Auto-generated catch block
18 e.printStackTrace();19 } catch(IOException e) {20 //TODO Auto-generated catch block
21 e.printStackTrace();22 } catch(SQLException e) {23 //TODO Auto-generated catch block
24 e.printStackTrace();25 } finally{26 JDBCUtils.closeSource(null, ps, conn);27 System.out.println("资源关闭成功");28 }29
30 }
然后在实际的操作中调用这个方法:
// 插入一个新的运动员,使用preparedStatement
1 public voidtestAddPlayers1(Players player) {2 String sql = "insert into userinfo values (?,?,?,?,?,?,?)";3 JDBCUtils.updateDataUsePS(sql, player.getId(), player.getPlayerID(), player.getName(), player.getTeam(),4 player.getCity(), player.getMaxScore(), player.getDraftyear());5 }
//在测试方法中,去调用testAddPlayers1(Players player) 这个方法:
// 测试方法
1 @Test2 public voidtestAddPlayers() {3 Players player =getFromConsole();4 testAddPlayers1(player);5 //return player;
6 }
这里就可以使用Junit框架进行测试了,控制台运行截图如下所示:
完整源代码如下所示:
1 packagecom.jdbc.basedemo;2
3 importjava.io.IOException;4 importjava.sql.Connection;5 importjava.sql.ResultSet;6 importjava.sql.SQLException;7 importjava.sql.Statement;8 importjava.util.Scanner;9
10 importorg.junit.Test;11
12 importcom.jdbc.bean.Players;13
14 public classCrudPlayer {15 //添加一个运动员,传入一个运动员对象
16 public voidaddPlayers(Players player) {17 //给出一个SQL
18 String sql = "insert into userinfo" + " values (" + player.getId() + ",'" + player.getPlayerID() + "','"
19 + player.getName() + "','" + player.getTeam() + "','" + player.getCity() + "'," +player.getMaxScore()20 + ",'" + player.getDraftyear() + "')";21 System.out.println(sql);22 //调用更新方法
23 JDBCUtils.updateData(sql);24 }25
26 //@插入一个新的运动员,使用preparedStatement
27
28 public voidtestAddPlayers1(Players player) {29 String sql = "insert into userinfo values (?,?,?,?,?,?,?)";30 JDBCUtils.updateDataUsePS(sql, player.getId(), player.getPlayerID(), player.getName(), player.getTeam(),31 player.getCity(), player.getMaxScore(), player.getDraftyear());32 }33
34 //测试方法
35 @Test36 public voidtestAddPlayers() {37 Players player =getFromConsole();38 testAddPlayers1(player);39 //return player;
40 }41
42 //从控制台获取输入的运动员信息
43 privatePlayers getFromConsole() {44 //TODO Auto-generated method stub
45 Scanner input = newScanner(System.in);46 Players player = newPlayers();47 System.out.println("请输入球员信息");48 //这里因为主键在数据表中设置了自增,所以拼接的SQL中只获取,而不用再一次输入ID
49 System.out.print("球员ID:");50 player.setPlayerID(input.next());51 System.out.print("球员姓名:");52 player.setName(input.next());53 System.out.print("球队名称:");54 player.setTeam(input.next());55 System.out.print("球队城市:");56 player.setCity(input.next());57 System.out.print("个人单场最高分:");58 player.setMaxScore(input.nextInt());59 System.out.println("选秀年:");60 player.setDraftyear(input.next());61
62 returnplayer;63 }64
65 //查询类型
66 @Test67 public voidtestGetPlayer() {68 //获取查询类型
69 int searchType =getTypeFromConsole();70 //执行查询
71 Players players =searchPlayer(searchType);72 //打印运动员信息
73 printPlayer(players);74
75 //return players;
76 }77
78 //打印运动员信息
79 private voidprintPlayer(Players players) {80 //TODO Auto-generated method stub
81 if (players != null) {82 System.out.println(players);83 } else{84 System.out.println("查无此人....");85 }86 }87
88 //具体查询运动员信息方法
89 private Players searchPlayer(intsearchType) {90 //TODO Auto-generated method stub91 //select * 的写法不好,这里为了偷懒
92 String sql = "select * from userinfo" + " where ";93 Scanner scanner = newScanner(System.in);94 //根据确定的type,提示用户输入查询的类型
95 最终确认SQL语句
96 if (searchType == 1) {97 System.out.print("请输入球员ID编号:");98 String playerID =scanner.next();99 sql = sql + "playID='" + playerID + "'";100 } else{101 System.out.print("请输入球员姓名:");102 String name =scanner.next();103 sql = sql + "username='" + name + "'";104 }105 //执行查询
106 Players player =getPlayer(sql);107
108 returnplayer;109 }110
111 //执行查询的方法,根据SQL语句执行查询
112 privatePlayers getPlayer(String sql) {113 //TODO Auto-generated method stub
114 Players player = null;115 Connection conn = null;116 Statement statement = null;117 ResultSet rs = null;118
119 try{120 conn =JDBCUtils.getConnection();121 statement =conn.createStatement();122 rs =statement.executeQuery(sql);123 if(rs.next()) {124 player = new Players(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5),125 rs.getInt(6), rs.getString(7));126 int id = rs.getInt(1);127 String playID = rs.getString(2);128 String name = rs.getString(3);129 String team = rs.getString(4);130 String city = rs.getString(5);131 int maxScore = rs.getInt(6);132 String draftYear = rs.getString(7);133
134 System.out.println("ID:" +id);135 System.out.println("playID:" +playID);136 System.out.println("名字:" +name);137 System.out.println("球队:" +team);138 System.out.println("城市:" +city);139 System.out.println("个人单场最高分:" +maxScore);140 System.out.println("选秀年:" +draftYear);141
142 }143 } catch(ClassNotFoundException e) {144 //TODO Auto-generated catch block
145 e.printStackTrace();146 } catch(IOException e) {147 //TODO Auto-generated catch block
148 e.printStackTrace();149 } catch(SQLException e) {150 //TODO Auto-generated catch block
151 e.printStackTrace();152 } finally{153 JDBCUtils.closeSource(rs, statement, conn);154 }155 //System.out.println(sql);
156 returnplayer;157 }158
159 //从控制台读入一个整数,返回1,用球员ID 查询,2,用姓名查询,其他的无效并提示让用户重新输入
160 private static intgetTypeFromConsole() {161 //TODO Auto-generated method stub162 //1 根据提示让用户输入查询类型,返回1,用球员ID 查询,2,用姓名查询
163 System.out.println("请输入查询类型:1.使用球员ID查询,2.使用姓名查询");164 Scanner input = newScanner(System.in);165 int type =input.nextInt();166 //判断
167 if (type != 1 && type != 2) {168 System.out.println("输入不合法,请重新输入....");169 throw new RuntimeException(); //中断程序
170
171 }172 returntype;173 }174
175 }
=================================================================================================================================================================
JDBCUtils.java的源代码文件:
1 packagecom.jdbc.basedemo;2
3 importjava.io.IOException;4 importjava.io.InputStream;5 importjava.sql.Connection;6 importjava.sql.DriverManager;7 importjava.sql.PreparedStatement;8 importjava.sql.ResultSet;9 importjava.sql.SQLException;10 importjava.sql.Statement;11 importjava.util.Properties;12
13 /*
14 * 操作数据库的公共类,里面是一些公共方法15 **/
16 public classJDBCUtils {17 //获取数据库连接18 //连接数据库
19
20 public static Connection getConnection() throwsClassNotFoundException, IOException, SQLException {21 //定义相关变量
22 String driverClass = null;23 String jdbcUrl = null;24 String user = null;25 String password = null;26
27 //读取配置文件jdbc.properties
28 InputStream in = JDBCUtils.class.getClassLoader().getResourceAsStream("jdbc.properties");29
30 Properties properties = newProperties();31 properties.load(in);32 driverClass = properties.getProperty("driver");33 jdbcUrl = properties.getProperty("jdbcUrl");34 user = properties.getProperty("user");35 password = properties.getProperty("password");36 //加载数据库驱动
37 Class.forName(driverClass);38 //获取连接
39 Connection conn =DriverManager.getConnection(jdbcUrl, user, password);40 returnconn;41 }42
43 //释放资源的方法
44 public static voidcloseSource(ResultSet rs, Statement statement, Connection conn) {45 if (rs != null) {46 try{47 rs.close();48 } catch(Exception e) {49 //TODO: handle exception
50 e.printStackTrace();51 }52 }53 if (statement != null) {54 try{55 statement.close();56 } catch(Exception e) {57 //TODO: handle exception
58 e.printStackTrace();59 }60 }61 if (conn != null) {62 try{63 conn.close();64 } catch(Exception e) {65 //TODO: handle exception
66 e.printStackTrace();67 }68 }69 }70
71 //封装一个通用的更新方法,适用于INSERT,UPDATE,DELETE
72 public static voidupdateData(String sql) {73 //获取连接
74 Connection conn = null;75 Statement statement = null;76 ResultSet rs = null;77 try{78 conn = JDBCUtils.getConnection();//获得连接
79 statement = conn.createStatement(); //创建statement对象
80 statement.executeUpdate(sql);81 //System.out.println(sql);
82 System.out.println("SQL执行成功");83 } catch(ClassNotFoundException e) {84 //TODO Auto-generated catch block
85 e.printStackTrace();86 } catch(IOException e) {87 //TODO Auto-generated catch block
88 e.printStackTrace();89 } catch(SQLException e) {90 //TODO Auto-generated catch block
91 e.printStackTrace();92 } finally{93 JDBCUtils.closeSource(rs, statement, conn);94 System.out.println("资源关闭成功");95 }96
97 }98 //使用preparedStatement对象进行更新
99
100 public static voidupdateDataUsePS(String sql, Object... args) {101 //获取连接
102 Connection conn = null;103 PreparedStatement ps = null;104 ResultSet rs = null;105 try{106 conn = JDBCUtils.getConnection();//获得连接
107 ps = conn.prepareStatement(sql); //创建statement对象108 //获取可变参数的值
109 for (int i = 0; i < args.length; i++) {110 ps.setObject(i + 1, args[i]);111 }112 ps.executeUpdate();113 //System.out.println(sql);
114 System.out.println("SQL执行成功");115 } catch(ClassNotFoundException e) {116 //TODO Auto-generated catch block
117 e.printStackTrace();118 } catch(IOException e) {119 //TODO Auto-generated catch block
120 e.printStackTrace();121 } catch(SQLException e) {122 //TODO Auto-generated catch block
123 e.printStackTrace();124 } finally{125 JDBCUtils.closeSource(null, ps, conn);126 System.out.println("资源关闭成功");127 }128
129 }130 }