本文为博主辛苦总结,希望自己以后返回来看的时候理解更深刻,也希望可以起到帮助初学者的作用.
转载请注明 出自 : luogg的博客园 谢谢配合!
jdbc day02
DML语法
- 比起插叙语句,没有ResultSet结果集,stmt.executeUpdate(sql)返回的是改变几行的int类型.
前几步和day01一致
//3.写sql语句
String sql = "insert into copy_emp(last_name) values("+"'洛哥哥')";
//4.创建发送对象
Statement stmt = conn.createStatement();
stmt.executeUpdate(sql);
//7.关闭资源
SQL注入
sql注入就是用户输入的值,当做了sql语言的一部分,然后攻破密码等重要信息.
为了防止sql注入,就要将含有单引号的采用预编译处理.
比如 :
String USERNAME = "admin";
String PASSWORD = "123 OR '1' = '1";
(sql注入,使破解后的密码恒成立)
打印输入密码之后的sql语言
select count(*) from login where USERNAME= 'admin' and PASSWORD= '123' OR '1'='1';
预编译
- 采用?占位符, 将需要输入单引号的sal语法用?表示.
- 书写简单,而且防止sql注入问题.
- PreparedStatement继承Statement.
- 预编译中的问号,pre.setString(1,"哈哈");
1代表第一个问号,不代表第一个参数.
预编译中和普通编译区别在于:
普通编译:
//4.创建发送对象
Statement stmt = conn.createStatement();
//5.发送sql语句
ResultSet rs = stmt.executeQuery(sql);
预编译
//4.创建预编译对象
PreparedStatement pstmt = conn.prepareStatement(sql);
//5.填入数据
pstmt.setString(1, myUsername);
pstmt.setString(2, myPassword);
//6.创建结果集
ResultSet rs = pstmt.executeQuery();
前两步与day01一致,查看用户输入的用户名和密码是否是已经存在数据库的
String myUsername = "admin";
String myPassword = "admin";
//3.写sql语句(判断输入的用户名密码在数据库中有几条记录)
String sql = "select count(*) count from login1 where username = ? and password = ?";
//4.创建预编译对象
PreparedStatement pstmt = conn.prepareStatement(sql);
//5.填入数据
pstmt.setString(1, myUsername);
pstmt.setString(2, myPassword);
//6.创建结果集
ResultSet rs = pstmt.executeQuery();
//7.查询select结果
if(rs.next()) {
String flag = rs.getString("count");
if("1".equals(flag)) {
System.out.println("登录成功");
} else {
System.out.println("登录失败");
}
}
//8.关闭资源
DBUtil
- 将属性和方法设置为static静态,方便以后直接用类名调用.
public class DBUtil {
private final static String DriverName = "oracle.jdbc.OracleDriver";
private final static String URL = "jdbc:oracle:thin:@localhost:1521:orcl";
private final static String USERNAME = "scott";
private final static String PASSWORD = "luogg";
private static Connection conn ;
private static Statement stmt ;
//private static PreparedStatement pstmt;
/**
* 连接数据库方法
* @return Connection
* @param conn 数据库连接对象
*/
public static Connection getConnection() {
try {
Class.forName(DriverName);
conn = DriverManager.getConnection(URL,USERNAME,PASSWORD);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return conn;
}
/**
* 查询语句关闭
* @param rs 结果集
* @param stmt 发送对象
* @param conn 连接对象
*/
public static void close(ResultSet rs,Statement stmt,Connection conn) {
if(rs != null){
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(stmt != null) {
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(conn != null) {
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
/**
* 非查询语句关闭资源
* @param stmt 发送对象
* @param conn 连接对象
*/
public static void close(Statement stmt,Connection conn) {
close(null,stmt,conn);
}
/**
* 查询语句
* @param sql
* @return ResultSet
*/
public static ResultSet selectSql(String sql) {
ResultSet rs = null;
try {
stmt = conn.createStatement();
rs = stmt.executeQuery(sql);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return rs;
}
/**
* 增删改的SQL语句
* @param sql
* @return
*/
public static int runSql(String sql) {
int result = 0;
try {
Statement stmt = conn.createStatement();
result = stmt.executeUpdate(sql);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return result;
}